多模式下，数据导入导出范例

3.1.2.1    本地加载
建表语句
drop table t1;
CREATE TABLE t1 (col1 int(11),col2 varchar(10),col3 datetime); 
加载语句
LOAD DATA INFILE 'file://gbase:gbase@10.10.1.101//opt/load/t1.txt' INTO TABLE db1.t1 fields terminated by '|';
检查语句
select count (*) from db1.t1;
select * from db1.t1;

3.1.2.2    FTP协议
truncate table db1.t1;
LOAD DATA INFILE 'ftp://gbase:gbase@10.10.1.101//opt/load/t1.txt' INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select * from db1.t1;

3.1.2.3    SFTP协议
truncate table db1.t1;
LOAD DATA INFILE 'sftp://gbase:gbase@10.10.1.101//opt/load/t1.txt' INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select * from db1.t1;

3.1.2.4    HDP协议
truncate table db1.t1; 
LOAD DATA INFILE 'hdp://hadoop@gbase04.gbase.cn/load/t1.snappy' INTO TABLE db1.t1 FIELDS terminated by '|';
LOAD DATA INFILE 'hdp://hadoop@gbase04.gbase.cn/load/t1.txt' INTO TABLE db1.t1 FIELDS terminated by '|';
select count (*) from db1.t1;
select* from db1.t1;

3.1.2.5    HTTP协议
truncate table db1.t1;
LOAD DATA INFILE 'http://10.10.1.101/t1.txt' INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select * from db1.t1;

3.1.2.6    单表多协议混合并行加载   
truncate table db1.t1;
LOAD DATA INFILE 'ftp://gbase:gbase@10.10.1.101//opt/load/t1.txt,sftp://gbase:gbase@10.10.1.101//opt/load/t1.txt,http://gbase:gbase@10.10.1.101/t1.txt,hdp://hadoop@gbase04.gbase.cn/load/t1.snappy'  INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select* from db1.t1;

3.1.3.1    TXT格式
truncate table db1.t1;
LOAD DATA INFILE 'ftp://gbase:gbase@10.10.1.101//opt/load/t1.txt' INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select * from db1.t1;

3.1.3.2    CSV格式
truncate table db1.t1;
LOAD DATA INFILE 'ftp://gbase:gbase@10.10.1.101//opt/load/t1.csv' INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select * from db1.t1;

3.1.3.3    GZIP压缩格式
truncate table db1.t1;
LOAD DATA INFILE 'ftp://gbase:gbase@10.10.1.101//opt/load/t1.txt.gz' INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select * from db1.t1;

3.1.3.4    SNAPPY压缩格式
truncate table db1.t1;
LOAD DATA INFILE 'ftp://gbase:gbase@10.10.1.101//opt/load/t1.snappy' INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select * from db1.t1;

3.1.3.5    LZO压缩格式
truncate table db1.t1;
LOAD DATA INFILE 'ftp://gbase:gbase@10.10.1.101//opt/load/t1.txt.lzo' INTO TABLE db1.t1 fields terminated by '|';
select count (*) from db1.t1;
select * from db1.t1;

3.1.3.6    AVRO格式
建表语句
drop table t_avro;
drop table  t_json;
create table t_avro (         l_orderkey bigint,         l_partkey bigint,         l_suppkey bigint,         l_linenumber bigint,         l_quantity decimal(15,2),         l_extendedprice decimal(15,2),         l_discount decimal(15,2),         l_tax decimal(15,2),         l_returnflag char(1),         l_linestatus char(1),         l_shipdate date,         l_commitdate date,         l_receiptdate date,         l_shipinstruct char(25),         l_shipmode char(10),         l_comment varchar(50) ) distributed by ('l_orderkey');
create table t_json (OL_W_ID int,OL_D_ID int,OL_O_ID int,OL_NUMBER int,OL_I_ID int,OL_DELIVERY_D datetime,OL_AMOUNT int,OL_SUPPLY_W_ID int,OL_QUANTITY int, OL_DIST_INFO varchar(30));
数据文件
 
加载语句
load data infile 'ftp://gbase:gbase@10.10.1.101//opt/load/1.json' into table db1.t_json data_format 6 datetime format '%Y-%m-%d %H:%i:%s.%f' ;
load data infile 'ftp://gbase:gbase@10.10.1.101//opt/load/lineitem_10.tbl.avro' into table db1.t_avro data_format avro fields terminated by '|';
检查语句
select count (*) from db1.t_avro;
select* from db1.t_avro;
select count (*) from db1.t_json;
select* from db1.t_json;

