On this page

    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
       <USER1>
    

    Or

    Grant external query permissions to role
    GRANT EXTERNAL_QUERY ON SOURCE <DATA-SOURCE-NAME> TO ROLE
       <ROLE1>
    

    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.