CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerymaterialized-views
ProGirlXOXO
ProGirlXOXO

Reputation: 2300

Add Bogus Aggregate for Materialized View?

In BigQuery, is it possible to create a bogus aggregate such that I can use a materialized view to query a table?

The query:

SELECT
  date_of_data,
  id,
  endpoint,
  value
FROM
  `project.dataset.table`
WHERE
  endpoint='some_endpoint'

If I try to make a materialized view out of that I get Materialized Views must contain an aggregator.

I want to use a materialized view because the historical data does not change and filtering by endpoint creates a much smaller table to query against later on.

Upvotes: 1

Views: 209

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10152

Probably you can try any aggregate function and list all available columns in the GROUP BY statement:

SELECT
  date_of_data,
  id,
  endpoint,
  value,
  count(*) as cnt
FROM
  `project.dataset.table`
WHERE
  endpoint='some_endpoint'
GROUP BY 1,2,3,4

Upvotes: 1

Related Questions