bigqueryutil

README

BigQueryUtil

A Golang library for building an SQL query based on a filter represented by a given structure.

---

Table of Contents

• 1. Description
• 2. Technology Stack
• 3. Getting Started
• 4. Changelog
• 5. Collaborators
• 6. Contributing
• 7. Versioning
• 8. License
• 9. Contact Information

1. Description

BigQueryUtil is a library to build a SQL query and Big Query Parameters based on provided columns, a query template and a filter.

2. Technology Stack

Stack	Version
Golang	v1.18
golangci-lint	v1.45

3. Getting Started

• Prerequisites

  • Any Golang programming language version installed, preferred 1.18 or later.

• Install

  go get -u github.com/arquivei/bigqueryutil
  

• Configuration Setup

  go mod vendor
  go mod tidy
  

• Usage

  • Import the package

    import (
        "github.com/arquivei/bigqueryutil"
    )
    

  • Instantiate the QueryBuilderSpec struct that represents the spec for the query builder

    var queryBuilderExample = bigqueryutil.QueryBuilderSpec{
      RepeatedColumns: map[string]struct{}{
        "AccessKey": {},
        "Owner":     {},
      },
      SQLQuery: "SELECT * EXCEPT(r) FROM (SELECT %s, " +
        "ROW_NUMBER() OVER (PARTITION BY AccessKey, Owner order by Version desc) r " +
        "FROM %s WHERE %s) WHERE r = 1;",
    }
    

  • Declare the TimeRange struct that represents a time with a beginning and an end.

    var TimeRangeExample = bigqueryutil.TimeRange{
      From: time.Date(2022, 1, 1, 0, 0, 0, 0, time.UTC),
      To:   time.Date(2022, 2, 1, 0, 0, 0, 0, time.UTC),
    }
    

  • Declare and instantiante the struct that represents a filter for a query with Big Query's tags.

    type filter struct {
      Namespace string                  `bq:",omitempty"`
      CreatedAt *bigqueryutil.TimeRange `bq:",omitempty"`
      Owners    []string                `bq:"Owner,omitempty"`
      IsTaker   *bool                   `bq:",omitempty"`
    }
    
    var filterExample = filter{
      Namespace: "namespace",
      CreatedAt: &TimeRangeExample,
      Owners:    []string{"owner1", "owner2"},
      IsTaker:   ref.Bool(false),
    }
    

  • Declare the projection fields that will be required field on the HTTP API.

    var projectionExample = []string{"AccessKey"}
    

  • Instantiate the BuildColumnsClause that will return the string that represents the columnsClauseBuilder with all columns required by projection, or "*" if projection is nil.

    returnedColumns := bigqueryutil.BuildColumnsClause(queryBuilderExample,projectionExample)
    

  • Instantiate the EncodeBigqueryWhereClause that transforms a struct into a bigquery's query and parameters list.

    whereExample, queryParametersExample, err := bigqueryutil.EncodeBigqueryWhereClause(filterExample)
    if err != nil {
      panic(err)
    }
    

  • These parameters will be passed to Big Query and will be used in the query.

    fmt.Printf("Big Query Parameters: \n%+v\n\n", queryParametersExample)
    

  • The query is the string that will be used in the BigQuery.

    sqlQuery := fmt.Sprintf(queryBuilderExample.SQLQuery, returnedColumns, "`TABLE_EXAMPLE`", whereExample)
    fmt.Printf("Sql Query: \n%+v\n", sqlQuery)
    

  • Output

    /*
       Big Query Parameters:
       [{Name:Namespace Value:namespace} {Name:CreatedAtFrom Value:2022-01-01T00:00:00Z} {Name:CreatedAtTo Value:2022-02-01T00:00:00Z} {Name:Owner0 Value:owner1} {Name:Owner1 Value:owner2}]
    
       Sql Query:
       SELECT * EXCEPT(r) FROM (SELECT AccessKey, ROW_NUMBER() OVER (PARTITION BY AccessKey, Owner order by Version desc) r FROM `TABLE_EXAMPLE` WHERE Namespace = @Namespace AND CreatedAt BETWEEN @CreatedAtFrom AND @CreatedAtTo AND Owner IN (@Owner0,@Owner1) AND NOT IsTaker) WHERE r = 1;
    */    
    

• Examples

  • Sample usage

4. Changelog

• bigqueryutil 0.1.0 (DATE)

  • [New] Decoupling this package from Arquivei's API projects.
  • [New] Setting github's workflow with golangci-lint
  • [New] Example for usage.
  • [New] Documents: Code of Conduct, Contributing, License and Readme.

5. Collaborators

• Authors

  Victor Nunes

  Rodrigo Fonseca

• Maintainers

  Rilder Almeida

6. Contributing

Please read CONTRIBUTING.md for details on our code of conduct, and the process for submitting pull requests to us.

7. Versioning

We use Semantic Versioning for versioning. For the versions available, see the tags on this repository.

8. License

This project is licensed under the BSD 3-Clause - see the LICENSE.md file for details.

9. Contact Information

Contacts can be made by email: rilder.almeida@arquivei.com.br

Documentation

Index

• func AppendParam(params []bigquery.QueryParameter, name string, value interface{}) []bigquery.QueryParameter
• func BuildColumnsClause(spec QueryBuilderSpec, projection []string) string
• func EncodeBigqueryWhereClause(filter interface{}) (string, []bigquery.QueryParameter, error)
• type QueryBuilderSpec
• type TimeRange

Functions

func AppendParam

func AppendParam(
	params []bigquery.QueryParameter,
	name string,
	value interface{},
) []bigquery.QueryParameter

AppendParam append parameters for given @params.

func BuildColumnsClause

func BuildColumnsClause(spec QueryBuilderSpec, projection []string) string

BuildColumnsClause builds a column clause.

func EncodeBigqueryWhereClause

func EncodeBigqueryWhereClause(filter interface{}) (string, []bigquery.QueryParameter, error)

EncodeBigqueryWhereClause transforms a struct into a bigquery's query and parameters list.

struct {
	Namespace               string                 	`bq:",omitempty"`
	CreatedAt               *TimeRange 			   	`bq:",omitempty"`
	EmissionDate            *TimeRange 			   	`bq:",omitempty"`
	EmissionDateWithoutTime *TimeRange 			   	`bq:",omitempty" format:"2006-01-02"`
	Owners                  []string               	`bq:"Owner,omitempty"`
	OwnerRoles              []string               	`bq:",unnest,omitempty"`
	IsTaker                 *bool                  	`bq:",omitempty"`
}

Types

type QueryBuilderSpec

type QueryBuilderSpec struct {
	RepeatedColumns map[string]struct{}
	SQLQuery        string
}

QueryBuilderSpec represents the spec for the query builder.

type TimeRange

type TimeRange struct {
	From time.Time
	To   time.Time
}

TimeRange represents a time with a beginning and an end.