You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Gonzalo Ortiz Jaureguizar <go...@gmail.com> on 2017/09/01 07:27:50 UTC
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 <go...@gmail.com>:
> I think I sent that email to the wrong address. Reposting it here
>
>
>
> ---------- Forwarded message ----------
> From: Gonzalo Ortiz Jaureguizar <go...@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
>
>
>
>
>
>
>
Re: Problems using Postgres datasource
Posted by William Dickenson <w....@castsoftware.com>.
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 <go...@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 <go...@gmail.com>:
>
>> I think I sent that email to the wrong address. Reposting it here
>>
>>
>>
>> ---------- Forwarded message ----------
>> From: Gonzalo Ortiz Jaureguizar <go...@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
>>
>>
>>
>>
>>
>>
>>
Re: Problems using Postgres datasource
Posted by Gonzalo Ortiz Jaureguizar <go...@gmail.com>.
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 <kk...@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 <go...@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
> >
> >
> >
> >
> >
> >
> >
>
RE: Problems using Postgres datasource
Posted by Kunal Khatua <kk...@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 <go...@gmail.com>:
> I think I sent that email to the wrong address. Reposting it here
>
>
>
> ---------- Forwarded message ----------
> From: Gonzalo Ortiz Jaureguizar <go...@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
>
>
>
>
>
>
>