Look into the JSON data model http://drill.apache.org/docs/json-data-model/ <http://drill.apache.org/docs/json-data-model/>
You can set the union type to true for the session and the use the typeof function to determine data types of the fields. Perhaps do a group by typeof. exec.enable_union_type Do note that setting to all text mode need to be turned off before working on this. --Andries > On Oct 12, 2016, at 11:17 AM, Dan Blondowski <dan.blondow...@dhigroupinc.com> > wrote: > > Yes, I understand that part. > I wasn’t clear enough. By trial and error, I meant - me trying to find > which field was bad. I’m going to ask the guys that provide the source > file, to make sure data types for each field are consistent across records. > > > > > > On 10/12/16, 12:53 PM, "Khurram Faraaz" <kfar...@maprtech.com> wrote: > >> Since your JSON file has different types of data (line int, boolean, >> strings etc) we need to tell Drill to consider all data in that JSON file >> as text. This is by design, and this is not trial and error. >> Such occurrence of different types of data in a JSON file is called >> SchemaChange and to avoid that we set `store.json.all_text_mode` = true >> >> On Wed, Oct 12, 2016 at 9:31 PM, Dan Blondowski < >> dan.blondow...@dhigroupinc.com> wrote: >> >>> Hello. You were correct. >>> I was able to set `store.json.all_text_mode` = true; and run the query. >>> >>> I also found the data that needed changing. There were actually 3 >>> fields. >>> >>> Is there a way for Drill to display which field is bad, so I don¹t have >>> to >>> do the trial & error? >>> >>> >>> >>> >>> On 10/12/16, 10:12 AM, "Abhishek Girish" <abhishek.gir...@gmail.com> >>> wrote: >>> >>>> Hey Dan, >>>> >>>> This usually happens when there is a schema change across records. It >>>> could >>>> be intentional (records do need different types for some fields) or bad >>>> formatting (1 vs "1"). >>>> >>>> Can you try setting this session option and retry your query? Let us >>> know >>>> if it helps. You could attempt to use explicit casts in the query to >>> get >>>> types you want. >>>> >>>> set `store.json.all_text_mode` = true; >>>> >>>> Regards, >>>> Abhishek >>>> >>>> On Wednesday, October 12, 2016, Dan Blondowski < >>>> dan.blondow...@dhigroupinc.com> wrote: >>>> >>>>> Hello. >>>>> >>>>> I have a json file with 2 records (see attached). >>>>> >>>>> When I run a query against it I get this error: >>>>> >>>>> *Error: DATA_READ ERROR: Error parsing JSON - You tried to write a >>>>> BigInt >>>>> type when you are using a ValueWriter of type >>>>> NullableVarCharWriterImpl.* >>>>> >>>>> >>>>> *File /test.json* >>>>> >>>>> *Record 2* >>>>> >>>>> *Fragment 0:0* >>>>> >>>>> >>>>> *[Error Id: 8b5166cc-28b6-488c-893f-f0265d483e13 on >>>>> ip-10-3-48-183:31010] >>>>> (state=,code=0)* >>>>> >>>>> If I break it up into individual files, I can run the query against >>> both >>>>> records just fine. Any clues? >>>>> >>>>> Thanks! >>>>> >>>>> Daniel Blondowski >>>>> >>>>> Big Data Architecture/Engineering >>>>> DHI Group, Inc. >>>>> >>>>> dan.blondow...@dice.com >>>>> <javascript:_e(%7B%7D,'cvml','dan.blondow...@dice.com');> >>>>> 515-313-2137 >>>>> >>>>> >>>>> ------------------------------ >>>>> This email has been scanned for email related threats by Mimecast >>>>> <http://www.mimecast.com> >>>>> ------------------------------ >>>>> >>> ------------------------------------------------------------ >>> --------------------------- >>> This email has been scanned for email related threats and delivered >>> safely by Mimecast. >>> For more information please visit http://www.mimecast.com >>> ------------------------------------------------------------ >>> --------------------------- >>> > --------------------------------------------------------------------------------------- > This email has been scanned for email related threats and delivered safely by > Mimecast. > For more information please visit http://www.mimecast.com > ---------------------------------------------------------------------------------------
