In Amazon Athena: First we need to get the schema of our source table or query. I’m doing a select * from my source table so I can use: show create table headeraltpartitions4; We need the schema to create the table in Google BigQuery (if it doesn’t exist already) In Google BigQuery, we’ll modify the …
How to stream into Google BigQuery from an Amazon Athena query Read More »
Let’s say we want to update the data in our Athena table. We know we can’t do this directly using Athena as update and delete statements are not supported. However we can achieve this indirectly by removing the data in S3 then running an Athena insert query. Starting off, let’s inspect our table in Athena …
How to add data to missing partitions using an Athena Query Read More »
This approach involves using a S3 Copy Objects job type to copy the S3 Objects to a new location. Thereafter we’ll create an Athena table pointing to the data in this new location. We can then profile the data using Athena queries to ensure it agrees. Leaving the “Sub Folder Names” blank means all the …
Let’s try an alternative choice of partitioning columns. Profiling your data is so important! If we were to partition by column f3, there would be 197 partitions: select count(1) as numrowsfrom (select f3, count(1) as numrowsfrom headersplitcompressedgroup by f3); If we include the date column f6 as well, this swells to 16,291 partitions! If we …
Multiple Partitioning columns with Athena Batch Partitioning Insert Job v2 Read More »
Say you want to change a table ie recreate it or re-populate the underlying data. Since Athena doesn’t provide the ability to delete rows, you’re options are to recreate the table using an Athena statement or stick with the original table and perform an S3 copy or delete operation: So let’s look at just deleting …
This time let’s create a partitioned table by multiple partitioning columns. We’ll use f3 and f6. For f6 which is a date string we’ll use a formula to get year, month and day: Let’s profile the data first: select F3, F6,substr(cast(date_parse(substr(NULLIF(cast(F6 AS varchar(10)),”), 1, 10), ‘%Y-%m-%d’) AS varchar(10)),1,4) AS year ,substr(cast(date_parse(substr(NULLIF(cast(F6 AS varchar(10)), ”), 1, …
Multiple Partitioning columns with Athena Batch Partitioning Insert Job Read More »
So we have some sample data from AWS Data Exchange: https://console.aws.amazon.com/dataexchange/home?region=us-east-1#/products/prodview-stk4wn3mbhx24 And we’ve subscribed and downloaded it to our S3 bucket: s3://rockstaretltesting1/sample_data/shipping/d0e9bd6148e8f14889980954017b0927/83bdd5c3225a70b48acff63e86c74193/ams/2020/202009291500/ So the first thing we have to do is decide which file we’re going to use and copy it to it’s own folder. This is because we can’t create an Athena table pointing …