3.1.4    不规整数据加载 
建表语句SQL1
create table tcplx (a int, b varchar(100), c datetime, d datetime  );
加载语句SQL2
LOAD DATA INFILE 'ftp://gbase:gbase@10.10.1.101//opt/load/tcplx.txt' INTO TABLE db1.tcplx DATA_FORMAT 3 FIELDS TERMINATED BY '#$%^&' table_fields 'a, b, c date "%H:%i:%s %Y-%m-%d", d ' autofill FIELDS ENCLOSED BY '\xFF' LINES TERMINATED BY '\x80'  null_value '%nu$ll%'  character set gbk  ignore 3 lines   ;
检查语句SQL3：
select * from 
    (select count(*) table_cnt from tcplx) a,
    (select count(*) b_null_cnt from tcplx where b is null) b,
    (select count(*) b_enter_cnt from tcplx where b like '%\n%') c,
    (select count(*) d_null_cnt from tcplx where d is null) d, 
    (select max(length(b)) b_max_lth,count(distinct(d)) d_dst_cnt from tcplx) e
1、    入库数据：10行
2、    Select * from tcplx;显示内容无乱码
3、    检查预期结果：
+-----------+------------+-------------+------------+-----------+-----------+
| table_cnt | b_null_cnt | b_enter_cnt | d_null_cnt | b_max_lth | d_dst_cnt |
+-----------+------------+-------------+------------+-----------+-----------+
|        10 |          5 |           5 |          2 |        87 |         8 |
+-----------+------------+-------------+------------+-----------+-----------+
table_cnt如果不为10，则数据未全部加载成功；
b_null_cnt如果不为5，则null值未正常处理；
b_enter_cnt如果不为5，则数据中的回车未正常处理；
d_null_cnt如果不为2，则缺失的字段未正常补齐；
b_max_lth如果不为87，则属于不支持GBK编码；
d_dst_cnt如果不为8，则缺失的字段补齐不正确；

3.1.5    混合传输协议、混合文件格式加载
1、规整数据混合加载
truncate table  db1.tcplx;
LOAD DATA INFILE 'http://10.10.1.101/tcplx1.gz,hdp://hadoop@gbase04.gbase.cn/load/tcplx1.lzo,hdp://hadoop@gbase04.gbase.cn/load/tcplx1.snappy,ftp://gbase:gbase@10.10.1.101//opt/load/test/regular_data/tcplx1.txt,sftp://gbase:gbase@10.10.1.101//opt/load/test/regular_data/tcplx1.csv' INTO TABLE db1.tcplx DATA_FORMAT 3  null_value '\\N' fields terminated by '\t';
select * from 
    (select count(*) regular_cnt from db1.tcplx where a>=11 and a <=25) b,
    (select count(*) reg_gzp_cnt from db1.tcplx where a>=11 and a <=13) c,
    (select count(*) reg_lzo_cnt from db1.tcplx where a>=14 and a <=16) d, 
    (select count(*) reg_spy_cnt from db1.tcplx where a>=17 and a <=19) e,
    (select count(*) reg_txt_cnt from db1.tcplx where a>=20 and a <=22) f,
    (select count(*) reg_csv_cnt from db1.tcplx where a>=23 and a <=25) g;
2、不规整数据混合加载测试
truncate table  db1.tcplx;
LOAD DATA INFILE 'http://10.10.1.101/tcplx.txt.gz,hdp://hadoop@gbase04.gbase.cn/load/tcplx.txt.lzo,ftp://gbase:gbase@10.10.1.101//opt/load/test/not_regular_data/tcplx.txt,sftp://gbase:gbase@10.10.1.101//opt/load/test/not_regular_data/tcplx.txt.snappy' INTO TABLE db1.tcplx DATA_FORMAT 3 FIELDS TERMINATED BY '#$%^&' table_fields 'a, b, c date "%H:%i:%s %Y-%m-%d", d ' autofill FIELDS ENCLOSED BY '\xFF' LINES TERMINATED BY '\x80'  null_value '%nu$ll%'  character set gbk;
select a, count(*) table_cnt from tcplx where  a>=1 and a <=10  group by a ;

