This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 26%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
Hello,
I am unable to run a simple spark.sql() (ex. df = spark.sql("SELECT * FROM table1")) in Synapse notebooks. I am able to load and view the file without using SQL, but when using spark.sql() I receive errors for all files including csv and parquet file types.
I have tried different sized clusters, restarting clusters, spark versions, and changing the language and code from PySpark to Scala. My workspace has permission to access my data in ADLS Gen 2. Apologies if this question has already been answered elsewhere. Below is the error I am receiving.
AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.NullPointerException;
Traceback (most recent call last):
File "/opt/spark/python/lib/pyspark.zip/pyspark/sql/session.py", line 767, in sql
return DataFrame(self._jsparkSession.sql(sqlQuery), self._wrapped)
File "/opt/spark/python/lib/py4j-0.10.7-src.zip/py4j/java_gateway.py", line 1257, in call
answer, self.gateway_client, self.target_id, self.name)
File "/opt/spark/python/lib/pyspark.zip/pyspark/sql/utils.py", line 75, in deco
raise AnalysisException(s.split(': ', 1)[1], stackTrace)
pyspark.sql.utils.AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.NullPointerException;
Thanks
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 77%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERA%3C/text%3E%3C/svg%3E)
To ask the obvious... are you sure the table exists? In the "Data" screen, can you find the table?
Yes, but thanks for checking.
I can load it with PySpark and run a .show() just fine. I just receive errors when running .sql().
Posting the solution I was given after contacting support:
This is a bug that sometimes occurs when the workspace is created. After I created a new workspace and ran the same commands, the code worked great.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
Thank you for sharing the learnings!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(297.6, 57.00000000000001%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECM%3C/text%3E%3C/svg%3E)
I am using the "Getting Started with Delta Lake" from the Gallery, that Microsoft created. I am getting an error in cell 33, see below. I didn't really do anything except run the notebook a cell at atime. I had already created a delta database from a very simple script.
I can't believe the answer is to create a new workspace. For me that would be allot of work. Is there no way to fix.
Does anyone have a better answer?
Thanks,
Mike
AnalysisException: Table default.ManagedDeltaTable already exists
Traceback (most recent call last):
File "/opt/spark/python/lib/pyspark.zip/pyspark/sql/readwriter.py", line 1158, in saveAsTable
self._jwrite.saveAsTable(name)
File "/home/trusted-service-user/cluster-env/env/lib/python3.8/site-packages/py4j/java_gateway.py", line 1304, in __call__
return_value = get_return_value(
File "/opt/spark/python/lib/pyspark.zip/pyspark/sql/utils.py", line 117, in deco
raise converted from None
pyspark.sql.utils.AnalysisException: Table default.ManagedDeltaTable already exists
how are you loading in PySpark? Via the forPath? Did you do a "saveAsTable" on creation? (or any subsequent table creation command)?
I am loading with the folder path. Such as the following:
path = 'abfss://<container>@<account>.dfs.core.windows.net/<path>
df = spark.read.option('header', 'true') \
.option('delimiter', ',') \
.csv(path)
dfSQL = spark.sql("SELECT * FROM df")
The parquet file was loaded with this:
parquetFile = spark.read.parquet("<path>")
parquetFile.createOrReplaceTempView("parquetFile")
df = spark.sql("SELECT * FROM parquetFile")
df.show()
If I removed df = spark.sql("SELECT * FROM parquetFile"), and ran parquetFile.show() it would load.
ahh, no, you can't select from a dataframe in that way
If you want to use SQL on a dataframe, then as you did in subsequent comment you can df.createOrReplaceTempView(<name of temp view>) and then you can do "spark.sql('select * from <name of temp view>')"
Otherwise you can use the dataframe directly by doing df.select(...) or df.filter(...) etc
parquet file in "parquetFile.show()" is your daaframe (from "parquetFile = spark.read.parquet("<path>")") not the temp view
Hmmm, are you suggesting something like the following?
parquetFile = spark.read.parquet("<path>")
parquetFile.createOrReplaceTempView("df")
df2 = spark.sql("SELECT * FROM df")
df2.show()
This seems to shoot me back an error as well. Sorry for all the trouble with something simple!
What's the new error?
Start off with
parquetFile = spark.read.parquet(<path>)
display(parquetFile)
do you get a result?
Do note that parquetFile that you've assigned is a dataframe, you don't need to do the df2 bit because you're affectively just assigning parquetFile to df2
Alright this worked fine:
parquetFile = spark.read.parquet(<path>)
display(parquetFile)
Here is the error that I get when running:
parquetFile = spark.read.parquet("<path>")
parquetFile.createOrReplaceTempView("df")
df2 = spark.sql("SELECT * FROM df")
df2.show()
Error:
AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.NullPointerException;
Traceback (most recent call last):
File "/opt/spark/python/lib/pyspark.zip/pyspark/sql/session.py", line 767, in sql
return DataFrame(self._jsparkSession.sql(sqlQuery), self._wrapped)
File "/opt/spark/python/lib/py4j-0.10.7-src.zip/py4j/java_gateway.py", line 1257, in call
answer, self.gateway_client, self.target_id, self.name)
File "/opt/spark/python/lib/pyspark.zip/pyspark/sql/utils.py", line 75, in deco
raise AnalysisException(s.split(': ', 1)[1], stackTrace)
pyspark.sql.utils.AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.NullPointerException;
hmmm, don't know...
if you ran the below in a new cell, does it work?
%%sql
select * from df
It does not. It returns a long list of org.apache.spark.sql errors.
I'm not spotting anything at issue with your statements, maybe a support call with Microsoft is required
Thanks for trying to figure it out with me. I appreciate it! I'll give them a call.
I changed the code in cell 33..
# Write data to a new managed catalog table.
## old...... data.write.format("delta").saveAsTable("ManagedDeltaTable")
##new
data.write.format("delta").mode("overwrite").saveAsTable("ManagedDeltaTable")