github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/table/external_table.sql (about) 1 --env prepare statement 2 drop table if exists ex_table_1; 3 drop table if exists ex_table_2_1; 4 drop table if exists ex_table_2_2; 5 drop table if exists ex_table_2_3; 6 drop table if exists ex_table_2_4; 7 drop table if exists ex_table_2_5; 8 drop table if exists ex_table_2_6; 9 drop table if exists ex_table_2_7; 10 drop table if exists ex_table_2_8; 11 drop table if exists ex_table_2_9; 12 drop table if exists ex_table_2_10; 13 drop table if exists ex_table_2_11; 14 drop table if exists ex_table_2_12; 15 drop table if exists ex_table_2_13; 16 drop table if exists ex_table_2_14; 17 drop table if exists ex_table_2_15; 18 drop table if exists ex_table_2_16; 19 drop table if exists ex_table_2_17; 20 drop table if exists ex_table_2_18; 21 drop table if exists ex_table_2_19; 22 drop table if exists ex_table_3; 23 drop table if exists ex_table_3_1; 24 drop table if exists ex_table_3_2; 25 drop table if exists ex_table_3_3; 26 drop table if exists ex_table_3_4; 27 drop table if exists ex_table_3_5; 28 drop table if exists ex_table_31; 29 drop table if exists ex_table_4; 30 drop table if exists ex_table_5; 31 drop table if exists ex_table_6; 32 drop table if exists ex_table_6a; 33 drop table if exists ex_table_7; 34 drop table if exists ex_table_8; 35 drop table if exists ex_table_9; 36 drop table if exists ex_table_10; 37 drop table if exists ex_table_10a; 38 drop table if exists ex_table_11; 39 drop table if exists ex_table_12; 40 drop table if exists ex_table_13; 41 drop table if exists ex_table_14; 42 drop table if exists ex_table_text; 43 drop table if exists ex_table_log; 44 drop table if exists ex_table_gzip; 45 drop table if exists ex_table_bzip2; 46 drop table if exists ex_table_lz4; 47 drop table if exists ex_table_auto; 48 drop table if exists ex_table_none; 49 drop table if exists ex_table_nocomp; 50 drop table if exists ex_table_cp1; 51 drop table if exists ex_table_cp2; 52 drop table if exists ex_table_15; 53 drop table if exists ex_table_drop; 54 drop table if exists table_15; 55 drop table if exists table_16; 56 drop table if exists ex_table_yccs; 57 drop table if exists ex_table_null; 58 59 --覆盖各数值类型正常值,极值，空值 60 create external table ex_table_1(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_number.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 61 select * from ex_table_1; 62 63 --覆盖各数值类型异常值：非法值中文字符特殊字符，超出范围的值 64 create external table ex_table_2_1(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_1.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 65 select * from ex_table_2_1; 66 create external table ex_table_2_2(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_2.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 67 select * from ex_table_2_2; 68 create external table ex_table_2_3(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_3.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 69 select * from ex_table_2_3; 70 create external table ex_table_2_4(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_4.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 71 select * from ex_table_2_4; 72 create external table ex_table_2_5(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_5.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 73 select * from ex_table_2_5; 74 create external table ex_table_2_6(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_6.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 75 select * from ex_table_2_6; 76 create external table ex_table_2_7(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_7.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 77 select * from ex_table_2_7; 78 create external table ex_table_2_8(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_8.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 79 select * from ex_table_2_8; 80 create external table ex_table_2_9(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_9.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 81 select * from ex_table_2_9; 82 create external table ex_table_2_10(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_10.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 83 select * from ex_table_2_10; 84 create external table ex_table_2_11(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_11.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 85 select * from ex_table_2_11; 86 create external table ex_table_2_12(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_12.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 87 select * from ex_table_2_12; 88 create external table ex_table_2_13(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_13.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 89 select * from ex_table_2_13; 90 create external table ex_table_2_14(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_14.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 91 select * from ex_table_2_14; 92 create external table ex_table_2_15(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_15.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 93 select * from ex_table_2_15; 94 create external table ex_table_2_16(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_16.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 95 select * from ex_table_2_16; 96 create external table ex_table_2_17(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_17.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 97 select * from ex_table_2_17; 98 create external table ex_table_2_18(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_18.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 99 select * from ex_table_2_18; 100 create external table ex_table_2_19(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_19.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 101 select * from ex_table_2_19; 102 103 104 --覆盖字符数字中文特殊字符 105 create external table ex_table_3(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_char.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 106 select char_1,char_2,date_1,date_2 from ex_table_3; 107 --增加text类型 108 set time_zone='SYSTEM'; 109 --覆盖非法值，超出范围值 110 create external table ex_table_3_1(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_3_1.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 111 select * from ex_table_3_1; 112 create external table ex_table_3_2(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_3_2.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 113 select * from ex_table_3_2; 114 create external table ex_table_3_3(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_3_3.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 115 select * from ex_table_3_3; 116 create external table ex_table_3_4(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_3_4.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 117 select * from ex_table_3_4; 118 create external table ex_table_3_5(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_3_5.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 119 select * from ex_table_3_5; 120 121 create external table ex_table_31(clo1 tinyint default 8,clo2 smallint null,clo3 int not null,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(clo1))infile{"filepath"='$resources/external_table_file/ex_table_3_6.csv'}; 122 select clo1,clo2,clo3,clo4,clo5,clo6,clo7,clo8,col9,col10,col11,col12,col13,col15,col16,col17,col18,col19,col20 from ex_table_31; 123 124 create external table ex_table_4(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 125 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_1.csv'} fields terminated by '|' enclosed by '\"' lines terminated by '\n'; 126 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_4; 127 create external table ex_table_5(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_2.csv'} fields terminated by '|' lines terminated by '\n'; 128 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_5; 129 create external table ex_table_6(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 130 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_3.csv'} fields terminated by '*' enclosed by '\"' lines terminated by '\n'; 131 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_6; 132 create external table ex_table_6a(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 133 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_11.csv'} fields terminated by '\t' enclosed by '\"' lines terminated by '\n'; 134 select clo1,clo5,clo7,col12,col13,col16 from ex_table_6a; 135 136 --异常值分隔符封闭符#，\r,\n 137 create external table ex_table_7(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 138 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_4.csv'} fields terminated by '#' enclosed by '\"' lines terminated by '\n'; 139 select * from ex_table_7; 140 create external table ex_table_8(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 141 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_5.csv'} fields terminated by '\n' enclosed by '\"' lines terminated by '\n'; 142 select * from ex_table_8; 143 create external table ex_table_9(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 144 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_6.csv'} fields terminated by '\r' enclosed by '\"' lines terminated by '\n'; 145 select * from ex_table_9; 146 147 --异常值字段数据包含封闭符 148 create external table ex_table_10(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 149 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_7.csv'} fields terminated by '' lines terminated by '\n'; 150 select * from ex_table_10; 151 152 --文件中分隔符和封闭符和create指定不匹配 153 create external table ex_table_10a(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 154 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_1.csv'} fields terminated by ',' enclosed by '@' lines terminated by '\n'; 155 select * from ex_table_10a; 156 create external table ex_table_11(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 157 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_8.csv'} fields terminated by '|' lines terminated by '\n'; 158 select * from ex_table_11; 159 160 --缺省换行符 161 create external table ex_table_12(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 162 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_9.csv'} fields terminated by ',' enclosed by '\"' ; 163 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_12; 164 --换行符为\r 165 create external table ex_table_13(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 166 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_10.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\r\n'; 167 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_13; 168 --缺省fields terminated ，terminated，ENCLOSED(默认分隔符，封闭符"") 169 create external table ex_table_14(clo1 tinyint primary key,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_10.csv'}; 170 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_14; 171 --text 172 create external table ex_table_text(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_char.text'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 173 select char_1,char_2,date_1,date_2 from ex_table_text; 174 --log 175 create external table ex_table_log(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_char.log'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 176 select char_1,char_2,date_1,date_2 from ex_table_log; 177 178 --gzip 179 create external table ex_table_gzip(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_gzip.gz',"compression"='gzip'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 180 select * from ex_table_gzip; 181 182 --bzip2 183 create external table ex_table_bzip2(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_bzip.bz2',"compression"='bz2'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 184 select char_1,char_2,date_1,date_2 from ex_table_bzip2; 185 186 --lz4 187 create external table ex_table_lz4(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_lz4.lz4',"compression"='lz4'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 188 select * from ex_table_lz4; 189 190 --auto 191 create external table ex_table_auto(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_lz4.lz4',"compression"='auto'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 192 select * from ex_table_auto; 193 194 --none 195 create external table ex_table_none(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date, 196 col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255)) infile{"filepath"='$resources/external_table_file/ex_table_sep_8.csv',"compression"='none'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 197 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_none; 198 199 --缺省compression 200 create external table ex_table_nocomp(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_bzip.bz2'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 201 select char_1,char_2,date_1,date_2 from ex_table_nocomp; 202 --异常：压缩格式不对应，未压缩文件 203 create external table ex_table_cp1(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_gzip.gz',"compression"='lz4'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 204 select * from ex_table_cp1; 205 create external table ex_table_cp2(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_number.csv',"compression"='lz4'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 206 select * from ex_table_cp2; 207 208 --insert into internal table select from external table 209 create external table ex_table_15(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_1.csv'} fields terminated by '|' enclosed by '' lines terminated by '\n'; 210 --create table table_15 as select * from ex_table_15; 211 create table table_15(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255)); 212 insert into table_15 select * from ex_table_15; 213 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from table_15; 214 215 --drop外部表后再创建 216 create external table ex_table_drop(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_1.csv'} fields terminated by '|' enclosed by '' lines terminated by '\n'; 217 drop table ex_table_drop; 218 create external table ex_table_drop(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/ex_table_sep_1.csv'} fields terminated by '|' enclosed by '' lines terminated by '\n'; 219 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_drop; 220 221 --内部表和外部表关联 222 select count(*) from ex_table_15,table_15 where ex_table_15.clo1=table_15.clo1; 223 --异常测试:insert/update/delete 224 create external table ex_table_yccs(char_1 char(20),char_2 varchar(10),date_1 date,date_2 datetime,date_3 timestamp)infile{"filepath"='$resources/external_table_file/ex_table_char.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 225 insert into ex_table_yccs select 'yellow','apple','2020-09-30','2020-09-30 10:20:08','2020-09-30 10:20:08.09834'; 226 update ex_table_yccs set char_1="cat123"; 227 delete from ex_table_yccs; 228 229 create table table_16(a int); 230 insert into table_16 values(99); 231 select * from table_16, ex_table_gzip; 232 233 create external table ex_table_space(a varchar(200), b varchar(200), c varchar(200)) infile{"filepath"='$resources/external_table_file/ex_table_space.csv'} fields terminated by '|' enclosed by '\"' lines terminated by '\n'; 234 select length(a), length(b), length(c) from ex_table_space; 235 236 create table t1(a varchar(100), b varchar(100), c varchar(100)); 237 load data infile '$resources/external_table_file/ex_table_space.csv' into table t1 fields terminated by '|' enclosed by '\"' lines terminated by '\n'; 238 select length(a), length(b), length(c) from t1; 239 240 create external table ex_table_null( col1 int, col2 float, col3 varchar, col4 blob, col6 date, col7 bool )infile{"filepath"='$resources/external_table_file/ex_table_null.csv'} fields terminated by ',' enclosed by '"' lines terminated by '\n' set col3=nullif(col3,'null'); 241 select max(col3), min(col3) from ex_table_null; 242 243 drop table if exists ex_table_1; 244 drop table if exists ex_table_2_1; 245 drop table if exists ex_table_2_2; 246 drop table if exists ex_table_2_3; 247 drop table if exists ex_table_2_4; 248 drop table if exists ex_table_2_5; 249 drop table if exists ex_table_2_6; 250 drop table if exists ex_table_2_7; 251 drop table if exists ex_table_2_8; 252 drop table if exists ex_table_2_9; 253 drop table if exists ex_table_2_10; 254 drop table if exists ex_table_2_11; 255 drop table if exists ex_table_2_12; 256 drop table if exists ex_table_2_13; 257 drop table if exists ex_table_2_14; 258 drop table if exists ex_table_2_15; 259 drop table if exists ex_table_2_16; 260 drop table if exists ex_table_2_17; 261 drop table if exists ex_table_2_18; 262 drop table if exists ex_table_2_19; 263 drop table if exists ex_table_3; 264 drop table if exists ex_table_3_1; 265 drop table if exists ex_table_3_2; 266 drop table if exists ex_table_3_3; 267 drop table if exists ex_table_3_4; 268 drop table if exists ex_table_3_5; 269 drop table if exists ex_table_31; 270 drop table if exists ex_table_4; 271 drop table if exists ex_table_5; 272 drop table if exists ex_table_6; 273 drop table if exists ex_table_6a; 274 drop table if exists ex_table_7; 275 drop table if exists ex_table_8; 276 drop table if exists ex_table_9; 277 drop table if exists ex_table_10; 278 drop table if exists ex_table_10a; 279 drop table if exists ex_table_11; 280 drop table if exists ex_table_12; 281 drop table if exists ex_table_13; 282 drop table if exists ex_table_14; 283 drop table if exists ex_table_text; 284 drop table if exists ex_table_log; 285 drop table if exists ex_table_gzip; 286 drop table if exists ex_table_bzip2; 287 drop table if exists ex_table_lz4; 288 drop table if exists ex_table_auto; 289 drop table if exists ex_table_none; 290 drop table if exists ex_table_nocomp; 291 drop table if exists ex_table_cp1; 292 drop table if exists ex_table_cp2; 293 drop table if exists ex_table_15; 294 drop table if exists ex_table_drop; 295 drop table if exists table_15; 296 drop table if exists ex_table_yccs; 297 drop table if exists ex_table_space; 298 drop table if exists t1; 299 drop table if exists ex_table_null;