On this page

    Querying Your Data

    The SQL Runner is where you run queries on your datasets and get results. To navigate to the SQL Runner, click on the This is the icon that represents the SQL runner. icon in the side navigation bar. The main components of the SQL Runner are highlighted below:

    This is a screenshot showing the main components of the SQL Runner. These components include data, SQL history, run mode, preview mode, engine selector, SQL editor, execution state, transformations, and results table.

    1. Data

    The Data panel is used to explore your data catalog, which includes sources, tables, and views. For catalog objects that you use frequently, you can star the objects to make them easier to access from the panel.

    To add a dataset into the SQL editor, go to the data source. Use the left > caret to expand the source view. Locate the dataset that you would like to use within the query. Click the + button or drag and drop the data into the SQL editor.

    2. Scripts

    You can save your SQL as a script if you have drafts or SQL statements that you run frequently. Each saved script has a name, when the script was created or modified, and the context that was set for the editor.

    In the Scripts panel, you can:

    • Open a script in the SQL editor
    • Rename a script
    • Delete a script
    • Search your set of scripts by name
    • Sort scripts by name or date

    This screenshot is displaying the Scripts panel within the SQL Runner.

    3. SQL history

    When you run a query, it is tracked as a new link in the SQL history. You can use the SQL history to go back to a previous query that you can reload and edit in the current tab or a new tab. If you choose to use the SQL history, make sure to go back to the top link of the history chain to continue creating new queries.

    note:

    SQL history is not currently supported when there are multiple statements in the SQL editor.

    4. Run mode

    Running the query routes it to the selected engine and returns the complete result set. Learn more about engine autoscaling.

    warning:

    If the engine has zero replicas running at the time of the query run, the startup time will take about two minutes.

    note:

    Sometimes COUNT and SELECT query results do not match, because the results are truncated after the query hits a threshold of 1 million. For example, if a phase has 10 threads, then each fragment will stop accepting batches once it reaches 100K records. If the same query is run with more nodes and the phase has 20 threads, then each fragment will stop accepting batches once it reaches 50k records.

    5. Preview mode

    Executing a preview returns a subset of rows in the result set. Like the run mode, running the preview job will route the query to the selected engine, although the preview mode runs a subset of your results in less time.

    6. Discard

    By clicking Discard, you can clear the contents of the SQL editor.

    7. Engine selector

    Use the Engine dropdown to specify an engine for query execution. To choose your engine, click the Engine dropdown and select an engine from the dropdown. By default, Automatic is selected, which will use engine routing rules at runtime to route the query to the appropriate engine.

    8. SQL editor

    The SQL editor is where you create and edit queries to get insight from your data. You can also add multiple queries in the SQL editor by using a semicolon to parse the queries. For more information on supported SQL, see the SQL Reference. In the top-right corner of the SQL editor, you’ll find:

    This is a screenshot showing the SQL editor on the SQL Runner.

    a. Save your SQL as a script or as a view. You can save a script even if there are syntax errors. Saving a new view requires valid syntax, and there can be only one SQL statement in the editor.

    b. Setting Context for a session allows you to run queries without having to qualify the referenced objects.

    c. Setting Nessie references uses Dremio Sonar with Arctic. For more information, see Connecting Dremio Sonar to Arctic.

    d. The fx button provides a list of functions supported by Dremio along with a short description and syntax of each function. Click on the + button or drag and drop the function template into the SQL editor.

    e. The dark/light mode setting toggles the theme of the SQL editor.

    f. Enabling autocomplete can provide suggestions for SQL keywords, catalog objects, and functions while you are constructing SQL statements. Suggestions depend on the context set in the SQL Runner. The autocomplete feature can also be enabled or disabled for all your users using the Project Settings.

    g. Use the keyboard to reference any shortcuts.

    9. Result set actions

    Above the top-right corner of the result set, you will find these actions:

    This is a screenshot showing the result set actions on the SQL Runner.

    a. Download the result set as a JSON, CSV, or Parquet file.

    b. Copy result set to a clipboard. If the result set is too large, then download the table content to get the complete results.

    note:

    • The option to download as a CSV file is not available if the result set includes one or more columns that have complex datatypes (ie., a union, map, or array). Column headers for the results table indicate complex types with this icon: Hierarchy
    • The download process runs a CREATE TABLE AS SELECT (CTAS) command, which is why compute resources are required.

    10. Execution state

    The execution state will show you the type of job that was run, the number of records, and the amount of time that it took to run the query. When you click on the linked job, you will be directed to the Jobs page for a summary, execution time, and more details. If the job took too long or failed, viewing the job details can help you troubleshoot what actually happened.

    This is a screenshot showing the execution state on the SQL Runner.

    11. Transformations

    Transformations can be applied to data. Using the following no-code UI flows automatically updates the SQL in the SQL editor:

    • Add Field
    • Group By
    • Join
    • Filter Columns

    note:

    If you are using the preview mode, transformations use a subset of the results and may not provide a complete profile of the result set. It may show null or incomplete values in the dataset as a result of joining, grouping, or calculating fields. You may encounter an error showing “no rows returned due to the LIMIT logic” or “more rows returned due to an outer join”.

    12. Results table

    The results of the query are shown in a table format. You can edit a table column by clicking directly on the column title to open a dropdown of edit options, which include sorting results, converting data types, renaming columns, and calculating fields.

    This is a screenshot showing the result set actions on the SQL Runner.