Google BigQuery Connector

KraftSQL's BigQuery Connector let's you execute SQL in Google BigQuery, Google's cloud data warehouse.

Getting Started

1. Add KraftSQL

Add rocks.frieler.kraftsql:kraftsql-bigquery as dependency to your Kotlin application.

2. Default Connection

KraftSQL provides a default connection to BigQuery, which is automatically used where no explicit BigQueryConnection is specified.

The default connection picks up an existing authentication from the environment, e.g. Application Default Credentials. In addition it is configurable through the following environment variables:

• KRAFTSQL_BIGQUERY_LOCATION: the location where statements are executed
• KRAFTSQL_BIGQUERY_DEFAULT_SESSION_MODE: when set to true, all statements as executed in a session

If you need more configuration options, you can also build a custom BigQueryConnection and configure it in the BigQueryEngine's DefaultConnection or specify it explicitly when executing SQL statements.

3. Writing SQL

You can use the Select DSL:

// all BigQuery specific SQL constructs reside in packages below `rocks.frieler.kraftsql.bq`:
import rocks.frieler.kraftsql.bq.dsl.Select
import rocks.frieler.kraftsql.bq.objects.Table
// common SQL constructs from the cre library are just below `rocks.frieler.kraftsql`:
import rocks.frieler.kraftsql.dsl.`as`
import rocks.frieler.kraftsql.expressions.Sum
import rocks.frieler.kraftsql.objects.DataRow

data class KeyValuePair(
    val key: String,
    val value: Int,
)

fun main() {
    val table = Table<KeyValuePair>("my-project", "my-dataset", "my_table", KeyValuePair::class)
    
    val data = Select<DataRow> { // tipp: use a data class to type the Data instead of generic DataRows (like with the Table above)
        from(table)
        columns(
            table[KeyValuePair::key] `as` "key",
            Sum.Companion(table[KeyValuePair::value]) `as` "sum",
        )
        groupBy(table[KeyValuePair::key])
    }

    // use data e.g. to insert it into a table...
}

More Examples

You can find more examples using the KraftSQL BigQuery Connector here.