Delimit BigQuery REGEXP_EXTRACT strings in Google Cloud Build YAML script

Question

I have a complex query that creates a View within the BigQuery console. I have simplified it to the following to illustrate the issue

SELECT
REGEXP_EXTRACT(FIELD1,  r"[\d]*") as F1,
REGEXP_REPLACE(FIELD2, r"\'", "") AS F2,
FROM `project.mydataset.mytable`

Now I am trying to automate the creation of the view with cloud build. I cannot workout how to delimit the strings inside the regex to work with both yaml and SQL.

- name: 'gcr.io/cloud-builders/gcloud'
entrypoint: 'bq'
args: [
'mk',
'--use_legacy_sql=false',
'--project_id=${_PROJECT_ID}',
'--expiration=0',
'--view= 
    REGEXP_EXTRACT(FIELD1,  r"[\d]*") as F1 ,
    REGEXP_REPLACE(FIELD2, r"\'", "") AS F2,
    REGEXP_EXTRACT(FIELD3,  r"\[(\d{3,12}).*\]") AS F3
    FROM `project.mydataset.mytable`" 
    '${_TARGET_DATASET}.${_TARGET_VIEW}'
  ]

I get the following error

Failed to trigger build: failed unmarshalling build config cloudbuild/build-views.yaml: json: cannot unmarshal number into Go value of type string

I have tried using Cloud Build substitution parameters, and as many combinations of SQL and YAML escape sequences as I can think of to find a working solution.

An alternative is to move it to a bash script and run it form it, as is mentioned in this other post. https://stackoverflow.com/a/55349768/8210931 — Enrique Del Valle, Jul 02 '20 at 22:03

score 2 · Accepted Answer · answered Jul 02 '20 at 19:52

Generally, you want to use block scalars in such cases, as they do not process any special characters inside them and are terminated via indentation.

I have no idea how the command is supposed to look, but here's something that's at least valid YAML:

- name: 'gcr.io/cloud-builders/gcloud'
  entrypoint: 'bq'
  args:
  - 'mk'
  - '--use_legacy_sql=false'
  - '--project_id=${_PROJECT_ID}'
  - '--expiration=0'
  - >- # folded block scalar; newlines are folded into spaces
    --view= 
    REGEXP_EXTRACT(FIELD1,  r"[\d]*") as F1,
    REGEXP_REPLACE(FIELD2, r"\'", "") AS F2,
    REGEXP_EXTRACT(FIELD3,  r"\[(\d{3,12}).*\]") AS F3
    FROM `project.mydataset.mytable`" 
    '${_TARGET_DATASET}.${_TARGET_VIEW}'
  - dummy value to show that the scalar ends here

A folded block scalar is started with >, the following minus tells YAML to not append the final newline to its value.

Thanks, that works. I also discovered submitting the build job locally gives better diagnostics and avoids the checking step. > gcloud builds submit --config=cloudbuild.yaml — intotecho, Jul 03 '20 at 05:30

Delimit BigQuery REGEXP_EXTRACT strings in Google Cloud Build YAML script

1 Answers1