Apply unitycatalog tags programmatically

[afisl]

Hello,

I'm interested in the "Tags" feature of columns/schemas/tables of the UnityCatalog (described here: https://learn.microsoft.com/en-us/azure/databricks/data-governance/unity-catalog/tags)

I've been able to play with them by hand and would now like to integrate them from a programmatic perspective. I was able to QUERY them programmatically (described here https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/information-schema/column_tag... but not SET/UPDATE them programmatically.

I think it should be possible to set them programmatically according to the following paragraph in the documentation:

Currently, Azure Databricks supports tagging SQL commands with catalogs, schemas, tables (views, materialized views, streaming tables), and table columns. Learn more about the Databricks SQL tag commands at SQL language reference.

Source: https://learn.microsoft.com/en-us/azure/databricks/data-governance/unity-catalog/tags#manage-tags-wi...

I've tried UPDATE statement on the information_schema.column_tags table, but get an spark error message:

SparkUnsupportedOperationException: UPDATE TABLE is not supported temporarily.

Any idea how I can set tags programmatically?

Thanks for your support,

Best

Antoine

[Kaniz_Fatma]

Hi @afisl, Certainly! Let’s explore how you can set tags programmatically in the Unity Catalog within Azure Databricks.

Requirements and Constraints:

• To add tags to Unity Catalog securable objects, you must have the following privileges:
  • APPLY TAG privilege on the object.
  • USE SCHEMA privilege on the object’s parent schema.
  • USE CATALOG privilege on the object’s parent catalog.
• Constraints:
  • You can assign a maximum of 20 tags to a single securable object.
  • The maximum length of a tag is 255 characters.
  • Special characters cannot be used in tag names.
  • Searching Unity Catalog tables by tag name or value is supported with exact match only.

Managing Tags in Catalog Explorer (UI):

• Click Catalog in the sidebar.
• Select a securable object to view the tag information.
• Click Add/Edit Tags to manage tags for the current securable object. You can add and remove multiple tags simultaneously in the tag management modal.

Retrieve Tag Information with Information Schema Tables:

• Each catalog created on Unity Catalog includes an INFORMATION_SCHEMA.
• You can query the following tables to retrieve tag information:
  • INFORMATION_SCHEMA.CATALOG_TAGS
  • INFORMATION_SCHEMA.COLUMN_TAGS
  • INFORMATION_SCHEMA.SCHEMA_TAGS
  • INFORMATION_SCHEMA.TABLE_TAGS

Manage Tags with SQL Commands:

• This feature is available for Databricks Runtime versions 13.3 and above.
• You can use Databricks SQL commands to tag catalogs, schemas, tables (views, materialized views, streaming tables), and table columns.
• Learn more about the Databricks SQL tag commands in the SQL language reference.

Using Tags for Search and Filtering:

• To search and filter tables using tags:
  • Click the Search field in the top bar of the Azure Databricks workspace or use the keyboard shortcut Command-P.
  • Enter your search criteria.
  • Search for tables in Unity Catalog by entering the assigned tag name or value.
  • You can also use the tag filter in the Table search tab to filter search results with existing tag names.

Remember that this is a powerful way to organize and categorize your data assets within Unity Catalog. Happy tagging! 🏷️🌟

For more detailed information, refer to the official documentation.

[afisl]

Hi @Kaniz_Fatma ,

thanks for your answer. The documentation you quoted is exactly the one I saw.

But this paragraph is for me unclear:"

Manage Tags with SQL Commands:

• This feature is available for Databricks Runtime versions 13.3 and above.
• You can use Databricks SQL commands to tag catalogs, schemas, tables (views, materialized views, streaming tables), and table columns.
• Learn more about the Databricks SQL tag commands in the SQL language reference."

I cannot find the Databricks SQL tag commands in the SQL language reference. Could you help me there please?

Thanks,

Antoine

[Kaniz_Fatma]

Hi @afisl, Certainly! Let’s clarify that paragraph.

The Databricks SQL tag commands allow you to manage tags for various database objects within Databricks, including catalogs, schemas, tables (views, materialized views, streaming tables), and table columns. These tags serve as metadata labels that help organize and categorize your data assets.

Here are the key points about Databricks SQL tag commands:

Availability: This feature is available for Databricks Runtime versions 13.3 and above.

Usage: You can use Databricks SQL commands to apply and manage tags. These commands allow you to associate descriptive labels with your data objects, making it easier to search, filter, and organize them.

Where to Find Information: To learn more about the specific SQL commands related to tagging, refer to the SQL language reference provided by Databricks. This reference contains detailed information on how to use these commands effectively.

If you’re looking for the exact syntax and examples of tag-related SQL commands, I recommend checking the official Databricks SQL language reference. It provides comprehensive details on all supported commands, including those related to tagging.

Feel free to explore the reference documentation to gain a deeper understanding of how to work with tags in Databricks SQL! 🚀

[melbourne · Accepted solution]

Hi @afisl 

Use following SQL command to add tags programmatically:

alter table <table_name>          
set tags ('key1' = 'value1', 'key2' = 'value2')

[dflores_mn]

is there a way to apply column tags programmatically to views?

[cltj]

Hi lets explore how  AI in a comminication channel like this will expoenentially waste the time users use to craft their post and explain their issue or question.  

I do not understand why a simple "I dont know, but I will find out" is out of range these days.

Sometimes we can make just as much or even more impact with simplicity without sending users in a loop or down the wrong rabbit hole, perfectly illustrated here by melbourne.

[databass]

just confirming, that as at March 2024 you can use SQL to set/unset tags on:

1. Tables
2. Table Columns
3. Views

But NOT on View Columns

however you CAN do this via the UI. 

[Mamzy10]

@afisl did you ever get down to the bottom of this? im trying to automatically set PI tags against column with certain words

[Jiri_Koutny]

Hi, running ALTER TABLE SET TAGS works on views too!