SQL dialect and data scope

The Editor runs ClickHouse SQL against the Lake and Warehouse tables your account can read. Read this page to check which statements run, which data your queries can read, and what the Role selector changes. See Editor for a map of the screen.

Which SQL runs in the Editor

Write read-only SQL. The statements the Editor runs start with SELECT, WITH or (; EXISTS checks also run. It counts the rows of a statement first and reads the result in pages of up to 1000 rows, so a statement that is not a read query fails with an error. This includes CREATE, INSERT, ALTER, DROP and SHOW, for example SHOW TABLES.

A semicolon at the end of a statement is fine.

To see which tables exist, use the Database Explorer. To create tables, use the Warehouse, as described in Create a dimension or fact table.

Choose the role whose data you browse

The Role selector decides which of your roles' databases the Database Explorer, autocomplete and AI suggestions use. It shows the active role's name, or No Role when no role is active. If your account has no roles, the selector does not appear.

Click Role at the right of the editor toolbar, or in the header of the Database Explorer.
Under Active Role, click the role whose data you want to browse.
Write your query against that role's databases, for example <role>_WH."<table>". You can also insert a table name from the Database Explorer.

For each role, the Editor offers the databases <role>_Lake and <role>_WH, and their PII_ tokenised mirrors. When a role is selected, the Database Explorer, autocomplete and AI suggestions show only these databases. A database name ending in _WH marks Warehouse; every other database is Lake. The Editor uses the ending when it opens table popups and previews.

Who decides what a query can read

A query runs under your own account, whichever role is selected. It can read the tables that your account is allowed to read.

Administrators set table access on each role, with Manage Tables. See Roles and permissions and How access works.

Note

Changing the role changes only what the Database Explorer, autocomplete and AI suggestions show. It does not change what your query is allowed to read.

While you view DataMind OS as another user, the Viewing as banner shows at the bottom of the screen. The Editor does not run SQL in this view: the refusal reads "SQL execution is disabled while impersonating another user; exit the view to run queries". Click Exit on the banner, then run the query again. See Impersonate a user.

Settings and defaults

Setting	What it does	Default
Role	Chooses the databases that the Database Explorer, autocomplete and AI suggestions use.	The role you last picked in this browser. If you have not picked one, your first role.
Column data types	Shows each column's ClickHouse type under the column name in the results. The results toolbar button toggles it, and its tooltip reads Show column data types or Hide column data types.	On.

Limits and edge cases

Large whole numbers appear as text. Integers larger than 9,007,199,254,740,992 (2^53) are shown as text, so that no digits are lost in the browser. 128-bit and 256-bit integers are always shown as text.
Results are read in pages of up to 1000 rows. Moving to another page runs the query again for that page.

Questions, answered

Which SQL dialect does the Editor use?

ClickHouse SQL. The statements it runs start with SELECT, WITH or (; EXISTS checks also run.

Can I create or change tables from the Editor?

No. The Editor does not run statements that change data or objects. CREATE, INSERT, ALTER and DROP fail, and SHOW statements fail as well. Build tables in the Warehouse instead, for example with Create a dimension or fact table.

Why does my SHOW TABLES query fail?

SHOW statements are not read queries, so the Editor does not run them. Use the Database Explorer to browse tables and their columns.

Why does my query return the same data after I change the role?

The Role selector changes what the Database Explorer, autocomplete and AI suggestions show. It does not change what a query reads. A query always runs under your own account, so it can read only the tables that your account is allowed to read. If you need a table that you cannot read, ask an administrator to check the table access of your roles.

Why can a colleague see data that I cannot?

Access is set per account, through the roles each person holds and the table access those roles give. A colleague with a different role can read tables that your account cannot. Ask an administrator to compare your roles. See Roles and permissions.

Why can't I run queries while viewing as another user?

The Editor does not run SQL while you view DataMind OS as another user. The refusal reads "SQL execution is disabled while impersonating another user; exit the view to run queries". Click Exit on the Viewing as banner, then run the query again.

Why do some big numbers appear as text?

Whole numbers larger than 9,007,199,254,740,992 (2^53) are shown as text. A browser cannot hold every digit of a larger number exactly, so the Editor sends these values as text.

Which role does the Editor use the first time?

Your first role. After that, the Editor uses the role you last picked in this browser.

What do the `_Lake` and `_WH` endings mean?

<role>_Lake and <role>_WH are the databases of the selected role. The _WH ending marks Warehouse; every other database is Lake. The Database Explorer uses the ending when it opens a table's popup or preview.