SQL on Anywhere

Various types of engine components provide many interfaces for big data processing. However, for traditional database users, the SQL language is still the most familiar and convenient interface. Their operations will be efficient using SQL statements on a client to perform operations on big data components.

openGauss supports SQL on Anywhere and allows to operate Hadoop, Oracle, Spark, and openGauss, building a unified big data computing platform.

Accessing Data Across Database Instances Based on Extension Connector

You can use Extension Connector provided by openGauss to send SQL statements to an Oracle, Spark, or other database instances outside the openGauss database, and return the execution result in the current database, implementing data processing.

To use Extension Connector, construct a data source (containing the connection and encoding information about the target database), obtain the permission to use the data source, connect to the target database using the standard ODBC API, send SQL statements, and obtain the execution result.

Extension Connector provides a unified connection function exec_on_extension (text, text), where the first text specifies the data source name and the second specifies the SQL statement to be sent.

Notice the following issues when using the exec_on_extension function:

The permission of exec_on_extension to connect to the target database is controlled by the data source usage permission.
The character encoding of exec_on_extension is stored in a data source. The default encoding is UTF-8.
exec_on_extension returns a value of the record type, that is, a dataset. Therefore, AS (table definition) is required to receive the return value. CALL is not supported.

exec_on_extension can send multiple types of SQL statements. The following table shows how to process their result sets.

SQL Statement Type	Returned Result	AS Clause
DDL (Example: CREATE and DROP)	Empty set: indicates a success. Non-empty set: indicates a failure.	Define a column of the TEXT type. (c1 text)
DQL (Example: SELECT and FROM)	Data tables	Match the type with the return value type by column.
DML (Example: INSERT and UPDATE)	Empty set: indicates a success. Non-empty set: indicates a failure.	Define a column of the TEXT type. (c1 text)
Text display command (Example: SHOW and EXPLAIN)	Several rows and columns	Define the text type to match the result. For example, if two text columns are returned, the clause can be defined as (c1 text, c2 text).

SQL Statement Type

Returned Result

AS Clause

DDL

(Example: CREATE and DROP)

Empty set: indicates a success.

Non-empty set: indicates a failure.

Define a column of the TEXT type.

(c1 text)

DQL

(Example: SELECT and FROM)

Data tables

Match the type with the return value type by column.

DML

(Example: INSERT and UPDATE)

Empty set: indicates a success.

Non-empty set: indicates a failure.

Define a column of the TEXT type.

(c1 text)

Text display command

(Example: SHOW and EXPLAIN)

Several rows and columns

Define the text type to match the result. For example, if two text columns are returned, the clause can be defined as (c1 text, c2 text).

Comply with the following rules to use exec_on_extension:

exec_on_extension first searches for USERNAME and PASSWORD in data sources. If they are not found, unixODBC searches the odbc.ini file. If they are still not found, the connection fails. If USERNAME and PASSWORD are incorrect in the data sources but correct in the odbc.ini file, the connection still fails.
In SELECT * FROM exec_on_extension() AS (C1, C2, …, Cn), the columns specified by the AS clause are the first n columns of the data returned by executing the SQL statement. Therefore, the column types should match the first n columns of the returned data. Otherwise, the result may be incorrect or the execution may fail. Do not use only columns in the middle or end of the returned result. Ensure that columns in the AS clause are no more than the returned columns.
exec_on_extension does not check the validity of the SQL statements to be sent. It can send only one SQL statement at a time. You are not advised to send the following statements or commands (exec_hadoop_sql also restricted):
- Do not send commands or SQL statements that are valid only in the current session, for example, the statement for creating a temporary table.
- Do not send statements that start a transaction, for example, START TRANSACTION.
- Do not send statements that call the exec_on_extension or exec_hadoop_sql function, for example, select * from exec_on_extension('xxx', 'select * from exec_on_extension()…').
- Do not send client commands, for example, the SQLPlus command of Oracle and meta-commands in “Client Tools > gsql > Meta-Command Reference” in the Tool Reference of the openGauss database instance.
- Do not send interactive commands, for example, the commands that require users to enter passwords.

NOTE:
For compatibility purposes, exec_hadoop_sql used in the earlier version for interconnecting to the Spark database instance is retained. Therefore, both exec_on_extension and exec_hadoop_sql can be used for Spark interconnection. The exec_hadoop_sql function has security risks and can be used only after permission is granted. You are advised to use the exec_on_extension function.
The exec_on_extension and exec_hadoop_sql functions are not supported in MySQL compatibility scenarios.