Skip to main content

5 posts tagged with "bigquery"

View All Tags

Google Provider Update - December 2025

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

We've released a major update to the StackQL Google provider with a new service, enhanced AI/ML capabilities, and improvements across 177 service files.

New Service: Speech-to-Text v2​

The speechv2 service brings Cloud Speech-to-Text API v2 to StackQL with 6 resources:

ResourceDescription
recognizersManage speech recognition configurations with create, list, get, patch, delete, undelete, recognize, and batch_recognize methods
custom_classesCreate custom vocabulary classes for improved recognition accuracy
phrase_setsDefine phrase hints to boost recognition of specific terms
configManage location-level Speech-to-Text configuration
locationsQuery available service locations
operationsTrack long-running operations

Key features include support for multiple audio encodings (WAV, FLAC, MP3, OGG, WebM, MP4/AAC), translation capabilities, denoiser config, and KMS encryption support.

Vertex AI / AI Platform​

The largest update in this release with 87,000+ line changes introduces powerful new RAG and evaluation capabilities:

  • RAG Resources: rag_corpora, rag_files, rag_engine_config for Retrieval-Augmented Generation
  • Conversational AI: New chat resource
  • Model Evaluation: evaluation_sets and evaluation_items for systematic model assessment
  • New Resources: science, invoke, and openapi resources
  • Performance: Enhanced cache_config for caching configurations

Discovery Engine​

Major enhancements (50,000+ line changes) for search and conversational AI:

  • New assistants resource
  • New sitemaps resource for site search
  • New custom_models resource
  • Enhanced sessions and answers for conversational search
  • New authorized_views and authorized_view_sets for access control

Contact Center AI Insights​

Quality assurance and analytics improvements (20,000+ line changes):

  • New qa_questions and qa_question_tags for quality assurance workflows
  • New analysis_rules resource
  • New segments resource
  • New authorized_views with IAM policy support
  • New datasets and views resources

BigQuery​

Enhanced governance and access control (18,000+ line changes):

  • New routines_iam_policies for stored procedure/function IAM
  • Enhanced row_access_policies

Healthcare API​

Expanded metrics and data mapping (15,000+ line changes):

  • New data_mapper_workspaces_iam_policies
  • Enhanced metrics: hl7_v2_store_metrics, dicom_store_metrics, series_metrics, study_metrics
  • New instances_storage_info resource

Cloud Spanner​

Backup and security enhancements (14,000+ line changes):

  • New backup_schedules with IAM support
  • New databases_split_points resource
  • New database_roles with IAM policies

Cloud SQL Admin​

New integration and management features (12,000+ line changes):

  • New instances_entra_id_certificate for Microsoft Entra ID integration
  • New instances_disk_shrink_config
  • New instances_latest_recovery_time

GKE On-Prem​

Enhanced IAM across VMware and Bare Metal clusters (9,000+ line changes):

  • Enhanced VMware cluster resources with IAM policies
  • Enhanced Bare Metal cluster resources with IAM policies
  • New vmware_node_pools and bare_metal_node_pools with IAM

Developer Connect​

Git integration improvements (3,500+ line changes):

  • New git_repository_links_git_refs resource
  • New users_self and users_access_token resources
  • New token resources: read_token, read_write_token

Text-to-Speech​

Enhanced voices and text resources with new capabilities.

Get Started​

Update to the latest Google provider:

stackql registry pull google

Let us know your thoughts! Visit us and give us a star on GitHub.

Big Query Cost Analysis using StackQL

· 2 min read
Jeffrey Aven
Technologist and Cloud Consultant

Queries (particularly) repetitive queries that don't take advantage of results caching can lead to extraordinarily high bills.

StackQL, with it's backend SQL engine, allows you to query Big Query statistics in real time, including identifying queries which are not served from cache and understanding billable charges per query or time slice.

Here is a simple query to break down a time period into hours and show the total queries, queries served from cache and the total query charges per hour.

SELECT
 STRFTIME('%H', DATETIME(SUBSTR(JSON_EXTRACT(statistics, '$.startTime'), 1, 10), 'unixepoch')) as hour,
 COUNT(*) as num_queries,
 SUM(JSON_EXTRACT(statistics, '$.query.cacheHit')) as using_cache,
 SUM(JSON_EXTRACT(statistics, '$.query.totalBytesBilled')*{{ .costPerByte }} ) as queryCost
FROM google.bigquery.jobs
 WHERE projectId = '{{ .projectId }}'
  AND allUsers = 'true'
  AND minCreationTime = '{{ .minCreationTime }}'
  AND maxCreationTime = '{{ .maxCreationTime }}'
  AND state = 'DONE'
  AND JSON_EXTRACT(statistics, '$.query') IS NOT null
GROUP BY STRFTIME('%H', datetime(SUBSTR(JSON_EXTRACT(statistics, '$.startTime'), 1, 10), 'unixepoch'));

Many more examples to come, including using this data to create visualisations in a Jupyter notebook, stay tuned!

Querying Big Query Errors and Load Stats

· 8 min read
Jeffrey Aven
Technologist and Cloud Consultant

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!

Enable Logging for Google Cloud Storage Buckets and Analyzing Logs in Big Query (Part II)

· 4 min read
Jeffrey Aven
Technologist and Cloud Consultant

In the previous post, we showed you how to enable usage and storage logging for GCS buckets. Now that we have enabled logging, let's load and analyze the logs using Big Query. We will build up a data file vars.jsonnet as we go and show the queries step by step, at the end we will show how to run this as one batch using StackQL.

Step 1 : Create a Big Query dataset​

We will need a dataset (akin to a schema or a database in other RDMBS parlance), basically a container for objects such as tables or views, the data and code to do this are shown here:

