Question 18057 in Twillio Question posted in
The official Twillio API documentation can be found here.

Exclude views from a table_suffix query – Twillio

I am using Twillio Segment to sync web_app_ui events into bigquery (serves as data lake).
In Bigquery data we have many tables with similar structure. Most fields are common, but some are different.
We have many such tables (~200), and many are added regularly.
Each table has a view with similar structure aimed at records deduplication and performance optimization.
So I have a table: mytable, and next to it mytable_view

I would like to query all these tables together using Table_Suffix wildcard functionality. However… since eache table has a view that matches the wildcard, I get the error message: Views cannot be queried through prefix.

I am looking for an idea for going around this limitation. And querying all tables in one query, while ignoring all views. or in other words, how to make this work?

Select a,b,c
from `my_dataset`.*
where _TABLE_SUFFIX not like "%_view'
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As mentioned above, table wildcard won't work if it would match both views and tables. Eventually I went for the solution of creating a view using dynamic SQL. I use this code:

    EXECUTE IMMEDIATE (
with 
tbls as 
(
Select table_name 
from `<myproject>.<mydataset>.INFORMATION_SCHEMA.TABLES`
where table_name not like '%_view' )

SELECT 
" CREATE VIEW `<myproject>.<mydataset>.<myview>` AS "||
STRING_AGG(
"SELECT a FROM `<myproject>.<mydataset>."||table_name||"`",
" UNION ALL ")
From tbls);

    (simplified version)

    This is scheduled to run daily. It creates a view that does Union All of all relevant tables. One can also pick relevant fields in similar manner from INFORMATION_SCHEMA.COLUMNS.


  2. 0

    According to 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. This is true whether or not your query contains a WHERE clause on the _TABLE_SUFFIX pseudo column to filter out the view.

    https://cloud.google.com/bigquery/docs/querying-wildcard-tables#limitations

    So, the only option you have is to isolate the views on a different dataset or project given that any _TABLE_SUFFIX will not be evaluated if there is any view matched by the wildcard in the first place.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search