gs_loader for Importing Data

Description

gs_loader is used to import data. gs_loader converts the syntax supported by the control file to the \COPY syntax, uses the existing \COPY function to import data, and records the \COPY result in logs.

gs_loader does not support M-compatible databases.

gs_loader supports PDBs.

During a rolling upgrade from an earlier version to this version, do not use the gs_loader tool before all nodes are upgraded.

Prerequisites

• Before using gs_loader, ensure that the gs_loader version is consistent with the gsql version and database version.
• After installing and deploying the gs_loader service, you need to add the tool path to PATH. gs_loader supports SSL encrypted communication. The method of using gs_loader is the same as that of using gsql.
• Set the log level for developers to view. After the setting, the tool running information is printed on the console.

  export gs_loader_log_level=debug
  export gs_loader_log_level=info
  export gs_loader_log_level=warning
  export gs_loader_log_level=error

Installation and Deployment

Install and configure the gs_loader client tool on the server where source data files are stored so that you can use the gs_loader tool to import data.

1. Create a directory for storing the gs_loader tool package.

   mkdir -p /opt/bin

2. Upload the gsql tool package to the created directory.

   Taking the tool package for EulerOS as an example, upload the gsql tool package GaussDB-Kernel_Database version number_OS version number_64bit_gsql.tar.gz in the software installation package to the created directory.

3. Go to the directory where the tool package is located and decompress the package.

   cd /opt/bin
   tar -zxvf GaussDB-Kernel_Database version number_OS version number_64bit_gsql.tar.gz
   source gsql_env.sh

4. Verify the tool location and version information.

   which gs_loader

5. Verify the client version information.
   The gs_loader tool version number corresponds to the gsql tool version number. You can directly query the gsql client version number to verify the client version information.

   gsql -V

6. Verify that the database version is the same as the client tool version.
   Use gsql to connect to the database and run the following command:

   select version();

Permission

The application scenarios of gs_loader are classified into separation-of-duties and non-separation-of-duties scenarios. You can set GUC parameter enableSeparationOfDuty to on or off to enable or disable the separation of duties function.

The GUC parameter enable_copy_error_log controls whether to use the error table gs_copy_error_log and log table gs_copy_summary_log, and is set to off by default, indicating that the error table and log table are not used. Error records are directly recorded in the .bad file of gs_loader, and logs are directly recorded in the log file of gs_loader. If this parameter is set to on, the error table gs_copy_error_log and log table gs_copy_summary_log are used to insert error records into the error table. For details about the error table, see Table 1 Error table gs_copy_error_log. For details about the log table, see Table 2.

-- Enable separation of duties.
gs_guc set  -Z datanode -N all -I all -c "enableSeparationOfDuty = on"

-- Disable separation of duties.
gs_guc set  -Z datanode -N all -I all -c "enableSeparationOfDuty = off"

-- When enable_copy_error_log is set to on, the error table gs_copy_error_log is used.
gs_guc reload -Z datanode -N all -I all -c "enable_copy_error_log = on"

If enableSeparationOfDuty is set to off, the user can be a common database user or an administrator. If the user is a common user, administrators need to grant permissions to the common user. An administrator can use it directly.

1. Create a user as an administrator.

   gaussdb=# CREATE USER load_user WITH PASSWORD '********';

2. (Optional) Grant permissions on the pg_catalog.gs_copy_summary_log table to the new user.
   

   • The public.gs_copy_summary table used by gs_loader is changed to pg_catalog.gs_copy_summary_log. The original public.gs_copy_summary table is discarded. In addition, the system view pg_catalog.gs_copy_summary is added to query data in pg_catalog.gs_copy_summary_log for query compatibility. The name of the public.gs_copy_summary table may be the same as that of a user table. Therefore, exercise caution when migrating data to the pg_catalog.gs_copy_summary_log table and make sure the table is not a user table. After data migration is complete, delete public.gs_copy_summary. Because pg_catalog.gs_copy_summary exists, running the \d(+) gsql meta-command will preferentially find pg_catalog.gs_copy_summary, though public.gs_copy_summary is not displayed in the list because it is a system view. You can directly reference public.gs_copy_summary.
   • The pg_catalog.gs_copy_summary_log table is compatible only with the SELECT operation and is incompatible with the DELETE and TRUNCATE operations.

   gaussdb=# GRANT INSERT,SELECT,DELETE ON  pg_catalog.gs_copy_summary_log To load_user;

