Add project setup utility scripts

[critzo]

Setting up a new set of GCP resources to support a new measurement program with a fleet of devices was a bit manual. This PR adds some automation scripts to make the setup process easier. Two scripts and associated schema files are included. @robertodauria PTAL?

• setup_bq.sh

  • given a user account or service account authorized to create resources on a GCP project, set several variables: _gcp_project, bq_dataset, bq_ndt7_table, bq_ndt5_table, bq_speedtesttable
  • if dataset doesn't exist, creates it
  • if tables don't exist, create them
  • create three Views: _device_metadata, server_coords, unified_perftests. These views were modeled after the views used in the MLBN project, and will back a template example DataStudio report.

• bq_load_murakami.sh

  • Intended to be run as a daily crontab, to move test results from GCS from the prev. day into BigQuery tables.

---

This change is 

[critzo]

---

utilities/bq_load_murakami.sh, line 24 at r1 (raw file):

Previously, robertodauria (Roberto D'Auria) wrote…

Could this path be made more specific? e.g. `/ndt7/${loaddate}*`

This path has two wildcards because each test result starts with the test name, and the GCS exporter pushes all results to the same bucket/path. So the load script was designed to push results based on the date, for each test, into BigQuery.

[critzo]

---

utilities/bq_load_murakami.sh, line 32 at r1 (raw file):

Previously, robertodauria (Roberto D'Auria) wrote…

Same as above.

same as above :)

[critzo]

---

utilities/bq_load_murakami.sh, line 12 at r1 (raw file):

Previously, robertodauria (Roberto D'Auria) wrote…

I would suggest taking these from the command line so the user does not need to edit the script to use it and provide a usage example. e.g.: ``` usage="$0 " gcp_project=${1:?Please provide the GCP project: ${usage}} bucket=${2:?Please provide the GCS bucket: ${usage}} bq_dataset=${3:?Please provide the dataset name: ${usage}} ```

This script is intended to be run by cron. Given that does this suggestion still make sense?

[critzo]

---

utilities/setup_bq.sh, line 7 at r1 (raw file):

Previously, robertodauria (Roberto D'Auria) wrote…

If these are meant to be replaced by the user, please get them from the command line. If they are not meant to be changed, just disregard this comment.

Agree in this case. Will change.

[critzo]

---

utilities/setup_bq.sh, line 19 at r1 (raw file):

Previously, robertodauria (Roberto D'Auria) wrote…

Please use the $(...) syntax to run commands (https://github.com/koalaman/shellcheck/wiki/SC2006) - same for the rest of these scripts.

ack

[critzo]

---

utilities/setup_bq.sh, line 83 at r1 (raw file):

Previously, robertodauria (Roberto D'Auria) wrote…

> ``` > ·· > ``` Nit: extra spaces after `WITH`.

ack

[critzo]

---

utilities/setup_bq.sh, line 142 at r1 (raw file):

Previously, robertodauria (Roberto D'Auria) wrote…

Could you use `"` here and simplify the variable expansion below? Same for the other queries.

Yes.

[critzo]

---

utilities/bq_load_murakami.sh, line 12 at r1 (raw file):

Previously, robertodauria (Roberto D'Auria) wrote…

I think it does: any user needs to provide their own project, bucket and dataset before running the script, and that approach ensures that they have explicitly provided them or the script won't even start. It also allows us to automate the deployment with either Travis or GCB in the future more easily (we can pass those arguments in build env variables and add them to the command line instead of doing string replacements into the script).

Thanks. Makes sense.

[critzo]

@robertodauria a few commits added to resolve your suggestions. PTAL again?