Forum Discussion
Load table from shortcut to ADLS Gen2 storage using spark sql not working
Hi, thanks for the reply. This also doesn't work as the 'table path already exists'. I would have expected that using the import schema function that it would define the schema and tables?
---------------------------------------------------------------------------
Py4JJavaError Traceback (most recent call last)
Cell In[47], line 1
----> 1 spark.sql(f"""
2 CREATE EXTERNAL TABLE DLHDEV02.AzureDevOps.Teams
3 USING DELTA
4 LOCATION 'abfss://<workspace>@onelake.dfs.fabric.microsoft.com/DLHDEV02.Lakehouse/Tables/AzureDevOps/Teams'
5 """)
File /opt/spark/python/lib/pyspark.zip/pyspark/sql/session.py:1440, in SparkSession.sql(self, sqlQuery, args, **kwargs)
1438 try:
1439 litArgs = {k: _to_java_column(lit(v)) for k, v in (args or {}).items()}
-> 1440 return DataFrame(self._jsparkSession.sql(sqlQuery, litArgs), self)
1441 finally:
1442 if len(kwargs) > 0:
File ~/cluster-env/trident_env/lib/python3.10/site-packages/py4j/java_gateway.py:1322, in JavaMember.__call__(self, *args)
1316 command = proto.CALL_COMMAND_NAME +\
1317 self.command_header +\
1318 args_command +\
1319 proto.END_COMMAND_PART
1321 answer = self.gateway_client.send_command(command)
-> 1322 return_value = get_return_value(
1323 answer, self.gateway_client, self.target_id, self.name)
1325 for temp_arg in temp_args:
1326 if hasattr(temp_arg, "_detach"):
File /opt/spark/python/lib/pyspark.zip/pyspark/errors/exceptions/captured.py:169, in capture_sql_exception.<locals>.deco(*a, **kw)
167 def deco(*a: Any, **kw: Any) -> Any:
168 try:
--> 169 return f(*a, **kw)
170 except Py4JJavaError as e:
171 converted = convert_exception(e.java_exception)
File ~/cluster-env/trident_env/lib/python3.10/site-packages/py4j/protocol.py:326, in get_return_value(answer, gateway_client, target_id, name)
324 value = OUTPUT_CONVERTER[type](answer[2:], gateway_client)
325 if answer[1] == REFERENCE_TYPE:
--> 326 raise Py4JJavaError(
327 "An error occurred while calling {0}{1}{2}.\n".
328 format(target_id, ".", name), value)
329 else:
330 raise Py4JError(
331 "An error occurred while calling {0}{1}{2}. Trace:\n{3}\n".
332 format(target_id, ".", name, value))
Py4JJavaError: An error occurred while calling o324.sql.
: com.azure.storage.file.datalake.models.DataLakeStorageException: Status code 409, "{"error":{"code":"PathAlreadyExists","message":"The specified path already exists.\nRequestId:97d86a26-301f-000d-3c72-72ce0c000000\nTime:2025-01-29T17:26:17.6573026Z"}}"
If I try to change the table name that is being created there is a different error again stating that only managed tables are supported.
---------------------------------------------------------------------------
Py4JJavaError Traceback (most recent call last)
Cell In[11], line 1
----> 1 spark.sql(f"""
2 CREATE EXTERNAL TABLE DLHDEV02.AzureDevOps.TeamsTest
3 USING DELTA
4 LOCATION 'abfss://<workspace>@onelake.dfs.fabric.microsoft.com/DLHDEV02.Lakehouse/Tables/AzureDevOps/Teams'
5 """)
File /opt/spark/python/lib/pyspark.zip/pyspark/sql/session.py:1440, in SparkSession.sql(self, sqlQuery, args, **kwargs)
1438 try:
1439 litArgs = {k: _to_java_column(lit(v)) for k, v in (args or {}).items()}
-> 1440 return DataFrame(self._jsparkSession.sql(sqlQuery, litArgs), self)
1441 finally:
1442 if len(kwargs) > 0:
File ~/cluster-env/trident_env/lib/python3.10/site-packages/py4j/java_gateway.py:1322, in JavaMember.__call__(self, *args)
1316 command = proto.CALL_COMMAND_NAME +\
1317 self.command_header +\
1318 args_command +\
1319 proto.END_COMMAND_PART
1321 answer = self.gateway_client.send_command(command)
-> 1322 return_value = get_return_value(
1323 answer, self.gateway_client, self.target_id, self.name)
1325 for temp_arg in temp_args:
1326 if hasattr(temp_arg, "_detach"):
File /opt/spark/python/lib/pyspark.zip/pyspark/errors/exceptions/captured.py:169, in capture_sql_exception.<locals>.deco(*a, **kw)
167 def deco(*a: Any, **kw: Any) -> Any:
168 try:
--> 169 return f(*a, **kw)
170 except Py4JJavaError as e:
171 converted = convert_exception(e.java_exception)
File ~/cluster-env/trident_env/lib/python3.10/site-packages/py4j/protocol.py:326, in get_return_value(answer, gateway_client, target_id, name)
324 value = OUTPUT_CONVERTER[type](answer[2:], gateway_client)
325 if answer[1] == REFERENCE_TYPE:
--> 326 raise Py4JJavaError(
327 "An error occurred while calling {0}{1}{2}.\n".
328 format(target_id, ".", name), value)
329 else:
330 raise Py4JError(
331 "An error occurred while calling {0}{1}{2}. Trace:\n{3}\n".
332 format(target_id, ".", name, value))
Py4JJavaError: An error occurred while calling o324.sql.
: java.lang.AssertionError: assertion failed: Only managed tables are supported
Hi Anonymous,
Thank you for reaching out in Microsoft Community Forum.
Please follow below steps to acheive 'table path already exists' error;
1. Check if the table already exists in the metastore using below code
spark.sql("SHOW TABLES IN DLHDEV02.AzureDevOps").show()
If the table exists, drop it before re-creating:
spark.sql("DROP TABLE IF EXISTS DLHDEV02.AzureDevOps.Teams")
2. If you're registering the table as an external table, ensure the path is correct and doesn't conflict with any existing data. You can try registering the table as an external table:
spark.sql("""
CREATE EXTERNAL TABLE DLHDEV02.AzureDevOps.Teams
USING DELTA
LOCATION 'abfss://<workspace>@onelake.dfs.fabric.microsoft.com/DLHDEV02.Lakehouse/Tables/AzureDevOps/Teams'
""")
If the error persists and Spark expects a managed table, create the table as a managed table:
spark.sql("""
CREATE TABLE DLHDEV02.AzureDevOps.Teams
USING DELTA
LOCATION 'abfss://<workspace>@onelake.dfs.fabric.microsoft.com/DLHDEV02.Lakehouse/Tables/AzureDevOps/Teams'
""")
3. make sure there are no conflicts with existing data paths when registering the table.
if still issue is persists, please raise a support ticket and provide them with all the troubleshooting steps you've already taken, which will help them understand the issue better and provide a resolution.
Below is the link to create Microsoft Support ticket:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket
If you found this post helpful, please consider marking it as "Accept as Solution" and select "Yes" if it was helpful. help other members find it more easily.
Regards,
Pavan.