Error with Create Table USING DELTA LOCATION in training exercise

Question

Error with Create Table USING DELTA LOCATION in training exercise

James Mitchell 15

In the exercise https://microsoftlearning.github.io/mslearn-databricks/Instructions/Exercises/03-Delta-lake-in-Azure-Databricks.html

the line of code

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

returns the error AnalysisException: [RequestId=6a2d00e1-908b-4614-96c6-d5953418fa45 ErrorClass=INVALID_PARAMETER_VALUE] GenerateTemporaryPathCredential uri /delta/products-delta is not a valid URI. Error message: INVALID_PARAMETER_VALUE: Missing cloud file system scheme.

Databricks diagnose error suggests changing the code to

spark.sql("CREATE TABLE AdventureWorks.ProductsExternal USING DELTA LOCATION 'dbfs:{0}'".format(delta_table_path))

The updated code returns the error:

AnalysisException: [UC_FILE_SCHEME_FOR_TABLE_CREATION_NOT_SUPPORTED] Creating table in Unity Catalog with file scheme dbfs is not supported. Instead, please create a federated data source connection using the CREATE CONNECTION command for the same table provider, then create a catalog based on the connection with a CREATE FOREIGN CATALOG command to reference the tables therein.

PRADEEPCHEEKATLA 91,866 Reputation points

2024-05-08T11:17:45.0566667+00:00

@James Mitchell - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

2 answers

Your answer

PRADEEPCHEEKATLA 91,866 Reputation points

2024-05-08T11:17:45.0566667+00:00

@James Mitchell - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

udaybhanu 0 Microsoft Employee

same problem i am facing looks like external tables in unity catalog need complete storage URI

Igor Trush 0 Reputation points

2025-02-06T09:52:25.67+00:00

Same problem facing on step 3. Error has been generated after db AdventureWorks was created and attempted to create external table
Leonardo Blandim 0 Reputation points

2025-03-21T23:27:55.01+00:00

same problem... =/

Igor Trush 0

same problem with https://microsoftlearning.github.io/mslearn-databricks/Instructions/Exercises/DE-01-Real-time-ingestion.html

Step 4:

# create a catalog table based on the streaming sink
spark.sql("CREATE TABLE IotDeviceData USING DELTA LOCATION '{0}'".format(delta_stream_table_path))

1
2
3
AnalysisException: [RequestId=b71248b6-f284-4caa-9a57-e3d7254c2afa ErrorClass=INVALID_PARAMETER_VALUE.INVALID_PARAMETER_VALUE] Missing cloud file system scheme
File

Answer 2

PRADEEPCHEEKATLA 91,866

@James Mitchell - Thanks for the question and using MS Q&A paltform.

Did you run the cell which are created in section Create a notebook and ingest data & Load the file data into a delta table before creating Create catalog tables?

I had tested the Exercise - Use Delta Lake in Azure Databricks from my end and it's working as excepted with Azure Databricks: 14.3 LTS (includes Apache Spark 3.5.0, Scala 2.12).

Step1: Create a notebook and ingest data
User's image

Step2: Load the file data into a delta table

User's image

Step3: Create catalog tables

User's image

After follow-up these three steps if you are still expering the same issue, please do let us know on your Azure databricks runtime used?

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

James Mitchell 15 Reputation points

2024-05-02T12:14:59.4633333+00:00
I did run all the cells in the notebook

I was using Unrestricted, single node, single user, 13.3 LTS with Spark 3.41 and Standard_D3_V2 and Standard_D4ds_v5

When I modified the cluster to 14.3 LTS and Spark 3.5.0 the error message changed to:

AnalysisException: [RequestId=a0f6ec0e-c88a-41eb-b4e6-985fe0a37747 ErrorClass=INVALID_PARAMETER_VALUE] GenerateTemporaryPathCredential uri /delta/products-delta is not a valid URI. Error message: INVALID_PARAMETER_VALUE: Missing cloud file system scheme.

File <command-2518999536623076>, line 1 ----> 1 spark.sql("CREATE TABLE AdventureWorks.ProductsExternal USING DELTA LOCATION '{0}'".format(delta_table_path))

File /databricks/spark/python/pyspark/errors/exceptions/captured.py:230, in capture_sql_exception.<locals>.deco(*a, **kw) 226 converted = convert_exception(e.java_exception) 227 if not isinstance(converted, UnknownException): 228 # Hide where the exception came from that shows a non-Pythonic 229 # JVM exception message. --> 230 raise converted from None 231 else: 232 raise

My best guess is this is happening because I am using the free trial version of Azure Databricks and couldn't use the setup script which defaults to a premium subscription.

Instead,

I logged into Azure (which is set up with the 1 month and $200 credits)

clicked on create a resource

Analytics

create an Azure Databricks workspace

Create new Resource Group

Pricing Tier Trial.
PRADEEPCHEEKATLA 91,866 Reputation points

2024-05-06T05:50:37.2866667+00:00

@James Mitchell - Thanks for the update. I will test the same on envornment as shared above and will get back to you as soon.
PRADEEPCHEEKATLA 91,866 Reputation points

2024-05-06T10:53:03.4566667+00:00

@James Mitchell - Yes, it's looks like a know limitaion with Azure Databricks with Trial (Premium - 14-Days Free DBUs).

Note: The same limitation has been metioned in the documenation: https://microsoftlearning.github.io/mslearn-databricks/Instructions/Exercises/03-Delta-lake-in-Azure-Databricks.html

When I tested on Unrestricted, single node, single user, 13.3 LTS with Spark 3.41 and Standard_D3_V2, I was experiencing the same issue as shown above:

To succesfully run the lab, you can create a Premium tier Azure Databricks workspace or upgrade the existing Trial (Premium - 14-Days Free DBUs)

to Premium.

Now the existing Trial (Premium - 14-Days Free DBUs)

upgraded to Premium

Hope this helps. Do let us know if you any further queries.
PRADEEPCHEEKATLA 91,866 Reputation points

2024-05-20T05:53:37.2866667+00:00

@James Mitchell - Following up to see if the above answer was helpful. Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Rajeshwar Busa 0 Reputation points

2024-10-09T18:53:01.0266667+00:00

Hey it still isnt working
Sylvain MENEUST 0 Reputation points

2024-11-20T15:43:17.59+00:00

Hello

Same problem today even with compute 14.3 TLS in premium account and premium Databricks.

We can't anymore use delta table location stored into dbfs in databricks because of the Unity Catalog ?

Then the exercice need an update. Impossible to complete like that.

Share via

Error with Create Table USING DELTA LOCATION in training exercise

2 answers

Your answer