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
> >
> >
> >
> >
> >
> >
> >
>
|