Clean up slo report (BQ + Datastudio) docs & create TF example

[gplasky]

https://github.com/google/slo-generator/blob/master/docs/deploy/datastudio_slo_report.md

• BQ view doesn't work without Standard SQL mode (bq query --nouse_legacy_sql)
• Nit: Fix UUOC with $(<file)

Also create a separate PR in https://github.com/terraform-google-modules/terraform-google-slo with an example of how to deploy the BQ dataset & view and link here.

[tuananhnguyen-ct]

Regarding the view, should we create it optionally in https://github.com/google/slo-generator/blob/master/slo_generator/exporters/bigquery.py?

[lvaylet]

Hi @gplasky, @tuananhnguyen-ct,

I will review the deploy instructions, create a sample BigQuery report to confirm they are still up-to-date, then advise on the next steps and get back to you.

[lvaylet]

I just confirmed the BQ view cannot be created with the documented query:

$ cat create_view.sql
CREATE VIEW `slo-generator-demo.slo_generator.last_report` AS
SELECT
   r2.*
FROM
   (
      SELECT
         r.service_name,
         r.feature_name,
         r.slo_name,
         r.window,
         MAX(r.timestamp_human) AS timestamp_human
      FROM
         `slo-generator-demo.slo_generator.custom_metrics` AS r
      GROUP BY
         r.service_name,
         r.feature_name,
         r.slo_name,
         r.window
      ORDER BY
         r.window
   )
   AS latest_report
   INNER JOIN
      `slo-generator-demo.slo_generator.custom_metrics` AS r2
      ON r2.service_name = latest_report.service_name
      AND r2.feature_name = latest_report.feature_name
      AND r2.slo_name = latest_report.slo_name
      AND r2.window = latest_report.window
      AND r2.timestamp_human = latest_report.timestamp_human
ORDER BY
   r2.service_name,
   r2.feature_name,
   r2.slo_name,
   r2.error_budget_policy_step_name
$ bq query `cat create_view.sql`
Error in query string: Error processing job 'slo-generator-demo:bqjob_r7e99d09194d84a93_000001848af1cff1_1': 1.1 - 0.0:
Unrecognized token CREATE.
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]

Then switching to Google Standard SQL with --use_legacy_sql=false or --nouse_legacy_sql works as expected:

$ bq query --use_legacy_sql=false `cat create_view.sql`
Waiting on bqjob_r73661e3c9c6d85a4_000001848af3acc9_1 ... (0s) Current status: DONE   
Created slo-generator-demo.slo_generator.last_report

@gplasky Regarding the use of cat, I am under the impression bq is able to read from the standard input as $ bq query --use_legacy_sql=false < create_view.sql works just fine. Does that work for you instead of $(<create_view.sql)?

Now on to the Data Studio part.

[lvaylet]

I was able to create a Data Looker Studio report by following the instructions. We just need to update the screenshots so they match the current UI.

@tuananhnguyen-ct Regarding the creation of the view: it is something that needs to be done only once. As such, IMHO, it does not make sense to delegate the creation to the exporter. In a "separation of concerns" and "least privilege" approach, I think the exporter should focus on doing one thing only (exporting metrics) and doing it well under a dedicated identity. Then the creation of the view can be delegated to another identity/job role (for example someone from the BI team). WDYT?

[lvaylet]

Reopening for the Terraform part.