Thanks for your fast answer. I bet it is a misconfiguration from my side, but I'm pretty blocked and it seems the typical thing that is easily detected by a 3rd observer ;)
2017-09-01 9:33 GMT+02:00 Kunal Khatua <[email protected]>: > I'm not very familiar with the details of Postgres, but I do so see people > occassionally asking about it > > Have you checked the mailing list archives? You might find your answers > there. > > -----Original Message----- > From: Gonzalo Ortiz Jaureguizar [mailto:[email protected]] > Sent: Friday, September 01, 2017 12:28 AM > To: [email protected] > Subject: Re: Problems using Postgres datasource > > 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 > > > > > > > > > > > > > > >
