topic Re: How to transfer ownership of a database and/or table? in Data Engineering

How to transfer ownership of a database and/or table?

pgrandjean — Thu, 03 Aug 2023 14:50:27 GMT

We created a new Service Principal (SP) on Azure and would like to transfer the ownership of the databases and tables created with the old SP. The issue is that these databases and tables are not visible to the users using the new SP.

I am using a Hive Metastore (instead of Unity).

Re: How to transfer ownership of a database and/or table?

pgrandjean — Fri, 04 Aug 2023 12:48:58 GMT

Btw, the `ALTER SCHEMA foo SET OWNER TO bar` statement does not work from a notebook.

Re: How to transfer ownership of a database and/or table?

pgrandjean — Fri, 04 Aug 2023 22:29:40 GMT

Hi @Retired_mod , here it is. I'm using runtime 11.3 LTS.

com.databricks.backend.common.rpc.SparkDriverExceptions$SQLExecutionException: org.apache.spark.sql.catalyst.parser.ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near 'owner'(line 1, pos 26) == SQL == alter schema [REDACTED] owner to '[REDACTED]' ----------------------------------^^^ at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:356) at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:166) at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:90) at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:112) at com.databricks.sql.parser.DatabricksSqlParser.$anonfun$parsePlan$1(DatabricksSqlParser.scala:77) at com.databricks.sql.parser.DatabricksSqlParser.parse(DatabricksSqlParser.scala:97) at com.databricks.sql.parser.DatabricksSqlParser.parsePlan(DatabricksSqlParser.scala:74) at org.apache.spark.sql.SparkSession.$anonfun$sql$2(SparkSession.scala:818) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:340) at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:818) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:815) at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:695) at com.databricks.backend.daemon.driver.SQLDriverLocal.$anonfun$executeSql$1(SQLDriverLocal.scala:91) at scala.collection.immutable.List.map(List.scala:293) at com.databricks.backend.daemon.driver.SQLDriverLocal.executeSql(SQLDriverLocal.scala:37) at com.databricks.backend.daemon.driver.SQLDriverLocal.repl(SQLDriverLocal.scala:145) at com.databricks.backend.daemon.driver.DriverLocal.$anonfun$execute$24(DriverLocal.scala:740) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at com.databricks.backend.daemon.driver.DriverLocal.$anonfun$execute$21(DriverLocal.scala:723) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:403) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:401) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:398) at com.databricks.backend.daemon.driver.DriverLocal.withAttributionContext(DriverLocal.scala:62) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:446) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:431) at com.databricks.backend.daemon.driver.DriverLocal.withAttributionTags(DriverLocal.scala:62) at com.databricks.backend.daemon.driver.DriverLocal.execute(DriverLocal.scala:700) at com.databricks.backend.daemon.driver.DriverWrapper.$anonfun$tryExecutingCommand$1(DriverWrapper.scala:622) at scala.util.Try$.apply(Try.scala:213) at com.databricks.backend.daemon.driver.DriverWrapper.tryExecutingCommand(DriverWrapper.scala:614) at com.databricks.backend.daemon.driver.DriverWrapper.executeCommandAndGetError(DriverWrapper.scala:533) at com.databricks.backend.daemon.driver.DriverWrapper.executeCommand(DriverWrapper.scala:568) at com.databricks.backend.daemon.driver.DriverWrapper.runInnerLoop(DriverWrapper.scala:438) at com.databricks.backend.daemon.driver.DriverWrapper.runInner(DriverWrapper.scala:381) at com.databricks.backend.daemon.driver.DriverWrapper.run(DriverWrapper.scala:232) at java.lang.Thread.run(Thread.java:750) at com.databricks.backend.daemon.driver.SQLDriverLocal.executeSql(SQLDriverLocal.scala:130) at com.databricks.backend.daemon.driver.SQLDriverLocal.repl(SQLDriverLocal.scala:145) at com.databricks.backend.daemon.driver.DriverLocal.$anonfun$execute$24(DriverLocal.scala:740) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at com.databricks.backend.daemon.driver.DriverLocal.$anonfun$execute$21(DriverLocal.scala:723) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:403) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:401) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:398) at com.databricks.backend.daemon.driver.DriverLocal.withAttributionContext(DriverLocal.scala:62) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:446) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:431) at com.databricks.backend.daemon.driver.DriverLocal.withAttributionTags(DriverLocal.scala:62) at com.databricks.backend.daemon.driver.DriverLocal.execute(DriverLocal.scala:700) at com.databricks.backend.daemon.driver.DriverWrapper.$anonfun$tryExecutingCommand$1(DriverWrapper.scala:622) at scala.util.Try$.apply(Try.scala:213) at com.databricks.backend.daemon.driver.DriverWrapper.tryExecutingCommand(DriverWrapper.scala:614) at com.databricks.backend.daemon.driver.DriverWrapper.executeCommandAndGetError(DriverWrapper.scala:533) at com.databricks.backend.daemon.driver.DriverWrapper.executeCommand(DriverWrapper.scala:568) at com.databricks.backend.daemon.driver.DriverWrapper.runInnerLoop(DriverWrapper.scala:438) at com.databricks.backend.daemon.driver.DriverWrapper.runInner(DriverWrapper.scala:381) at com.databricks.backend.daemon.driver.DriverWrapper.run(DriverWrapper.scala:232) at java.lang.Thread.run(Thread.java:750)

