a month ago
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
4 weeks ago
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
4 weeks ago
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
3 weeks ago
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
3 weeks ago - last edited 3 weeks ago
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)
3 weeks ago
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?
3 weeks ago
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 our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections.
Click here to register and join today!
Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.