Using the Athena Batch Partition Insert Job Type

So we’ve got our raw data in s3:

And we have an Athena table over this data:

The Athena query statement used to create this table:

CREATE EXTERNAL TABLE invoices (
invoiceid int,
customerid int,
billtocustomerid int,
orderid int,
deliverymethodid int,
contactpersonid int,
accountspersonid int,
salespersonpersonid int,
packedbypersonid int,
invoicedate date,
customerpurchaseordernumber string,
iscreditnote int,
totaldryitems int,
totalchilleritems int,
deliveryrun string,
runposition string,
confirmeddeliverytime timestamp,
confirmedreceivedby string,
lasteditedby int,
lasteditedwhen timestamp)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://rockstaretltesting/raw_data/invoices2'
TBLPROPERTIES (
'skip.header.line.count'='1')

Then we create the staging table:

CREATE table staging.invoices_last
WITH (format='PARQUET', parquet_compression='SNAPPY', partitioned_by=array['year','month','day','lasteditedby'], external_location = 's3://rockstaretltesting/staging/invoices_last/') AS
SELECT invoiceid,
billtocustomerid,
orderid,
deliverymethodid,
contactpersonid,
accountspersonid,
salespersonpersonid,
packedbypersonid,
invoicedate,
customerpurchaseordernumber,
iscreditnote,
totaldryitems,
totalchilleritems,
deliveryrun,
runposition,
confirmeddeliverytime,
confirmedreceivedby,
substr(cast("date_parse"("substr"(NULLIF(cast(lasteditedwhen AS varchar(10)),
''), 1, 10), '%Y-%m-%d') AS varchar(10)),1,4) AS year ,substr(cast("date_parse"("substr"(NULLIF(cast(lasteditedwhen AS varchar(10)), ''), 1, 10), '%Y-%m-%d') AS varchar(10)),6,2) AS month ,substr(cast("date_parse"("substr"(NULLIF(cast(lasteditedwhen AS varchar(10)), ''), 1, 10), '%Y-%m-%d') AS varchar(10)),9,2) AS day ,lasteditedby
FROM raw_sales.invoices
WHERE lasteditedby = 20

Now that the table is created, we can create the insert statement:

insert into staging.invoices_last (invoiceid, billtocustomerid, orderid, deliverymethodid, contactpersonid, accountspersonid, salespersonpersonid, packedbypersonid, invoicedate, customerpurchaseordernumber, iscreditnote, totaldryitems, totalchilleritems, deliveryrun, runposition, confirmeddeliverytime, confirmedreceivedby, year, month,day,lasteditedby)
SELECT invoiceid,
billtocustomerid,
orderid,
deliverymethodid,
contactpersonid,
accountspersonid,
salespersonpersonid,
packedbypersonid,
invoicedate,
customerpurchaseordernumber,
iscreditnote,
totaldryitems,
totalchilleritems,
deliveryrun,
runposition,
confirmeddeliverytime,
confirmedreceivedby,
substr(cast("date_parse"("substr"(NULLIF(cast(LastEditedWhen AS varchar(10)),
''), 1, 10), '%Y-%m-%d') AS varchar(10)),1,4) AS year ,substr(cast("date_parse"("substr"(NULLIF(cast(LastEditedWhen AS varchar(10)), ''), 1, 10), '%Y-%m-%d') AS varchar(10)),6,2) AS month ,substr(cast("date_parse"("substr"(NULLIF(cast(LastEditedWhen AS varchar(10)), ''), 1, 10), '%Y-%m-%d') AS varchar(10)),9,2) AS day ,lasteditedby
FROM raw_sales.invoices

After copying and pasting into a text file:

We copy it to s3: