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 (15 hours ago)
 by 
5.0
 (3 ratings)
bigquery-connector

BigQuery Connector

Details
BigQuery Connector lets OutSystems 11 applications work with Google BigQuery through the official REST API v2, using a Google service account to sign in.
Read more

Requirements

  • OutSystems 11.
  • The GoogleAPIToken Forge component (dependency).
  • A Google Cloud project with the BigQuery API enabled.


Service account setup

  1. In the Google Cloud console, open IAM & Admin, then Service Accounts, and create a service account.
  2. Give it the roles it needs, either on the whole project or only on specific datasets. For example:
    • BigQuery Job User, to run queries;
    • BigQuery Data Viewer, to read data;
    • BigQuery Data Editor, to insert or change data.
  3. Open the service account and go to Keys, then Add key, then Create new key, and choose JSON. Download the file and keep it somewhere safe.
  4. You need two values from the JSON file: client_email and private_key.

Configuration (Service Center, module BigQueryConnector, Site Properties)

  • ClientEmail: the client_email value.
  • PrivateKey (Secret): the private_key value, including the BEGIN and END lines.
  • DefaultProjectId: the project to use when an action is called with an empty ProjectId.
  • Scope: the OAuth scopes. The default (cloud-platform and bigquery) works for most cases.

Set the values in each environment. Never put the key in code or in client-side variables.

How to use

  1. In your module, open Manage Dependencies and add the BigQueryConnector service actions you need.
  2. Call an action and check Success. When it is False, read ErrorCode and ErrorMessage.

To run a query:

  • Call RunQuery with Query = “SELECT name, total FROM my_dataset.sales WHERE country = @country”.
  • Set QueryParameters to one item with name “country”, parameterType.type “STRING” and parameterValue.value “PT”.
  • Read the results from RowsJSON, for example with JSONDeserialize into your own structure, or from Schema and Rows.
  • If JobComplete is False, call GetQueryResults with JobId and JobLocation until it is True.
  • To get more pages, call GetQueryResults with PageToken = NextPageToken.


To insert rows:

  • Call InsertRows with DatasetId, TableId and Rows. Rows is a Text List with one JSON object per row; you can build each one from a structure with JSONSerialize.
  • To avoid duplicates when you retry, also pass InsertIds, in the same order as Rows.
  • Check AllRowsInserted and InsertErrors.


To list with pagination:

  • Call ListDataset, ListAllTables, ListAllJobs or another list action with MaxResults.
  • While NextPageToken is not empty, call it again with PageToken = NextPageToken.


Supported methods

  • Datasets: GetDataset, ListDataset, InsertDataset, PatchDataset, UpdateDataset, DeleteDataset, UndeleteDataset.
  • Tables: GetTable, ListAllTables, InsertTable, PatchTable, UpdateTable, DeleteTable, GetIamPolicy, SetIamPolicy, TestIamPermissions.
  • Table data: ListTableData, InsertRecord (raw insertAll), InsertRows (helper).
  • Jobs: QueryJob (raw jobs.query), RunQuery (helper), InsertJob, GetJob, ListAllJobs, GetQueryResults, CancelJob, DeleteJob.
  • Models: GetModel, ListAllModels, PatchModel, DeleteModel.
  • Routines: GetRoutine, ListRoutine, InsertRoutine, UpdateRoutine, DeleteRoutine.
  • Projects: ListProjects, GetServiceAccount.
  • Row access policies: ListRowAccessPolicies, GetRowAccessPolicy, InsertRowAccessPolicy, UpdateRowAccessPolicy, DeleteRowAccessPolicy, BatchDeleteRowAccessPolicies.


Error handling

  • ErrorCode is the HTTP status returned by Google, for example:
    • 400: invalid request or SQL;
    • 403: no permission, or quota exceeded;
    • 404: not found;
    • 409: already exists.
  • ErrorCode -1 means a local error: network, timeout, token, or a response that could not be mapped. ErrorMessage and ResponseJSON have the details.
  • Errors are also logged in Service Center.


Notes and limitations

  • In request structures, attributes left at their default value (empty text, 0, False) are not sent. For QueryJob this means useLegacySql=False is not sent, so the API falls back to legacy SQL. Use RunQuery instead, or start the query with #standardSQL.
  • RowsJSON returns every value as a JSON string. Nested RECORD or REPEATED columns are not mapped; read them from ResponseJSON.
  • Fields that the structures do not model are always available in ResponseJSON.
  • The Storage Read and Storage Write APIs use gRPC and are not covered. For large exports, use InsertJob with an extract job to Cloud Storage.
  • BigQuery charges for the bytes each query processes. Use DryRun in RunQuery to estimate the cost.
Release notes (2.0.0)

Version 2.0.0 is a major rewrite. Inputs and outputs of the service actions have changed, so consumers must refresh their dependencies and adjust their calls.

Breaking changes

  • Renamed from BigQuery API (module BigQuery_IS) to BigQuery Connector (module BigQueryConnector).
  • All service action inputs now use PascalCase names.
  • Every service action now returns Success, ErrorCode, ErrorMessage, Response, NextPageToken (on list actions), and ResponseJSON.
  • PrivateKey is now a Secret site property, so set it again in each environment.
  • The legacy URL parameters (alt, fields, key, oauth_token, prettyPrint, quotaUser, userIp) were removed.


Fixes

  • Responses are now returned for every method.
  • The request body is now sent for queries and other write operations.
  • Fixed wrong data types in several structures: Dataset.datasetReference, Dataset. labels, and the attributes that were typed as static entity identifiers, which are now Text.
  • Optional parameters are no longer sent when they are empty.


New

  • RunQuery helper: GoogleSQL by default, named parameters, polling until the job completes, and rows returned as JSON objects (RowsJSON).
  • InsertRows helper: stream JSON rows, with optional insert IDs, and get back AllRowsInserted and InsertErrors.
  • New methods: UndeleteDataset, DeleteJob, and row access policies (List, Get, Insert, Update, Delete, BatchDelete).
  • DefaultProjectId site property, used when ProjectId is empty.
  • Google errors are mapped to ErrorCode (the HTTP status) and ErrorMessage instead of raising exceptions. ErrorCode is -1 for local errors.


Improvements

  • The access token is cached for 50 minutes.
  • Each method has its own timeout: 30 s, or 120 s for queries and inserts.
  • Every element is documented: actions, inputs, outputs, structures, attributes, and site properties.
  • The unused structures and static entities were removed.
  • A new demo app (BigQuery Connector Demo) covers the connection test, queries, a data explorer, dataset management, and streaming inserts.
License (2.0.0)
Reviews (0)
Team
Other assets in this category