Search code examples
viewgoogle-bigquerywildcard

Create a big query view - multiple shards

What is the cleanest solution in Big Query to create a view from multiple tables that share the same prefix and are located in the same dataset? I'd like to have the large table updated automatically every time a new {prefix}_'yyyy_mm_dd' table is added to the dataset.

Following the documentation:

The wildcard table functionality does not support views. If the wildcard table matches any view in the dataset, the query returns an error.

Is creating a new table (duplicating the data) the only option then?

Solution

  • A view can query several tables:

    create view Test.one
as
Select *
from `Test.oneM*`

    However, query several views is not allowed:

    Select *
from `Test.one*`