Our QV prime has raised this up to the QV Support team, so I'm expecting some clarity to follow shortly.
- 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)';
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
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:
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.