CODEWITHSUNDEEP
google-bigquery
jamiet
jamiet

Reputation: 12234

How can we discover the cost of maintaining materialized views?

Materialized views are great however there is a cost associated with the jobs that maintain them. We want to create a materialized view over the top of a table into which we're streaming about 50million events per day and are worried about the cost implication of that materialized view.

How can we track the cost of maintaining those materialized views?

Upvotes: 0

Views: 726

Answers (2)

Kokizzu
Kokizzu

Reputation: 26818

I use this for past 7 days queries, but it seems it cost half of that

SELECT
  query
  , SUM(total_slot_ms) / (1000 * 60 * 60) AS slot_hours
  , SUM(total_bytes_processed) / 1000 / 1000 / 1000 AS gb_processed
  , (SUM(total_slot_ms) / (1000 * 60 * 60)) * 0.04 AS slot_hours_usd
  , (SUM(total_bytes_processed) / 1000 / 1000 / 1000 / 1000) * 6.25 AS tb_processed_usd
  , (SUM(total_slot_ms) / (1000 * 60 * 60)) * 0.04 + (SUM(total_bytes_processed) / 1000 / 1000 / 1000 / 1000) * 6.25 AS cost_usd 
  , ((SUM(total_slot_ms) / (1000 * 60 * 60)) * 0.04 + (SUM(total_bytes_processed) / 1000 / 1000 / 1000 / 1000) * 6.25) / COUNT(1) AS usd_per_query 
  , COUNT(1) AS query_count
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
  AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
  AND (total_slot_ms > 0 OR total_bytes_processed > 0)
GROUP BY 1
ORDER BY 6 DESC;

Upvotes: 0

Mr.Batra
Mr.Batra

Reputation: 833

Try this.

select  ref_tabls.table_id,jobs.*     
from    `project-id`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT jobs,
unnest(referenced_tables) as ref_tabls     
left join `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES tb     
on ref_tabls.table_id = tb.table_name     
where   tb.table_type = 'MATERIALIZED VIEW'

Upvotes: 2

Related Questions