CODEWITHSUNDEEP
amazon-web-servicesamazon-redshiftaws-glueaws-glue-data-catalog
Tsimp. Dim
Tsimp. Dim

Reputation: 84

Redshift COPY and create table automatically?

Is there not a way to automatically create an internal table in Redshift and then move data into it with COPY? Can I not use the metadata stored on AWS Glue Data Catalog to create it?

Right now as I understand it, one has to manually write SQL to create a table and then run COPY to move data into the table.

Edit: My problem is the table creation. For me to run COPY a table must already exist. Can i create this table using the existing GLUE metadata? Or can i only write SQL by hand?

Upvotes: 1

Views: 3792

Answers (2)

kartik
kartik

Reputation: 2135

Automatic table creation can be achieved using the below workaround.

  1. Create table definition by running the Glue Crawler.

  2. Create an external database in redshift pointing to the glue database.

    CREATE EXTERNAL SCHEMA my_ext_db FROM DATA CATALOG DATABASE 'my_glue_db' IAM_ROLE 'arn:aws:iam:::role/my-redshift-role';

  3. Now create a redshift table using the below command

    CREATE TABLE my_internal_db.my_table AS Select * from my_ext_db.my_table;

  4. you can now run the copy into command on this empty table my_internal_db.my_table.

Upvotes: 1

Prabhakar Reddy
Prabhakar Reddy

Reputation: 5124

If you want to automate the data movement from Glue catalog to Redshift table you can use AWS Glue to achieve it. Please refer to this for more information.

Once you have your Glue jobs ready you can always schedule them to run at some point of time everyday.

Upvotes: 0

Related Questions