github.com/matrixorigin/matrixone@v1.2.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 drop table if exists ex_empty_table; 59 drop table if exists join_table; 60 61 --覆盖各数值类型正常值,极值，空值 62 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'; 63 select * from ex_table_1; 64 65 --覆盖各数值类型异常值：非法值中文字符特殊字符，超出范围的值 66 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'; 67 select * from ex_table_2_1; 68 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'; 69 select * from ex_table_2_2; 70 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'; 71 select * from ex_table_2_3; 72 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'; 73 select * from ex_table_2_4; 74 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'; 75 select * from ex_table_2_5; 76 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'; 77 select * from ex_table_2_6; 78 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'; 79 select * from ex_table_2_7; 80 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'; 81 select * from ex_table_2_8; 82 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'; 83 select * from ex_table_2_9; 84 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'; 85 select * from ex_table_2_10; 86 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'; 87 select * from ex_table_2_11; 88 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'; 89 select * from ex_table_2_12; 90 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'; 91 select * from ex_table_2_13; 92 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'; 93 select * from ex_table_2_14; 94 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'; 95 select * from ex_table_2_15; 96 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'; 97 select * from ex_table_2_16; 98 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'; 99 select * from ex_table_2_17; 100 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'; 101 select * from ex_table_2_18; 102 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'; 103 select * from ex_table_2_19; 104 105 106 --覆盖字符数字中文特殊字符 107 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'; 108 select char_1,char_2,date_1,date_2 from ex_table_3; 109 --增加text类型 110 set time_zone='SYSTEM'; 111 --覆盖非法值，超出范围值 112 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'; 113 select * from ex_table_3_1; 114 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'; 115 select * from ex_table_3_2; 116 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'; 117 select * from ex_table_3_3; 118 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'; 119 select * from ex_table_3_4; 120 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'; 121 select * from ex_table_3_5; 122 123 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'} fields terminated by ','; 124 select clo1,clo2,clo3,clo4,clo5,clo6,clo7,clo8,col9,col10,col11,col12,col13,col15,col16,col17,col18,col19,col20 from ex_table_31; 125 126 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, 127 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'; 128 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_4; 129 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'; 130 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_5; 131 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, 132 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'; 133 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_6; 134 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, 135 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'; 136 select clo1,clo5,clo7,col12,col13,col16 from ex_table_6a; 137 138 --异常值分隔符封闭符#，\r,\n 139 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, 140 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'; 141 select * from ex_table_7; 142 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, 143 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'; 144 select * from ex_table_8; 145 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, 146 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'; 147 select * from ex_table_9; 148 149 --异常值字段数据包含封闭符 150 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, 151 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'; 152 select * from ex_table_10; 153 154 --文件中分隔符和封闭符和create指定不匹配 155 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, 156 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'; 157 select * from ex_table_10a; 158 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, 159 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'; 160 select * from ex_table_11; 161 162 --缺省换行符 163 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, 164 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 '\"' ; 165 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_12; 166 --换行符为\r 167 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, 168 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'; 169 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_13; 170 --缺省fields terminated ，terminated，ENCLOSED(默认分隔符，封闭符"") 171 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'} fields terminated by ','; 172 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_14; 173 --text 174 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'; 175 select char_1,char_2,date_1,date_2 from ex_table_text; 176 --log 177 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'; 178 select char_1,char_2,date_1,date_2 from ex_table_log; 179 180 --gzip 181 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'; 182 select * from ex_table_gzip; 183 184 --bzip2 185 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'; 186 select char_1,char_2,date_1,date_2 from ex_table_bzip2; 187 188 --lz4 189 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'; 190 select * from ex_table_lz4; 191 192 --auto 193 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'; 194 select * from ex_table_auto; 195 196 --none 197 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, 198 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'; 199 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_none; 200 201 --缺省compression 202 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'; 203 select char_1,char_2,date_1,date_2 from ex_table_nocomp; 204 --异常：压缩格式不对应，未压缩文件 205 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'; 206 select * from ex_table_cp1; 207 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'; 208 select * from ex_table_cp2; 209 210 --insert into internal table select from external table 211 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'; 212 --create table table_15 as select * from ex_table_15; 213 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)); 214 insert into table_15 select * from ex_table_15; 215 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from table_15; 216 217 --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 drop table ex_table_drop; 220 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'; 221 select clo1,clo5,clo7,col12,col13,col16,col17,col18 from ex_table_drop; 222 223 --内部表和外部表关联 224 select count(*) from ex_table_15,table_15 where ex_table_15.clo1=table_15.clo1; 225 --异常测试:insert/update/delete 226 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'; 227 insert into ex_table_yccs select 'yellow','apple','2020-09-30','2020-09-30 10:20:08','2020-09-30 10:20:08.09834'; 228 update ex_table_yccs set char_1="cat123"; 229 delete from ex_table_yccs; 230 231 create table table_16(a int); 232 insert into table_16 values(99); 233 select * from table_16, ex_table_gzip; 234 235 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'; 236 select length(a), length(b), length(c) from ex_table_space; 237 238 create table t1(a varchar(100), b varchar(100), c varchar(100)); 239 load data infile '$resources/external_table_file/ex_table_space.csv' into table t1 fields terminated by '|' enclosed by '\"' lines terminated by '\n'; 240 select length(a), length(b), length(c) from t1; 241 242 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'); 243 select max(col3), min(col3) from ex_table_null; 244 245 --empty table for join 246 create external table ex_empty_table(a int, b int)infile{"filepath"='noexistfile.csv'} fields terminated by ',' enclosed by '"' lines terminated by '\n'; 247 create table join_table(a int, b int); 248 insert into join_table values(1,1), (2,2); 249 select * from ex_empty_table join join_table on ex_empty_table.a = join_table.a; 250 251 drop table if exists ex_table_1; 252 drop table if exists ex_table_2_1; 253 drop table if exists ex_table_2_2; 254 drop table if exists ex_table_2_3; 255 drop table if exists ex_table_2_4; 256 drop table if exists ex_table_2_5; 257 drop table if exists ex_table_2_6; 258 drop table if exists ex_table_2_7; 259 drop table if exists ex_table_2_8; 260 drop table if exists ex_table_2_9; 261 drop table if exists ex_table_2_10; 262 drop table if exists ex_table_2_11; 263 drop table if exists ex_table_2_12; 264 drop table if exists ex_table_2_13; 265 drop table if exists ex_table_2_14; 266 drop table if exists ex_table_2_15; 267 drop table if exists ex_table_2_16; 268 drop table if exists ex_table_2_17; 269 drop table if exists ex_table_2_18; 270 drop table if exists ex_table_2_19; 271 drop table if exists ex_table_3; 272 drop table if exists ex_table_3_1; 273 drop table if exists ex_table_3_2; 274 drop table if exists ex_table_3_3; 275 drop table if exists ex_table_3_4; 276 drop table if exists ex_table_3_5; 277 drop table if exists ex_table_31; 278 drop table if exists ex_table_4; 279 drop table if exists ex_table_5; 280 drop table if exists ex_table_6; 281 drop table if exists ex_table_6a; 282 drop table if exists ex_table_7; 283 drop table if exists ex_table_8; 284 drop table if exists ex_table_9; 285 drop table if exists ex_table_10; 286 drop table if exists ex_table_10a; 287 drop table if exists ex_table_11; 288 drop table if exists ex_table_12; 289 drop table if exists ex_table_13; 290 drop table if exists ex_table_14; 291 drop table if exists ex_table_text; 292 drop table if exists ex_table_log; 293 drop table if exists ex_table_gzip; 294 drop table if exists ex_table_bzip2; 295 drop table if exists ex_table_lz4; 296 drop table if exists ex_table_auto; 297 drop table if exists ex_table_none; 298 drop table if exists ex_table_nocomp; 299 drop table if exists ex_table_cp1; 300 drop table if exists ex_table_cp2; 301 drop table if exists ex_table_15; 302 drop table if exists ex_table_drop; 303 drop table if exists table_15; 304 drop table if exists ex_table_yccs; 305 drop table if exists ex_table_space; 306 drop table if exists t1; 307 drop table if exists ex_table_null; 308 drop table if exists ex_empty_table; 309 drop table if exists join_table;