question Re: Hive Functions via ODBC Driver (QlikView) in Support Questions

Hive Functions via ODBC Driver (QlikView)

jwaligora — Fri, 11 Dec 2015 05:00:06 GMT

Hi,

I'm having some trouble executing function-based Hive queries in QlikView (ODBC connector parse error), and I'm looking to understand if the fault lies in the QV syntax, or if functions are simply not supported by the Hive ODBC driver.

Example...

The following works in Hive command line:

select distinct a_date, reverse(a_date) from a_table where a_date > '20151201';

The following fails in QV:

SQL SELECT 
	reverse("a_date")
FROM HIVE.a_db."a_table"
WHERE "a_date" > '20151201';

The QV and connector implementation is fine otherwise; substituting 'reverse' with 'max' executes happily.

Thanks,

Re: Hive Functions via ODBC Driver (QlikView)

nsabharwal — Fri, 11 Dec 2015 05:32:17 GMT

Hive cli - working

select distinct a_date, length(a_date)from a_table where a_date >'20151201';

beeline ?

If beeline works then it's QV issue. Please test

@Joanna Waligora

Re: Hive Functions via ODBC Driver (QlikView)

jwaligora — Fri, 11 Dec 2015 05:48:08 GMT

It works in 'hive (a_db)>', but I also checked in beeline (i.e. without using ODBC driver).

Re: Hive Functions via ODBC Driver (QlikView)

nsabharwal — Fri, 11 Dec 2015 06:06:37 GMT

@Joanna Waligora

Do you mean it works with beeline and jdbc? but not working with QV+ odbc?

Re: Hive Functions via ODBC Driver (QlikView)

nsabharwal — Fri, 11 Dec 2015 06:09:07 GMT

@Joanna Waligora If QV + ODBC issue then please see this https://community.qlik.com/thread/142680

I would say , open a case with them

Re: Hive Functions via ODBC Driver (QlikView)

jwaligora — Fri, 11 Dec 2015 06:10:37 GMT

Correct.

Re: Hive Functions via ODBC Driver (QlikView)

jwaligora — Fri, 11 Dec 2015 06:53:11 GMT

Does confirmation of jdbc functionality conclusively exclude odbc driver as the culprit?

I read the QV community thread earlier, but since the item was unresolved, I started my own. Subsequently it occurred to me that QV folks won't be able to help me if it's a driver limitation.

Re: Hive Functions via ODBC Driver (QlikView)

nsabharwal — Fri, 11 Dec 2015 07:10:06 GMT

@Joanna Waligora Do you have access to hortonworks support? or Are you a customer? I would suggest to open a ticket or in worse case , open a jira ticket to address this.

Re: Hive Functions via ODBC Driver (QlikView)

rgelhausen — Fri, 11 Dec 2015 08:01:12 GMT

You'll need to use the ODBC Data Source Administrator tool to uncheck "Use Native Query" from your ODBC driver installation.

Re: Hive Functions via ODBC Driver (QlikView)

jwaligora — Sat, 12 Dec 2015 02:26:21 GMT

Thanks. It's unchecked by default.

I played with that setting before; setting it to 'on' does cause my previously functional syntax to fail.

Right now (native unchecked), doing a select on [a_date] works, but Hive functions like reverse([a_date]) fail.

Re: Hive Functions via ODBC Driver (QlikView)

aervits — Wed, 03 Feb 2016 23:45:36 GMT

@Joanna Waligora are you still having issues with this? Can you accept best answer or provide your own solution?

Re: Hive Functions via ODBC Driver (QlikView)

jwaligora — Thu, 04 Feb 2016 01:25:52 GMT

The QV support team is currently investigating this problem. I will provide an update / closure once I receive their feedback.

Also, I want to correct my initial statement - max, and length functions work fine, but reverse, instr, and regexp_extract don't.

Re: Hive Functions via ODBC Driver (QlikView)

jwaligora — Wed, 17 Feb 2016 04:45:29 GMT

This problem has been resolved. Errors were caused by QV syntax peculiarities. Details can be viewed in the QV Community post here.

Thanks!