Error when attempting to select * from Apache Spark table in the synapse SQL pool

Question

Error when attempting to select * from Apache Spark table in the synapse SQL pool

Merca Ovnerud 1

According to documentation I should be able to query the spark tables without any additional setup.
However, this keeps failing for me.

I created table in spark with df.write.format("delta").saveAsTable(table) and can query this fine in notebook.
When running on dedicated SQL pool

SELECT TOP (100) * FROM sparkdb.dbo.table

Error: Reference to database and/or server name in 'sparkdb.dbo.table' is not supported in this version of SQL Server.
When running on built-in

SELECT TOP (100) * FROM sparkdb.dbo.table

Error is: Invalid object name 'sparkdb.dbo.table'.

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-01-26T14:37:13.537+00:00
@Merca Ovnerud Thanks for using Microsoft Q&A !!

I believe you have not created an external table on your Apache Spark Pool Database. Please create the external table on your sparkdb database using the command like below:

%%pyspark spark.sql("CREATE TABLE IF NOT EXISTS sparkdb.dbo.table USING Parquet LOCATION 'abfss://<filename>@<storage>.dfs.core.windows.net/demodata/demo_df'")

Then you can use the command - SELECT TOP (100) * FROM sparkdb.dbo.table to access the file. You can also validate your table existence after stopping your spark session as well. Please let me know if any issues.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-02-01T16:00:29.997+00:00

@Merca Ovnerud I have not heard back from you. Did my answer solve your issue?
HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2021-02-04T00:24:52.19+00:00

@Merca Ovnerud We have not heard back from you yet . Did @Saurabh Sharma answer solved your issue?
Byers, Luke 1 Reputation point

2021-06-03T13:09:04.137+00:00

I am seeing the same issue except in my case I did fire the script to create the table. The only difference I can see in my case is instead of "...USING PARQUET..." mine is "... USING DELTA...". Is it not possible to use these tables with a delta path?
Toan Thanh Nguyen (DM.1) 1 Reputation point

2021-10-04T06:42:24.697+00:00

@Saurabh Sharma and @HimanshuSinha-msft please confirm if Synapse support external Delta table created by Spark

3 answers

Your answer

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-01-26T14:37:13.537+00:00

@Merca Ovnerud Thanks for using Microsoft Q&A !!

I believe you have not created an external table on your Apache Spark Pool Database. Please create the external table on your sparkdb database using the command like below:

%%pyspark spark.sql("CREATE TABLE IF NOT EXISTS sparkdb.dbo.table USING Parquet LOCATION 'abfss://<filename>@<storage>.dfs.core.windows.net/demodata/demo_df'")

Then you can use the command - SELECT TOP (100) * FROM sparkdb.dbo.table to access the file. You can also validate your table existence after stopping your spark session as well. Please let me know if any issues.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-02-01T16:00:29.997+00:00

@Merca Ovnerud I have not heard back from you. Did my answer solve your issue?
HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2021-02-04T00:24:52.19+00:00

@Merca Ovnerud We have not heard back from you yet . Did @Saurabh Sharma answer solved your issue?
Byers, Luke 1 Reputation point

2021-06-03T13:09:04.137+00:00

I am seeing the same issue except in my case I did fire the script to create the table. The only difference I can see in my case is instead of "...USING PARQUET..." mine is "... USING DELTA...". Is it not possible to use these tables with a delta path?
Toan Thanh Nguyen (DM.1) 1 Reputation point

2021-10-04T06:42:24.697+00:00

@Saurabh Sharma and @HimanshuSinha-msft please confirm if Synapse support external Delta table created by Spark

Answer 1

Toan Thanh Nguyen (DM.1) 1

same issue, the external Parquet table works just fine, but Delta table not working

Answer 2

Stephen Connell 21

I get the same error.

I used this statement from the samples:

spark.sql("CREATE TABLE ExternalDeltaTable2 USING DELTA LOCATION '{0}'".format(delta_table_path))

https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-delta-lake-overview?pivots=programming-language-python#save-as-catalog-tables

I have other parquet scripts that allow me to query the result but for all Delta I get similar to Invalid object name 'default.dbo.externaldeltatable2'.

Stephen Connell 21 Reputation points

2021-11-22T08:44:47.19+00:00
Incidentaly the code:

%%pyspark df = spark.sql("SELECT * FROM `default`.`externaldeltatable2`") df.show(10)

does return a result set. It seems only the SQL serverless SQL queries which is problematic. I have also changed the database away from default to see it that has any impact. It does not.

Answer 3

Kristof De Middelaer 1

Is it confirmed that this indeed does not work with Delta format? Is it known if support is going to be added?

Stephen Connell 21

I'm not certain that (at least I've not had) confirmation has been supplied that this behavior is expected. What I have found from some implementation is that for Spark databases you cannot - or at least if you can I've not found the correct method - select from Spark Database (Lake Databases) using SQL even from the replicated SQL Version of that database. However it is possible to create external Delta format in a SQL Serverless database.

CREATE DATABASE [azure_mdp_lakehouse] 
GO

CREATE EXTERNAL FILE FORMAT DeltaLakeFormat  
WITH (  
        FORMAT_TYPE = DELTA
    ); 
GO

CREATE EXTERNAL DATA SOURCE AzureMDP_Lakehouse_Delta
WITH (
    LOCATION = 'https://foo.dfs.core.windows.net/bar/03-Gold/02-LakeHouse/'
    );
GO

CREATE EXTERNAL TABLE [DimAsset] (
    [DimAssetID] int,
    [AssetID] int,
    [AssetCode] nvarchar(40),
    [AssetName] nvarchar(80),
    [AssetType] nvarchar(16),
    [AssetOperatingType] nvarchar(16),
    [AssetArea] nvarchar(16),
    [AssetParentID] int,
    [LastUpdated] DATETIME
    )
    WITH (
    LOCATION = 'DimAsset',
    DATA_SOURCE = AzureMDP_Lakehouse_Delta,
    FILE_FORMAT = DeltaLakeFormat
    )

Couple of comments:

the external file format definition does not play nice with SMS nor SQL projects in Visual studio.
Time Travel is not supported

Stephen Connell 21 Reputation points

2022-05-10T08:18:19.067+00:00
While you cannot select from the Spark delta table there are advantages to creating it there. If you want to modify the table settings this appears to need to be done in a notebook in sparks

db = 'deltashell' interval = 36 logRetentionDuration = 'interval 367 days' ... for table in tableList: sqlCmd = f""" ALTER TABLE {db}.{table} SET TBLPROPERTIES ( delta.checkpointInterval = '{interval}', delta.logRetentionDuration = '{logRetentionDuration}' ) """ spark.sql(sqlCmd)

When using these settings we will typically create the Spark Database at the time of the change and delete it afterwards, the setting still exists in the log. When we need to do table data maintenance we use spark but typically do not create a spark Delta table at that time. These specific delete and update commands comprising data maintenance feel easier in Spark than building dataflows to manage the change.

delta_table.update( condition=expr("like(CurrencyCode,'%E%')"), set={"CardType": expr("CurrencyCode")} ) delta_table.delete("like(ProductionDay,'2022-%') AND AssetName == 'Foo'")

Share via

Error when attempting to select * from Apache Spark table in the synapse SQL pool

3 answers

Your answer