Pyspark SQL dataframe map with multiple data types

user_6637257 0
Viewed 914

I'm having a pyspark code in glue where I want to create a dataframe with map structure to be a combination of integer and string.

sample data:

{ "Candidates": [
    {
      "jobLevel": 6,
      "name": "Steven",
    },    {
      "jobLevel": 5,
      "name": "Abby",
    } ] }

Hence, I tried using the below code to create the map data type. But every time the integer data type jobLevel gets converted to string data type. Any suggestion to get this done by retaining the data type of the job level?

code used:

df = spark.sql("select Supervisor_name, 
           map('job_level', INT(job_level_name), 
          'name', employeeLogin) as Candidates 
     from dataset_1")
aws-glue-spark aws-glue pyspark apache-spark-sql dataframe
2 Answers

It is not possible for map values to have different types. Use a struct for this situation.

df = spark.sql("""
    select Supervisor_name, 
           struct(INT(job_level_name) as job_level, 
                  employeeLogin as name
                 ) as Candidates 
    from dataset_1
""")
user_141657300

I am new to pyspark :-). However, lets try parallelize and then define schema to desired;

js={ "Candidates": [
    {
      "jobLevel": 6,
      "name": "Steven",
    },    {
      "jobLevel": 5,
      "name": "Abby",
    } ] }



    from pyspark.sql.types import *
    df=sc.parallelize(js["Candidates"])
    schema = StructType([StructField('name', StringType(), True),
                         StructField('jobLevel', IntegerType(), True)])
    df1=spark.read.json(df, schema)
    df1.show(truncate=False)
    df1.printSchema()

I get:

+------+--------+
|name  |jobLevel|
+------+--------+
|Steven|6       |
|Abby  |5       |
+------+--------+

root
 |-- name: string (nullable = true)
 |-- jobLevel: integer (nullable = true)
user_89869750
Related