topic Re: Using yml variables as table owner through SQL in Data Engineering

Using yml variables as table owner through SQL

AsgerLarsen — Wed, 16 Apr 2025 10:43:38 GMT

I'm trying to change the ownership of a table in the Unity Catalog created through a SQL script. I want to do this though code.

I'm using a standard databricks bundle setup, which uses three workspaces: dev, test and prod.

I have created a variable in the databricks.yml file, let's call it 'admin_group'.

The variable's value depends on the workspace, so if the code is being executed from the dev environment, the value would be 'azure_admin_group_dev', if the environment is test, the value would be 'azure_admin_group_test' etc.

I'm trying to pass this variable into the ALTER TABLE with the OWNER TO statement, so the executed result would look like this:

ALTER TABLE catalog_name.schema_name.table_name OWNER TO `azure_admin_group_dev`

Notice the backticks wrapped around the admin_group value.

I run into some issue because of certain limitations:

The 'OWNER TO' SQL statement requires the value to be wrapped in backticks (``)
The 'OWNER TO' SQL statement can't execute subqueries like e.g. (SELECT CONCAT('`',{{admin_group}},'`') as c)
The .yml files, where the variable value is defined, doesn't allow for backticks unless singlequotes are wrapped around them, in which case the statement won't accept the value.
I would very much like to be able to do this in an SQL file.

With these limitations, is it even possible to achive this?
Any input is appriciated.

Re: Using yml variables as table owner through SQL

-werners- — Wed, 16 Apr 2025 11:10:52 GMT

have you tried without backticks? I mean using common variable substitution:
ALTER TABLE my_table OWNER TO '${new_owner}' (new_owner is a dab var of course).

Re: Using yml variables as table owner through SQL

AsgerLarsen — Wed, 16 Apr 2025 11:25:06 GMT

I have tried that, it will result in single quotes being wrapped around the variable value and cause a syntax error

Re: Using yml variables as table owner through SQL

-werners- — Wed, 16 Apr 2025 11:41:45 GMT

how does your variables section look like?
Perhaps something you have tried but what if you use backticks instead of the single quotes in the query.
The var is substituted with the value (the value itself does not contains backticks though!).

Re: Using yml variables as table owner through SQL

AsgerLarsen — Wed, 16 Apr 2025 11:54:32 GMT

The variable looks like this in the yml file:

admin_group: azure_admin_group_dev

This will return the value like this:

'azure_admin_group_dev'

If I add the backticks in as part of the value like this:

admin_group: '`azure_admin_group_dev`'

The value will be returned like this:

'`azure_admin_group_dev`'

Unfortunatly, none of these values are acceptable in the OWNER TO statement. It needs to be returned like this:

`azure_admin_group_dev`

Re: Using yml variables as table owner through SQL

-werners- — Wed, 16 Apr 2025 12:15:07 GMT

ugh,
SQL... 🙂
tried to escape the backticks in the yml? so \`group\`?

Re: Using yml variables as table owner through SQL

AsgerLarsen — Wed, 16 Apr 2025 12:34:37 GMT

Yea, still no luck. I might start looking into refactoring the scripts into notebooks and be done with it.

Using backslash in yml (admin_group: \`azure_admin_group_dev\`) will return:

'\\`azure_admin_group_dev\\`'

Re: Using yml variables as table owner through SQL

-werners- — Wed, 16 Apr 2025 12:42:02 GMT

I guess that is a safe bet.
Good luck!