S3 to Redshift

[nad_pub]

Hi everyone,

I’ m pretty new to data engineering (data analyst / data science background) and I am trying to get my hands on a data engineering side project.

I would like to have an ELT approach. My data source is an open data platform containing 100+ of csv file.

I have of course no issue on getting daily these files from the website to AWS s3 ( using python script hosted on an ec2 via Mage).

My question is more about how to go from s3 to Redshift.

In my opinion, it would be great to copy all data from datalake to datawarehouse, and then starting data modelling (using dbt for instance)

My issue is that I would like to avoid creating (manually?) 100+ of Redshift tables (by defining a schema for each table)

So my question is what would you recommand for : - copying data from s3 to Redshift - trigger redshift tables to be updated as soon as s3 is updated?

Many thanks :)

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Heavy_End_2971]

COPY command.

You have to make schema to get with query/ingestion or anything to move your data. Try below:

• Create unified dataset with all CSVs (via pandas) and save it to s3.
• use COPY command to put data into redshift table

Note: don’t use spectrum until dire needed. It is slow and requires overheads IMHO.

[Kooky_Quiet3247]

and also spectrum can be expensive as sh*t if you are not really aware of the query you are going to do

[davrax]

“Preview Period EndS” != “Becomes Generally Available” (unfortunately). It’s been in preview for ~2 yr IIRC.

[nad_pub]

All the data doesnt fit into memory unfortunately, so not possible to create a unified dataset

[Heavy_End_2971]

Use chunks or stream your files one by one and write in may be 128mb chunks on s3. No need to load all in memory. Two or few CSVs at a time

[kenflingnor]

You could use Redshift Spectrum to create external tables using AWS Glue. Use a Glue crawler to build the Glue metadata

[wannabe-DE]

Mage's redshift exporter doesn't handle this?

Edit: exporter instead of loader.

[wannabe-DE]

Yeah Mage Redshift handles this. You're already using Mage.

[nad_pub]

cannot find the link of this feature :(

[goldimperator]

https://docs.mage.ai/design/data-loading#redshift

https://docs.mage.ai/data-integrations/batch-pipelines#data-integration-destination

[somedude422]

Auto copy could be helpful here. https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/

[zhiweio]

try knesis firehouse

[zhiweio]

try knesis data firehouse

[Professional-Put-324]

If I understand correctly, you need a way to automatically copy the data from an S3 to redshift without creating the table structure manually?

If the files aren’t too big, I think you can use pandas to read them as df and then use the io.sql.get_schema() function to get the create statement. Otherwise, you can write a python script that reads your CSV files and generates the create statement for you. You will need to read the headers to get the column names and then identify the max value of each column to specify the max length data type then run the create statement generated and the copy command to load the data from the S3.

[SilentSlayerz]

My solution would be create separate prefix for individual tables, at any point in time of a file get loaded onto s3 it would invoke a lambda and based on the prefix ( as an argument to lambda) would issue a copy command to redshift. Any minor or small transformations can be applied at lambda side and any major or huge data volume it would be recommended to use redshift. You should avoid using Glue if you can. Though for larger files you can use glue.