or run

tessl search

tessl/golang-cloud-google-com--go--bigquery

tessl install tessl/golang-cloud-google-com--go--bigquery@1.72.0

Google Cloud BigQuery client library providing comprehensive Go APIs for querying, loading data, managing datasets and tables, streaming inserts, and accessing BigQuery's ecosystem of services including Storage, Analytics Hub, Data Transfer, and Migration APIs

Advanced Table Features

This document covers advanced BigQuery table features including partitioning, clustering, encryption, BigLake integration, ML models, and routines.

Table Partitioning

Time Partitioning

type TimePartitioning struct {
    Type                   TimePartitioningType
    Expiration             time.Duration
    Field                  string
    RequirePartitionFilter bool
}

type TimePartitioningType string

const (
    HourPartitioningType  TimePartitioningType = "HOUR"
    DayPartitioningType   TimePartitioningType = "DAY"
    MonthPartitioningType TimePartitioningType = "MONTH"
    YearPartitioningType  TimePartitioningType = "YEAR"
)

Create a partitioned table:

meta := &bigquery.TableMetadata{
    Schema: schema,
    TimePartitioning: &bigquery.TimePartitioning{
        Type:       bigquery.DayPartitioningType,
        Field:      "timestamp",
        Expiration: 90 * 24 * time.Hour, // 90 days
    },
}
err := table.Create(ctx, meta)

Ingestion-Time Partitioning

Partition by when data is loaded (no explicit timestamp field):

meta := &bigquery.TableMetadata{
    Schema: schema,
    TimePartitioning: &bigquery.TimePartitioning{
        Type: bigquery.DayPartitioningType,
        // Field is empty for ingestion-time partitioning
    },
}

Require Partition Filter

meta := &bigquery.TableMetadata{
    Schema: schema,
    TimePartitioning: &bigquery.TimePartitioning{
        Type:  bigquery.DayPartitioningType,
        Field: "created_at",
    },
    RequirePartitionFilter: true,
}

Range Partitioning

type RangePartitioning struct {
    Field string
    Range *RangePartitioningRange
}

type RangePartitioningRange struct {
    Start    int64
    End      int64
    Interval int64
}

Partition by integer column:

meta := &bigquery.TableMetadata{
    Schema: schema,
    RangePartitioning: &bigquery.RangePartitioning{
        Field: "user_id",
        Range: &bigquery.RangePartitioningRange{
            Start:    0,
            End:      1000000,
            Interval: 10000,
        },
    },
}

Clustering

type Clustering struct {
    Fields []string
}

Create a clustered table:

meta := &bigquery.TableMetadata{
    Schema: schema,
    Clustering: &bigquery.Clustering{
        Fields: []string{"country", "product_id", "category"},
    },
}

Combine partitioning and clustering:

meta := &bigquery.TableMetadata{
    Schema: schema,
    TimePartitioning: &bigquery.TimePartitioning{
        Type:  bigquery.DayPartitioningType,
        Field: "timestamp",
    },
    Clustering: &bigquery.Clustering{
        Fields: []string{"user_id", "event_type"},
    },
}

Encryption

Customer-Managed Encryption Keys (CMEK)

type EncryptionConfig struct {
    KMSKeyName string
}

Create encrypted table:

meta := &bigquery.TableMetadata{
    Schema: schema,
    EncryptionConfig: &bigquery.EncryptionConfig{
        KMSKeyName: "projects/my-project/locations/us/keyRings/my-ring/cryptoKeys/my-key",
    },
}
err := table.Create(ctx, meta)

Dataset-level encryption (applies to all new tables):

datasetMeta := &bigquery.DatasetMetadata{
    DefaultEncryptionConfig: &bigquery.EncryptionConfig{
        KMSKeyName: "projects/my-project/locations/us/keyRings/my-ring/cryptoKeys/my-key",
    },
}
err := dataset.Create(ctx, datasetMeta)

Table Expiration

Absolute Expiration

expirationTime := time.Now().Add(30 * 24 * time.Hour) // 30 days

