cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Apply unitycatalog tags programmatically

afisl
New Contributor II

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

1 ACCEPTED SOLUTION

Accepted Solutions

melbourne
Contributor

Hi @afisl 

Use following SQL command to add tags programmatically:

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

View solution in original post

7 REPLIES 7

Kaniz_Fatma
Community Manager
Community Manager

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
New Contributor II

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
Community Manager
Community Manager

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
Contributor

Hi @afisl 

Use following SQL command to add tags programmatically:

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

cltj
New Contributor III

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
New Contributor II

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
New Contributor II

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

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!