Guides

Bring data in

You have data in a file, a spreadsheet, a Google Sheet, or a warehouse. With this guide, you get it onto a dashboard, whichever way it comes, and you keep it fresh.

Each way ends with a datasource: a named set of rows saved into the dashboard. Charts and tables read those saved rows. Viewers read them too, and never touch your file, your sheet, or your accounts.

This guide describes arkush.app. An org install offers more paths, such as a BigQuery query that runs as a service account. Those sections carry the note Org installs only.

Make a dashboard

Data goes onto a dashboard that you can edit. Any signed-in person can make a new one. Use a desktop or tablet window: a window the size of a phone shows no New dashboard button.

1. Open the Dashboards page. When you open arkush without a link to a dashboard, you start there. From a dashboard, select arkush at the top left of the header.
2. Select New dashboard.

An empty dashboard opens with the mode switch in the header on Edit. The canvas shows the Start your dashboard walk: one step for each way to add data that you can take now, such as Import a file and Connect BigQuery. When you are signed in, the dashboard saves itself to your account after your first change.

Drop a file on the canvas

This path is the fastest. arkush reads CSV, JSON, and Parquet files, Excel workbooks saved as .xlsx, and a CSV or JSON file compressed with gzip (.csv.gz, .json.gz). A CSV file that Excel on Windows saved imports too: arkush reads text that is not UTF-8 as Windows-1252. An older Excel workbook (.xls) or an OpenDocument spreadsheet (.ods) does not import: open it in Excel or LibreOffice, save it as .xlsx or CSV, then choose the saved file.

Every file that you bring in is stored. While you work on the import, the file stays in this browser. When you select Create datasource with a signed-in account, the file is saved to your files. Without an account, the file stays in this browser until you sign in and sync. The datasource is a SQL query over the file, so you can change the query later, and you can make more datasources from the same file.

The first row of a CSV file is always the header. A row with every cell empty is skipped. In a column whose other values are all numbers or dates, a cell that reads NA, N/A, null, - or a spreadsheet error such as #N/A counts as missing. In a text column, the same word stays as written.

1. Open your dashboard, and set the mode switch in the header to Edit. If you have no dashboard yet, see Make a dashboard.
2. Drop the file anywhere on the canvas. The Data workspace opens with the file read. If the canvas cannot take the file, a notice says why and what to do first.
3. Look at the file in the Data workspace. One tile per column shows its values, how many are missing, and the type the column is read as. Change a type with the picker on its tile. If some rows of a CSV file have more or fewer fields than the header, a note counts those rows and names the first one. To keep every value, correct those rows in the file and choose the file again.
4. Select Create datasource.

The file becomes a datasource, and a table bound to it appears where you dropped the file. A notice states the row count. On an empty dashboard, the same import is the first step of the Start your dashboard walk: Import a file opens the file picker. After the dashboard has data, the walk shows Add more data, which opens the same picker.

To get a chart from the file instead of a table, start with New chart. The same import step runs, and a chart of the file opens in the Chart workspace. Read Make one chart.

An Excel workbook imports the same way, and the workbook is stored as it is. A workbook with several sheets also shows a Sheet picker. Pick the sheet before you select Create datasource. To use a second sheet, make a second datasource from the same file and pick the other sheet.

When the file has more than 500,000 rows

The Data workspace opens with the file already read and the true row count shown. The size meter at the start of the facts line shows four red squares. A datasource holds at most 500,000 rows, so you get a SQL query over the file. The result of the query becomes the datasource. Until you run the query for the first time, a line under the preview has the next steps.

1. Keep the starter query to take the first 500,000 rows. To summarize all the rows instead, pick a starter from Insert a transform. Its starters use your own columns: group and count, count by month, top 10, and more.
2. Select Run query.
3. Make sure that the preview has the rows you want.
4. Select Create datasource.

Paste rows from a spreadsheet

You can paste copied cells. You do not need a file: the pasted rows are stored as a file named pasted_data_ and the date.

1. Copy the rows in Excel or Google Sheets.
2. Select the Data workspace icon in the icon rail at the right edge of the screen. The Data workspace opens, with the list of datasources at the left.
3. Select New datasource. The editor opens under the data.
4. Select Import as the source.
5. Paste into the Or paste your data here. box.
6. Read the facts line: a size meter, the row count, the columns, and how the app read the text.
7. Type a name in Name, or keep the name of the pasted file.
8. Select Create datasource.

Each column has a tile, with the type the app read in a picker. If the app read a column as the wrong type, for example a date as text, pick the correct type on its tile. The tile counts the values that do not convert to the type you pick.

