Copy parquet file content into an SQL temp table and include partition key as column

Question

I have multiple parquet files in S3 that are partitioned by date, like so: s3://mybucket/myfolder/date=2022-01-01/file.parquet s3://mybucket/myfolder/date=2022-01-02/file.parquet and so on.

All of the files follow the same schema, except some which is why I am using the FILLRECORD (to fill the files with NULL values in case a column is not present). Now I want to load the content of all these files into an SQL temp table in redshift, like so:

DROP TABLE IF EXISTS table;
CREATE TEMP TABLE table 
(
var1 bigint,            
var2 bigint,        
date timestamp
);


COPY table
FROM 's3://mybucket/myfolder/'
access_key_id 'id'secret_access_key 'key'
PARQUET FILLRECORD;

The problem is that the date column is not a column in the parquet files which is why the date column in the resulting table is NULL. I am trying to find a way to use the date to be inserted into the temp table.

Is there any way to do this?

Answer 1

I believe there are only 2 approaches to this:

1. Perform N COPY commands, one per S3 partition value, and populate the date column with the same information as the partition key value as a literal. A simple script can issue the SQL to Redshift. The issue with this is that you are issuing many COPY commands and if each partition in S3 has only 1 parquet file (or a few files) this will not take advantage of Redshift's parallelism.
2. Define the region of S3 with the partitioned parquet files as a Redshift partitioned external table and then INSERT INTO (SELECT * from );. The external table knows about the partition key and can insert this information into the local table. The down side is that you need to define the external schema and table and if this is a one time process, you will want to then tear these down after.

There are some other ways to attack this but none that are worth the effort or will be very slow.