Querying data from external hive metastore

a-data-guy 0
Viewed 50

I am trying to configure an external hive metastore for my azure synapse spark pool. The rational behind using external meta store is to share table definitions across databricks and synapse workspaces.

However, I am wondering if its possible to access backend data via the metastore. For example, can clients like PowerBI,tableau connect to external metastore and retrieve not just the metadata, but also the business data in the underlying tables?

Also what additional value does an external metastore provides ?

hive-metastore azure-databricks azure-synapse
1 Answers

You can configure the external Hive Metadata in Synapse by creating a Linked Service for that external source and then query it in Synapse Serverless Pool.

Follow the below steps to connect with External Hive Metastore.

  1. In Synapse Portal, go to the Manage symbol on the left side of the of the page. Click on it and then click on Linked Services. To create the new Linked Service, click on + New.

enter image description here

  1. Search for Azure SQL Database or Azure Database for MySQL for the external Hive Metastore. Synapse supports these two Hive external metastore. Select and Continue.

enter image description here

  1. Fill in all the required details like Name, Subscription, Server name, Database name, Username and Password and Test the connection.

enter image description here

  1. You can test the connection with the Hive metadata using below code.
%%spark 
import java.sql.DriverManager 
/** this JDBC url could be copied from Azure portal > Azure SQL database > Connection strings > JDBC **/ 
val url = s"jdbc:sqlserver://<servername>.database.windows.net:1433;database=<databasename>;user=utkarsh;password=<password>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;" 
try { 
    val connection = DriverManager.getConnection(url) 
    val result = connection.createStatement().executeQuery("select * from dbo.persons") 
    result.next(); 
    println(s"Successful to test connection. Hive Metastore version is ${result.getString(1)}") 
} catch { 
    case ex: Throwable => println(s"Failed to establish connection:\n $ex") 
}

Check the same in below snippet for your reference.

enter image description here

can clients like PowerBI,tableau connect to external metastore and retrieve not just the metadata, but also the business data in the underlying tables?

Yes, Power BI allows us to connect with Azure SQL Database using in-built connector.

In Power BI Desktop, go to Get Data, click on Azure and select Azure SQL Database. Click connect.

enter image description here

In the next step, go give the Server name in this format <utkarsh.database.windows.net>, database name, Username and Password and you can now access data in Power BI. Refer below image.

enter image description here

Utkarsh Pal0
Related