To show the new datasource, select Chart in the edit-mode header, or add a table from the Add menu beside it.

In the list of datasources, each row shows a glyph for the kind of datasource, a size meter for its rows, and the age of its data. The meter has four squares. More squares mean more rows. Three amber squares mean that a chart of all the rows draws slowly, and four red squares mean that the rows are near the limit of 500,000. Point at the meter to read the row count. Point at the age to read who refreshed or imported the data, and when. In Fields, select the (i) in the Missing, shape header to learn how to filter the rows.

Connect a Google Sheet or a file at an address

Data at an address becomes a datasource: a Google Sheet, or a CSV, JSON, or Parquet file that Google hosts. On arkush.app, your browser reads it, and the server reads nothing at an address. A file on another host does not read from its address: download the file and bring it in with Import. In the Sheets/URL source, you see what the server reads.

1. Open the Data workspace and select New datasource.
2. Select Sheets/URL as the source.
3. Paste the address. For a Google Sheet, copy the link while the tab you want is open, because the link names the tab. The field shows what the app recognized: the source, the sheet tab, and the table name your SQL reads.
4. For a Google Sheet, check Read with. On arkush.app, it reads Your browser. See What each address needs for the read that fits your sheet.
5. For a Google Sheet, select Look at the sheet. Your browser reads the sheet, and the Data workspace shows its columns and rows. Nothing is saved yet.
6. Write the SQL. The SQL field starts as SELECT * FROM sheet, which keeps every row and column. After you look at the sheet, select Run on the sheet to see the result.
7. Select Create datasource.

The new datasource appears in the list of datasources, with its rows. The canvas shows no chart yet. To show the rows, select Chart in the edit-mode header, or add a table from the Add menu beside it.

What each address needs

In Read with, you choose who reads a Google Sheet. On arkush.app, it shows Your browser and offers no choice, because the server reads nothing at an address.

Google Sheets has two ways to open a sheet to others, and each one gets a different read. Sharing by link is Share → General access → Anyone with the link. Publishing is File → Share → Publish to web.

• A Google Sheet shared as “anyone with the link” reads in your browser. Keep Read with on your browser. No Google window opens.
• A Google Sheet that is not shared by link reads in your browser with your own Google account. Keep Read with on your browser. A sheet that only you can see works. This Google connection is separate from your sign-in to arkush, so it works when you sign in with GitHub too: it lets the app read the sheet. The first read opens a Google window, where you sign in to Google if Google asks. The window then shows that sheet. Select the sheet once, and the app gets access to that one file only. Under Read with, a row names the Google account that reads the sheet, with Disconnect. With no account connected, the row shows a broken-link glyph and Connect Google account. A line under the row says what the connection lets the app read, and what reaches the server.
• A sheet that your browser reads refreshes only when a person selects Refresh. The schedule never runs it.
• A file at an address that Google does not host does not read on arkush.app. Download the file and bring it in with Import.

The server reads an address

Org installs only

An org install can let its server read addresses, from the hosts that the people who run it name. There, Read with also offers This server's Google account, the Google account that the server itself uses.

• A published CSV or a published sheet needs no credential. Set Read with to This server's Google account for a sheet. The server gets the file, and gets it again on the refresh schedule. Any editor can use this path.
• A host that the server does not allow makes the run fail. The message names what works instead: read the sheet in your browser, or download the file and bring it in with Import.
• A server that reads no addresses says so in the Sheets/URL source. Some of these servers also read no private sheet. There, the source says so and offers no Google connection. Share the sheet by link, or download a copy and bring it in with Import.

A private Google Sheet on the schedule

Org installs only

On an org install, the server can read a private sheet with its own Google account, so the schedule keeps the sheet fresh. Only a person with the analyst right can set up this read. The people who run the org install give that right to named people. The app shows no list of your rights. Without the right, Read with starts on your browser, and a server read of a private sheet fails with a message that names what works instead.

1. Share the sheet with the server's Google account. The app does not show that account's address: get it from the people who run the org install.
2. Set Read with to This server's Google account. The server then reads the sheet on the schedule.

Query BigQuery with your own account

Write SQL against the warehouse and save the result. The query runs as you, with your own Google account, in your browser, and bills your own Google Cloud project. This path needs a signed-in account. The Google connection is separate from your sign-in to arkush, so you can sign in with GitHub and still query BigQuery with a Google account. On an empty dashboard, Connect BigQuery in the Start your dashboard walk opens this path.

