Kevin Hansen
Kevin Hansen

Reputation: 1

Google Cloud Platform Workflow - how to delete rows from table using sql

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

Answers (0)

Related Questions