Add DuckDB zipfile support example

[OlivierBinette]

DuckDB can read zipped tsv file directly from PatentsView bulk data download URLs using the new zipfs extension.

For example, using the DuckDB CLI:

INSTALL zipfs FROM community;
LOAD zipfs;

SELECT * FROM read_csv("zip://https://s3.amazonaws.com/data.patentsview.org/download/g_patent.tsv.zip/g_patent.tsv") LIMIT 5;
┌───────────┬─────────────┬─────────────┬───┬────────────┬───────────┬───────────────┐
│ patent_id │ patent_type │ patent_date │ … │ num_claims │ withdrawn │   filename    │
│   int64   │   varchar   │    date     │   │   int64    │   int64   │    varchar    │
├───────────┼─────────────┼─────────────┼───┼────────────┼───────────┼───────────────┤
│  10000000 │ utility     │ 2018-06-19  │ … │         20 │         0 │ ipg180619.xml │
│  10000001 │ utility     │ 2018-06-19  │ … │         12 │         0 │ ipg180619.xml │
│  10000002 │ utility     │ 2018-06-19  │ … │          9 │         0 │ ipg180619.xml │
│  10000003 │ utility     │ 2018-06-19  │ … │         18 │         0 │ ipg180619.xml │
│  10000004 │ utility     │ 2018-06-19  │ … │          6 │         0 │ ipg180619.xml │
├───────────┴─────────────┴─────────────┴───┴────────────┴───────────┴───────────────┤
│ 5 rows                                                         8 columns (6 shown) │
└────────────────────────────────────────────────────────────────────────────────────┘

We could use this to simplify the duckdb read-in example.

[OlivierBinette]

Here's an example computing the number of patent grants by first assignee state and by year in the US. The only dependency is the DuckDB CLI, which is easy to install.

1. First install the DuckDB CLI.
2. Run duckdb

duckdb temp.ddb

3. Copy-paste the following in the DuckDB CLI to download and parse tables from PatentsView's Bulk data downloads

INSTALL zipfs FROM community;
INSTALL httpfs;
LOAD zipfs;
LOAD httpfs;

CREATE OR REPLACE MACRO read_patentsview(table_name) AS TABLE (
    SELECT * FROM read_csv(
            "zip://https://s3.amazonaws.com/data.patentsview.org/download/"||table_name||".tsv.zip/"||table_name||".tsv", 
            all_varchar=true,
            delim="\t",
            escape='"',
            quote='"'
        )
);

CREATE TABLE IF NOT EXISTS patent AS (SELECT * FROM read_patentsview('g_patent'));
CREATE TABLE IF NOT EXISTS location_raw AS (SELECT * FROM read_patentsview('g_location_not_disambiguated'));
CREATE TABLE IF NOT EXISTS assignee_raw AS (SELECT * FROM read_patentsview('g_assignee_not_disambiguated'));

4. Copy-paste the following in the DuckDB CLI to compute the number of patents granted by first assignee state and by year:

CREATE OR REPLACE VIEW result AS
WITH 
    first_assignee AS (  -- First assignee listed on each patent
        SELECT 
            first(patent_id) AS patent_id,
            first(rawlocation_id) AS rawlocation_id
        FROM 
            (SELECT * FROM assignee_raw ORDER BY assignee_sequence ASC NULLS LAST)
        GROUP BY patent_id
    ),
    patent_with_year AS (
        SELECT 
            *, 
            datepart('year', cast(patent_date as DATE)) AS patent_year 
        FROM patent
    )
SELECT 
    raw_state,
    patent_year,
    count(*) as patent_count
FROM 
    patent_with_year
    left join first_assignee on patent_with_year.patent_id = first_assignee.patent_id
    left join location_raw on location_raw.rawlocation_id = first_assignee.rawlocation_id
WHERE raw_country = 'US'
GROUP BY raw_state, patent_year
ORDER BY patent_count DESC;

5. Export to CSV:

COPY result TO 'result.csv' (HEADER, DELIMITER ',');

Resulting CSV file: result.csv