1. Open the Data workspace and select New datasource.
2. Select BigQuery as the source.
3. Write the SQL, or select a table in the table browser for a starter query.
4. Select Run in BigQuery. You do not need to connect first. The first run asks you to sign in with Google, and asks for read-only access to BigQuery only.
5. Make sure that the preview has the rows you want.
6. Select Create datasource.

The connection row above the SQL shows which Google account the queries run as, with Disconnect. With no account connected, the row shows a broken-link glyph and Connect Google account. A line under the row says what the connection lets the app read. If the server is not set up for Google sign-in, the BigQuery connection heading shows the broken-link glyph and an (i) that says who fixes the setup. Each query bills to a billing project that you choose in the Billing project field of the BigQuery connection group, at the top of the BigQuery form. On arkush.app, the group opens and asks you to choose one before the first run. On an org install, the group can start on a default project that the org install sets, and you can pick another.

If your Google account has no Google Cloud project, the list is empty. Create a project in the Google Cloud console, then connect again. Google bills the project for each query, except within the free limits of Google's BigQuery sandbox. Public datasets, such as bigquery-public-data, read under any project you bill. If a run says your account can't start a query in the project, pick a project where your account can run queries: one you own, or one where you hold the BigQuery Job User role.

A query under your own Google account refreshes only when a person selects Refresh. No schedule runs it, and an agent works from the rows of its last refresh.

Query BigQuery as a service account

Org installs only

On an org install, a query that the schedule or an agent refreshes runs on the server, as a service account. Each org install registers its own service accounts, and some register none. To use an account, you need the Service Account User role on that account in Google Cloud. The people who manage that Google Cloud project grant the role there. An account that runs from a key file takes no Google role: an admin of the org install puts you on the account's list in the app instead. With the role or a place on the list, the Runs as field of the BigQuery source offers the account on a dashboard saved to the server. Without it, the field is greyed, and the note under it says why.

1. Save the dashboard to the server first. On a dashboard that lives only in this browser, Runs as offers no account, because the server runs only a dashboard it holds.
2. Open the Data workspace and select New datasource.
3. Select BigQuery as the source. Runs as already names an account.
4. Write the SQL, or select a table in Browse tables for a starter query. The browser lists the tables that the account can read.
5. Select Create datasource. The server saves the query, runs it as the account, and saves the rows as the datasource's data.

The server runs only a saved query. To change the query, edit it and select Save and run. To run a query with your own Google account instead, set Runs as to Your own Google account. The query then runs in your browser, and the schedule never runs it.

If you hold the role on no registered account, the field does not show, and the query runs as you. The Findings panel names each scheduled datasource that runs as a person. The link on the finding opens that datasource at its Runs as field.

A datasource runs as an account you cannot use

A service account is a Google account for a program, which an org install registers so that its server runs queries. A dashboard that came from an org install, for example as an imported file, can hold a datasource that names a service account. That account does not run for you. Runs as shows the account, a note that says why it does not run for you, and one other choice: Your own Google account.

1. In the Data workspace, select the datasource, then select Edit beside its name.
2. In Runs as, select Your own Google account.
3. Select Run in BigQuery. The query runs in your browser.
4. Select Save snapshot to keep the rows.

Refresh on the datasource names the same step.

Query a database

Org installs only

A query can run on a PostgreSQL database that your arkush server connects to. This path depends on your arkush server: the people who run it register each database, and most servers register none. The query runs on the server as a service account, so you need the Service Account User role on an account that the database admits. Read Query BigQuery as a service account for how the role is granted.

1. Open the Data workspace and select New datasource.
2. Select Database as the source. The source shows only on a server that registered a database.
3. Select the database in Database.
4. Select the account in Runs as.
5. Write the SQL in PostgreSQL. The editor does not check it as you type, and it has no table browser.
6. Select Create datasource. The server saves the query, runs it on the database as the account, and saves the rows as the datasource's data.

The query runs read-only, and a result over 500,000 rows is refused. Aggregate or filter in the SQL. Query parameters do not apply here: write the values into the SQL. The schedule can refresh the datasource.

If the run refuses, the message names the step. When the database refuses the account, the people who run your arkush server make the account a user of the database. When a table is refused, tell the people who run your arkush server. They ask the owner of the database to give the account read access to the table.

Write SQL that runs in your browser

In three editors, your SQL runs in DuckDB in your browser: a Transform datasource, the query over an imported file, and the query over a Google Sheet or a file at an address. DuckDB SQL is close to the SQL of BigQuery and PostgreSQL. Some functions have other names, for example date_trunc('month', day) and strftime(day, '%Y-%m').

