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:
Dremio supports only
SELECTSQL statements in external queries. Dremio does not support batched
SELECTstatements 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.
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:
Advanced Optionsin the
Grant External Query access.
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:
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
SELECT * FROM table(PostgreSQL.external_query('SELECT * FROM Actor'))
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
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'