Using DataKing

Getting data in

Drop a file anywhere on the window. CSV, TSV, Excel (.xlsx), JSON, JSON lines and Parquet all work, and so do those files zipped or gzipped.

• Every row is read to work out each column's type, so a surprise on row 900,000 can't sink the load.
• On a large file, a few stray values won't turn a whole column into text. They become empty, and you're told which ones and on which row.
• Column names become snake_case, so Order ID becomes order_id and you never type a quote. Words SQL reserves get a trailing underscore, like order_.
• Values that look like identifiers stay as text. 00501 keeps its zeros.
• From an Excel workbook, the first sheet is loaded, and you're told if there are others.

Use Add data for the other ways in: a link to a file that opens without signing in (a published Google Sheet works), or tables copied from Postgres or MySQL. DataKing only ever reads from your database, but use a read-only user if you have one. Your database has to allow connections from outside. Connection details are encrypted before they're stored and never shown again.

To add this month's export to an existing table, open the table and choose Add rows from a file. Columns are matched by name.

Writing queries

The SQL is close to PostgreSQL. Ctrl+Enter runs a block, or just the part you've selected, and Ctrl+K finds anything. Column and table names aren't case sensitive. Times without an offset are taken as UTC, and that's the zone now() uses.

select date_trunc('month', ordered_at) as month, sum(amount) as revenue
from orders
where status = 'completed'
group by 1
order by 1

You can change data too: CREATE TABLE ... AS SELECT, INSERT, UPDATE, DELETE and DROP TABLE. Every change can be undone from the table's history.

An UPDATE or DELETE rewrites the whole table as a new version, which is fine for occasional changes. To add rows often, use INSERT.

If your workspace has the assistant on, you can also ask in plain English. You get real SQL you can read and change. Claude sees your table and column names, never your rows. Reads run straight away, and anything that changes data waits for you to press Run.

Pages

A page is SQL blocks, charts and text. It's a worksheet while you work, and Present turns it into a dashboard by hiding the SQL.

Blocks build on each other

Each block has a name, and any other block on the page can read from it like a table. Change one and everything built on it follows. There's no run order to get wrong and no hidden state.

-- in a block named monthly_revenue
select date_trunc('month', ordered_at) as month, sum(amount) as revenue
from orders group by 1

-- in another block
select * from monthly_revenue order by revenue desc limit 5

Charts

DataKing draws a chart when the shape of the answer makes it obvious, such as a date and a number. Switch between views under any result. Long results show their first 20,000 rows, and the download button gets all of them.

Parameters

Write {{channel}} in your SQL and an input appears at the top of the page. Viewers of a shared page can change it, and it's always treated as a value, never as SQL.

Tables and history

Every change makes a new version, and each table keeps them for 30 days. Open a table to look at any version or go back to it. Going back is itself a new version, so it can be undone too.

select count(*) from "orders@3"   -- read version 3 of orders

A clone (create table b clone a) shares the same files, so it's instant and takes no extra space. A dropped table can be brought back for 30 days from Dropped tables in the account menu.

Keeping things fresh

Choose Save as a table on any block, or use CREATE TABLE ... AS SELECT, and that table can refresh hourly, daily, weekly, or whenever its sources change. That last one carries down the chain by itself, so there's no graph of jobs to maintain. Connected tables refresh on a schedule too.

If a refresh fails, the table keeps its last good data and shows what went wrong.

Moving from Snowflake or Databricks

Use Add data, then From Snowflake or From Databricks, and pick your tables. Snowflake needs your account name and a programmatic access token. Databricks needs your workspace address, a personal access token and a SQL warehouse ID. Their compute runs the reads, so their usual charges apply while copying.

Most of your SQL will run as it is. Uppercase names find their snake_case columns, and these are understood:

IFF IF ZEROIFNULL NULLIFZERO DIV0 EQUAL_NULL DATEADD DATEDIFF DATE_ADD DATE_SUB ADD_MONTHS LAST_DAY YEAR QUARTER MONTH WEEK DAY HOUR MINUTE SECOND GETDATE SYSDATE TO_VARCHAR TO_NUMBER TRY_TO_NUMBER TRY_TO_DATE TRY_TO_TIMESTAMP TO_BOOLEAN LEN CHARINDEX CONTAINS STARTSWITH ENDSWITH LISTAGG COUNT_IF APPROX_COUNT_DISTINCT COLLECT_LIST COLLECT_SET ANY_VALUE ARRAY_SIZE QUALIFY ILIKE ::

Not supported: FLATTEN, MERGE, stored procedures, Python, and notebooks. If something doesn't run, the error will say what to change.

People and sharing

There are three roles. Owners manage people, editors can change data, and viewers can read and run any SELECT. A viewer can change the SQL on a page to explore, and those changes aren't saved.

Share on a page makes a link anyone can open without signing in. They see the answers, not your SQL or your tables, and you can turn the link off at any time.

API

Make a token under API access in the account menu. A token can do whatever you can do in that workspace.

curl https://harrowsoft.com/dataking/api/query \
  -H "Authorization: Bearer dk_your_token" \
  -H "Content-Type: application/json" \
  -d '{"sql": "select count(*) from orders"}'

curl "https://harrowsoft.com/dataking/api/upload?filename=sales.csv&table=sales&mode=append" \
  -H "Authorization: Bearer dk_your_token" \
  --data-binary @sales.csv

For uploads, mode is create (the default), replace or append. The file goes in the request body, and filename says what kind of file it is. Use --data-binary, because curl's -d removes line breaks.

Limits

These keep one query from getting in anyone else's way, and they're the same however much you use it.

Hit one and the error will say which, and what to try instead. As a guide, 20 million rows total into 100,000 groups in about half a second.