> On Nov 15, 2019, at 1:39 PM, Paul Rogers <par0...@yahoo.com.INVALID> wrote: > > Hi Charles, > > A thought on debugging deserialization is to not do it in a query. Capture > the JSON returned from a rest call. Write a simple unit test that > deserializes that by itself from a string or file. Deserialization is a bit > of a black art, and is really a problem separate from Drill itself.
So dumb non-dev question... How exactly do I do that? I have SeDe unit test(s), but the query in question is failing in the first part of the unit test. @Test public void testSerDe() throws Exception { String sql = "SELECT COUNT(*) FROM http.`/json?lat=36.7201600&lng=-4.4203400&date=2019-10-02`"; String plan = queryBuilder().sql(sql).explainJson(); long cnt = queryBuilder().physical(plan).singletonLong(); assertEquals("Counts should match",1L, cnt); } > > As it turns out, for my "day job" I'm doing a POC using Drill to query > SumoLogic. I took this as an opportunity to fill that gap you mentioned in > our book: how to create a storage plugin. See [1]. This is a work in > progress, but it has helped me build the planner-side stuff up to the batch > reader, after which the work is identical to that for a format plugin. YES!! Awesome! I know it is super involved, but simply documenting it will help a lot. Add that to the number of beers I owe you! > > The Sumo API is REST-based, but for now I'm using the clunky REST client > available in the Sumo public repo because of some unfortunate details of the > Sumo REST service when used for this purpose. (Sumo returns data as a set of > key/value pairs, not as a fixed JSON schema. [4]) > > Poking around elsewhere, it turns out someone wrote a very simple Presto > connector for REST [2] using the Retrofit library from Square [3] which seems > very simple to use. If we create a generic REST plugin, we might want to look > at how it was done in Presto. Presto requires an up-front schema which > Retrofit can provide. Drill, of course, does not require such a schema and so > works with ad-hoc schemas, such as the one that Sumo's API provides. > > Actually, better than using a deserializer would be to use Drill's existing > JSON parser to read data directly into value vectors. But, that existing code > has lots of tech debt. I've been working on a PR for new version based on > EVF, but that is a while off, and won't help us today. > > It is interesting to note that neither the JSON reader, nor a generic REST > API would work with the Sumo API because of is structure. I think the JSON > reader would read an entire batch of Sumo results as a single record composed > of a repeated Map, with elements being the key/value pairs. Not at all ideal. > > So, both the JSON reader, and the REST API, should eventually handle data > formats which are generic (name/value pairs) rather than expressed in the > structure of JSON objects (as required by Jackson and Retrofit.) That is a > topic for later, but is why the Sumo plugin has to be custom to Sumo's API > for now. > > > Thanks, > - Paul > > > [1] https://github.com/paul-rogers/drill/wiki/Create-a-Storage-Plugin > > [2] https://github.com/prestosql-rocks/presto-rest > > [3] https://square.github.io/retrofit/ > > [4] https://help.sumologic.com/APIs/Search-Job-API/About-the-Search-Job-API > > > > > > On Friday, November 15, 2019, 09:04:21 AM PST, Charles Givre > <cgi...@gmail.com> wrote: > > Hi Igor, > Thanks for the advice. I've been doing some digging and am still pretty > stuck here. Can you recommend any techniques about how to debug the Jackson > serialization/deserialization? I added a unit test that serializes a query > and then deserializes it and that test fails. I've tracked this back to a > constructor not receiving the plugin config and then throwing a NPE. What I > can't seem to figure out is where that is being called from and why. > > Any advice would be greatly appreciated. Code can be found here: > https://github.com/apache/drill/pull/1892 > <https://github.com/apache/drill/pull/1892> > Thanks, > -- C > > >> On Oct 12, 2019, at 3:27 AM, Igor Guzenko <ihor.huzenko....@gmail.com> wrote: >> >> Hello Charles, >> >> Looks like you found another new issue. Maybe I explained unclear, but my >> previous suggestion wasn't about EXPLAIN PLAN construct, but rather: >> 1) Use http client like Postman or simply browser to save response of >> requested rest service into json file >> 2) Try to debug reading the file by Drill in order to compare how >> Calcite's conversion from AST SqlNode to RelNode tree differs for existing >> dfs storage plugin from same flow in your storage plugin. >> >> From your last email I can figure out that exists another issue with class >> HttpGroupScan, at some point Drill tried to deserialize json into instance >> of HttpGroupScan and jackson library didn't find how to do this. Probably >> you missed some constructor with jackson metadata, for example see in >> HiveScan operator: >> >> @JsonCreator >> public HiveScan(@JsonProperty("userName") final String userName, >> @JsonProperty("hiveReadEntry") final HiveReadEntry >> hiveReadEntry, >> @JsonProperty("hiveStoragePluginConfig") final >> HiveStoragePluginConfig hiveStoragePluginConfig, >> @JsonProperty("columns") final List<SchemaPath> columns, >> @JsonProperty("confProperties") final Map<String, >> String> confProperties, >> @JacksonInject final StoragePluginRegistry >> pluginRegistry) throws ExecutionSetupException { >> this(userName, >> hiveReadEntry, >> (HiveStoragePlugin) pluginRegistry.getPlugin(hiveStoragePluginConfig), >> columns, >> null, confProperties); >> } >> >> Kind regards, >> Igor >> >> >> >> On Fri, Oct 11, 2019 at 10:53 PM Charles Givre <cgi...@gmail.com >> <mailto:cgi...@gmail.com>> wrote: >> >>> Hi Igor, >>> Thanks for responding. I'm not sure if this is what you intended, but >>> looked at the JSON for the Query plans and found something interesting. >>> For the SELECT * query, I'm getting the following when I try to run the >>> physical plan that it generates (without modification). Do you think this >>> could be a related problem? >>> >>> >>> org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: >>> InvalidDefinitionException: Cannot construct instance of >>> `org.apache.drill.exec.store.http.HttpGroupScan` (no Creators, like default >>> construct, exist): cannot deserialize from Object value (no delegate- or >>> property-based Creator) >>> at [Source: (String)"{ >>> "head" : { >>> "version" : 1, >>> "generator" : { >>> "type" : "ExplainHandler", >>> "info" : "" >>> }, >>> "type" : "APACHE_DRILL_PHYSICAL", >>> "options" : [ ], >>> "queue" : 0, >>> "hasResourcePlan" : false, >>> "resultMode" : "EXEC" >>> }, >>> "graph" : [ { >>> "pop" : "http-scan", >>> "@id" : 2, >>> "scanSpec" : { >>> "uri" : "/json?lat=36.7201600&lng=-4.4203400&date=2019-10-02" >>> }, >>> "columns" : [ "`**`" ], >>> "storageConfig" : { >>> "type" : "http", >>> "[truncated 766 chars]; line: 16, column: 5] (through reference chain: >>> org.apache.drill.exec.physical.PhysicalPlan["graph"]->java.util.ArrayList[0]) >>> >>> >>> Please, refer to logs for more information. >>> >>> [Error Id: 751b6d05-a631-4eca-9d83-162ab4fa839f on localhost:31010] >>> >>> >>>> On Oct 11, 2019, at 12:25 PM, Igor Guzenko <ihor.huzenko....@gmail.com> >>> wrote: >>>> >>>> Hello Charles, >>>> >>>> You got the error from Apache Calcite at the planning stage while >>>> converting SQLIdentifier to RexNode. From your stack trace the conversion >>>> starts here DefaultSqlHandler.convertToRel(DefaultSqlHandler.java:685) >>> and >>>> goes to >>> SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:3694). I >>>> would suggest to save json returned by rest locally as file and debug >>> same >>>> trace for query on the json file. So then you can find difference between >>>> conversion of sql identifier to rel for standart json reading and for >>> your >>>> storage plugin. >>>> >>>> Thanks, Igor >>>> >>>> >>>> On Fri, Oct 11, 2019 at 6:34 PM Charles Givre <cgi...@gmail.com >>>> <mailto:cgi...@gmail.com> <mailto: >>> cgi...@gmail.com>> wrote: >>>> >>>>> Hello all, >>>>> I decided to take the leap and attempt to implement a storage plugin. I >>>>> found that a few people had started this, so I thought I'd complete a >>>>> simple generic HTTP/REST storage plugin. The use case would be to enrich >>>>> data sets with data that's available via public or internal APIs. >>>>> >>>>> Anyway, I'm a little stuck and need some assistance. i got the plugin >>> to >>>>> successfully execute a star query and return the results correctly: >>>>> >>>>> apache drill> SELECT * FROM >>>>> http.`/json?lat=36.7201600&lng=-4.4203400&date=2019-10-02`; >>>>> >>>>> >>> +------------+------------+-------------+------------+----------------------+--------------------+-------------------------+-----------------------+-----------------------------+---------------------------+ >>>>> | sunrise | sunset | solar_noon | day_length | >>>>> civil_twilight_begin | civil_twilight_end | nautical_twilight_begin | >>>>> nautical_twilight_end | astronomical_twilight_begin | >>>>> astronomical_twilight_end | >>>>> >>>>> >>> +------------+------------+-------------+------------+----------------------+--------------------+-------------------------+-----------------------+-----------------------------+---------------------------+ >>>>> | 6:13:58 AM | 5:59:55 PM | 12:06:56 PM | 11:45:57 | 5:48:14 AM >>>>> | 6:25:38 PM | 5:18:16 AM | 6:55:36 PM | >>>>> 4:48:07 AM | 7:25:45 PM | >>>>> >>>>> >>> +------------+------------+-------------+------------+----------------------+--------------------+-------------------------+-----------------------+-----------------------------+---------------------------+ >>>>> 1 row selected (0.392 seconds) >>>>> >>>>> However, when I attempt to select individual fields i get errors. (see >>>>> below for full stack trace). I've walked through this with the >>> debugger, >>>>> but it seems like the code is breaking before it hits my storage plugin >>> and >>>>> I'm not sure what to do about it. Here's a link to the code: >>>>> https://github.com/cgivre/drill/tree/storage-http/contrib/storage-http >>>>> <https://github.com/cgivre/drill/tree/storage-http/contrib/storage-http> >>> < >>>>> https://github.com/cgivre/drill/tree/storage-http/contrib/storage-http >>> <https://github.com/cgivre/drill/tree/storage-http/contrib/storage-http>> >>>>> >>>>> Any assistance would be greatly appreciated. Thanks!! >>>>> >>>>> >>>>> >>>>> apache drill> !verbose >>>>> verbose: on >>>>> apache drill> SELECT sunset FROM >>>>> http.`/json?lat=36.7201600&lng=-4.4203400&date=2019-10-02`; >>>>> Error: SYSTEM ERROR: AssertionError: Field ordinal 1 is invalid for >>> type >>>>> '(DrillRecordRow[**])' >>>>> >>>>> >>>>> Please, refer to logs for more information. >>>>> >>>>> [Error Id: d7bccd2f-73e6-40d7-9b8a-73a772f65c02 on 192.168.1.21:31010] >>>>> (state=,code=0) >>>>> java.sql.SQLException: SYSTEM ERROR: AssertionError: Field ordinal 1 is >>>>> invalid for type '(DrillRecordRow[**])' >>>>> >>>>> >>>>> Please, refer to logs for more information. >>>>> >>>>> [Error Id: d7bccd2f-73e6-40d7-9b8a-73a772f65c02 on 192.168.1.21:31010] >>>>> at >>>>> >>> org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:538) >>>>> at >>>>> >>> org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(DrillCursor.java:610) >>>>> at >>>>> >>> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:1278) >>>>> at >>>>> >>> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:58) >>>>> at >>>>> >>> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:667) >>>>> at >>>>> >>> org.apache.drill.jdbc.impl.DrillMetaImpl.prepareAndExecute(DrillMetaImpl.java:1102) >>>>> at >>>>> >>> org.apache.drill.jdbc.impl.DrillMetaImpl.prepareAndExecute(DrillMetaImpl.java:1113) >>>>> at >>>>> >>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675) >>>>> at >>>>> >>> org.apache.drill.jdbc.impl.DrillConnectionImpl.prepareAndExecuteInternal(DrillConnectionImpl.java:200) >>>>> at >>>>> >>> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156) >>>>> at >>>>> >>> org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:217) >>>>> at sqlline.Commands.executeSingleQuery(Commands.java:1008) >>>>> at sqlline.Commands.execute(Commands.java:957) >>>>> at sqlline.Commands.sql(Commands.java:921) >>>>> at sqlline.SqlLine.dispatch(SqlLine.java:717) >>>>> at sqlline.SqlLine.begin(SqlLine.java:536) >>>>> at sqlline.SqlLine.start(SqlLine.java:266) >>>>> at sqlline.SqlLine.main(SqlLine.java:205) >>>>> Caused by: org.apache.drill.common.exceptions.UserRemoteException: >>> SYSTEM >>>>> ERROR: AssertionError: Field ordinal 1 is invalid for type >>>>> '(DrillRecordRow[**])' >>>>> >>>>> >>>>> Please, refer to logs for more information. >>>>> >>>>> [Error Id: d7bccd2f-73e6-40d7-9b8a-73a772f65c02 on 192.168.1.21:31010] >>>>> at >>>>> >>> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123) >>>>> at >>>>> org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:422) >>>>> at >>>>> org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:96) >>>>> at >>>>> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:273) >>>>> at >>>>> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:243) >>>>> at >>>>> >>> io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) >>>>> at >>>>> >>> io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:287) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) >>>>> at >>>>> >>> io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) >>>>> at >>>>> >>> io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:312) >>>>> at >>>>> >>> io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:286) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) >>>>> at >>>>> >>> io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) >>>>> at >>>>> >>> io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) >>>>> at >>>>> >>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) >>>>> at >>>>> >>> io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911) >>>>> at >>>>> >>> io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131) >>>>> at >>>>> >>> io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645) >>>>> at >>>>> >>> io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580) >>>>> at >>>>> >>> io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497) >>>>> at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459) >>>>> at >>>>> >>> io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131) >>>>> at java.lang.Thread.run(Thread.java:748) >>>>> Caused by: org.apache.drill.exec.work.foreman.ForemanException: >>>>> Unexpected exception during fragment initialization: Field ordinal 1 is >>>>> invalid for type '(DrillRecordRow[**])' >>>>> at org.apache.drill.exec.work >>>>> .foreman.Foreman.run(Foreman.java:303) >>>>> at .......(:0) >>>>> Caused by: java.lang.AssertionError: Field ordinal 1 is invalid for >>> type >>>>> '(DrillRecordRow[**])' >>>>> at >>>>> org.apache.calcite.rex.RexBuilder.makeFieldAccess(RexBuilder.java:197) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:3694) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter.access$2200(SqlToRelConverter.java:217) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4765) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4061) >>>>> at >>>>> org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:317) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4625) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:3908) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:670) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:627) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3150) >>>>> at >>>>> >>> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:563) >>>>> at >>>>> >>> org.apache.drill.exec.planner.sql.SqlConverter.toRel(SqlConverter.java:414) >>>>> at >>>>> >>> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToRel(DefaultSqlHandler.java:685) >>>>> at >>>>> >>> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:202) >>>>> at >>>>> >>> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:172) >>>>> at >>>>> >>> org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:226) >>>>> at >>>>> >>> org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan(DrillSqlWorker.java:124) >>>>> at >>>>> >>> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:90) >>>>> at org.apache.drill.exec.work >>>>> .foreman.Foreman.runSQL(Foreman.java:591) >>>>> at org.apache.drill.exec.work >>>>> .foreman.Foreman.run(Foreman.java:276) >>>>> ... 1 more >>>>> apache drill>