Share via

hive metastore table on azure sql server

Shambhu Rai 1,411 Reputation points
2023-08-01T14:19:57.82+00:00

Hi Expert,

Hivemetastore tables gets stored in azure sql server select * from tbo.tbls tables but how to query table directly on sql server or use these table in .net or report application

select * from dbo.tbls can see the table name from but can not query the table on azure sql server

Best Regards

Azure SQL Database

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Wilko van de Velde 2,226 Reputation points
    2023-08-02T06:18:02.4766667+00:00

    Hi @Shambhu Rai ,

    Welcome to Microsoft Q&A!

    I am not familiar with Hive metastore tables, but based on the information I found, I think the Hive metastore tables only contain metadata like Database names, table names, columns, data types, etc. The actual data is in Hive, so if you want to query these you need to do this through Hive

    User's image

    Source: https://lakefs.io/wp-content/uploads/elementor/thumbs/hive-architecture-1x-pbcvl9jhp0gjc43th982jel8b33veur9h06z0jmzgg.png

    Greetings,
    Wilko

    Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

  2. ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee
    2023-08-02T07:06:12.1133333+00:00

    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:

    1. Set up a Hive Metastore on a Hadoop cluster or HDInsight cluster.
    2. Install the Hive ODBC driver on the machine where you want to connect to the Hive Metastore.
    3. Create a System DSN (Data Source Name) for the Hive ODBC driver and configure it to connect to the Hive Metastore.
    4. Install PolyBase on the Azure SQL Server instance.
    5. Create an External Data Source in SQL Server that points to the Hadoop or HDInsight cluster where the Hive tables are stored.
    6. Create an External File Format in SQL Server that specifies the format of the data in the Hive tables.
    7. Create an External Table in SQL Server that references the Hive tables using the External Data Source and External File Format you created earlier.
    8. 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.