topic Re: Retrieve table/view popularity in Data Engineering

Retrieve table/view popularity

noorbasha534 — Mon, 07 Oct 2024 18:59:41 GMT

Dears

Is there a way to retrieve the popularity score of an unity catalog object? I looked at APIs documentation but couldn't find one that serves the need.

Appreciate any thoughts.

Br,

Noor.

Re: Retrieve table/view popularity

szymon_dybczak — Mon, 07 Oct 2024 19:35:15 GMT

Hi @noorbasha534 ,

You can take a look at table insights:

https://docs.databricks.com/en/discover/table-insights.html

Another option you can try is to use system tables and create based on them custom reports.

Re: Retrieve table/view popularity

noorbasha534 — Tue, 08 Oct 2024 06:49:53 GMT

Hi @szymon_dybczak I like to retrieve programmatically what is displayed in the UI. If the system tables store the "direct" value of popularity score, please kindly guide me to the relevant table/column.

Appreciate. Thanks in advance.

Re: Retrieve table/view popularity

filipniziol — Tue, 08 Oct 2024 07:35:27 GMT

Hi @noorbasha534,

You can query system.access.audit table:

%sql SELECT request_params.full_name_arg AS table_Name, COUNT(*) AS table_usage FROM system.access.audit WHERE action_name = 'getTable' GROUP BY table_name ORDER BY table_usage DESC

The table is in preview not enabled by default. To enable you need to send API call:

# Import necessary libraries import requests # Define the necessary variables workspace_url = "<workspace_url>" metastore_id = "metastore_id" schema_name = "access" pat_token = "pat_token" # Construct the URL for the API request url = f"https://{workspace_url}/api/2.0/unity-catalog/metastores/{metastore_id}/systemschemas/{schema_name}" # Set the headers for the HTTP request headers = { "Authorization": f"Bearer {pat_token}" } # Make the PUT request to enable access response = requests.put(url, headers=headers) # Check the response status if response.status_code == 200: print(f"Access successfully enabled for schema `{schema_name}`.") elif response.status_code == 401: print("Authorization failed. Please check your PAT token.") else: print(f"Failed to enable access: {response.status_code} - {response.text}")

Re: Retrieve table/view popularity

noorbasha534 — Tue, 08 Oct 2024 07:41:34 GMT

@filipniziol Hi Filip, ah ok. So, it is the usage from audit tables that is leveraged to derive popularity score. Thanks for this.

One final check please - we expose views to our users but I like to get popularity of tables and impose some data quality checks for the most popular tables, say, those that have been hit 100 times in a week. If an user is querying a view that has 2 underlying tables, the audit tables will record "getTable" event for those 2 tables as UC query engine will try to parse the view query? Are you aware of this?

Thanks in advance.

Re: Retrieve table/view popularity

filipniziol — Tue, 08 Oct 2024 11:05:50 GMT

Hi @noorbasha534 ,

yes, you are correct. When the query is hitting 2 tables, the audit table should record 2 "getTable" events.

Regards,
Filip

Re: Retrieve table/view popularity

noorbasha534 — Tue, 08 Oct 2024 15:24:47 GMT

@filipniziol Hi Filip, Thank you. I did a quick test. In my environment, the table query (indirect) event is getting registered with "getTemporaryTableCredential". However, the view query (direct) event is with "getTable".

Thanks for your time again. Appreciate the support.

Re: Retrieve table/view popularity

szymon_dybczak — Tue, 08 Oct 2024 15:41:58 GMT

Hi @noorbasha534 ,

Please mark @filipniziol answer as a solution for this thread. This will help others with same kind of problem find proper solution faster.