title: unioning tables from ec2 with aws glue
body: <p>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.</p>
  
  <p>script:</p>
  
  <pre><code>create external schema schema1 from data catalog 
  database ‘db1’ 
  iam_role 'arn:aws:iam::228276743211:role/madeup’
  region 'us-west-2';
  </code></pre>