Forum Discussion
Load table from shortcut to ADLS Gen2 storage using spark sql not working
Hi, I have triied all these suggestions already unfortunately
1.Run the following commands to check the active catalog and schema:
print(spark.sql("SELECT current_catalog()").collect())
print(spark.sql("SELECT current_schema()").collect())
[Row(current_catalog()='spark_catalog')]
[Row(current_database()='8hgn8o..................................................................')]
2.Explicitly set the catalog and schema in your session:
spark.sql("USE CATALOG DLHDEV02")
spark.sql("USE SCHEMA AzureDevOps")
--------------------------------------------------------------------------- ParseException Traceback (most recent call last) Cell In[8], line 3 1 # Welcome to your new notebook 2 # Type here in the cell editor to add code! ----> 3 spark.sql("USE CATALOG DLHDEV02") 4 spark.sql("USE SCHEMA AzureDevOps") 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:175, in capture_sql_exception.<locals>.deco(*a, **kw) 171 converted = convert_exception(e.java_exception) 172 if not isinstance(converted, UnknownException): 173 # Hide where the exception came from that shows a non-Pythonic 174 # JVM exception message. --> 175 raise converted from None 176 else: 177 raise ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near 'DLHDEV02': extra input 'DLHDEV02'.(line 1, pos 12) == SQL == USE CATALOG DLHDEV02 ------------^^^
3.If setting the context is not feasible, always qualify the table name fully:
df = spark.sql("SELECT * FROM DLHDEV02.AzureDevOps.Teams LIMIT 1000")
display(df)
This ensures that Spark SQL knows where to look for the table.
This is what using the 'load table' feature was doing
5.If the table is missing in the metastore, you can register it manually:
--------------------------------------------------------------------------- Py4JJavaError Traceback (most recent call last) Cell In[17], line 1 ----> 1 spark.sql(f""" 2 CREATE TABLE DLHDEV02.AzureDevOps.Teams 3 USING DELTA 4 LOCATION 'abfss://<workspace>@onelake.dfs.fabric.microsoft.com/DLHDEV02.Lakehouse/Tables/AzureDevOps/Teams' 5 """)
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.
Based on the issues you're encountering, here’s the updated solution:
1. you should fully qualify the table name in your queries since USE CATALOG and USE SCHEMA aren't working
df = spark.sql("SELECT * FROM DLHDEV02.AzureDevOps.Teams LIMIT 1000")
display(df)
2.The "only managed tables are supported" error suggests that Spark is expecting a managed table. Since your table is stored externally, you should register it as an external table like below
spark.sql(f"""
CREATE EXTERNAL TABLE DLHDEV02.AzureDevOps.Teams
USING DELTA
LOCATION 'abfss://<workspace>@onelake.dfs.fabric.microsoft.com/DLHDEV02.Lakehouse/Tables/AzureDevOps/Teams'
""")
3. Check if the table is properly registered using below code
spark.sql("SHOW TABLES IN DLHDEV02.AzureDevOps").show()
Please continue using Microsoft community forum.
If you found this post helpful, please consider marking it as "Accept as Solution" and select "Yes" if it was helpful and add "kudos". help other members find it more easily.
Regards,
Pavan.
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 supportedHi 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.