Hive ODBC Parsing Error in QV Select Statement

[jwaligora]

Hi,

I'm having the exact same problem as documented in Hive error, but figured that rather than appending to a stale thread I would start a new one to get some fresh eyes on the topic.

SQL SELECT

"the_date" as Date

"red_fox" as Fox,

"chubby_chick" as Chicken,

"regexp_extract(fluffy_bunny, '\/(.*)-', 1)" as Bunny

FROM HIVE.the_animals."farm_land"

WHERE "the_date" > '$(vHiveStartDate)'

AND "the_date" < '$(vHiveEndDate)';

Breaks with an error message of the sort:

Error: SQL##f - SqlState: S1000, ErrorCode: 35, ErrorMsg: [Hortonworks][HiveODBC] (35) Error from Hive: error code: '40000' error message: 'Error while compiling statement: FAILED: ParseException line 10:14 cannot recognize input near 'the_animals' '.' '"farm_land"' in table name'.

The Select statement works just fine without the regex line in it. I've tried various quotation mark combinations with no success thus far.

Can anyone assist with the syntax?

...for the record, a select the_date, regexp_extract(fluffy_bunny, '\/(.*)-', 1) from farm_land; line executes without a problem inside Hive command line. It extracts f-bunnySoft from string fluffy/f-bunnySoft-4u54ou2oiu53ou5.

Thanks,

J.

[Jesús_Centeno]

Hello Joanna,

Can you please provide more information as far as the version of the ODBC driver that you are using and also how you are incorporating such SQL statement in your Qlik app?

Thanks,

J

[jwaligora]

Our QV prime has raised this up to the QV Support team, so I'm expecting some clarity to follow shortly.

Tried:

• x86: Hortonworks Hive ODBC Driver with SQL Connector 2.0.5
• x64: Hortonworks Hive ODBC Driver with SQL Connector 1.4.8
• With & without 'Force 32-bit' setting

Not much by way of 'incorporation'. I just put the following into the script box, and then attempt to execute.

LET vConnName = 'Buzz';

ODBC CONNECT TO '$(vConnName)';

FurryTab:

SQL SELECT

    max(the_date) as Latest  // works fine

    distinct reverse(the_date) as Date_Flip  // does not work

    count(instr(fluffy_bunny,'black')>0) as Shady_Bunny  // does not work

FROM HIVE.the_animals."farm_land"

WHERE [the_date] >= '20160107' and [the_date] <= '20160109';

[jwaligora · Accepted solution]

My problems were resolved with a bit of hand-holding from QV support (Thanks!)

Basically I've been a bit overzealous with my use of double quotes and square brackets. Queries work fine after removal of quotes off the table name and removal of quotes (or square brackets) off field names referenced in the WHERE clause.

The following works:

FurryTab:

SQL SELECT

    distinct reverse(the_date) as Date_Flip

FROM HIVE.the_animals.farm_land   // NO quotes!

WHERE the_date >= '20160107' and the_date <= '20160109';   // NO quotes or brackets around field names!

For supplementary info, you can check out the "Quotation Marks in Scripting" help file. The part about double quotes sometimes designating variables instead of fields is noteworthy.

It is still unclear why the max() function processed correctly regardless of the presence of "" and [], while the reverse() function did not.

Finally, using backquotes `` aka grave accents around field names inside the WHERE clause, seemed to cause no issues.