github.com/matrixorigin/matrixone@v0.7.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;
    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 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;
    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 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;
    58  select * from t2;
    59  delete from t2;
    60  
    61  
    62  load data infile '$resources/load_data/char_varchar_3.csv' into table t2;
    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;
    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;
    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 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;
   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;
   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;
   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 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;
   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;
   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;
   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  load data infile '$resources/load_data/auto_increment_2.csv' into table t6;
   172  select * from t6;
   173  
   174  drop table t6;
   175  
   176  create table t7(
   177  col1 int,
   178  col2 int,
   179  col3 int
   180  );
   181  load data infile '$resources/load_data/auto_increment_2.csv' into table t7 parallel 'true';
   182  select * from t7 order by col1;
   183  
   184  drop table t7;
   185  
   186  create table t8(a int, b int);
   187  load data infile '$resources/load_data/auto_increment_20.csv' into table t7 set col2=nullif(col2, '1');