以下整個資料顯示在單列中。對于每個 ID，我們可能有多個 Party 如何將其轉換為結構化格式。
{"123": {"Partyl": {"FIRSTNAMEBEN": "ABC","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969"}, “Party2”：{“FIRSTNAMEBEN”：“ABCC”，“ALIASBEN”：“”，“RELATIONSHIPTYPE”：“ABC，FGHIJK LMN”，“DATEOFBIRTH”：“7/Oct/1969”}}，“GeneratedTime”：“ 2022-01-30 03:09:26"}
{"456": {"Partyl": {"FIRSTNAMEBEN": "ABCD","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969"}, "Party2": {"FIRSTNAMEBEN":"ABCDD","ALIASBEN":"","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969"},"Party3": {" FIRSTNAMEBEN": "ABCDDE","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969"}},"GeneratedTime": "2022-01-30 03 :09:26"}
{"345": {},"GeneratedTime": "2022-01-30 03:09:26"}
預期輸出：在此處輸入影像描述
樣本資料：
val values = List(List("123", """"{"123": {"Partyl": {"FIRSTNAMEBEN": "ABC","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN ","DATEOFBIRTH": "7/Oct/1969"},"Party2": {"FIRSTNAMEBEN": "ABCC","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969"}},"GeneratedTime": "2022-01-30 03:09:26"}"""") ,List("456", """"{"456": {" Partyl": {"FIRSTNAMEBEN": "ABCD","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969"},"Party2": {"FIRSTNAMEBEN":"ABCDD","ALIASBEN":"","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969"},"Party3": {"FIRSTNAMEBEN ": "ABCDDE","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969"}},"GeneratedTime": "2022-01-30 03: 09:26"}"""") ,List("345", """"{"345": {},"GeneratedTime": "2022-01-30 03:09:26"}"""" )).map(x =>(x(0), x(1)))7/Oct/1969"},"Party3": {"FIRSTNAMEBEN": "ABCDDE","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969" }},"GeneratedTime": "2022-01-30 03:09:26"}"""") ,List("345", """"{"345": {},"GeneratedTime": "2022 -01-30 03:09:26"}"""")).map(x =>(x(0), x(1)))7/Oct/1969"},"Party3": {"FIRSTNAMEBEN": "ABCDDE","ALIASBEN": "","RELATIONSHIPTYPE": "ABC, FGHIJK LMN","DATEOFBIRTH": "7/Oct/1969" }},"GeneratedTime": "2022-01-30 03:09:26"}"""") ,List("345", """"{"345": {},"GeneratedTime": "2022 -01-30 03:09:26"}"""")).map(x =>(x(0), x(1)))"GeneratedTime": "2022-01-30 03:09:26"}"""")).map(x =>(x(0), x(1)))"GeneratedTime": "2022-01-30 03:09:26"}"""")).map(x =>(x(0), x(1)))
val df = values.toDF ("CaseID","RelatedParty")
df.show
uj5u.com熱心網友回復：
您可以使用以下方式來實作您想要的。
val df = spark.read.option("multiline", true).json("...json")
val df1 = df.
select(explode(array("123.*")) as "level1").
withColumn("ID",lit(df.columns(0))).
withColumn("FIRSTNAMEBEN", col("level1.FIRSTNAMEBEN")).
withColumn("ALIASBEN", col("level1.ALIASBEN")).
withColumn("RELATIONSHIPTYPE", col("level1.RELATIONSHIPTYPE")).
withColumn("DATEOFBIRTH", col("level1.DATEOFBIRTH")).
drop("level1")
Output：
scala> df1.show(false)
--- ------------ -------- ---------------- -----------
|ID |FIRSTNAMEBEN|ALIASBEN|RELATIONSHIPTYPE|DATEOFBIRTH|
--- ------------ -------- ---------------- -----------
|123|ABCC | |ABC, FGHIJK LMN |7/Oct/1969 |
|123|ABC | |ABC, FGHIJK LMN |7/Oct/1969 |
--- ------------ -------- ---------------- -----------
Update 1：
根據modified輸入，下面是一種動態作業的 hack。但是，它還沒有處理這個Null案例，因為我沒有足夠的帶寬來找出Null案例的解決方案。這也是NOT一個最佳解決方案，因為它為每個展平記錄寫入磁盤，因此更高disk I/O。
同時，您可以瀏覽代碼：
val onlyDigitsList = df.columns.toList.filter(x => x.matches("^[0-9]*$"))
var colName=""
for(i<-onlyDigitsList.zipWithIndex)
{
println(i._2)
colName=i._1.toString()
println(colName)
val df1 = df.
select(explode(array(s"$colName.*")) as "level1").
withColumn("ID",lit(colName)).
withColumn("FIRSTNAMEBEN", col("level1.FIRSTNAMEBEN")).
withColumn("ALIASBEN", col("level1.ALIASBEN")).
withColumn("RELATIONSHIPTYPE", col("level1.RELATIONSHIPTYPE")).
withColumn("DATEOFBIRTH", col("level1.DATEOFBIRTH")).
drop("level1")
if(i._2==0)
{
df1.filter((!col("FIRSTNAMEBEN").isNull) &&(!col("ALIASBEN").isNull) && (!col("RELATIONSHIPTYPE").isNull) && (!col("DATEOFBIRTH").isNull)).write.mode("overwrite").json("/home/.../Documents/jsonData/")
}
if(i._2>0)
{
df1.filter((!col("FIRSTNAMEBEN").isNull) &&(!col("ALIASBEN").isNull) && (!col("RELATIONSHIPTYPE").isNull) && (!col("DATEOFBIRTH").isNull)).write.mode("append").json("/home/.../Documents/jsonData/")
}
}
Assumption: 該ID列將是純數字的，否則正則運算式會改變。
如果您的輸入格式發生變化，代碼也會發生變化。資料集沒有這樣generic solution的JSON。
一旦弄清楚Null情況，我將編輯此答案，或者您甚至可以嘗試一下并嘗試調整此代碼，也許您可??以提出更好的解決方案。
uj5u.com熱心網友回復：
schema=StructType([StructField('123',MapType(StringType(),StructType([
StructField('ALIASBEN',StringType(),True),StructField('DATEOFBIRTH',StringType(),True),StructField('FIRSTNAMEBEN',StringType(),True),StructField('RELATIONSHIPTYPE',StringType(),True),]),True))])
df =spark.read.json(path= '/content/sample_data/new.json',multiLine=True,schema=schema)
df.select(explode('123')).select('value.*').show()
uj5u.com熱心網友回復：
// Please note that if your json data is multiline the do add an option for multiline
spark.read.option("multiline", true).json("/path/to/json/file")
.selectExpr("stack(1,'123',`123`) as (ID,value)")
.withColumn("Party1",col("value.Partyl"))
.withColumn("Party2",col("value.Party2"))
.selectExpr("ID","stack(2,'Party1',Party1,'Party2',Party2) as (key1,value1)")
.select(col("ID"),col("value1.*")).show
// Here is the output of the above code.
--- -------- ----------- ------------ ----------------
| ID|ALIASBEN|DATEOFBIRTH|FIRSTNAMEBEN|RELATIONSHIPTYPE|
--- -------- ----------- ------------ ----------------
|123| | 7/Oct/1969| ABC| ABC, FGHIJK LMN|
|123| | 7/Oct/1969| ABCC| ABC, FGHIJK LMN|
--- -------- ----------- ------------ ----------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/432902.html
標籤：json 斯卡拉 阿帕奇火花 蜂巢 apache-spark-sql