Creating a DLI Table and Associating It with RDS

Function

This statement is used to create a DLI table and associate it with an existing RDS table. This function supports access to the MySQL and PostgreSQL clusters of RDS.

In Spark cross-source development scenarios, there is a risk of password leakage if datasource authentication information is directly configured. You are advised to use the datasource authentication provided by DLI.

For details about datasource authentication, see Introduction to Datasource Authentication.

Prerequisites

Before creating a DLI table and associating it with RDS, you need to create a datasource connection. For details about operations on the management console, see Enhanced Datasource Connection.

Syntax

CREATE TABLE [IF NOT EXISTS] TABLE_NAME
  USING JDBC OPTIONS (
  'url'='xx',
  'driver'='DRIVER_NAME',
  'dbtable'='db_name_in_RDS.table_name_in_RDS',
  'passwdauth' = 'xxx',
  'encryption' = 'true');

Keyword

**Table 1** CREATE TABLE parameter description
Parameter	Description
url	Before obtaining the RDS IP address, you need to create a datasource connection first.. For details about operations on the management console, see Enhanced Datasource Connection. After an enhanced datasource connection is created, use the internal network domain name or internal network address and database port number provided by RDS to connect to DLI. If MySQL is used, the format is **protocol header://internal IP address:internal network port number. If PostgreSQL is used, the format is protocol header://internal IP address:internal network port number/database name. For example: jdbc:mysql://192.168.0.193:3306 or jdbc:postgresql://192.168.0.193:3306/postgres**.
driver	JDBC driver class name. To connect to a MySQL cluster, enter com.mysql.jdbc.Driver. To connect to a PostgreSQL cluster, enter org.postgresql.Driver.
dbtable	To access the MySQL cluster, enter Database name.Table name. CAUTION: The name of the RDS database cannot contain hyphens (-) or ^. Otherwise, the table fails to be created. To access the PostGre cluster, enter Schema name.Table name NOTE: The schema name is the name of the database schema. A schema is a collection of database objects, including tables and views.
user	(Discarded) Specifies the RDS username.
password	(Discarded) Specifies the RDS username and password.
passwdauth	Datasource password authentication name. For details about how to create datasource authentication, see Datasource Authentication in the Data Lake Insight User Guide.
encryption	Set this parameter to true when datasource password authentication is used.
partitionColumn	This parameter is used to set the numeric field used concurrently when data is read. NOTE: The partitionColumn, lowerBound, upperBound, and numPartitions parameters must be set at the same time. To improve the concurrent read performance, you are advised to use auto-increment columns.
lowerBound	Minimum value of a column specified by partitionColumn. The value is contained in the returned result.
upperBound	Maximum value of a column specified by partitionColumn. The value is not contained in the returned result.
numPartitions	Number of concurrent read operations. NOTE: When data is read, the number of concurrent operations are evenly allocated to each task according to the lowerBound and upperBound to obtain data. The following is an example: 'partitionColumn'='id', 'lowerBound'='0', 'upperBound'='100', 'numPartitions'='2' Two concurrent tasks are started in DLI. The execution ID of one task is greater than or equal to 0 and the ID is less than 50, and the execution ID of the other task is greater than or equal to 50 and the ID is less than 100.
fetchsize	Number of data records obtained in each batch during data reading. The default value is 1000. If this parameter is set to a large value, the performance is good but more memory is occupied. If this parameter is set to a large value, memory overflow may occur.
batchsize	Number of data records written in each batch. The default value is 1000. If this parameter is set to a large value, the performance is good but more memory is occupied. If this parameter is set to a large value, memory overflow may occur.
truncate	Indicates whether to clear the table without deleting the original table when overwrite is executed. The options are as follows: true false The default value is false, indicating that the original table is deleted and then a new table is created when the overwrite operation is performed.
isolationLevel	Transaction isolation level. The options are as follows: NONE READ_UNCOMMITTED READ_COMMITTED REPEATABLE_READ SERIALIZABLE The default value is READ_UNCOMMITTED.

Precautions

When creating a table associated with RDS, you do not need to specify the Schema of the associated table. DLI automatically obtains the schema of the table in the dbtable parameter of RDS.

Example

Accessing MySQL

CREATE TABLE IF NOT EXISTS dli_to_rds
  USING JDBC OPTIONS (
  'url'='jdbc:mysql://to-rds-117405104-3eAHxnlz.datasource.com:3306',
  'driver'='com.mysql.jdbc.Driver',
  'dbtable'='rds_test.test1',
  'passwdauth' = 'xxx',
  'encryption' = 'true');

Accessing PostgreSQL

CREATE TABLE IF NOT EXISTS dli_to_rds
  USING JDBC OPTIONS (
  'url'='jdbc:postgresql://to-rds-1174405119-oLRHAGE7.datasource.com:3306/postgreDB',
  'driver'='org.postgresql.Driver',
  'dbtable'='pg_schema.test1',
  'passwdauth' = 'xxx',
  'encryption' = 'true');