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:41:41 GMT
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 <kkhatua@mapr.com>:

> 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:golthiryus@gmail.com]
> Sent: Friday, September 01, 2017 12:28 AM
> To: user@drill.apache.org
> 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 <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