INSERT INTO google.bigquery.datasets(
  projectId,
  data__location,
  data__datasetReference,
  data__description,
  data__friendlyName
)
SELECT
  '{{ .projectId }}',
  '{{ .location }}',
  '{ "datasetId": "{{ .datasetId }}", "projectId": "{{ .projectId }}" }',
  '{{ .description }}',
  '{{ .friendlyName }}'
;

Step 2 : Create usage table​

Let's use StackQL to create a table named usage to host the GCS usage logs, the schema for the table is defined in a file named cloud_storage_usage_schema_v0.json which can be downloaded from the location provided, for reference this is provided in the Table Schema tab in the example provided below:

/* create_table.iql */

INSERT INTO google.bigquery.tables(
  datasetId,
  projectId,
  data__description,
  data__friendlyName,
  data__tableReference,
  data__schema
)
SELECT
  '{{ .datasetId }}',
  '{{ .projectId }}',
  '{{ .table.usage.description }}',
  '{{ .table.usage.friendlyName }}',
  '{"projectId": "{{ .projectId }}", "datasetId": "{{ .datasetId }}", "tableId": "{{ .table.usage.tableId }}"}',
  '{{ .table.usage.schema }}'
;

Run the following to execute the StackQL command with the input data shown:

stackql exec -i ./create_table.iql --iqldata ./vars.jsonnet

Step 3 : Load the usage data​

We have a Big Query dataset and a table, lets load some data. To do this we will need to create and submit a load job, we can do this by inserting into the google.bigquery.jobs resource as shown here:

/* bq_load_job.iql */

INSERT INTO google.bigquery.jobs(
  projectId,
  data__configuration
)
SELECT
  'stackql',
  '{
    "load": {
      "destinationTable": {
        "projectId": "{{ .projectId }}",
        "datasetId": "{{ .datasetId }}",
        "tableId": "{{ .table.usage.tableId }}"
      },
      "sourceUris": [
        "gs://{{ .logs_bucket }}/{{ .object_prefix }}"
      ],
      "schema": {{ .table.usage.schema }},
	  "skipLeadingRows": 1,
      "maxBadRecords": 0,
      "projectionFields": []
    }
  }'
;

Run the following to execute:

stackql exec -i ./bq_load_job.iql --iqldata ./vars.jsonnet

Clean up (optional)​

If you want to clean up what you have done, you can do so using StackQL DELETE statements, as provided below:

NOTE: To delete a Big Query dataset, you need to delete all of the tables contained in the dataset first, as shown in the following example

-- delete table(s) 

DELETE FROM google.bigquery.tables 
WHERE projectId = '{{ .projectId }}' 
AND datasetId = '{{ .datasetId }}' 
AND tableId = '{{ .table.usage.tableId }}';

-- delete dataset

DELETE FROM google.bigquery.datasets 
WHERE projectId = '{{ .projectId }}' 
AND datasetId = '{{ .datasetId }}';

Enable Logging for Google Cloud Storage Buckets and Analyzing Logs in Big Query (Part I)

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

In a previous article, Deploying and Querying GCS Buckets using StackQL, we walked through some basic creation and query operations on Google Cloud Storage buckets. In this post we will extend on this by enabling logging on a GCS bucket using StackQL. This post is based upon this article: Usage logs & storage logs.

Assuming we have deployed a bucket which we want to log activities on, follow the steps below:

Step 1 : Create a bucket to store the usage logs​

One bucket in a project can be used to collect the usage logs from one or more other buckets in the project. Use the StackQL Command Shell (stackql shell) or stackql exec to create this logs bucket as shown here:

INSERT INTO google.storage.buckets(
  project,
  data__name,
  data__location,
  data__locationType
)
SELECT
  'stackql',
  'stackql-download-logs',
  'US',
  'multi-region'
;

for more examples of creating Google Cloud Storage buckets using StackQL, see Deploying and Querying GCS Buckets using StackQL.

Step 2: Set IAM policy for the logs bucket​

You will need to create an IAM binding to enable writes to this bucket, do this by using the setIamPolicy method as shown here:

EXEC google.storage.buckets.setIamPolicy
@bucket = 'stackql-download-logs'
@@json = '{
  "bindings":[
    {
      "role": "roles/storage.legacyBucketWriter",
      "members":[
        "group:cloud-storage-analytics@google.com"
      ]
    }
  ]
}';

TIP: you should also add role bindings to the roles/storage.legacyBucketOwner role for serviceAccount or users who will be running StackQL SELECT queries against this logs bucket.

Step 3: Enable logging on the target bucket​

To enable logging on your target bucket (or buckets) run the following StackQL EXEC method:

EXEC google.storage.buckets.patch
@bucket = 'stackql-downloads'
@@json = '{
 "logging": {
  "logBucket": "stackql-download-logs",
  "logObjectPrefix": "stackql_downloads"
 }
}';

TIP: use SHOW METHODS IN google.storage.buckets; to see what operations are avaialable such as the patch and setIamPolicy examples shown in the previous steps.

Step 4: Check logging status on target bucket​

To see that logging has been enabled run the StackQL query below:

select name, logging
from google.storage.buckets
WHERE project = 'stackql'
and logging is not null;

To unpack the logging object, you can use the [JSON_EXTRACT]](/docs/language-spec/functions/json/json_extract) built in function as shown here:

select name, json_extract(logging, '$.logBucket') as logBucket,
json_extract(logging, '$.logObjectPrefix') as logObjectPrefix
from google.storage.buckets
WHERE project = 'stackql'
and logging is not null;

In Part II of this post, we will demonstrate how to create a Big Query dataset, then load and analyze the GCS usage logs you have collected using Big Query, stay tuned!