ADF to Hive using Linked service

ADFARI23 21

We have created a linked service to Hive in data factory and using copy activity to bring data from Hive to SQL MI. Usual queries are working fine but sub-queries are not working and getting time out issue. Please see the below sample query and error message

select * from custtable where modifieddate in (select max(modifieddate) from custtable)

Error:

ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '1' error message: 'Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask'.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '1'

AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee

2022-08-17T07:09:36.823+00:00

Hi @ADFARI23 ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question.

As I understand your query, you are trying to run query in Hive datastore using ADF pipeline , however, it's throwing error. Please let me know if my understanding is incorrect.

Please try the following query and let us know how it goes:

select * from custtable where custtable.modifieddate in (select max(modifieddate) from custtable)

Reference : Subqueries in the WHERE Clause
ADFARI23 21 Reputation points

2022-08-17T13:47:06.223+00:00

Hi @AnnuKumari-MSFT

Thank you for your reply.
Yes, that is correct. you understood the problem. We have tried with the query you provided but still we are getting the same issue.

Are there any limitations to connect to Hive from ADF? We are connecting to Hive by using Hive Linked service via a VM ODBC connection.

Please suggest.
AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee

2022-08-18T06:53:26.863+00:00

Hi @ADFARI23 ,
I am checking on this internally with the product team . I will update you once I hear back from them. Thankyou
ADFARI23 21 Reputation points

2022-08-23T12:40:46.71+00:00

Hi @AnnuKumari-MSFT : May I know, if you have any update from Product team?
AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee

2022-09-01T09:33:21.877+00:00

Hi @ADFARI23 ,
I am sorry for the inconvenience. We haven't yet heard from the product team. I have follow up with them again. I wil keep you posted once I hear from them . Thanks
AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee

2022-09-14T08:00:02.513+00:00

Hi @ADFARI23 ,

Since product team is quite busy with other priorities I would like to request that for a deeper investigation and immediate assistance on this issue , if you have a support plan you may file a support ticket. In case you don't have a support plan , I will enable a one-time free support request for your subscription. Please do let us know. Thankyou.

Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well.