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:

• Amazon Redshift
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL
• Snowflake

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 via the use of roles.

This may also be done from the SQL Editor using privilege GRANT commands:

GRANT EXTERNAL QUERY ON SOURCE <data_source_name> TO USER <username>

Or

GRANT EXTERNAL QUERY ON SOURCE <data_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.

Using Correct Syntax

External queries must use the following syntax:

SELECT
    *
FROM
    TABLE(
            PostgreSQL.EXTERNAL_QUERY (
                    'SELECT string_col FROM tbl WHERE string_col = ''test'' '
                )
        )

Sonar treats the source query as a string literal that must be surrounded by single quotation marks, or ’. 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:

SELECT
    *
FROM
    TABLE(
            PostgreSQL.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:

SELECT
    *
FROM
    TABLE(
            PostgreSQL.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(
               PostgreSQL.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(
               PostgreSQL.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.