espresso - a framework for testing BigQuery queries

Related tags

testing bigquery sql
Overview

CI codecov Go Report Card GoDoc Docker Image Version

espresso - a framework for testing BigQuery queries

Goals

  • Componentization: compose complex queries from smaller, reusable components
  • Test driven development: write tests for each query and run them like unit tests (except for the fact that they make calls to BigQuery)
  • Data as code: input and required output for tests can be defined as part of the code (as well as in real database tables)
  • No new languages to learn
  • Write tests in your own development stack: programming language, IDE and CI/CD pipeline

Writing Your Own SQL Tests

  1. Write an SQL query using Go Text Template notation, for example:

    {{ define "fruit" }}
    
    WITH base AS (
        {{ .Base }}
    )
    SELECT
        fruit
    FROM base
    
    {{ end }}

    The query may contain parameters, like {{ .Base }}

  2. Add additional SQL queries to pass as arguments to the main query, for example:

    {{ define "base" }}
    
    SELECT
        "orange" AS fruit
    UNION ALL
    SELECT
        "apple"
    
    {{ end }}
  3. Write your expected result query, for example:

    {{ define "fruit_result" }}
    
    SELECT
        "orange" AS fruit
    UNION ALL
    SELECT
        "apple"
    
    {{ end }}
  4. Create a query definition file describing your query and one or more tests, for example:

    Name: fruit
    Requires:
    - Base
    Tests:
      Test1:
        Args:
        - Name: Base
          Source: base
        Result:
          Source: fruit_result
  5. Put all files together in a directory

Query Definitions

The query definition file specifies how to construct an SQL query from the SQL templates.
A definition file contains Tests, each specifying how to construct an SQL query and an optional expected result.
To create a query from its components, espresso looks for an SQL template with the same name as the definition file itself, and then executes it with the given Args.
Each Arg must have a Name field that corresponds to a required argument in the SQL template and one of the following fields:

  1. Source - Another SQL template which will be executed and injected into the containing query.
  2. Table - a table name which will be combined with the Google project and BigQuery dataset (defined in Shot) and injected into the containing query.
  3. Const - a string that will be injected into the containing query.

Source may contain its own Args. If it doesn't, espresso will look for a corresponding template file with a test of the same name and parse the args from there.

Result is an optional field specifiying the test's expected result. It should have a Source field specifying an SQL template that will be executed to generate the expected result query. Source may contain Args as described above.

Access To BigQuery

The tests require access to BigQuery API. Please set the following environment variables to grant espresso access to BigQuery:

  • export GCLOUD_PROJECT_ID=
  • export BIGQUERY_KEY=

Running Tests From The Command-line

go build
./espresso -dir="./shot/queries/fruit/" -query="fruit" -test="Test1"

Running Tests From Docker

docker run --rm -t -e GCLOUD_PROJECT_ID=$GCLOUD_PROJECT_ID -e BIGQUERY_KEY=$BIGQUERY_KEY -v $(pwd)/shot:/shot:ro tufin/espresso -dir="/shot" -query="fruit" -test="Test1"

Running Tests From Golang

  1. Embed your tests directory
  2. Create an "Espresso Shot" and run it
  3. Use standard Go assertions to check the expected result against the actual output
func TestEspressoShot_Filesystem(t *testing.T) {
	queryValues, resultValues, err := shot.NewShotWithClient(env.GetGCPProjectID(), "", os.DirFS("./queries/fruit")).RunTest("fruit", "Test1", []bigquery.QueryParameter{}, &map[string]bigquery.Value{})
	require.NoError(t, err)
	require.ElementsMatch(t, queryValues, resultValues)
}

You can also embed the SQL templates directory into the code:

//go:embed queries/fruit
var templates embed.FS

func TestEspressoShot_Embed(t *testing.T) {
	queryValues, resultValues, err := shot.NewShotWithClient(env.GetGCPProjectID(), "", templates).RunTest("fruit", "Test1", []bigquery.QueryParameter{}, &map[string]bigquery.Value{})
	require.NoError(t, err)
	require.ElementsMatch(t, queryValues, resultValues)
}

Running Tests In Other Programming Languages

Currently only Go is supported. If you'd like to contribute additional language support, please start a dicssussion.

