Using the Raspberry Pi Pico's Built-in Temperature Sensor
Learn
0h 23m
22 Nov 24
Kevin McAleer
KevsRobots Learning Platform
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