How do I insert data in selective columns using PySpark?

user_15948815 0
Viewed 419

I have a table on Redshift, to which I want to insert some data using pyspark dataframe.
The redshift table has schema:

CREATE TABLE admin.audit_of_all_tables
(
    wh_table_name varchar,
    wh_schema_name varchar,
    wh_population_method integer,
    wh_audit_date timestamp without time,
    wh_percent_change numeric(15,5),
    wh_s3_path varchar
)
DISTSTYLE AUTO;

In my dataframe, I want to keep values for only the first 4 columns and write that dataframe's data to this table.
My dataframe is something like this: enter image description here

Now, I want to do df.write.format to my table on Redshift, but I need to somehow specify that I want to insert data to only the first four columns and pass no value for the last 2 columns (keeping them null by default).
Any idea how to specify this using dataframe.write.format (or any method).
Thanks for reading.

amazon-redshift pyspark apache-spark-sql dataframe python
1 Answers

You can use selectExpr to select the first four columns plus two additional columns with null that have been cast to the required type:

df2 = df.selectExpr("table_name as wh_table_name",
    "schema_name as wh_schema_name",
    "population_method as wh_population_method",
    "audit_date as wh_audit_date",
    "cast(null as double) as wh_percent_change",
    "cast(null as string) as wh_s3_path")

df2.write....
user_21298010
Related