Help Center/ Data Lake Insight/ SQL Syntax Reference (To Be Offline)/ Spark SQL Syntax Reference (Unavailable Soon)/ Creating a Datasource Connection with a DWS table/ Creating a DLI Table and Associating It with GaussDB(DWS)
Updated on 2024-11-08 GMT+08:00
View PDF
Share

Creating a DLI Table and Associating It with GaussDB(DWS)

Function

This statement is used to create a DLI table and associate it with an existing GaussDB(DWS) table.

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 DWS, you need to create a datasource connection. For operations on the management console, see Enhanced Datasource Connections.

Syntax

1
2
3
4
5
6
 
CREATE TABLE [IF NOT EXISTS] TABLE_NAME
  USING JDBC OPTIONS (
  'url'='xx',
  'dbtable'='db_name_in_DWS.table_name_in_DWS',
  'passwdauth' = 'xxx',
  'encryption' = 'true');

Keywords

Table 1 CREATE TABLE keywords

Parameter

Description

url

Create a datasource connection before you can obtain this GaussDB(DWS) connection URL. For operations on the management console, see Enhanced Datasource Connections.

If you have created an enhanced datasource connection, you can use the JDBC Connection String (intranet) provided by DWS or the intranet address and port number to access DWS. The format is protocol header: //Internal IP address:Internal network port/Database name, for example: jdbc:postgresql://192.168.0.77:8000/postgres.

NOTE:

The DWS IP address is in the following format: protocol header://IP address:port number/database name

The following is an example:

jdbc:postgresql://to-dws-1174405119-ihlUr78j.datasource.com:8000/postgres

If you want to connect to a database created in DWS, change postgres to the corresponding database name in this connection.

dbtable

Specifies the name or Schema name.Table name of the table that is associated with the DWS. For example: public.table_name.

user

(Discarded) DWS username.

password

User password of the DWS cluster.

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 DWS, 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 DWS.

Example

1
2
3
4
5
6
 
CREATE TABLE IF NOT EXISTS dli_to_dws
  USING JDBC OPTIONS (
  'url'='jdbc:postgresql://to-dws-1174405119-ih1Ur78j.datasource.com:8000/postgres',
  'dbtable'='test_dws',
  'passwdauth' = 'xxx',
  'encryption' = 'true');