Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
// Package bigquery is the plugin for BigQuery driver.
package bigquery
import (
"context"
"database/sql"
"errors"
"fmt"
"net"
"time"
"unicode"
"cloud.google.com/go/bigquery"
"google.golang.org/api/iterator"
"google.golang.org/api/option"
"google.golang.org/protobuf/proto"
"google.golang.org/protobuf/types/known/durationpb"
"google.golang.org/protobuf/types/known/structpb"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
"github.com/bytebase/bytebase/backend/plugin/db"
"github.com/bytebase/bytebase/backend/plugin/db/util"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
// Register how this engine plans a limit, for base.StatementWithResultLimit below.
_ "github.com/bytebase/bytebase/backend/plugin/parser/bigquery"
)
var (
_ db.Driver = (*Driver)(nil)
)
func init() {
db.Register(storepb.Engine_BIGQUERY, newDriver)
}
// Driver is the BigQuery driver.
type Driver struct {
config db.ConnectionConfig
connCtx db.ConnectionContext
client *bigquery.Client
// databaseName is the currently connected database name.
databaseName string
}
func newDriver() db.Driver {
return &Driver{}
}
// Open opens a BigQuery driver. It must connect to a specific database.
// If database isn't provided, part of the driver cannot function.
func (d *Driver) Open(ctx context.Context, _ storepb.Engine, config db.ConnectionConfig) (db.Driver, error) {
if config.DataSource.ProjectId == "" {
return nil, errors.New("project ID cannot be empty")
}
d.config = config
d.connCtx = config.ConnectionContext
d.databaseName = config.ConnectionContext.DatabaseName
var o []option.ClientOption
if gcpCredential := config.DataSource.GetGcpCredential(); gcpCredential != nil {
credOption, err := util.GCPCredentialOption([]byte(gcpCredential.Content))
if err != nil {
return nil, err
}
o = append(o, credOption)
}
// host and port optionally override the default bigquery.googleapis.com
// endpoint, e.g. with a Private Service Connect endpoint. A bare host[:port]
// is merged with the default endpoint's scheme and path by the client library.
if host := config.DataSource.Host; host != "" {
endpoint := host
if port := config.DataSource.Port; port != "" {
endpoint = net.JoinHostPort(host, port)
}
o = append(o, option.WithEndpoint(endpoint))
}
client, err := bigquery.NewClient(ctx, config.DataSource.ProjectId, o...)
if err != nil {
return nil, err
}
d.client = client
return d, nil
}
// Close closes the driver.
func (d *Driver) Close(_ context.Context) error {
return d.client.Close()
}
// Ping pings the instance.
func (d *Driver) Ping(ctx context.Context) error {
q := d.client.Query("SELECT 1")
if _, err := q.Read(ctx); err != nil {
return err
}
return nil
}
// GetDB gets the database.
func (*Driver) GetDB() *sql.DB {
return nil
}
// Execute executes a SQL statement.
func (d *Driver) Execute(ctx context.Context, statement string, opts db.ExecuteOptions) (int64, error) {
q := d.client.Query(statement)
q.DefaultDatasetID = d.databaseName
// BigQuery submits the whole sheet as one query job, so the task run log
// carries one command spanning the entire statement, as snowflake does.
// Without these the log holds only the surrounding sync entries and the
// engine's error reaches no reader.
opts.LogCommandExecute(&storepb.Range{Start: 0, End: int32(len(statement))}, statement)
job, err := q.Run(ctx)
if err != nil {
opts.LogCommandResponse(0, nil, err.Error())
return 0, err
}
status, err := job.Wait(ctx)
if err != nil {
opts.LogCommandResponse(0, nil, err.Error())
return 0, err
}
if err := status.Err(); err != nil {
opts.LogCommandResponse(0, nil, err.Error())
return 0, err
}
// NumDMLAffectedRows is 0 for DDL and for a multi-statement script, whose
// parent job reports statementType SCRIPT and carries no row count.
var affectedRows int64
if stats, ok := statisticsOf(status); ok {
affectedRows = stats.NumDMLAffectedRows
}
opts.LogCommandResponse(affectedRows, nil, "")
return affectedRows, nil
}
// statisticsOf returns the query statistics of a finished job.
// JobStatus.Statistics is nil when the jobs.get response carried no statistics
// block (Job.setStatistics returns early on that), so the Details assertion
// alone dereferences nil.
func statisticsOf(status *bigquery.JobStatus) (*bigquery.QueryStatistics, bool) {
if status.Statistics == nil {
return nil, false
}
stats, ok := status.Statistics.Details.(*bigquery.QueryStatistics)
return stats, ok
}
// QueryConn queries a SQL statement in a given connection.
func (d *Driver) QueryConn(ctx context.Context, _ *sql.Conn, statement string, queryContext db.QueryContext) ([]*v1pb.QueryResult, error) {
// For BigQuery, interpret Explain as dry run (BigQuery doesn't support EXPLAIN)
if queryContext.Explain {
return d.dryRunQuery(ctx, statement, queryContext)
}
statements, err := util.SanitizeSQL(statement)
if err != nil {
return nil, err
}
var results []*v1pb.QueryResult
for _, statement := range statements {
startTime := time.Now()
queryResult, err := func() (*v1pb.QueryResult, error) {
if util.IsSelect(statement) {
if queryContext.Limit > 0 {
statement = base.StatementWithResultLimit(storepb.Engine_BIGQUERY, statement, queryContext.Limit, "")
}
q := d.client.Query(statement)
if queryContext.OperatorEmail != "" {
q.Labels = map[string]string{"operator_email": encodeOperatorEmail(queryContext.OperatorEmail)}
}
q.DefaultDatasetID = d.databaseName
it, err := q.Read(ctx)
if err != nil {
return nil, err
}
result := &v1pb.QueryResult{}
readOnce := false
var fieldTypes []bigquery.FieldType
for {
var values []bigquery.Value
err := it.Next(&values)
if err == iterator.Done {
break
}
if err != nil {
return nil, err
}
// Get schema columns.
if !readOnce {
readOnce = true
for _, s := range it.Schema {
result.ColumnNames = append(result.ColumnNames, s.Name)
result.ColumnTypeNames = append(result.ColumnTypeNames, string(s.Type))
fieldTypes = append(fieldTypes, s.Type)
}
}
row := &v1pb.QueryRow{}
for i, v := range values {
row.Values = append(row.Values, convertValue(v, fieldTypes[i]))
}
result.Rows = append(result.Rows, row)
n := len(result.Rows)
if (n&(n-1) == 0) && int64(proto.Size(result)) > queryContext.MaximumSQLResultSize {
result.Error = util.FormatMaximumSQLResultSizeMessage(queryContext.MaximumSQLResultSize)
break
}
}
// The job that returned these rows reports the stages it ran as
// it goes; BigQuery has no EXPLAIN to ask for them instead.
result.QueryPlan = queryPlan(ctx, it.SourceJob())
return result, nil
}
q := d.client.Query(statement)
q.DefaultDatasetID = d.databaseName
job, err := q.Run(ctx)
if err != nil {
return nil, err
}
status, err := job.Wait(ctx)
if err != nil {
return nil, err
}
if err := status.Err(); err != nil {
return nil, err
}
stats, ok := statisticsOf(status)
if !ok {
return nil, errors.New("invalid status statistics detail type")
}
return util.BuildAffectedRowsResult(stats.NumDMLAffectedRows, nil), nil
}()
stop := false
if err != nil {
queryResult = &v1pb.QueryResult{
Error: err.Error(),
}
stop = true
}
queryResult.Statement = statement
queryResult.Latency = durationpb.New(time.Since(startTime))
queryResult.RowsCount = int64(len(queryResult.Rows))
results = append(results, queryResult)
if stop {
break
}
}
return results, nil
}
// dryRunQuery performs a dry run validation of the query without executing it.
// Returns validation status and estimated bytes to be processed.
func (d *Driver) dryRunQuery(ctx context.Context, statement string, queryContext db.QueryContext) ([]*v1pb.QueryResult, error) {
statements, err := util.SanitizeSQL(statement)
if err != nil {
return nil, err
}
var results []*v1pb.QueryResult
for _, stmt := range statements {
startTime := time.Now()
q := d.client.Query(stmt)
q.DefaultDatasetID = d.databaseName
q.DryRun = true
if queryContext.OperatorEmail != "" {
q.Labels = map[string]string{"operator_email": encodeOperatorEmail(queryContext.OperatorEmail)}
}
job, err := q.Run(ctx)
if err != nil {
results = append(results, &v1pb.QueryResult{
Statement: stmt,
Error: err.Error(),
Latency: durationpb.New(time.Since(startTime)),
})
continue
}
// For dry run, the job completes immediately - use Status() instead of Wait()
status := job.LastStatus()
if status == nil {
results = append(results, &v1pb.QueryResult{
Statement: stmt,
Error: "failed to get job status",
Latency: durationpb.New(time.Since(startTime)),
})
continue
}
if err := status.Err(); err != nil {
results = append(results, &v1pb.QueryResult{
Statement: stmt,
Error: err.Error(),
Latency: durationpb.New(time.Since(startTime)),
})
continue
}
// Extract dry run results
result := &v1pb.QueryResult{
Statement: stmt,
Latency: durationpb.New(time.Since(startTime)),
}
if stats, ok := statisticsOf(status); ok {
bytesProcessed := stats.TotalBytesProcessed
// Format output similar to bq CLI
message := fmt.Sprintf(
"Query successfully validated. Assuming the tables are not modified, "+
"running this query will process %d bytes (%.2f MB).",
bytesProcessed,
float64(bytesProcessed)/(1024*1024),
)
result.ColumnNames = []string{"Validation Result"}
result.ColumnTypeNames = []string{"STRING"}
result.Rows = []*v1pb.QueryRow{
{
Values: []*v1pb.RowValue{
{Kind: &v1pb.RowValue_StringValue{StringValue: message}},
},
},
}
result.RowsCount = 1
}
results = append(results, result)
}
return results, nil
}
// encodeOperatorEmail encodes email to valid format of resource label value.
// https://cloud.google.com/compute/docs/labeling-resources#requirements
func encodeOperatorEmail(email string) string {
var values []rune
for _, c := range email {
switch {
case unicode.IsDigit(c) || c == '_' || c == '-':
values = append(values, c)
case unicode.IsLetter(c):
values = append(values, unicode.ToLower(c))
default:
values = append(values, '_')
}
}
if len(values) > 63 {
return string(values[:63])
}
return string(values)
}
func convertValue(v bigquery.Value, fieldType bigquery.FieldType) *v1pb.RowValue {
if v == nil {
return &v1pb.RowValue{Kind: &v1pb.RowValue_NullValue{NullValue: structpb.NullValue_NULL_VALUE}}
}
switch fieldType {
case bigquery.StringFieldType:
if s, ok := v.(string); ok {
return &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{StringValue: s}}
}
case bigquery.BytesFieldType:
if bytes, ok := v.([]byte); ok {
return &v1pb.RowValue{Kind: &v1pb.RowValue_BytesValue{BytesValue: bytes}}
}
case bigquery.IntegerFieldType:
if i, ok := v.(int64); ok {
return &v1pb.RowValue{Kind: &v1pb.RowValue_Int64Value{Int64Value: i}}
}
case bigquery.FloatFieldType:
if f, ok := v.(float64); ok {
return &v1pb.RowValue{Kind: &v1pb.RowValue_DoubleValue{DoubleValue: f}}
}
case bigquery.BooleanFieldType:
if b, ok := v.(bool); ok {
return &v1pb.RowValue{Kind: &v1pb.RowValue_BoolValue{BoolValue: b}}
}
case bigquery.TimestampFieldType:
case bigquery.RecordFieldType:
case bigquery.DateFieldType:
case bigquery.TimeFieldType:
case bigquery.DateTimeFieldType:
case bigquery.NumericFieldType:
case bigquery.GeographyFieldType:
case bigquery.BigNumericFieldType:
case bigquery.IntervalFieldType:
case bigquery.JSONFieldType:
case bigquery.RangeFieldType:
default:
// Unknown field type, will fall back to string representation
}
// Fall back to string representation.
s := fmt.Sprintf("%v", v)
return &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{StringValue: s}}
}