In the query over an imported file, a date or time column reads as text. To use a date function on it, cast the column first, for example date_trunc('month', InvoiceDate::TIMESTAMP).

1. Write the query in the editor.
2. Read the line under the editor. Arkush checks the query each time you stop typing. The line reads ✓ The query has no errors, or it has the first error with its cause. The editor also underlines the part of the query with the error.
3. Press ⌘ Enter on a Mac, or Ctrl Enter on another computer, to run the query. You can also select the run button next to the editor.
4. Make sure that the preview has the rows you want.

For every function and every statement, read the SQL reference of DuckDB. For functions by type, read the function overview.

Store a file for reuse

Every file you import with a signed-in account goes to your files. You can also put a file there without a dashboard. Many dashboards can read a stored file, and a replacement reaches all of them. Only a signed-in account has the store, and it holds CSV, JSON, Parquet and .xlsx files.

• Upload from the Datasources page. Select arkush at the top left of the header. Then select the Datasources tab. The Uploaded files section is at the end of the page. Select Upload file, and pick the file. Its columns and rows show before anything is stored, under a line of facts: a meter of the file size against the 512 MB limit, a dim upload glyph while the file is not uploaded, the rows × the columns, and the format. Point at a glyph or at the meter to read it in words. Check the columns and rows, then select Upload.
• Import it on a dashboard. When you select Create datasource, the file is saved to your files, whatever its size.

To make a datasource that reads a stored file, such as a second datasource from a file:

1. Open the Data workspace and select New datasource.
2. Select Import as the source.
3. In Or use an uploaded file, pick the file. Its columns and rows show. Without an account, pick it in Or use a file in this browser.
4. Keep the starter SQL, or write SQL that reduces the file.
5. Select Create datasource.

A datasource built on a stored file has its own SQL. A refresh runs that SQL again, on the server, over the current bytes of the file. Without an account, the refresh runs in this browser, over the copy in this browser. When you open the datasource, you see its file and its query in the editor. When someone replaces the file with Replace file on the file's own page, every datasource that reads it carries a warning sign until its next refresh. Its tooltip reads File changed.

A file is private to you. On arkush.app it stays private, because only the people who run arkush.app add shares. On an org install, you share it from its page. A refresh needs access to the file itself as well as to the dashboard. The server sets a space limit per person. The section shows your usage, and at the limit an upload fails until you delete a file.

Keep the data fresh

Charts read saved rows. New numbers arrive only when a datasource refreshes. Opening the dashboard runs no query. You refresh in the Data workspace. To open it, select the Data workspace icon in the icon rail at the right edge of the screen. Or select the freshness chip in the header, then Open the Data workspace.

• One datasource: select Refresh, the circular-arrows button beside its name in the list at the left of the Data workspace. The stored query runs again, and the new rows replace the old rows. A notice then reads the number of rows that arrived, or why the run failed.
• Every datasource at once: when more than one datasource has a query, Refresh all is at the top of the list. A datasource over a file counts, because its query runs again over the file. In edit mode, Refresh data in the dashboard's top bar runs the same refresh. It runs each datasource in turn. A failure does not stop the others. One notice at the end has the number that refreshed, and the first three datasources that failed, each with the reason. If more failed, the notice has a count of the rest. Each error is then on its row in the list of the Data workspace.
• What a BigQuery refresh reads: as an editor, read the estimate before you select Refresh. The estimate is on the second line of the datasource's row, after the size meter: a download glyph and a size, for example 1.1 GB. The size meter is the row of small squares, and it measures the saved rows, not the scan. The line under Refresh all has the total of the estimates. A datasource over a file reads nothing from BigQuery, so it adds nothing to the total. BigQuery makes each estimate with a dry run, which reads no data and costs nothing. When a scan is larger than the server's limit, you see a warning mark with the limit and the fix, and the server refuses that refresh. A BigQuery datasource has no estimate in these cases:
  • It runs as you, and this browser holds no Google sign-in. Select Connect Google account at the top of the list, and the estimate follows.
  • It runs as a service account that you cannot use, or the dashboard is not saved to the server. The server makes the dry run as that account. See A datasource runs as an account you cannot use.
  • BigQuery refuses the query. Open the datasource and fix the SQL.
