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
Sort by WeekdaysSQL
Execute this SQL
-- duckdb has only weekday-name not number -- create enum in the right order create type Weekday as enum ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'); -- cast the string to enum for sorting select count(*), cast(dayname(CreationDate) as Weekday) as day from posts where posttypeid = 1 and tags like '%>sql<%' group by all order by day asc; -- doing the cast in order by causes a bug -- ┌──────────────┬───────────┐ -- │ count_star() │ day │ -- │ int64 │ weekday │ -- ├──────────────┼───────────┤ -- │ 103937 │ Monday │ -- │ 115575 │ Tuesday │ -- │ 119825 │ Wednesday │ -- │ 119514 │ Thursday │ -- │ 103445 │ Friday │ -- │ 47139 │ Saturday │ -- │ 47390 │ Sunday │ -- └──────────────┴───────────┘
Copy code
Copy code
Expand
Share link
Creating parameterized views with TVFs macros (Table-Valued functions)SQL
Execute this SQL
-- create macro CREATE OR REPLACE MACRO udf_products_in_year (v_year, v_category) AS TABLE SELECT name, category, created_at FROM products WHERE category = v_category AND year(created_at) = v_year; -- select using the macro as you would do from a table SELECT * FROM udf_products_in_year (2020, 'Home and Garden'); | Copper Light | Home and Garden | 2020-04-05 00:00:00.000 | | Pink Armchair | Home and Garden | 2020-06-23 00:00:00.000 | -- input ddl and data CREATE TABLE products ( name varchar, category varchar, created_at timestamp ); INSERT INTO products VALUES ('Cream Sofa', 'Home and Garden', '2019-03-14'), ('Copper Light', 'Home and Garden', '2020-04-05'), ('Pink Armchair', 'Home and Garden', '2020-06-23');
Copy code
Copy code
Expand
Share link
Aggregate rows into a sorted list.SQL
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
Allow unsigned extensions (own extensions or untrusted third-parties)Bash
Editor's note: While DuckDB has 20+ official extensions, it also allows you to write your own extensions. In order load extensions from unofficial repositories, you'll need to let DuckDB know that it's safe to do so. This can be done using the allow_unsigned_extensions flag or -unsigned for the CLI.
Execute this Bash
duckdb -unsigned
Copy code
Expand
Share link
Boxplot quantiles all at onceSQL
Editor's note: with a variety of supported statistical functions, including things like sampling and producing quantiles, DuckDB provides great analytical capabilities not always present in other databases.
Execute this SQL
select unnest([.1,.25,.5,.75,.9]) as quantile, unnest(quantile_cont(i, [.1,.25,.5,.75,.9])) as value from range(101) t(i); /* This returns: | quantile | value | | 0.10 | 10 | | 0.25 | 25 | | 0.50 | 50 | | 0.75 | 75 | | 0.90 | 90 | */
Copy code
Expand
Share link
Convert CSV to Parquet and provide schema to useBash
Editor's note: while there are other snippets showing file conversion, Parth's shows you how to convert from CSV to Parquet files using DuckDB with specification of the entire schema (columns) and compression codec.
Execute this Bash
duckdb -c "COPY (SELECT * FROM read_csv('pageviews-sanitized-20230101-000000.csv', delim=' ', header=False, columns={'domain_code': 'VARCHAR', 'page_title': 'VARCHAR', 'count_views': 'UINTEGER', 'total_response_size': 'UINTEGER'})) TO 'pageviews-sanitized-20230101-000000.parquet' (FORMAT 'PARQUET', CODEC 'zstd')"
Copy code
Expand
Share link