[Partner Block] Patched| google cloud billing by datatonic


This block has been patched to take into account the new sink steps and changes to the audit log tables (switched from sharded tables to partitioned tables).


The bigquery billing export is unchanged. If you have installed the block via the marketplace, the marketplace only includes the bigquery billing export, and therefore this update will not impact you (at time of this post).


If you have forked this block you will need to git pull the changes. If your audit log model is currently broken, these changes will fix the Lookml. However you will also need to set up a new data sink, the process is laid out in the Readme file under “Setting up BigQuery audit logs export”.


Link to the block on Lookers website:




Link to the source repo:



credit to @eliott for patching.


Note: the derived table in /bigquery_data_access.view.lkml appears not to be required anymore. This will be removed in the next patch once tested for breakages.


17 replies

Userlevel 6
Badge

This is amazing! Thank you for that. Now if we could have Looker cost to go with it, my life would be truly wonderful 😃

You can show what cost looker is driving in relation to Bigquery by looking at the service accounts used in the connections. If you mean licensing, you could just bring that via a derived table as its annual.

Userlevel 6
Badge

I did, I built something ad-hoc to calculate the cost but it would be amazing to have it out-of-the-box in Looker… just wishfully thinking out loud : )

Userlevel 6
Badge

Turns out I can’t even install it:


image



The fields for schema and table - is it what needs to exist or what will be created?

hey dawid.



You need to make sure you already have your GCP log sinks created before you try and install. check out our detailed readme on the source repo.



you then have two options for installing the repo, one is via the marketplace which you have tried above. The other is from our source repo (which is not the same as the marketplace), which has more detailed readme.



I would advise you use the 2nd link, which is the source repo and pull in the code directly. The readme has the instructions. This has both the GCP billing AND the bigquery audit logs in one (where as the marketplace has these split into two). You then can amend the code as you see fit.



In addition, the Google BigQuery Performance model in the marketplace has an issue if you land data partitioned. I’ve flagged it but we don’t maintain that, we rely on looker to patch the marketplace, based on our changes to the source repo which sits in our teamdatatonic space. Hopefully there will be a more robust system put in place for updating soon.

Userlevel 6
Badge

Actually we have everything setup already the logs, audit etc. but still getting the error when trying to install the block.



The table billing which is the equivalent of gcp_billing in your example is also available through the connection because even SQL Runner sees it



image



Unfortunately we’re not getting any meaningful errors here:



image

Userlevel 3

Hey Dawid! That does look like some funky behavior with the marketplace that we’ll want to get to the bottom of. I’d definitely recommend hopping on chat, or sending over a message via help.looker.com where we can help dig into this with you!

Userlevel 6
Badge

I managed to get it working but something worth noting is that filters get the timezone applied:



((TIMESTAMP(FORMAT_TIMESTAMP('%F %H:%M:%E*S', TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP(FORMAT_TIMESTAMP('%F %H:%M:%E*S', CURRENT_TIMESTAMP(), 'Europe/Madrid')), DAY), INTERVAL -29 DAY))



but the billing date doesn’t



EXTRACT(DATE from gcp_billing_export.usage_start_time) AS gcp_billing_export_billing_date,



Which will produce empty days:



image



Then I tried Table Calculation add_hours(2, ${gcp_billing_export.billing_date}) but it turns out the billing date is not a date but a string (?!) and I think you can’t do that in general because Table Calculation returns error

Userlevel 6
Badge

Another possible problem when getting totals:



A LookML model issue occurred.



Non-unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum.

hey dawid



See documentation on looker on this,because its a filter, we cannot use lookml to stop the conversion, the user or admin needs to make some changes:





Because filters always do time zone conversion, a difference between the database time zone and query time zone could cause data to unexpectedly be included or excluded from a dataset. To avoid this, ensure that the query time zone is set to the same value as the database time zone.



If User Specific Time Zones is enabled, set the time zone drop-down menu (located next to the Run button on Explores, Looks, and dashboards) to the same value as the database time zone. If User Specific Time Zones is disabled, set the Query Time Zone to the same value as the database time zone.





On the other issue, that is an error because there is a lack of primary key in one of the nested fields, annoyingly google doesn’t think about this because they are landing in BQ so they dont see a need for one, but looker needs one to compute asymmetric aggregate to deal with fanouts. If you let me know which fields you selected when that error occurred, i can figure out a way of creating one to fix it.

Userlevel 6
Badge

Hi @Jamie_Fry



It was Total Usage, which is calculated in seconds and probably causes some arithmetic overflow, I presume?



I guess we can’t change any LookML for that block because it’s managed externally, correct?

Hey Dawid,



Did you install via marketplace or did you go to our repo direct? If you pulled the code from our repo you can change anything you want. Via the marketplace, There might be a project that was created which allows you to reference the objects, you could then use a refinement to change ( i am not sure because i don’t set up the marketplace).



You can also push a change request to our https://github.com/teamdatatonic/looker-gcp-control repo


for me to review and approach. Looker then update the marketplace repo when they see changes from this one

Userlevel 6
Badge

Didn’t know you could install it from the Repo directly! That’s all right I will take a look and if I have any changes worth adding I’ll open a PR. Cheers!

Userlevel 6
Badge

@Jamie_Fry Do I need to create some kind of max_cache_age for the views? It seems like data is not being updated from the exact moment when I installed the block.

Update:



New fields added to gcp_billing_export table, now referred to as “daily cost detail” table in google documentation. These are in Beta and as such we have no plans to add these to the current block until they are out of Beta.



There is also a new billing table that has landed. This is for cases where you and google have negotiated discounts. We have no plans to add this to the existing block. Should you wish too, please feel free to contribute to the source repo here: https://github.com/teamdatatonic/looker-gcp-control



See https://cloud.google.com/billing/docs/how-to/export-data-bigquery-tables#data-schema for more info

Userlevel 6
Badge

Hi @Jamie_Fry 

 

I’ve been getting this errors for a while, any chance you could help?

 

A LookML model issue occurred.

Variable not found "link". (In field "gcp_billing_export.total_cost")

marketplace_datatonic_gcp_billing/gcp_billing_export_core.view.lkml:245

 

 

On a different topic - is there a way for me to change the project name (or at least what’s displayed in the develop menu)? I’ve tried in all the usual places but when I save it seems to just generate a new version of the project with the same name and the old one with the new name starts throwing errors. 

 

Thanks!

Reply