UK Bank HolidaysSQL
The UK government publish the UK bank holidays as a JSON object at a public endpoint. DuckDB can read the data from the endpoint, unpivot it, and expand it in a few lines!
Execute this SQL
/* division, title, date, notes, bunting */ select division, unnest(events.events, recursive:=true) from ( unpivot 'https://www.gov.uk/bank-holidays.json' on "england-and-wales", "scotland", "northern-ireland" into name division value events )Copy code
Expand
Share link
Parquet to ExcelSQL
Editor's note: although not very intuitive, format for MS Excel XLSX files is provided by the spatial extension. While other snippets show you how to read Excel files, this shows you how to write data from a named Parquet file (or any DuckDB resultset) to Excel.
Execute this SQL
INSTALL spatial; LOAD spatial; COPY (SELECT * FROM 'source.parquet') TO 'target.XLSX' WITH (FORMAT GDAL, DRIVER 'XLSX');Copy code
Expand
Share link
build-your-own SUMMARIZE with columns(*), unpivot, and struct.*SQL
Editor's note: Although SUMMARIZE() is fully supported in DuckDB, Hamilton shows you how to build this capability yourself using more primitive functions.
Execute this SQL
-- C = number of columns in TBL, S = number of aggregates -- ------------------------------------------------------ -- (1) use columns(*) to produce S aggregates -- for each column as a single struct, -- producing 1 struct row and C columns with aggregate as ( from sf311 select { name: first(alias(columns(*))), type: first(typeof(columns(*))), max: max(columns(*))::varchar, min: min(columns(*))::varchar, approx_unique: approx_count_distinct(columns(*)), nulls: count(*) - count(columns(*)) } ), -- (2) unpivot to get a result with C rows and 1 column of structs columns as (unpivot aggregate on columns(*)) -- (3) explode the struct to get a result with C rows and S columns select value.* from columns;Copy code
Expand
Share link
Execute this SQL
-- First, transform TBL (or any relation) -- into a single column of structs. with structs (from TBL select TBL) -- Then pack those structs into a list. from structs select list(TBL) as ready_to_plot;Copy code
Copy code
Expand
Share link
Execute this SQL
--Below SQL to generate MD5 of a string select md5('test')Copy code
Execute this SQL
-- list and array_agg take in their own ORDER BY clause, so that you -- can sort the aggregate. The statements order by cannot be used -- as the columns that are used for sorting then need to be a grouping -- key and cannot be used in the aggregate SELECT name, -- Order the aggregated list by another column from line_items list(item_name ORDER BY pos ASC) items FROM orders JOIN line_items ON order_id = id GROUP BY ALL -- Order by grouping keys is ofc possible ORDER BY name;Copy code
Copy code
Expand
Share link
Loading remote parquet files
Editor's note: DuckDB supports lambda functions for easy transformation. It also supports reading data over http/https [in addition from blob stores]. Simon's snippet shows you how to use both of those capabilities to dynamically generate filenames and query them when globbing is unsupported.
Load a single parquet fileSQL
-- Query a single remote parquet file -- Query the January data from NY taxi data set SELECT tpep_pickup_datetime, total_amount FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-01.parquet') LIMIT 10;Copy code
Dynamically iterate over remote parquet files - lambda function and list_transformSQL
-- Dynamically create the expected filenames to load -- eg, the files names `2023-01.parquet` to `2023-06.parquet` -- a lambda function can use the result of the generate_series(1, 6) -- and the format command can the `{:02d}` format specifier -- to left-pad filenames with 0's SELECT filename, min(tpep_pickup_datetime), max(tpep_pickup_datetime), avg(total_amount) FROM read_parquet( list_transform( generate_series(1, 6), n -> format('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-{:02d}.parquet', n) ), filename=true ) GROUP BY filename ORDER BY filename;Copy code
Expand
Share link