Hi Shambhu Rai,
Thank you for posting query in Microsoft Q&A Platform.
When you connect Hive Metastore with Azure SQL Server, the Hive tables are stored as metadata in the Azure SQL Server database. However, the actual data for the Hive tables is still stored in Hadoop or HDInsight cluster.
To query the Hive tables directly in Azure SQL Server or use them in a .NET or report application, you can use PolyBase. PolyBase is a feature in SQL Server that allows you to query external data sources such as Hadoop or Azure Blob Storage using T-SQL statements.
Here are the general steps to use PolyBase to query Hive tables in Azure SQL Server:
- Set up a Hive Metastore on a Hadoop cluster or HDInsight cluster.
- Install the Hive ODBC driver on the machine where you want to connect to the Hive Metastore.
- Create a System DSN (Data Source Name) for the Hive ODBC driver and configure it to connect to the Hive Metastore.
- Install PolyBase on the Azure SQL Server instance.
- Create an External Data Source in SQL Server that points to the Hadoop or HDInsight cluster where the Hive tables are stored.
- Create an External File Format in SQL Server that specifies the format of the data in the Hive tables.
- Create an External Table in SQL Server that references the Hive tables using the External Data Source and External File Format you created earlier.
- Query the External Table using T-SQL statements in SQL Server.
Note that you may need to configure additional settings such as authentication and security depending on your specific environment and requirements.
I hope this helps! Let me know if you have any further questions.
Please consider hitting
Accept Answer button. Accepted answers help community as well.