Updated on 2023-12-21 GMT+08:00

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

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
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');