Current Status

  • This is an initial proof-of-concept and request-for-comments
  • Please submit your feedback as pull requests, issues or discussions.
Issues
  • Bump google.golang.org/api from 0.57.0 to 0.58.0

    Bump google.golang.org/api from 0.57.0 to 0.58.0

    Bumps google.golang.org/api from 0.57.0 to 0.58.0.

    Release notes

    Sourced from google.golang.org/api's releases.

    google-api-go-client v0.58.0

    Features

    Changelog

    Sourced from google.golang.org/api's changelog.

    0.58.0 (2021-09-28)

    Features

    Commits

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies 
    opened by dependabot[bot] 1
  • Bump cloud.google.com/go/bigquery from 1.8.0 to 1.22.0

    Bump cloud.google.com/go/bigquery from 1.8.0 to 1.22.0

    Bumps cloud.google.com/go/bigquery from 1.8.0 to 1.22.0.

    Release notes

    Sourced from cloud.google.com/go/bigquery's releases.

    spanner spanner/v1.22.0

    Features

    • spanner: support request and transaction tags (#4336) (f08c73a)
    • spanner: enable request options for batch read (#4337) (b9081c3)

    bigquery bigquery/v1.22.0

    Features

    • bigquery/storage/managedwriter/adapt: add NormalizeDescriptor (#4681) (c54aa74)
    • bigquery/storage/managedwriter: more metrics instrumentation (#4690) (9505384)

    spanner spanner/v1.21.0

    Miscellaneous Chores

    • spanner: trigger a release for low cost instance (#4264) (24c4451)

    bigquery bigquery/v1.21.0

    Features

    • bigquery/storage/managedwriter: add project autodetection (#4605) (d8cc9be)
    • bigquery/storage/managedwriter: improve protobuf support (#4589) (a455082)
    • bigquery/storage/managedwriter: more instrumentation support (#4601) (ff488c8)
    • bigquery: switch to centralized project autodetect logic (#4625) (18ff070)

    Bug Fixes

    • bigquery/storage/managedwriter: support non-default regions (#4566) (68418f9)

    bigquery bigquery/v1.20.1

    Bug Fixes

    • bigquery/storage/managedwriter: fix flowcontroller double-release (#4555) (67facd9)

    spanner spanner/v1.20.0

    Features

    ... (truncated)

    Commits
    • 6e1be41 chore: release spanner 1.22.0 (#4339)
    • b2a6171 fix(firestore): correct an issue with returning empty paritions from GetParti...
    • f31fac6 fix(pubsublite)!: hide CreateSubscriptionOption.apply (#4344)
    • 63fdb2e chore(storage): generate GAPIC under internal subdir (#4018)
    • a89d341 chore(internal/gapicgen): gen googleapis-discovery compute gapic (#4307)
    • e4a5791 chore(internal): update microgen to v0.21.2 (#4315)
    • 949a953 chore(all): update all (#4326)
    • 3604ef2 chore: release bigquery 1.19.0 (#4101)
    • ef8d138 fix(bigquery): minor rename to feature that's not yet in a release (#4320)
    • f32dd72 feat(logging)!: increase DefaultEntryByteThreshold to 8Mb (#4247)
    • Additional commits viewable in compare view

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies 
    opened by dependabot[bot] 0
  • Bump google.golang.org/api from 0.56.0 to 0.57.0

    Bump google.golang.org/api from 0.56.0 to 0.57.0

    Bumps google.golang.org/api from 0.56.0 to 0.57.0.

    Release notes

    Sourced from google.golang.org/api's releases.

    google-api-go-client v0.57.0

    Features

    Changelog

    Sourced from google.golang.org/api's changelog.

    0.57.0 (2021-09-16)

    Features

    Commits

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies 
    opened by dependabot[bot] 0
  • Bump cloud.google.com/go/bigquery from 1.22.0 to 1.23.0

    Bump cloud.google.com/go/bigquery from 1.22.0 to 1.23.0

    Bumps cloud.google.com/go/bigquery from 1.22.0 to 1.23.0.

    Release notes

    Sourced from cloud.google.com/go/bigquery's releases.

    spanner spanner/v1.23.0

    Features

    • spanner/admin/database: add leader_options to InstanceConfig and default_leader to Database (7aa0e19)

    bigquery bigquery/v1.23.0

    Features

    • bigquery/reservation:
      • Deprecated SearchAssignments in favor of SearchAllAssignments
      • feat: Reservation objects now contain a creation time and an update time
      • feat: Added commitment_start_time to capacity commitments
      • feat: Force deleting capacity commitments is allowed while reservations with active assignments exist
      • feat: ML_EXTERNAL job type is supported
      • feat: Optional id can be passed into CreateCapacityCommitment and CreateAssignment
      • docs: Clarified docs for None assignments
      • fix!: Fixed pattern for BiReservation object BREAKING_CHANGE: Changed from bireservation to biReservation
      • (d9ce9d0)
    • bigquery/storage/managedwriter: BREAKING CHANGE: changeAppendRows behavior (#4729)
    • bigquery/storage: add BigQuery Storage Write API v1 (e52c204)
    • bigquery/storage: migrate managedwriter to v1 write from v1beta2 (#4788)
    • bigquery: add session and connection support (#4754) (e846dfd)
    • bigquery: expose the query source of a rowiterator via SourceJob() (#4748)
    Commits

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies 
    opened by dependabot[bot] 0
  • Bump cloud.google.com/go/bigquery from 1.23.0 to 1.24.0

    Bump cloud.google.com/go/bigquery from 1.23.0 to 1.24.0

    Bumps cloud.google.com/go/bigquery from 1.23.0 to 1.24.0.

    Release notes

    Sourced from cloud.google.com/go/bigquery's releases.

    spanner spanner/v1.24.0

    Features

    • spanner/spansql: add ROW DELETION POLICY parsing (#4496) (3d6c6c7)
    • spanner/spansql: fix unstable SelectFromTable SQL (#4473) (39bc4ec)
    • spanner/spansql: support ALTER DATABASE (#4403) (1458dc9)
    • spanner/spansql: support table_hint_expr at from_clause on query_statement (#4457) (7047808)
    • spanner: add row.String() and refine error message for decoding a struct array (#4431) (f6258a4)
    • spanner: allow untyped nil values in parameterized queries (#4482) (c1ba18b)

    Bug Fixes

    • spanner/spansql: fix DATE and TIMESTAMP parsing. (#4480) (dec7a67)

    bigquery bigquery/v1.24.0

    Features

    • bigquery/migration: Add PAUSED state to Subtask and add task details protos (bddab08)

    Bug Fixes

    • bigquery/storage: add missing read api retry setting on SplitReadStream (797a9bd)
    Commits
    • eabc63a chore: release spanner 1.24.0 (#4413)
    • a3e97da chore: release 0.89.0 (#4486)
    • 9401be5 fix(internal/gapicgen): tidy all after dep bump (#4515)
    • a52baa4 chore(all): auto-regenerate gapics (#4510)
    • 663c899 feat(bigquery/storage/managedwriter): naming and doc improvements (#4508)
    • 41246e9 fix(internal/gapicgen): exec Stdout already set (#4509)
    • d8ec92b test(bigquery/storage/managedwriter): test all stream types (#4507)
    • c6cf659 fix(bigquery/storage/managedwriter): fix double-close error, add tests (#4502)
    • 9923fd1 chore(all): auto-regenerate gapics (#4499)
    • f2d531d feat(storagetransfer): start generating apiv1 (#4505)
    • Additional commits viewable in compare view

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies 
    opened by dependabot[bot] 0
  • Bump google.golang.org/api from 0.58.0 to 0.59.0

    Bump google.golang.org/api from 0.58.0 to 0.59.0

    Bumps google.golang.org/api from 0.58.0 to 0.59.0.

    Release notes

    Sourced from google.golang.org/api's releases.

    google-api-go-client v0.59.0

    Features

    Changelog

    Sourced from google.golang.org/api's changelog.

    0.59.0 (2021-10-20)

    Features

    Commits

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies 
    opened by dependabot[bot] 0
Owner
Tufin
The Security Policy Company
Tufin
A generic fuzzing and delta-debugging framework

Tavor Tavor (Sindarin for woodpecker) is a framework for easily implementing and using fuzzing and delta-debugging. Its EBNF-like notation allows you

Markus Zimmermann 233 Oct 1, 2021
Datastore Testibility

Datastore Testibility (dsunit) This library is compatible with Go 1.10+ Please refer to CHANGELOG.md if you encounter breaking changes. Introduction M

Viant, Inc 39 Oct 17, 2021
Testing API Handler written in Golang.

Gofight API Handler Testing for Golang Web framework. Support Framework Http Handler Golang package http provides HTTP client and server implementatio

Bo-Yi Wu 394 Oct 12, 2021
Microservice Test Framework

This Microservice Test Framework (MTF) allows in simple way to mock service dependencies and setup docker test environment comprehensive.

Marek Smoliński 73 May 21, 2021
BDD Testing Framework for Go

Jump to the docs | 中文文档 to learn more. To start rolling your Ginkgo tests now keep reading! If you have a question, comment, bug report, feature reque

Onsi Fakhouri 5.2k Oct 24, 2021
Mutation testing for Go source code

go-mutesting go-mutesting is a framework for performing mutation testing on Go source code. Its main purpose is to find source code, which is not cove

Markus Zimmermann 475 Oct 21, 2021
A toolkit with common assertions and mocks that plays nicely with the standard library

Testify - Thou Shalt Write Tests ℹ️ We are working on testify v2 and would love to hear what you'd like to see in it, have your say here: https://cutt

Stretchr, Inc. 14.6k Oct 24, 2021
Fortio load testing library, command line tool, advanced echo server and web UI in go (golang). Allows to specify a set query-per-second load and record latency histograms and other useful stats.

Fortio Fortio (Φορτίο) started as, and is, Istio's load testing tool and now graduated to be its own project. Fortio is also used by, among others, Me

Fortio (Φορτίο) 2.1k Oct 15, 2021
End-to-end HTTP and REST API testing for Go.

httpexpect Concise, declarative, and easy to use end-to-end HTTP and REST API testing for Go (golang). Basically, httpexpect is a set of chainable bui

Victor Gaydov 1.8k Oct 21, 2021
Extremely flexible golang deep comparison, extends the go testing package and tests HTTP APIs

go-testdeep Extremely flexible golang deep comparison, extends the go testing package. Latest news Synopsis Description Installation Functions Availab

Maxime Soulé 228 Oct 17, 2021
Cucumber for golang

Godog The API is likely to change a few times before we reach 1.0.0 Please read the full README, you may find it very useful. And do not forget to pee

Cucumber 1.5k Oct 16, 2021
Cucumber for golang

Godog The API is likely to change a few times before we reach 1.0.0 Please read the full README, you may find it very useful. And do not forget to pee

Cucumber 1.5k Oct 20, 2021
HTTP traffic mocking and testing made easy in Go ༼ʘ̚ل͜ʘ̚༽

gock Versatile HTTP mocking made easy in Go that works with any net/http based stdlib implementation. Heavily inspired by nock. There is also its Pyth

Tom 1.5k Oct 24, 2021
Full-featured test framework for Go! Assertions, mocking, input testing, output capturing, and much more! 🍕

testza ?? Testza is like pizza for Go - you could life without it, but why should you? Get The Module | Documentation | Contributing | Code of Conduct

Marvin Wendt 282 Oct 18, 2021
testcase is an opinionated behavior-driven-testing library

Table of Contents testcase Guide Official API Documentation Getting Started / Example Modules Summary DRY Modularization Stability Case Study About te

Adam Luzsi 81 Oct 22, 2021
Partial fork of testify framework with allure integration

allure-testify Оглавление Demo Getting started Examples Global environments keys How to use suite Allure info Test info Label Link Allure Actions Step

null 2 Sep 14, 2021
Expressive end-to-end HTTP API testing made easy in Go

baloo Expressive and versatile end-to-end HTTP API testing made easy in Go (golang), built on top of gentleman HTTP client toolkit. Take a look to the

Tom 718 Oct 18, 2021
Hamcrest matchers for the Go programming language

Note: This has not been maintained and/or updated since 2011. Perhaps consider corbym/gocrest, instead. Introduction Hamcrest is a fluent framework fo

null 27 Oct 12, 2021
Golang HTTP client testing framework

flute Golang HTTP client testing framework Presentation https://speakerdeck.com/szksh/flute-golang-http-client-testing-framework Overview flute is the

Shunsuke Suzuki 16 Sep 19, 2021