3、混合加载事务一致性测试
truncate table  db1.tcplx;
LOAD DATA INFILE 'http://10.10.1.101/tcplx1.gz,hdp://hadoop@gbase04.gbase.cn/load/tcplx1.lzo,hdp://hadoop@gbase04.gbase.cn/load/tcplx1.snappy,ftp://gbase:gbase@10.10.1.101//opt/load/test/regular_data/tcplx4.txt,sftp://gbase:gbase@10.10.1.101//opt/load/test/regular_data/tcplx1.csv' INTO TABLE db1.tcplx DATA_FORMAT 3  null_value '\\N' fields terminated by '\t';
select count(*) from db1.tcplx;

LOAD DATA INFILE 'http://10.10.1.101/tcplx1.gz,hdp://hadoop@gbase04.gbase.cn/load/tcplx1.lzo,hdp://hadoop@gbase04.gbase.cn/load/tcplx1.snappy,ftp://gbase:gbase@10.10.1.101//opt/load/test/regular_data/tcplx4.txt,sftp://gbase:gbase@10.10.1.101//opt/load/test/regular_data/tcplx1.csv' INTO TABLE db1.tcplx DATA_FORMAT 3  null_value '\\N' fields terminated by '\t';
一段时间后中断

---------orc导出到本地：---------
select * from t into outfile '/tmp/t1.orc';
---------orc本地文件加载：---------

LOAD DATA INFILE 'ftp://gbase:gbase@172.16.6.100//tmp/t1.orc,ftp://gbase:gbase@172.16.6.101//tmp/t1.orc,ftp://gbase:gbase@172.16.6.102//tmp/t1.orc' 
INTO TABLE test.k1 
DATA_FORMAT orc  ;

---------orc导出到hdfs：--------- 注意，ERROR 1708 (HY000): [172.16.6.100:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01EX-700) Gbase general error: I/O operation on hdp://gbase@172.16.6.100:50070/tmp/t1_1.orc failed with error - write mismatch: write count and expected count did not match, File name hdp://gbase@172.16.6.100:50070/tmp/t1_1.orc
SQL: SELECT /*172.16.6.100_1391_11_2023-11-20_16:20:48*/ /*+ TID('393610') */ `sysvc.test.t`.`id` AS `id`, `sysvc.test.t`.`name` AS `name`, `sysvc.test.t`.`sj` AS `sj` INTO OUTFILE 'hdp://gbase@172.16.6.100:500

select * from t into outfile 'hdp://gbase@172.16.6.100:50070/tmp/t1.orc' OUTFILEMODE BY HDFS;

---------orc hdfs文件加载：   ---------  用户名，端口可不加。如果变了得加。
LOAD DATA INFILE 'hdp://gbase@172.16.6.100:50070//tmp/t1.orc' 
INTO TABLE test.k1 
DATA_FORMAT orc  ;

---------parquet 导出到本地： --------- 导出不是parquet格式
select * from t into outfile '/tmp/t1.parquet' ;

---------parquet 本地文件加载：--------- 
LOAD DATA INFILE 'ftp://gbase:gbase@172.16.6.100//tmp/11.parquet'  、
INTO TABLE test.ttt  
DATA_FORMAT parquet ;

---------parquet 导出到hdfs： ---------导出的不是parquet格式。
select * from t into outfile 'hdp://gbase@172.16.6.100:50070/tmp/t1.parquet' OUTFILEMODE BY HDFS;

---------parquet hdfs文件加载：--------- 
LOAD DATA INFILE 'hdp://gbase@172.16.6.100:50070//tmp/11.parquet' 
INTO TABLE test.ttt
DATA_FORMAT parquet  ;

---------read 外部表：---------
create read external table rft_hdp(a varchar(10),b varchar(10),c datetime) 
location 'hdp://172.16.6.100/tmp/t1.orc' informat data_format orc outformat;

create read external table rft_ftp(a varchar(10),b varchar(10),c datetime) 
location 'ftp://gbase:gbase@172.16.6.100//tmp/t1.orc' informat data_format orc outformat;

---------write 外部表：---------
create write external table wft_hdp(a varchar(10),b varchar(10),c datetime) 
location 'hdp://gbase@172.16.6.100/tmp/t3.orc'  informat data_format orc outformat;

create write external table wft_ftp(a varchar(10),b varchar(10),c datetime) 
location 'ftp://gbase:gbase@172.16.6.100//tmp/t2.orc'  informat data_format orc outformat;