In the past setting the below parameter still did not fix the issue. But still worth a try
ALTER SESSION SET `store.json.all_text_mode` = true; You might also want to try explicit casting to varchar for this specific column. On Fri, Jul 8, 2016 at 8:14 AM, Zelaine Fong <[email protected]> wrote: > Have you tried using > > ALTER SESSION SET `store.json.all_text_mode` = true; > > -- Zelaine > > On Fri, Jul 8, 2016 at 6:37 AM, Holy Alexander < > [email protected]> wrote: > > > Hi Vitalii! > > > > > > This is what I tried: > > > > Altered the setting system-wide: > > > > ALTER SYSTEM SET `exec.enable_union_type` = true > > > > Verified that the setting is really altered > > > > SELECT * > > FROM sys.options > > WHERE type in ('SYSTEM','SESSION') order by name > > > > And re-run the query > > > > Unfortunately this does not solve the problem. > > It just causes a different error: > > > > [30027]Query execution error. Details:[ > > SYSTEM ERROR: NullPointerException > > Fragment 0:0 > > [Error Id: 0f9cb7ae-d2d5-474c-ad57-2d558041e2c6 on > > > > (I tried this on Drill 1.7 and 1.6) > > > > Best regards, > > Alexander > > > > > > -----Original Message----- > > From: Vitalii Diravka [mailto:[email protected]] > > Sent: 08 July 2016 13:30 > > To: [email protected] > > Subject: Re: Looking for workaround to Schema detection problems > > > > Hi Alexander, > > > > Please try with turning on the union type: > > > > ALTER SESSION SET `exec.enable_union_type` = true; > > > > Kind regards > > Vitalii > > > > 2016-07-08 10:50 GMT+00:00 Holy Alexander <[email protected] > >: > > > > > My JSON data looks - simplified - like this > > > > > > {"ID":1,"a":"some text"} > > > {"ID":2,"a":"some text","b":"some other text"} {"ID":3,"a":"some > > > text"} > > > > > > Column b is only physically serialized when it is not null. > > > It is the equivalent of a NULLable VARCHAR() column in SQL. > > > > > > I run queries like these: > > > > > > SELECT b > > > FROM dfs.`D:\MyData\test.json` > > > WHERE b IS NOT NULL > > > > > > And normally all is fine. > > > However, among my thousands of data files, I have two files where the > > > first occurrence of b happens a few thousand records down the file. > > > These two data files would look like this: > > > > > > {"ID":1,"a":"some text"} > > > {"ID":2,"a":"some text"} > > > ... 5000 more records without column b ... > > > {"ID":5002,"a":"some text","b":"some other text"} {"ID":5003,"a":"some > > > text"} > > > > > > In this case, my simple SQL query above fails: > > > > > > [30027]Query execution error. Details:[ DATA_READ ERROR: Error parsing > > > JSON - You tried to write a VarChar type when you are using a > > > ValueWriter of type NullableIntWriterImpl. > > > File /D:/MyData/test.json > > > Record 5002 Fragment ... > > > > > > It seems that the Schema inference mechanism of Drill only samples a > > > certain amount of bytes (or records) to determine the schema. > > > If the first occurrence of a schema detail happens to far down things > > > go boom. > > > > > > I am now looking for a sane way to work around this. > > > Preferred by extending the query and not by altering my massive > > > amounts of data. > > > > > > BTW, I tried altering the data by chaning the first line: > > > {"ID":1,"a":"some text","b":null} > > > does not help. > > > > > > Of course, changing the first line to > > > {"ID":1,"a":"some text","b":""} > > > solves the problem, but this is not a practical solution. > > > > > > Any help appreciated. > > > Alexander > > > > > >
