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 onlySELECT
SQL statements in external queries. Dremio does not support batchedSELECT
statements that return multiple result sets.
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'