Re: [Drill 1.8] : Can same key hold a JSON as well as plain value?

[Abhishek Girish]

Unfortunately, I'm not sure of an easy fix for this. Is there any way you
can separate out records with a schema change into a different directory?
You could then query them individually and union results.

On Fri, Oct 21, 2016 at 12:56 AM, Anup Tiwari 
wrote:

> Thanks Abhishek for reply.
>
> But i tried above experimental feature and trail-mail query worked but it
> giving me "*SchemaChangeException: Failure while trying to materialize
> incoming schema.*" when i am trying to execute below query :-
>
>
> 0: jdbc:drill:> select url from
> dfs.`/user/hadoop/kafkalogs-stage/acl_15.json` t where t.body like
> '%customTrackingData%' limit 1;
> *Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to
> materialize incoming schema.  Errors:*
>
> *Error in expression at index -1.  Error: Missing function implementation:
> [like(MAP-REQUIRED, VARCHAR-REQUIRED)].  Full expression: --UNKNOWN
> EXPRESSION--..*
>
> Fragment 0:0
>
> [Error Id: 1ab73ede-1ad7-4285-8465-b5f1da6b08f0 on datanode2:31010]
> (state=,code=0)
> java.sql.SQLException: SYSTEM ERROR: SchemaChangeException: Failure while
> trying to materialize incoming schema.  Errors:
>
> Error in expression at index -1.  Error: Missing function implementation:
> [like(MAP-REQUIRED, VARCHAR-REQUIRED)].  Full expression: --UNKNOWN
> EXPRESSION--..
>
> Fragment 0:0
>
> [Error Id: 1ab73ede-1ad7-4285-8465-b5f1da6b08f0 on datanode2:31010]
> at
> org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> DrillCursor.java:247)
> at
> org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> DrillCursor.java:290)
> at
> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> DrillResultSetImpl.java:1933)
> at
> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> DrillResultSetImpl.java:73)
> at
> net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> AvaticaConnection.java:404)
> at
> net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> AvaticaStatement.java:351)
> at
> net.hydromatic.avatica.AvaticaStatement.executeInternal(
> AvaticaStatement.java:338)
> at
> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:69)
> at
> org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> DrillStatementImpl.java:101)
> at sqlline.Commands.execute(Commands.java:841)
> at sqlline.Commands.sql(Commands.java:751)
> at sqlline.SqlLine.dispatch(SqlLine.java:746)
> at sqlline.SqlLine.begin(SqlLine.java:621)
> at sqlline.SqlLine.start(SqlLine.java:375)
> at sqlline.SqlLine.main(SqlLine.java:268)
> Caused by: org.apache.drill.common.exceptions.UserRemoteException: SYSTEM
> ERROR: SchemaChangeException: Failure while trying to materialize incoming
> schema.  Errors:
>
> Error in expression at index -1.  Error: Missing function implementation:
> [like(MAP-REQUIRED, VARCHAR-REQUIRED)].  Full expression: --UNKNOWN
> EXPRESSION--..
>
> Fragment 0:0
>
> [Error Id: 1ab73ede-1ad7-4285-8465-b5f1da6b08f0 on datanode2:31010]
> at
> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> QueryResultHandler.java:123)
> at
> org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> UserClient.java:134)
> at
> org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> BasicClientWithConnection.java:46)
> at
> org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> BasicClientWithConnection.java:31)
> at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:65)
> at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:363)
> at
> org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(
> SerializedExecutor.java:89)
> at
> org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:240)
> at
> org.apache.drill.common.SerializedExecutor.execute(
> SerializedExecutor.java:123)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:274)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:245)
> at
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> MessageToMessageDecoder.java:89)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> AbstractChannelHandlerContext.java:324)
> at
> io.netty.handler.timeout.IdleStateHandler.channelRead(
> IdleStateHandler.java:254)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> AbstractChannelHandlerContext.java:324)
> at
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> MessageToMessageDecoder.java:103)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> 

Re: [Drill 1.8] : Can same key hold a JSON as well as plain value?

[Anup Tiwari]

Thanks Abhishek for reply.

But i tried above experimental feature and trail-mail query worked but it
giving me "*SchemaChangeException: Failure while trying to materialize
incoming schema.*" when i am trying to execute below query :-


0: jdbc:drill:> select url from
dfs.`/user/hadoop/kafkalogs-stage/acl_15.json` t where t.body like
'%customTrackingData%' limit 1;
*Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to
materialize incoming schema.  Errors:*

*Error in expression at index -1.  Error: Missing function implementation:
[like(MAP-REQUIRED, VARCHAR-REQUIRED)].  Full expression: --UNKNOWN
EXPRESSION--..*

Fragment 0:0

[Error Id: 1ab73ede-1ad7-4285-8465-b5f1da6b08f0 on datanode2:31010]
(state=,code=0)
java.sql.SQLException: SYSTEM ERROR: SchemaChangeException: Failure while
trying to materialize incoming schema.  Errors:

Error in expression at index -1.  Error: Missing function implementation:
[like(MAP-REQUIRED, VARCHAR-REQUIRED)].  Full expression: --UNKNOWN
EXPRESSION--..

Fragment 0:0

[Error Id: 1ab73ede-1ad7-4285-8465-b5f1da6b08f0 on datanode2:31010]
at
org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:247)
at
org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(DrillCursor.java:290)
at
org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:1933)
at
org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:73)
at
net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:404)
at
net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(AvaticaStatement.java:351)
at
net.hydromatic.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:338)
at
net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:69)
at
org.apache.drill.jdbc.impl.DrillStatementImpl.execute(DrillStatementImpl.java:101)
at sqlline.Commands.execute(Commands.java:841)
at sqlline.Commands.sql(Commands.java:751)
at sqlline.SqlLine.dispatch(SqlLine.java:746)
at sqlline.SqlLine.begin(SqlLine.java:621)
at sqlline.SqlLine.start(SqlLine.java:375)
at sqlline.SqlLine.main(SqlLine.java:268)
Caused by: org.apache.drill.common.exceptions.UserRemoteException: SYSTEM
ERROR: SchemaChangeException: Failure while trying to materialize incoming
schema.  Errors:

Error in expression at index -1.  Error: Missing function implementation:
[like(MAP-REQUIRED, VARCHAR-REQUIRED)].  Full expression: --UNKNOWN
EXPRESSION--..

Fragment 0:0

[Error Id: 1ab73ede-1ad7-4285-8465-b5f1da6b08f0 on datanode2:31010]
at
org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123)
at
org.apache.drill.exec.rpc.user.UserClient.handleReponse(UserClient.java:134)
at
org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:46)
at
org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:31)
at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:65)
at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:363)
at
org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(SerializedExecutor.java:89)
at
org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:240)
at
org.apache.drill.common.SerializedExecutor.execute(SerializedExecutor.java:123)
at
org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:274)
at
org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:245)
at
io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:89)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:254)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:242)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
at

Re: [Drill 1.8] : Can same key hold a JSON as well as plain value?

[Abhishek Girish]

Hey Anup,

>From the sample data you shared, it looks like you have JSON documents
which differ considerably in the schema / structure. This isn't supported
by default.

You could try turning on UNION type (an experimental feature).

> set `exec.enable_union_type` = true;
+---+--+
|  ok   | summary  |
+---+--+
| true  | exec.enable_union_type updated.  |
+---+--+
1 row selected (0.193 seconds)


On Mon, Oct 17, 2016 at 2:33 AM, Anup Tiwari 
wrote:

> Hi Team,
>
> I have a data in which key name "event" normally consist of plain values
> like 'load' OR "click" etc. Also from past few days in few JSON,
> this("event" key) contains JSON value.
>
> And while reading event section, we are getting *DATA_READ ERROR: Error
> parsing JSON. *Please provide solution asap.
>
>
> Please find below eg :-
>
> *Case 1* :- {"body":{*"event":"click"*,"url":"https://www.rnd.com/
> ","timestamp":1476514176276,"id":"abc","ajaxUrl":null,"
> ajaxData":null,"metadata":null}
>
> *Case 2* :- {"body":{
> *"event":{"isTrusted":true,"customTrackingData":"ACE-
> click-MON-ABC_UL-NA"}*
> ,"url":"https://www.rnd.com/test.html
> ","timestamp":1476531412179,"id":"pqr","ajaxUrl":null,"
> ajaxData":null,"metadata":null}
>
>
>
> *Error :*0: jdbc:drill:> select t.body from
> dfs.`/user/hadoop/kafkalogs-stage/acl_15.json` t  limit 1;
> Error: DATA_READ ERROR: Error parsing JSON - You tried to start when you
> are using a ValueWriter of type NullableVarCharWriterImpl.
>
> File  /user/hadoop/kafkalogs-stage/acl_15.json
> Record  120
> Fragment 0:0
>
> [Error Id: bc33aac6-4cca-4237-9d01-01e1f1015572 on datanode1:31010]
> (state=,code=0)
> java.sql.SQLException: DATA_READ ERROR: Error parsing JSON - You tried to
> start when you are using a ValueWriter of type NullableVarCharWriterImpl.
>
> File  /user/hadoop/kafkalogs-stage/acl_15.json
> Record  120
> Fragment 0:0
>
> [Error Id: bc33aac6-4cca-4237-9d01-01e1f1015572 on datanode1:31010]
> at
> org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> DrillCursor.java:247)
> at
> org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> DrillCursor.java:290)
> at
> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> DrillResultSetImpl.java:1933)
> at
> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> DrillResultSetImpl.java:73)
> at
> net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> AvaticaConnection.java:404)
> at
> net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> AvaticaStatement.java:351)
> at
> net.hydromatic.avatica.AvaticaStatement.executeInternal(
> AvaticaStatement.java:338)
> at
> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:69)
> at
> org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> DrillStatementImpl.java:101)
> at sqlline.Commands.execute(Commands.java:841)
> at sqlline.Commands.sql(Commands.java:751)
> at sqlline.SqlLine.dispatch(SqlLine.java:746)
> at sqlline.SqlLine.begin(SqlLine.java:621)
> at sqlline.SqlLine.start(SqlLine.java:375)
> at sqlline.SqlLine.main(SqlLine.java:268)
> Caused by: org.apache.drill.common.exceptions.UserRemoteException:
> DATA_READ ERROR: Error parsing JSON - You tried to start when you are using
> a ValueWriter of type NullableVarCharWriterImpl.
>
> File  /user/hadoop/kafkalogs-stage/acl_15.json
> Record  120
> Fragment 0:0
>
> [Error Id: bc33aac6-4cca-4237-9d01-01e1f1015572 on datanode1:31010]
> at
> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> QueryResultHandler.java:123)
> at
> org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> UserClient.java:134)
> at
> org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> BasicClientWithConnection.java:46)
> at
> org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> BasicClientWithConnection.java:31)
> at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:65)
> at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:363)
> at
> org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(
> SerializedExecutor.java:89)
> at
> org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:240)
> at
> org.apache.drill.common.SerializedExecutor.execute(
> SerializedExecutor.java:123)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:274)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:245)
> at
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> MessageToMessageDecoder.java:89)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> AbstractChannelHandlerContext.java:324)
> at
> 

