What do 'show databases' display? If you do 'use customers' and 'show tables', what does that display?
-- Jacques Nadeau CTO and Co-Founder, Dremio On Fri, Nov 6, 2015 at 3:41 PM, Kieran Benton <m...@kieranbenton.com> wrote: > Thanks Jacques, > I'm a little further - I can now add the postgres storage and I'm no longer > getting the NullReferenceException. However, I've got almost as much of a > confusing error message (with exec.errors.verbose turned on): > > 0: jdbc:drill:zk=local> SELECT * FROM customers.profile; > Nov 06, 2015 11:39:20 PM > org.apache.calcite.sql.validate.SqlValidatorException <init> > SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table > 'customers.profile' not found > Nov 06, 2015 11:39:20 PM org.apache.calcite.runtime.CalciteException <init> > SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 15 to line 1, column 23: Table 'customers.profile' not found > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 23: Table > 'customers.profile' not found > > > [Error Id: 4ef5422b-8c1d-4c66-a5d5-5b62fdae8147 on > DESKTOP-T4FPNUR.Home:31010] > > (org.apache.calcite.tools.ValidationException) > org.apache.calcite.runtime.CalciteContextException: From line 1, column 15 > to line 1, column 23: Table 'customers.profile' not found > org.apache.calcite.prepare.PlannerImpl.validate():179 > org.apache.calcite.prepare.PlannerImpl.validateAndGetType():188 > > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():447 > > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():190 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():159 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():184 > org.apache.drill.exec.work.foreman.Foreman.runSQL():905 > org.apache.drill.exec.work.foreman.Foreman.run():244 > java.util.concurrent.ThreadPoolExecutor.runWorker():-1 > java.util.concurrent.ThreadPoolExecutor$Worker.run():-1 > java.lang.Thread.run():-1 > Caused By (org.apache.calcite.runtime.CalciteContextException) From line > 1, column 15 to line 1, column 23: Table 'customers.profile' not found > sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2 > sun.reflect.NativeConstructorAccessorImpl.newInstance():-1 > sun.reflect.DelegatingConstructorAccessorImpl.newInstance():-1 > java.lang.reflect.Constructor.newInstance():-1 > org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405 > org.apache.calcite.sql.SqlUtil.newContextException():685 > org.apache.calcite.sql.SqlUtil.newContextException():673 > > org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():3930 > org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():106 > org.apache.calcite.sql.validate.AbstractNamespace.validate():86 > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():877 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():863 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2777 > <snip> > > I've tried different combinations of table name... customers.profile > (storage name + table), customers.customers.profile (storage name + db name > + table) and customers.`customers.profile`. Same result. > > What can I do to diagnose whats wrong now? > > Cheers, > Kieran > > On 6 November 2015 at 22:02, Jacques Nadeau <jacq...@dremio.com> wrote: > > > Few answers: > > > > > MSSQL three part naming: > > Right now in Drill a particular connection will need to be focused on a > > particular database in MSSQL. If you need to connect to multiple > databases, > > you'll need to configure multiple instance of the JDBC storage plugin. > > > > 1. Is there anything I can do to up the logging and so be able to track > > > these kinds of issues myself and maybe contribute fixes back? > > > > > > > Best two things: > > > > 1) Make sure to enable verbose errors via: > > > > ALTER SYSTEM SET `exec.errors.verbose` = true; > > > > 2) Install Logback Lilith (https://github.com/huxi/lilith) and enable > the > > socket appender by uncommenting these blocks in your logback file: > > > > > > > https://github.com/apache/drill/blob/master/distribution/src/resources/logback.xml#L19 > > > > > https://github.com/apache/drill/blob/master/distribution/src/resources/logback.xml#L80 > > > > > > 2. Im trialling using some fairly exotic types in postgres, mainly arrays > > > and hstore. I'm assuming drill won't support those out of the box and > > I'll > > > need to use views to flatten them? If I myself wanted to contribute > > support > > > back where would I start? > > > > > > > > > Two main pieces of code. The first is in the Calcite Project which Drill > > uses. It is here: > > > > > > > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java > > > > We would need to make sure that this correctly presents the data types > > desired as part of the table schema. > > > > The second thing would be to address the reading behavior in the Drill > > RecordReader in this code: > > > > > > > https://github.com/apache/drill/blob/master/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcRecordReader.java > > > > Would be happy to help in any way. > > > > Thanks, > > Jacques > > > > > > > > > Cheers, > > > Kieran > > > On 6 Nov 2015 7:49 pm, "Jacques Nadeau" <jacq...@dremio.com> wrote: > > > > > > > He Kieran, > > > > > > > > There were some issues with the 1.2 JDBC storage plugin. It had > issues > > > with > > > > certain data types including TEXT/BLOB. We're in the process of > > releasing > > > > version 1.3 of Drill which should solve many of these issues. Can you > > try > > > > the current release candidate of Drill 1.3 to see if that solves the > > > issues > > > > you are seeing? > > > > > > > > You can download the release candidate here: > > > > > > > > http://people.apache.org/~jacques/apache-drill-1.3.0.rc0/ > > > > > > > > thanks, > > > > Jacques > > > > > > > > -- > > > > Jacques Nadeau > > > > CTO and Co-Founder, Dremio > > > > > > > > On Fri, Nov 6, 2015 at 10:09 AM, Kieran Benton <m...@kieranbenton.com> > > > > wrote: > > > > > > > > > Hi all, > > > > > I'm having a whale of a time getting the JDBC storage support > working > > > > with > > > > > Drill 1.2 :) Could someone please give me a pointer at what I'm > doing > > > > > wrong, or even just confirm to me that the feature isnt baked yet > and > > > has > > > > > issues? > > > > > > > > > > I've done the following (I'm on Windows for my sins): > > > > > > > > > > 1. Installed Drill in embedded mode > > > > > > > > > > 2. Added the postgres jdbc driver to the jars / thirdparty folder > as > > > > > referenced at > > > > > > > > > > > > > > > > > > > > http://stackoverflow.com/questions/33311547/unable-to-configure-postgresql-jdbc-driver-using-apache-drill-1-2 > > > > > > > > > > 3. ./sqlline.bat > > > > > > > > > > 4. !connect jdbc:drill:zk=local (admin/admin) > > > > > > > > > > 5. Accessed the web console and added a new storage 'customers' > with > > > the > > > > > definition: > > > > > { > > > > > "type": "jdbc", > > > > > "driver": "org.postgresql.Driver", > > > > > "url": "jdbc:postgresql://localhost:5432/customers", > > > > > "username": "customers", > > > > > "password": "customers", > > > > > "enabled": true > > > > > } > > > > > > > > > > 6. Success! (took a while to get this far, finding the 'right' > driver > > > > wasnt > > > > > easy) > > > > > > > > > > 7. Try 'SELECT * FROM customers.profile;' in the console -> > > > > > > > > > > Error: VALIDATION ERROR: java.lang.NullPointerException > > > > > [Error Id: 62cb35b1-db49-4740-ac38-4d748a080f3a on > > 172.31.9.168:31010] > > > > > (state=,code=0) > > > > > > > > > > > > > > > > > > > > And then I'm completely stuck. Where do I go from here? > > > > > > > > > > Cheers, > > > > > Kieran > > > > > > > > > > > > > > >
