1. Introduction

Micronaut BigQuery library helps to simplify usage of BigQuery in Micronaut application.

The main use case is running simple queries and updates.

There is a micronaut-bigquery-mock artefact which allows you to run against an arbitrary database instead of BigQuery.

Groovy language has a first-class support. You can use GString to define staments which are automatically converted into named SQL statements.

2. Installation

The libraries are available in JCenter repository:

Gradle Installation
repositories {
    jcenter()
}

dependencies {
    // the main library
    compile 'com.agorapulse:micronaut-bigquery:0.3.1-micronaut-3.0'

    // the mock library for testing
    testCompile 'com.agorapulse:micronaut-bigqery-mock:0.3.1-micronaut-3.0'

    // required for testing using Testcontainers (see examples bellow)
    testCompile 'io.micronaut.sql:micronaut-jdbc-tomcat'
    testCompile 'org.postgresql:postgresql:42.2.9'
    testCompile 'org.testcontainers:spock:1.14.3'
    testCompile 'org.testcontainers:postgresql:1.14.3'
}

2.1. Connectiong to Google Cloud

To get started with the library locally you need to setup GOOGLE_APPLICATION_CREDENTIALS environment variable pointing to the JSON file obtained from Google Cloud Console. Follow the Quickstart: Using client libraries tutorial to get one.

To use the JSON locally for running the application you can add following snippet into your build.gradle file:

run {
    environment 'GOOGLE_APPLICATION_CREDENTIALS', file('path/to/credentials.json').absolutePath
}

If you are using the library within Google Cloud then BigQuery connection will be configured for you once the BigQuery API is enabled and configured for the project.

3. Usage

The library provides BigQuery and BigQueryService beans into the application context. BigQueryService has a couple of useful methods to execute SQL against BigQuery data warehouse - insert, query and execute.

In a following examples we’re be reffering to a class Person:

Person
@Introspected                                                                           (1)
public class Person {

    private long id;
    private boolean enabled = true;
    private Instant created = Instant.now();
    private double score = 1.0;
    private String firstName;
    private String lastName;
    private String email;
    private Role role;

    // getters, setters, equals, hash code
}
1 classes used for database mapping must be annotated with @Introspected (if you plan to use insert method)

The example bellow are defined inside a person service class:

Java
@Singleton
public class JavaPersonService {

    private final String schema;
    private final String table;
    private final BigQueryService bq;

    public JavaPersonService(
        @Value("${person.schema:persons}") String schema,
        @Value("${person.table:persons}") String table,
        BigQueryService bq
    ) {
        this.schema = schema;
        this.table = table;
        this.bq = bq;
    }

}
Groovy
@Singleton
class GroovyPersonService {

    private final String schema
    private final String table
    private final BigQueryService bq

    GroovyPersonService(
        @Value('${person.schema:persons}') String schema,
        @Value('${person.table:persons}') String table,
        BigQueryService bq
    ) {
        this.schema = schema
        this.table = table
        this.bq = bq
    }

}

3.1. Inserting New Rows

Inserting new rows is very easy. You only need to suppy the object and the name of the data set and table.

Java
public Person createPerson(String firstName, String lastName, String email, Role role) {
    Person person = new Person();
    person.setId(System.currentTimeMillis());
    person.setFirstName(firstName);
    person.setLastName(lastName);
    person.setEmail(email);
    person.setRole(role);

    return bq.insert(person, schema, table);
}
Groovy
Person createPerson(String firstName, String lastName, String email, Role role) {
    return bq.insert(new Person(
        id: System.currentTimeMillis(),
        firstName: firstName,
        lastName: lastName,
        role: role,
        email: email
    ), schema, table)
}

Alternatively you can use execute method to have a full control over the SQL statement.

3.2. Running Queries

If you want to retrieve single item you can run querySingle method which returns an Optional:

Java
public Optional<Person> get(long id) {
    return bq.querySingle(
        Collections.singletonMap("id", id),                                         (1)
        String.format("select * from %s.%s where id = @id", schema, table),         (2)
        JavaPersonService::buildPerson                                              (3)
    );
}
1 define the named parameter map
2 BigQuery uses @ prefix for the named parameters, : won’t be accepted
3 buildPerson method reference constructs the Person object
Groovy
Optional<Person> get(long id) {
    return bq.querySingle("select * from ${schema}.${table} where id = $id") {      (1)
        return buildPerson(it)                                                      (2)
    }
}
1 use GString to define the query, only variables after first occurence of where, on, set, values are considered named parameters so it is safe to use variables for data set and table
2 buildPerson called from within the closure

