GBQ Performance and Consumption SpotApp

SpotApps are ThoughtSpot’s out-of-the-box solution templates built for specific use cases and data sources. They are built on ThoughtSpot Modeling Language (TML) Blocks, which are pre-built pieces of code that are easy to download and implement directly from the product.

The GBQ Performance and Consumption SpotApp mimics the GBQ data model. When you deploy it, ThoughtSpot creates several Worksheets, Answers, and Liveboards, based on your GBQ data in your cloud data warehouse.

This is a sample Liveboard, created after you deploy the GBQ Performance and Consumption SpotApp:

GBQ SpotApp Liveboard

Use the GBQ Performance and Consumption SpotApp to manage GBQ costs and consumption. You can track how and where consumption of slots and bytes are processed.

Prerequisites

Before you can deploy the GBQ Performance and Consumption SpotApp, you must complete the following prerequisites:

  • Review the required tables and columns for the SpotApp.

  • Ensure that your columns match the required column type listed in the schema for your SpotApp.

  • Sync all tables and columns from GBQ to your cloud data warehouse. You can sync only the required tables and columns, but ThoughtSpot recommends syncing all tables and columns from GBQ to your CDW. The columns can be GBQ’s out-of-the-box columns, or any custom columns that you are using instead of the out-of-the-box columns.

    If you are using an ETL/ELT tool or working with another team in your organization to move data, our recommendation is that you sync all columns from the tables listed in the SpotApp.

  • Obtain credentials and SYSADMIN privileges to connect to GBQ. The cloud data warehouse must contain the data you would like ThoughtSpot to use to create Answers, Liveboards, and Worksheets. Refer to the Connection reference for GBQ for information about required credentials.

  • The connection name for each new SpotApp must be unique.

  • Administrator access to GBQ on console.cloud.google.com for the project

  • Administrator access to IAM, to create roles and Service accounts

  • Access to the following GBQ tables and Worksheets in your cloud data warehouse. Refer to GBQ Performance and Consumption SpotApp schema for more details.

    • JOBS_BY_PROJECT (table)

    • GBQ : JOB TIMELINE BY PROJECT (Worksheet)

  • Run the required SQL commands in your cloud data warehouse. Refer to Run SQL commands.

Run SQL commands

These commands import the JOBS_BY_PROJECT metadata table into your project dataset. Replace the database name and schema name with your specific information.

The following SQL example is for the Amazon Redshift cloud data warehouse. You may need to modify the code for the SQL requirements of your specific cloud data warehouse. 
create  view `<project_name>.<dataset_name>.JOBS_BY_PROJECT` as
select * from  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

Deploy the GBQ Performance and Consumption SpotApp

After you complete the prerequisites, you are ready to deploy the GBQ Performance and Consumption SpotApp and begin leveraging its pre-built content.

To deploy the GBQ Performance and Consumption SpotApp, refer to Deploying SpotApps.

GBQ Performance and Consumption SpotApp schema

The following table describes the schema for the GBQ Performance and Consumption SpotApp.

Table/View Column Column type Required column

JOBS_BY_PROJECT

CREATION_TIME

DATE_TIME

Y

JOBS_BY_PROJECT

PROJECT_ID

VARCHAR

Y

JOBS_BY_PROJECT

PROJECT_NUMBER

INT64

Y

JOBS_BY_PROJECT

USER_EMAIL

VARCHAR

Y

JOBS_BY_PROJECT

JOBS_ID

VARCHAR

Y

JOBS_BY_PROJECT

JOBS_TYPE

VARCHAR

Y

JOBS_BY_PROJECT

STATEMENT_TYPE

VARCHAR

Y

JOBS_BY_PROJECT

PRIORITY

VARCHAR

N

JOBS_BY_PROJECT

START_TIME

DATE_TIME

Y

JOBS_BY_PROJECT

END_TIME

DATE_TIME

Y

JOBS_BY_PROJECT

QUERY

VARCHAR

Y

JOBS_BY_PROJECT

STATE

VARCHAR

N

JOBS_BY_PROJECT

RESERVATION_ID

VARCHAR

N

JOBS_BY_PROJECT

TOTAL_BYTES_PROCESSED

INT64

Y

JOBS_BY_PROJECT

TOTAL_SLOT_MS

INT64

Y

JOBS_BY_PROJECT

CACHE_HIT

BOOLEAN

N

JOBS_BY_PROJECT

TOTAL_BYTES_BILLED

INT64

Y

JOBS_BY_PROJECT

TRANSACTION_ID

VARCHAR

N

JOBS_BY_PROJECT

PARENT_JOBS_ID

VARCHAR

N

JOBS_BY_PROJECT

TOTAL_MODIFIED_PARTITIONS

INT64

N

JOBS_BY_PROJECT

ERROR_MESSAGE

VARCHAR

Y

JOBS_BY_PROJECT

ERROR_REASON

VARCHAR

Y