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
Highlighted

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.

View solution in original post

1 REPLY 1
Highlighted

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.

View solution in original post

Don't have an account?
Coming from Hortonworks? Activate your account here