sqx

Quick start

Three queries that show what sqx does: query a CSV, join two files and audit live URLs. It takes about five minutes.

Before you start

Install sqx, then get the sample files by cloning the repository and running the examples from its root:

git clone https://github.com/sqx-go/sqx-go.github.io.git sqx
cd sqx

Don't want to clone? Point sqx at any CSV or JSON file you already have. Every example works the same way.

1. Query a CSV piped on stdin

Whatever you pipe into sqx becomes a table called stdin:

cat data/users.csv | sqx "SELECT country, COUNT(*) AS customers FROM stdin GROUP BY country"
+---------+-----------+
| country | customers |
+---------+-----------+
| CN      | 1         |
| IN      | 2         |
| MX      | 1         |
| TR      | 1         |
| US      | 1         |
+---------+-----------+

You did not tell sqx that the input was CSV, or which columns were numbers. It worked that out.

2. Join a CSV with a JSON Lines file

Use -f name=path to load each file as a named table, then join them like any other tables:

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"
+-------------+--------+---------+
| name        | events | revenue |
+-------------+--------+---------+
| Diana Lopez | 1      | 310     |
| Chen Wei    | 2      | 100.5   |
| Asha Rao    | 2      | 49.99   |
| Farah Khan  | 2      | 15.25   |
| Ben Carter  | 1      | 0       |
| Elif Kaya   | 0      |         |
+-------------+--------+---------+

3. Audit live URLs

Use -i lines to read one URL per line. Each URL is fetched once, cached on disk, and shared by every function in the query:

cat data/urls.txt | sqx -i lines "
  SELECT url, http_status(url) AS status, http_final_url(url) AS final_url, html_title(url) AS title
  FROM stdin"

Run it a second time and it returns instantly, because the pages come from the cache.

Bonus: change the output

In a terminal sqx prints a table. Piped to another program it prints CSV. Pick any format with -o:

sqx -f u=data/users.csv -o json "SELECT id, name, plan FROM u LIMIT 2"
[
  {
    "id": 1,
    "name": "Asha Rao",
    "plan": "pro"
  },
  {
    "id": 2,
    "name": "Ben Carter",
    "plan": "free"
  }
]

Next steps