title: Athena Query taking very long
body: <p>I have a large Textfile file which is pipe delimited with probably over million records. I am copying it into s3 and then loading it into athena:</p>
  <pre><code>CREATE EXTERNAL TABLE IF NOT EXISTS main
                        ( `pin` string,
                          `service_location_no` string,
                          `practice_type` string)
                        ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
                        WITH SERDEPROPERTIES (
                          'serialization.format' = '|',
                          'field.delim' = '|',
                          'collection.delim' = 'undefined',
                          'mapkey.delim' = 'undefined'
                        ) LOCATION 's3://file_bucket/ci/main_file/main/'
                        TBLPROPERTIES ('has_encrypted_data'='false');
  </code></pre>
  <p>I have another smaller file (same format ) and load it into athena</p>
  <pre><code> CREATE EXTERNAL TABLE IF NOT EXISTS static
                           ( `pin` string,
                              `service_location_no` string,
                              `practice_type` string)
                        ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
                        WITH SERDEPROPERTIES (
                          'serialization.format' = '|',
                          'field.delim' = '|',
                          'collection.delim' = 'undefined',
                          'mapkey.delim' = 'undefined'
                        ) LOCATION 's3://file_bucket/ci/static_file/static/'
                        TBLPROPERTIES ('has_encrypted_data'='false');
    
  </code></pre>
  <p>So i now have 2 tables main and static. Both have same columns.
  My goal is to create a 3 rd table which contains records from main and static with below query.</p>
  <pre><code>   CREATE TABLE final
                          WITH (
                            format = 'TEXTFILE',
                            external_location = 's3://file_bucket/ci/final_file/final/',
                            field_delimiter = '|',
                            bucketed_by = ARRAY['pin'],
                            bucket_count = 1
                          ) AS
                          SELECT   *
                  FROM     main m
                  WHERE   pin NOT IN (SELECT  DISTINCT pin FROM static)
                  UNION
                  SELECT  * FROM static
  </code></pre>
  <p>This is taking over 2 hours and it times out. I am trying to see if it can be accomplished in a more efficient way.</p>