Dremio enables users to run external queries, queries that use the native syntax of the relational database, to process SQL statements that are not yet supported by Dremio or are too complex to convert. Dremio supports external queries for the following relational databases:
Note
Dremio supports onlySELECTSQL statements in external queries. Dremio does not support batchedSELECTstatements that return multiple result sets.
To perform external queries, users need the EXTERNAL_QUERY privilege, which appears as External Query on the Privileges screen. Administrators must manually grant each user this privilege at the System or Source level.
With this privilege, users can access all types of data assets that might exist in external sources, including databases and tables. Therefore, Dremio recommends that you restrict access to external sources. You can do so by, for example, granting only read-only access to datasets.
A Dremio administrator must enable external queries for a relational database data source. Dremio recommends configuring access to the source using a database user with read-only access to the relational databases. Once enabled, Dremio users with edit access to the source also have access to all derived database assets, such as databases and tables.
Warning
Enabling this feature allows users to bypass dataset and table-specific permissions in Dremio.
You must have the following prerequisites before enabling external queries:
To enable external queries:
Datasets page, click External Sources.Advanced Options in the Edit Source modal.Grant External Query access.Save.Note: Dremio clears permissions, formats, and data reflections for all datasets created from external queries when you update the metadata for the data source.
External queries use the following syntax:
table(<datasource-name>.external_query('<source-query'))
Dremio 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, Dremio users must 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 consecutive quotation marks, as shown in the following example:
SELECT * FROM table(postgresql.external_query('SELECT string_col from tbl where string_col = ‘'john''''s car'’))
The following example selects the entire table named Actor from the data source named PostgreSQL:
SELECT * FROM table(PostgreSQL.external_query('SELECT * FROM Actor'))
Datasets page, click External Sources.Run.Dremio users and developers can then save the results of their external queries to a virtual data source by clicking the disk image icon, then selecting Save As....
Important
Dremio does not support moving virtual data sources created from external queries until after Dremio performs the first refresh of the data source.
The following source query demontrates how to perform a JOIN between a Dremio physical data source and a table in a virtual data source created from the results of an external query:
SELECT b.customer_id, a.product_id, a.price
FROM table(postgresql.external_query( source_a,
'SELECT product_id, price
FROM products' )) AS a,
source_b.sales AS b
WHERE b.product_id = a.product_id'