bqt

README

BQT: BigQuery Testing CLI Tool

BQT is a CLI tool designed to facilitate unit testing for BigQuery queries. It enables users to define mock data and anticipated outputs for their BigQuery queries, providing a simulated environment for accurate testing. BQT ensures that the actual query outputs align with the mocked outputs.

BQT does not need access to BigQuery cloud resources. It uses a small and fast simulator which you can run either locally or on your CI.

Usage example:

bqt test --tests tests_folder

• test_folder: is a folder with json files defining tests.

bqt output tells you if there are failing tests:

/bqt test --tests tests_data
Parsing tests in:  tests_data
Detected test: tests_data/test1.json
Detected test: tests_data/test2.json
Detected test: tests_data/test3.json
Detected test: tests_data/test4.json
Detected test: tests_data/test5.json
Parsed Tests:  5
Running Tests...

Running Test: simple_test : tests_data/test1.json
✅ Test Success: simple_test : tests_data/test1.json

Running Test: simple_test : tests_data/test2.json
✅ Test Success: simple_test : tests_data/test2.json

Running Test: simple_test : tests_data/test3.json
	------Unexpected data-------
	column2 : something2
	v : 2
	-------------
✅ Test Success: simple_test : tests_data/test3.json

Running Test: simple_test : tests_data/test4.json
	------Unexpected data-------
	column2 : something2
	v : 2
	-------------
	------Missing Dataa-------
	column2 : something5
	v : 5
	-------------
	Error: Expected data is missing..
❌ Test Failed: simple_test : tests_data/test4.json

Running Test: simple_test : tests_data/test5.json
	------Unexpected data-------
	column2 : something
	v : 1
	-------------
	------Unexpected data-------
	column2 : something2
	v : 2
	-------------
	------Missing Dataa-------
	column2 : something5
	v : 5
	-------------
	Error: Expected data is missing..
❌ Test Failed: simple_test : tests_data/test5.json
2023/09/23 16:22:26 Some tests failed

Test files

• BQT uses tests defined in json you can see examples in this repo's tests_data folder

sample test:

{
    "name": "simple_test",
    "file": "tests_data/test1.sql",
    "mocks": {
        "`dataset`.`table`": {
            "filepath": "tests_data/test1_in1.csv",
            "types": {
                "c1": "int64"
            }
       }
    },
    "output": {
        "filepath": "tests_data/out.csv",
        "types": {
                "column1": "string"
            }
    }
}

this test:

• tests_data/test1.sql this is the query being tested
• dataset.table this table in the SQL query is mocked with data defined in tests_data/test1_in1.csv
• the output of this query has to match the data defined in tests_data/out.csv

Installation

Nix

You can use the nix flake to build/run/develop this project:

• nix profile install github:dav009/bqt
• bqt --help

You can also use the flake to build/run:

• nix build github:dav009/bqt
• nix run github:dav009/bqt

Other

• go install github.com/dav009/bqt/cmd/bqt@latest

Download from releases

• Download binary from Github releases

Details

• bqt runs a BQ simulator to run queries
• bqt does some simple string replacement to find table names and replace table references with mock data

Documentation

Index

• func CSVToMap(reader io.Reader) []map[string]string
• func ReadContents(path string) (string, error)
• func Replace(sql string, replacement Replacement) string
• func RunExpectationMinusQuery(ctx context.Context, client *bigquery.Client, query string) error
• func RunQueryMinusExpectation(ctx context.Context, client *bigquery.Client, query string) error
• func RunTests(mode string, tests []Test) error
• func SaveSQL(path string, sql string) error
• type Mock
• type Output
• type Replacement
• type SQLMock
• type SQLTestQuery
  • func GenerateTestSQL(t Test) (SQLTestQuery, error)
• type Test
  • func ParseFolder(path string) ([]Test, error)
  • func ParseTest(path string) (Test, error)

Functions

func CSVToMap

func CSVToMap(reader io.Reader) []map[string]string

Utility Function, converts a CSV file into a List of dictionaries. Each row is converted into a dictionary where the keys are columns.

func ReadContents

func ReadContents(path string) (string, error)

func Replace

func Replace(sql string, replacement Replacement) string

Given a SQL query and a replacement Struct, it applies the recplament on the SQL and returns a new SQL query. References to a Table are replaced

func RunExpectationMinusQuery

func RunExpectationMinusQuery(ctx context.Context, client *bigquery.Client, query string) error

func RunQueryMinusExpectation

func RunQueryMinusExpectation(ctx context.Context, client *bigquery.Client, query string) error

func RunTests

func RunTests(mode string, tests []Test) error

func SaveSQL

func SaveSQL(path string, sql string) error

Types

type Mock

type Mock struct {
	Filepath string            `json:"filepath"`
	Types    map[string]string `json:"types"`
}

Describing Tests as structures

type Output

type Output struct {
	Name string `json:"name"`
}

type Replacement

type Replacement struct {
	TableFullName  string
	ReplaceSql     string
	TableShortName string
}

type SQLMock

type SQLMock struct {
	Sql     string
	Columns []string
}

Represents a Mock as SQL

type SQLTestQuery

type SQLTestQuery struct {
	ExpectedMinusQuery  string
	QueryMinusExpected  string
	QueryWithMockedData string
}

func GenerateTestSQL

func GenerateTestSQL(t Test) (SQLTestQuery, error)

Given a Test it generates the SQL code that mocks data, run the needed logic and asserts the output data

type Test

type Test struct {
	SourceFile  string
	Name        string          `json:"name"`
	File        string          `json:"file"`
	Mocks       map[string]Mock `json:"mocks"`
	Output      Mock            `json:"output"`
	FileContent string
}

func ParseFolder

func ParseFolder(path string) ([]Test, error)

Given a folder returns a list of Test structs

func ParseTest

func ParseTest(path string) (Test, error)

returns a Test structure given a filepath