On this page

    External Sources

    You can run queries directly against relational databases, referred to in Dremio Cloud as “external sources.” Using these external sources, 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 external sources are supported:

    Enabling Users to Perform External Queries

    To perform external queries directly from Dremio Cloud, 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 
       <USER1>
    

    Or

    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:

    Dremio Cloud 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:

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

    Dremio 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. From the Datasets page, click External Sources.
    2. Select the data source that you want to query.
    3. Using the SQL Editor, enter your external query. For example:
    SELECT
        *
    FROM
        TABLE(
                PostgreSQL.EXTERNAL_QUERY ('SELECT * FROM Actor')
            )
    
    1. 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 External Sources.
    2. Select the table that you want to query.
    3. Using the SQL Editor, enter your external query. For example:
    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
    
    1. 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.