drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gonzalo Ortiz Jaureguizar <golthir...@gmail.com>
Subject Re: Problems using Postgres datasource
Date Fri, 01 Sep 2017 07:27:50 GMT
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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message