HIVE ODBC Driver Timeout on INSERT

[#1]

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.

[#2]

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-Ap...

on page 81.

[#3]

It is the timeout from the client side.

I have set the socket timeout as well but it has no effect.

It looks like something related to SQL server configuration. I will look further in that direction.

Thanks for the support.

[#4 · Accepted solution]

Hi,

I am seeing CancelOperation:
HardyTCLIServiceThreadSafeClient::CancelOperation

Have 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?

Cheers
Eric

[#5]

I checked the log but nothing indicates a server side issue.

From the server side it indicates that the client has cancelled.

I have tested the ODBC driver using a client app (C#) and things were fine.

So I'm now trying to see if there is something to be done from SQL Server side.

Thanks a lot for your support.