runExternalQuery

Syntax

runExternalQuery(config, query, [preExecuteQueries])

Details

Sends the specified SQL statement to a remote database, executes it there, and returns the execution result.

Note:
  • Before calling this function, you must install and load the ODBC plugin.
  • Supported remote databases include MySQL, PostgreSQL, SQL Server, ClickHouse, SQLite, Oracle, Hive, and GaussDB. It is also recommended to deploy the database on CentOS 7.

Parameters

config: A dictionary that specifies the parameters for connecting to the remote database. The key is "connectionString", and the value is an ODBC connection string. Example: config = dict(["connectionString"], ["Dsn=MyOracleDB"]).

query: A STRING scalar that specifies the SQL statement to be executed. The statement must conform to the SQL syntax of the remote database.

preExecuteQueries (optional): A STRING vector. If certain SQL statements need to be executed before the SQL statement specified by query, set this parameter. The system executes the SQL statements in the order they appear in the vector, but no results are returned for these pre-executed statements.

Returns

An in-memory table.

Examples

oracle_cfg = dict(["connectionString"], ["Dsn=MyOracleDB"])

t1 = createExternalTable("t1", "oracle", oracle_cfg)

t2 = runExternalQuery(oracle_cfg, "select * from t1 inner join t2 on t1.id = t2.id;")

t3 = select * from t1, t2, runExternalQuery(oracle_cfg, "select * from t3;") as t3;

Related Function: createExternalTable