meta := &bigquery.TableMetadata{
    Schema:         schema,
    ExpirationTime: expirationTime,
}

Remove Expiration

var NeverExpire = time.Time{}.Add(-1)

update := bigquery.TableMetadataToUpdate{
    ExpirationTime: optional.ToTime(bigquery.NeverExpire),
}
meta, err := table.Update(ctx, update, "")

BigLake (Apache Iceberg)

BigLakeConfiguration

type BigLakeConfiguration struct {
    ConnectionID string
    StorageURI   string
    FileFormat   BigLakeFileFormat
    TableFormat  BigLakeTableFormat
}

type BigLakeFileFormat string

const (
    UnspecifiedBigLakeFileFormat BigLakeFileFormat = "FILE_FORMAT_UNSPECIFIED"
    ParquetBigLakeFileFormat     BigLakeFileFormat = "PARQUET"
)

type BigLakeTableFormat string

const (
    UnspecifiedBigLakeTableFormat BigLakeTableFormat = "TABLE_FORMAT_UNSPECIFIED"
    IcebergBigLakeTableFormat     BigLakeTableFormat = "ICEBERG"
)

Create BigLake table (Apache Iceberg):

meta := &bigquery.TableMetadata{
    BigLakeConfiguration: &bigquery.BigLakeConfiguration{
        ConnectionID: "projects/my-project/locations/us/connections/my-connection",
        StorageURI:   "gs://my-bucket/iceberg-tables/my_table/",
        FileFormat:   bigquery.ParquetBigLakeFileFormat,
        TableFormat:  bigquery.IcebergBigLakeTableFormat,
    },
}
err := table.Create(ctx, meta)

Reservations

Reservations allow you to purchase dedicated BigQuery compute capacity. Jobs can be configured to use specific reservations.

Reservation Configuration

type ReservationUsage struct {
    Name string
}

The ReservationUsage type is returned in query statistics to show which reservation was used:

status, err := job.Wait(ctx)
if err != nil {
    return err
}

stats := status.Statistics
if stats.ReservationID != "" {
    fmt.Printf("Job used reservation: %s\n", stats.ReservationID)
}

Reservation Editions

type ReservationEdition string

const (
    StandardEdition  ReservationEdition = "STANDARD"
    EnterpriseEdition ReservationEdition = "ENTERPRISE"
    EnterprisePlusEdition ReservationEdition = "ENTERPRISE_PLUS"
)

Reservation editions determine the features available with purchased capacity:

StandardEdition - Standard BigQuery features
EnterpriseEdition - Enterprise features including multi-region deployments
EnterprisePlusEdition - Enterprise Plus features including longest commitment durations

Assigning Jobs to Reservations

Configure jobs to use specific reservations:

// Query with reservation
q := client.Query("SELECT * FROM dataset.table")
q.Reservation = "projects/my-project/locations/US/reservations/my-reservation"
job, err := q.Run(ctx)

// Load job with reservation
loader := table.LoaderFrom(gcsRef)
loader.Reservation = "projects/my-project/locations/US/reservations/my-reservation"
job, err := loader.Run(ctx)

// Copy job with reservation
copier := dstTable.CopierFrom(srcTable)
copier.Reservation = "projects/my-project/locations/US/reservations/my-reservation"
job, err := copier.Run(ctx)

Note: Reservations are managed through the BigQuery Reservation API, which is separate from the main BigQuery client. Use the reservation client from cloud.google.com/go/bigquery/reservation/apiv1 for creating and managing reservations.

ML Models

Model Type

type Model struct {
    ProjectID string
    DatasetID string
    ModelID   string
}

func (d *Dataset) Model(modelID string) *Model
func (m *Model) Metadata(ctx context.Context) (*ModelMetadata, error)
func (m *Model) Update(ctx context.Context, mm ModelMetadataToUpdate, etag string) (*ModelMetadata, error)
func (m *Model) Delete(ctx context.Context) error

ModelMetadata Type

