3 Replies Latest reply: Feb 16, 2016 3:40 PM by Joanna Waligora RSS

    Hive ODBC Parsing Error in QV Select Statement

    Joanna Waligora

      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.

        • Re: Hive ODBC Parsing Error in QV Select Statement
          Jesus 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

            • Re: Hive ODBC Parsing Error in QV Select Statement
              Joanna Waligora

              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';

            • Re: Hive ODBC Parsing Error in QV Select Statement
              Joanna Waligora

              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.