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