CODEWITHSUNDEEP
amazon-s3amazon-ec2amazon-redshiftaws-glueaws-glue-data-catalog
user3476463
user3476463

Reputation: 4575

unioning tables from ec2 with aws glue

I have two mysql databases each on their own ec2 instance. Each database has a table ‘report’ under a schema ‘product’. I use a crawler to get the table schemas into the aws glue data catalog in a database called db1. Then I’m using aws glue to copy the tables from the ec2 instances into an s3 bucket. Then I’m querying the tables with redshift. I get the external schema in to redshift from the aws crawler using the script below in query editor. I would like to union the two tables together in to one table and add a column ’source’ with a flag to indicate the original table each record came from. Does anyone know if it’s possible to do that with aws glue during the etl process? Or can you suggest another solution? I know I could just union them with sql in redshift but my end goal is to create an etl pipeline that does that before it gets to redshift.

script:

create external schema schema1 from data catalog 
database ‘db1’ 
iam_role 'arn:aws:iam::228276743211:role/madeup’
region 'us-west-2';

Upvotes: 0

Views: 640

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

You can create a view that unions the 2 tables using Athena, then that view will be available in Redshift Spectrum.

CREATE OR REPLACE VIEW db1.combined_view AS
SELECT col1,cole2,col3 from db1.mysql_table_1
union all 
SELECT col1,cole2,col3 from db1.mysql_table_2
;

run the above using Athena (not Redshift)

Upvotes: 1

Related Questions