Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive odbc with prepared statements: ParseException

SOLVED Go to solution
Highlighted

Hive odbc with prepared statements: ParseException

Expert Contributor

Context: Hive3, HDP 3.1. Tests done with Python/odbc (official HDP driver) under Windows and Linux.

I ran the following queries:

  1. "select ? as lic, ? as cpg"
  2. "select * from (select ? as lic, ? as cpg) as t"
  3. "with init as (select ? as lic, ? as cpg) select * from init",

1) and 2) work fine, and give me the expected result. 3 gives me a ParseException :

Error while compiling statement: FAILED: ParseException line 1:21 cannot recognize input near '?' 'as' 'lic' in select clause (80) (SQLPrepare)")

The exact same statements ran with java/jdbc work fine. Note that 2) looks like is a workaround for 3) but it works for this tiny example, not for bigger queries.

Is there something I can do to have ODBC working as expected? Alternatively, where can I find the limits of the ODBC driver?

For full context, the full test code is as follow:

cnxnstr = 'DSN=HiveProd'
cnxn = pyodbc.connect(cnxnstr, autocommit=True)

cursor = cnxn.cursor()
queries = [ 
    "with init as (select ? as lic, ? as cpg) select * from init", 
    "select 2 * ? as lic, ? as cpg", 
    "select * from (select ? as lic, ? as cpg) as t", 
]

for q in queries:
    print("\nExecuting " + q)
    try:
        cursor.execute(q, '1', '2')
    except pyodbc.ProgrammingError as e:
        print(e)
        continue
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Hive odbc with prepared statements: ParseException

Expert Contributor

The ODBC driver does not support all syntax niceties (no CTE) and if there is a syntax error, it will output a completely irrelevant message, which adds a lot to the confusion. To actually see the actual error, you need to add ODBC logging and look at the log files.

1 REPLY 1

Re: Hive odbc with prepared statements: ParseException

Expert Contributor

The ODBC driver does not support all syntax niceties (no CTE) and if there is a syntax error, it will output a completely irrelevant message, which adds a lot to the confusion. To actually see the actual error, you need to add ODBC logging and look at the log files.