LOAD DATA

功能描述

将文件中的数据导入到数据库指定表中。

注意事项

• LOAD DATA语法需要具有表的INSERT和DELETE权限。
• 当参数enable_copy_server_files关闭时，只允许初始用户执行LOAD DATA命令；当参数enable_copy_server_files打开时，允许具有SYSADMIN权限的用户或继承内置角色gs_role_copy_files权限的用户执行LOAD DATA命令，但默认禁止对数据库配置文件、密钥文件、证书文件和审计日志执行LOAD DATA命令，防止用户越权查看或修改敏感文件。
• 当参数enable_copy_server_files打开时，管理员可以通过GUC参数safe_data_path设置普通用户可以导入/导出的路径，但设置的路径必须为设置的safe_data_path的子路径；未设置GUC参数safe_data_path时（默认情况），不对普通用户使用的路径进行拦截。
• 执行LOAD DATA语法写入表中的数据若无法转换为表中数据类型格式时，LOAD DATA导入数据将执行失败。
• LOAD DATA只能用于表，不能用于视图。

语法格式

LOAD DATA
    [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

参数说明

• LOCAL

  指定导入文件的位置。

  不指定LOCAL时，若'file_name'为相对路径，则默认导入路径为数据目录；

  若指定LOCAL参数则需要指定'file_name'为绝对路径，当指定为相对路径时默认导入路径为数据库二进制所在路径，即$GAUSSHOME/bin/。

  

  • 当导入数据与表中数据冲突或文件中字段数小于指定表中字段数时，指定LOCAL与指定IGNORE作用一致。
  • 当前LOAD DATA仅支持从服务端导入文件到数据库中。
• REPLACE | IGNORE

  当导入数据与表中原有数据冲突时，若指定REPLACE，则替换冲突行数据；若指定IGNORE则跳过冲突行数据，继续导入。若数据冲突但不指定REPLACE，IGNORE或LOCAL中任意一个则终止导入并报错。

  

  若文件字段数小于指定表列数，指定LOCAL或IGNORE参数会为剩余列赋默认值。不指定IGNORE或LOCAL参数会报错。

• PARTITION

  当导入表为分区表时，此参数用来指定分区。若数据与指定分区范围不一致则报错。

• CHARACTER SET

  指定数据文件的编码格式名称，缺省为当前客户端编码格式。

• FIELDS | COLUMNS
  • TERMINATED BY

    指定两列之间分隔符，缺省为'\t'。

    

    指定换行符不能与分隔符相同。

  • [OPTIONALLY] ENCLOSED BY

    指定引号字符，缺省为''。

    OPTIONALLY参数为可选参数，无实际作用。

    引号符仅支持单字符，不支持字符串。

  • ESCAPED BY

    指定转义符，缺省为'\'。

    转义字符仅支持单字符，不支持字符串。

• LINES
  • STARTING BY

    指定导入数据文件起始字段样式。

  • TERMINATED BY

    指定导入数据文件换行符样式。

• IGNORE

  指定数据导入时，跳过数据文件的前 number行。

• col_name_or_user_var

  可选的待复制字段列表。

  取值范围：如果没有声明字段列表，将使用所有字段。

  

  • 指定列参数不支持重复指定列。
  • LOAD DATA语法指定列时，col_name_or_user_var支持指定为表中存在列或用户变量。
• SET

  指定列值，可以指定为表达式或DEFAULT。

  

  • 表达式中不支持列名。
  • 若表达式结果类型与被赋值列对应类型之间不存在隐式转换函数则报错。

示例

-- 准备待导入的文件。
m_db=# CREATE TABLE load_data_tmp1(a int, b int);
m_db=# INSERT INTO load_data_tmp1 VALUES(1,1),(2,2),(3,3);
m_db=# \copy load_data_tmp1 to '/home/omm/load1.csv';
m_db=# CREATE TABLE load_data_tmp2(a int, b int);
m_db=# INSERT INTO load_data_tmp2 VALUES(1,2);
m_db=# \copy load_data_tmp2 to '/home/omm/load2.csv';
m_db=# CREATE TABLE load_data_tmp3(a int, b int);
m_db=# INSERT INTO load_data_tmp3 VALUES(4,4),(5,5);
m_db=# \copy load_data_tmp3 to '/home/omm/load3.csv';
m_db=# CREATE TABLE load_data_tmp4(a char(50));
m_db=# INSERT INTO load_data_tmp4 VALUES('"load test quote"'), ('\'load test single_quote\'');
m_db=# \copy load_data_tmp4 to '/home/omm/load4.csv';

-- 创建表。
m_db=# CREATE TABLE load_data_tbl1(load_col1 INT UNIQUE, load_col2 INT, load_col3 CHAR(10));

-- 向表中插入一条数据。
m_db=# INSERT INTO load_data_tbl1 VALUES(0,0,'load0');

-- 从文件/home/omm/load1.csv中复制数据到load_data_tbl表，指定列名，设置load_col3列值统一为load。
m_db=# LOAD DATA INFILE '/home/omm/load1.csv' INTO TABLE load_data_tbl1(load_col1, load_col2) SET load_col3 = 'load';

-- 后续操作导入数据，load_col3列值均为'load'。
m_db=# SELECT * FROM load_data_tbl1 ORDER BY load_col1;
 load_col1 | load_col2 | load_col3
-----------+-----------+-----------
         0 |         0 | load0
         1 |         1 | load
         2 |         2 | load
         3 |         3 | load
(4 rows)

-- 从文件/home/omm/load2.csv中复制数据到load_data_tbl表，指定IGNORE忽略冲突。
m_db=# LOAD DATA INFILE '/home/omm/load2.csv' IGNORE INTO TABLE load_data_tbl1;

-- 表load_data_tbl1中数据不变，冲突数据跳过。
m_db=# SELECT * FROM load_data_tbl1 ORDER BY load_col1;
 load_col1 | load_col2 | load_col3
-----------+-----------+-----------
         0 |         0 | load0
         1 |         1 | load
         2 |         2 | load
         3 |         3 | load
(4 rows)

-- 创建分区表
m_db=# CREATE TABLE load_data_tbl2
(
    load_col_col1 INT,
    load_col_col2 INT
) PARTITION BY RANGE (load_col_col2)
(
    PARTITION load_p1 VALUES LESS THAN(3),
    PARTITION load_p2 VALUES LESS THAN(9),
    PARTITION load_p3 VALUES LESS THAN(MAXVALUE)
);

-- 从文件/home/omm/load3.csv中复制数据到load_data_tbl2表，指定PARTITION。
m_db=# LOAD DATA INFILE '/home/omm/load3.csv' INTO TABLE load_data_tbl2 PARTITION (load_p2);

-- 数据导入到load_data_tbl2表中指定分区。
m_db=# SELECT * FROM load_data_tbl2;
 load_col_col1 | load_col_col2
---------------+---------------
             4 |             4
             5 |             5
(2 rows)

-- 创建表。
m_db=# CREATE TABLE load_data_tbl3(load_col_col1 CHAR(30));

-- 从文件/home/omm/load4.csv中复制数据到load_data_tbl3表，指定FIELDS ENCLOSED BY。
m_db=# LOAD DATA INFILE '/home/omm/load4.csv' INTO TABLE load_data_tbl3 FIELDS ENCLOSED BY '"';

-- 数据"load test quote"双引号被去掉，'load test single_quote'单引号保留。
m_db=# select * from load_data_tbl3;
         load_col_col1
--------------------------------
 load test quote
 'load test single_quote'
(2 rows)

-- 删除表。
m_db=# DROP TABLE load_data_tmp1;
m_db=# DROP TABLE load_data_tmp2;
m_db=# DROP TABLE load_data_tmp3;
m_db=# DROP TABLE load_data_tmp4;
m_db=# DROP TABLE load_data_tbl1;
m_db=# DROP TABLE load_data_tbl2;
m_db=# DROP TABLE load_data_tbl3;