Querying Your Data

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

caution

Dremio's query engine intentionally ignores any file or folder if the filename or folder name starts with a period (“.”) or an underscore (“_”).

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
• Share a script by adding privileges
• Search your set of scripts by name
• Sort scripts by name or date

Adding Privileges

You can share a saved script with others in your organization by adding privileges.

To add privileges to a saved script:

1. In the Scripts panel, click the ellipsis next to the saved script to see available actions, and then click Privileges.

2. In the Privileges dialog, enter the name of the user or role that you want to grant access to.

3. Click the Add to Privileges button. The added user or role will be displayed in the Users table.

4. For the users or roles in the Users table, toggle the green checkmark for each privilege you want to grant for the script. You can select from View, Modify, Manage Grants, and Delete. To remove an added privilege, click the green checkmark.

   note

   For each privilege, hover over the column name in the Users table for a description of the privilege.

5. Click Save.

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 engine and returns the complete result set.

caution

Dremio's query engine intentionally ignores any file or folder if the filename or folder name starts with a period (“.”) or an underscore (“_”).

caution

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 result of queries run in the Dremio app has a threshold of one million. Depending on the number of threads (minor fragments) and how data is distributed among those threads, Dremio could truncate results before reaching the threshold. Each individual thread stops processing after returning a number of records equal to threshold/# of threads. For example, a query runs with 10 threads and should return 800,000 records, but one of the threads is responsible for 400,000 records. The per-thread threshold is 100,000, so that thread will only return 100,000 records and you will only see 500,000 records in the output. When results are truncated, the Dremio app will display a warning that the results are not complete and users can execute the query using JDBC/ODBC to get complete results.

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. SQL editor

The SQL editor is where you create and edit queries to get insight from your data. For more information on supported SQL, see the SQL Reference. In the top-right corner of the SQL editor, you'll find:

a. Toggle this button to show or hide the SQL editor. If you hide the editor and navigate away from the page, the editor will remain hidden when you return.

b. Grant script privileges to share a saved script with others in your organization.

c. 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.

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

e. 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.

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

g. 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 by navigating to Project Settings > Preferences > Autocomplete.

h. Use any of these keyboard shortcuts:

Function	MacOS Shortcut	Windows/Linux Shortcut
Run	Cmd + Shift + Enter	Ctrl + Shift + Enter
Preview	Cmd + Enter	Ctrl + Enter
Comment Out/In	Cmd + /	Ctrl + /
Find	Cmd + f	Ctrl + f
Trigger Autocomplete	Cmd + Space	Ctrl + Space
Format Query	Cmd + Shift + f	Ctrl + Shift + f

Running Multiple Queries

You can run multiple queries in the SQL editor by using a semicolon to separate each statement. To run all of the queries in the SQL editor, simply click Run. The results of each query will be shown in the same order that the queries are constructed:

When you have multiple queries, you can also select a subset to run. If you select one or more queries and then click Run, only the selected queries will run accordingly, as shown below:

8. Result set actions

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

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

2. 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: 
   • The download process runs a CREATE TABLE AS SELECT (CTAS) command, which is why compute resources are required.

The download and copy results features can be enabled or disabled for a project by navigating to Project Settings > Preferences > Copy or Download Results. Disabling this in a project will prevent all users from downloading and copying results from the project.

9. 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, a Job Overview page opens in a new tab, providing a summary, total execution time, reflections data, job results, and more details. If a job took too long or failed, viewing the job overview can help you troubleshoot what actually happened.

10. 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".

11. 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.

You can edit a result value directly if you click and drag your cursor over the result value, which opens a dropdown of available edit options such as to replace, keep only, exclude, or copy the result value.