type ModelMetadata struct {
    Name                    string
    Description             string
    Type                    string
    CreationTime            time.Time
    LastModifiedTime        time.Time
    ExpirationTime          time.Time
    Location                string
    EncryptionConfig        *EncryptionConfig
    Labels                  map[string]string
    TrainingRuns            []*TrainingRun
    FeatureColumns          []*StandardSQLField
    LabelColumns            []*StandardSQLField
    ETag                    string
}

Get model metadata:

model := dataset.Model("my_ml_model")
meta, err := model.Metadata(ctx)
if err != nil {
    return err
}

fmt.Printf("Model type: %s\n", meta.Type)
fmt.Printf("Training runs: %d\n", len(meta.TrainingRuns))

ModelMetadataToUpdate Type

type ModelMetadataToUpdate struct {
    Description  *string
    Name         *string
    ExpirationTime time.Time
}

Update model metadata:

import "cloud.google.com/go/internal/optional"

model := dataset.Model("my_ml_model")
update := bigquery.ModelMetadataToUpdate{
    Description: optional.ToString("Updated model description"),
    Name:        optional.ToString("My Updated Model"),
}

meta, err := model.Update(ctx, update, "")
if err != nil {
    return err
}

StandardSQL Types

type StandardSQLDataType struct {
    TypeKind         string
    ArrayElementType *StandardSQLDataType
    StructType       *StandardSQLStructType
    RangeElementType *StandardSQLDataType
}

type StandardSQLStructType struct {
    Fields []*StandardSQLField
}

type StandardSQLField struct {
    Name string
    Type *StandardSQLDataType
}

StandardSQL types are used to define schema for ML model features, labels, and routine parameters:

// Define a routine with StandardSQL types
routine := dataset.Routine("my_function")
meta := &bigquery.RoutineMetadata{
    Type: "SCALAR_FUNCTION",
    Language: "SQL",
    Arguments: []*bigquery.StandardSQLField{
        {
            Name: "input_value",
            Type: &bigquery.StandardSQLDataType{
                TypeKind: "INT64",
            },
        },
        {
            Name: "config",
            Type: &bigquery.StandardSQLDataType{
                TypeKind: "STRUCT",
                StructType: &bigquery.StandardSQLStructType{
                    Fields: []*bigquery.StandardSQLField{
                        {
                            Name: "threshold",
                            Type: &bigquery.StandardSQLDataType{TypeKind: "FLOAT64"},
                        },
                        {
                            Name: "enabled",
                            Type: &bigquery.StandardSQLDataType{TypeKind: "BOOL"},
                        },
                    },
                },
            },
        },
    },
    ReturnType: &bigquery.StandardSQLDataType{
        TypeKind: "STRING",
    },
}

Common TypeKind values:

"INT64", "FLOAT64", "NUMERIC", "BIGNUMERIC"
"BOOL", "STRING", "BYTES"
"DATE", "DATETIME", "TIME", "TIMESTAMP"
"GEOGRAPHY", "JSON"
"ARRAY" (with ArrayElementType specified)
"STRUCT" (with StructType specified)
"RANGE" (with RangeElementType specified)

Model Iterator

func (d *Dataset) Models(ctx context.Context) *ModelIterator

type ModelIterator struct{}

func (it *ModelIterator) Next() (*Model, error)
func (it *ModelIterator) PageInfo() *iterator.PageInfo

List models:

it := dataset.Models(ctx)
for {
    model, err := it.Next()
    if err == iterator.Done {
        break
    }
    if err != nil {
        return err
    }
    fmt.Printf("Model: %s\n", model.ModelID)
}

Training Runs

type TrainingRun struct {
    TrainingOptions      map[string]string
    StartTime            time.Time
    Results              []*IterationResult
    EvaluationMetrics    *EvaluationMetrics
    DataSplitResult      *DataSplitResult
    GlobalExplanations   []*GlobalExplanation
}

Routines (UDFs and Stored Procedures)

Routine Type

type Routine struct {
    ProjectID string
    DatasetID string
    RoutineID string
}

