Pyspark:从涉及数组列的Json模式创建模式 [英] Pyspark: Create Schema from Json Schema involving Array columns
问题描述
我已经在json文件中为df定义了自己的架构,如下所示:
I have defined my schema for the df in a json file as follows:
{
"table1":{
"fields":[
{"metadata":{}, "name":"first_name", "type":"string", "nullable":false},
{"metadata":{}, "name":"last_name", "type":"string", "nullable":false},
{"metadata":{}, "name":"subjects", "type":"array","items":{"type":["string", "string"]}, "nullable":false},
{"metadata":{}, "name":"marks", "type":"array","items":{"type":["integer", "integer"]}, "nullable":false},
{"metadata":{}, "name":"dept", "type":"string", "nullable":false}
]
}
}
EG JSON数据:
EG JSON DATA:
{
"table1": [
{
"first_name":"john",
"last_name":"doe",
"subjects":["maths","science"],
"marks":[90,67],
"dept":"abc"
},
{
"first_name":"dan",
"last_name":"steyn",
"subjects":["maths","science"],
"marks":[90,67],
"dept":"abc"
},
{
"first_name":"rose",
"last_name":"wayne",
"subjects":["maths","science"],
"marks":[90,67],
"dept":"abc"
},
{
"first_name":"nat",
"last_name":"lee",
"subjects":["maths","science"],
"marks":[90,67],
"dept":"abc"
},
{
"first_name":"jim",
"last_name":"lim",
"subjects":["maths","science"],
"marks":[90,67],
"dept":"abc"
}
]
}
我想从此json文件创建等效的spark模式.以下是我的代码:(参考:从中创建spark数据帧架构json模式表示形式)
I want to create the equivalent spark schema from this json file. Below is my code: (reference: Create spark dataframe schema from json schema representation)
with open(schemaFile) as s:
schema = json.load(s)["table1"]
source_schema = StructType.fromJson(schema)
如果我没有任何数组列,则上面的代码可以正常工作.但是,如果我的架构中有数组列,则会引发以下错误.
The above code works fine if i dont have any array columns. But throws the below error if i have array columns in my schema.
无法解析数据类型:数组" (无法解析数据类型:%s" json_value)
"Could not parse datatype: array" ("Could not parse datatype: %s" json_value)
推荐答案
在您的情况下,数组的表示存在问题.正确的语法是:
In your case there was an issue with the representation of the arrays. The correct syntax is:
{ "metadata": {},
"name": "marks",
"nullable": true, "type": {"containsNull": true, "elementType": "long", "type": "array" } }
.
{ "metadata": {},
"name": "marks",
"nullable": true, "type": {"containsNull": true, "elementType": "long", "type": "array" } }
.
为了从json检索架构,您可以编写下一个pyspark代码段:
In order to retrieve the schema from json you can write the next pyspark snippet:
jsonData = """{
"table1": [{
"first_name": "john",
"last_name": "doe",
"subjects": ["maths", "science"],
"marks": [90, 67],
"dept": "abc"
},
{
"first_name": "dan",
"last_name": "steyn",
"subjects": ["maths", "science"],
"marks": [90, 67],
"dept": "abc"
},
{
"first_name": "rose",
"last_name": "wayne",
"subjects": ["maths", "science"],
"marks": [90, 67],
"dept": "abc"
},
{
"first_name": "nat",
"last_name": "lee",
"subjects": ["maths", "science"],
"marks": [90, 67],
"dept": "abc"
},
{
"first_name": "jim",
"last_name": "lim",
"subjects": ["maths", "science"],
"marks": [90, 67],
"dept": "abc"
}
]
}"""
df = spark.read.json(sc.parallelize([jsonData]))
df.schema.json()
这应该输出:
{
"fields": [{
"metadata": {},
"name": "table1",
"nullable": true,
"type": {
"containsNull": true,
"elementType": {
"fields": [{
"metadata": {},
"name": "dept",
"nullable": true,
"type": "string"
}, {
"metadata": {},
"name": "first_name",
"nullable": true,
"type": "string"
}, {
"metadata": {},
"name": "last_name",
"nullable": true,
"type": "string"
}, {
"metadata": {},
"name": "marks",
"nullable": true,
"type": {
"containsNull": true,
"elementType": "long",
"type": "array"
}
}, {
"metadata": {},
"name": "subjects",
"nullable": true,
"type": {
"containsNull": true,
"elementType": "string",
"type": "array"
}
}],
"type": "struct"
},
"type": "array"
}
}],
"type": "struct"
}
或者,您可以使用df.schema.simpleString()
这将返回相对简单的架构格式:
Alternatively, you could use df.schema.simpleString()
this will return an relatively simpler schema format:
struct<table1:array<struct<dept:string,first_name:string,last_name:string,marks:array<bigint>,subjects:array<string>>>>
最后,您可以将上面的架构存储到文件中,并在以后使用以下方式加载它:
Finally you can store the schema above into a file and load it later on with:
import json
new_schema = StructType.fromJson(json.loads(schema_json))
与您已经做过的一样. 记住,您也可以针对任何json数据动态地实现所描述的过程.
As you did already. Remember that you could achieve the described process dynamically as well for any json data.
这篇关于Pyspark:从涉及数组列的Json模式创建模式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!