<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/two-new-apache-drill-udfs-for-processing-urils-and-internet-domain-names/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2018/07/26/two-new-apache-drill-udfs-for-processing-urils-and-internet-domain-names/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Continuing the blog’s UDF theme of late, there are two new UDF kids in town:</p>
<ul>
<li><a rel="nofollow" href="https://github.com/hrbrmstr/drill-url-tools" target="_blank"><code>drill-url-tools</code><img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> for slicing &amp; dicing URI/URLs (just going to use ‘URL’ from now on in the post)</li>
<li><a rel="nofollow" href="https://github.com/hrbrmstr/drill-domain-tools" target="_blank"><code>drill-domain-tools</code><img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> for slicing &amp; dicing internet domain names (IDNs).</li>
</ul>
<p>Now, if you’re an Apache Drill fanatic, you’re likely thinking <em>“Hey hrbrmstr: don’t you know that Drill has a <a rel="nofollow" href="https://github.com/apache/drill/blob/62f14690870568364723dc77494043a9854a0447/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/ParseUrlFunction.java" target="_blank"><code>parse_url()</code><img alt="🔗" src="https://i2.wp.com/s.w.org/images/core/emoji/2.4/72x72/1f517.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></a> function already?”</em> My answer is <em>“Sure, but it’s based on <code>java.net.URL</code> which is <a rel="nofollow" href="http://galimatias.mola.io/" target="_blank">fundamentally broken</a>.”</em></p>
<p>Slicing &amp; dicing URLs and IDNs is a large part of the <code>$DAYJOB</code> and they go together pretty well, hence the joint UDF release.</p>
<p>Rather than just use boring SQL for an example, we’ll start with some SQL and use R for a decent example of working with the two, new UDFs.</p>
<h3>Counting Lying Lock Icons</h3>
<p>SSL/TLS is all the craze these days, so let’s see how many distinct sites in the <a rel="nofollow" href="https://blog.gdeltproject.org/announcing-gdelt-global-frontpage-graph-gfg/" target="_blank">GDELT Global Front Page</a> (GFG) data set use port 443 vs port 80 (a good indicator, plus it will help show how the URL tools pick up ports even when they’re not there).</p>
<p>If you go to the aforementioned URL it instructs us that the most current GFG dataset URL can be retrieved by inspecting the contents of <a rel="nofollow" href="http://data.gdeltproject.org/gdeltv3/gfg/alpha/lastupdate.txt" target="_blank">this metadata URL</a></p>
<p>There are over a million records in that data set but — as we’ll see — not nearly as many distinct hosts.</p>
<p>Let’s get the data:</p>
<pre><code>library(sergeant)
library(tidyverse)

read_delim(
  file = &quot;http://data.gdeltproject.org/gdeltv3/gfg/alpha/lastupdate.txt&quot;, 
  delim = &quot; &quot;, 
  col_names = FALSE,
  col_types = &quot;ccc&quot;
) -> gfg_update

dl_path &lt;- file.path(&quot;~/Data/gfg_links.tsv.gz&quot;)

if (!file.exists(dl_path)) download.file(gfg_update$X3[1], dl_path)</code></pre>
<p>Those operations have placed the GFG data set in a place where my local Drill instance can get to them. It's a tab separated file (TSV) which — while not a great data format — is workable with Drill.</p>
<p>Now we'll setup a SQL query that will parse the URLs and domains, giving us a nice rectangular structure for R &amp; <code>dbplyr</code>. We'll use the second column since a significant percentage of the URLs in column 6 are malformed:</p>
<pre><code>db &lt;- src_drill()

tbl(db, &quot;(
SELECT 
  b.host,
  port,
  b.rec.hostname AS hostname,
  b.rec.assigned AS assigned,
  b.rec.tld AS tld,
  b.rec.subdomain AS subdomain
FROM
  (SELECT
    host, port, suffix_extract(host) AS rec             -- break the hostname into components
  FROM
    (SELECT
      a.rec.host AS host, a.rec.port AS port
    FROM
      (SELECT 
        columns[1] AS url, url_parse(columns[1]) AS rec -- break the URL into components
      FROM dfs.d.`/gfg_links.tsv.gz`) a
    WHERE a.rec.port IS NOT NULL                        -- filter out URL parsing failures
    )
  ) b
WHERE b.rec.tld IS NOT NULL                             -- filter out domain parsing failures
)&quot;) -> gfg_df

gfg_df
## # Database: DrillConnection
##    hostname  port host              subdomain assigned      tld  
##    </code></pre>