func (d *Dataset) Routine(routineID string) *Routine
func (r *Routine) Create(ctx context.Context, rm *RoutineMetadata) error
func (r *Routine) Metadata(ctx context.Context) (*RoutineMetadata, error)
func (r *Routine) Update(ctx context.Context, rm RoutineMetadataToUpdate, etag string) (*RoutineMetadata, error)
func (r *Routine) Delete(ctx context.Context) error

RoutineMetadata Type

type RoutineMetadata struct {
    Type              string
    Language          string
    Arguments         []*RoutineArgument
    ReturnType        *StandardSQLDataType
    ReturnTableType   *StandardSQLTableType
    ImportedLibraries []string
    Body              string
    Description       string
    DeterminismLevel  DeterminismLevel
    CreatedTime       time.Time
    ModifiedTime      time.Time
    ETag              string
}

const (
    ScalarFunctionRoutine       = "SCALAR_FUNCTION"
    ProcedureRoutine            = "PROCEDURE"
    TableValuedFunctionRoutine  = "TABLE_VALUED_FUNCTION"
)

Create UDF

routine := dataset.Routine("my_udf")
meta := &bigquery.RoutineMetadata{
    Type:     bigquery.ScalarFunctionRoutine,
    Language: "SQL",
    Body:     "x * 2",
    Arguments: []*bigquery.RoutineArgument{
        {
            Name: "x",
            DataType: &bigquery.StandardSQLDataType{
                TypeKind: "INT64",
            },
        },
    },
    ReturnType: &bigquery.StandardSQLDataType{
        TypeKind: "INT64",
    },
}

err := routine.Create(ctx, meta)

Create JavaScript UDF

routine := dataset.Routine("js_udf")
meta := &bigquery.RoutineMetadata{
    Type:     bigquery.ScalarFunctionRoutine,
    Language: "JAVASCRIPT",
    Body: `
        function transform(x) {
            return x.toUpperCase();
        }
        return transform(input);
    `,
    Arguments: []*bigquery.RoutineArgument{
        {
            Name: "input",
            DataType: &bigquery.StandardSQLDataType{
                TypeKind: "STRING",
            },
        },
    },
    ReturnType: &bigquery.StandardSQLDataType{
        TypeKind: "STRING",
    },
}

Create Stored Procedure

routine := dataset.Routine("my_procedure")
meta := &bigquery.RoutineMetadata{
    Type:     bigquery.ProcedureRoutine,
    Language: "SQL",
    Body: `
        DECLARE msg STRING;
        SET msg = CONCAT('Hello, ', name);
        SELECT msg;
    `,
    Arguments: []*bigquery.RoutineArgument{
        {
            Name: "name",
            DataType: &bigquery.StandardSQLDataType{
                TypeKind: "STRING",
            },
        },
    },
}

Routine Iterator

func (d *Dataset) Routines(ctx context.Context) *RoutineIterator

type RoutineIterator struct{}

func (it *RoutineIterator) Next() (*Routine, error)
func (it *RoutineIterator) PageInfo() *iterator.PageInfo

List routines:

it := dataset.Routines(ctx)
for {
    routine, err := it.Next()
    if err == iterator.Done {
        break
    }
    if err != nil {
        return err
    }
    fmt.Printf("Routine: %s\n", routine.RoutineID)
}

Metadata Cache Mode

type MetadataCacheMode string

const (
    AutomaticMetadataCacheMode MetadataCacheMode = "AUTOMATIC"
    ManualMetadataCacheMode    MetadataCacheMode = "MANUAL"
)

Enable metadata caching for external tables:

meta := &bigquery.TableMetadata{
    ExternalDataConfig: &bigquery.ExternalDataConfig{
        SourceFormat:      bigquery.Parquet,
        SourceURIs:        []string{"gs://my-bucket/data/*.parquet"},
        MetadataCacheMode: bigquery.AutomaticMetadataCacheMode,
    },
}

Table Collation

Set default collation for string comparisons:

