28:null 29:["$","title",null,{"children":"Introducing sqlite-url: A SQLite extension for parsing and generating URLs | Alex Garcia | Observable"}] 2e:T477,^The above uses Observable's [SQL Cells](https://observablehq.com/@observablehq/sql-cell) Sweet! Let's play with a small dataset extracted from the [News Homepages](https://palewi.re/docs/news-homepages/) project. There, Ben Welsh maintains a large list of scrapers that archive the front page of various news websites, and publishes the data for anyone to view. We'll take a look a a small slice of the [hyperlinks](https://palewi.re/docs/news-homepages/extracts.html#hyperlink-files-csv) extract from that project, which contains the links that news homepages contains (aka the `href` attributes of all `` elements). For this demo, we get the data of all links from washingtonpost.com on 2022-09-11, which we load into a table called `washingtonpost_links` like so: ```sql create table washingtonpost_links as select value ->> 'text' as text, value ->> 'url' as url from json_each( http_get_body( 'https://ia902505.us.archive.org/25/items/washingtonpost-2022/washingtonpost-2022-09-11T17:18:52.590600-04:00.hyperlinks.json' ) ); ``` And here we query that table in our Datasette instance:2f:T48c,

https://www.washingtonpost.com/national-security/2022/09/06/trump-nuclear-documents/
https://www.washingtonpost.com/opinions/2022/09/11/post-elizabeth-newsletter-royal-family-transition-grieving-charles/
https://www.washingtonpost.com/elections/2022/09/08/an-ex-professor-spreads-election-myths-across-us-one-town-time/
https://www.washingtonpost.com/politics/2022/09/08/congress-pramila-jayapal-threats-stalking/
https://www.washingtonpost.com/dc-md-va/2022/08/30/richmond-black-history-statues-jackson-ward/

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":30,"value":" DALL·E attempts for this post's thumbnail","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":13,"value":"## Demo\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":63,"value":"sqlite-url is written in C, and could theoretically be compiled to WASM, but libcurl is gnarly to compile into WASM (unlike [sqlite-path](https://observablehq.com/@asg017/introducing-sqlite-path) or [sqlite-lines](https://observablehq.com/@asg017/introducing-sqlite-lines)). Instead, we will use [Datasette](https://datasette.io/) hosted on [fly.io](https://fly.io/) to try things out on the browser, without having to install anything!\n\nFirst off, we will import the [Datasette Client for Observable](https://observablehq.com/@ambassadors/datasette-client) notebook and load in a URL to our [Datasette instance](https://sqlite-extension-examples.fly.dev/data). ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":35,"value":"import { DatasetteClient } from \"@ambassadors/datasette-client\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":41,"value":"db = new DatasetteClient(\"https://sqlite-extension-examples.fly.dev/data\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":71,"value":"Let's verify that it loaded correctly, and that we can call the sqlite-url functions in SQL.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":73,"value":"select url_version(), url('https://alexgarcia.xyz/');","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":78,"value":"$2e","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":47,"value":"select * from washingtonpost_links;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":200,"value":"What are the most common domains in these links?","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":85,"value":"select url_host(url), count(*) \nfrom washingtonpost_links\nwhere url_valid(url)\ngroup by 1\norder by 2 desc;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":202,"value":"Sweet, mostly to other subdomains under washingtonpost.com, and a few 3rd party services like `wapo.onelink.me` and `search.proquest.com`!\n\nDo note that many of these \"URLs\" aren't _actual_ URLs. These are extracted from `href` attributes in `` elements, which could be relative links instead of absolute URLs. Let's take a look at all the invalid urls in this table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":90,"value":"select url, text\nfrom washingtonpost_links\nwhere not url_valid(url)\norder by 1","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":209,"value":"Yup, mostly relative URLs and anchors! \n\nLet's focus on the URLs that link to stories, where there are some interesting segments we can extract out and analyze. Here's a small sample:\n\n```\nhttps://www.washingtonpost.com/national-security/2022/09/06/trump-nuclear-documents/\nhttps://www.washingtonpost.com/opinions/2022/09/11/post-elizabeth-newsletter-royal-family-transition-grieving-charles/\nhttps://www.washingtonpost.com/elections/2022/09/08/an-ex-professor-spreads-election-myths-across-us-one-town-time/\nhttps://www.washingtonpost.com/politics/2022/09/08/congress-pramila-jayapal-threats-stalking/\nhttps://www.washingtonpost.com/dc-md-va/2022/08/30/richmond-black-history-statues-jackson-ward/\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":244,"value":"Immediately we can see one interesting insight: the first part of the URL path contains the \"section\" the story is filed under.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":137,"value":"$2f","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":247,"value":"The yellow highlight (national-security/2022/09/06/trump-nuclear-documents/) is the path of the given URL, which we can extract with `url_path()` like so:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":189,"value":"```sql\n.param set :url1 'https://www.washingtonpost.com/national-security/2022/09/06/trump-nuclear-documents/'\n\nselect url_path(:url1); -- '/national-security/2022/09/06/trump-nuclear-documents/'\n```\nThe blue highlight (national-security) is the \"section\" we want to extract. We can use the [`path_at()`](https://github.com/asg017/sqlite-path/blob/main/docs.md#path_part_at) function from the [`sqlite-path`](https://github.com/asg017/sqlite-path) to slice out that part, like so:\n\n```sql\nselect path_at(url_path(:url1), 0); -- 'national-security'\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":261,"value":"Now let's perform that on all the URLs in the table, and see what are most common sections that were linked on washingtonpost.com on 2022-09-11:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":96,"value":"select \n path_at(url_path(url), 0) as news_section,\n count(*)\nfrom washingtonpost_links\n-- filter out invalid URLs\nwhere news_section is not null\ngroup by 1\norder by 2 desc","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":268,"value":"World, sports, and opinions, gotta love the Washington Post...\n\nNow let's try the same thing on the \"date\" section of the URL, shown here:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":236,"value":"$30","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":271,"value":"Looks like the path parts at index 1, 2, and 3 contains the year, month, and date of the article's publish date (respectively). Let's see how old all those articles were on 2022-09-11!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":104,"value":"select\n -- validate as a real date with sqlite's date() function\n date(\n -- get '2022-09-06' from 'https://www.washingtonpost.com/national-security/2022/09/06/trump-nuclear-documents/'\n printf('%s-%s-%s', \n path_at(url_path(url), 1), \n path_at(url_path(url), 2), \n path_at(url_path(url), 3)\n )\n ) as date,\n count(*)\nfrom washingtonpost_links\n-- filter out invalid dates\nwhere date is not null\ngroup by 1\norder by 1;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":274,"value":"Seems like most linked stories were in the 1 week leading up to 2022-09-11, with the most popular date being that day with 79 links. There also appears to be some stragglers dating all the way back from 2011, for some reason...","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":277,"value":"Now the last trick for this dataset: The final part of the path is the [slug](https://en.wikipedia.org/wiki/Slug_(publishing) of the story. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":175,"value":"$31","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":197,"value":"We can extract this with `path_at()` like before, using `-1` as the index to get the last part in the path. But what interesting insight can we gain from just a list of slugs?\n\nWell, we can split each slug into individual words and see which words are the most common! Since each word is separated with a single dash (`-`), we can use the [`lines()`](https://github.com/asg017/sqlite-lines/blob/main/docs.md#linesdocument-delimeter) table function from the [`sqlite-lines`](https://github.com/asg017/sqlite-lines) project with a custom delimiter to split those words into individual rows!\n\nLet's test it first, combining the individual words into a JSON array to verify it works as expected. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":109,"value":"select \n distinct path_at(url_path(url), -1) as slug,\n (\n select json_group_array(line) \n from lines(path_at(url_path(url), -1), '-')\n ) as slug_words\nfrom washingtonpost_links\nwhere path_at(url_path(url), 1) = '2022'\norder by 1;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":300,"value":"So `\"14th-amendment-disqualification-couy-trump\"` gets split up into `[\"14th\", \"amendment\", \"disqualification\", \"couy\", \"trump\"]` with this method - looks right to me! Now let's get the most common words in all the linked stories from 2022-09-11:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":183,"value":"select\n slug_words.line as word,\n count(*)\nfrom washingtonpost_links\njoin lines(path_at(url_path(url), -1), '-') as slug_words\nwhere path_at(url_path(url), 1) = '2022'\ngroup by 1\norder by 2 desc\nlimit 15","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":307,"value":"Queen Elizabeth, Ukraine, Republicans? September 2022 sure has been weird...","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":8,"value":"## Stay in the loop!\n\nThis is a post in my [SQLite blog series](https://observablehq.com/@asg017/a-new-sqlite-blog-series), where I talk about new tools and SQLite extensions that I am making, guides, and how-to's. Consider subscribing to the [email newsletter](https://buttondown.email/alexgarcia)! **No more than one (1) post a week**.\n\n\n\nAlternatively, there is an [RSS feed](https://buttondown.email/alexgarcia/rss), or you can [follow me on Twitter](https://twitter.com/agarcia_me).\n\nNext up: Building SQLite extensions in Go, new SQLite extensions for querying Excel spreadsheets","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":82,"value":"## See Also\n\n${previews([\n {\n path: \"@asg017/introducing-sqlite-http\",\n title: \"Introducing sqlite-http - make HTTP Requests\",\n thumbnail: \"f39e88f0c499dcf949690a66732df01dc6dd8f3eb1bedb774a86714a91a20712\"\n },\n {\n path: \"@asg017/introducing-sqlite-path\",\n title: \"Introducing sqlite-path - \",\n thumbnail: \"bdfabc88f46def80b92828764ca65e445645aaff3eba4768df39fc13a27bf927\"\n },\n {\n path: \"@asg017/scrape-json-html-zip-with-sqlite\",\n title: \"Scraping JSON, HTML, and ZIP with SQLite\",\n thumbnail: \"c2de010b717366cf7cefe0d56aabb89fbfec2fba680a0e695c7ce21625f9f706\"\n },\n ])}\n \n ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":316,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":22,"value":"imgs = Promise.all([\n FileAttachment(\n \"DALL·E 2022-08-29 16.28.24 - sea lion astronaut drifting in space, dark black and bright green background, realistic, dramatic lighting.png\"\n ).url(),\n FileAttachment(\n \"DALL·E 2022-08-29 16.29.05 - sea lion astronaut drifting in space, dark black and bright pink background, cartoon.png\"\n ).url(),\n FileAttachment(\n \"DALL·E 2022-08-29 16.28.17 - sea lion astronaut drifting in space, dark black and bright green background, realistic, dramatic lighting.png\"\n ).url()\n])","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":320,"value":"import { preview, previews } from \"@d3/gallery\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W32","stdlib":"1","titling":"auto"}} 2d:"$2c:body"