Data Analysis and Queries

Google BigQuery

Description

The component Google BigQuery allows systems to connect to BigQuery, enabling data ingestion, query execution, and the export of results to other systems.

The component works exclusively as a producer, which means it does not allow running queries in BigQuery, only data insertion.

Example

The flow below receives data via POST request, processes it, and sends it to Google BigQuery, with JSON format conversions during the process. See the steps:

The flow starts with a REST POST request.
The received data is converted from JSON (marshal) to an internal format using the Jackson library.
The data is sent to Google BigQuery, to the employees table in the poc_bigquery dataset, in the registry-ipaas-testing project.
The service account key key.json is used to authenticate the request.
The data is converted back (unmarshal) to JSON format using the Jackson library, after processing.

The Google BigQuery component accepts a "List" or "Map" as input, which requires the use of the "unmarshal" EIP to convert the data to the proper format.

Script

Diagram

- from:
    uri: rest:post:/demo
    steps:
      - unmarshal:
          json:
            library: Jackson
      - to:
          uri: google-bigquery://registry-ipaas-testing:poc_bigquery:employees
          parameters:
            serviceAccountKey: key.json
      - marshal:
          json:
            library: Jackson

Google BigQuery SQL Standard

Description

The component Google BigQuery SQL Standard allows systems to connect to BigQuery, enabling data ingestion, query execution, and the export of results to other systems.

The current implementation supports only standard SQL DML statements. The BigQuery Data Manipulation Language (DML) allows you to update, insert, and delete data from BigQuery tables. The component does not allow the use of SELECT.

Example

In the example below, the flow deletes data from a table in Google BigQuery when a GET request is received. See the steps:

The flow starts with a REST GET request.
A DELETE SQL query is executed on Google BigQuery to delete all records from the employees table.
The service account key key.json is used to authenticate the operation on Google BigQuery.
The data is converted to JSON format using the Jackson library, after executing the SQL query.

Script

Diagram

- from:
          uri: rest:get:/demo
          steps:
          - to:
              uri: google-bigquery-sql://registry-ipaas-testing:delete from poc_bigquery.employees where true
              parameters:
                serviceAccountKey: key.json
          - marshal:
              json:
                library: Jackson

Thanks for your feedback!

EDIT

Share your suggestions with us!
Click here and then [+ Submit idea]