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

     1  set time_zone = 'SYSTEM';
     2  drop table if exists t1;
     3  create table t1(col1 bool,col2 int,col3 varchar(100), col4 date,col5 datetime,col6 timestamp,col7 decimal,col8 float,col9 json,col10 text,col11 json,col12 bool);
     4  load data infile {'filepath'='$resources/load_data/jsonline_object.jl','format'='jsonline','jsondata'='object'} into table t1;
     5  select * from t1;
     6  delete from t1;
     7  load data infile {'filepath'='$resources/load_data/jsonline_array.jl','format'='jsonline','jsondata'='array'} into table t1;
     8  load data infile {'filepath'='$resources/load_data/jsonline_array.jl.bz2','format'='jsonline','jsondata'='array','compression'='bzip2'} into table t1;
     9  load data infile {'filepath'='$resources/load_data/jsonline_array.jl.gz','format'='jsonline','jsondata'='array','compression'='gzip'} into table t1;
    10  load data infile {'filepath'='$resources/load_data/jsonline_array.jl.bz2','jsondata'='array'} into table t1;
    11  load data infile {'filepath'='$resources/load_data/jsonline_array.jl.gz','jsondata'='array'} into table t1;
    12  select * from t1;
    13  
    14  -- jsonline integer numbers,include null and extremum
    15  drop table if exists jsonline_t1;
    16  create table jsonline_t1(
    17  col1 tinyint,
    18  col2 smallint,
    19  col3 int,
    20  col4 bigint,
    21  col5 tinyint unsigned,
    22  col6 smallint unsigned,
    23  col7 int unsigned,
    24  col8 bigint unsigned
    25  );
    26  load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t1;
    27  select * from jsonline_t1;
    28  -- into outfile
    29  select * from jsonline_t1 into outfile '$resources/into_outfile/json_outfile_integer_numbers_1.csv';
    30  select * from jsonline_t1;
    31  delete from jsonline_t1;
    32  load data infile '$resources/into_outfile/json_outfile_integer_numbers_1.csv' into table jsonline_t1 fields terminated by ',' ignore 1 lines;
    33  select * from jsonline_t1;
    34  delete from jsonline_t1;
    35  load data infile{'filepath'='$resources/load_data/integer_numbers_1_array.jl','format'='jsonline','jsondata'='array'}into table jsonline_t1;
    36  select * from jsonline_t1;
    37  
    38  -- jsonline char varchar type and include Chinese and special symbols
    39  drop table if exists jsonline_t2;
    40  create table jsonline_t2(
    41  col1 char(225),
    42  col2 varchar(225),
    43  col3 text,
    44  col4 varchar(225)
    45  );
    46  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t2;
    47  select * from jsonline_t2;
    48  -- into outfile
    49  select * from jsonline_t2 into outfile '$resources/into_outfile/json_outfile_char_varchar_1.csv';
    50  select * from jsonline_t2;
    51  delete from jsonline_t2;
    52  -- load data
    53  load data infile '$resources/into_outfile/json_outfile_char_varchar_1.csv' into table jsonline_t2 fields terminated by ',' ignore 1 lines;
    54  select * from jsonline_t2;
    55  delete from jsonline_t2;
    56  load data infile{'filepath'='$resources/load_data/char_varchar_1_array.json','format'='jsonline','jsondata'='array'}into table jsonline_t2;
    57  select * from jsonline_t2;
    58  
    59  -- jsonline float type double type
    60  drop table if exists jsonline_t3;
    61  create table jsonline_t3(
    62  col1 float,
    63  col2 double,
    64  col3 decimal(38,16),
    65  col4 decimal(38,16)
    66  );
    67  load data infile{'filepath'='$resources/load_data/float_1.json','format'='jsonline','jsondata'='object'}into table jsonline_t3;
    68  select * from jsonline_t3;
    69  truncate table jsonline_t3;
    70  load data infile{'filepath'='$resources/load_data/float_1_array.jl','format'='jsonline','jsondata'='array'}into table jsonline_t3;
    71  select * from jsonline_t3;
    72  -- jsonline Time and Date type
    73  drop table if exists jsonline_t4;
    74  create table jsonline_t4(
    75  col1 date,
    76  col2 datetime(6),
    77  col3 timestamp(3),
    78  col4 bool
    79  );
    80  set time_zone = 'SYSTEM';
    81  load data infile{'filepath'='$resources/load_data/time_date_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t4;
    82  select * from jsonline_t4;
    83  -- into outfile
    84  select * from jsonline_t4 into outfile '$resources/into_outfile/json_outfile_time_date_1.csv';
    85  -- load data
    86  load data infile '$resources/into_outfile/json_outfile_time_date_1.csv' into table jsonline_t4 fields terminated by ',' ignore 1 lines;
    87  select * from jsonline_t4;
    88  truncate table jsonline_t4;
    89  load data infile{'filepath'='$resources/load_data/time_date_1_array.jl','format'='jsonline','jsondata'='array'}into table jsonline_t4;
    90  select * from jsonline_t4;
    91  
    92  --Abnormal test: The json key and column do not correspond
    93  create table jsonline_t5 (a char(225),b varchar(225),c text,d varchar(225));
    94  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t5;
    95  select * from jsonline_t5;
    96  drop table jsonline_t5;
    97  create table jsonline_t5 (col1 char(225),col2 varchar(225),col3 text,col4 varchar(225),col5 int default 10);
    98  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t5;
    99  
   100  --The number of json keys is inconsistent with the number of columns
   101  create table jsonline_t6 (col1 char(225),col2 varchar(225),col4 varchar(225));
   102  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t6;
   103  select * from jsonline_t6 where col1='a';
   104  
   105  --Abnormal test:filepath,format,jsondata,compression
   106  create table  jsonline_t7 (col1 char(225),col2 varchar(225),col3 text,col4 varchar(225));
   107  load data infile{'filepath'='$resources/load_data/char1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t7;
   108  load data infile{'filepath'='','format'='jsonline','jsondata'='object'}into table jsonline_t7;
   109  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='array'}into table jsonline_t7;
   110  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline'}into table jsonline_t7;
   111  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','compression'='none','jsondata'='object'}into table jsonline_t7;
   112  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','compression'='gzip','jsondata'='object'}into table jsonline_t7;
   113  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','jsondata'='object'}into table jsonline_t7;
   114  drop table jsonline_t7;
   115  create table jsonline_t7(col1 float,col2 double,col3 decimal(38,16),col4 decimal(38,16));
   116  load data infile{'filepath'='$resources/load_data/float_1.json','format'='csv'} into table jsonline_t7 fields terminated by ',';
   117  load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t7;
   118  
   119  --column constraint test
   120  create table jsonline_t8(
   121  col1 tinyint not null primary key,
   122  col2 smallint,
   123  col3 int,
   124  col4 bigint,
   125  col5 tinyint unsigned,
   126  col6 smallint unsigned,
   127  col7 int unsigned,
   128  col8 bigint unsigned
   129  );
   130  load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t8;
   131  drop table jsonline_t8;
   132  create table jsonline_t8(
   133  col1 tinyint default 10,
   134  col2 smallint,
   135  col3 int,
   136  col4 bigint,
   137  col5 tinyint unsigned,
   138  col6 smallint unsigned,
   139  col7 int unsigned,
   140  col8 bigint unsigned
   141  );
   142  load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t8;
   143  drop table jsonline_t8;
   144  create table jsonline_t8(
   145  col1 tinyint primary key,
   146  col2 smallint,
   147  col3 int,
   148  col4 bigint,
   149  col5 tinyint unsigned,
   150  col6 smallint unsigned,
   151  col7 int unsigned,
   152  col8 bigint unsigned
   153  );
   154  load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t8;
   155  drop table jsonline_t8;
   156  
   157  --compression:gzip,bzip2,lz4
   158  create table jsonline_gzip(col1 char(225),col2 varchar(225),col3 text,col4 varchar(225));
   159  load data infile{'filepath'='$resources/load_data/char_varchar_1_array.json.gz','format'='jsonline','compression'='gzip','jsondata'='array'}into table jsonline_gzip;
   160  select * from jsonline_gzip;
   161  truncate table jsonline_gzip;
   162  create table jsonline_bzip2(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned);
   163  load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl.bz2','format'='jsonline','compression'='bz2','jsondata'='object'}into table jsonline_bzip2;
   164  select * from jsonline_bzip2;
   165  create table jsonline_lz4(col1 float,col2 double,col3 decimal(38,16),col4 decimal(38,16));
   166  load data infile{'filepath'='$resources/load_data/float_1.json.lz4','format'='jsonline','compression'='lz4','jsondata'='object'}into table jsonline_lz4;
   167  select * from jsonline_lz4;
   168  
   169  --abnormal test
   170  drop table if exists jsonline01;
   171  create table jsonline01(col1 bool,col2 int,col3 varchar, col4 date,col5 datetime,col6 timestamp,col7 decimal,col8 float);
   172  load data infile {'filepath'='$resources/load_data/jsonline_array01.jl','format'='jsonline','jsondata'='array'} into table jsonline01;
   173  drop table jsonline01;