Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
Our QV prime has raised this up to the QV Support team, so I'm expecting some clarity to follow shortly.
Tried:
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';
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.