BigQuery integration

As soon as a plan or test completes, the execution details such as outcome, duration, and browser type will be immediately written to tables in your configured BigQuery dataset. See the export schema for details.

Enterprise Feature

This feature is enabled during trials and requires an Enterprise plan.

Getting Started

BigQuery Sandbox Incompatability

The BigQuery Sandbox feature available to unpaid trial Google Cloud accounts cannot be used with mabl BigQuery feed export. This is because Streaming Inserts are not supported by BigQuery Sandbox.

Upgrade your BigQuery account to use this feature.

You'll need to add a Service Account to your destination GCP Project so mabl can deliver data.

Google Cloud Project Aliases

Sometimes GCP will alias a project, meaning the project Id and Name differ.
Make sure to use the account Id not the account Name in this case.

Run gcloud project list to see all project details (Id, Name, Number).

  1. Add the ServiceAccount [email protected] as a BigQuery Data Editor to the destination Google Cloud Platform project (more details).
  2. In the mabl web UI, Visit Settings > Integrations and click BigQuery Export.
  3. Add the destination project Id, dataset name, and a prefix for the export table.
  4. Click save.
Click the `Export BigQuery` button to get started.

Click the Export BigQuery button to get started.

Upon running the first test through the BigQuery export feed, the specified dataset and following tables will be created.

  • <table_prefix>_plan_run
  • <table_prefix>_journey_run

For example, if you use the defaults, the following BigQuery tables will be created:

  • <your-project>:mabl_export.mabl_plan_run
  • <your-project>:mabl_export.mabl_journey_run

Personally Identifiable Data Redaction

For readability, exports reference entities using both an opaque unique identifier, and the customer supplied name property.

For user entities, this name is their email. However, some customers may not want this information in BigQuery. Enabling the Redact Personal Information Fields will replace these entries with the value PII_REDACTED on all subsequent writes.

Redact personally identifiable fields.

Redact personally identifiable fields.

Field Redaction Doesn't Include Free Text

The PII redaction only excludes the below fields.

  • trigger_user_email

Other free text fields, like test name will still be included. mabl does not inspect the contents of these fields.

Table Schema Changes

Table schema changes will be rare and only be additive if they occur

  • New columns only
  • Announcement via Friends of mabl Slack and email lists
  • Admins of active integration users will be notified

Table Schema

Table *_plan_run

Contains every plan run on the integrated workspace, written upon plan run completion.

Column Name
Type
Description

id

String

plan run id

plan_id

String

plan id

plan_name

String

plan name

plan_url

String

plan UI URL

status

String

plan run status

success

Boolean

overall plan run success

start_time

Timestamp

plan run start time UTC

stop_time

Timestamp

plan run stop time UTC

runtime_millis

Integer

total plan run time milliseconds

tags

String, Repeated

plan tags

labels

String, Repeated

plan labels

application_id

String

application under test id

application_name

String

application under test name

application_url

String

application under test UI URL

starting_url

String

application under test starting url

trigger_type

String

execution trigger type

trigger_user_id

String

execution triggering user (optional)

trigger_user_email

String

execution triggering user email (optional)

deployment_id

String

execution trigger deployment id (optional)

workspace_id

String

workspace name

workspace_name

String

workspace name

Notes on Trigger Types

The trigger_type dimension will be one of the following values:

Trigger Type Value
Description
User Id/Email populated

SCHEDULE

Executed by timer/schedule trigger

No

MANUAL

Executed manually from mabl web app or CLI
(non-Deployment event)

Yes

CUSTOMER_EVENT

Executed for Deployment API trigger

No

INSIGHT

Executed in reaction to a mabl Insight

No

Table *_journey_run

Contains every test run on the integrated workspace, written upon test run completion (possibly before parent plan run, as parent isn't yet complete).

Column Name
Type
Description

id

String

test run id

journey_id

String

test id

journey_name

String

test name

journey_url

String

test UI URL

view_output_url

String

test run output UI URL

status

String

test run status

success

Boolean

overall test run success

start_time

Timestamp

test run start time UTC

stop_time

Timestamp

test run stop time UTC

runtime_millis

Integer

total test run time milliseconds

tags

String, Repeated

test tags

labels

String, Repeated

test labels

mabl_branch

String

mabl branch test ran against

environment_id

String

test run environment

environment_name

String

test run environment name

environment_url

String

test run environment UI URL

browser_type

String

browser under test type

browser_version

String

browser under test version

plan_id

String

plan id

plan_name

String

plan name

plan_url

String

plan UI URL

plan_run_id

String

plan run id

workspace_id

String

workspace id

workspace_name

String

workspace name

Table *_run_categorization

Contains every test run failure reason for the integrated workspace, written when a user sets a failure reason on a given test run output.

Repeated Entries per Test Run on update

A new row will be written for each change to a test run's failure reason (stored in the category dimension). Group by journey_run_id and take the latest create_time value to find the most recent category given to a test run.

If the failure reason is removed, category will be NULL.
This indicates that a user has cleared the test run of a failure reason.

Column name
Type
Description

id

String

unique id

category

String

failure reason (NULL on removed)

journey_id

String

test id

journey_name

String

test name

journey_run_id

String

test run id

created_time

Timestamp

grading time UTC

start_time

Timestamp

test run start time UTC

stop_time

Timestamp

test run stop time UTC

grader_user_id

String

grading user id

grader_user_email

String

grading user email (optional)

workspace_id

String

workspace id

workspace_name

String

workspace name

tombstone

Boolean

Indicator that failure reason has been removed from test run. Will be true if test run no longer has a failure reason and category will be NULL.

Querying Failure Reason

Because BigQuery exports are done in a Data Warehouse fashion, tables are never updated/deleted, but rather only appended.

This means that mutable values like failure reasons result in multiple rows being written when they change. Thus, to determine the latest failure reason, you need to (1) find the last update and (2) select the matching rows. The following query does this.

To use, replace my-project.mabl_export.mabl_run_categorization with the table name in your BigQuery system.

SELECT
  failure_reason.*
  
FROM (
  -- Get latest update time
  SELECT
    journey_run_id,
    MAX(created_time) AS last_updated_time
  FROM
    `my-project.mabl_export.mabl_run_categorization`
  GROUP BY
    journey_run_id ) AS latest_categories

JOIN `my-project.mabl_export.mabl_run_categorization` AS failure_reason
ON failure_reason.created_time = latest_categories.last_updated_time

-- Ignore removed failure reasons
WHERE failure_reason.category IS NOT NULL

Updated 2 months ago



BigQuery integration


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.