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 Integration Requires a Paid Google Cloud Account

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 to a paid GCP BigQuery account to use mabl BigQuery Export.

🚧

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.
447

Click the BigQuery + SETUP button to get started.

Alternative IAM Permission Setup

If you desire constraining the mabl [email protected] user to a greater extent, you can create a Custom IAM Role with the following permissions, and assign the role to the mabl service account on the target GCP project.

  • bigquery.datasets.create
  • bigquery.datasets.get
  • bigquery.tables.create
  • bigquery.tables.get
  • bigquery.tables.update
  • bigquery.tables.updateData

BigQuery table layout and partitions

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
  • <table_prefix>_run_categorization
  • <table_prefix>_performance_test_run
  • <table_prefix>_activity_feed

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
  • <your-project>:mabl_export.mabl_run_categorization
  • <your-project>:mabl_export.mabl_performance_test_run
  • <your-project>:mabl_export.mabl_activity_feed

These tables will be partitioned by day, based on the columns:

  • <table_prefix>_plan_run on column start_time
  • <table_prefix>_journey_run on column start_time
  • <table_prefix>_run_categorization on column created_time
  • <table_prefix>_performance-test_run on column start_time
  • <table_prefix>_activity_feed on column created_time

If you want to discard test data after a set amount of time, you can configure partition expiration in your BigQuery data warehouse.

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.

1200

Redact personally identifiable fields.

🚧

Field redaction doesn't include free text

The PII redaction only excludes the below fields.

  • trigger_user_email
  • actor_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 NameTypeDescription
idStringplan run id
plan_idStringplan id
plan_nameStringplan name
plan_urlStringplan UI URL
statusStringplan run status
successBooleanoverall plan run success
start_timeTimestampplan run start time UTC
stop_timeTimestampplan run stop time UTC
runtime_millisIntegertotal plan run time milliseconds
tagsString, Repeatedplan tags
labelsString, Repeatedplan labels
application_idStringapplication under test id
application_nameStringapplication under test name
application_urlStringapplication under test UI URL
starting_urlStringapplication under test starting url
trigger_typeStringexecution trigger type
trigger_user_idStringexecution triggering user (optional)
trigger_user_emailStringexecution triggering user email (optional)
deployment_idStringexecution trigger deployment id (optional)
workspace_idStringworkspace name
workspace_nameStringworkspace name

Notes on trigger types

The trigger_type dimension will be one of the following values:

Trigger Type ValueDescriptionUser Id/Email populated
SCHEDULEExecuted by timer/schedule triggerNo
MANUALExecuted manually from mabl web app or CLI
(non-Deployment event)
Yes
CUSTOMER_EVENTExecuted for Deployment API triggerNo
INSIGHTExecuted in reaction to a mabl InsightNo

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).

TypeColumn NameDescription
Stringidtest run id
Stringjourney_idtest id
Stringjourney_nametest name
Stringjourney_urltest UI URL
Stringview_output_urltest run output UI URL
Stringstatustest run status
Booleansuccessoverall test run success
Timestampstart_timetest run start time UTC
Timestampstop_timetest run stop time UTC
Integerruntime_millistotal test run time milliseconds
String, Repeatedtagstest tags
String, Repeatedlabelstest labels
Stringmabl_branchmabl branch test ran against
Stringenvironment_idtest run environment
Stringenvironment_nametest run environment name
Stringenvironment_urltest run environment UI URL
Stringbrowser_typebrowser under test type
Stringbrowser_versionbrowser under test version
Stringplan_idplan id
Stringplan_nameplan name
Stringplan_urlplan UI URL
Stringplan_run_idplan run id
Stringworkspace_idworkspace id
Stringworkspace_nameworkspace 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 nameTypeDescription
idStringunique id
categoryStringfailure reason (NULL on removed)
journey_idStringtest id
journey_nameStringtest name
journey_run_idStringtest run id
created_timeTimestampgrading time UTC
start_timeTimestamptest run start time UTC
stop_timeTimestamptest run stop time UTC
grader_user_idStringgrading user id
grader_user_emailStringgrading user email (optional)
workspace_idStringworkspace id
workspace_nameStringworkspace name
tombstoneBooleanIndicator 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

Table *_performance_test_run

Contains metrics for every performance test run on the integrated workspace, written upon test run completion (possibly before parent plan run, as parent isn't yet complete). Can be joined with the journey run table on id.

Column NameTypeDescription
idStringtest run id
journey_idStringtest id
journey_nameStringtest name
journey_urlStringtest UI URL
journey_run_idStringtest run output UI URL
start_timeTimestamptest run start time UTC
stop_timeTimestamptest run stop time UTC
api_virtual_user_hours_consumedFloatvirtual user hours consumed of performance test run
api_concurrent_user_countIntegerconcurrent users of performance test run
api_average_response_timeIntegeraverage response time in milliseconds of performance test run
api_requests_per_secondIntegeraverage requests per second of performance test run
api_error_rate_percentFloataverage error rate as a percent [0-100] of performance test run
workspace_idStringworkspace id
workspace_nameStringworkspace name

Table *_activity_feed

Contains every activity feed entry in the integrated workspace. Contains activity data for all types supported by the activity feed in the mabl ui.

Column NameTypeDescription
idStringactivity feed entry id
entity_idStringid of the entity changed
entity_nameStringname of the entity changed
entity_typeStringtype of the entity changed
entity_canonical_idStringtest run output UI URL
entity_version_numberIntegerversion number for versioned entities
action_typeStringtype of action taken
action_timestampTimestamptime the action was taken
created_timeTimestampactivity feed entry creation time
actor_idStringid of the person or API key that took the action
actor_emailStringemail of the actor, if applicable
entity_previous_idStringid of the entity before this action
entity_previous_nameStringname of the entity before this action
entity_previous_version_numberIntegerversion of the entity before this action
workspace_idStringworkspace id
workspace_nameStringworkspace name

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