SQL console

SQL console provides an interface for writing and executing SQL queries in Foundry.

Using SQL console

You can access SQL console in the bottom panel of Supported applications. The starter query is prepopulated based on your currently selected resource.

Supported modes and applications

SQL console is available in the following modes and applications:

Data mode

You can access the SQL console in data mode in the applications specified above. The example below demonstrates where to find the console in Dataset Preview.

You can open the console using the SQL console option in the top navigation bar, or from the expand button at the bottom.

After opening SQL console, there will be a SELECT * command populated by default. You can update and run queries here according to your needs.

Object mode [Beta]

To access SQL console in object mode, navigate to an object type in Ontology Manager. When viewing an object type, you can find the option to open SQL console in the bottom toolbar.

Similar to data mode, after opening SQL console, you can create and run queries. Note that SQL console in object mode will use a tabular format for outputs.

When accessing the SQL console in Ontology Manager, you can select between object mode and data mode. The object type's backing and materialized dataset will be suggested in the drop down.

You can write any supported SQL query following the supported Spark SQL dialect. In Spark SQL, identifiers such as table names should be quoted using backticks ( ` ) rather than single or double quotes. You can also leverage auto-complete features, such as searching for dataset names or column names using backticks.

Core functionality

SQL console provides the following core functionality:

• Query tabular data (SELECT)
• Autocompletion of the dataset schema and column names
• Search for other datasets within backticks (`) to perform efficient JOIN queries
• Use editor-friendly features such as keyboard shortcuts to run a highlighted query
• Output a preview table for results of the executed SQL query
• Resize columns and the bottom panel to fit your preferences

Beta functionality

Additionally, SQL console provides the following beta functionality, which can be enabled on request:

• Queries on ontology object types in Ontology Manager.
• Table creation (CREATE TABLE)
• Table alteration for Iceberg tables (INSERT INTO, UPDATE, DELETE)
• Savable SQL worksheet resources
• Optional full-screen view

SQL console roles and permissions

In order to use SQL console in data mode, you must have access to at least one of the following roles:

If your organization has set up custom roles or download controls, Query datasets using SQL may have been restricted. Enable Preview datasets using SQL to allow previewing data while restricting full table downloads.