Create views in Azure Databricks Unity catalog using service principal

Ashwini Gaikwad 130

Hello Team,

Is there a possibility to Create views in Azure databricks Unity Catalog using service principal.

and if there is how to update views based on tables added or deleted in schema. For e.g. I would like to create each views per tables in the schema using service principal , so how can I manage it if a new table is added or existing table is updated or deleted?

are there any reference article for it?

phemanth 11,980 Reputation points Microsoft Vendor

2024-11-13T17:39:15.59+00:00
@Ashwini Gaikwad Thanks for reaching out to Microsoft Q&A.

Yes, you can create views in Azure Databricks Unity Catalog using a service principal! Here’s how you can do it and manage updates based on schema changes: Creating Views with a Service Principal

Set Up the Service Principal: Ensure that your service principal has the necessary permissions. You need to grant it the following:

USE CATALOG permission on the parent catalog. USE SCHEMA and CREATE TABLE permissions on the schema where you want to create views.

Use SQL Commands: You can create views using SQL commands in Databricks notebooks or through the Databricks REST API. For example:

CREATE VIEW my_view AS SELECT * FROM my_table;

Authentication: When using a service principal, authenticate using OAuth tokens or personal access tokens to execute the SQL commands programmatically.

Updating Views Based on Schema Changes To manage views when tables are added, updated, or deleted:

Monitor Schema Changes: Implement a mechanism to monitor changes in your schema. This could be done using event triggers or scheduled jobs that check for schema updates.

Dynamic View Creation: When a new table is added, you can programmatically create a new view for it. For example, if you have a list of tables, iterate through them and create views as needed.

Drop or Alter Views: If a table is deleted, you can drop the corresponding view:
DROP VIEW IF EXISTS my_view;

For updates, you might need to recreate the view with the new table structure.

Reference Articles

Set up and manage Unity Catalog - This article provides an overview of Unity Catalog and how to manage it.

Manage service principals - Details on how to create and manage service principals in Azure Databricks.

Hope this helps. Do let us know if you any further queries.
Ashwini Gaikwad 130 Reputation points

2024-11-13T18:27:19.2666667+00:00

Hello @phemanth ,

Thank you for your response, I tried using workspace client for spn authentication and tried to create views in databricks notebook

but it doesn’t work in the notebook stating sql is not an object of workspace client.

Can you share the reference code which works for creating views using service principal or point to an article which will give reference to authenticate using spn and create views.

Regards,

Ashwini
phemanth 11,980 Reputation points Microsoft Vendor

2024-11-13T19:57:33.12+00:00
@Ashwini Gaikwad Here’s a basic example of how to authenticate using a service principal and create a view in Azure Databricks:

Authenticate Using Service Principal: You can use the databricks-cli or the Databricks REST API to authenticate. Here’s an example using Python with the databricks-api library:
from databricks_api import DatabricksAPI # Replace these with your values host = 'https://<your-databricks-instance>' client_id = '<your-client-id>' client_secret = '<your-client-secret>' tenant_id = '<your-tenant-id>' # Authenticate db = DatabricksAPI( host=host, token=client_secret # Use client secret as token )

Create a View: After authenticating, you can execute SQL commands to create a view:
sql_command = "CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table" db.sql(sql_command)

To manage views when tables are added, updated, or deleted, consider implementing a monitoring script that checks the schema and updates views accordingly. You can use the Databricks REST API to list tables and their schemas, and then adjust your views as needed.

Reference Articles

For more detailed guidance, you can refer to these articles:

Manage service principals - Azure Databricks - This article covers how to create and manage service principals.

Databricks REST API reference - This provides details on using the REST API for various operations, including SQL execution.
Ashwini Gaikwad 130 Reputation points

2024-11-13T20:26:53.8366667+00:00

Thank you @phemanth for your quick response. I will give it a try to create a view using above code and update you if that works. Thanks once again for your time and help.
Ashwini Gaikwad 130 Reputation points

2024-11-14T14:55:33.7133333+00:00

@phemanth I tried with the code which you shared to create views using spn but unfortunately it didn't work and it gave an error for DatabricksAPI has no attribute sql .

I changed the name of catalog, schema name view name as well as table name in my own code.

Please let me know if creating views, materialized views works with an spn and accordingly the working code.

Have you tried one in your own working environment?
Ashwini Gaikwad 130 Reputation points

2024-11-14T14:58:47.18+00:00

@phemanth thank you for your response and sharing the code but unfortunately it didn't work and gave me an error AttributeError: 'DatabricksAPI' object has no attribute 'sql'

Please refer to screenshot, I have changed my catalog, schema, table and view name accordingly.

Request you to let me know the working code for creation of views or materialized views using spn and have you tried in your own working environment?

Regards,

Ashwini
Ashwini Gaikwad 130 Reputation points

2024-11-15T12:16:14.0133333+00:00

Hello @phemanth @PRADEEPCHEEKATLA

Do you have any inputs for the working code?Regards,

Ashwini G
phemanth 11,980 Reputation points Microsoft Vendor

2024-11-15T12:49:37.1866667+00:00

@Ashwini Gaikwad We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
Smaran Thoomu 17,765 Reputation points Microsoft Vendor

2024-11-18T14:05:35.6+00:00

Hi @Ashwini Gaikwad

Could you please try the code below and make any necessary adjustments?

I hope this helps. Please let me know if you have any questions.
phemanth 11,980 Reputation points Microsoft Vendor

2024-11-19T17:16:07.06+00:00

@Ashwini Gaikwad We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ashwini Gaikwad 130 Reputation points

2024-11-25T11:22:00.9066667+00:00

@phemanth thank you for your response. But I managed to create the views using SPN as per my below code. Thank you for your time and assistance.
phemanth 11,980 Reputation points Microsoft Vendor

2024-11-25T11:47:00.24+00:00

@Ashwini Gaikwad Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

1 answer

phemanth 11,980 Reputation points Microsoft Vendor

2024-11-25T11:46:26.65+00:00

@Ashwini Gaikwad

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask:

Is there a possibility to Create views in Azure databricks Unity Catalog using service principal.

and if there is how to update views based on tables added or deleted in schema. For e.g. I would like to create each views per tables in the schema using service principal , so how can I manage it if a new table is added or existing table is updated or deleted?

are there any reference article for it?

Solution:

But I managed to create the views using SPN as per my below code. Thank you for your time and assistance.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Create views in Azure Databricks Unity catalog using service principal

1 answer

Your answer