Help Center> Data Lake Insight> SQL Syntax Reference (To Be Offline)> Spark SQL Syntax Reference (Unavailable Soon)> Creating a Datasource Connection with a DDS Table> Creating a DLI Table and Associating It with DDS
Updated on 2024-02-07 GMT+08:00
View PDF

Creating a DLI Table and Associating It with DDS

Function

This statement is used to create a DLI table and associate it with an existing DDS collection.

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 DDS, you need to create a datasource connection and bind it to a queue. For details about operations on the management console, see Enhanced Datasource Connection

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE TABLE [IF NOT EXISTS] TABLE_NAME(
    FIELDNAME1 FIELDTYPE1,
    FIELDNAME2 FIELDTYPE2)
  USING MONGO OPTIONS (
  'url'='IP:PORT[,IP:PORT]/[DATABASE][.COLLECTION][AUTH_PROPERTIES]',
  'database'='xx',
  'collection'='xx',
  'passwdauth' = 'xxx',
  'encryption' = 'true'
);

Keywords

Table 1 CREATE TABLE keywords

Parameter

Description

url

Before obtaining the DDS IP address, you need to create a datasource connection first.For details about operations on the management console, see Enhanced Datasource Connections.

After creating an enhanced datasource connection, use the random connection address provided by DDS. The format is as follows:

"IP:PORT[,IP:PORT]/[DATABASE][.COLLECTION][AUTH_PROPERTIES]"

Example: "192.168.4.62:8635,192.168.5.134:8635/test?authSource=admin"

database

DDS database name. If the database name is specified in the URL, the database name in the URL does not take effect.

collection

Collection name in the DDS. If the collection is specified in the URL, the collection in the URL does not take effect.

user

(Discarded) Username for accessing the DDS cluster.

password

(Discarded) Password for accessing the DDS 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.

If a collection already exists in DDS, you do not need to specify schema information when creating a table. DLI automatically generates schema information based on data in the collection.

Example

1
2
3
4
5
6
 
create table 1_datasource_mongo.test_mongo(id string, name string, age int) using mongo options(
  'url' = '192.168.4.62:8635,192.168.5.134:8635/test?authSource=admin',
  'database' = 'test',
  'collection' = 'test',
  'passwdauth' = 'xxx',
  'encryption' = 'true');