Q51: How will you read the below JSON file employee.json into a Pyspark Dataframe?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
|
{ "company_name": "XYZ Org", "company_id": 123, "executive_name": "Jonathan", "employees": [ { "emp_id": 1, "emp_name": "John", "emp_city": "Sydney", "emp_salary": 35000 }, { "emp_id": 2, "emp_name": "Peter", "emp_city": "Melbourne", "emp_salary": 45000 }, { "emp_id": 3, "emp_name": "Sam", "emp_city": "Sydney", "emp_salary": 55000 }, { "emp_id": 4, "emp_name": "Danny", "emp_city": "Melbourne", "emp_salary": 45000 }, { "emp_id": 7, "emp_city": "Sydney", "emp_salary": 35000 }, { "emp_id": 8, "emp_name": "Lisa", "emp_city": "Sydney", "emp_salary": 25000 } ] } |
A51: Here is the PySpark code with schema to read the above JSON file. read_json_spark.py: Read with a static schema
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
|
from pyspark.sql import SparkSession import pyspark.sql.functions as F from pyspark.sql.types import * from pyspark import sql def read_file(spark): # defne the schema schema = StructType([StructField('company_name', StringType(), False), StructField('company_id', IntegerType(), False), StructField('executive_name', StringType(), False), StructField('employees', ArrayType(StructType([ \ StructField('emp_id', StringType(), False), \ StructField('emp_name', StringType(), False),\ StructField('emp_city', StringType(), True),\ StructField('emp_salary', DoubleType(), True)])))]) # read a json file emp_df = spark.read.format("json")\ .option("multiline", True)\ .schema(schema)\ .load("/tmp/employee.json") emp_df.printSchema() emp_df.show(20, False) if __name__ == '__main__': spark = SparkSession.builder.appName("Spark read a JSON file")\ .master("local[*]")\ .getOrCreate() read_file(spark) |
Run As:
|
|
$ spark-submit read_json_spark.py |
Outputs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
|
...... |-- company_name: string (nullable = true) |-- company_id: integer (nullable = true) |-- executive_name: string (nullable = true) |-- employees: array (nullable = true) | |-- element: struct (containsNull = true) | | |-- emp_id: string (nullable = true) | | |-- emp_name: string (nullable = true) | | |-- emp_city: string (nullable = true) | | |-- emp_salary: double (nullable = true) ...... +------------+----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |company_name|company_id|executive_name|employees | +------------+----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |XYZ Org |123 |Jonathan |[{1, John, Sydney, 35000.0}, {2, Peter, Melbourne, 45000.0}, {3, Sam, Sydney, 55000.0}, {4, Danny, Melbourne, 45000.0}, {7, NULL, Sydney, 35000.0}, {8, Lisa, Sydney, 25000.0}]| +------------+----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ...... |
Explode the array Q52: How will you explode the...