Skip to main content

One post tagged with "load statistics"

View All Tags

· 7 min read

Big Query provides a wealth of metrics and statistics for jobs run against it which could be queries, load jobs or export jobs. This article demonstrates some queries you can run using StackQL to bring back live statistics from load operations into Big Query as well as detail regarding errors encountered during the loading of data into Big Query.

Loading Data into Big Query from GCS using StackQL​

In a previous blog, we demonstrated how to create a Big Query dataset and how to create a Big Query table using StackQL INSERT statements. Having created a target dataset and table in Big Query, we can invoke a load job using StackQL by performing an INSERT into the google.bigquery.jobs resource.

The data for this operation is shown in the Data tab which is supplied in Jsonnet format. 

INSERT INTO google.bigquery.jobs(
  projectId,
  data__configuration
)
SELECT
  'stackql',
  '{{ .configuration }}'
;

Query for Big Query Errors​

The Big Query Job Object can be queried using an StackQL SELECT statement.

To see the available fields with their data types and descriptions, you can run the following StackQL DESCRIBE statement:

DESCRIBE EXTENDED google.bigquery.jobs;

As you can see from running the above command or looking at the API documentation, there is a state field which is an enum showing the state of the job, since we are only concerned with completed jobs we will filter on jobs with a state of DONE. The errorResult field is an object but its presence alone indicates that an error has occurred so we will add another filter to only show results where errorResult is not null.

A simple query to start off with is to count the number of errors, this will be for all job types (load, extract and query): 

SELECT COUNT(*) as num_errors 
FROM google.bigquery.jobs
WHERE projectId = 'stackql'
AND state = 'DONE'
AND errorResult IS NOT null;

To get a little more information about Big Query errors we can run a detailed query, extracting fields from the errorResult object using the JSON_EXTRACT built in function. This function is exceptionally useful as many of the fields returned from Google APIs are complex objects. 

SELECT id, JSON_EXTRACT(errorResult, '$.reason') AS errorReason
FROM google.bigquery.jobs
WHERE projectId = 'stackql'
AND state = 'DONE'
AND errorResult IS NOT null;

Get Big Query Load Specific Errors​

The previous queries returned all errors for all Big Query job types. If we want to narrow our query to just Big Query load operations we can use the Big Query JobStatistics object, which includes fields for each job type.

To refine results to only load operations add the following expression to the WHERE clause: 

AND JSON_EXTRACT(statistics, '$.load') IS NOT null;

Date values returned in job responses are in Unix timestamp format, to format them in a human readable format we can use the DATETIME built in function. Here is a more advanced example: 

SELECT id,
JSON_EXTRACT(errorResult, '$.message') AS errorMessage,
JSON_EXTRACT(errorResult, '$.reason') AS errorReason,
DATETIME(SUBSTR(JSON_EXTRACT(statistics, '$.creationTime'), 1, 10), 'unixepoch') AS creationTime,
DATETIME(SUBSTR(JSON_EXTRACT(statistics, '$.startTime'), 1, 10), 'unixepoch') AS startTime,
DATETIME(SUBSTR(JSON_EXTRACT(statistics, '$.endTime'), 1, 10), 'unixepoch') AS endTime
FROM google.bigquery.jobs
WHERE projectId = 'stackql'
AND state = 'DONE'
AND errorResult IS NOT null
AND JSON_EXTRACT(statistics, '$.load') IS NOT null;

Get Big Query Load Statistics​

Now if you want to query for statistics for Big Query load operations which were successful, we can refine the query using the following conditions: 

WHERE project = 'myproject'
AND state = 'DONE'
AND errorResult IS null
AND JSON_EXTRACT(statistics, '$.load') IS NOT null;

The JobStatistics object for a Big Query load job can be found here: https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobStatistics3. Let's run an StackQL query to return all of the statistics for load jobs run in a given GCP project. 

SELECT id,
DATETIME(SUBSTR(JSON_EXTRACT(statistics, '$.creationTime'), 1, 10), 'unixepoch') AS creationTime,
DATETIME(SUBSTR(JSON_EXTRACT(statistics, '$.startTime'), 1, 10), 'unixepoch') AS startTime,
DATETIME(SUBSTR(JSON_EXTRACT(statistics, '$.endTime'), 1, 10), 'unixepoch') AS endTime,
JSON_EXTRACT(statistics, '$.load.inputFiles') AS inputFiles,
JSON_EXTRACT(statistics, '$.load.inputFileBytes') AS inputFileBytes,
JSON_EXTRACT(statistics, '$.load.outputRows') AS outputRows,
JSON_EXTRACT(statistics, '$.load.outputBytes') AS outputBytes,
JSON_EXTRACT(statistics, '$.load.badRecords') AS badRecords
FROM google.bigquery.jobs
WHERE projectId = 'stackql'
AND state = 'DONE'
AND errorResult IS null
AND json_extract(statistics, '$.load') IS NOT null;

In future posts we will show similar examples using StackQL to query for errors and statistics for extract and query jobs in Big Query, see you then!