Hi,
Thanks for the info. I am trying to execute the following SQL query from a
Java app using Kylin JDBC driver. The same query works fine in Impala and
am able to catch the exception below and redirect to Impala successfully
from the Java app.
Kylin is on Ubuntu 12.04 64bit Linux machine if that helps. HBase is CdH
0.98.6. Kylin is 1.5.3 pre-built binary.
Java app is run from Windows machine from eclipse. It is using the right
Kylin JDBC 1.5.3 drivers to connect.
Query: select productid, sum(quantity) from hive_aggregator_kylin where
quantity < 100 group by productid;
Error Trace:
*java.sql.SQLException*: Error while executing SQL "select productid,
sum(quantity) from hive_aggregator_kylin where quantity < 100 group by
productid": *java.sql.SQLException*:*java.io.IOException*: POST failed,
error code 500 and response: {"url":"http://
<http://10.139.9.238:7070/kylin/api/query>hostip
<http://10.139.9.238:7070/kylin/api/query>:7070/kylin/api/query
<http://10.139.9.238:7070/kylin/api/query>","exception":"Error while
executing SQL \"select productid, sum(quantity) from hive_aggregator_kylin
where quantity < 100 group by productid\": null"}
at
org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.Helper.createException(
*Helper.java:56*)
at
org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.Helper.createException(
*Helper.java:41*)
at
org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.AvaticaStatement.executeInternal(
*AvaticaStatement.java:143*)
at
org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.AvaticaStatement.executeQuery(
*AvaticaStatement.java:186*)
at Kylin_Impala_JDBC.main(*Kylin_Impala_JDBC.java:27*)
Caused by: *java.lang.RuntimeException*:*java.sql.SQLException*:
*java.io.IOException*: POST failed, error code 500 and response: {"url":"
http:// <http://10.139.9.238:7070/kylin/api/query>hostip
<http://10.139.9.238:7070/kylin/api/query>:7070/kylin/api/query
<http://10.139.9.238:7070/kylin/api/query>","exception":"Error while
executing SQL \"select productid, sum(quantity) from hive_aggregator_kylin
where quantity < 100 group by productid\": null"}
at org.apache.kylin.jdbc.KylinMeta.prepareAndExecute(
*KylinMeta.java:81*)
at
org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(
*AvaticaConnection.java:571*)
at
org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.AvaticaStatement.executeInternal(
*AvaticaStatement.java:135*)
... 2 more
Caused by: *java.sql.SQLException*:*java.io.IOException*: POST failed,
error code 500 and response: {"url":"http://
<http://10.139.9.238:7070/kylin/api/query>hostip
<http://10.139.9.238:7070/kylin/api/query>:7070/kylin/api/query
<http://10.139.9.238:7070/kylin/api/query>","exception":"Error while
executing SQL \"select productid, sum(quantity) from hive_aggregator_kylin
where quantity < 100 group by productid\": null"}
at org.apache.kylin.jdbc.KylinResultSet.execute(
*KylinResultSet.java:64*)
at
org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.AvaticaConnection$1.execute(
*AvaticaConnection.java:566*)
at org.apache.kylin.jdbc.KylinMeta.prepareAndExecute(
*KylinMeta.java:77*)
... 4 more
Caused by: *java.io.IOException*: POST failed, error code 500 and response:
{"url":"http:// <http://10.139.9.238:7070/kylin/api/query>hostip
<http://10.139.9.238:7070/kylin/api/query>:7070/kylin/api/query
<http://10.139.9.238:7070/kylin/api/query>","exception":"Error while
executing SQL \"select productid, sum(quantity) from hive_aggregator_kylin
where quantity < 100 group by productid\": null"}
at org.apache.kylin.jdbc.KylinClient.asIOException(
*KylinClient.java:414*)
at org.apache.kylin.jdbc.KylinClient.executeKylinQuery(
*KylinClient.java:374*)
at org.apache.kylin.jdbc.KylinClient.executeQuery(
*KylinClient.java:325*)
at org.apache.kylin.jdbc.KylinResultSet.execute(
*KylinResultSet.java:62*)
... 6 more
Exception in thread "main"*java.lang.NullPointerException*
at Kylin_Impala_JDBC.main(*Kylin_Impala_JDBC.java:36*)
Thanks
Best
Sarnath
On Sep 7, 2016 8:55 PM, "ShaoFeng Shi" <[email protected]> wrote:
> 1. Could you please provide the error trace?
>
> 3. From a recent version Kylin start to support "select *" with the
> aggregated data in cube.
>
> 2016-09-07 21:40 GMT+08:00 Sarnath K <[email protected]>:
>
> > Hi,
> > Today I tested this routing feature. It did not work as expected.
> >
> > 1. When I execute Group By queries along with WHERE conditions on a plain
> > cube built without conditions, the JDBC driver simply throws an
> > exception. I was expecting Kylin to redirect to impala which I had
> > configured through hive.url configuration parameter. This did not
> happen. I
> > see the code that tries olapenumerator and if that catches exception,
> > redirects to Hive enumerator. But the output I see does not correspond to
> > the code. What am I missing?
> >
> > 2. For the scenario described above, I am able to catch the exception in
> > Java app code and redirect to Impala. This works like a charm and is very
> > fast. Not sure if self-service BI tools will do all these. Consider
> > filtering a pivot chart. This translates to a SQL with a WHERE followed
> by
> > a GROUP BY. This is not a rare case but rather commonly occurring case.
> >
> > 3. When I issue 'select * from table', Kylin returns cube data instead
> of
> > giving raw table contents. This is a bit unexpected. But I remember
> > reading somewhere in the forums earlier that this is actually a feature.
> > Does my memory serve me right?
> >
> > Thanks,
> > Best,
> > Sarnath
> > Sent from 1+3
> >
> > On Sep 2, 2016 9:37 AM, "Sarnath K" <[email protected]> wrote:
> >
> > > Sure. I assume hive routing is enabled by default so that I should be
> > able
> > > to do 'select *' queries through JDBC. This is what we have been trying
> > > with 1.1 and t did not work. The webapp does not use routing and it is
> > > documented. No worries on the webapp part.
> > >
> > > Is there any docs on uninstalling Kylin -remove all HBase tables,
> > > Meta-Data and wipe of all intermediate hive tables etc..?
> > >
> > > Also, I am using prebuilt binaries for HBase 0.98.
> > > We are using HBase 0.98.6 that comes with CDH.. Hope this config works.
> > > On Sep 2, 2016 6:43 AM, "ShaoFeng Shi" <[email protected]> wrote:
> > >
> > >> Hi Sarnath,
> > >>
> > >> For kylin.sh on Ubuntu issue, there is an open JIRA (
> > >> https://issues.apache.org/jira/browse/KYLIN-1978), would you like to
> > >> contribute your finding as a patch? thank you!
> > >>
> > >> The "kylin.route.hive.enabled" isn't in use anymore; what need is just
> > >> "hive.url", "hive.user" and "hive.password".
> > >>
> > >> All the config parameters can be found in the java
> > >> class org.apache.kylin.common.KylinConfigBase.java; There is a work
> > item
> > >> about adding doc for these parameters, we will add it soon.
> > >>
> > >> Changing kylin.properties need restart kylin server to take effective;
> > >> Besides I havn't heard such problem (even with 1.1), please retry and
> > >> report problem with detail information and we can have a check.
> > >>
> > >>
> > >> 2016-09-01 23:18 GMT+08:00 Sarnath K <[email protected]>:
> > >>
> > >> > I just downloaded 1.5.3 version of Kylin. The startup scripts fail
> in
> > >> > Ubuntu because of "sh" in place of "bash". Replaced that in 2
> scripts
> > to
> > >> > get going.
> > >> >
> > >> > And, apart from hive.URL and other variables, should I also set
> > >> > kylin.route.hive.enabled to true to enable routing to Hive?
> > >> >
> > >> > Where can I find a list of all config vars? Looks like the
> > >> documentation is
> > >> > very sparse.
> > >> >
> > >> > Also, the webapp does not reflect kylin.properties well. Even after
> > >> > refreshes.... At least this was the case with 1.1incubating.
> > >> > On Aug 30, 2016 12:40 PM, "ShaoFeng Shi" <[email protected]>
> > >> wrote:
> > >> >
> > >> > > Hi Sarnath, I totally agree with you; we're looking forward to see
> > >> your
> > >> > > sharing and patch.
> > >> > >
> > >> > > 2016-08-30 13:47 GMT+08:00 Sarnath K <[email protected]>:
> > >> > >
> > >> > > > Sure. I just hope this never goes away. It is a great feature
> and
> > BI
> > >> > > tools
> > >> > > > like Tableau can work seamlessly. And, it is a point I keep
> > telling
> > >> > > > customers too. It is a great optimization that is really
> awesome.
> > >> > > > I will test it with Impala and post back.
> > >> > > >
> > >> > >
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Best regards,
> > >> > >
> > >> > > Shaofeng Shi
> > >> > >
> > >> >
> > >>
> > >>
> > >>
> > >> --
> > >> Best regards,
> > >>
> > >> Shaofeng Shi
> > >>
> > >
> >
>
>
>
> --
> Best regards,
>
> Shaofeng Shi
>
