Querying Relational-Database Sources Directly
Dremio enables users to run SQL commands directly against relational-database sources. These external queries, so called because they are passed by and run outside of Dremio, use syntax that is native to the relational-database sources that they run on. They can be SQL commands that are not supported directly in Dremio or that are too complex for Dremio convert to syntax that it can run.
Users can run external queries against relational-database sources of these types:
- Amazon Redshift
- IBM Db2
- Microsoft Azure Data Explorer
- Microsoft Azure Synapse Analytics
- Microsoft SQL Server
- Dremio supports only
SELECTSQL statements in external queries.
- Dremio does not support batched
SELECTstatements that return multiple result sets.
To execute external queries, users need the EXTERNAL QUERY privilege.
- For the steps of granting this privilege through the Dremio console, see Granting Privileges
- For the syntax of granting this privilege through an SQL command, see GRANT/REVOKE.
- For the syntax of granting this privilege through a REST API, see GRANTS. The type of catalog object that this privilege can be granted on is referred to as an ARP_SOURCE in the API reference.
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.
For the SQL syntax of external queries, see SELECT.
Dremio clears permissions, formats, and data reflections for all views created from the results of external queries when you update the metadata for the data source.