Importing Data

Prerequisites

The IP addresses and ports of servers where CNs and DNs reside can connect to those of a GDS server.

Procedure

1. Create a target table in GaussDB to store imported data. For details, see CREATE TABLE.
2. (Optional) If the target table has an index, the index information will be incrementally updated during the import, affecting data import performance. You are advised to delete the index from the target table before the import. You can create the indexes again after the import is complete.
   1. If there is an ordinary index product_idx in the product_id column of the target table product_info, delete the index from the table before importing data.

      1	DROP INDEX product_idx;

   2. After importing the data, create the index again.

      1	openGauss=# CREATE INDEX product_idx ON product_info(product_id);

   3. Set enable_stream_operator to on.

      1	openGauss=# set enable_stream_operator=on;

   

   To accelerate the index recreation, add the maintenance_work_mem and psort_work_mem parameters.

3. Import data.

   1	openGauss=# INSERT INTO [Target table name] SELECT * FROM [Foreign table name]

   • If information similar to the following is displayed, the data has been imported. Query the error information table to check whether any data format errors occurred. For details, see Handling Import Errors.

     INSERT 0 9

   • If data fails to be loaded, troubleshoot the problem by following the instructions provided in Handling Import Errors and try again.
   

   • If a data loading error occurs, the entire data import task will fail.
   • Create batch processing scripts to concurrently import data. The degree of parallelism depends on server resource usage. You can test several tables and monitor resource usage to determine whether to increase or reduce the amount. Common resource monitoring commands include top for monitoring memory and CPU usage, iostat for monitoring I/O usage, and sar for monitoring networks. For details on application cases, see Example: Data Import Using Multiple Threads.
   • If possible, more GDS servers can significantly improve the data import efficiency. For details on application cases, see Example: Parallel Import from Multiple Data Servers.
   • In a scenario where many GDS servers import data concurrently, you can extend the TCP Keepalive interval for connections between GDS servers and DNs to ensure connection stability. (The recommended interval is 5 minutes.) TCP Keepalive settings of the cluster affect its fault detection response time.
   • If enable_stream_operator is set to on, the performance is affected. If there are other SQL statements to be executed in the session, you are advised to set enable_stream_operator to off. If there is no SQL statement to be executed in the session, disconnect the session.

Examples

1. Create a target table named reasons.

   1 2 3 4 5 6 7

   openGauss=# CREATE TABLE reasons ( r_reason_sk integer not null, r_reason_id char(16) not null, r_reason_desc char(100) ) DISTRIBUTE BY HASH (r_reason_sk);

2. Delete the index from the target table. Create the indexes again after the import is complete.
   1. If there is an ordinary table index reasons_idx in the r_reason_id column of the reasons table, delete the index from the table before importing data.

      1	openGauss=# DROP INDEX reasons_idx;

   2. After importing the data, create the index again.

      1	openGauss=# CREATE INDEX reasons_idx ON reasons(r_reasons_id);

   3. Set enable_stream_operator to on.

      1	openGauss=# set enable_stream_operator=on;

3. Import data from source data files through the foreign_tpcds_reasons foreign table to the reasons table.

   1	openGauss=# INSERT INTO reasons SELECT * FROM foreign_tpcds_reasons ;