meta := &bigquery.TableMetadata{
    Schema:           schema,
    DefaultCollation: "und:ci", // Case-insensitive
}

Dataset-level collation:

datasetMeta := &bigquery.DatasetMetadata{
    DefaultCollation: "und:ci",
}

Time Travel

Max Time Travel

Configure time travel window for datasets:

datasetMeta := &bigquery.DatasetMetadata{
    MaxTimeTravel: 7 * 24 * time.Hour, // 7 days
}

Query historical data:

q := client.Query(`
    SELECT *
    FROM ` + "`dataset.table`" + `
    FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
`)

Complete Advanced Example

package main

import (
    "context"
    "fmt"
    "log"
    "time"

    "cloud.google.com/go/bigquery"
    "google.golang.org/api/iterator"
)

func main() {
    ctx := context.Background()
    client, err := bigquery.NewClient(ctx, "my-project")
    if err != nil {
        log.Fatal(err)
    }
    defer client.Close()

    dataset := client.Dataset("analytics")

    // Create advanced table
    createAdvancedTable(ctx, dataset)

    // Create ML model (via query)
    createMLModel(ctx, client, dataset)

    // Create UDF
    createUDF(ctx, dataset)

    // List all models
    listModels(ctx, dataset)

    // List all routines
    listRoutines(ctx, dataset)
}

func createAdvancedTable(ctx context.Context, dataset *bigquery.Dataset) {
    table := dataset.Table("advanced_events")

    schema := bigquery.Schema{
        {Name: "event_id", Type: bigquery.StringFieldType, Required: true},
        {Name: "timestamp", Type: bigquery.TimestampFieldType, Required: true},
        {Name: "user_id", Type: bigquery.IntegerFieldType},
        {Name: "event_type", Type: bigquery.StringFieldType},
        {Name: "country", Type: bigquery.StringFieldType},
        {Name: "revenue", Type: bigquery.NumericFieldType},
        {Name: "metadata", Type: bigquery.JSONFieldType},
    }

    meta := &bigquery.TableMetadata{
        Name:        "Advanced Events Table",
        Description: "Partitioned, clustered, and encrypted event tracking",
        Schema:      schema,

        // Time partitioning
        TimePartitioning: &bigquery.TimePartitioning{
            Type:       bigquery.DayPartitioningType,
            Field:      "timestamp",
            Expiration: 365 * 24 * time.Hour, // 1 year
        },

        // Clustering
        Clustering: &bigquery.Clustering{
            Fields: []string{"country", "event_type"},
        },

        // Encryption
        EncryptionConfig: &bigquery.EncryptionConfig{
            KMSKeyName: "projects/my-project/locations/us/keyRings/my-ring/cryptoKeys/my-key",
        },

        // Require partition filter
        RequirePartitionFilter: true,

        // Labels
        Labels: map[string]string{
            "environment": "production",
            "team":        "analytics",
        },

        // Table expiration
        ExpirationTime: time.Now().Add(2 * 365 * 24 * time.Hour), // 2 years
    }

    if err := table.Create(ctx, meta); err != nil {
        log.Printf("Table creation failed: %v\n", err)
        return
    }

    fmt.Println("Advanced table created successfully")
}

func createMLModel(ctx context.Context, client *bigquery.Client, dataset *bigquery.Dataset) {
    q := client.Query(`
        CREATE OR REPLACE MODEL ` + "`analytics.user_churn_model`" + `
        OPTIONS(
            model_type='LOGISTIC_REG',
            input_label_cols=['churned'],
            max_iterations=10
        ) AS
        SELECT
            user_id,
            country,
            total_sessions,
            avg_session_duration,
            churned
        FROM ` + "`analytics.user_features`" + `
    `)

    job, err := q.Run(ctx)
    if err != nil {
        log.Printf("Model creation query failed: %v\n", err)
        return
    }

    status, err := job.Wait(ctx)
    if err != nil {
        log.Printf("Model creation failed: %v\n", err)
        return
    }

    if err := status.Err(); err != nil {
        log.Printf("Model creation error: %v\n", err)
        return
    }

    fmt.Println("ML model created successfully")
}