3. (Optional) Grant permissions on the error table gs_copy_error_log to the new user.
   

   • The public.pgxc_copy_error_log table used by gs_loader is changed to pg_catalog.gs_copy_error_log. The original public.pgxc_copy_error_log table is discarded. In addition, the system view pg_catalog.pgxc_copy_error_log is added to query data in pg_catalog.gs_copy_error_log for query compatibility. The name of the public.pgxc_copy_error_log table may be the same as that of a user table. Therefore, exercise caution when migrating data to the pg_catalog.gs_copy_error_log table and make sure the table is not a user table. After data migration is complete, delete public.pgxc_copy_error_log. Because pg_catalog.pgxc_copy_error_log exists, running the \d(+) gsql meta-command will preferentially find pg_catalog.pgxc_copy_error_log, though it is not displayed in the list because it is a system view. You can directly reference public.pgxc_copy_error_log.
   • The pg_catalog.gs_copy_error_log table is compatible only with the SELECT operation and is incompatible with the DELETE and TRUNCATE operations.

   gaussdb=# GRANT INSERT,SELECT,DELETE ON  pg_catalog.gs_copy_error_log To load_user;

If enableSeparationOfDuty is set to on, the user can be a common database user or an administrator. Create the pgxc_copy_error_log and gs_copy_summary tables in their respective schemas and add indexes. No permission granting is required.

The pgxc_copy_error_log and gs_copy_summary tables always exist in the pg_catalog schema. In the setting of search_path, the pg_catalog schema takes precedence over user schemas. Therefore, to search for tables in a user schema by running the \d(+) gsql meta-command in the separation of duties scenario, explicitly specify the user schema.

1. Create a user as the initial user.

   gaussdb=# CREATE USER load_user WITH PASSWORD '********';

2. Switch to the new user as the initial user.

   gaussdb=# \c - load_user

3. (Optional) Create a gs_copy_summary table and add an index.
   

   If the GUC parameter enable_copy_error_log is not set (off by default) or is set to off, you do not need to use the log table and do not need to create it. Otherwise, you need to create the log table.

   gaussdb=# CREATE TABLE load_user.gs_copy_summary(relname varchar, begintime timestamptz, endtime timestamptz, id bigint, pid bigint, readrows bigint, skiprows bigint, loadrows bigint, errorrows bigint, whenrows bigint, allnullrows bigint, detail text);
   gaussdb=# CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);

4. (Optional) Create a pgxc_copy_error_log table and add an index.
   

   If the GUC parameter enable_copy_error_log is not set (off by default) or is set to off, you do not need to use the error table and do not need to create it. Otherwise, you need to create the error table.

   -- Create the pgxc_copy_error_log table.
   gaussdb=# CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text);
   
   -- Create an index.
   gaussdb=# CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);

Format

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

LOAD [ DATA ]
[CHARACTERSET char_set_name]
[INFILE [directory_path] [filename ] ]
[BADFILE [directory_path] [filename ] ]
[OPTIONS(name=value)]
[{ INSERT | APPEND | REPLACE | TRUNCATE }] 
INTO TABLE table_name
[{ INSERT | APPEND | REPLACE | TRUNCATE }] 
[FIELDS CSV]
[TERMINATED [BY] { 'string' }]
[OPTIONALLY ENCLOSED BY { 'string' }]
[TRAILING NULLCOLS]
[ WHEN { (start:end) | column_name } {= | !=} 'string' ]
[(
col_name [ [ POSITION ({ start:end }) ]  ["sql_string"] ] | [ FILLER [column_type [external] ] ] | [ CONSTANT "string" ] | [ SEQUENCE ( { COUNT | MAX | integer } [, incr] ) ]|[NULLIF (COL=BLANKS)]
[, ...]
)]

Parameters

