github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/load_data/load_data.sql (about)

     1  
     2  -- test load data, integer numbers
     3  drop table if exists t1;
     4  create table t1(
     5  col1 tinyint,
     6  col2 smallint,
     7  col3 int,
     8  col4 bigint,
     9  col5 tinyint unsigned,
    10  col6 smallint unsigned,
    11  col7 int unsigned,
    12  col8 bigint unsigned
    13  );
    14  
    15  -- load data
    16  load data infile '$resources/load_data/integer_numbers_1.csv' into table t1 fields terminated by ',';
    17  select * from t1;
    18  
    19  -- into outfile
    20  select * from t1 into outfile '$resources/into_outfile/outfile_integer_numbers_1.csv';
    21  delete from t1;
    22  
    23  -- load data
    24  load data infile '$resources/into_outfile/outfile_integer_numbers_1.csv' into table t1 fields terminated by ',' ignore 1 lines;
    25  select * from t1;
    26  delete from t1;
    27  
    28  load data infile '$resources/load_data/integer_numbers_2.csv' into table t1 fields terminated by'*';
    29  select * from t1;
    30  delete from t1;
    31  
    32  drop table t1;
    33  
    34  
    35  -- test load data, char varchar type
    36  drop table if exists t2;
    37  create table t2(
    38  col1 char(225),
    39  col2 varchar(225),
    40  col3 text,
    41  col4 varchar(225)
    42  );
    43  
    44  -- load data
    45  load data infile '$resources/load_data/char_varchar_1.csv' into table t2 fields terminated by ',';
    46  select * from t2;
    47  
    48  -- into outfile
    49  select * from t2 into outfile '$resources/into_outfile/outfile_char_varchar_1.csv';
    50  delete from t2;
    51  
    52  -- load data
    53  load data infile '$resources/into_outfile/outfile_char_varchar_1.csv' into table t2 fields terminated by ',' ignore 1 lines;
    54  select * from t2;
    55  delete from t2;
    56  
    57  load data infile '$resources/load_data/char_varchar_2.csv' into table t2 fields terminated by ',';
    58  select * from t2;
    59  delete from t2;
    60  
    61  
    62  load data infile '$resources/load_data/char_varchar_3.csv' into table t2 fields terminated by ',';
    63  select * from t2;
    64  delete from t2;
    65  
    66  load data infile '$resources/load_data/char_varchar_4.csv' into table t2 fields terminated by'|';
    67  select * from t2;
    68  delete from t2;
    69  
    70  load data infile '$resources/load_data/char_varchar_5.csv' into table t2 fields terminated by'?';
    71  select * from t2;
    72  delete from t2;
    73  
    74  drop table t2;
    75  
    76  
    77  -- test load data, float type double type
    78  drop table if exists t3;
    79  create table t3(
    80  col1 float,
    81  col2 double,
    82  col3 decimal(5,2),
    83  col4 decimal(20,5)
    84  );
    85  
    86  insert into t3 values (1.3,1.3,1.3,1.3);
    87  select * from t3;
    88  load data infile '$resources/load_data/float_1.csv' into table t3 fields terminated by ',';
    89  select * from t3;
    90  delete from t3;
    91  
    92  -- load data
    93  load data infile '$resources/load_data/float_2.csv' into table t3 fields terminated by ',';
    94  select * from t3;
    95  
    96  -- into outfile
    97  select * from t3 into outfile '$resources/into_outfile/outfile_float_2.csv';
    98  delete from t3;
    99  
   100  -- load data
   101  load data infile '$resources/into_outfile/outfile_float_2.csv' into table t3 fields terminated by ',' ignore 1 lines;
   102  select * from t3;
   103  delete from t3;
   104  
   105  load data infile '$resources/load_data/float_3.csv' into table t3 fields terminated by ',';
   106  
   107  drop table t3;
   108  
   109  -- test load data, Time and Date type
   110  drop table if exists t4;
   111  create table t4(
   112  col1 date,
   113  col2 datetime,
   114  col3 timestamp,
   115  col4 bool
   116  );
   117  set time_zone = 'SYSTEM';
   118  load data infile '$resources/load_data/time_date_1.csv' into table t4 fields terminated by ',';
   119  select * from t4;
   120  delete from t4;
   121  
   122  -- load data
   123  load data infile '$resources/load_data/time_date_2.csv' into table t4 fields terminated by ',';
   124  select * from t4;
   125  
   126  -- into outfile
   127  select * from t4 into outfile '$resources/into_outfile/outfile_time_date_2.csv';
   128  delete from t4;
   129  
   130  -- load data
   131  load data infile '$resources/into_outfile/outfile_time_date_2.csv' into table t4 fields terminated by ',' ignore 1 lines;
   132  select * from t4;
   133  delete from t4;
   134  
   135  load data infile '$resources/load_data/time_date_3.csv' into table t4 fields terminated by ',';
   136  delete from t4;
   137  
   138  load data infile '$resources/load_data/time_date_4.csv' into table t4 fields terminated by';';
   139  select * from t4;
   140  delete from t4;
   141  
   142  load data infile '$resources/load_data/time_date_5.csv' into table t4 fields terminated by ',';
   143  select * from t4;
   144  delete from t4;
   145  
   146  drop table t4;
   147  
   148  -- test load data, auto_increment
   149  drop table if exists t5;
   150  create table t5(
   151  col1 int auto_increment primary key,
   152  col2 int,
   153  col3 int
   154  );
   155  
   156  insert into t5 values (1,1,1);
   157  -- echo duplicate
   158  load data infile '$resources/load_data/auto_increment_1.csv' into table t5 fields terminated by ',';
   159  select * from t5;
   160  
   161  drop table t5;
   162  
   163  drop table if exists t6;
   164  create table t6(
   165  col1 int auto_increment primary key,
   166  col2 int,
   167  col3 int
   168  );
   169  
   170  -- echo duplicate
   171  -- @bvt:issue#3433
   172  load data infile '$resources/load_data/auto_increment_2.csv' into table t6 fields terminated by ',';
   173  select * from t6;
   174  -- @bvt:issue
   175  load data infile '$resources/load_data/auto_increment_2.csv' into table t6 FIELDS ESCAPED BY '\\' TERMINATED BY ',';
   176  load data infile '$resources/load_data/auto_increment_2.csv' into table t6 FIELDS TERMINATED BY ',' LINES STARTING BY 'aaa';
   177  drop table t6;
   178  
   179  create table t7(
   180  col1 int,
   181  col2 int,
   182  col3 int
   183  );
   184  load data infile '$resources/load_data/auto_increment_2.csv' into table t7 fields terminated by ',' parallel 'true';
   185  select * from t7 order by col1;
   186  
   187  drop table t7;
   188  
   189  create table t8(a int, b int);
   190  load data infile '$resources/load_data/auto_increment_20.csv' into table t7 fields terminated by ',' set col2=nullif(col2, '1');
   191  
   192  create table t9(a varchar, b varchar, c varchar, d varchar);
   193  load data infile {"filepath"="$resources/load_data/parallel.txt.gz", "compression"="gzip"} into table t9 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
   194  select * from t9;
   195  load data infile {"filepath"="$resources/load_data/parallel.txt.gz", "compression"="gzip"} into table t9 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' parallel 'true';
   196  select * from t9;
   197  
   198  create account if not exists `abc2` admin_name 'user' identified by '111';
   199  -- @session:id=1&user=abc2:user:accountadmin&password=111
   200  create database if not exists ssb;
   201  use ssb;
   202  create table test_table(
   203  col1 int AUTO_INCREMENT,
   204  col2 float,
   205  col3 bool,
   206  col4 Date,
   207  col5 varchar(255),
   208  col6 text,
   209  PRIMARY KEY (`col1`)
   210  );
   211  load data infile '$resources/load_data/test_1.csv' into table test_table fields terminated by ',' parallel 'true';
   212  select * from test_table;
   213  drop table test_table;
   214  drop database ssb;
   215  -- @session
   216  drop account `abc2`;
   217  
   218  drop table if exists t1;
   219  create table t1(
   220  col1 char(225),
   221  col2 varchar(225),
   222  col3 text,
   223  col4 varchar(225)
   224  );
   225  
   226  load data infile '$resources/load_data/char_varchar_5.csv' into table t1 fields terminated by'?';
   227  delete from t1;
   228  load data infile '$resources/load_data/char_varchar_5.csv' into table t1 fields terminated by'?';
   229  delete from t1;
   230  load data infile '$resources/load_data/char_varchar_5.csv' into table t1 fields terminated by'?';
   231  delete from t1;
   232  drop table t1;
   233  
   234  drop table if exists t1;
   235  create table t1 (col1 int);
   236  load data infile '$resources/load_data/test_character.csv' into table t1 CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   237  select * from t1;
   238  load data infile '$resources/load_data/test_character.csv' into table t1 CHARACTER SET utf_8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   239  select * from t1;
   240  load data infile '$resources/load_data/test_character.csv' into table t1 CHARACTER SET gbk FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   241  select * from t1;
   242  load data infile '$resources/load_data/test_character.csv' into table t1 CHARACTER SET utf_16 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   243  select * from t1;
   244  load data infile '$resources/load_data/test_character.csv' into table t1 CHARACTER SET utf_xx FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   245  select * from t1;
   246  load data infile '$resources/load_data/test_character.csv' into table t1 CHARACTER SET "utf-xx" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   247  select * from t1;
   248  load data infile '$resources/load_data/test_character.csv' into table t1 CHARACTER SET "utf-16" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   249  select * from t1;
   250  drop table t1;
   251  
   252  drop table if exists t1;
   253  create table t1(col1 int, col2 varchar(10));
   254  load data infile '$resources/load_data/test_character01.csv' into table t1 CHARACTER SET abcd FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   255  select * from t1;
   256  load data infile '$resources/load_data/test_character01.csv' into table t1 CHARACTER SET utf_8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   257  select * from t1;
   258  load data infile '$resources/load_data/test_character01.csv' into table t1 CHARACTER SET "utf-16" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   259  select * from t1;
   260  load data infile '$resources/load_data/test_character01.csv' into table t1 CHARACTER SET "utf_xx" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
   261  select * from t1;
   262  drop table t1;
   263  
   264  drop table if exists test01;
   265  create table test01(col1 int, col2 varchar(20));
   266  load data infile '$resources/load_data/test_starting_by02.csv' into table test01 CHARACTER SET "utf_8" fields terminated by ',' lines starting by 'cha';
   267  select * from test01;
   268  drop table test01;
   269  
   270  drop table if exists test02;
   271  create table test02(col1 int, col2 bigint, col3 varchar(30));
   272  load data infile '$resources/load_data/test_starting_by03.csv' into table test02 fields terminated by '|' lines starting by '1' terminated by '\n';
   273  select * from test02;
   274  drop table test02;
   275  
   276  -- default starting by string ''
   277  drop table if exists test03;
   278  create table test03(col1 varchar(20), col2 varchar(20));
   279  load data infile '$resources/load_data/test_starting_by01.csv' into table test03 CHARACTER SET "utf_8" fields terminated by ',' lines terminated by '\n';
   280  select * from test03;
   281  drop table test03;
   282  
   283  drop table if exists test04;
   284  create table test04 (col1 varchar(20), col2 varchar(60));
   285  load data infile '$resources/load_data/test_escaped_by01.csv' into table test04 fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n';
   286  select * from test04;
   287  drop table test04;
   288  
   289  drop table if exists test05;
   290  create table test05 (col1 varchar(20), col2 varchar(60));
   291  load data infile '$resources/load_data/test_escaped_by02.csv' into table test05 fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n';
   292  select * from test05;
   293  drop table test05;
   294  
   295  -- @bvt:issue#15110
   296  drop table if exists test06;
   297  create table test06(col1 varchar(20), col2 varchar(20));
   298  load data infile '$resources/load_data/test_enclosed_by01.csv' into table test06 fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n';
   299  select * from test06;
   300  drop table test06;
   301  -- @bvt:issue
   302  
   303  drop table if exists test07;
   304  create table test07(col1 varchar(20), col2 varchar(20));
   305  load data infile '$resources/load_data/test_enclosed_by02.csv' into table test07 fields terminated by ',' enclosed by '`' lines terminated by '\n';
   306  select * from test07;
   307  drop table test07;
   308  
   309  -- @bvt:issue#15110
   310  drop table if exists test08;
   311  create table test08 (col1 varchar(20), col2 varchar(20));
   312  load data infile '$resources/load_data/test_enclosed_by01.csv' into table test08 fields terminated by ',' enclosed by '`' lines terminated by '\n';
   313  select * from test08;
   314  drop table test08;
   315  -- @bvt:issue
   316  
   317  drop table if exists test09;
   318  create table test09(col1 varchar(20), col2 varchar(20));
   319  load data infile '$resources/load_data/test_starting_by04.csv' into table test09 CHARACTER SET "utf_8" fields terminated by ',' lines starting by ' ';
   320  select * from test09;
   321  drop table test09;
   322  
   323  drop table if exists test10;
   324  create table test10(col1 text, col2 text);
   325  load data infile {'filepath'='$resources/load_data/text.csv.tar.gz', 'compression'='tar.gz'} into table test10 FIELDS  ENCLOSED BY '"' TERMINATED BY "," LINES TERMINATED BY '\n' parallel 'true';
   326  select * from test10;
   327  
   328  load data infile {'filepath'='$resources/load_data/text.csv.tar.bz2', 'compression'='tar.bz2'} into table test10 FIELDS  ENCLOSED BY '"' TERMINATED BY "," LINES TERMINATED BY '\n' parallel 'true';
   329  select * from test10;
   330  drop table test10;