Reading Files With SQL

Survey has two ways to get at your data, and it's worth knowing which one you're using.

The one I reach for is + Add data. Choose a file, a database, or a link, and Survey reads it once and creates a named table. From there you query the table by name, and Profile, Report and Relations all work, because there's a real table for them to measure.

SELECT * FROM orders;

The other is to read a remote source directly. The read_ functions point SQL straight at a file over the network and query it in place, without creating a table.

SELECT * FROM read_json('https://example.com/data.json');

Both are legitimate. Add data is the workspace: it fetches once, keeps the table, and everything that measures your data has something to measure. The readers are the escape hatch for when you just want to look at something remote without keeping it, and they fetch every time they run rather than once. For a file on your own machine, reach for + Add data first. A page running in your browser can't open a path off your disk on its own, so the readers below are about URLs, not local paths.

The readers

There's one function per format, and they all behave the same way. Hand them a URL, get back something you can select from. read_csv handles anything delimited, and it'll sniff the separator, work out whether the first line is a header, and pick a type for every column. You can overrule any of that.

SELECT * FROM read_csv('https://example.com/export.txt', delim=';', header=false);

read_json reads either an array of objects or one object per line, and it doesn't care which, because both arrive as rows. A single object at the root arrives as one row. Nested objects become struct columns and arrays become list columns, which matters in a minute. read_xlsx reads a spreadsheet, first sheet by default, sheet='Name' for another.

Then there's read_parquet. Nothing is inferred, because Parquet files carry their own schema, so this is the format to reach for when you have a choice. It's also the one that made me change engines. The old runtime couldn't read it at all, and no amount of work on my side was going to fix that.

Nested data

If your JSON has arrays in it then you've got list columns, and a list column isn't much use until you flatten it. Nested structures survive the read intact, which is the part that makes JSON worth querying directly rather than pre-processing somewhere else first.

SELECT id, UNNEST(tags) AS tag FROM read_json('https://example.com/posts.json');

One row per element. This is also, more or less, how you query an API response. Fetch the JSON, read it, unnest the part you care about, and you're querying a web service with a SELECT.

Reading over the network, and why it sometimes won't

Every reader takes a URL, and here's the first place it'll surprise you.

SELECT * FROM read_csv('https://example.com/data.csv');

That request goes from your browser directly to that site. There's no server of mine in the middle, which is rather the point, and it also means the site has to allow requests from other origins. Plenty don't. When one doesn't, your browser blocks the request before it starts, and no amount of cleverness on my end can work around it, because working around it would mean putting a server in the middle and reading your data on the way past.

So a remote read either works immediately or it doesn't work at all. Survey tells you which, and names the likely reason rather than handing you the engine's own message about failing to open a file.

One more thing worth holding onto: a direct read is a fetch on every run. Honestly, if you're going to come back to the same remote file, + Add data → From a link fetches it once into a table instead, which is kinder to the site and faster for you.

Statistics in one statement

This isn't a reader, but it belongs here anyway. It's the fastest way to find out what you're actually holding, and I reach for it before writing anything else.

SUMMARIZE SELECT * FROM sales;

Minimum, maximum, distinct count, average, standard deviation, quartiles and null percentage, for every column, in one go. Survey runs a fuller version of this for you after every query, though it's worth knowing the statement exists on its own.

A second database

The primary way to bring a database file in is + Add data → Database file: Survey lists its tables and you pick which to import, as named tables in the workspace. If you'd rather do it by hand, ATTACH opens another DuckDB file alongside whatever you've already loaded and lets you query across both. Attaching read only is worth doing by habit, since it means nothing you type can write back to the file.

ATTACH 'https://example.com/archive.duckdb' AS archive (READ_ONLY);
SELECT * FROM archive.orders;

Useful when last year lives in one file and this year lives in another and you'd like them joined.

The two places it'll surprise you

Types are guessed, and guessing has consequences. A column of postcodes that all start with a zero looks exactly like a column of numbers, and read as numbers, 05561 becomes 5561. Survey watches for that specific shape and keeps the column as text, telling you it did. The general problem doesn't go away though, which is why the type override exists.

SELECT * FROM read_csv('https://example.com/customers.csv', types={'postcode': 'VARCHAR'});

The other one is that whole-file sniffing costs something. By default Survey reads the entire file before deciding on types, rather than sampling the first few thousand rows. Sampling is faster and it's wrong in a way that's genuinely hard to notice. A column that's numeric for twenty thousand rows and then contains the word "unknown" gets typed as numeric, and every row after that quietly becomes null. I'd rather the read took a moment longer.

Why any of this exists

The other three tools in SQLTerrain reason about SQL. They read your query and tell you what it says, what it'll do, and, more usefully, what they can't prove about it. Whether a column is really unique. Whether a join will multiply rows. Whether those nulls matter.

Survey is the one that can answer. It's got your actual data, so it stops guessing and starts measuring, and then it hands the answer back to the tool that asked. That's the whole reason it reads files at all.