topic Re: Ownership change for table using SQL in Data Engineering

Ownership change for table using SQL

Nid-cbs — Mon, 09 Sep 2024 10:45:28 GMT

It's not possible to use the ALTER TABLE tblname OWNER TO serviceprinc1 command in Azure Databricks, as this isn't supported. I was trying to set a catalog table's ownership, but it resulted in an error. How can I achieve this using a script

Re: Ownership change for table using SQL

szymon_dybczak — Mon, 09 Sep 2024 11:08:25 GMT

Hi @Nid-cbs ,

Are you sure that you're using correct syntax? According to documentation it should be:

ALTER TABLE table_name SET OWNER TO principal

Re: Ownership change for table using SQL

Nid-cbs — Mon, 09 Sep 2024 12:32:09 GMT

ALTER TABLE catalogue.schema.table OWNER TO GUID

the rror is [PARSE_SYNTAX_ERROR] Syntax error at or near 'OWNER'.(line 1, pos 43)

Re: Ownership change for table using SQL

szymon_dybczak — Mon, 09 Sep 2024 12:38:26 GMT

I still think that you're using bad synxtax. You're missing SET keyword before OWNER. Try as below:

ALTER TABLE catalogue.schema.table SET OWNER TO GUID

Re: Ownership change for table using SQL

Nid-cbs — Mon, 09 Sep 2024 12:47:17 GMT

Thanks. Still the same error

[PARSE_SYNTAX_ERROR] Syntax error at or near 'OWNER'.(line 1, pos 35)

Re: Ownership change for table using SQL

filipniziol — Mon, 09 Sep 2024 13:57:37 GMT

Hi @Nid-cbs ,

It depends on how you are using the code.
Make sure you are using .sql notebook type or %sql magic command, and not spark.sql.
Also, the owner should be wrapped in ` backtick characters:

%sql ALTER TABLE test_table SET OWNER TO `owner`

More on that:
https://stackoverflow.com/questions/68804903/databricks-alter-table-owner-to-userid-is-not-working-with-spark-sql-in-pysp

Re: Ownership change for table using SQL

Nid-cbs — Mon, 09 Sep 2024 16:19:27 GMT

it turns out that The ALTER TABLE statement in Databricks SQL does not support changing the owner of a table directly.It seems we need to use REST API to do the same

Re: Ownership change for table using SQL

szymon_dybczak — Mon, 09 Sep 2024 16:51:17 GMT

Thanks for the information. This is very strange though. I'll check it tommorrow. It should work in a notebook with %sql cell.

It won't work if use spark.sql("ALTER TABLE test_table SET OWNER TO `owner_name`") though.

Re: Ownership change for table using SQL

vjani — Tue, 12 Nov 2024 21:16:07 GMT

I was getting same error in python notebook and I found typo in my sql:

Changing from

ALTER TABLE table_name SET OWNER TO 'principal'

to below fixed the issue.

ALTER TABLE table_name SET OWNER TO `principal`