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
> > > >
> > >
> >
>
