Reputation: 1
https://cloud.google.com/workflows/docs/samples/workflows-connector-bigquery
Reviewing the above bigquery connector example. Trying to do a incremental delete and insert on a table to schedule on a regular basis. I cant figure out how to trigger the delete query.
1st attempt at building a workflow for automating run through cloud scheduler. I know I can get the results I want using bigquery scheduler, but trying to expand my horizons. Not much experience with yaml - so wondering if there is a proper call for triggering the delete portion? Or a better way to code for the delete and inserts I'm looking to accomplish.
# Create a dataset and then insert a table with data from a public dataset
# Delete both the table and dataset
# Expected output: "SUCCESS"
- init:
assign:
- project_id: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
- dataset_id: "cloud_function_test"
- table_id: "model_test"
- query: "
select
distinct(model) as model,
source_type,
current_timestamp as date_time
from `cl-subaru-of-america.views_adobe_analytics.t1_subarucom_model`
where model is not null
group by model, source_type;
"
- create_disposition: "CREATE_IF_NEEDED" # creates table if it doesn't exist
- write_disposition: "WRITE_TRUNCATE" # truncates table if it already exists
# - write_dispsition: "WRITE_APPEND" # append data If the table already exists, BigQuery appends the data to the table
#- create_dataset:
# call: googleapis.bigquery.v2.datasets.insert
# args:
# projectId: ${project_id}
# body:
# datasetReference:
# datasetId: ${dataset_id}
# projectId: ${project_id}
# access[].role: "roles/bigquery.dataViewer"
# access[].specialGroup: "projectReaders"
- insert_table_into_dataset:
call: googleapis.bigquery.v2.jobs.insert
args:
projectId: ${project_id}
body:
configuration:
query:
query: ${query}
destinationTable:
projectId: ${project_id}
datasetId: ${dataset_id}
tableId: ${table_id}
create_disposition: ${create_disposition}
write_disposition: ${write_disposition}
allowLargeResults: true
useLegacySql: false
# - delete_table_from_dataset:
# call: googleapis.bigquery.v2.tables.delete
# args:
# projectId: ${project_id}
# datasetId: ${dataset_id}
# tableId: ${table_id}
# - delete_dataset:
# call: googleapis.bigquery.v2.datasets.delete
# args:
# projectId: ${project_id}
# datasetId: ${dataset_id}
- the_end:
return: "SUCCESS"
Tried using a variety of calls (jobs, tables - delete, insert, query)Either deleting all from table (which I don't want) or throwing an errors such as "Required parameter is missing: query","reason":"required", or JobID parameter is missing.
Upvotes: 0
Views: 58