Spring Boot integration for Google BigQuery, built around the Storage Read API, with an emulator you can test against.
List<GenericRecord> rows = reader.query(
"SELECT name FROM `my-project.shop.products` WHERE price > 1.5",
TableId.of("shop", "query_result"));Reading a large query result through the BigQuery REST API is slow. The Storage Read API streams rows in Avro or Arrow over gRPC instead. bqkit wires that path into Spring Boot.
Run a query into a destination table, then stream it through BigQueryReadClient: rows in Avro, or columnar batches in Arrow.
Application Default Credentials, with optional service account impersonation.
A @ServiceConnection for the BigQuery emulator with Testcontainers: no GCP project needed.
Every bean steps aside when your application defines its own.
One call to query(sql, destination) runs a query job, lands the result in a table, then streams that table in Avro (QueryAvroReader) or in Arrow (QueryArrowReader).
Use the core alone, or let the starter wire it for you.
bqkit-coreSpring-free clients: BigQueryClients, CredentialsFactory, QueryAvroReader, QueryArrowReader.bqkit-spring-boot-starterAuto-configuration of BigQuery, BigQueryReadClient, QueryAvroReader and QueryArrowReader.bqkit-testEmulator container and @ServiceConnection support for tests.Each major version of bqkit targets one Spring Boot generation.
unreleasedbqkit is not released yet: build it locally with mvn install.
<dependency>
<groupId>io.github.bqkit</groupId>
<artifactId>bqkit-spring-boot-starter</artifactId>
<version>${bqkit.version}</version>
</dependency>bqkit:
bigquery:
project-id: my-project
impersonate-service-account: reader@my-project.iam.gserviceaccount.com # optional
location: EU # optional, location of the query jobsList<GenericRecord> rows = reader.query(
"SELECT name FROM `my-project.shop.products` WHERE price > 1.5",
TableId.of("shop", "query_result"));query(sql, destination)Truncates the destination table before writing, then reads it. Concurrent calls into the same destination table race: each one truncates the table, so a call can read the rows of another, or none. Give each call its own destination table, or use query(sql), whose anonymous result table belongs to its job.query(sql)Reads the anonymous result table that BigQuery creates for the job.readTable(table)Reads an existing table. The read session is created in the connection project, which needs bigquery.readsessions.create and pays for the read, whatever the project of the table. The connection's service account also needs bigquery.readsessions.getData on the table.query(configuration)Runs a QueryJobConfiguration as is, for query parameters, maximumBytesBilled or labels, then reads the table the job wrote to. It sets no write disposition, and the job runs in the location of the client. Only the result of a SELECT can be read.query(sql, destination, rows)Same as query(sql, destination), row by row to the callback.query(sql, rows)Same as query(sql), row by row to the callback.query(configuration, rows)Same as query(configuration), row by row to the callback.readTable(table, rows)Same as readTable(table), row by row to the callback.The starter declares both readers: inject the one that fits the use case. QueryAvroReader reads row by row: it returns the rows as a List<GenericRecord>, which holds the whole result in memory, or hands each row to a callback, for large results. QueryArrowReader hands the result to a callback, one columnar record batch at a time: to work on columns.
QueryAvroReader streams the rows with query(sql, rows), query(sql, destination, rows), query(configuration, rows) or readTable(table, rows):
reader.query("SELECT name FROM `my-project.shop.products`", TableId.of("shop", "query_result"),
row -> System.out.println(row.get("name")));The next response of the stream is only requested once every row of the current one has gone through the callback, so a slow callback slows the read down instead of filling the memory. If a row cannot be decoded or the callback throws, the read stops and the stream is cancelled: the rows before it have already been handed to the callback.
QueryAvroReader returns BigQuery values as Java types: STRING as String, TIMESTAMP as Instant, DATE as LocalDate, TIME as LocalTime, NUMERIC and BIGNUMERIC as BigDecimal, INT64 as Long, FLOAT64 as Double, BOOL as Boolean, BYTES as ByteBuffer. DATETIME, GEOGRAPHY and JSON stay String, as BigQuery sends them. STRUCT is a nested GenericRecord, ARRAY a List, and NULL is null.
QueryArrowReader takes the same arguments, and calls the callback once per record batch:
reader.query("SELECT name, price FROM `my-project.shop.products`", TableId.of("shop", "query_result"), batch -> {
VarCharVector name = (VarCharVector) batch.getVector("name");
for (int i = 0; i < batch.getRowCount(); i++) {
System.out.println(name.getObject(i));
}
});The batch is released once the callback returns: copy what you keep. Arrow needs access to java.nio on Java 21: start the application, and the tests, with --add-opens=java.base/java.nio=ALL-UNNAMED. Without it, the first read fails with Failed to initialize MemoryUtil.
Every bean backs off when the application defines its own. Defining a BigQueryConnectionDetails bean replaces the connection built from the properties.
The starter ships the configuration metadata of the bqkit.* keys: your IDE completes and documents them in application.yml.
With spring-cloud-gcp-starter-bigquery on the classpath, bqkit runs after it and reuses its BigQuery client. The Storage Read client takes the credentials of that client: the query and the read run with the spring.cloud.gcp.* project and credentials, and bqkit.bigquery.* does not apply. bqkit logs a warning at startup when bqkit.bigquery.* is set anyway. spring-cloud-gcp itself requires spring.cloud.gcp.bigquery.dataset-name: its BigQueryTemplate fails without it.
bqkit-core has no Spring dependency. Declare the beans yourself, with keys of your own in application.yml:
<dependency>
<groupId>io.github.bqkit</groupId>
<artifactId>bqkit-core</artifactId>
<version>${bqkit.version}</version>
</dependency>bigquery:
project-id: my-project
impersonate-service-account: reader@my-project.iam.gserviceaccount.com # optional
location: EU # optional, location of the query jobs@Configuration
public class BigQueryConfig {
@Bean
BigQueryConnection bigQueryConnection(
@Value("${bigquery.project-id}") String projectId,
@Value("${bigquery.impersonate-service-account:}") String impersonateServiceAccount,
@Value("${bigquery.location:}") String location) {
return BigQueryConnection.google(projectId, CredentialsFactory.create(impersonateServiceAccount))
.withLocation(location);
}
@Bean
BigQuery bigQuery(BigQueryConnection connection) {
return BigQueryClients.bigQuery(connection);
}
@Bean(destroyMethod = "close")
BigQueryReadClient bigQueryReadClient(BigQueryConnection connection) {
return BigQueryClients.readClient(connection);
}
@Bean
QueryAvroReader queryAvroReader(BigQuery bigQuery, BigQueryReadClient readClient) {
return new QueryAvroReader(bigQuery, readClient);
}
@Bean
QueryArrowReader queryArrowReader(BigQuery bigQuery, BigQueryReadClient readClient) {
return new QueryArrowReader(bigQuery, readClient);
}
}BigQueryConnection.google(...) targets the Google endpoints; BigQueryConnection.emulator(...) targets an emulator. The @ServiceConnection of bqkit-test relies on the starter.
Set impersonate-service-account to read with a service account's permissions instead of your own.
Declare the emulator container: Spring Boot connects every bqkit bean to it.
<dependency>
<groupId>io.github.bqkit</groupId>
<artifactId>bqkit-test</artifactId>
<version>${bqkit.version}</version>
<scope>test</scope>
</dependency>@SpringBootTest
@Testcontainers
class ProductRepositoryTest {
@Container
@ServiceConnection
static final BqkitBigQueryEmulatorContainer EMULATOR =
new BqkitBigQueryEmulatorContainer();
}BqkitBigQueryEmulatorContainer runs DEFAULT_IMAGE, the goccy/bigquery-emulator image bqkit is tested against, on project testproject. @ServiceConnection only accepts this container: the Testcontainers BigQueryEmulatorContainer starts the emulator on test-project, and the emulator's Avro codec rejects the hyphen of that id. To pull the image from a mirror, pass a DockerImageName declared with asCompatibleSubstituteFor("ghcr.io/goccy/bigquery-emulator"). With spring-cloud-gcp on the classpath, exclude GcpBigQueryAutoConfiguration in tests: its client would keep querying Google while bqkit reads from the emulator.
The application code queries through QueryAvroReader:
@Repository
public class ProductRepository {
private final QueryAvroReader reader;
public ProductRepository(QueryAvroReader reader) {
this.reader = reader;
}
public List<String> findNamesInStock() {
TableId result = TableId.of("shop", "products_in_stock_" + UUID.randomUUID().toString().replace('-', '_'));
return reader.query("SELECT name FROM shop.products WHERE stock > 0", result).stream()
.map(row -> (String) row.get("name"))
.toList();
}
}Each call writes to its own destination table, so concurrent calls do not race. Set a default table expiration on the dataset so that these tables do not pile up.
The test seeds the emulator with the BigQuery bean, calls the repository, then checks the result:
@SpringBootTest
@Testcontainers
class ProductRepositoryTest {
@Container
@ServiceConnection
static final BqkitBigQueryEmulatorContainer EMULATOR =
new BqkitBigQueryEmulatorContainer();
private static final TableId PRODUCTS = TableId.of("shop", "products");
@Autowired
BigQuery bigQuery;
@Autowired
ProductRepository repository;
@Test
void finds_the_names_of_the_products_in_stock() {
// Given
bigQuery.create(DatasetInfo.of(PRODUCTS.getDataset()));
bigQuery.create(TableInfo.of(PRODUCTS, StandardTableDefinition.of(Schema.of(
Field.of("name", StandardSQLTypeName.STRING),
Field.of("stock", StandardSQLTypeName.INT64)))));
bigQuery.insertAll(InsertAllRequest.newBuilder(PRODUCTS)
.addRow(Map.of("name", "banana", "stock", 3))
.addRow(Map.of("name", "apple", "stock", 0))
.addRow(Map.of("name", "cherry", "stock", 12))
.build());
// When
List<String> names = repository.findNamesInStock();
// Then
assertThat(names).containsExactlyInAnyOrder("banana", "cherry");
}
}The emulator keeps its data for the lifetime of the container: with several tests in a class, give each its own dataset or destination table (see Emulator limits).
Keep these in mind when a test behaves differently from production.
Hyphenated project ids break Avro reads. The emulator builds the Avro namespace from the project id, so test-project (the Testcontainers default) fails. testcontainers-java#12123 proposes withProjectId.
bqkit uses testproject.
WRITE_TRUNCATE is ignored. query(sql, destination) appends to the destination table instead of replacing it, so a second query into the same table returns the rows of both.
Use a distinct destination table per test, or drop it before each test.
TIME fails in Avro. The emulator cannot send a TIME column in Avro (cannot transform to binary time-micros).
See also bigquery-emulator#508.
prodNone of these limits affect BigQuery itself.