我有一個json如下存盤在json檔案中的amazon s3中。
{
"owner": "mindey",
"ownerId": "4e751a",
"batch": "test1",
"mode": "testserve",
"subType": "self",
"versionId": "v89.0563",
"createdDate": "2021-12-10T00:41:02.652Z",
"modifiedDate": "2021-12-10T00:41:02.652Z",
"createdBy": "[email protected]",
"modifiedBy": "[email protected]",
"numChanges": 10,
"progressFlag": true,
"auditing": {
"hack": "TBD",
"facetCd": [
"652439",
"A97341"
],
"modelColors": [
"083y32-058",
"978t22-456",
"A097ee-009"
],
"consumersGroup": [
"25a9874-098a-08nh-4424-90838e"
],
"cnsmrTargets": [
{
"id": "7jsfg8734-038h-9825-9784-37768ebe6ba1",
"type": "self"
}
],
"modelGroups": [
{
"seedIds": [
"7kjshf99-754a-gf78-a015",
"6d874mq3-23fa-4e76-baad"
],
"exact": true,
"position": 5
}
],
"txGroups": [
{
"txIds": [
"7bw97862-aca7-9873",
"5ddfb803-2f11-8763"
]
}
],
"shops": [
"ue749nt2-acc6-2452",
"ue749nt2-982y-4286"
]
}
}
現在，我閱讀了這些檔案并希望在 pyspark 中定義一個模式以將資料附加到表中。所以我定義了我的架構，并通過 pyspark 中的臨時資料框讀取它，如下所示。代碼沒有出錯，但是我希望得到一個不同的結果，這也在下面提到
sch = StructType([StructField("owner",StringType()),
StructField("ownerId",StringType()),
StructField("batch",StringType()),
StructField("mode",StringType()),
StructField("subType",StringType()),
StructField("versionId",StringType()),
StructField("createdDate",StringType()),
StructField("modifiedDate",StringType()),
StructField("createdBy",StringType()),
StructField("modifiedBy",StringType()),
StructField("numChanges",StringType()),
StructField("progressFlag",StringType()),
StructField("auditing",StringType())])
df_read = spark.read.json(src,schema=sch)
df_read.createOrReplaceTempView('df')
spark.sql("select * from df").show(10,False)
------ ------- ----- ---- ------- --------- ------------------------ ------------------------ ------------ ------------ ---------- ------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|owner |ownerId|batch|mode|subType|versionId|createdDate |modifiedDate |createdBy |modifiedBy |numChanges|progressFlag|auditing |
------ ------- ----- ---- ------- --------- ------------------------ ------------------------ ------------ ------------ ---------- ------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|mindey|4e751a |test1|null|self |v89.0563 |2021-12-10T00:41:02.652Z|2021-12-10T00:41:02.652Z|[email protected]|[email protected]|10 |true |{"hack":"TBD","facetCd":["652439","A97341"],"modelColors":["083y32-058","978t22-456","A097ee-009"],"consumersGroup":["25a9874-098a-08nh-4424-90838e"],"cnsmrTargets":[{"id":"7jsfg8734-038h-9825-9784-37768ebe6ba1","type":"self"}],"modelGroups":[{"seedIds":["7kjshf99-754a-gf78-a015","6d874mq3-23fa-4e76-baad"],"exact":true,"position":5}],"txGroups":[{"txIds":["7bw97862-aca7-9873","5ddfb803-2f11-8763"]}],"shops":["ue749nt2-acc6-2452","ue749nt2-982y-4286"]}|
------ ------- ----- ---- ------- --------- ------------------------ ------------------------ ------------ ------------ ---------- ------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
我希望輸出如下所示，其中嵌套的 json 被適當地拆分為不同的列 -
------ ------- ----- ---- ------- --------- ------------------------ ------------------------ ------------ ------------ ---------- ------------ ------------- -------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------------------- -------------------------------------------------------- -------------------------------------------------
|owner |ownerId|batch|mode|subType|versionId|createdDate |modifiedDate |createdBy |modifiedBy |numChanges|progressFlag|auditing_hack|auditing_facetCd |auditing_modelColors |auditing_consumersGroup |auditing_cnsmrTargets_id |auditing_cnsmrTargets_type |auditing_modelGroups_seedIds |auditing_txGroups_txIds |auditing_txGroups_shops |
------ ------- ----- ---- ------- --------- ------------------------ ------------------------ ------------ ------------ ---------- ------------ ------------- -------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------------------- -------------------------------------------------------- -------------------------------------------------
|mindey|4e751a |test1|null|self |v89.0563 |2021-12-10T00:41:02.652Z|2021-12-10T00:41:02.652Z|[email protected]|[email protected]|10 |true |TBD |["652439","A97341"] |["083y32-058","978t22-456","A097ee-009"] |25a9874-098a-08nh-4424-90838e |7jsfg8734-038h-9825-9784-37768ebe6ba1 |self |["7kjshf99-754a-gf78-a015","6d874mq3-23fa-4e76-baad"] |["7bw97862-aca7-9873","5ddfb803-2f11-8763"] |["ue749nt2-acc6-2452","ue749nt2-982y-4286"]} |
------ ------- ----- ---- ------- --------- ------------------------ ------------------------ ------------ ------------ ---------- ------------ ------------- -------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------- -------------------------------------------------------- -------------------------------------------------------- -------------------------------------------------
有人可以幫我解決這個結果嗎？提前致謝！！
uj5u.com熱心網友回復：
架構物件應如下所示：
sch = StructType([
StructField("owner",StringType()),
StructField("ownerId",StringType()),
StructField("batch",StringType()),
StructField("mode",StringType()),
StructField("subType",StringType()),
StructField("versionId",StringType()),
StructField("createdDate",StringType()),
StructField("modifiedDate",StringType()),
StructField("createdBy",StringType()),
StructField("modifiedBy",StringType()),
StructField("numChanges",StringType()),
StructField("progressFlag",StringType()),
StructField("auditing",StructType([
StructField('hack', StringType()),
StructField('facetCd', ArrayType(StringType())),
StructField('modelColors', ArrayType(StringType())),
StructField('consumersGroup', ArrayType(StringType())),
StructField('cnsmrTargets', ArrayType(StringType())),
StructField('modelGroups', ArrayType(StructType([
StructField('seedIds', ArrayType(StringType())),
StructField('exact', StringType()),
StructField('position', IntegerType()),
]))),
StructField('facetCd', ArrayType(StructType([
StructField('txIds', ArrayType(StringType())),
]))),
StructField('shops', ArrayType(StringType())),
]))
]
)
應用架構后，您將獲得一個用于審計列的嵌套結構。然后選擇陳述句和別名的組合可以獲得所需的列名。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/394034.html