CODEWITHSUNDEEP
google-bigquery
Anthony Liu
Anthony Liu

Reputation: 373

How to allow join culling big query view?

I want to ensure when a view is used in google big query joins to table where a columns is not used is culled/excluded.

eg.

if the view is

Select  _fact.A
, _Dim.B
from _fact
inner join _dim
on _fact.dim_sk = _dim.Dim_sk

and I query

Select _fact.A 
from _view

It will ignore the join in the _dim

In SQL Server if you create Foregin Key relationship it will do this.

Upvotes: 0

Views: 104

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

BigQuery does not have the concept of primary keys, so there is no way for it to preform such an optimization; it does not know in advance that all keys in _dim are unique. You could create a view of this form, however:

#standardSQL
SELECT
  A,
  (SELECT B FROM _dim
   WHERE _dim.dim_sk = _fact.dim_sk) AS B
FROM _fact;

When you select only A from this view, the query should not end up scanning any columns from _dim. You will need to ensure that there are no duplicate values of dim_sk in either table or else you will get an error that the scalar subquery produced more than one element.

Upvotes: 1

Related Questions