How is your experience with the OutSystems Community?
bigquery-connector
Service icon

BigQuery Connector

Stable version 2.0.0 (Compatible with OutSystems 11)
Uploaded
 on 19 Nov (14 hours ago)
 by 
5.0
 (3 ratings)
bigquery-connector

BigQuery Connector

Documentation
2.0.0

Overview

BigQuery Connector 2.0.0 gives OutSystems 11 apps access to Google BigQuery through the REST API v2, using a Google service account. It ships as a Service module (BigQueryConnector), so consumers reference service actions and don't have to republish when the connector changes.

What it covers:

  • Run SQL with RunQuery, with named parameters, polling until the job finishes and rows returned as JSON objects.
  • Stream rows with InsertRows, with optional insert ids for deduplication.
  • Manage datasets, tables, table data, jobs, models, routines, projects, row access policies and table IAM policies, with one service action per API method (42 methods).

How a call flows:

  1. Your app calls a BigQueryConnector service action.
  2. The connector gets an OAuth 2.0 access token from the GoogleAPIToken component, signed with the service account key, and caches it for 50 minutes.
  3. The action serializes its Request structure to JSON and calls the BigQuery REST endpoint over HTTPS, with its own timeout.
  4. The connector maps any Google error to ErrorCode and ErrorMessage, or deserializes the response into the Response structure. The connector always returns the raw JSON in ResponseJSON.

Requirements and service account setup

You need OutSystems 11, the GoogleAPIToken Forge component (a dependency), and a Google Cloud project with the BigQuery API enabled.

To create the service account:

  1. In the Google Cloud console, open IAM & Admin, then Service Accounts, and create a service account.
  2. Grant it only the roles it needs, on the project or on specific datasets:
    • BigQuery Job User, to run queries and jobs.
    • BigQuery Data Viewer, to read tables and data.
    • BigQuery Data Editor, to insert or change data and tables.
    • BigQuery Admin, only if the app manages datasets or row access policies.
  3. Open the service account, go to Keys, then Add key, then Create new key, and choose JSON. Store the file safely and never commit it.
  4. Copy two values from the JSON file: client_email and private_key.

Configuration

Set four site properties on the BigQueryConnector module in Service Center, separately for each environment.

  • ClientEmail: the client_email value from the key file. It identifies the service account.
  • PrivateKey (Secret): the private_key value, including the BEGIN and END lines. Service Center masks it and it never reaches the client.
  • DefaultProjectId: your Google Cloud project id, used when an action is called with an empty ProjectId.
  • Scope: the OAuth scopes. The default (cloud-platform and bigquery) fits most cases.

Never put the key in code, in literals or in client-side variables. When you rotate the key in Google Cloud, update PrivateKey; the cached token expires within 50 minutes.

Using the connector

Add the service actions you need from BigQueryConnector in Manage Dependencies, call them from server actions, and check Success after every call.

Output contract

Every service action returns the same outputs:

  • Success (Boolean): True when Google returned a 2xx status.
  • ErrorCode (Integer): the HTTP status of the Google error, or -1 for a local error.
  • ErrorMessage (Text): Google's status and message, for example NOT_FOUND: Not found: Dataset my-project:sales.
  • Response (structure): the response mapped to the method's structure.
  • NextPageToken (Text): list actions only; empty on the last page.
  • ResponseJSON (Text): the raw JSON body, including fields the structure does not model.

ProjectId is optional on every action. When it is empty, DefaultProjectId is used.

Run a query (RunQuery)

  1. Set Query, for example: SELECT name, total FROM my_dataset.sales WHERE country = @country
  2. Add one QueryParameters item: name = "country", parameterType.type = "STRING", parameterValue.value = "PT".
  3. Optionally set Location, MaxResults, TimeoutMs, DryRun or UseLegacySql (False by default).
  4. Read the results:
    • RowsJSON: a JSON array of objects, one per row, keyed by column name. Use JSONDeserialize into your own structure list.
    • Schema and Rows: BigQuery's native format.
    • TotalRows, TotalBytesProcessed, NumDmlAffectedRows (for INSERT, UPDATE and DELETE), JobComplete, JobId and JobLocation.
  5. If JobComplete is False after the built-in polling, call GetQueryResults with JobId and JobLocation until it is True.
  6. For more pages, call GetQueryResults with PageToken = NextPageToken.

Use DryRun = True to check the SQL and see TotalBytesProcessed without running it or paying for it.

Stream rows (InsertRows)

  1. Build one JSON object per row, for example with JSONSerialize on a structure whose attribute names match the column names.
  2. Call InsertRows with DatasetId, TableId and Rows (a Text List).
  3. To make retries safe, pass InsertIds in the same order as Rows. BigQuery drops a row whose id it saw in the last minute or so.
  4. Optionally set SkipInvalidRows (insert the valid rows even if some fail) and IgnoreUnknownValues.
  5. Check AllRowsInserted. When it is False, InsertErrors lists the row index and the reason for each rejected row.

Streamed rows can be queried within seconds, but they cannot be updated or deleted with DML for up to 30 minutes.

Paginate a list

  1. Call a list action (ListDataset, ListAllTables, ListAllJobs, ListTableData and others) with MaxResults.
  2. While NextPageToken is not empty, call it again with PageToken = NextPageToken.

Call any other method

Fill the method's Request structure (for example InsertDataset with a Dataset record), call the action, then read Response or ResponseJSON. Attributes left at their default value are not sent, so a PATCH only changes the fields you set.

