Create a custom table from SQL

A custom table is a Lake table that your own SQL query defines. This page covers the popup that creates one: where the table lives, how the query is checked and what happens when you press Create. For the wider import journey, see Import tables.

Open the popup

1. In Lake, right-click the datasource, database or schema and choose Add new. The item appears on a datasource that supports custom tables, and on its databases and schemas.
2. The popup holds two sections: Table Location for the destination and SQL Query for the query itself.

Fill in the table location

1. Pick a Database and a Schema, then type a Table Name. While they are empty, the fields read Select a database, Select a schema and Enter table name.
2. Schema stays greyed out until a Database is chosen.
3. If you opened the popup from a database or a schema row, those choices arrive filled in and locked; fill in only what is left free.
4. Changing Database clears the chosen Schema, so pick it again.

Write and check the query

Write the query in the SQL Query editor. While the box has text, a chip beside Run Query shows where the check stands:

1. Click Run Query to check the query. While the check runs, the editor is read-only and the button reads Running....
2. Run Query stays greyed out until a database, a schema, a table name and a query are all filled in. It is also inactive while a check runs and after the query has passed; edit the query to check it again.
3. When the query passes, its result opens in a Query Data Preview window; for what that window shows and its downloads, see Shared elements in table popups.
4. When it fails, the chip returns to Query not validated and a Query validation failed notification carries the reason. Fix the query and run it again.

The check is tied to one database and one query text. Change either and the chip drops back to Query not validated, unless the database and the query exactly match what you last checked.

Create the table

1. When the location, the name and a validated query are all in place, Create becomes active in the footer.
2. Click Create. While the table is being created, Create shows a spinner and Cancel is disabled.
3. On success, a Success notification reads Table created successfully., the popup closes, and the new table opens in a popup of its own, ready to set up; see Configure a table for its columns, sync and deployment.
4. If the creation is refused, an Error notification carries the reason (or reads Failed to create table), and the popup stays as you left it, so fix what the message points at and press Create again.

Run Query only checks the query; the table is created when you press Create. Cancel closes the popup without creating anything. If you have unsaved changes, the popup's Close button asks you to confirm first: "You have unsaved changes. Do you want to close the popup?" The popup keeps what you filled in through a page refresh.

Questions, answered

Why is Run Query greyed out even though I have written my query?

The button needs more than a query: Database, Schema and Table Name must be filled in too. It is also inactive while a check runs and after the query has passed. Fill in the three fields; after a pass, edit the query to check a new version.

Why did Query validated go back to Query not validated?

The check holds one database and one query text. The chip reads Query validated only while the current database and query are exactly the pair that last passed; any other change returns it to Query not validated, and Run Query restores it.

Can I change the database the popup arrived with?

No. A database or schema that the popup was opened from arrives filled in and locked. If you need different values, close the popup and open it again from a level where the choice is free, such as the datasource row.

Does Run Query create the table?

No. Run Query only checks the query and shows the result in the Query Data Preview window when it passes. The table is created only when you press Create.

What happens after I press Create?

On success the popup closes and the new table opens in its own popup, so you can set up its columns, sync and deployment right away.

The popup says Failed to create table. What should I do?

Read the notification's description: it carries the reason from the server. The popup stays open with everything as you left it, so fix what the message points at and press Create again.

Does anything warn me if the table name is already taken?

No. This popup does not check the chosen schema for an existing name while you fill the form, so pick a name that is unique in the schema. If the creation is refused, the Error notification carries the server's reason.

Why does the popup ask me to confirm before it closes?

Because it holds something that has not been created yet: a name, a query, or a location that differs from what the popup opened with. Close in the confirmation closes the popup without creating the table; dismiss it to keep editing.