Ah - good pointer, ok I've gotten a little further - I can 'USE' the right
database and can see my tables now... Before trying drill with any of my
more complicated tables I've created a simple one with 'normal' types:
CREATE TABLE "test_table" (
"id" integer not null default nextval('test_table_id_seq'::regclass),
"name" text,
PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "test_table_pkey" ON "test_table" ("id");
And confirming it exists using SHOW TABLES:
| customers.customers | test_table |
| customers.customers | test_table_id_seq |
| customers.customers | test_table_pkey |
+--------------------------------+-------------------------------------+
But I'm still having problems...
0: jdbc:drill:zk=local> SELECT * FROM test_table;
org.apache.drill.common.exceptions.UserException: DATA_READ ERROR: The JDBC
storage plugin failed while trying setup the SQL query.
sql SELECT *
FROM "customers"."test_table"
plugin customers
[Error Id: 86b40739-0f88-4aa1-bd9a-43a632d40d70 ]
at
org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:534)
at
org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:221)
at
org.apache.drill.exec.physical.impl.ScanBatch.<init>(ScanBatch.java:101)
at
org.apache.drill.exec.physical.impl.ScanBatch.<init>(ScanBatch.java:128)
at
org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch(JdbcBatchCreator.java:40)
at
org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch(JdbcBatchCreator.java:33)
at
org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch(ImplCreator.java:151)
at
org.apache.drill.exec.physical.impl.ImplCreator.getChildren(ImplCreator.java:174)
at
org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch(ImplCreator.java:131)
at
org.apache.drill.exec.physical.impl.ImplCreator.getChildren(ImplCreator.java:174)
at
org.apache.drill.exec.physical.impl.ImplCreator.getRootExec(ImplCreator.java:105)
at
org.apache.drill.exec.physical.impl.ImplCreator.getExec(ImplCreator.java:79)
at
org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:230)
at
org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: relation
"customers.test_table" does not exist
Position: 16
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:616)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:332)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:177)
... 15 more
Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup
the SQL query.
sql SELECT *
FROM "customers"."test_table"
plugin customers
Fragment 0:0
[Error Id: 86b40739-0f88-4aa1-bd9a-43a632d40d70 on
DESKTOP-T4FPNUR.Home:31010]
(org.postgresql.util.PSQLException) ERROR: relation
"customers.test_table" does not exist
Position: 16
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182
org.postgresql.core.v3.QueryExecutorImpl.processResults():1911
org.postgresql.core.v3.QueryExecutorImpl.execute():173
org.postgresql.jdbc2.AbstractJdbc2Statement.execute():616
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags():452
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery():332
org.apache.commons.dbcp.DelegatingStatement.executeQuery():208
org.apache.commons.dbcp.DelegatingStatement.executeQuery():208
org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177
org.apache.drill.exec.physical.impl.ScanBatch.<init>():101
Sorry, I'm a bit helpless to get this going! Thanks again for your help.
Cheers,
Kieran
On 6 November 2015 at 23:48, Jacques Nadeau <[email protected]> wrote:
> 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 <[email protected]> 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 <[email protected]> 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" <[email protected]> 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 <
> [email protected]>
> > > > > 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
> > > > > >
> > > > >
> > > >
> > >
> >
>
