How to copy AWS Glue table structure to AWS Redshift

Question

I created new Database and the Table structure using AWS Glue without using crawler and can do the same thing, I mean create the table structure using crawler. That's not the problem, what I want is to create the same table structure in AWS Redshift based on AWS Glue table metadata.

Manually I did it with Django with Python, I get the meta data of the table and create the "CREATE TABLE ..." command and execute it. It works, I have this alternative solution already. Can we do this from AWS side or using AWS SDKs such as Boto3? I don't need any of the data inside table, just want to create the empty table in the AWS Redshift. Is this possible?

I also checked the AWS Redshift Spectrum. If I can create this table in AWS Redshift, then using spectrum command I can fetch data from S3 or any other resources. So do that I need the Table first.

Have you tried creating external schema in redshift spectrum as in docs.amazonaws.cn/en_us/redshift/latest/dg/… . When you create a table in Glue it will automatically become available in redshift — Prabhakar Reddy

Alexis Facques Alexis Facques · Accepted Answer · 2020-03-12T08:27:25

Given you populated your Glue table with the proper schema, and all its partitions, you should be able to run queries on it with Redshift Spectrum without having to create an actual table with the CREATE TABLE... statement.

From your RedShift client/editor, create an external (Spectrum) schema pointing to your data catalog database containing your Glue tables (here, named spectrum_db). iam_role value should be the ARN of your Redshift cluster IAM role, to which you would have added the glue:GetTable action policy.

create external schema spectrum_schema from data catalog 
database 'spectrum_db' 
iam_role 'arn:aws:iam::123456789012:role/MySpectrumRole'
create external database if not exists;

You should now be able to run queries over your external Glue tables. Only constraint doing so is that your can't SELECT * over your tables:

SELECT ... FROM spectrum_schema.Your_table

From there you should be able to move data from Spectrum to Standard Redshift more easily.

Reference(s):

Creating External Schemas for Amazon Redshift Spectrum

How to copy AWS Glue table structure to AWS Redshift

1 Answers