• All parameters are in lowercase and are compatible with the gsql login mode, including -p port number, -h host, -d database, -U username, and -W password.
• OPTIONS, INFILE, and BADFILE are not supported. Syntax errors are not reported only in specific scenarios.
• gs_loader uses a .bad file to record errors from the rawrecord column in an error table if the GUC parameter enable_copy_error_log is set to enable the error table. If a raw record cannot be read due to unrecognized coding, the record will be marked as a blank line in the .bad file.
• When gs_loader sets the GUC parameter a_format_load_with_constraints_violation to support non-rollback upon constraint conflicts, if a table has a BEFORE/AFTER ROW INSERT trigger, a maximum of 10,000,000 rows can be committed at a time.
• gs_loader does not support statement-level triggers when the GUC parameter a_format_load_with_constraints_violation is set to support non-rollback upon constraint conflicts.
• When the rows parameter is specified, the number of commit times cannot exceed 1000. Otherwise, the performance is affected. The number of commit times is approximately equal to the number of data rows in the data file divided by the value of rows. If the rows parameter is not specified, there is no default value for rows. In this case, the transaction is committed only once after all data is imported to the table.
• Frequent commit of a small amount of data affects the data import performance. You are advised to set the rows parameter properly to ensure that the amount of data committed each time is greater than 5 MB. For common 16 vCPU 128 GB servers, in the scenario where one primary node and two standby nodes are deployed and 13 GB data is imported to a table with five columns. The rate of multiple commits is about 10 MB/s, which is basically the same as that of a single commit (5 MB data is committed each time).
• The compatible_nul parameter controls the value of the GUC parameter loader_support_nul_character.
  • compatible_nul=true corresponds to session-level set loader_support_nul_character='s2'.
  • compatible_nul=false corresponds to session-level set loader_support_nul_character='s1'.

    You are advised to set this parameter using the CLI. The priority of setting this parameter using compatible_nul is higher than that of GUC parameter setting.

• Currently, gs_loader supports compatibility only when data files contain NUL characters. It does not support NUL characters in .ctl control files. If the .ctl file contains the NUL character, unexpected problems may occur.
• When gs_loader is used to import data, if transcoding is not required, the maximum size of a single row of data is 1 GB minus 1 byte. If transcoding is required, the maximum size of a single row of data is 256 MB minus 1 byte. If the data volume in a single row is too large and the value of max_process_memory is too small, an error message is displayed, indicating that the memory is insufficient. In this case, you need to adjust the value of max_process_memory and try again.
• It is recommended that the size of a single file to be imported be less than or equal to 1 GB. gs_loader has no limit on the size of a single file to be imported. However, importing a large file is time-consuming. Therefore, you are advised to split a large data file, start multiple gs_loader processes to append data to the table. (If the truncate operation is required, run the truncate command separately instead of writing the truncate operation to the control file.) When the CPU resources are sufficient, this method can effectively improve the import speed.
• In a basic data migration scenario, you are advised to delete the index on the table and disable the trigger on the table. After the data migration is complete, rebuild the index and restore the trigger on the table. This is helpful to improve the import performance.
• After the binary parameter is set to true, the following requirements must be met:
  • The data file must be a binary file exported in \COPY binary mode. However, the data file exported in this mode has poor compatibility and portability. You are advised to use \COPY to import the data file.
  • gs_loader converts the syntax in the control file to the simplest syntax in \COPY binary mode, that is, \COPY table_name FROM 'binary_file_path' BINARY;. Only the import mode, table name, as well as control, data, binary, guc_param, and database connection parameters in the control file are parsed. Other parameters are not parsed and do not take effect.
  • The command lines and control files of gs_loader must meet the following requirements:
    • Character set configuration is not supported.
    • The WHEN filter and DISCARD operation are not supported.
    • If enable_copy_error_log is set to off, error data cannot be directly written into .bad files, and log data cannot be directly written into log files. If enable_copy_error_log is set to on, error data cannot be directly written into the error table, and log data cannot be directly written into the log table.
    • The CSV mode is not supported, delimiters and wrappers cannot be specified, and the TRAILING NULLCOLS syntax is not supported.
    • Data type configuration, POSITION configuration, and column expression usage are not supported.
    • The FILLER, CONSTANT, SEQUENCE, and NULLIF parameters are not supported.
    • The skip, rows, compatible_nul, compatible_illegal_chars, and parallel parameters are not supported.
• If parallel is set to a value greater than 1:
  • If binary is set to true, the setting of parallel becomes invalid and data is imported in serial mode.
  • OPTIONALLY ENCLOSED BY or FIELDS CSV cannot be set in the control file.
  • The SEQUENCE column cannot be set in the control file.
  • Data may not be imported in the sequence specified in the data file. If a table contains auto-increment columns, the sequence of values in the auto-increment columns may be different from that in the data file after the table is imported.
  • If the errors parameter is also used, the errors parameter indicates the maximum number of error lines allowed for each subtask.
  • If the skip parameter is also used, the skip parameter indicates the number of rows to be skipped at the beginning of the entire data file.
  • If the rows parameter is also used, the subtasks committed each batch are calculated independently.
  • When the CPU and memory resources of the client and the CPU and memory resources, idle threads, and network bandwidth of the server do not have bottlenecks and the total proportion of bad and discarded files does not exceed 1%, the performance is improved by at least 1.5, 3, or 5 times compared with serial import when the degree of parallelism is 2, 4, or 8.
  • Each time the degree of parallelism increases by 1, the required client memory increases by about 10 MB and the required server memory increases by about 35 MB.
• If the GUC parameter support_zero_character is set to on, character 0x00 can be written to and read from the database. When gs_loader imports data, 0x00 is imported in the original style instead of being converted to 0x20 due to other compatibility parameters. Regarding the processing of 0x00, the parameter priority is as follows: support_zero_character > copy_special_character_version > compatible_illegal_chars > loader_support_nul_character.
• When copy_special_character_version is set to 'no_error', the setting of copy_special_character_version is prior to that of compatible_illegal_chars. Invalid characters will be imported without conversion.
• You are advised to use digits, letters, Chinese characters, and file path separators ('/') for parameter values; other characters may lead to shell command parsing anomalies.

Examples

Example 1

1. (If the separation of duties function is disabled) For common users only:
   1. Create a user (as an administrator).

      gaussdb=# CREATE USER load_user WITH PASSWORD '********';

   2. (Optional) Grant permission on the gs_copy_summary_log table to the user (as an administrator).
      

      If the GUC parameter enable_copy_error_log is not set (off by default) or is set to off, you do not need to use the log table and do not need to create it. Otherwise, you need to create the log table.

      gaussdb=# GRANT INSERT,SELECT,DELETE ON  pg_catalog.gs_copy_summary_log To load_user;

   3. (Optional) Grant permission on the gs_copy_error_log table to the user (as an administrator).
      

      If the GUC parameter enable_copy_error_log is not set (off by default) or is set to off, you do not need to use the error table and do not need to create it. Otherwise, you need to create the error table.

      gaussdb=# GRANT INSERT,SELECT,DELETE ON  pg_catalog.gs_copy_error_log To load_user;

   4. Switch to another user.

      gaussdb=# \c - load_user

2. (If the separation of duties function is enabled) For common users and administrators:
   1. Create a user (as the initial user).

      gaussdb=# CREATE USER load_user WITH PASSWORD '********';

   2. Switch to the load_user user (as the initial user).

      gaussdb=# \c - load_user

   3. (Optional) Create a gs_copy_summary table and add an index.
      

      If the GUC parameter enable_copy_error_log is not set (off by default) or is set to off, you do not need to use the log table and do not need to create it. Otherwise, you need to create the log table.

      gaussdb=# CREATE TABLE load_user.gs_copy_summary(relname varchar, begintime timestamptz, endtime timestamptz, id bigint, pid bigint, readrows bigint, skiprows bigint, loadrows bigint, errorrows bigint, whenrows bigint, allnullrows bigint, detail text);
      gaussdb=# CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);

   4. (Optional) Create a pgxc_copy_error_log table and add an index.
      

      If the GUC parameter enable_copy_error_log is not set (off by default) or is set to off, you do not need to use the error table and do not need to create it. Otherwise, you need to create the error table.

      gaussdb=# CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text);
      gaussdb=# CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);

3. Create a table and a control file, and prepare a data file.

   Create the loader_tbl table.

   1 2 3 4 5 6 7

   gaussdb=# CREATE TABLE loader_tbl ( ID NUMBER, NAME VARCHAR2(20), CON VARCHAR2(20), DT DATE );

   (On the gs_loader client) Create the control file loader.ctl.

   1 2 3 4 5 6 7 8 9 10 11

   LOAD DATA truncate into table loader_tbl WHEN (2:2) = ',' fields terminated by ',' trailing nullcols ( id integer external, name char(32), con ":id || '-' || :name", dt date )

   (On the gs_loader client) Create the GUC parameter file guc.txt.

   1	set a_format_copy_version='s1';

   (On the gs_loader client) Create the data file data.csv.

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

   1,OK,,2007-07-8 2,OK,,2008-07-8 3,OK,,2009-07-8 4,OK,,2007-07-8 43,DISCARD,,2007-07-8 ,,, 32,DISCARD,,2007-07-8 a,ERROR int,,2007-07-8 8,ERROR date,,2007-37-8 ,,,, , 8,ERROR fields,,2007-37-8 ,,, 5,OK,,2021-07-30

