gs_loader

Overview

• 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.
• Before using gs_loader, ensure that the gs_loader version is consistent with the gsql version and database version.

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 package to the created directory.

   Upload the gsql tool package GaussDB-Kernel_Database version number_OS version number_64bit_gsql.tar.gz (the EulerOS tool package is used as an example) in the software installation package to the directory created in the previous step.

3. Go to the new directory 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 corresponds to the gsql tool version. You can directly query the gsql client version.

   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();

Log Level Configuration

Set the log level for developers to view. After the setting, the tool running information is printed on the console.

Permission

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

• If enableSeparationOfDuty is set to off:

  The user can be a common user or the database administrator. If the user is a common user, the administrator needs to grant permissions to the common user. The administrator account can be used directly.

  Create a user.

  CREATE USER load_user WITH PASSWORD '************';

  Create related tables and grant permissions.

  GRANT ALL ON FUNCTION copy_error_log_create() TO load_user;
  GRANT ALL ON SCHEMA public TO load_user;
  SELECT copy_error_log_create();
  SELECT copy_summary_create();
  GRANT ALL PRIVILEGES ON  public.pgxc_copy_error_log To load_user;
  GRANT ALL PRIVILEGES ON  public.gs_copy_summary To load_user;

• If enableSeparationOfDuty is set to on:

  The user can be a common user or the database administrator. Create the pgxc_copy_error_log and gs_copy_summary tables in their respective schemas and add indexes. No permission granting is required.

  Create a user.

  CREATE USER load_user WITH PASSWORD '********';

  Create related tables and add indexes.

  CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text);
  CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);
  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);
  CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);

Usage Environment

You need to add the tool path to PATH. gs_loader supports SSL-based encrypted communication. The method of using gs_loader is the same as that of using gsql.

Adding System Catalogs

The gs_copy_summary table is added to record the COPY execution result summary, including the number of successful rows, number of error rows, number of ignored rows, and number of empty rows.

The copy_summary_create function is added to create the gs_copy_summary table.

The format of the gs_copy_summary table is as follows:

relname     | public.sqlldr_tbl
begintime   | 2021-09-03 16:00:11.7129-04
endtime     | 2021-09-03 16:00:15.259908-04
id          | 21870
pid         | 47582725060352
readrows    | 100000
skiprows    | 0
loadrows    | 111
errorrows   | 0
whenrows    | 99889
allnullrows | 0
detail      | 111 Rows successfully loaded.
            | 0 Rows not loaded due to data errors.
            | 99889 Rows not loaded because all WHEN clauses were failed.
            | 0 Rows not loaded because all fields were null.
            | 

Usage Guidelines

1. Create a user and related tables, and add indexes.
   • If the separation of duties function is disabled for common users only:

     Create a user.

     CREATE USER load_user WITH PASSWORD '************';

     Create related tables and grant permissions.

     

     The gs_copy_summary and pgxc_copy_error_log tables cannot contain objects that may cause privilege escalation, such as RULE, TRIGGER, index functions, row-level security, CHECK constraints, GENERATED columns, DEFAULT columns, and ON UPDATE columns; otherwise, the system considers that the user is created by a malicious user, reports an error, and exits.

     GRANT ALL ON FUNCTION copy_error_log_create() TO load_user;
     GRANT ALL ON SCHEMA public TO load_user;
     SELECT copy_error_log_create();
     SELECT copy_summary_create();
     GRANT ALL PRIVILEGES ON  public.pgxc_copy_error_log To load_user;
     GRANT ALL PRIVILEGES ON  public.gs_copy_summary To load_user;

   • If the separation of duties function is enabled for common users and administrators:

     Create a user.

     CREATE USER load_user WITH PASSWORD '********';

     Create related tables and add indexes.

     CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text);
     CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);
     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);
     CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);

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

   Create the loader_tbl table.

   1 2 3 4 5 6 7

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

   Create the loader.ctl control file.

   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 )

   Create the data.csv data file.

   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

3. Import the data.

   Before importing data, ensure that the gs_loader tool has the execute permission. 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 errors=5 port=8000 passwd=************ user=load_user

   Execution result:

   1 2 3 4 5 6

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

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.
• gs_loader uses a .bad file to record errors coming from the rawrecord column in an error table. The error table does not record rawrecord if an error cannot be read by certain code. In this case, a blank line is recorded in the .bad file.

Control Files

• Syntax

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

  LOAD [ DATA ] [CHARACTERSET char_set_name] [INFILE [directory_path] [filename ] ] [BADFILE [directory_path] [filename ] ] [{ 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)] [, ...] )]