Creating a BigQuery Transfer Service with more complex reges

0
votes

I have a bucket that stores files based on a transaction time into a filename structure like

gs://my-bucket/YYYY/MM/DD/[autogeneratedID].parquet

Lets assume this structure dates back to 2015/01/01

Some of the files might arrive late, so in theory a new file could be written to the 2020/07/27 structure tomorrow.

I now want to create a BigQuery table that inserts all files with transaction date 2019-07-01 and newer.

My current strategy is to slice the past into small enough chunks to just run batch loads, e.g. by month. Then I want to create a transfer service that listens for all new files coming in. I cannot just point it to gs://my-bucket/* as this would try to load the date prior to 2019-07-01.

So basically I was thinking about encoding the "future looking file name structures" into a suitable regex, but it seems like the wildcard names https://cloud.google.com/storage/docs/gsutil/addlhelp/WildcardNames only allow for very limited syntax which is not as flexible as awk regex for instance.

I know there are streaming inserts into BQ but still hoping to avoid that extra complexity and just make a smart configuration of the transfer config following the batch load.

1
regexgoogle-cloud-platformgoogle-bigquery
I cannot follow. There are certainly many ways, e.g. materialized views, automatic date-based partitions, and sure, streaming data. - wp78de
Reading the docs, they only offer recursive ** wildcards. That is, it looks like you need to do multiple requests, such as: gs://my-bucket/2019/07/**, gs://my-bucket/2019/08/**, gs://my-bucket/2019/09/**, gs://my-bucket/2019/1**, gs://my-bucket/2020/**. - Peter Thoeny
Thanks Peter! This will probably be the easiest workaround if we want to avoid streaming inserts, even though I somehow dislike the idea as it looks a bit duct-taped. But fair enough, these configs can still be auto-generated with some simple bash magic, so at least it would be repeatable / paramterized. - Ragastra

1 Answers

0
votes

You can use scheduled queries with external table. When you query your external table, you can use the pseudo column _FILE_NAME in the where condition of your request and filter on this.