04-11-2024 09:06 AM
Hi all
iam working on a data containing JSON fields with embedded commas into CSV format. iam facing challenges due to the commas
within the JSON being misinterpreted as column delimiters during the conversion process.
i tried several methods to modify data and tried to escape the comma comes under the row value but it doesent works at the same time i should make sure the JSON code has to be in correct syntax as that will be used in some other place within the project
the sample pyspark code and data that i worked with
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType
schema = StructType([
StructField("IDcol1", IntegerType(), True),
StructField("IDcol2", IntegerType(), True),
StructField("AdditionalRequestParameters", StringType(), True),
StructField("RequestURL", StringType(), True)
])
data = [
(1, 2, "{'Locale':'en','KnowledgeType':[{'Name':'IndustryKnowledge'},{'Name':'KnowledgeIndustry'}],'SegmentCountry':[{'Country':'US','IndustrySegment':'IP'}],'Setversion':'VersionValue','Flags':null,'ScalingID':0,'VersionInfo':{'PracticeSubType':'SubPracticeValue','Version':'VersionValue'}}", 'https://abc/something/API/2021v3/nothing/data'),
]
df = spark.createDataFrame(data, schema=schema)
df.display()
by displaying as an dataframe its no doubt it works fine
and that is how the expected data should be but while writing it into an CSV file in my ADLS it misbehaves and creates an new column for every comma that comes under the JSON column
anyway iam unable to read the csv data i tried display() and show() and when i look into the csv file that generated from the container this is what i was able to find
please help me how to handle this commas . Thnaks
04-14-2024 07:39 AM
Hello,
I managed to parse using the "json_tuple" function
There are other functions that can help
schema_of_json
get_json_object
from_json
04-14-2024 09:19 AM
OHH ,thankyou for your time, that was working well for but that was extracting the json data from the json column which is ok but our real issue is when we try to write the dataframe into an csv we get values from AdditionalRequestParameters column that gets splitted into many columns due to comma contains inside the data and finally instead of having 4 columns while reading the csv file we get many number of columns . please help me if any technique can be used to handle this issue . thanks
04-16-2024 12:05 PM
Hello,
You will need to use some CSV configuration parameters
follow the documentation below
https://spark.apache.org/docs/latest/sql-data-sources-csv.html
04-16-2024 04:55 AM - edited 04-16-2024 04:58 AM
Hi Sai,
I assume that the problem comes not from the PySpark, but from Excel.
I tried to reproduce the error and didn't find the way - that a good thing, right ? Please try the following :
df.write.format("csv").save("/Volumes/<my_catalog_name>/<my_schema_name>/<my_volume_name>/<file_name>")
(how to create a Volume ; Volumes have a lot of value, but if you prefer to use a path in your object storage it's also ok)
04-16-2024 07:39 AM
sounds an cool option but here we are leveraging Azure Data Lake Storage as an medium of storage and we directly write the data into the preferred location within ADLS so thats where things gets complicated , any idea?
04-16-2024 09:10 AM
Regardless on how you create the file (however, take a look at Volumes, I'm 100% you'll see the value), please try to proceed with an approach with Excel I described above
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group