1

I have a file structure such as:

gs://BUCKET/Name/YYYY/MM/DD/Filename.csv

Every day my cloud functions are creating another path with another file innit corresponding to the date of the day (so for today's 5th of August) we would have gs://BUCKET/Name/2022/08/05/Filename.csv

I need to find a way to query this data to Big Query automatically so that if I want to query it for 'manual inspection' I can select for example data from all 3 months in one query doing CREATE TABLE with gs://BUCKET/Name/2022/{06,07,08}/*/*.csv

How can I replicate this? I know that BigQuery does not support more than 1 wildcard, but maybe there is a way to do so.

Martin Walczyński
  • 83
  • 2
  • 10
  • see https://stackoverflow.com/questions/64217474/bigquery-create-external-table and you can specify multiple buckets in options like **uris=[gs://BUCKET/Name/2022/06/*/*.csv, gs://BUCKET/Name/2022/07/*/*.csv, ...]** – Jaytiger Aug 05 '22 at 12:16

2 Answers2

0

To query data inside GCS from Big Query you can use an external table.

Problem is this will fail because you cannot have a comma (,) as part of the URI list

CREATE EXTERNAL TABLE `bigquerydevel201912.foobar` 
OPTIONS (
  format='CSV',
  uris = ['gs://bucket/2022/{1,2,3}/data.csv']
)

You have to specify the 3 CSV file locations like this:

CREATE EXTERNAL TABLE `bigquerydevel201912.foobar` 
OPTIONS (
  format='CSV',
  uris = [
    'gs://inigo-test1/2022/1/data.csv',
    'gs://inigo-test1/2022/2/data.csv']
    'gs://inigo-test1/2022/3/data.csv']
)

Since you're using this sporadically, probably makes more sense to create a temporal external table.

Iñigo González
  • 3,735
  • 1
  • 11
  • 27
0

se I found a solution that works at least for my use case, without using the external table.

During the creation of table in dataset in BigQuery use create table from: GCS and then when using URI pattern I used gs://BUCKET/Name/2022/* ; As long as filename is the same in each subfolder and schema is identical, then BQ will load everything and then you can perform date operations directly in BQ (I have a column with ingestion date)

Martin Walczyński
  • 83
  • 2
  • 10