Kafka exporter
Run the Kafka exporter
Stream Kafka topics into Snowflake, Databricks (beta) or BigQuery (beta) tables continuously, with a streaming route, staged files or load jobs.
The Kafka exporter reads Kafka topics continuously and writes each one into a
Snowflake, Databricks (beta) or BigQuery (beta) table, through a streaming route, staged files or load jobs. Its sqlload.toml is described in
Kafka exporter configuration; the repository’s
sqlload.example.toml
is a complete example.
Run the exporter
Section titled “Run the exporter”| Step | Command |
|---|---|
| Check the configuration and the connections. | sqlload debug |
| See how topics map to tables, without connecting to the warehouse. | sqlload tables [--topics-file FILE] |
| Show each topic’s table and the objects it needs. | sqlload plan |
| Create missing tables, and the stages, pipes or volumes the route needs. | sqlload apply |
Export until stopped, or for --max-duration. |
sqlload run [--max-duration D] [--shard N] |
| Show per-partition lag and committed offsets. | sqlload status |
load, copy, contract and logged-state are for loader projects, and refuse an exporter
configuration.
How rows reach the warehouse
Section titled “How rows reach the warehouse”[staging] kind chooses the route:
kind |
Snowflake | Databricks (beta) | BigQuery (beta) |
|---|---|---|---|
none |
Snowpipe Streaming. | Zerobus Ingest: rows sent as JSON records; a batch counts as written once the stream’s flush says it’s durable. | The Storage Write API’s default stream: rows sent as Arrow; each acknowledged append is committed. |
internal |
Files on a table stage, loaded with Snowpipe or COPY. |
Parquet files uploaded to the stream’s Unity Catalog volume (naming.stage), then one COPY INTO per batch. apply creates the volume; retain prunes its dated folders. |
One load job per batch, with its Parquet file sent along with the job, so no bucket is needed. |
s3 |
Files in your bucket, loaded with Snowpipe or COPY. |
Parquet files in your bucket, then COPY INTO from s3://.... Unity Catalog reads the bucket through an external location you create on it. |
Refused. |
gcs |
Refused. | Refused. | Parquet files in your Cloud Storage bucket, then one load job per batch reading them. |
Kafka offsets are committed only once the warehouse has the rows: when Snowpipe reports the files
loaded, when COPY INTO or a load job finishes, or when the streaming route confirms them, oldest
batch first.
Delivery is at least once: a crash or restart between the write and the Kafka commit replays
those rows, so a table can hold a record twice. Every row carries its topic, partition and offset
(see Envelope columns), so dedupe on those three columns.
Snowflake and Databricks skip a staged file they have loaded before, so each run of the exporter
names its files with an id of its own (<stream>_<run>_<digest>). Rows replayed after the
exporter restarts therefore always load, even into a table dropped and created again, instead of
being skipped as already loaded. A retry within a run reuses the batch’s files, which load once.
Batches are bounded by [batching] max_rows (default 100,000) and max_bytes (default
32 MiB), whichever comes first, so one write never holds more than that.
Auto-ingest
Section titled “Auto-ingest”With [staging.s3] auto_ingest = true, Snowflake loads each file when the bucket’s event
notification reaches the pipe. Snowpipe’s load reports don’t cover these loads, so the exporter
reads the table’s COPY_HISTORY and commits a batch’s offsets only once every one of its files
shows as loaded. That query needs a warehouse, so destination.warehouse is required.
- Each check is one query for every file waiting on the stream, from the oldest one’s upload. It
runs only while files wait, and at most once every
pending_check_after(default5m), after a failed query too.sqlload plansays so. While the exporter stops, it checks every 10 seconds for up to a minute, so a restart doesn’t load the last minutes’ rows again. - A stream that never stops always has files waiting, so the warehouse resumes at every check,
around the clock. Snowflake bills each resume for at least 60 seconds, so at the default
5mthat is about 12 resumes and 12 billed minutes an hour: roughly 20% of the same warehouse left running. On an X-Small (1 credit an hour) that is about 0.2 credits an hour, or 5 a day. - Use a dedicated X-Small warehouse with a short
AUTO_SUSPEND, such as 60 seconds. To pay less, check less often:pending_check_after = "15m"resumes it about 4 times an hour, roughly 7%. - Commits trail the loads by up to
pending_check_after. A busy stream may fillmax_pending_filesin that time and pause; raise it, or lowerpending_check_afterat the cost of more queries. - A file that never shows up, for example because notifications don’t reach the pipe, keeps
its batch and every later one uncommitted, with a warning naming the file. Check
SYSTEM$PIPE_STATUS. A file gone from the bucket followson_load_error. Truncating the table or re-creating the pipe erases the history of files loaded but not yet confirmed; restart the exporter, which loads their rows again.
Databricks
Section titled “Databricks”The Databricks destination is in beta: it is tested, but gets less everyday use than Snowflake, so expect rough edges, and please report them in GitHub issues.
- Sign in with an OAuth machine-to-machine client (a service principal):
client_idandclient_secret. Zerobus Ingest doesn’t take personal access tokens. - The SQL warehouse in
warehouse_idrunsplan,applyandCOPY INTO. Zerobus Ingest writes without it, so a streaming exporter keeps it busy only forplanandapply. - For
none, setzerobus_endpoint. The service principal needs the workspace access entitlement,USE CATALOG,USE SCHEMA, andMODIFYandSELECTon each table, granted explicitly. Zerobus Ingest sometimes refuses the first token for a new table for a few seconds; the exporter retries, and stops after 4 minutes with the grants it needs. - Each Zerobus request holds at most
max_request_bytes, and a record larger than that followsoversized. Zerobus Ingest limits a record to 10 MB. - For
s3, create a Unity Catalog storage credential and an external location covering the bucket andprefix, and grant the service principalREAD FILESon it. The exporter writes with the AWS credentials in its environment or shared credentials file. - Files are Parquet;
staging.format = "json",load = "snowpipe",s3.stageands3.auto_ingestare Snowflake’s and are refused. - Names are lower case: catalog, schema, tables and volumes. Envelope columns default to Databricks types; see Envelope columns.
BigQuery
Section titled “BigQuery”The BigQuery destination is in beta: it is tested, but gets less everyday use than Snowflake, so expect rough edges, and please report them in GitHub issues.
- Sign in with a service account’s key (
service_account_key). It needsroles/bigquery.dataEditoron the dataset androles/bigquery.jobUseron the project; forgcs, alsoroles/storage.objectAdminon the bucket (or on itsprefix). - The dataset must exist in
location;applycreates the tables in it. - BigQuery runs at most 1,500 load jobs per table a day. On
internalandgcseach batch is one load job, and a table’s next job starts at least 60 seconds after its previous one, so the exporter stays under that quota whatevermax_agesays;plannotes the streams where this pacing, notmax_age, sets how often they load. Usemax_age = "5m"or more, which leaves room for retries. The Storage Write API (none) has no such limit. - Each load job is named after its table, the exporter’s run and its batch’s offsets. When the exporter retries a batch whose job may already have run, it reads that job instead of running another, so a retry doesn’t load the rows twice. A crash before the Kafka commit can still replay them, as on every route. A load job still unfinished after 7 minutes stops the exporter; after a restart the batch loads again, so if that job finishes later its rows load twice.
- Each Storage Write API request holds at most
max_request_bytesof Arrow rows; BigQuery refuses a request over 10 MB. - Files are Parquet;
staging.format = "json"andload = "snowpipe"are refused. - Names are lower case: tables and envelope columns. Envelope columns default to BigQuery types; see Envelope columns.
Naming topics
Section titled “Naming topics”sqlload tables shows how topics map to tables before anything connects. With the example
configuration and a file of topic names:
$ sqlload tables --topics-file topics.txtshop.legacy-orders ORDERS_V1 orders_v1 sqlload-orders_v1shop.orders_v2.events ORDERS__EVENTS orders__events sqlload-orders__eventsshop.payments_v1.events PAYMENTS__EVENTS payments__events sqlload-payments__eventsKafka topic naming covers the rules.
Running several instances
Section titled “Running several instances”Set shards under [source] above 1 to split the streams between instances, and run each with
its own --shard, from 0, or SQLLOAD_SHARD. See sqlload run.