Notes on first steps with duckdb

duckdb_notes.txt

brew install duckdb

duckdb -c "INSTALL httpfs"

REMOTE_FILE="https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv"

# read a remote CSV file in to a duckdb database into an in-memory database
duckdb -c "SELECT * FROM '${REMOTE_FILE}';" 

# By default, the CLI will open a temporary in-memory database. 
# To open or create a persistent database, simply include a path as a command line
# argument like duckdb path/to/my_database.duckdb
# https://duckdb.org/docs/api/cli.html
duckdb -c "CREATE TABLE penguins AS SELECT * FROM read_csv_auto('${REMOTE_FILE}');" \
  penguins.duckdb 

# see https://duckdb.org/docs/data/csv/overview.html#parameters
# for configurable options

# non-interactive queries
duckdb -c "SELECT * from penguins WHERE sex = 'MALE';" penguins.duckdb

# interactive exploration of the penguins.db database
duckdb penguins.duckdb

SHOW TABLES;
SELECT * FROM penguins WHERE SEX = 'MALE' LIMIT 5;
.mode json
SELECT * FROM penguins WHERE SEX = 'MALE' LIMIT 5;

# install tad to view CSV, Parquet, and SQLite and DuckDb database files
# https://duckdb.org/docs/guides/data_viewers/tad
https://www.tadviewer.com/

# create symbolic link to launch tad from the command line
sudo ln -s /Applications/Tad.app/Contents/Resources/tad.sh /usr/local/bin/tad

tad penguins.duckdb