How to Retrieve Only Tables and Columns Metadata from Databricks in Power BI Without Importing Data?

[Khalid1988]

Hi everyone!

I’m working with Databricks in Power BI and I need some help.

I want to connect to the **hive_metastore** in Databricks, but instead of importing the actual data, I only want to retrieve the **table names** and **column names** (along with their data types) for each table in the database.

Is there a way to connect and fetch just the metadata (tables and columns) without pulling in the full dataset? I already have the connection details like the **server URL** and **HTTP (token)**, and I’m able to connect to the catalog.

Thanks in advance!

[123abc]

To retrieve only the table and column metadata from Databricks in Power BI without importing the actual data, you can use the Databricks JDBC driver along with SQL queries that target the metadata. Here's a step-by-step approach to achieve this:

Step 1: Connect to Databricks

1. Open Power BI Desktop.
2. Go to Home > Get Data > More....
3. Select Database > Databricks.
4. Enter your server details:
   • Server: Your Databricks server URL.
   • HTTP (token): Your authentication token.

Step 2: Use a SQL Query to Retrieve Metadata

Once you're connected, you'll want to use a SQL query to fetch the metadata. You can write a query to list the tables and their respective columns directly. Here's an example query to retrieve table names and column details:

SELECT
t.table_name,
c.column_name,
c.data_type
FROM
information_schema.tables t
JOIN
information_schema.columns c
ON
t.table_name = c.table_name
WHERE
t.table_schema = '<your_database_name>';

Step 3: Execute the Query

1. After connecting, instead of loading the data directly, you can choose to execute the SQL statement.
2. In the Navigator pane, switch to the Advanced options.
3. Paste the above SQL query in the SQL statement box.
4. Click OK.

Step 4: Load the Metadata

1. After executing the query, Power BI will return a table containing the metadata (table names, column names, and data types).
2. Click Load to load this metadata into your Power BI model.

Step 5: Create Visualizations

Now that you have the metadata loaded, you can create reports or visualizations based on this information.

Additional Notes

• Make sure you have the necessary permissions to access the information_schema in Databricks.
• The specific SQL syntax may vary depending on your Databricks setup and the SQL dialect it uses, so adjust the query as necessary.

This method allows you to efficiently retrieve metadata without loading the actual data, enabling you to manage and visualize your database structure easily.

Solution is generating with the help of AI model.