With regards to the SQuirrel issue (and any JDBC client), I believe you can tell the you can tell Drill to return a JSON string for all complex objects to the client by setting a configuration parameter. Upon initial glance, it looks like you need to do this by setting a parameter at the JVM level. Can you try starting SQuirrel with the following additional option:
-Ddrill.client.supports-complex-types=false -- Jacques Nadeau CTO and Co-Founder, Dremio On Wed, Jan 6, 2016 at 3:45 PM, Eric Pederson <[email protected]> wrote: > Dear all: > > I'm a new Drill user using version 1.0 (we are going to be upgrading to the > latest MapR version, just not sure when). > > We are querying JSON data that is not too deep but has a few levels. My > questions: > > When I query one of the top level elements using sqlline the output > contains a string with the JSON nested under that element name. When I use > SQuirrel to do the same queries I get "<Error>" for any nested fields or > arrays. I was able to query elements that are arrays using > flatten(element). Is there a similar function to convert nested > JSON objects to strings? I tried using CAST(element as STRING) and > CAST(element as VARCHAR) and got this error: > > Error: SYSTEM ERROR: org.apache.drill.exec.exception.SchemaChangeException: > Failure while trying to materialize incoming schema. Errors: > > Error in expression at index -1. Error: Missing function implementation: > [castVARCHAR(MAP-REQUIRED, BIGINT-REQUIRED)]. Full expression: --UNKNOWN > EXPRESSION--.. > > > I also tried CONVERT_FROM/CONVERT TO and got these. > > Error: SYSTEM ERROR: org.apache.drill.exec.exception.SchemaChangeException: > Failure while trying to materialize incoming schema. Errors: > > Error in expression at index -1. Error: Missing function implementation: > [convert_fromjson(MAP-REQUIRED)]. Full expression: --UNKNOWN > EXPRESSION--.. > > > Is it possible to convert from JSON to string using SQuirrel? I haven't > tried with raw JDBC Java code but that's next on my list. > > I tried to use the JDBC driver in DB Visualizer and got this error. No > queries worked. > > 18:33:22 [ERROR ExecutorRunner-pool-3-thread-1 C.?] Internal error while > executing: select b.wcmo_deal.`INTEX$TRAN... > > java.lang.UnsupportedOperationException > > at > > org.apache.drill.exec.vector.accessor.GenericAccessor.isNull(GenericAccessor.java:33) > > at > > org.apache.drill.exec.vector.accessor.BoundCheckingAccessor.isNull(BoundCheckingAccessor.java:49) > > at > > org.apache.drill.jdbc.impl.TypeConvertingSqlAccessor.isNull(TypeConvertingSqlAccessor.java:51) > > at > > org.apache.drill.jdbc.AvaticaDrillSqlAccessor.wasNull(AvaticaDrillSqlAccessor.java:81) > > at > org.apache.drill.jdbc.DrillAccessorList.wasNull(DrillAccessorList.java:59) > > at > org.apache.drill.jdbc.DrillCursor.wasNull(DrillCursor.java:210) > > at > net.hydromatic.avatica.AvaticaResultSet.wasNull(AvaticaResultSet.java:201) > > at com.onseven.dbvis.J.B.U.?(Z:399) > > > Are the newer JDBC drivers backwards compatible with the older servers? > > A couple other questions. We have impersonation enabled. When I log in to > sqlline or SQuirrel with an account that as permission to read the > directory and files everything works ok. Otherwise I get an error "Table > xxxx not found". That is fine. How do I supply a username to the Drill > web interface? All queries using the web interface fail with the "Table > xxxx not found" error. I also ran across the REST interface information in > the user mailing list archives and tried that. I can query the > INFORMATION_SCHEMA but not our tables. I tried passing the username as > http://user:password@host:8047/query.json but that didn't work. How do I > supply a username to the REST API? > > Finally, speaking of the REST API, once we have the authentication part > resolved, will it return actual nested JSON elements in the results if that > is what is in the file? (That is, not as a string, but as nested elements > in the result?). The JDBC interface seems to be strictly tabular (which > makes sense). > > Thanks! > > > > > -- > Sent from Gmail Mobile >