Service action reference

The connector has 44 service actions: one per REST method, plus the RunQuery and InsertRows helpers. Each one's description in Service Studio has Google's text, a usage note and the endpoint it wraps.

Datasets

  • GetDataset: returns a dataset.
  • ListDataset: lists the datasets in a project; supports All, Filter and pagination.
  • InsertDataset: creates a dataset.
  • PatchDataset: changes only the fields you set.
  • UpdateDataset: replaces the whole dataset resource.
  • DeleteDataset: deletes a dataset; set DeleteContents to delete its tables too.
  • UndeleteDataset: restores a deleted dataset within the time travel window.


Tables

  • GetTable, ListAllTables, InsertTable, PatchTable, UpdateTable, DeleteTable: read and manage tables and views.
  • GetIamPolicy, SetIamPolicy, TestIamPermissions: read and change table access, and test the caller's permissions.


Table data

  • ListTableData: reads rows directly from a table, without a query; supports StartIndex, SelectedFields and pagination.
  • InsertRecord: the raw insertAll call, with the full request structure.
  • InsertRows: helper over insertAll that takes rows as JSON objects and returns AllRowsInserted and InsertErrors.


Jobs

  • RunQuery: helper that runs SQL and returns RowsJSON, Schema and Rows.
  • QueryJob: the raw jobs.query call, with the full request structure.
  • InsertJob: starts a query, load, extract or copy job.
  • GetJob, ListAllJobs: read job status and history.
  • GetQueryResults: reads the results of a query job, page by page.
  • CancelJob: asks BigQuery to stop a running job.
  • DeleteJob: deletes a job's metadata.


Models

  • GetModel, ListAllModels, PatchModel, DeleteModel: read and manage BigQuery ML models.


Routines

  • GetRoutine, ListRoutine, InsertRoutine, UpdateRoutine, DeleteRoutine: read and manage user-defined functions and stored procedures.


Projects

  • ListProjects: lists the projects the service account can access. The demo uses it to test the connection.
  • GetServiceAccount: returns the BigQuery service account of a project, used for Cloud KMS encryption.


Row access policies

  • ListRowAccessPolicies, GetRowAccessPolicy, InsertRowAccessPolicy, UpdateRowAccessPolicy, DeleteRowAccessPolicy, BatchDeleteRowAccessPolicies: read and manage row-level security on a table.


Error handling

The actions do not throw on Google errors: check Success and branch on ErrorCode.

  • 400: invalid request or SQL. ErrorMessage gives the reason and often the line and column.
  • 401: the token was rejected. Check ClientEmail and PrivateKey.
  • 403: missing permission, or a quota or rate limit was exceeded. Grant the role, or retry later with backoff.
  • 404: the project, dataset, table or job does not exist, or the job is in another location.
  • 409: the resource already exists. When creating, treat it as success if that is what you want.
  • -1: a local error: network, timeout, token, or a response that could not be mapped. Details are in ErrorMessage and ResponseJSON.

Every error is also logged in Service Center, under the BigQueryConnector module.

Notes and limitations

  • Default values are not sent. Request attributes left at empty text, 0 or False are left out of the JSON. For QueryJob, useLegacySql=False is therefore not sent and BigQuery falls back to legacy SQL; use RunQuery, or start the query with #standardSQL.
  • RowsJSON values are strings. Every value comes back as a JSON string, as BigQuery returns it. Nested RECORD or REPEATED columns are not flattened; read them from ResponseJSON.
  • Unmodelled fields: anything the structures do not cover is still in ResponseJSON.
  • No Storage APIs. The Storage Read and Storage Write APIs use gRPC and are not covered. For large exports, run an extract job to Cloud Storage with InsertJob.
  • Cost. BigQuery charges for the bytes each query scans. Use DryRun to estimate it, select only the columns you need, and filter on partition columns.
  • Timeouts. Each method has its own timeout: 120 s for query, getQueryResults, insertAll and jobs.insert, and 30 s for the rest. For longer queries, use InsertJob and poll GetJob.


Demo app

BigQuery Connector Demo (module BigQueryConnectorDemo, Reactive Web) shows each feature on its own screen. Configure the connector's site properties first, then open Setup.

  • Setup: the service account steps and a Test connection button, which calls ListProjects.
  • Query: runs SQL against the public table bigquery-public-data.usa_names.usa_1910_2013 with a @state parameter, shows the bytes processed and draws the results grid from the schema.
  • Explorer: pick a dataset, then a table, to see its schema and the first rows, with paging.
  • Datasets: create, edit (PatchDataset) and delete datasets in your project.
  • Stream rows: creates a demo table, sends one row or three sample rows (one invalid, to show InsertErrors), then queries the latest rows.

The Query screen reads public data. The Datasets and Stream rows screens write to your project, so use a test project.

Upgrading from 1.0.1

Version 2.0.0 is a breaking release; plan to update every consumer.

  1. Publish the new version and set the site properties again: PrivateKey is now Secret, and DefaultProjectId is new.
  2. In each consumer module, refresh the dependencies on BigQueryConnector.
  3. Fix the calls: inputs now have PascalCase names, and outputs are Success, ErrorCode, ErrorMessage, Response and ResponseJSON.
  4. Replace any exception handling around the calls with checks on Success.
  5. For queries, consider moving from QueryJob to RunQuery.