# Querying the HTTP Archive with DuckDB

**URL:** <https://discuss.httparchive.org/t/querying-the-http-archive-with-duckdb/2568>\
**Category:** Analysis\
**Created:** [May 3, 2023, 10:51am UTC](https://discuss.httparchive.org/t/querying-the-http-archive-with-duckdb/2568 "2023-05-03T10:51:04Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![acme](https://yyz1.discourse-cdn.com/flex035/user_avatar/discuss.httparchive.org/acme/32/2123_2.png) [@acme](https://discuss.httparchive.org/u/acme)\
**Post date:** [May 3, 2023, 10:51am UTC](https://discuss.httparchive.org/t/querying-the-http-archive-with-duckdb/2568/1 "2023-05-03T10:51:04Z")

</div>

I needed to explore ETag response headers locally and I’ve come up with a workflow for querying HTTP Archive data on my laptop in Parquet format using [DuckDB](https://duckdb.org/).

You may have to create Google Cloud project IDs, Google BigQuery workspaces, Google Cloud Storage buckets etc.

Query the HTTP Archive in Google BigQuery to create a new table:

```sql
SELECT
  resp_etag,
  COUNT(*) AS sum
FROM
  `httparchive.summary_requests.2023_02_01_mobile`
GROUP BY
  resp_etag
HAVING
  sum > 1
ORDER BY
  sum DESC

```

```sql
bq query --use_legacy_sql=false --destination_table lbrocard_bigquery.httparchive_summary_requests_2023_02_01_mobile_resp_etag < etags.sql

```

That makes a 1.04 GB table with 34,316,234 rows.

Extract the table as compressed Parquet format files in a Google Cloud Storage bucket:

```sql
bq extract --destination_format PARQUET --compression ZSTD 'lbrocard_bigquery.httparchive_summary_requests_2023_02_01_mobile_resp_etag' 'gs://lbrocard-httparchive/httparchive.summary_requests.2023_02_01_mobile_resp_etag/*'

```

This creates 7 Parquet files, with a combined size of 528 MB.

Copy the files locally:

mkdir httparchive.summary\_requests.2023\_02\_01\_mobile\_resp\_etag  
gsutil -m rsync gs://lbrocard-httparchive/httparchive.summary\_requests.2023\_02\_01\_mobile\_resp\_etag/ httparchive.summary\_requests.2023\_02\_01\_mobile\_resp\_etag

Then query the local files using the duckdb CLI with:

```sql
duckdb
D SELECT resp_etag, sum FROM read_parquet('httparchive.summary_requests.2023_02_01_mobile_resp_etag/*') WHERE regexp_matches(resp_etag, '(W/)?".*apple.*"$') ORDER BY sum DESC;
┌──────────────────────────────────────────────────────────────────────────────────────┬───────┐
│ resp_etag │ sum │
│ varchar │ int64 │
├──────────────────────────────────────────────────────────────────────────────────────┼───────┤
│ "apple-touch-icon-07b519aab2aa41fafd5da3a6642a5d9c.png" │ 12 │
│ "global/download/apple-62b449309f50b222e6e6f06e581eb66e.svg" │ 10 │
│ "apple/apple-login-53408aea6fc94d03eab2e540df50c123.js" │ 5 │
│ W/"static/media/icon-apple.74ca668ab1d7f2eafa5d.756fab2c07.svg" │ 3 │
│ W/"favicons/apple-touch-icon-60x60-precomposed-a0e563e71bba6a64f81bda6bc7b0e313.png" │ 2 │
│ "images/home-page/header/apple-6046f0c7c8a1fe157f8d5b5d1212a3f9.svg" │ 2 │
└──────────────────────────────────────────────────────────────────────────────────────┴───────┘

```

I embed similar queries into an R Markdown document and generate some interesting reports.

Hope this is useful! Leon