Update Data in BigQuery

Overview

This lambda has been written to UPDATE or INSERT data in BigQuery based on the parameters passed to it.

Primarily it needs

  1. BigQuery Dataset and Table_name

  2. WHERE condition if any

  3. Column names to be updated/inserted along with the values

This lambda can be triggered as part of Step Function or it can also be triggered event based by placing message in sns-euw1-${env}-digital-storepick-events topic of SNS with the message attribute attached to the message to be eventType=logRowToBq.

Example Repo where it is triggered as part of Step Function:

storepick-free-delivery

Example Repo where it is triggered based on SNS Topic:

storepick-nhs-discount-workflow

Dependencies

Configuration

See Configuration

Code

lambdas.bigquery_update_data.lambda_handler(event, context)[source]

This Lambda will be called in one of the 2 ways. It will be either triggered by SNS when message is placed in the sns-euw1-${env}-digital-storepick-events topic and the message attribute attached to the message is eventType=logRowToBq or called by an upstream Lambda which is part of the step function.

This Lambda is for updating bq_records in BigQuery.

Structured event to craft SQL UPDATE query contains:

  • config_file - config file related to the service

  • bq_update_data - details required to build bigquery sql query (dataset_and_table is optional - if not provided then same will be used from Config)

This function works both for UPDATE and INSERT:

  • if condition == INSERT, then it will INSERT data based on columns_to_update dictionary

  • if condition != INSERT, then it will UPDATE data based on columns_to_update dictionary AND specified condition (for WHERE)

Parameters

event (dict) –

“config_file” - config file related to the service

”bq_update_data” - data for update

Example

event = {
     "config_file": "storepick-nhs-discount.json",
     "bq_update_data" : [
         {
             "dataset_and_table" : "store_pick_discount.nhs_discount",
             "condition"  : "status="PENDING"",
             "columns_to_update" : {
                 "status" : "FAILED",
                 "message" : "test message",
             },
         },
     ]
}
The following query will be crafted based on the above example:

UPDATE store_pick_discount.nhs_discount SET status=”FAILED”, message=”test message” WHERE retailerCustomerId=”aaa-bbb”

Returns

Updated input event with ‘bq_records’ list containing rows read from BigQuery.

Return type

event (dict)

Example

resp = {
    "config_file": "storepick-nhs-discount.json",
    "bq_get_data_query": "SQL query used to update data in BigQuery",
    "bq_records": [
        {
            'key1': 'value1',
            'key2': 'value2',
            ...
        },
        {
            'key1': 'value3',
            'key2': 'value4',
            ...
        },
    ]
}
Raises

  • MoriException – MoriException containing details about exception

  • GoogleCloudBased exceptions – Any error message received from GCP API