Join files

Load as many files and URLs as you like, in any mix of formats, and join them in a single SQL query. No import step, no schema to write.

Join two files

Give each file a table name with -f name=path:

sqx -f users=data/users.csv -f events=data/events.jsonl "
  SELECT u.name, COUNT(e.id) AS events, ROUND(SUM(e.amount), 2) AS revenue
  FROM users u LEFT JOIN events e ON e.user_id = u.id
  GROUP BY u.id ORDER BY revenue DESC"

Here a CSV file is joined to a JSON Lines file. sqx does not care what format each side is in.

Mix stdin with files

Piped input and -f tables live side by side. Use -t to give stdin a meaningful name:

cat data/events.jsonl | sqx -t events -f users=data/users.csv "
  SELECT u.country, json_extract(e.meta, '$.device') AS device, COUNT(*) AS n
  FROM events e JOIN users u ON u.id = e.user_id
  GROUP BY 1, 2"

Join a remote file

A URL works anywhere a path does. It is fetched once and cached:

sqx -f orders=orders.csv -f countries=https://example.com/countries.csv "
  SELECT c.name, COUNT(*) FROM orders o JOIN countries c ON c.code = o.country GROUP BY c.name"

Chain sqx with pipes

When its output is piped, sqx writes CSV, which the next sqx reads automatically. That lets you build a query in stages:

sqx -f u=data/users.csv -o csv "SELECT * FROM u WHERE plan != 'free'" \
  | sqx "SELECT country, COUNT(*) FROM stdin GROUP BY country"

Join with a saved database

Files loaded with -f can be joined with tables in a SQLite file opened with -db. If a loaded table has the same name as a saved one, write main.<name> to reach the saved table:

sqx -db project.db -f incoming=data/users.csv "
  SELECT i.id, i.plan AS new_plan, u.plan AS old_plan
  FROM incoming i JOIN main.users u ON u.id = i.id
  WHERE i.plan != u.plan"

See Save to a SQLite database.