Re: How to transfer ownership of a database and/or table?

shan_chandra — Mon, 07 Aug 2023 03:52:00 GMT

@pgrandjean - Kindly enable Table access control on the cluster for the hive metastore using the below config to the cluster - Advanced options - spark

spark.databricks.hive.metastoreTableAccessControl.enabled true

and run the below command to check the ownership. say for example

show grant on schema <schema-name>

Re: How to transfer ownership of a database and/or table?

pgrandjean — Mon, 07 Aug 2023 12:40:45 GMT

Thank you @shan_chandra . I added the option, however I am getting the following exception when trying to execute the "show grant" statement. Here is the stack trace:

com.databricks.backend.common.rpc.SparkDriverExceptions$SQLExecutionException: org.apache.spark.SparkException: Trying to perform permission action on Hive Metastore /CATALOG/`hive_metastore`/DATABASE/[REDACTED] but Table Access Control is not enabled on this cluster. at com.databricks.sql.acl.ResolvePermissionManagement.apply(ResolvePermissionManagement.scala:119) at com.databricks.sql.acl.ResolvePermissionManagement.apply(ResolvePermissionManagement.scala:34) at org.apache.spark.sql.catalyst.rules.RuleExecutor.$anonfun$execute$3(RuleExecutor.scala:216) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.sql.catalyst.rules.RuleExecutor.$anonfun$execute$2(RuleExecutor.scala:216) at scala.collection.LinearSeqOptimized.foldLeft(LinearSeqOptimized.scala:126) at scala.collection.LinearSeqOptimized.foldLeft$(LinearSeqOptimized.scala:122) at scala.collection.immutable.List.foldLeft(List.scala:91) at org.apache.spark.sql.catalyst.rules.RuleExecutor.$anonfun$execute$1(RuleExecutor.scala:213) at org.apache.spark.sql.catalyst.rules.RuleExecutor.$anonfun$execute$1$adapted(RuleExecutor.scala:205) at scala.collection.immutable.List.foreach(List.scala:431) at org.apache.spark.sql.catalyst.rules.RuleExecutor.execute(RuleExecutor.scala:205) at org.apache.spark.sql.catalyst.analysis.Analyzer.org$apache$spark$sql$catalyst$analysis$Analyzer$$executeSameContext(Analyzer.scala:331) at org.apache.spark.sql.catalyst.analysis.Analyzer.$anonfun$execute$1(Analyzer.scala:324) at org.apache.spark.sql.catalyst.analysis.AnalysisContext$.withNewAnalysisContext(Analyzer.scala:231) at org.apache.spark.sql.catalyst.analysis.Analyzer.execute(Analyzer.scala:324) at org.apache.spark.sql.catalyst.analysis.Analyzer.execute(Analyzer.scala:252) at org.apache.spark.sql.catalyst.rules.RuleExecutor.$anonfun$executeAndTrack$1(RuleExecutor.scala:184) at org.apache.spark.sql.catalyst.QueryPlanningTracker$.withTracker(QueryPlanningTracker.scala:154) at org.apache.spark.sql.catalyst.rules.RuleExecutor.executeAndTrack(RuleExecutor.scala:184) at org.apache.spark.sql.catalyst.analysis.Analyzer.$anonfun$executeAndCheck$1(Analyzer.scala:304) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.markInAnalyzer(AnalysisHelper.scala:361) at org.apache.spark.sql.catalyst.analysis.Analyzer.executeAndCheck(Analyzer.scala:303) at org.apache.spark.sql.execution.QueryExecution.$anonfun$analyzed$1(QueryExecution.scala:147) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:340) at org.apache.spark.sql.execution.QueryExecution.$anonfun$executePhase$3(QueryExecution.scala:337) at org.apache.spark.sql.execution.QueryExecution$.withInternalError(QueryExecution.scala:763) at org.apache.spark.sql.execution.QueryExecution.$anonfun$executePhase$1(QueryExecution.scala:337) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.QueryExecution.executePhase(QueryExecution.scala:334) at org.apache.spark.sql.execution.QueryExecution.analyzed$lzycompute(QueryExecution.scala:141) at org.apache.spark.sql.execution.QueryExecution.analyzed(QueryExecution.scala:141) at org.apache.spark.sql.execution.QueryExecution.assertAnalyzed(QueryExecution.scala:133) at org.apache.spark.sql.Dataset$.$anonfun$ofRows$2(Dataset.scala:106) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:104) at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:820) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:815) at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:695) at com.databricks.backend.daemon.driver.SQLDriverLocal.$anonfun$executeSql$1(SQLDriverLocal.scala:91) at scala.collection.immutable.List.map(List.scala:293) at com.databricks.backend.daemon.driver.SQLDriverLocal.executeSql(SQLDriverLocal.scala:37) at com.databricks.backend.daemon.driver.SQLDriverLocal.repl(SQLDriverLocal.scala:145) at com.databricks.backend.daemon.driver.DriverLocal.$anonfun$execute$24(DriverLocal.scala:740) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at com.databricks.backend.daemon.driver.DriverLocal.$anonfun$execute$21(DriverLocal.scala:723) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:403) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:401) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:398) at com.databricks.backend.daemon.driver.DriverLocal.withAttributionContext(DriverLocal.scala:62) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:446) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:431) at com.databricks.backend.daemon.driver.DriverLocal.withAttributionTags(DriverLocal.scala:62) at com.databricks.backend.daemon.driver.DriverLocal.execute(DriverLocal.scala:700) at com.databricks.backend.daemon.driver.DriverWrapper.$anonfun$tryExecutingCommand$1(DriverWrapper.scala:622) at scala.util.Try$.apply(Try.scala:213) at com.databricks.backend.daemon.driver.DriverWrapper.tryExecutingCommand(DriverWrapper.scala:614) at com.databricks.backend.daemon.driver.DriverWrapper.executeCommandAndGetError(DriverWrapper.scala:533) at com.databricks.backend.daemon.driver.DriverWrapper.executeCommand(DriverWrapper.scala:568) at com.databricks.backend.daemon.driver.DriverWrapper.runInnerLoop(DriverWrapper.scala:438) at com.databricks.backend.daemon.driver.DriverWrapper.runInner(DriverWrapper.scala:381) at com.databricks.backend.daemon.driver.DriverWrapper.run(DriverWrapper.scala:232) at java.lang.Thread.run(Thread.java:750) at com.databricks.backend.daemon.driver.SQLDriverLocal.executeSql(SQLDriverLocal.scala:130) at com.databricks.backend.daemon.driver.SQLDriverLocal.repl(SQLDriverLocal.scala:145) at com.databricks.backend.daemon.driver.DriverLocal.$anonfun$execute$24(DriverLocal.scala:740) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at com.databricks.backend.daemon.driver.DriverLocal.$anonfun$execute$21(DriverLocal.scala:723) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:403) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:401) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:398) at com.databricks.backend.daemon.driver.DriverLocal.withAttributionContext(DriverLocal.scala:62) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:446) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:431) at com.databricks.backend.daemon.driver.DriverLocal.withAttributionTags(DriverLocal.scala:62) at com.databricks.backend.daemon.driver.DriverLocal.execute(DriverLocal.scala:700) at com.databricks.backend.daemon.driver.DriverWrapper.$anonfun$tryExecutingCommand$1(DriverWrapper.scala:622) at scala.util.Try$.apply(Try.scala:213) at com.databricks.backend.daemon.driver.DriverWrapper.tryExecutingCommand(DriverWrapper.scala:614) at com.databricks.backend.daemon.driver.DriverWrapper.executeCommandAndGetError(DriverWrapper.scala:533) at com.databricks.backend.daemon.driver.DriverWrapper.executeCommand(DriverWrapper.scala:568) at com.databricks.backend.daemon.driver.DriverWrapper.runInnerLoop(DriverWrapper.scala:438) at com.databricks.backend.daemon.driver.DriverWrapper.runInner(DriverWrapper.scala:381) at com.databricks.backend.daemon.driver.DriverWrapper.run(DriverWrapper.scala:232) at java.lang.Thread.run(Thread.java:750)

Re: How to transfer ownership of a database and/or table?

dylanberry — Wed, 13 Sep 2023 15:31:46 GMT

Any updates on this issue? I am encountering the same problem.

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

Re: How to transfer ownership of a database and/or table?

VivekChandran — Thu, 15 Aug 2024 12:15:05 GMT

Regarding the [PARSE_SYNTAX_ERROR] Syntax error at or near 'OWNER'.

Remember to wrap the new owner name in the SQL statement with the Grave Accent (`) as the below sample.

ALTER SCHEMA schema_name OWNER TO `new_oner_name`;