Recently I thought I’d look at BigQuery’s new row-level security feature: https://cloud.google.com/bigquery/docs/row-level-security-intro For a moment I was excited because I thought I could remove the view scenario I’m using to manage row-level access at the moment. Briefly, the fact table is joined to another table on the relevant key and each user’s email address is …
Google Cloud recently changed their authorized views functionality in the console. And they broke it! You can no longer authorize views across projects using the console! It seems some of their documentation hasn’t been able to keep up either! https://cloud.google.com/bigquery/docs/authorized-views#console Fortunately some of their other documentation is up to date (but there’s a catch): https://cloud.google.com/bigquery/docs/share-access-views#authorize_the_view_to_access_the_source_dataset …
How to authorize BigQuery views across projects using the Java Client Library Read More »
There are a few limitations with the free editions: When you add a job: In the paid version, you can add 1000 steps. In the free edition, each pipeline can only be run once per day: In the paid version, you can run pipelines every minute. In the free edition, the system will only run …
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 …