I am trying to push data frame with varying number of columns to aws redshift.

this is the data frame header :

col1    col2    col3

I have created a temp table using something like this :

DROP TBALE TEMP;
CREATE TABLE temp (
    col1 int,
    col2 int,
    col3 int
);

but now the data frame has two new columns and the number of columns keeping changing.

How to drop create this table temp based on changing data frame columns

col1    col2    col3   col4    col5

any way to tackle this in one shot or do i keep editing ddl every time data is read

CodePudding user response：

Assuming that you are loading the data via a COPY command from S3, maybe you can try creating a table with the maximum number of columns you expect to receive in the CSV files, and then use the flag FILLRECORD (https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html#copy-fillrecord)

In this way if the file contains less columns, the other columns will have NULL values. Example, if your file has 3 columns, but the TEMP table has 5: