云数据库 GAUSSDB-LOAD DATA:示例

时间：2024-11-13 14:47:00

云数据库 GAUSSDB L

示例

--创建表。
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;