github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/load_data/load_data_jsonline.result (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 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 7 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 ["1", 2, null, false, true, {"q": 1}] 1qaz null null 8 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 {"b": ["a", "b", {"q": 4}], "c": 1} 1aza null null 9 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 ["1", 2, null, false, true, {"q": 1}] 1az null null 10 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 {"b": ["a", "b", {"q": 4}], "c": 1} 1qaz null null 11 delete from t1; 12 load data infile {'filepath'='$resources/load_data/jsonline_array.jl','format'='jsonline','jsondata'='array'} into table t1; 13 load data infile {'filepath'='$resources/load_data/jsonline_array.jl.bz2','format'='jsonline','jsondata'='array','compression'='bzip2'} into table t1; 14 load data infile {'filepath'='$resources/load_data/jsonline_array.jl.gz','format'='jsonline','jsondata'='array','compression'='gzip'} into table t1; 15 load data infile {'filepath'='$resources/load_data/jsonline_array.jl.bz2','jsondata'='array'} into table t1; 16 load data infile {'filepath'='$resources/load_data/jsonline_array.jl.gz','jsondata'='array'} into table t1; 17 select * from t1; 18 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 19 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 ["1", 2, null, false, true, {"q": 1}] 1qaz null null 20 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 {"b": ["a", "b", {"q": 4}], "c": 1} 1aza null null 21 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 ["1", 2, null, false, true, {"q": 1}] 1qaz null null 22 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 {"b": ["a", "b", {"q": 4}], "c": 1} 1aza null null 23 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 ["1", 2, null, false, true, {"q": 1}] 1qaz null null 24 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 {"b": ["a", "b", {"q": 4}], "c": 1} 1aza null null 25 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 ["1", 2, null, false, true, {"q": 1}] 1qaz null null 26 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 {"b": ["a", "b", {"q": 4}], "c": 1} 1aza null null 27 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 ["1", 2, null, false, true, {"q": 1}] 1qaz null null 28 true 1 var 2020-09-07 2020-09-07 00:00:00 2020-09-07 00:00:00 18 121.11 {"b": ["a", "b", {"q": 4}], "c": 1} 1aza null null 29 drop table if exists jsonline_t1; 30 create table jsonline_t1( 31 col1 tinyint, 32 col2 smallint, 33 col3 int, 34 col4 bigint, 35 col5 tinyint unsigned, 36 col6 smallint unsigned, 37 col7 int unsigned, 38 col8 bigint unsigned 39 ); 40 load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t1; 41 select * from jsonline_t1; 42 col1 col2 col3 col4 col5 col6 col7 col8 43 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 44 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 45 40 0 60 50 100 0 90 20 46 null null null null null null null null 47 select * from jsonline_t1 into outfile '$resources/into_outfile/json_outfile_integer_numbers_1.csv'; 48 select * from jsonline_t1; 49 col1 col2 col3 col4 col5 col6 col7 col8 50 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 51 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 52 40 0 60 50 100 0 90 20 53 null null null null null null null null 54 delete from jsonline_t1; 55 load data infile '$resources/into_outfile/json_outfile_integer_numbers_1.csv' into table jsonline_t1 fields terminated by ',' ignore 1 lines; 56 select * from jsonline_t1; 57 col1 col2 col3 col4 col5 col6 col7 col8 58 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 59 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 60 40 0 60 50 100 0 90 20 61 null null null null null null null null 62 delete from jsonline_t1; 63 load data infile{'filepath'='$resources/load_data/integer_numbers_1_array.jl','format'='jsonline','jsondata'='array'}into table jsonline_t1; 64 select * from jsonline_t1; 65 col1 col2 col3 col4 col5 col6 col7 col8 66 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 67 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 68 0 0 0 0 0 0 0 0 69 null null null null null null null null 70 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 71 drop table if exists jsonline_t2; 72 create table jsonline_t2( 73 col1 char(225), 74 col2 varchar(225), 75 col3 text, 76 col4 varchar(225) 77 ); 78 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t2; 79 select * from jsonline_t2; 80 col1 col2 col3 col4 81 a 中文 #¥%……&# 234cccc重要 82 "a" "b" "c" "d" 83 'd' '123456' 'b' 'a' 84 "'a'" "'b'" "'c'" abc 85 123f fff yeah 红tu bb" 86 "aa"""" aa" "bb"""" bb" 87 null null 88 null null null null 89 select * from jsonline_t2 into outfile '$resources/into_outfile/json_outfile_char_varchar_1.csv'; 90 select * from jsonline_t2; 91 col1 col2 col3 col4 92 a 中文 #¥%……&# 234cccc重要 93 "a" "b" "c" "d" 94 'd' '123456' 'b' 'a' 95 "'a'" "'b'" "'c'" abc 96 123f fff yeah 红tu bb" 97 "aa"""" aa" "bb"""" bb" 98 null null 99 null null null null 100 delete from jsonline_t2; 101 load data infile '$resources/into_outfile/json_outfile_char_varchar_1.csv' into table jsonline_t2 fields terminated by ',' ignore 1 lines; 102 select * from jsonline_t2; 103 col1 col2 col3 col4 104 a 中文 #¥%……&# 234cccc重要 105 "a" "b" "c" "d" 106 'd' '123456' 'b' 'a' 107 "'a'" "'b'" "'c'" abc 108 123f fff yeah 红tu bb" 109 "aa"""" aa" "bb"""" bb" 110 null null 111 null null null null 112 delete from jsonline_t2; 113 load data infile{'filepath'='$resources/load_data/char_varchar_1_array.json','format'='jsonline','jsondata'='array'}into table jsonline_t2; 114 select * from jsonline_t2; 115 col1 col2 col3 col4 116 a 重工 4546 @#¥%……&*() 117 'a' 'b' 'c' 'd' 118 aa,aa bb,bb [20 50 <nil> map[col:9] <nil>] dd,dd 119 null e f g fff 120 aa',',,aa _yeah_123 1111111111uuuuuuuuuhhhhzzzzzzzzffff dd',',,dd 121 null null null null 122 drop table if exists jsonline_t3; 123 create table jsonline_t3( 124 col1 float, 125 col2 double, 126 col3 decimal(38,16), 127 col4 decimal(38,16) 128 ); 129 load data infile{'filepath'='$resources/load_data/float_1.json','format'='jsonline','jsondata'='object'}into table jsonline_t3; 130 select * from jsonline_t3; 131 col1 col2 col3 col4 132 1.3 5.0 2.0000000000000000 0.4000000000000000 133 1.0 null 635437923742.3333333330000000 1.0000000000000000 134 null null null 40.0200000000000000 135 -1.763835E21 -1.0E-14 1.2345600000000000 3.9800000000000000 136 -345.34244 -1.1118772349834299E73 8349538974359357.0000000000000000 3.9484359854839584 137 truncate table jsonline_t3; 138 load data infile{'filepath'='$resources/load_data/float_1_array.jl','format'='jsonline','jsondata'='array'}into table jsonline_t3; 139 select * from jsonline_t3; 140 col1 col2 col3 col4 141 1.3 5.0 2.0000000000000000 0.4000000000000000 142 1.0 null 635437923742.3333333330000000 1.0000000000000000 143 null null null 40.0200000000000000 144 -1.7638359E25 -1.9348593579835793 1.2345600000000000 3.9800000000000000 145 -345.34244 -1.1118772349834298 83495389743593573.0000000000000000 9484359854839584.0000000000000000 146 drop table if exists jsonline_t4; 147 create table jsonline_t4( 148 col1 date, 149 col2 datetime(6), 150 col3 timestamp(3), 151 col4 bool 152 ); 153 set time_zone = 'SYSTEM'; 154 load data infile{'filepath'='$resources/load_data/time_date_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t4; 155 select * from jsonline_t4; 156 col1 col2 col3 col4 157 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 false 158 9999-12-31 9999-12-31 00:00:00 2038-01-19 00:00:00 true 159 1000-01-01 0001-01-01 00:00:00 null false 160 1000-01-01 0001-01-01 00:00:00 null true 161 1000-01-01 0001-01-01 00:00:00.000001000 null false 162 null null null null 163 null null null null 164 9999-12-31 9999-12-30 23:59:59.999999000 null false 165 select * from jsonline_t4 into outfile '$resources/into_outfile/json_outfile_time_date_1.csv'; 166 load data infile '$resources/into_outfile/json_outfile_time_date_1.csv' into table jsonline_t4 fields terminated by ',' ignore 1 lines; 167 select * from jsonline_t4; 168 col1 col2 col3 col4 169 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 false 170 9999-12-31 9999-12-31 00:00:00 2038-01-19 00:00:00 true 171 1000-01-01 0001-01-01 00:00:00 null false 172 1000-01-01 0001-01-01 00:00:00 null true 173 1000-01-01 0001-01-01 00:00:00.000001000 null false 174 null null null null 175 null null null null 176 9999-12-31 9999-12-30 23:59:59.999999000 null false 177 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 false 178 9999-12-31 9999-12-31 00:00:00 2038-01-19 00:00:00 true 179 1000-01-01 0001-01-01 00:00:00 null false 180 1000-01-01 0001-01-01 00:00:00 null true 181 1000-01-01 0001-01-01 00:00:00.000001000 null false 182 null null null null 183 null null null null 184 9999-12-31 9999-12-30 23:59:59.999999000 null false 185 truncate table jsonline_t4; 186 load data infile{'filepath'='$resources/load_data/time_date_1_array.jl','format'='jsonline','jsondata'='array'}into table jsonline_t4; 187 select * from jsonline_t4; 188 col1 col2 col3 col4 189 1000-01-01 0001-01-01 00:00:00 null false 190 1000-01-01 0001-01-01 00:00:00 2023-01-12 10:02:34 true 191 1000-01-01 0001-01-01 00:00:00 2022-09-10 00:00:00 true 192 9999-12-31 9999-12-30 23:59:59.999999000 2023-01-12 10:02:34.093000000 false 193 null null null null 194 create table jsonline_t5 (a char(225),b varchar(225),c text,d varchar(225)); 195 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t5; 196 invalid input: the attr a is not in json 197 select * from jsonline_t5; 198 a b c d 199 drop table jsonline_t5; 200 create table jsonline_t5 (col1 char(225),col2 varchar(225),col3 text,col4 varchar(225),col5 int default 10); 201 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t5; 202 internal error: the table column is larger than input data column 203 create table jsonline_t6 (col1 char(225),col2 varchar(225),col4 varchar(225)); 204 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t6; 205 select * from jsonline_t6 where col1='a'; 206 col1 col2 col4 207 a 中文 234cccc重要 208 create table jsonline_t7 (col1 char(225),col2 varchar(225),col3 text,col4 varchar(225)); 209 load data infile{'filepath'='$resources/load_data/char1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t7; 210 invalid input: the file does not exist in load flow 211 load data infile{'filepath'='','format'='jsonline','jsondata'='object'}into table jsonline_t7; 212 invalid configuration: the filepath must be specified 213 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='array'}into table jsonline_t7; 214 json: cannot unmarshal object into Go value of type []interface {} 215 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline'}into table jsonline_t7; 216 invalid configuration: the jsondata must be specified 217 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','compression'='none','jsondata'='object'}into table jsonline_t7; 218 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','compression'='gzip','jsondata'='object'}into table jsonline_t7; 219 gzip: invalid header 220 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','jsondata'='object'}into table jsonline_t7; 221 drop table jsonline_t7; 222 create table jsonline_t7(col1 float,col2 double,col3 decimal(38,16),col4 decimal(38,16)); 223 load data infile{'filepath'='$resources/load_data/float_1.json','format'='csv'} into table jsonline_t7 fields terminated by ','; 224 internal error: the input value '{"col1": "1.3"' is not float32 type for column 0 225 load data infile{'filepath'='$resources/load_data/char_varchar_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t7; 226 internal error: the input value 'a' is not float32 type for column 0 227 create table jsonline_t8( 228 col1 tinyint not null primary key, 229 col2 smallint, 230 col3 int, 231 col4 bigint, 232 col5 tinyint unsigned, 233 col6 smallint unsigned, 234 col7 int unsigned, 235 col8 bigint unsigned 236 ); 237 load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t8; 238 constraint violation: Column 'col1' cannot be null 239 drop table jsonline_t8; 240 create table jsonline_t8( 241 col1 tinyint default 10, 242 col2 smallint, 243 col3 int, 244 col4 bigint, 245 col5 tinyint unsigned, 246 col6 smallint unsigned, 247 col7 int unsigned, 248 col8 bigint unsigned 249 ); 250 load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t8; 251 drop table jsonline_t8; 252 create table jsonline_t8( 253 col1 tinyint primary key, 254 col2 smallint, 255 col3 int, 256 col4 bigint, 257 col5 tinyint unsigned, 258 col6 smallint unsigned, 259 col7 int unsigned, 260 col8 bigint unsigned 261 ); 262 load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl','format'='jsonline','jsondata'='object'}into table jsonline_t8; 263 constraint violation: Column 'col1' cannot be null 264 drop table jsonline_t8; 265 create table jsonline_gzip(col1 char(225),col2 varchar(225),col3 text,col4 varchar(225)); 266 load data infile{'filepath'='$resources/load_data/char_varchar_1_array.json.gz','format'='jsonline','compression'='gzip','jsondata'='array'}into table jsonline_gzip; 267 select * from jsonline_gzip; 268 col1 col2 col3 col4 269 a 重工 4546 @#¥%……&*() 270 'a' 'b' 'c' 'd' 271 aa,aa bb,bb [20 50 <nil> map[col:9] <nil>] dd,dd 272 null e f g fff 273 aa',',,aa _yeah_123 1111111111uuuuuuuuuhhhhzzzzzzzzffff dd',',,dd 274 null null null null 275 truncate table jsonline_gzip; 276 create table jsonline_bzip2(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned); 277 load data infile{'filepath'='$resources/load_data/integer_numbers_1.jl.bz2','format'='jsonline','compression'='bz2','jsondata'='object'}into table jsonline_bzip2; 278 select * from jsonline_bzip2; 279 col1 col2 col3 col4 col5 col6 col7 col8 280 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 281 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 282 40 0 60 50 100 0 90 20 283 null null null null null null null null 284 create table jsonline_lz4(col1 float,col2 double,col3 decimal(38,16),col4 decimal(38,16)); 285 load data infile{'filepath'='$resources/load_data/float_1.json.lz4','format'='jsonline','compression'='lz4','jsondata'='object'}into table jsonline_lz4; 286 select * from jsonline_lz4; 287 col1 col2 col3 col4 288 1.3 5.0 2.0000000000000000 0.4000000000000000 289 1.0 null 635437923742.3333333330000000 1.0000000000000000 290 null null null 40.0200000000000000 291 -1.763835E21 -1.0E-14 1.2345600000000000 3.9800000000000000 292 -345.34244 -1.1118772349834299E73 8349538974359357.0000000000000000 3.9484359854839584 293 drop table if exists jsonline01; 294 create table jsonline01(col1 bool,col2 int,col3 varchar, col4 date,col5 datetime,col6 timestamp,col7 decimal,col8 float); 295 load data infile {'filepath'='$resources/load_data/jsonline_array01.jl','format'='jsonline','jsondata'='array'} into table jsonline01; 296 invalid input: [true,1,"var","2020-09-07","2020-09-07 00:00:00","2020-09-07 00:00:00","18",121.11,"1","1qaz",null,null] , wrong number of colunms 297 drop table jsonline01;