HIVE ODBC Driver Timeout on INSERT

Question

Hi,

I'm having a time out issue when trying to perform and insert operation into HIVE using the ODBC driver (v2.6.1.1001 (64 bit) Windows) as a linked server in SQL Server.

When I perfrom a SELECT query using OPENQUERY function, the driver will wait till execution is complete no matter how many minutes it take.

However, when I perform an INSERT query, the driver always return a timeout message when the execution time exceeds 30 seconds.

I set all timeout parameters to 0, however, the issue persists.

Is there a way to make INSERTS taking longer than 30 seconds succeed?

From HUE, the inserts work properly and succeed after around 1:30 minutes.

Appreciate your insights.

-------------

Below is a sample trace log of the driver when executing the insert statement:

May 05 16:57:03.394 TRACE 9468 HardyTCLIServiceThreadSafeClient::ExecuteStatement: +++++ profile leave +++++
May 05 16:57:03.394 TRACE 9468 HardyTCLIServiceClient::CheckCancel: +++++ enter +++++
May 05 16:57:03.519 DEBUG 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: TGetOperationStatusReq
operationHandle.operationId.guid = 86e328c56f4048b7aae3196921de9c24
May 05 16:57:03.519 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile enter +++++
May 05 16:57:08.522 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile leave +++++
May 05 16:57:08.522 TRACE 9468 HardyTCLIServiceClient::CheckCancel: +++++ enter +++++
May 05 16:57:08.632 DEBUG 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: TGetOperationStatusReq
operationHandle.operationId.guid = 86e328c56f4048b7aae3196921de9c24
May 05 16:57:08.632 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile enter +++++
May 05 16:57:13.633 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile leave +++++
May 05 16:57:13.633 TRACE 9468 HardyTCLIServiceClient::CheckCancel: +++++ enter +++++
May 05 16:57:13.742 DEBUG 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: TGetOperationStatusReq
operationHandle.operationId.guid = 86e328c56f4048b7aae3196921de9c24
May 05 16:57:13.742 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile enter +++++
May 05 16:57:18.742 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile leave +++++
May 05 16:57:18.742 TRACE 9468 HardyTCLIServiceClient::CheckCancel: +++++ enter +++++
May 05 16:57:18.852 DEBUG 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: TGetOperationStatusReq
operationHandle.operationId.guid = 86e328c56f4048b7aae3196921de9c24
May 05 16:57:18.852 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile enter +++++
May 05 16:57:23.966 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile leave +++++
May 05 16:57:23.966 TRACE 9468 HardyTCLIServiceClient::CheckCancel: +++++ enter +++++
May 05 16:57:24.076 DEBUG 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: TGetOperationStatusReq
operationHandle.operationId.guid = 86e328c56f4048b7aae3196921de9c24
May 05 16:57:24.076 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile enter +++++
May 05 16:57:29.079 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile leave +++++
May 05 16:57:29.079 TRACE 9468 HardyTCLIServiceClient::CheckCancel: +++++ enter +++++
May 05 16:57:29.188 DEBUG 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: TGetOperationStatusReq
operationHandle.operationId.guid = 86e328c56f4048b7aae3196921de9c24
May 05 16:57:29.188 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile enter +++++
May 05 16:57:34.188 TRACE 9468 HardyTCLIServiceThreadSafeClient::GetOperationStatus: +++++ profile leave +++++
May 05 16:57:34.188 TRACE 9468 HardyTCLIServiceClient::CheckCancel: +++++ enter +++++
May 05 16:57:34.188 TRACE 9468 HardyTCLIServiceClient::CancelOperation: +++++ enter +++++
May 05 16:57:34.188 TRACE 9468 HardyTCLIServiceClient::GetBackendCxn: +++++ enter +++++
May 05 16:57:34.188 DEBUG 9468 HardyTCLIServiceThreadSafeClient::CancelOperation: TCancelOperationReq
operationHandle.operationId.guid = 86e328c56f4048b7aae3196921de9c24
May 05 16:57:34.188 TRACE 9468 HardyTCLIServiceThreadSafeClient::CancelOperation: +++++ profile enter +++++
May 05 16:57:34.204 TRACE 9468 HardyTCLIServiceThreadSafeClient::CancelOperation: +++++ profile leave +++++
May 05 16:57:34.204 TRACE 9468 HardyHardySSPropertyManager::NotifyExecEnd: +++++ enter +++++
May 05 16:57:34.204 TRACE 9468 HardyHardyQuotedIDManager::HandleExecEnd: +++++ enter +++++
May 05 16:57:34.204 TRACE 9468 HardyHardySSPropertyManager::NotifyExecEnd: +++++ enter +++++
May 05 16:57:34.204 TRACE 9468 HardyHardySSPropertyManager::HandleExecEnd: +++++ enter +++++
May 05 16:57:34.204 ERROR 9468 ClassicQueryExecutor::Execute: [Cloudera][Hardy] (72) Query execution timeout expired.
May 05 16:57:34.204 ERROR 9468 Statement::SQLExecute: [Cloudera][Hardy] (72) Query execution timeout expired.

EricL · Accepted Answer

Hi,I am seeing CancelOperation:HardyTCLIServiceThreadSafeClient::CancelOperationHave you checked on the HS2 log to see if anything useful from server side?It does not make sense that SELECT works but INSERT will get timeout, there is no separate configuration for them. What about CTAS query?CheersEric

EricL · Answer

Hi,When you are saying "I set all timeout parameters to 0,", can you please confirm what timeouts you set? From client side or Hive server side?Have you tried to increase the SocketTimeout on the ODBC driver side:https://www.cloudera.com/documentation/other/connectors/hive-odbc/latest/Cloudera-ODBC-Driver-for-Apache-Hive-Install-Guide.pdfon page 81.

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.