4. Import the data.

   (On the gs_loader client) Before importing data, ensure that the gs_loader tool has the execute permission on the gs_loader tool. Ensure that the current path has the write permission on files. (The gs_loader generates some temporary files during the processing and automatically deletes them after the import is complete.)

   1	gs_loader control=loader.ctl data=data.csv db=testdb bad=loader.bad guc_param=guc.txt errors=5 port=8000 passwd=******** user=load_user

   Execution result:

   1 2 3 4 5 6

   gs_loader: version 0.1 5 Rows successfully loaded. log file is: loader.log

gs_copy_summary is used to record the called COPY syntax and its details. [badfile]_bad.log is used to record error data and its details. To prevent the error data and details recorded during the last import from being overwritten, you are advised to use different bad parameters for each import. If the error table is used to record error data and details, enable the GUC parameter enable_copy_error_log. To delete data from a table, perform the TRUNCATE or DELETE operation on the error and log tables.

Example 2

If the data in the .bad file is empty, refer to the source file and row number in the error table. (The code sequence is not identified, the .bad file content is not written, and only blank rows are recorded.)

-- For the file corresponding to the loader, search for the first row of the data text to find the source data.
loader=# select * from pgxc_copy_error_log;
       relname        |           begintime           | filename | lineno | rawrecord |                     detail                      
----------------------+-------------------------------+----------+--------+-----------+-------------------------------------------------
 public.test_gsloader | 2023-02-09 09:20:33.646843-05 | STDIN    |      1 |           | invalid byte sequence for encoding "UTF8": 0xb4
(1 row)

Example 3

NULLIF use cases:

-- Create the gsloader_test_nullif table.
gaussdb=# create table gsloader_test_nullif(
col1   varchar2(100) not null enable,
col2   number(5,0) not null enable,
col3   varchar2(200) not null enable,
col4   varchar2(34) not null enable,
col5   varchar2(750),
col6   number(20,0),
col7   varchar2(4000),
col8   varchar2(200)
);

-- View the data file test.csv.
6007 17060072021-09-0360070001102010000000230          1        600700010000218               0        1        1        229465        3
6007 17060072021-09-0360070001102010000000299          1        600700010000282               0        1        1        230467        3
6007 17060072021-09-0360070001102010000000242          1        600700010000255               0        1        1        226400        3
6007 17060072021-09-0360070001102010000000202          1        600700010000288               0        1        1        219107        3
6007 17060072021-09-0360070001102010000000294          1        600700010000243               0        1        1        204404        3
6007 17060072021-09-0360070001102010000000217          1        600700010000270               0        1        1        226644        3

-- View the control file test.ctl.
LOAD DATA  
CHARACTERSET UTF8
TRUNCATE
INTO TABLE gsloader_test_nullif
TRAILING NULLCOLS
(COL1 POSITION(1:10) CHAR NULLIF (COL1 = BLANKS),
COL2  POSITION(11:14) CHAR NULLIF (COL2 = BLANKS),
COL3  POSITION(21:30) CHAR NULLIF (COL3 = BLANKS),
COL4  POSITION(31:40) CHAR NULLIF (COL4 = BLANKS),
COL5  sysdate,
COL6,
COL7, 
COL8 POSITION(71:80) CHAR NULLIF (COL8 = BLANKS))

-- Import data.
gs_loader -p xxx host=xxx control=test.ctl  data=test.csv -d testdb -W xxx 

-- View the import result. The import is successful.
loader=# select * from gsloader_test_nullif;
    col1    | col2 |    col3    |    col4    |        col5         | col6 | col7 |   col8
------------+------+------------+------------+---------------------+------+------+-----------
 6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000218
 6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000282
 6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000255
 6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000288
 6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000243
 6007 17060 |  720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 |      |      | 010000270
(6 rows)

According to the data in the imported table, after the NULLIF keyword is used, the imported columns are normal except for the columns with the specified NULLIF and sysdate calculations. The imported columns without specified calculations are empty.

