How to execute Hive queries in Synapse spark

Dharmesh Rathod 0

Hello!

I am replacing a HDI cluster with Azure Synapse. My current HDI spark cluster executes some HIVE queries for data transformation. Is it possible to execute the same HIVE queries into Azure Synapse spark pool?

Thanks,

PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-02-09T06:18:43.77+00:00

Hello @Dharmesh Rathod

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-02-03T06:10:35.65+00:00

Hello @Dharmesh Rathod

Thanks for the question and using MS Q&A platform.

You can execute Hive queries in Synapse Spark by connecting to an external Hive Metastore. To do this, you need to set up a linked service to the external Hive Metastore in your Synapse workspace and configure your Spark pool to use the linked external Hive Metastore. You can then use the HiveContext in your Spark code to execute Hive queries.

For more details, refer to Use external Hive Metastore for Synapse Spark Pool

Hope this helps. Do let us know if you any further queries.

Please don’t forget to Accept Answer wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.
PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-02-14T10:47:51.01+00:00

Hello @Dharmesh Rathod

Following up to see if the above suggestion was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Dharmesh Rathod 0 Reputation points

2023-02-16T09:26:42.24+00:00

HI! Pradeep,

I haven't implemented this change yet, once I do it, I will confirm. But sounds like it should work.

Thanks,

DR

Dharmesh Rathod 0 Reputation points

2023-02-16T09:26:58.3366667+00:00

Pradeep,

Do I also need spark pool inside synapse for my current hive scripts to be executed?

So, the issue is I have HDI cluster 3.6 deployed in application, which is down and HDI 3.6 is not supported and I want to migrate the Transformation part of the application into Synapse from HDI. Please help me in architecting this solution.

Thanks,

DR

PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-02-16T09:30:08.9266667+00:00

Hello @Dharmesh Rathod

Yes, you need to configure a Spark pool to execute the hive script.

Note: To query the existing tables, you need to set up connection to the storage account that stores the underlying data for your Hive tables as well.

Could you please provide more details on what do you mean by "the issue is I have HDI cluster 3.6 deployed in application, which is down and HDI 3.6 is not supported?"

Dharmesh Rathod 0 Reputation points

2023-02-17T04:10:48.5966667+00:00

Hi! Pradeep,

The current cluster in the solution is version 3.6, it was abandoned by Microsoft in Sept 22. They also stopped producing SSL certificates for HDI 3.6. So I need to upgrade the cluster to HDI 4.0. But due to extreme amount of customization into devops pipelines, this upgrade is also failing. So, now planning to move this solution into Synapse.

Thanks,

DR

PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-02-17T04:17:20.6766667+00:00

Hello @Dharmesh Rathod

Yes, the HDInsight cluster is abandoned, you will still have access to the underlying storage account.

As I said above answer, to query the existing tables, you need to set up connection to the storage account that stores the underlying data for your Hive tables as well.

PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-02-20T06:50:03.16+00:00

Hello @Dharmesh Rathod

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment