Skip to main content
Last updated on

remote_query table-valued function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Preview

This feature is in Public Preview.

Returns the tabular result of the query executed on the remote database engine.

remote_query fetches data from remote systems using credentials from a connection. The function accepts a set of connector options, so besides query.

This function requires named parameter invocation.

Syntax​

remote_query(connectionName [, connectorOptionKey => connectorOptionValue ] [...])

Arguments​

  • connectionName

    A mandatory STRING literal that references Unity Catalog connection name. If the connection name does not exist Databricks raises CONNECTION_NOT_FOUND.

    The following connections are supported:

    • BigQuery
    • MySQL
    • Oracle
    • PostgreSQL
    • Redshift
    • Snowflake
    • SQL Server
    • Teradata

    If the connection is not supported CONNECTION_TYPE_NOT_SUPPORTED_FOR_REMOTE_QUERY_FUNCTION is raised.

  • connectorOptionKey

    A connector option key which is a case-sensitive identifier. Each connection type has corresponding mandatory options (connections to SQL databases require query or table option, while NoSQL databases require collection option) and additional optional configuration options (e.g. MySQL, PostgreSQL, SQLServer, and other JDBC-like connections support fetchSize which configures the size of batches being fetched). If a key is not supported PARAMETER_NOT_SUPPORTED_FOR_REMOTE_QUERY_FUNCTION is raised.

  • connectorOptionValue

    A constant expression representing the connector option value.

Returns​

Returns a table. The output schema is dependent on the query or the tables being scanned on a remote system.

Notes​

This function cannot be used in streaming queries (UNSUPPORTED_STREAMING_TABLE_VALUED_FUNCTION).

Examples​

SQL
-- Simple example with MySQL connection
> SELECT a, b FROM remote_query('mysql_connection', query => 'SELECT * FROM mysql_database.mysql_table WHERE sin(b) = 0.5');
  a        b
  angle_1  30

-- Simple example with MongoDB (NoSQL) connection
> SELECT a, b FROM remote_query('mongo_connection', collection => 'collection1', pipeline => '[{''$match'': {''b'': 30}}]');
  a        b
  angle_1  30

-- Connection name does not exist
> SELECT * FROM remote_query('non_existent_uc_connection', query => '');
  Error: CONNECTION_NOT_FOUND

-- Connection type is not supported
> SELECT * FROM remote_query('google_drive_connection', query => '');
  Error: CONNECTION_TYPE_NOT_SUPPORTED_FOR_REMOTE_QUERY_FUNCTION