Skip to main content

Databases

You can run queries directly against relational databases, referred to in Dremio Sonar as databases. Using these databases, you can perform "external queries." External queries use native syntax for a source and can be used for SQL statements that are not supported directly in Dremio or are too complex to convert.

The following databases are supported:

Enabling Users to Perform External Queries​

To perform external queries directly from Sonar, users need the EXTERNAL QUERY privilege, which appears as External Query on the Privileges tab of a source's settings dialog. Administrators must manually grant each user this privilege at the source level or by using roles.

Privileges can be managed using the GRANT TO USER or GRANT TO ROLE commands:

Grant external query permissions to a user 
GRANT EXTERNAL QUERY ON SOURCE <source_name> TO USER <username>
Grant external query permissions to a role 
GRANT EXTERNAL QUERY ON SOURCE <source_name> TO ROLE <role_name>

When external querying is not enabled, all queries must use standard SQL syntax. Likewise, all user or role read and write access to tables or views within a source are governed using Dremio's access management rules.

note

Sonar clears permissions, formats, and data reflections for all datasets created from external queries when you update the metadata for the data source.

Using Correct Syntax​

External queries must use the following syntax:

Syntax for external queries 
SELECT *
  FROM TABLE(<source_name>.EXTERNAL_QUERY ('<select_statement'))

Sonar treats the source query as a string literal that must be surrounded by single quotation marks. If an external query contains a string literal inside the source query, you must then use double-single quotes, as shown in the following example:

Syntax for external query with string literal 
SELECT *
  FROM TABLE(<source_name>.EXTERNAL_QUERY ('SELECT string_col FROM tbl WHERE string_col = ''test'' '));

To escape a single quotation mark, use four (4) consecutive quotation marks, as shown below:

Syntax for external query with quotation mark escapes 
SELECT *
  FROM TABLE(<source_name>.EXTERNAL_QUERY ('SELECT string_col FROM tbl WHERE string_col = ''john '''' s car '''));

Performing a SELECT External Query​

The following steps describe how to perform an external query:

  1. On the Datasets page, click Databases.

  2. Select the data source that you want to query.

  3. Using the SQL editor, enter your external query. For example:

    Example external query 
    SELECT *
      FROM TABLE(<source_name>.EXTERNAL_QUERY ('SELECT * FROM Actor'));

  4. Click Run.

Performing a JOIN External Query​

The following steps describe how to perform a JOIN between a table and a view created by an external query:

  1. On the Datasets page, click Databases.

  2. Select the table that you want to query.

  3. Using the SQL editor, enter your external query. For example:

    Example JOIN external query 
    SELECT B.customer_id, A.product_id, A.price
      FROM TABLE(<source_name>.EXTERNAL_QUERY ('SELECT product_id, price FROM products')) AS A,source_b.sales AS B
        WHERE B.product_id = A.product_id;

  4. Click Run.

Limitations​

  • You cannot move views created from external queries until after the first the data source refresh is performed.
  • Only SELECT statements are supported for external queries. Batched SELECT statements that return multiple result sets are not supported.