Using the Raspberry Pi Pico's Built-in Temperature Sensor
Learn
0h 23m
22 Nov 24
Kevin McAleer
KevsRobots Learning Platform
DuckDB - Fast, free analytics
14% Percent Complete
Introduction
Getting Started with DuckDB
Integrating DuckDB with Python
Datalakes, Parquet, and Performance
Conclusion and Next Steps
Quickstart
One-page guide: install DuckDB, run first queries, export data, use Python.
By Kevin McAleer, 3 Minutes
TL;DR
- Install DuckDB (CLI) and Python package.
- Create a table from CSV, aggregate, and export results.
- Optional: enable httpfs for HTTPS/S3 reads.
Install
- macOS CLI:
brew install duckdb - Python:
python -m pip install duckdb pandas
Create a workspace folder and an exports/ subfolder so COPY/export won’t fail.
90 seconds in the CLI
-- Start the shell: duckdb
-- Create a small table from a public CSV
CREATE OR REPLACE TABLE tips AS
SELECT * FROM read_csv_auto('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/tips.csv');
-- Simple analytics
SELECT day, time, ROUND(AVG(total_bill), 2) AS avg_bill, COUNT(*) AS orders
FROM tips
GROUP BY day, time
ORDER BY avg_bill DESC;
-- Export results
COPY (SELECT * FROM tips LIMIT 100) TO 'exports/tips_sample.csv' (HEADER, DELIMITER ',');
COPY (SELECT * FROM tips) TO 'exports/tips.parquet' (FORMAT PARQUET);
Exit with .quit when done.
90 seconds in Python
import duckdb, pandas as pd
# Read CSV to a DataFrame
url = 'https://raw.githubusercontent.com/mwaskom/seaborn-data/master/tips.csv'
df = pd.read_csv(url)
# Query the DataFrame with SQL and get a DataFrame back
res = duckdb.query('''
SELECT day, time,
ROUND(AVG(total_bill), 2) AS avg_bill,
ROUND(AVG(tip / NULLIF(total_bill,0) * 100), 2) AS avg_tip_pct,
COUNT(*) AS orders
FROM df
GROUP BY day, time
ORDER BY avg_bill DESC
''').df()
print(res)
# Persist to a local .duckdb file for reuse
con = duckdb.connect('analytics.duckdb')
con.execute("CREATE TABLE IF NOT EXISTS tips AS SELECT * FROM df")
con.close()
Read from HTTPS/S3 (httpfs)
INSTALL httpfs;
LOAD httpfs;
SELECT COUNT(*) FROM read_parquet('https://duckdb-public-datasets.s3.us-east-1.amazonaws.com/tpch/1/parquet/lineitem/part-00000-*.parquet');
Use folder globs (*) to read many files at once.
Minimal Parquet export
Create Parquet from a CSV or table in one step. Parquet is faster to read and keeps types.
-- From an existing table
COPY (SELECT * FROM tips) TO 'exports/tips.parquet' (FORMAT PARQUET);
-- Or, directly from CSV without creating a table
COPY (
SELECT * FROM read_csv_auto('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/tips.csv')
) TO 'exports/tips.parquet' (FORMAT PARQUET);
Notes:
- Ensure the
exports/folder exists first. - Use a
.parquetextension; DuckDB infers Parquet fromFORMAT PARQUET.
Python: minimal Parquet export
import duckdb, pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/tips.csv')
with duckdb.connect() as con:
con.execute("COPY (SELECT * FROM df) TO 'exports/tips.parquet' (FORMAT PARQUET)")
Offline sample data
See source/duckdb/data/README.md to create tiny local CSV/Parquet samples (e.g., tips.csv, tips.parquet).
Then swap the path in examples to point at those local files.
Quick troubleshooting
- Exports fail: create the target folder, e.g.,
exports/. - SSL on macOS: either run the Python certificate installer or use DuckDB
httpfsto fetch data. - Too slow? Try:
PRAGMA threads = 8; PRAGMA memory_limit = '2GB';and prefer Parquet over CSV.
You can use the arrows ← → on your keyboard to navigate between lessons.
Comments
Comments
Did you find this content useful?
If you found this high quality content useful please consider supporting my work, so I can continue to create more content for you.
I give away all my content for free: Weekly video content on YouTube, 3d Printable designs, Programs and Code, Reviews and Project write-ups, but 98% of visitors don't give back, they simply read/watch, download and go. If everyone who reads or watches my content, who likes it, helps fund it just a little, my future would be more secure for years to come. A price of a cup of coffee is all I ask.
There are a couple of ways you can support my work financially:
If you can't afford to provide any financial support, you can also help me grow my influence by doing the following:
Thank you again for your support and helping me grow my hobby into a business I can sustain.
- Kevin McAleer