If you want to retrieve single more items you can run query method which returns Flowable:

Java
public Flowable<Person> findByLastName(String lastName) {
    return bq.query(
        Collections.singletonMap("last_name", lastName),
        String.format("select * from %s.%s where last_name = @last_name", schema, table),
        JavaPersonService::buildPerson
    );
}
Groovy
Flowable<Person> findByLastName(String lastName) {
    return bq.query("select * from ${schema}.${table} where last_name = $lastName") {
        return buildPerson(it)
    }
}

This is how the buildPerson method looks like:

Java
private static Person buildPerson(RowResult result) {
    Person person = new Person();
    person.setId(result.getLongValue("id"));
    person.setFirstName(result.getStringValue("first_name"));
    person.setLastName(result.getStringValue("last_name"));
    person.setEmail(result.getStringValue("email"));
    person.setRole(result.getEnumValue("role", Role.class));
    person.setScore(result.getDoubleValue("score"));
    person.setCreated(result.getTimestampValue("created"));
    person.setEnabled(result.getBooleanValue("enabled"));
    return person;
}
Groovy
private static Person buildPerson(RowResult result) {
    return new Person(
        id: result.getLongValue('id'),
        firstName: result.getStringValue('first_name'),
        lastName: result.getStringValue('last_name'),
        email: result.getStringValue('email'),
        role: result.getEnumValue('role', Role),
        score: result.getDoubleValue('score'),
        created: result.getTimestampValue('created'),
        enabled: result.getBooleanValue('enabled')
    )
}

3.3. Executing Write Operations

You can execute write operations using execute method. This method does not return any values but if there is a problem with your statement it will throw an exception.

This is an example of updating a role of the Person:

Java
public void updateRole(long id, Role role) {
    Map<String, Object> parameters = new HashMap<>();
    parameters.put("id", id);
    parameters.put("role", role);

    bq.execute(
        parameters,                                                                 (1)
        String.format("update %s.%s set role = @role where id = @id", schema, table)(2)
    );
}
1 define the named parameter map
2 BigQuery uses @ prefix for the named parameters, : won’t be accepted
Groovy
void updateRole(long id, Role role) {
    bq.execute """
        update ${schema}.${table}
        set
            role = $role
        where
            id = $id
    """
}

This is an example of deleting a Person:

Java
public void deletePerson(long id) {
    bq.execute(
        Collections.singletonMap("id", id),                                         (1)
        String.format("delete from %s.%s where id = @id", schema, table)            (2)
    );
}
1 define the named parameter map
2 BigQuery uses @ prefix for the named parameters, : won’t be accepted
Groovy
void deletePerson(long id) {
    bq.execute "delete from ${schema}.${table} where id = $id"
}

3.4. Testing

Once micronaut-bigquery-mock is on the classpath the BigQueryService is replaced with pure SQL implementation.

You can use Testcontainers to setup a test database which emulates BigQuery:

Groovy
@Testcontainers
class PersonServiceSpec {

    private static final String TABLE_DEFINITION = '''
    CREATE SCHEMA persons;
    CREATE TABLE persons.persons (
        id bigserial primary key,
        first_name character varying(256),
        last_name character varying(256),
        email character varying(256),
        role character varying(10),
        score numeric,
        enabled boolean,
        created timestamp
    );
    '''

    private static final String DRIVER = 'org.postgresql.Driver'

    @Shared PostgreSQLContainer container = new PostgreSQLContainer()                   (1)
    @AutoCleanup ApplicationContext context

    void setupSpec() {
        Sql sql = Sql.newInstance(
            container.jdbcUrl,
            container.username,
            container.password,
            DRIVER
        )
        sql.execute(TABLE_DEFINITION)                                                   (2)
    }

    void setup() {
        context = ApplicationContext.builder(                                           (3)
            'datasources.default.url': container.jdbcUrl,
            'datasources.default.driverClassName': DRIVER,
            'datasources.default.username': container.username,
            'datasources.default.password': container.password,
        ).build()
        context.start()
    }

    void 'test person service'() {
        given:
            PersonService service = context.getBean(serviceType)                        (4)
            // tests
    }

}
1 Using PostgreSQL running using Testcontainers to emulate BigQuery
2 Create the table inside the test database
3 Start Micronaut context with a default datasource pointing to the test database
4 Get the instance of the service under test from the application context

4. Links

Javadoc

Source