云数据库 GaussDB-LOAD DATA:示例

时间：2025-02-12 15:08:14

云数据库 GaussDB

示例

--创建表。gaussdb=# CREATE TABLE load_data_tbl1(load_col1 INT UNIQUE, load_col2 INT, load_col3 CHAR(10));--向表中插入一条数据。gaussdb=#  INSERT INTO load_data_tbl1 VALUES(0,0,'load0');--从文件/home/omm/load1.csv中复制数据到load_data_tbl表,指定列名，设置.load_col3列值统一为"load"。gaussdb=#  LOAD DATA INFILE '/home/omm/load1.csv' INTO TABLE load_data_tbl1(load_col1, load_col2) SET load_col3 = 'load';--后面导入数据load_col3列值均为'load'gaussdb=#  SELECT * FROM load_data_tbl1; load_col1 | load_col2 | load_col3-----------+-----------+------------         0 |         0 | load0         3 |         3 | load         1 |         1 | load         2 |         2 | load(4 rows)--从文件/home/omm/load2.csv中复制数据到load_data_tbl表.，指定IGNORE忽略冲突gaussdb=# LOAD DATA INFILE '/home/omm/load2.csv' IGNORE INTO TABLE load_data_tbl1;--表load_data_tbl1中数据不变，冲突数据跳过。gaussdb=#  SELECT * FROM load_data_tbl1; load_col1 | load_col2 | load_col3-----------+-----------+------------         0 |         0 | load0         3 |         3 | load         1 |         1 | load         2 |         2 | load(4 rows)--创建分区表gaussdb=# 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。gaussdb=# LOAD DATA INFILE '/home/omm/load3.csv' INTO TABLE load_data_tbl2 PARTITION (load_p2);--数据导入到load_data_tbl2表中指定分区gaussdb=#  SELECT * FROM load_data_tbl2; load_col_col1 | load_col_col2---------------+---------------             4 |             4             5 |             5(2 rows)--创建表gaussdb=# CREATE TABLE load_data_tbl3(load_col_col1 CHAR(30));--从文件/home/omm/load4.csv中复制数据到load_data_tbl3表.，指定FIELDS ENCLOSED BY;gaussdb=# LOAD DATA INFILE '/home/omm/load4.csv' INTO TABLE load_data_tbl3 FIELDS ENCLOSED BY '"';--数据"load test quote"双引号被去掉，'load test single_quote'单引号保留gaussdb=#  select * from load_data_tbl3;         load_col_col1-------------------------------- load test quote 'load test single_quote'(2 rows)--删除表。gaussdb=# drop table load_data_tbl1;gaussdb=# DROP TABLE load_data_tbl2;gaussdb=# DROP TABLE load_data_tbl3;