read_csv_auto reads CSV data directly from a supplied filename or URI and exposes it as a queryable relation. Use it when the source is CSV but its format cannot be inferred from a .csv extension, such as a shortened remote URL. section 2.5 section 5.2
Read a shortened remote CSV URL
SELECT count(*) FROM read_csv_auto('https://bit.ly/3KoiZR0'); The query treats the extensionless URL as CSV and returns the same 227-row population dataset available through its canonical .csv URL.
During automatic parsing, DuckDB samples CSV rows to infer the delimiter, quoting and escaping rules, column types, and header presence. The default sample is 20,480 rows. section 5.2.1
Use the inferred relation like any other SQL source:
- Inspect inferred columns with DESCRIBE or query them with SELECT. section 5.2
- Read multiple files by passing a list of URLs or using a glob; DuckDB combines their schemas. section 5.2 section 8.3.5
- Switch to read_csv when specific column types or parsing options must override inference. section 5.2 section 5.2.1
Ingest remote readings into a table
INSERT INTO readings(system_id, read_on, power) SELECT SiteId, "Date-Time", CASE WHEN ac_power < 0 OR ac_power IS NULL THEN 0 ELSE ac_power END FROM read_csv_auto('https://developer.nrel.gov/api/pvdaq/v3/data_file?...'); This pattern combines remote CSV ingestion with immediate cleaning and insertion into a relational table. section 3.4.1
Listing 8.12 Computing a list of CSV files as sources for the pipeline
version: 2
sources:
- name: github
meta:
external_location: >
(FROM read_csv_auto(
list_transform(
range(1968, 2023), #1
y -> 'https://raw.githubusercontent.com/JeffSackmann/
tennis_atp/master/atp_matches_' || y || '.csv' #2
),
types={'winner_seed': 'VARCHAR', 'loser_seed': 'VARCHAR'}
))
formatter: oldstyle #3
tables:
- name: matches_file
Browse a pipeline source that generates CSV URLs for many years, supplies explicit types for problematic fields, and feeds read_csv_auto. section 8.3.5
