drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From William Dickenson <w.dicken...@castsoftware.com>
Subject Re: Problems using Postgres datasource
Date Fri, 01 Sep 2017 07:31:49 GMT
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 <golthiryus@gmail.com> 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 <golthiryus@gmail.com>:
> 
>> I think I sent that email to the wrong address. Reposting it here
>> 
>> 
>> 
>> ---------- Forwarded message ----------
>> From: Gonzalo Ortiz Jaureguizar <golthiryus@gmail.com>
>> Date: 2017-08-25 11:24 GMT+02:00
>> Subject: Problems using Postgres datasource
>> To: user-owner@drill.apache.org
>> 
>> 
>> 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
>> 
>> 
>> 
>> 
>> 
>> 
>> 

Mime
View raw message