Shankar created DRILL-4520:
------------------------------
Summary: Error parsing JSON ( a column with different datatypes )
Key: DRILL-4520
URL: https://issues.apache.org/jira/browse/DRILL-4520
Project: Apache Drill
Issue Type: Test
Reporter: Shankar
I am stuck in the middle of somewhere. Could you please help me to resolve
below error.
I am running query on drill 1.6.0 in cluster on logs json data (150GB size of
log file) ( 1 json / line).
{quote}
solution as per my opinion -
1. Either drill should able to ignore those lines(ANY data type) while reading
or creating the table (CTAS).
2. Or Data will get stored as it is with ANY data type if any fields in data
differs in their data types. This will be useful in the case where other
columns (excluding ANY data type columns) carrying important informations.
{quote}
h4. -------------- test.json --------------
Abount Data :
1. I have just extract 3 lines from logs for test purpose.
2. In data field called "ajaxUrl" is differ in datatype. Sometimes it contains
string and sometime array of jsons and null as well.
3. Here in our case - Some events in 150 gb json file are like this where they
differ in structure. I could say there are only 0.1% (per 150gb json file) are
such events.
{noformat}
{"ajaxData":null,"metadata":null,"ajaxUrl":"/player/updatebonus1","selectedItem":null,"sessionid":"BC497C7C39B3C90AC9E6E9E8194C3","timestamp":1457658600032}
{"gameId":"https://daemon2.com/tournDetails.do?type=myGames&userId=1556148&jsonp_callback=jQuery213043","ajaxData":null,"metadata":null,"ajaxUrl":[{"R":0,"rNo":1,"gid":4,"wal":0,"d":{"gid":4,"pt":3,"wc":2326,"top":"1","reg":true,"brkt":1457771400268,"sk":"25070010105301000009","id":56312439,"a":0,"st":1457771400000,"e":"0.0","j":0,"n":"Loot
Qualifier
1","tc":94,"et":0,"syst":1457771456,"rc":145770000,"s":5,"t":10000,"tk":false,"prnId":56311896,"jc":10000,"tp":"100000.0","ro":145400000,"rp":0,"isprn":false},"fl":"192.168.35.42","aaid":"5828"}],"selectedItem":null,"sessionid":"D18104E8CA3071C7A8F4E141B127","timestamp":1457771458873}
{"ajaxData":null,"metadata":null,"ajaxUrl":"/player/updatebonus2","selectedItem":null,"sessionid":"BC497C7C39B3C90AC9E6E9E8194C3","timestamp":1457958600032}
{noformat}
h4. -------------- Select Query (ERROR) --------------
{noformat}
select
`timestamp`,
sessionid,
gameid,
ajaxUrl,
ajaxData
from dfs.`/tmp/test.json` t
;
{noformat}
{color:red}
Error: DATA_READ ERROR: Error parsing JSON - You tried to start when you are
using a ValueWriter of type NullableVarCharWriterImpl.
File /tmp/test.json
Record 2
Fragment 0:0
{color}
h4. -------------- Select Query (works Fine with UNION type) --------------
Tried UNION type (an experimental feature)
set `exec.enable_union_type` = true;
{noformat}
set `exec.enable_union_type` = true;
+-------+----------------------------------+
| ok | summary |
+-------+----------------------------------+
| true | exec.enable_union_type updated. |
+-------+----------------------------------+
1 row selected (0.193 seconds)
select
`timestamp`,
sessionid,
gameid,
ajaxUrl,
ajaxData
from dfs.`/tmp/test.json` t
;
+----------------+--------------------------------+----------------------------------------------------------------------------------------------+-----------------------+-----------+
| timestamp | sessionid |
gameid |
ajaxUrl | ajaxData |
+----------------+--------------------------------+----------------------------------------------------------------------------------------------+-----------------------+-----------+
| 1457658600032 | BC497C7C39B3C90AC9E6E9E8194C3 | null
|
/player/updatebonus1 | null |
| 1457771458873 | D18104E8CA3071C7A8F4E141B127 |
https://daemon2.com/tournDetails.do?type=myGames&userId=1556148&jsonp_callback=jQuery213043
| [] | null |
| 1457958600032 | BC497C7C39B3C90AC9E6E9E8194C3 | null
|
/player/updatebonus2 | null |
+----------------+--------------------------------+----------------------------------------------------------------------------------------------+-----------------------+-----------+
3 rows selected (0.965 seconds)
{noformat}
h4. -------------- CTAS Query (ERROR) --------------
{noformat}
set `exec.enable_union_type` = true;
+-------+----------------------------------+
| ok | summary |
+-------+----------------------------------+
| true | exec.enable_union_type updated. |
+-------+----------------------------------+
1 row selected (0.193 seconds)
create table dfs.tmp.test1 AS
select
`timestamp`,
sessionid,
gameid,
ajaxUrl,
ajaxData
from dfs.`/tmp/test.json` t
;
{noformat}
{color:red}
Error: SYSTEM ERROR: NullPointerException
Fragment 0:0
{color}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
