SQL, Python & More for DuckDB - Page 4

‌

Emulate the Initcap functionSQL

In many database systems, there is a SQL function called something like INITCAP which capitalizes the first letter of all the words in a text string. Unfortunately, DuckDB doesn’t have this built-in, so let’s see if we can emulate it using function chaining and list comprehension.

Execute this SQL

SELECT ([upper (x[1])||x[2:] 
for x in 
(
'the quick brown fox jumped over the lazy dog'
).string_split(' ')]).list_aggr('string_agg',' ') 
Copy code

Thomas Reid

Created 08/21/24

‌

Updating CSV files in-placeSQL

DuckDB allows updating CSV files in-place.

Execute this SQL

COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x))
TO 'example.csv';

COPY (SELECT s FROM 'example.csv') TO 'example.csv';
Copy code

Gábor Szárnyas

Created 08/13/24

‌

Specifying types in the CSV loaderSQL

We can specify types for the `read_csv` function on a per-column basis.

Execute this SQL

COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x))
TO 'example.csv';

CREATE OR REPLACE TABLE example AS
    FROM read_csv('example.csv', types = {'x': 'DECIMAL(15, 3)'});
Copy code

Gábor Szárnyas

Created 08/13/24

‌

Deterministically shuffling dataSQL

Sometimes it's beneficial to shuffle data sets in a deterministic, repeatable way. We can achieve this by using the `hash` function on the `rowid` pseudocolumn.

Execute this SQL

COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x))
TO 'example.csv';

CREATE OR REPLACE TABLE example AS FROM 'example.csv';
FROM example ORDER BY hash(rowid + 42);
Copy code

Gábor Szárnyas

Created 08/13/24

‌

Copying the schema of a tableSQL

We can use `LIMIT` to copy the schema of an existing table without repeating the specification of its column names/types.

Execute this SQL

COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x))
TO 'example.csv';

CREATE TABLE tbl AS
    FROM example
    LIMIT 0;
Copy code

Gábor Szárnyas

Created 08/13/24

‌

Pretty-printing floating-pont numbersSQL

By casting the result to a `DECIMAL` with a fixed number of digits after the decimal point, we can pretty-print it while keeping it as a numeric value.

Execute this SQL

COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x))
TO 'example.csv';

SELECT x::DECIMAL(15, 3) AS x
FROM 'example.csv';
Copy code

Gábor Szárnyas

Created 08/13/24

‌

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

Bill Wallis

Created 04/08/24

‌

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

Simon Müller

Edited 02/23/24