You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Arman Siddiqui <AS...@symmetryinvestments.com> on 2016/05/25 17:32:31 UTC
Error with flatten function on MongoDB documents that contain array
of key-value pairs
Good afternoon,
I am receiving the following error when using the flatten function to query a MongoDB collection:
Error: SYSTEM ERROR: IllegalArgumentException: You tried to write a VarChar type when you are using a ValueWriter of type NullableFloat8WriterImpl.
The collection contains a number documents, where each document consists of some key-value pairs and a single array which itself contains exactly 2 key-value pairs per element.
Here is an example document in the collection:
{
"_id" : ObjectId("1234567890abcdef12345678"),
"TradeId" : NumberInt(12345),
"DateValueCollection" : [
{
"ScenarioDate" : ISODate("2011-05-20T00:00:00.000+0000"),
"Pnl" : 22.0
},
{
"ScenarioDate" : ISODate("2011-05-23T00:00:00.000+0000"),
"Pnl" : -30.0
},
{
"ScenarioDate" : ISODate("2011-05-24T00:00:00.000+0000"),
"Pnl" : 15.0
},
{
"ScenarioDate" : ISODate("2011-05-25T00:00:00.000+0000"),
"Pnl" : 9.0
}
]
}
Within the array, I have checked that every ScenarioDate value is of MongoDB type Date and that every PnL value is of MongoDB type double. There are no null values.
Each document contains this same array structure with approx. 1k such elements.
When I copy a small set of these documents into a new collection, the flatten functions works correctly. But in the full collection, the flatten function fails with the error above.
I have tried toggling the store.mongo.read_numbers_as_double and store.mongo.all_text_mode flags with no luck.
Command used (via shell or web client) and verbose error output for reference:
0: jdbc:drill:zk=local> SELECT flatten(`x`.`DateValueCollection`) FROM `mongo`.`db_name`.`some.random.collection.name` AS `x` limit 10;
Error: SYSTEM ERROR: IllegalArgumentException: You tried to write a VarChar type when you are using a ValueWriter of type NullableFloat8WriterImpl.
Fragment 0:0
[Error Id: a43e57f6-f8ce-4850-8040-65858828056f on SYM156.options-it.com:31010] (state=,code=0)
java.sql.SQLException: SYSTEM ERROR: IllegalArgumentException: You tried to write a VarChar type when you are using a ValueWriter of type NullableFloat8WriterImpl.
Fragment 0:0
[Error Id: a43e57f6-f8ce-4850-8040-65858828056f on somehostname: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:1923)
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:355)
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: IllegalArgumentException: You tried to write a VarChar type when you are using a ValueWriter of type NullableFloat8WriterImpl.
Fragment 0:0
[Error Id: a43e57f6-f8ce-4850-8040-65858828056f on somehostname:31010]
at org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:119)
at org.apache.drill.exec.rpc.user.UserClient.handleReponse(UserClient.java:113)
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:67)
at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:374)
at org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(SerializedExecutor.java:89)
at org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:252)
at org.apache.drill.common.SerializedExecutor.execute(SerializedExecutor.java:123)
at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:285)
at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:257)
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 io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:847)
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:511)
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:468)
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:382)
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:354)
at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:111)
at java.lang.Thread.run(Unknown Source)
0: jdbc:drill:zk=local>
Thanks for your help.
Kind regards,
Arman
This message (and any attached documents) contains confidential information and is intended only for the named recipient and may contain confidential, proprietary or legally privileged information. If you are not the named addressee you should not disseminate, distribute or copy this e-mail or its attachments. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail and any attachments from your system. Any views or opinions presented are solely those of the author and do not necessarily represent those of Symmetry Investments UK LLP or any of its affiliates.
This message has been issued by Symmetry Investments, or on behalf of one of its affiliates.
E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments.
We do not warrant that this e-mail is virus free. You must therefore take full responsibility for virus checking.
Symmetry Investments UK LLP is incorporated in England & Wales, registration number OC386690 and whose registered office is at 2nd Floor, 15 Sackville Street, London, W1S 3DJ, United Kingdom.
Symmetry Investments UK LLP is authorised and regulated in the United Kingdom by the Financial Conduct Authority.
Re: Error with flatten function on MongoDB documents that contain
array of key-value pairs
Posted by rahul challapalli <ch...@gmail.com>.
Just to be sure, can you run the below query which does not contain
flatten? If this query also fails, then it could be bad data in "Pnl"
column ( may be an empty string?)
SELECT x.DateValueCollection FROM `mongo`.`db_name`.`
some.random.collection.name` AS x;
On Wed, May 25, 2016 at 10:32 AM, Arman Siddiqui <
ASiddiqui@symmetryinvestments.com> wrote:
> Good afternoon,
>
> I am receiving the following error when using the flatten function to
> query a MongoDB collection:
> Error: SYSTEM ERROR: IllegalArgumentException: You tried to write a
> VarChar type when you are using a ValueWriter of type
> NullableFloat8WriterImpl.
>
> The collection contains a number documents, where each document consists
> of some key-value pairs and a single array which itself contains exactly 2
> key-value pairs per element.
>
> Here is an example document in the collection:
> {
>
> "_id" : ObjectId("1234567890abcdef12345678"),
> "TradeId" : NumberInt(12345),
> "DateValueCollection" : [
> {
> "ScenarioDate" : ISODate("2011-05-20T00:00:00.000+0000"),
> "Pnl" : 22.0
> },
> {
> "ScenarioDate" : ISODate("2011-05-23T00:00:00.000+0000"),
> "Pnl" : -30.0
> },
> {
> "ScenarioDate" : ISODate("2011-05-24T00:00:00.000+0000"),
> "Pnl" : 15.0
> },
> {
> "ScenarioDate" : ISODate("2011-05-25T00:00:00.000+0000"),
> "Pnl" : 9.0
> }
> ]
> }
>
>
> Within the array, I have checked that every ScenarioDate value is of
> MongoDB type Date and that every PnL value is of MongoDB type double.
> There are no null values.
>
> Each document contains this same array structure with approx. 1k such
> elements.
>
> When I copy a small set of these documents into a new collection, the
> flatten functions works correctly. But in the full collection, the flatten
> function fails with the error above.
>
> I have tried toggling the store.mongo.read_numbers_as_double and
> store.mongo.all_text_mode flags with no luck.
>
>
>
> Command used (via shell or web client) and verbose error output for
> reference:
>
> 0: jdbc:drill:zk=local> SELECT flatten(`x`.`DateValueCollection`) FROM
> `mongo`.`db_name`.`some.random.collection.name` AS `x` limit 10;
>
> Error: SYSTEM ERROR: IllegalArgumentException: You tried to write a
> VarChar type when you are using a ValueWriter of type
> NullableFloat8WriterImpl.
>
> Fragment 0:0
>
> [Error Id: a43e57f6-f8ce-4850-8040-65858828056f on
> SYM156.options-it.com:31010] (state=,code=0)
> java.sql.SQLException: SYSTEM ERROR: IllegalArgumentException: You tried
> to write a VarChar type when you are using a ValueWriter of type
> NullableFloat8WriterImpl.
>
> Fragment 0:0
>
> [Error Id: a43e57f6-f8ce-4850-8040-65858828056f on somehostname: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:1923)
> 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:355)
> 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: IllegalArgumentException: You tried to write a VarChar type when you
> are using a ValueWriter of type NullableFloat8WriterImpl.
>
> Fragment 0:0
>
> [Error Id: a43e57f6-f8ce-4850-8040-65858828056f on somehostname:31010]
> at
> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:119)
> at
> org.apache.drill.exec.rpc.user.UserClient.handleReponse(UserClient.java:113)
> 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:67)
> at
> org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:374)
> at
> org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(SerializedExecutor.java:89)
> at
> org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:252)
> at
> org.apache.drill.common.SerializedExecutor.execute(SerializedExecutor.java:123)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:285)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:257)
> 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
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
> at
> io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:847)
> at
> io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
> at
> io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:511)
> at
> io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:468)
> at
> io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:382)
> at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:354)
> at
> io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:111)
> at java.lang.Thread.run(Unknown Source)
> 0: jdbc:drill:zk=local>
>
>
> Thanks for your help.
>
> Kind regards,
> Arman
>
> This message (and any attached documents) contains confidential
> information and is intended only for the named recipient and may contain
> confidential, proprietary or legally privileged information. If you are not
> the named addressee you should not disseminate, distribute or copy this
> e-mail or its attachments. Please notify the sender immediately by e-mail
> if you have received this e-mail by mistake and delete this e-mail and any
> attachments from your system. Any views or opinions presented are solely
> those of the author and do not necessarily represent those of Symmetry
> Investments UK LLP or any of its affiliates.
>
> This message has been issued by Symmetry Investments, or on behalf of one
> of its affiliates.
>
> E-mail transmission cannot be guaranteed to be secure or error-free as
> information could be intercepted, corrupted, lost, destroyed, arrive late
> or incomplete, or contain viruses. The sender therefore does not accept
> liability for any errors or omissions in the contents of this message which
> arise as a result of e-mail transmission. If verification is required
> please request a hard-copy version. This message is provided for
> informational purposes and should not be construed as a solicitation or
> offer to buy or sell any securities or related financial instruments.
>
> We do not warrant that this e-mail is virus free. You must therefore take
> full responsibility for virus checking.
>
> Symmetry Investments UK LLP is incorporated in England & Wales,
> registration number OC386690 and whose registered office is at 2nd Floor,
> 15 Sackville Street, London, W1S 3DJ, United Kingdom.
>
> Symmetry Investments UK LLP is authorised and regulated in the United
> Kingdom by the Financial Conduct Authority.
>