• A file you brought in: Refresh runs the datasource's query again over the stored file. The rows change only when the stored file changed. To load an updated copy of the file:
  1. In the list, select the datasource, then select Edit beside its name. The editor opens on the file, with the datasource's query.
  2. In the line at the top of the editor, select Change.
  3. Select Choose a file and pick the new copy, or drop the file on the box.
  4. If the new copy has the same file name, the query stays. Select Run query to see its result. If the name is different, the query goes, and the rows read as they are in the file.
  5. Select Replace data.
  When the new rows are saved, Not saved yet goes from the facts line, and the datasource's row in the list reads just now. The saved rows are already the new ones, so no Refresh is necessary. The column types that you set at the import stay only when the query stays, because the query holds them. When the query goes, set the types again with the picker on each tile before Replace data. The labels, descriptions and formats of the fields, and the description of the datasource, stay in both cases.
• Replace data or Replace file: with Replace data, you keep the new copy as a new file, and you change only this datasource. With a signed-in account, the new file goes to your files. Other datasources that read the old file still read the old file. To change the file for every dashboard that reads it, open the file from Uploaded files on the Datasources page. On the file's page, select Replace file… in its actions menu. Each datasource that reads the file then has the warning sign File changed until someone selects Refresh on it. Use Replace data for one dashboard, and Replace file when several dashboards read one file.
• Imported rows with no Refresh: a datasource with no Refresh button holds imported rows with no query. Arkush made such datasources before every import became a file. Replace data makes it a query over a file, and it then has Refresh.
• On a schedule: a schedule refreshes a datasource that the server runs, then the derived views that read it. On arkush.app, the server runs no query, no sheet and no file at an address, so a schedule re-runs only derived views over the rows that are already saved. New rows from BigQuery, a sheet or a file arrive when you select Refresh. On an org install, a query as a service account and a sheet or a file that the server reads run on the schedule too. Where a dashboard has such a datasource, select Schedule… in the Scheduled refresh section of the Data workspace. Pick a cadence: Daily, Weekdays, Mondays or Monthly, and set the time of day in At. For any other schedule, choose Custom and type a cron expression: five values for the minute, the hour, the day of the month, the month and the day of the week. 0 */6 * * * runs every six hours. In Time zone, type part of a city or region name and pick the zone from the list. Each zone has its offset from UTC. Leave the field empty to use UTC. You see the next run before you select Save schedule. The schedule repeats only a query that a person, or an agent that the person connected, already ran on the server. A query that never ran, a query you changed, and data that did not come from a server run are paused, and the section names each one with its cause. Run it once, and the schedule continues from there. A query under your own Google sign-in runs only in your browser, so the schedule never runs it. If a scheduled refresh stops, the owner and the person it runs as get an entry in their inbox, the bell icon in the header, that names the datasource. The Scheduled refresh section also names each stopped datasource. Where the server recorded why, an editor reads the reason there. Run the datasource once to resume its schedule.
• When a scheduled run fails: the schedule tries the datasource again after 5, 10, 20, 40 and 80 minutes. When the last try fails, the schedule stops retrying it, the section states that, and the owner's inbox entry comes back unread. Fix the cause, then run the datasource once, and the schedule starts again. To see what the schedule did, open Last runs at the end of the section: each run has the time it ran and what it saved or why it failed.
• When an orchestrator finishes a load: on an org install, a tool such as Airflow can refresh the dashboard as soon as its load finishes, so the data does not wait for the next scheduled time. In the Scheduled refresh section, select Edit or Schedule…, turn on Refresh when triggered, and select Save schedule. The cron is optional beside it. The section then has the address the orchestrator calls. The operator of the instance gives the orchestrator the refresh token for the call. A dashboard with the setting off refuses the call.

In the Scheduled refresh section, the schedule reads as a clock and the cadence in short words, such as "Mondays 06:00". Point at it to read the full cadence, the time zone, and the next run. When the schedule is paused until a datasource runs once, an amber pause glyph follows. Point at the glyph to read the reason and what to do.

The header has the freshness chip: a clock and the age of the oldest saved data, such as "2 h ago". Editors and viewers see the same chip. Select the chip to see each datasource and the age of its data, then select Open the Data workspace. With a schedule set, the chip turns amber when a run is overdue.

The limits

Each limit has the same fix: make the data smaller.

• 500,000 rows per datasource. When a BigQuery run goes over the cap, you see its preview and a hint that a save holds only the first rows. An imported file over the cap goes to the reduce flow. In both cases, aggregate or filter until the whole result fits.
• 50,000 rows per chart, as advice. A chart that still reads more rows after the browser reduces them draws slowly on an older laptop, and the Findings panel tells the editor so. See How a dashboard works. Past 250,000 rows across all the charts, it says the same of the whole dashboard. The save still works.
• 512 MB per imported file. The app checks the size before it reads the file.

Every viewer reads saved rows, and no viewer runs a query.