This article is contributed. See the original author and article here.

NaN stands for Not a Number. This scenario was a customer trying to insert a parquet file into SQL, but he was not able to do it.

That’s because of the following error in one column: Error converting values NaN or Infinity to type ‘FLOAT’. NaN and Infinity are not supported.

 

The example bellow is based on this piece of documentation:

https://docs.microsoft.com/en-us/azure/synapse-analytics/spark/synapse-spark-sql-pool-import-export

 

I added  some customization and also I have some nice discussion with my colleague Diya Mothafar. So my colleague mentioned to do the same using Pandas which also valid. My demo will not use Pandas, but again it also does the job.

 

First open Synapse Studio -> Notebook ->PySpark

phytonNotebook.png

Fig 1 PySpark

 

%%pyspark
from pyspark.sql.functions import col, when

data_path = spark.read.load('abfss://filesystemdatalake@mystorage.dfs.core.windows.net/test/filetest.snappy.parquet', format='parquet')

#here we handle the NaN values
data_path = data_path.replace(float('nan'), None)

data_path.createOrReplaceTempView("pysparkdftemptable")

 

Add a Scala cell into the Notebook. Note the table will be created with the JOB you do not need to create in advanced.

addcel.png

Fig 2 Add Cell

%%spark
val scala_df = spark.sqlContext.sql ("select * from pysparkdftemptable")
//scala_df.show(100)
scala_df.write.sqlanalytics("YourDatabaseName.dbo.PySparkTable", Constants.INTERNAL)

 

Your new cell should look like this

cell.png

Fig 3 Cell

 

Once the Job is complete. You can check the results by opening SSMS and querying the table.

 

That is it!

Liliam 

Uk Engineer

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

%d bloggers like this: