CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-bi-engine
Rua Col
Rua Col

Reputation: 11

Google BigQuery BI Engine monitoring and comparing

I have recently been asked to look into BI Engine for our BigQuery Tables and Views. I am trying to find out how to compare the speed of using BI Engine reservation against not using it.. any way i can see this? Thank you

Upvotes: 1

Views: 419

Answers (3)

Sander van den Oord
Sander van den Oord

Reputation: 12818

BI Engine will in general only speed up smaller SELECT queries coming from Tableau, Looker etc., and the UI. So for example queries processing < 16 GB.

My advice would be to make a reservation for example for 8GB and then check how long it took for queries that used BI Engine. You can do that by querying the information schema:

select 
    creation_time, 
    start_time,
    end_time,
    (unix_millis(end_time) - unix_millis(start_time)) / 1000 total_time_seconds,
    job_id, 
    cache_hit,
    bi_engine_statistics.bi_engine_mode,
    user_email,
    query,
from `your_project_id.region-eu.INFORMATION_SCHEMA.JOBS` 
where 
    creation_time >= '2022-12-13' -- partitioned on creation_time
    and creation_time < '2022-12-14' 
    and bi_engine_statistics.bi_engine_mode = 'FULL' -- BI Engine fully used for speed up
    and query not like '%INFORMATION_SCHEMA%'  -- BI Engine will not speed up these queries
order by creation_time desc, job_id

Then switch off BI Engine, and run the queries that had BI Engine mode = FULL again, but now without BI Engine. Also make sure cache is turned off!

You can now compare the speed. In general queries are 1.5 to 2 times faster. Although it can also happen that there is no speed up, or in some cases a query will take slightly longer.

See also:

Upvotes: 0

Alexey Klishin
Alexey Klishin

Reputation: 184

There are a couple of ways to do that:

1) If your table is less than 1Gb, it will use free tier. Then any dashboard created in Data Studio will be accelerated (see https://cloud.google.com/bi-engine/pricing).

2) If not, create reservation in pantheon: https://cloud.google.com/bi-engine/docs/reserving-capacity. Once you create reservation, Data Studio dashboards will be accelerated. You can experiment for couple of hours and remove reservation, and will only be charged for the time reservation was enabled.

Upvotes: 0

Enrique Zetina
Enrique Zetina

Reputation: 835

Keep in mind that BI Engine uses BigQuery as a backend, for that reason, the BI Engine reservations works like BigQuery reservations too, based on this, I suggest you look the Reservations docs to get more information about the differences between On-demand capacity and flat-rate pricing.

You can find useful concepts about reservations in this link.

Upvotes: 0

Related Questions