CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-redshiftamazon-athena
tikirin tukurun
tikirin tukurun

Reputation: 141

How to directly copy Amazon Athena tables into Amazon Redshift?

I have some JSON files in S3 and I was able to create databases and tables in Amazon Athena from those data files. It's done, my next target is to copy those created tables into Amazon Redshift. There are other tables in the Amazon Athena which I created base on those data files. I mean I created three tables using those data files which is in the S3, latter I created new tables using those those 3 tables. So at the moment I have 5 different tables which want to create in the Amazon Redshift with data or without data.

I checked the COPY command in Amazon Redshift, but there is no COPY command for Amazon Athena. Here are the available list.

If there is no any other solutions, I planned to create new JSON files based on newly created tables in the Amazon Athena into S3 buckets. Then we can easily copy those from S3 into the Redshift, isn't it? Are there any other good solutions for this?

Upvotes: 3

Views: 10875

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

If your s3 files are in an OK format you can use Redshift Spectrum.

1) Set up a hive metadata catalog of your s3 files, using aws glue if you wish.

2) Set up Redshift Spectrum to see that data inside redshift (https://docs.aws.amazon.com/redshift/latest/dg/c-getting-started-using-spectrum.html)

3) Use CTAS to create a copy inside redshift

create table redshift_table as select * from redshift_spectrum_schema.redshift_spectrum_table;

Upvotes: 8

Related Questions