CockroachDB

A distributed SQL database designed for speed, scale, and survival.

Overview

The CockroachDB source supports both Full Refresh and Incremental syncs. You can choose if the Zenskar's CockroachDB connector will copy only the new or updated data or all rows in the tables and columns you set up for replication, every time a sync is run.

Resulting schema

Data-type mapping

CockroachDb data types are mapped to the following data types when synchronizing data:

CockroachDb TypeResulting TypeNotes
bigintinteger
bitboolean
booleanboolean
characterstring
character varyingstring
datestring
double precisionstring
enumnumber
inetstring
intinteger
jsonstring
jsonbstring
numericnumber
smallintinteger
textstring
time with timezonestringmay be written as a native date type depending on the destination
time without timezonestringmay be written as a native date type depending on the destination
timestamp with timezonestringmay be written as a native date type depending on the destination
timestamp without timezonestringmay be written as a native date type depending on the destination
uuidstring

🚧

Note

Arrays for all the above types as well as custom types are supported, although they may be de-nested depending on the destination.

Features

FeatureSupportedNotes
Full Refresh SyncYes
Incremental SyncYes
Change Data CaptureNo
SSL SupportYes

πŸ•β€πŸ¦Ί Setup guide

🚧

Prerequisites

  1. CockroachDb v1.15.x or above
  2. Allow connections from Zenskar to your CockroachDb database
  3. Create a dedicated read-only Zenksar user with access to all tables needed for replication

Allow Zenskar to access the database

This is dependent on your networking setup. The easiest way to verify if Airbyte is able to connect to your CockroachDb instance is via the check connection tool in the UI.

Create a dedicated read-only user with access to the relevant tables

This step is optional but highly recommended to allow for better permission control and auditing. Alternatively, you can use Zenskar with an existing user in your database.

To create a dedicated database user, run the following commands against your database:

CREATE USER zenskar PASSWORD 'your_password_here';

Then give it access to the relevant schema:

GRANT USAGE ON SCHEMA <schema_name> TO zenskar

πŸ“–

You can re-run the above command to grant access to all the relevant schemas. However, you must set up multiple data source connections to the same CockroachDB database to replicate data from multiple schemas.

Grant the user read-only access to the relevant tables:

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO zenskar;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> 
GRANT SELECT ON TABLES TO zenskar;

βš™οΈ Set up CockroachDB as data source in Zenskar

βš™οΈ Set up data source and type

  1. Log into your Zenskar account.
  2. In the left navigation bar, navigate to Metering > Data Sources. In the top-right corner, click + ADD DATA SOURCE.
  3. In the Set Up Source section of the Add New Data Source page, enter a name for the CockroachDB data source connection.
  4. Select CockroachDB from the Source Type dropdown.

βš™οΈ Configure data source

In the Source Config section of the Add New Data Source page, do the following:

  • Connect using SSL: Select Yes if you need an SSL connection.
  • Host: The host name or address of a CockroachDB node or load balancer.
  • Port: The port number of the SQL interface of the CockroachDB node or load balancer. The default port number for CockroachDB is 26257. Use this value when in doubt.
  • DB Name: A database name to use as current database. Defaults to defaultdb when using cockroach client commands. Drivers and ORMs may have different defaults.
  • Password: The user's password.
  • User: The user's password. It is not recommended to pass the password in the URL directly.
  • JDBC URL Parameters (Advanced): Refer this.