Changed Data Capture from CockroachDB to ConfluentCloud

chriscasano — Fri, 12 Mar 2021 03:20:37 +0000

Changed Data Capture from CockroachDB to ConfluentCloud

Here's a simple tutorial for sending data from CockroachDB directly to Confluent Cloud using CockroachDB Change Data Capture, which is typically refered to as a Changefeed. This example can be applied to CockroachCloud or a self hosted deployment of CockroachDB. This tutorial was tested on CockroachDB 20.2 and ConfluentCloud 1.25.

Setup Confluent Cloud

Setup Your Kafka Cluster

Get Kafka Resource ID

The ID list here for your Kafka cluster will be needed in the steps below

ccloud kafka cluster list

Create API Keys

The API Key and API Secret are needed for creating the CockroachDB Changefeed

ccloud api-key create --resource <RESOURCE ID>

Get Kafka End Point

The end point is needed to connect the Changefeed to Kafka

ccloud kafka cluster describe <RESOURCE ID>

Create Topic

ccloud kafka topic create demo_t --partitions 6

Start a Kafka Consumer to Verify Your Change Data Feed

ccloud kafka topic consume demo_t

Setup CockroachDB or CockroachCloud

Get CockroachDB or CockroachCloud

Do note that Changefeeds do not currently work on CockroachCloud Free-Tier. Use a Dedicated cluster to try this instead.

Create CockroachDB Table

Open a new terminal window and leave the Kafka consumer one open for later. Log in the cockroach sql command line and enter the following commands.

First, ensure rangefeeds are enabled.

SET CLUSTER SETTING kv.rangefeed.enabled = true;

Next, create a table.

create table t (k int default unique_rowid() primary key, v string);

Create Changefeed

When creating the changefeed, notice that you'll use kafka:// instead of using the returned endpoints earlier in ConfluentCloud (ie. https:// or SASL_SSL://). Also, be sure to include your API Key and Secret in the Changefeed.

CREATE CHANGEFEED FOR TABLE t INTO 'kafka://<CONFLUENT CLOUD URL>:9092?sasl_enabled=true&sasl_password=<API SECRET>&sasl_user=<API KEY>&tls_enabled=true&topic_prefix=demo_' WITH updated, key_in_value, format = json;

Insert Some Rows

insert into t (v) values ('one');
insert into t (v) values ('two');
insert into t (v) values ('three');

Verify data is showing up in your consumer app

Make table and index creation faster in a multi-region CockroachDB environment

chriscasano — Tue, 22 Dec 2020 15:35:51 +0000

Do you want to make your schema and object creation faster in CockroachDB? I was working with a customer the other day that had a multi-region CockroachDB cluster spun up in US West 2, US East 1 and EU West 2 in AWS. Anytime they created an object, their DDL statements would take a few seconds because of all of the hops they must make to system tables for creating the objects. By default, the system tables are replicated uniformly across the cluster and the leaseholders/RAFT leaders (leaders for reads/writes) are dispersed in the cluster as well. In their case, creating all of their database objects took almost 30 minutes.

One way to optimize the creation of these database objects is to move all of the leaseholders for tables in the system database to a specific region. In the same region you move the leaseholder to, you should run the DDL statements

So, if you want to run all of your DDL changes in US East 1, run this command to move the leaseholder of the system database to US East 1.

alter database system configure zone using lease_preferences = '[[+region=us-east-1]]';

This will move all of the leaseholders in the system database to be in US-East-1. This change is not immediate, so do wait a few minutes. Then check the ranges of the system tables to see if their leaseholders were moved to us-east-1. Here are some examples to test if the leaseholders moved:

show ranges from table system.users;
show ranges from table system.jobs;
show ranges from table system.zone;
...

root@localhost:26257/postgres> show ranges from table system.users;
  start_key | end_key | range_id | range_size_mb | lease_holder |           lease_holder_locality            | replicas |                                                            replica_localities
------------+---------+----------+---------------+--------------+--------------------------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------
  NULL      | NULL    |        6 |      0.129162 |            2 | cloud=aws,region=us-east-1,zone=us-east-1b | {1,2,3}  | {"cloud=aws,region=us-west-2,zone=us-west-2b","cloud=aws,region=us-east-1,zone=us-east-1b","cloud=aws,region=eu-west-1,zone=eu-west-1b"}
(1 row)

Once the leaseholders have moved, try running your DDL statements and see if the creation time improves.

This was before...

CREATE TABLE CHRIS (PK INT PRIMARY KEY);

CREATE TABLE

Time: 1.905s total (execution 1.905s / network 0.000s)

This was after...

CREATE TABLE CHRIS (PK INT PRIMARY KEY);

CREATE TABLE

Time: 284ms total (execution 284ms / network 0ms)

I'm not a fan of changing system database stuff but sometimes this helps for setting up tests or recreating environments. Just as easily it is to move the leaseholders into one region, you can always set it back to the default by running:

alter database system configure zone using lease_preferences = '[]';

DEV Community: chriscasano

Changed Data Capture from CockroachDB to ConfluentCloud

Changed Data Capture from CockroachDB to ConfluentCloud

Setup Confluent Cloud

Setup Your Kafka Cluster

Get Kafka Resource ID

Create API Keys

Get Kafka End Point

Create Topic

Start a Kafka Consumer to Verify Your Change Data Feed

Setup CockroachDB or CockroachCloud

Create CockroachDB Table

Create Changefeed

Insert Some Rows

Verify data is showing up in your consumer app

Make table and index creation faster in a multi-region CockroachDB environment