topic Re: [DeltaTable] Usage with Unity Catalog (ParseException) in Get Started Discussions

[DeltaTable] Usage with Unity Catalog (ParseException)

Ludo — Wed, 09 Aug 2023 15:04:35 GMT

Hi,

I'm migrating my workspaces to Unity Catalog and the application to use three-level notation. (catalog.database.table)

See: Tutorial: Delta Lake | Databricks on AWS

I'm having the following exception when trying to use DeltaTable.forName(string name) or DeltaTable.tableName(string name) with three-level notation such as catalog.database.table :

org.apache.spark.sql.catalyst.parser.ParseException : [PARSE_SYNTAX_ERROR] Syntax error at or near '.'.(line 1, pos 18) == SQL == spark_catalog.Gold.FactSyPerson ------------------^^^ at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:306) at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:144) at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:52) at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parseTableIdentifier(ParseDriver.scala:54) at io.delta.sql.parser.DeltaSqlParser.parseTableIdentifier(DeltaSqlParser.scala:138) at io.delta.tables.DeltaTable$.forName(DeltaTable.scala:783) at io.delta.tables.DeltaTable$.forName(DeltaTable.scala:770)

Looks like it's not supported yet, could you please help? Is there a workaround?

Thank you.

Re: [DeltaTable] Usage with Unity Catalog (ParseException)

saipujari_spark — Wed, 09 Aug 2023 19:50:19 GMT

Hey @Ludo I am able to access the table using 3 level namespace.

make sure

1. you are using a Unity catalog-enabled cluster

2. try using the latest DBR

Re: [DeltaTable] Usage with Unity Catalog (ParseException)

Ludo — Thu, 10 Aug 2023 13:16:09 GMT

Thank you for the quick feedback @saipujari_spark

Indeed, it's working great within a notebook with Databricks Runtime 13.2 which most likely has a custom behavior for unity catalog.

It's not working in my scala application running in local with direct use of delta-core libraries 2.4.0. (no databricks runtime locally) There is a missing piece of software within delta-core libraries I guess.

I hope it will be updated soon if my understanding is correct.

Nevertheless, spark libraries are ready thanks to this MR => [SPARK-39235] Make Catalog API be compatible with 3-layer-namespace - ASF JIRA (apache.org)

Re: [DeltaTable] Usage with Unity Catalog (ParseException)

eniqen — Wed, 09 Oct 2024 15:23:51 GMT

Hi guys, I also faced the same problem but with Pyspark API inside unit tests, mainly the problem happens when we are building standalone spark session with support of delta extesnsion, inside pyspark this still does not work,

pyspark="3.5.3"
delta-spark="3.2.1"

for example I have fixture like this

@pytest.mark.usefixtures("spark_session") def test_join_operation_with_catalog(self, spark_session: SparkSession): source_schema = StructType([ StructField("id", StringType(), True), StructField("derived_column", StringType(), True), StructField("filter_column", StringType(), True) ]) spark_session.sql("CREATE SCHEMA source") spark_session.sql("DROP TABLE IF EXISTS spark_catalog.source.source_table_join") spark_session.catalog.setCurrentCatalog("spark_catalog") spark_session.catalog.setCurrentDatabase("source") DeltaTable.createOrReplace(spark_session).tableName("source_table_join").addColumns( source_schema).execute() try: print(DeltaTable.forName(spark_session, "source.source_table_join").toDF().collect()) print('SUCCESS') except Exception as err: print("FAILURE") print(err)

@pytest.fixture(scope="session") def spark_session(): shutil.rmtree("spark-warehouse", ignore_errors=True) os.environ['PYSPARK_PYTHON'] = sys.executable os.environ['PYSPARK_DRIVER_PYTHON'] = sys.executable builder = (SparkSession.builder .master("local[*]") .config("spark.jars.packages", "io.delta:delta-core_2.12:2.3.0") .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension") .config("spark.databricks.delta.schema.autoMerge.enabled", "true") .config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog") .appName("test") ) yield configure_spark_with_delta_pip(builder).getOrCreate() shutil.rmtree("spark-warehouse", ignore_errors=True)

at the end it will thrown with Parsing exception like

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

== SQL ==
spark_catalog.source.source_table_join
--------------------^^^
and

DeltaTable.createOrReplace also does not work if I use full qualified name catalog.schema.table

Re: [DeltaTable] Usage with Unity Catalog (ParseException)

PTangelder — Wed, 20 Nov 2024 13:39:49 GMT

Same here:

pyspark="3.5.3"
delta-spark="3.2.1"

spark.sql("SHOW CATALOGS").show() spark.sql("SHOW TABLES IN mycatalog.myschema").show() spark.sql("DESCRIBE mycatalog.myschema.mytable").show()

Code above is working with the three-level notation. (catalog.database.table)

When using the three-level notation with following code, the same error occurs:

from delta import DeltaTable dt = DeltaTable.forName(spark, "mycatalog.myschema.mytable")

Traceback (most recent call last): File "/workspace/test.py", line 13, in <module> dt = DeltaTable.forName(spark, "mycatalog.myschema.mytable") File "/venv/lib/python3.10/site-packages/delta/tables.py", line 419, in forName jdt = jvm.io.delta.tables.DeltaTable.forName(jsparkSession, tableOrViewName) File "/venv/lib/python3.10/site-packages/py4j/java_gateway.py", line 1322, in __call__ return_value = get_return_value( File "/venv/lib/python3.10/site-packages/pyspark/errors/exceptions/captured.py", line 185, in deco raise converted from None pyspark.errors.exceptions.captured.ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near '.'.(line 1, pos 11) == SQL == mycatalog.myschema.mytable ------------------^^^