Can't insert to impala via pyodbc due to 'unsupported query' error

user_10134422 0
Viewed 33

I have an insert query which works via hue but when performed via pyodc receives the error:

[CLOUDERAODBC] (140) Unsupported Query.

The query itself is stored in an iql file and read into python for execution

INSERT INTO db_name_tbl_name
SELECT DISTINCT field_name, now() 
FROM db_name.tbl_name 
WHERE field_name not in (SELECT field_name from someothertable)

python:

 with open('file/location.iql') as script:
    statement = script.read()
    print(statement)
    crsr = conn.cursor()
    crsr.execute(statement)
impala pyodbc python
1 Answers

The problem is the DISTINCT part.

For some reason the driver does not support performing a distinct query after an insert via pyodbc. This is the case for other query complexities raised during an insert as well. Your query will work in hue but is not supported via the cloudera odbc driver.

As a workaround you can do any complex parts of the query using a CTE and then do the insert, as follows:

WITH result_set AS (
  SELECT DISTINCT field_name
  FROM db_name.tbl_name
  WHERE field_name not in (SELECT field_name FROM someothertable)

INSERT INTO db_name.tbl_name
SELECT field_name, now() FROM result_set
;

I hope this helps somebody!

user_101344220
Related