Create a sink connector from Apache Kafka® to Google BigQuery
The Google BigQuery sink connector enables you to move data from an Aiven for Apache Kafka® cluster to a set of Google BigQuery tables for further processing and analysis.
See the full set of available parameters and configuration options in the connector's documentation.
Prerequisites
-
an Aiven for Apache Kafka service with Kafka Connect enabled or a dedicated Aiven for Apache Kafka Connect cluster.
-
Prepare the GCP account and collect the following information about the target BigQuery:
-
GCP_PROJECT_NAME
: The GCP project name where the target Google BigQuery is located -
GCP_SERVICE_KEY
: A valid GCP service account key for theGCP_PROJECT_NAME
. To create the project key review the dedicated documentwarningThe GCP BigQuery sink connector accepts the
GCP_SERVICE_KEY
JSON service key as a string, therefore all"
symbols within it must be escaped\"
.The
GCP_SERVICE_KEY
parameter should be in the format{\"type\": \"service_account\",\"project_id\": \"XXXXXX\", ...}
Additionally, any
\n
symbols contained in theprivate_key
field need to be escaped (by substituting with\\n
) -
BIGQUERY_DATASET_NAME
: The BigQuery dataset name, as defined in the dedicated pre-requisite step -
TOPIC_LIST
: The list of topics to sink divided by comma -
APACHE_KAFKA_HOST
: The hostname of the Apache Kafka service, only needed when using Avro as data format -
SCHEMA_REGISTRY_PORT
: The Apache Kafka's schema registry port, only needed when using Avro as data format -
SCHEMA_REGISTRY_USER
: The Apache Kafka's schema registry username, only needed when using Avro as data format -
SCHEMA_REGISTRY_PASSWORD
: The Apache Kafka's schema registry user password, only needed when using Avro as data format
-
The SCHEMA_REGISTRY
related parameters are available in the Aiven for
Apache Kafka® service page, Overview tab, and Schema Registry subtab
As of version 3.0, Aiven for Apache Kafka no longer supports Confluent Schema Registry. For more information, read the article describing the replacement, Karapace
Setup a Google BigQuery sink connector with Aiven Console
The following example demonstrates how to setup a Google BigQuery sink connector for Apache Kafka using the Aiven Console.
Define a Kafka Connect configuration file
Define the connector configurations in a file (we'll refer to it with
the name bigquery_sink.json
) with the following content:
{
"name":"CONNECTOR_NAME",
"connector.class": "com.wepay.kafka.connect.bigquery.BigQuerySinkConnector",
"topics": "TOPIC_LIST",
"project": "GCP_PROJECT_NAME",
"defaultDataset": ".*=BIGQUERY_DATASET_NAME",
"schemaRetriever": "com.wepay.kafka.connect.bigquery.retrieve.IdentitySchemaRetriever",
"schemaRegistryClient.basic.auth.credentials.source": "URL",
"schemaRegistryLocation":"https://SCHEMA_REGISTRY_USER:SCHEMA_REGISTRY_PASSWORD@APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT",
"key.converter": "io.confluent.connect.avro.AvroConverter",
"key.converter.schema.registry.url": "https://APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT",
"key.converter.basic.auth.credentials.source": "USER_INFO",
"key.converter.schema.registry.basic.auth.user.info": "SCHEMA_REGISTRY_USER:SCHEMA_REGISTRY_PASSWORD",
"value.converter": "io.confluent.connect.avro.AvroConverter",
"value.converter.schema.registry.url": "https://APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT",
"value.converter.basic.auth.credentials.source": "USER_INFO",
"value.converter.schema.registry.basic.auth.user.info": "SCHEMA_REGISTRY_USER:SCHEMA_REGISTRY_PASSWORD",
"autoCreateTables": "true",
"keySource": "JSON",
"keyfile": "GCP_SERVICE_KEY"
}
The configuration file contains the following entries:
-
name
: the connector name -
project
: the GCP project name where the target Google BigQuery is located. -
defaultDataset
: the target BigQuery dataset name, prefixed with.*=
. -
schemaRegistryLocation
: details of the connection to Karapace offering the schema registry functionality, only needed when the source data is in Avro format. -
key.converter
andvalue.converter
: define the message data format in the Apache Kafka topic. Theio.confluent.connect.avro.AvroConverter
converter translates messages from the Avro format. To retrieve the message schema we use Aiven's Karapace schema registry, as specified by theschema.registry.url
parameter and related credentials.noteThe
key.converter
andvalue.converter
sections are only needed when the source data is in Avro format. If omitted the messages will be read as binary format.When using Avro as source data format, set following parameters:
value.converter.schema.registry.url
: pointing to the Aiven for Apache Kafka schema registry URL in the form ofhttps://APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT
with theAPACHE_KAFKA_HOST
andSCHEMA_REGISTRY_PORT
parameters retrieved in the previous step.value.converter.basic.auth.credentials.source
: to the valueUSER_INFO
, since you're going to login to the schema registry using username and password.value.converter.schema.registry.basic.auth.user.info
: passing the required schema registry credentials in the form ofSCHEMA_REGISTRY_USER:SCHEMA_REGISTRY_PASSWORD
with theSCHEMA_REGISTRY_USER
andSCHEMA_REGISTRY_PASSWORD
parameters retrieved in the previous step.
-
autoCreateTables
: enables the auto creation of the target BigQuery tables if they do not yet exist. -
allBQFieldsNullable
: sets any created column of produced BigQuery schema as NULLABLE instead of REQUIRED (even from Avro fields defined as non-nullable).noteAdditional configuration parameters enable the BigQuery sink connector to automatically evolve tables in response to new incoming messages from the source topic. Specifically, these parameters provide the following functionalities: :
allowNewBigQueryFields
: new fields can be added to BigQuery tables during subsequent schema updates.allowBigQueryRequiredFieldRelaxation
: fields in BigQuery schema can be changed back from REQUIRED to NULLABLE.
warningWhen the connector automatically performs subsequent schema changes on tables, columns, and data type definitions, it reduces the control database users have over these changes. This can lead to unexpected errors, particularly if message evolution exceeds the compatibility limits of BigQuery and its associated applications.
-
keySource
: defines the format of the GCP key, the value should beJSON
if the key is generated in JSON format -
keyfile
: contains the GCP service account key, correctly escaped as defined in the prerequisite phasewarningThe configuration of the BigQuery connector in Aiven has a non-backward-compatible change between versions
1.2.0
and1.6.5
:-
version
1.2.0
uses thecredentials
field to specify the Google Cloud credentials in JSON format:...
"credentials": "{...}",
... -
from version
1.6.5
on, use thekeyfield
field and set thekeySource
parameter toJSON
:...
"keyfile": "{...}",
"keySource": "JSON",
...
You can review the connector version available in an Aiven for Apache Kafka service with the dedicated Aiven CLI command
avn service connector available
. -
The full list of parameters is available in the dedicated GitHub page.
Create a Kafka Connect connector with the Aiven Console
To create a Kafka Connect connector:
-
Log in to the Aiven Console and select the Aiven for Apache Kafka® or Aiven for Apache Kafka Connect® service where the connector needs to be defined.
-
Select Connectors from the left sidebar.
-
Select Create New Connector, it is enabled only for services with Kafka Connect enabled.
-
Select Google BigQuery Sink.
-
In the Common tab, locate the Connector configuration text box and select on Edit.
-
Paste the connector configuration (stored in the
bigquery_sink.json
file) in the form. -
Select Apply.
noteThe Aiven Console parses the configuration file and fills the relevant UI fields. You can review the UI fields across the various tabs and change them if necessary. The changes will be reflected in JSON format in the Connector configuration text box.
-
After all the settings are correctly configured, select Create connector.
-
Verify the connector status under the Connectors screen.
-
Verify the presence of the data in the target BigQuery dataset, the table name is equal to the Apache Kafka topic name. To change the target table name, you can do so using the Kafka Connect
RegexRouter
transformation.noteYou can also create connectors using the Aiven CLI command.
Example: Create a Google BigQuery sink connector on a topic with a JSON schema
You have a topic named iot_measurements
containing data in JSON
format, with a defined JSON schema:
{
"schema": {
"type":"struct",
"fields":[{
"type":"int64",
"optional": false,
"field": "iot_id"
},{
"type":"string",
"optional": false,
"field": "metric"
},{
"type":"int32",
"optional": false,
"field": "measurement"
}]
},
"payload":{ "iot_id":1, "metric":"Temperature", "measurement":14}
}
{
"schema": {
"type":"struct",
"fields":[{
"type":"int64",
"optional": false,
"field": "iot_id"
},{
"type":"string",
"optional": false,
"field": "metric"
},{
"type":"int32",
"optional": false,
"field": "measurement"
}]
},
"payload":{"iot_id":2, "metric":"Humidity", "measurement":60}
}
Since the JSON schema needs to be defined in every message, there is a big overhead to transmit the information. To achieve a better performance in term of information-message ratio you should use the Avro format together with the Karapace schema registry provided by Aiven
You can sink the iot_measurements
topic to BigQuery with the following
connector configuration, after replacing the placeholders for
GCP_PROJECT_NAME
, GCP_SERVICE_KEY
and BIGQUERY_DATASET_NAME
:
{
"name":"iot_sink",
"connector.class": "com.wepay.kafka.connect.bigquery.BigQuerySinkConnector",
"topics": "iot_measurements",
"project": "GCP_PROJECT_NAME",
"defaultDataset": ".*=BIGQUERY_DATASET_NAME",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"autoCreateTables": "true",
"keySource": "JSON",
"keyfile": "GCP_SERVICE_KEY"
}
The configuration file contains the following things to note:
"topics": "iot_measurements"
: defines the topic to sink"value.converter": "org.apache.kafka.connect.json.JsonConverter"
: the message value is in plain JSON format without a schema
Example: Create a Google BigQuery sink connector on a topic in Avro format
You have a topic named students
in Avro format with the schema stored
in Karapace.
You can sink the students
topic to BigQuery with the following
connector configuration, after replacing the placeholders for
GCP_PROJECT_NAME
, GCP_SERVICE_KEY
, BIGQUERY_DATASET_NAME
,
SCHEMA_REGISTRY_USER
, SCHEMA_REGISTRY_PASSWORD
, APACHE_KAFKA_HOST
,
SCHEMA_REGISTRY_PORT
:
{
"name":"students_sink",
"connector.class": "com.wepay.kafka.connect.bigquery.BigQuerySinkConnector",
"topics": "students",
"project": "GCP_PROJECT_NAME",
"defaultDataset": ".*=BIGQUERY_DATASET_NAME",
"schemaRetriever": "com.wepay.kafka.connect.bigquery.retrieve.IdentitySchemaRetriever",
"schemaRegistryClient.basic.auth.credentials.source": "URL",
"schemaRegistryLocation":"https://SCHEMA_REGISTRY_USER:SCHEMA_REGISTRY_PASSWORD@APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT",
"key.converter": "io.confluent.connect.avro.AvroConverter",
"key.converter.schema.registry.url": "https://APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT",
"key.converter.basic.auth.credentials.source": "USER_INFO",
"key.converter.schema.registry.basic.auth.user.info": "SCHEMA_REGISTRY_USER:SCHEMA_REGISTRY_PASSWORD",
"value.converter": "io.confluent.connect.avro.AvroConverter",
"value.converter.schema.registry.url": "https://APACHE_KAFKA_HOST:SCHEMA_REGISTRY_PORT",
"value.converter.basic.auth.credentials.source": "USER_INFO",
"value.converter.schema.registry.basic.auth.user.info": "SCHEMA_REGISTRY_USER:SCHEMA_REGISTRY_PASSWORD",
"autoCreateTables": "true",
"keySource": "JSON",
"keyfile": "GCP_SERVICE_KEY"
}
The configuration file contains the following things to note:
"topics": "students"
: setting the topic to sinkkey.converter
andvalue.converter
: define the message data format in the Apache Kafka topic. Theio.confluent.connect.avro.AvroConverter
converter translates messages from the Avro format. To retrieve the message schema we use Aiven's Karapace schema registry as specified by theschema.registry.url
parameter and related credentials.