CREATE DATA SOURCE

Function

CREATE DATA SOURCE creates an external data source object, which defines the information about the database that GaussDB will connect to.

Precautions

• The data source name must be unique in the database and comply with the identifier naming rules. Its length cannot exceed 63 bytes. Otherwise, it will be truncated.
• Only the system administrator or initial user has the permission to create data sources. The user who creates the object is the default owner of the object.
• If the password option is displayed, ensure that the datasource.key.cipher and datasource.key.rand files exist in the $GAUSSHOME/bin directory of each node in the cluster. If the two files do not exist, use the gs_guc tool to generate them and use the gs_ssh tool to release them to the $GAUSSHOME/bin directory on each node in the cluster.

Syntax

1
2
3
4

CREATE DATA SOURCE src_name
    [TYPE 'type_str']
    [VERSION {'version_str' | NULL}]
    [OPTIONS (optname 'optvalue' [, ...])];

Parameter Description

• src_name

  Specifies the name of the new data source, which must be unique in the database.

  Value range: a string. It must comply with the naming convention.

• TYPE

  Specifies the type of the data source. This parameter can be left empty, and its default value will be used.

  Value range: an empty string or a non-empty string

• VERSION

  Specifies the version number of the new data source object. This parameter can be left empty or set to null.

  Value range: an empty string, a non-empty string, or null

• OPTIONS
  Specifies the options of the data source object. This parameter can be left empty or specified using the following keywords:

  • optname

    Specifies the option name.

    Value range: dsn, username, password, and encoding. The value is case-insensitive.

    • dsn corresponds to the DSN in the ODBC configuration file.
    • username and password indicate the username and password for connecting to the destination database.

      The user name and password entered by the user are encrypted in the GaussDB background to ensure security. The key file required for encryption must be generated using the gs_guc tool and released to the $GAUSSHOME/bin directory of each node in the cluster using the gs_ssh tool. username and password shall not contain the prefix encryptOpt. Otherwise, the values of username and password will be considered as encrypted ciphertext.

    • encoding indicates the character string encoding mode used for interaction with the destination database (including the sent SQL statements and returned data of the character type). Its validity is not checked during object creation. Whether data can be encoded and decoded depends on whether the encoding you specified can be used in the database.
  • optvalue

    Specifies the option value.

    Value range: an empty string or a non-empty string

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Create an empty data source object that does not contain any information.
openGauss=# CREATE DATA SOURCE ds_test1;

-- Create a data source object with TYPE information and VERSION being null.
openGauss=# CREATE DATA SOURCE ds_test2 TYPE 'MPPDB' VERSION NULL;

-- Create a data source object that contains only OPTIONS.
openGauss=# CREATE DATA SOURCE ds_test3 OPTIONS (dsn 'GaussDB', encoding 'utf8');

-- Create a data source object that contains TYPE, VERSION, and OPTIONS.
openGauss=# CREATE DATA SOURCE ds_test4 TYPE 'unknown' VERSION '11.2.3' OPTIONS (dsn 'GaussDB', username 'userid', password 'pwd@123456', encoding '');

-- Delete the data source object.
openGauss=# DROP DATA SOURCE ds_test1;
openGauss=# DROP DATA SOURCE ds_test2;
openGauss=# DROP DATA SOURCE ds_test3;
openGauss=# DROP DATA SOURCE ds_test4;

Helpful Links

ALTER DATA SOURCE and DROP DATA SOURCE