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

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

Grant external query permissions to user
GRANT EXTERNAL QUERY ON SOURCE <data_source_name> TO USER <username>

Or

Grant external query permissions to role
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.

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(
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:

Syntax for external query with string literal
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:

Syntax for external query with quotation mark escapes
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.