Gonzalo - we use drill against Postgres extensively although almost exclusively with odbc - that being said, our expert returns on Tuesday so I will walk your question over
Thanks Sent from my iPhone > On Sep 1, 2017, at 9:28 AM, Gonzalo Ortiz Jaureguizar <[email protected]> > wrote: > > I do not want to look like I'm spamming, but there has been some activity > on the list and yet no one has answered me. I don't know if the topic is > not be very interesting or it is out of scope. Maybe the silence is due to > the fact that the email was forwarded and you may not read the interesting > part. As it can be a bug, it may be better to create a ticket instead of > talking about this list. What do you recommend me? > > Bests, > > Gonzalo > > 2017-08-29 9:08 GMT+02:00 Gonzalo Ortiz Jaureguizar <[email protected]>: > >> I think I sent that email to the wrong address. Reposting it here >> >> >> >> ---------- Forwarded message ---------- >> From: Gonzalo Ortiz Jaureguizar <[email protected]> >> Date: 2017-08-25 11:24 GMT+02:00 >> Subject: Problems using Postgres datasource >> To: [email protected] >> >> >> Hi there, >> >> My name is Gonzalo and I'm working at Logtrust, where we have use our own >> sql-like database to store and query logs. We provide a JDBC driver and we >> would like to join some data with a Posgres database. I'm evaluating >> different approaches and the two more promising are Apache Drill with two >> datasources or, if it doesn't work, use Apache Calcite to implement our own >> solution. I expect to find problems when trying to integrate our own JDBC >> driver with Drill (as I am not sure how much JDBC compliat we are), so I >> decided to first try the JDBC connector with the Postgres database. Sadly, >> I found some problems. >> >> I have followed Drill's documentation, installing Drill 1.11 on my linux >> ubuntu 17.04 notebook and then installed postgres from the official >> repositories. After creating a postgres user and a database, I started >> Drill on embedded mode and execute the following query to test everything >> is working: >> >> 0: jdbc:drill:zk=local> select * from INFORMATION_SCHEMA.`TABLES`; >>> +----------------+---------------------+-------------+---------------+ >>> | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | >>> +----------------+---------------------+-------------+---------------+ >>> | DRILL | INFORMATION_SCHEMA | VIEWS | SYSTEM_TABLE | >>> | DRILL | INFORMATION_SCHEMA | CATALOGS | SYSTEM_TABLE | >>> | DRILL | INFORMATION_SCHEMA | COLUMNS | SYSTEM_TABLE | >>> | DRILL | INFORMATION_SCHEMA | SCHEMATA | SYSTEM_TABLE | >>> | DRILL | INFORMATION_SCHEMA | TABLES | SYSTEM_TABLE | >>> | DRILL | sys | memory | SYSTEM_TABLE | >>> | DRILL | sys | options | SYSTEM_TABLE | >>> | DRILL | sys | threads | SYSTEM_TABLE | >>> | DRILL | sys | drillbits | SYSTEM_TABLE | >>> | DRILL | sys | boot | SYSTEM_TABLE | >>> | DRILL | sys | version | SYSTEM_TABLE | >>> +----------------+---------------------+-------------+---------------+ >>> >> >> Then I place the postgres driver on jar/3rdparty, configure the >> datasource, quits on the shell and start it again. The configuration I have >> used is the following: >> >>> { >>> "type": "jdbc", >>> "driver": "org.postgresql.Driver", >>> "url": "jdbc:postgresql://127.0.0.1/gortiz", >>> "username": "gortiz", >>> "password": <the password>, >>> "enabled": true >>> } >>> >> So i sould be connected to the database *gortiz* on 127.0.0.1 with >> username *gortiz*. Just for the record, I have also tried to connect to >> jdbc:postgresql://127.0.0.1/testdrill and I have the same problems. >> >> When I repeat the query, I get the following error: >> >>> Error: SYSTEM ERROR: NullPointerException: Error. Type information for >>> table postgres.gortiz.pg_aggregate_fnoid_index provided is null. >>> >>> Fragment 0:0 >>> >>> [Error Id: c41990a5-a114-411f-a06f-330f60a44318 on anduar:31010] >>> (state=,code=0) >> >> I am not expert on postgres (although I have been working on that db some >> time) but as far as I know and I don't know what is the relation p >> g_aggregate_fnoid_index, but Google doesn't have too many records about >> that. >> >> If I execute a select * from INFORMATION_SCHEMA.`COLUMNS` I can see tons >> of rows, some of them having TABLE_SCHEMA = 'postgres' (the name I gave to >> the storage plugin). If I execute select * from >> INFORMATION_SCHEMA.`COLUMNS` where TABLE_SCHEMA = 'postgres' then I get >> the following error: >> >>> 0: jdbc:drill:zk=local> select * from INFORMATION_SCHEMA.`COLUMNS` where >>> TABLE_SCHEMA = 'postgres'; >>> Error: SYSTEM ERROR: NullPointerException >>> >>> Fragment 0:0 >>> >>> [Error Id: 79d21ff1-cbc1-4a8a-b6b7-c88ea7ea9b8d on anduar:31010] >>> (state=,code=0) >>> >> >> I also checked the SCHEMATA table, which give me the following: >> >>> +---------------+------------------------------+------------ >>> ---+----------------+-------------+ >>> | CATALOG_NAME | SCHEMA_NAME | SCHEMA_OWNER | >>> TYPE | IS_MUTABLE | >>> +---------------+------------------------------+------------ >>> ---+----------------+-------------+ >>> | DRILL | INFORMATION_SCHEMA | <owner> | >>> ischema | NO | >>> | DRILL | cp.default | <owner> | >>> file | NO | >>> | DRILL | dfs.default | <owner> | >>> file | NO | >>> | DRILL | dfs.root | <owner> | >>> file | NO | >>> | DRILL | dfs.tmp | <owner> | >>> file | YES | >>> | DRILL | postgres.gortiz | <owner> | >>> jdbc | NO | >>> | DRILL | postgres.information_schema | <owner> | >>> jdbc | NO | >>> | DRILL | postgres.pg_catalog | <owner> | >>> jdbc | NO | >>> | DRILL | postgres.public | <owner> | >>> jdbc | NO | >>> | DRILL | postgres | <owner> | >>> jdbc | NO | >>> | DRILL | sys | <owner> | >>> system-tables | NO | >>> +---------------+------------------------------+------------ >>> ---+----------------+-------------+ >>> >> >> I don't know if a query on the table *test* on postgres should be written >> on drill as select * from postgres.`gortiz.test` or select * from >> postgres.`test', but both queries fail on the same way: >> >>> 0: jdbc:drill:zk=local> select * from postgres.`gortiz.test`; >>> Aug 25, 2017 11:13:22 AM >>> org.apache.calcite.sql.validate.SqlValidatorException >>> <init> >>> SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table >>> 'postgres.gortiz.test' not found >>> Aug 25, 2017 11:13:22 AM org.apache.calcite.runtime.CalciteException >>> <init> >>> SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, >>> column 15 to line 1, column 22: Table 'postgres.gortiz.test' not found >>> Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 22: >>> Table 'postgres.gortiz.test' not found >>> >>> SQL Query null >>> >>> [Error Id: 60a6a6d6-8eb3-470f-a36e-181378ccb565 on anduar:31010] >>> (state=,code=0) >>> 0: jdbc:drill:zk=local> select * from postgres.`test`; >>> Aug 25, 2017 11:13:31 AM >>> org.apache.calcite.sql.validate.SqlValidatorException >>> <init> >>> SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table >>> 'postgres.test' not found >>> Aug 25, 2017 11:13:31 AM org.apache.calcite.runtime.CalciteException >>> <init> >>> SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, >>> column 15 to line 1, column 22: Table 'postgres.test' not found >>> Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 22: >>> Table 'postgres.test' not found >>> >>> SQL Query null >>> >>> [Error Id: d8c28bb3-388d-4307-b25f-66ab0d1b6018 on anduar:31010] >>> (state=,code=0) >>> >> >> First I try all these sentences by using postgres 10 and the last JDBC >> driver (https://jdbc.postgresql.org/download/postgresql-42.1.4.jar). Then >> I tried other combinations like: >> >> - Postgres 9.6 and driver postgresql-42.1.4.jre7.jar >> - Postgres 9.6 and driver postgresql-42.1.4.jre6.jar >> - Postgres 9.2 (the older I can find on the apt repos) and driver >> 9.1-903 JDBC 4 (Your documentation recomends 9.1-901-1.jdbc4, but 9.1-903 >> JDBC 4 is the closest I could find). >> >> With all configurations, the results are the same. Do you know whar is >> happening? It may be due to some misconfiguration on my side, but I'm >> pretty blocked right now. >> >> Thanks for your time, >> >> Gonzalo >> >> >> >> >> >> >>