Example 4

The column type is not specified in the .ctl file, and the source data does not meet the column restrictions (data type and length restrictions) in the table.

-- Create a table.
gaussdb=# create table t_test(id int, text varchar(5));

-- View the data file test.csv.
addf2,bbbbaaa,20220907,

-- View the control file test.ctl.
Load Data
TRUNCATE INTO TABLE t_test
fields terminated by ','
TRAILING NULLCOLS(
id "length(trim(:id))",
text "replace(trim(:text),'bbbb','aa')"
)

-- Use guc_param to set the a_format_copy_version parameter.
cat test_guc.txt
set a_format_copy_version='s1';

-- Import data.
gs_loader -p xxx host=xxx control=test.ctl  data=test.csv -d testdb -W xxx guc_param=test_guc.txt

-- View the import result. The import is successful.
gaussdb=# select * from t_test;
 id | text  
----+-------
  5 | aaaaa
(1 row)

Example 5

The column type is not specified in the .ctl file, and the implicit type conversion is performed. (You are advised to add compatibility parameters because the implicit type conversion is involved.)

-- Create a table.
gaussdb=# create table test(mes int, mes1 text, mes2 float8, mes3 timestamp with time zone, mes4 INTEGER);

-- View the data file.
cat load_support_transform.data
1,mmoo,12.6789,Thu Jan 01 15:04:28 1970 PST,32767
2,yyds,180.883,Thu Jun 21 19:00:00 2012 PDT,32768

-- View the control file.
cat load_support_transform.ctl 
Load Data
TRUNCATE INTO TABLE test
fields terminated by ','
TRAILING NULLCOLS(
mes,
mes1 "mes1 || mes2",
mes2 "mes2 + 1",
mes3 "date_trunc('year', mes3)",
mes4
)

-- Use guc_param to set the a_format_copy_version parameter.
cat test_guc.txt
set a_format_copy_version='s1';
set a_format_dev_version='s2';
set a_format_version='10c';

-- Import data.
gs_loader -p xxx host=xxx control=load_support_transform.ctl data=load_support_transform.data -d testdb -W xxx guc_param=test_guc.txt 

-- View the import result. The import is successful.
gaussdb=# select * from test;
 mes |    mes1     |  mes2   |          mes3          | mes4  
-----+-------------+---------+------------------------+-------
   1 | mmoo12.6789 | 13.6789 | 1970-01-01 00:00:00+08 | 32767
   2 | yyds180.883 | 181.883 | 2012-01-01 00:00:00+08 | 32768

Example 6

-- Create a table.
gaussdb=# create table t_spec(col1 varchar(10), col2 varchar(10));

-- View the data file.
cat t_spec.txt
1234,5678,

-- View the control file.
cat t_spec.ctl
Load Data
TRUNCATE INTO TABLE t_spec
fields terminated by ','
TRAILING NULLCOLS(
col1 position(2:6) integer,
col2 position(5:8) smallint
)

-- Use guc_param to set the a_format_copy_version parameter.
cat test_guc.txt
set a_format_copy_version='s1';
set a_format_dev_version='s2';
set a_format_version='10c';

-- Import data.
gs_loader -p xxx host=xxx control=t_spec.ctl data=t_spec.txt -d testdb -W xxx guc_param=test_guc.txt

-- View the import result. The import is successful.
gaussdb=# select * from t_spec;
   col1    | col2
-----------+-------
 741618482 | 13612
(1 row)

Example 7

-- Create a table.
gaussdb=# create table t_raw(col raw(50));

-- View the data file.
cat t_raw.txt
12\n\x78!<~?'k^(%s)>/c[$50]

-- View the control file.
cat t_raw.ctl
Load Data
TRUNCATE INTO TABLE t_raw
TRAILING NULLCOLS(
col position(1:50) raw
)

-- Use guc_param to set the a_format_copy_version parameter.
cat test_guc.txt
set a_format_copy_version='s1';
set a_format_dev_version='s2';
set a_format_version='10c';

-- Import data.
gs_loader -p xxx host=xxx control=t_raw.ctl data=t_raw.txt -d testdb -W xxx guc_param=test_guc.txt

-- View the import result. The import is successful.
gaussdb=# select * from t_raw;
                          col
--------------------------------------------------------
 31325C6E5C783738213C7E3F276B5E282573293E2F635B2435305D
(1 row)