func createUDF(ctx context.Context, dataset *bigquery.Dataset) {
    routine := dataset.Routine("calculate_discount")

    meta := &bigquery.RoutineMetadata{
        Type:     bigquery.ScalarFunctionRoutine,
        Language: "SQL",
        Body:     "IF(total >= 100, total * 0.9, total)",
        Arguments: []*bigquery.RoutineArgument{
            {
                Name: "total",
                DataType: &bigquery.StandardSQLDataType{
                    TypeKind: "NUMERIC",
                },
            },
        },
        ReturnType: &bigquery.StandardSQLDataType{
            TypeKind: "NUMERIC",
        },
        Description: "Apply 10% discount for orders over $100",
    }

    if err := routine.Create(ctx, meta); err != nil {
        log.Printf("UDF creation failed: %v\n", err)
        return
    }

    fmt.Println("UDF created successfully")
}

func listModels(ctx context.Context, dataset *bigquery.Dataset) {
    fmt.Println("\nModels in dataset:")
    it := dataset.Models(ctx)
    for {
        model, err := it.Next()
        if err == iterator.Done {
            break
        }
        if err != nil {
            log.Printf("Error listing models: %v\n", err)
            return
        }

        meta, err := model.Metadata(ctx)
        if err != nil {
            continue
        }

        fmt.Printf("  %s (%s)\n", model.ModelID, meta.Type)
    }
}

func listRoutines(ctx context.Context, dataset *bigquery.Dataset) {
    fmt.Println("\nRoutines in dataset:")
    it := dataset.Routines(ctx)
    for {
        routine, err := it.Next()
        if err == iterator.Done {
            break
        }
        if err != nil {
            log.Printf("Error listing routines: %v\n", err)
            return
        }

        meta, err := routine.Metadata(ctx)
        if err != nil {
            continue
        }

        fmt.Printf("  %s (%s, %s)\n", routine.RoutineID, meta.Type, meta.Language)
    }
}

Version

tessl/golang-cloud-google-com--go--bigquery

advanced-features.mddocs/

Advanced Table Features

Table Partitioning

Time Partitioning

Ingestion-Time Partitioning

Require Partition Filter

Range Partitioning

Clustering

Encryption

Customer-Managed Encryption Keys (CMEK)

Table Expiration

Absolute Expiration

Remove Expiration

BigLake (Apache Iceberg)

BigLakeConfiguration

Reservations

Reservation Configuration

Reservation Editions

Assigning Jobs to Reservations

ML Models

Model Type

ModelMetadata Type

ModelMetadataToUpdate Type

StandardSQL Types

Model Iterator

Training Runs

Routines (UDFs and Stored Procedures)

Routine Type

RoutineMetadata Type

Create UDF

Create JavaScript UDF

Create Stored Procedure

Routine Iterator

Metadata Cache Mode

Table Collation

Time Travel

Max Time Travel

Complete Advanced Example

Version

tessl/golang-cloud-google-com--go--bigquery

advanced-features.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Advanced Table Features

Table Partitioning

Time Partitioning

Ingestion-Time Partitioning

Require Partition Filter

Range Partitioning

Clustering

Encryption

Customer-Managed Encryption Keys (CMEK)

Table Expiration

Absolute Expiration

Remove Expiration

BigLake (Apache Iceberg)

BigLakeConfiguration

Reservations

Reservation Configuration

Reservation Editions

Assigning Jobs to Reservations

ML Models

Model Type

ModelMetadata Type

ModelMetadataToUpdate Type

StandardSQL Types

Model Iterator

Training Runs

Routines (UDFs and Stored Procedures)

Routine Type

RoutineMetadata Type

Create UDF

Create JavaScript UDF

Create Stored Procedure

Routine Iterator

Metadata Cache Mode

Table Collation

Time Travel

Max Time Travel

Complete Advanced Example

advanced-features.mddocs/