I have an SSIS package that brings data from Impala database and loads into SQL server - gateway I'm using here ODBC-32 bit. The issue here is after SSIS package getting 1M rows it is failing. The error message that I get from the package are below
1)[ODBC Source ] Error: Open Database Connectivity (ODBC) error occurred. state: 'HY000'. Native Error Code: 120. [Cloudera][ImpalaODBC] (120) Error while retrieving data from in Impala: [HY000] : Invalid query handle:
2)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on ODBC Source returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput().
But, when I check the ODBC logs this is the error message: ERROR::SQLGetInfoW: [Cloudera][ODBC] (11180) SQLGetInfo property not found.
Any suggestions or help on this issue?
If the query didn't fail immediately after the error "[Cloudera][ODBC] (11180) SQLGetInfo property not found", we can ignore this error message. Or you can try UseNativeQuery option to avoid this kind of messages.
From the point of my view, "Invalid query handle" was most likely the cause. If I were you, I would check if the query or session timed out or cancelled. If there is a load balancer, I'd also check if the LB sent the request to a wrong impala daemon by chance.
1. We are using HAProxy, do you have by chance any haproxy.cfg file that we could use as reference?
2. How could we know the request was sent to a wrong impala daemon? what would be a wrong impala daemon?
Sorry for the delay.
Here is an example of HAProxy configuration: https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/impala_proxy.html#tut_proxy. Please check if you set balance to SOURCE for ODBC proxy. I used to see some client (I can't remember if it's an ODBC or JDBC client) opened a connection and reused an existing session. If the balance is not set to SOURCE, HAProxy could forward the connection to another impala daemon so the client would hit 'Invalid query handle'.
You can enable debug level logging for Impala ODBC Driver. After the issue occurs, you can trace the operations in ODBC driver logs. If the client opened a connection prior to "Invalid query handle", the LB could forward the connection to a wrong impala daemon. Then you can look up impalad logs by the timestamp to figure out which impala daemon the client connected to and check if the impala daemon is the coordinator of the query (it can be found from query profile). Otherwise, you need to trace the query id in the impalad coordinator logs to find out why it returned "Invalid query handle" to the client.