[Drill 1.8] : Can same key hold a JSON as well as plain value?

[Anup Tiwari]

Hi Team,

I have a data in which key name "event" normally consist of plain values
like 'load' OR "click" etc. Also from past few days in few JSON,
this("event" key) contains JSON value.

And while reading event section, we are getting *DATA_READ ERROR: Error
parsing JSON. *Please provide solution asap.


Please find below eg :-

*Case 1* :- {"body":{*"event":"click"*,"url":"https://www.rnd.com/
","timestamp":1476514176276,"id":"abc","ajaxUrl":null,"ajaxData":null,"metadata":null}

*Case 2* :- {"body":{
*"event":{"isTrusted":true,"customTrackingData":"ACE-click-MON-ABC_UL-NA"}*
,"url":"https://www.rnd.com/test.html
","timestamp":1476531412179,"id":"pqr","ajaxUrl":null,"ajaxData":null,"metadata":null}



*Error :*0: jdbc:drill:> select t.body from
dfs.`/user/hadoop/kafkalogs-stage/acl_15.json` t  limit 1;
Error: DATA_READ ERROR: Error parsing JSON - You tried to start when you
are using a ValueWriter of type NullableVarCharWriterImpl.

File  /user/hadoop/kafkalogs-stage/acl_15.json
Record  120
Fragment 0:0

[Error Id: bc33aac6-4cca-4237-9d01-01e1f1015572 on datanode1:31010]
(state=,code=0)
java.sql.SQLException: DATA_READ ERROR: Error parsing JSON - You tried to
start when you are using a ValueWriter of type NullableVarCharWriterImpl.

File  /user/hadoop/kafkalogs-stage/acl_15.json
Record  120
Fragment 0:0

[Error Id: bc33aac6-4cca-4237-9d01-01e1f1015572 on datanode1:31010]
at
org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:247)
at
org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(DrillCursor.java:290)
at
org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:1933)
at
org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:73)
at
net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:404)
at
net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(AvaticaStatement.java:351)
at
net.hydromatic.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:338)
at
net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:69)
at
org.apache.drill.jdbc.impl.DrillStatementImpl.execute(DrillStatementImpl.java:101)
at sqlline.Commands.execute(Commands.java:841)
at sqlline.Commands.sql(Commands.java:751)
at sqlline.SqlLine.dispatch(SqlLine.java:746)
at sqlline.SqlLine.begin(SqlLine.java:621)
at sqlline.SqlLine.start(SqlLine.java:375)
at sqlline.SqlLine.main(SqlLine.java:268)
Caused by: org.apache.drill.common.exceptions.UserRemoteException:
DATA_READ ERROR: Error parsing JSON - You tried to start when you are using
a ValueWriter of type NullableVarCharWriterImpl.

File  /user/hadoop/kafkalogs-stage/acl_15.json
Record  120
Fragment 0:0

[Error Id: bc33aac6-4cca-4237-9d01-01e1f1015572 on datanode1:31010]
at
org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123)
at
org.apache.drill.exec.rpc.user.UserClient.handleReponse(UserClient.java:134)
at
org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:46)
at
org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:31)
at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:65)
at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:363)
at
org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(SerializedExecutor.java:89)
at
org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:240)
at
org.apache.drill.common.SerializedExecutor.execute(SerializedExecutor.java:123)
at
org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:274)
at
org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:245)
at
io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:89)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:254)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:242)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at