CockroachDB
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 Type | Resulting Type | Notes |
|---|---|---|
bigint | integer | |
bit | boolean | |
boolean | boolean | |
character | string | |
character varying | string | |
date | string | |
double precision | string | |
enum | number | |
inet | string | |
int | integer | |
json | string | |
jsonb | string | |
numeric | number | |
smallint | integer | |
text | string | |
time with timezone | string | may be written as a native date type depending on the destination |
time without timezone | string | may be written as a native date type depending on the destination |
timestamp with timezone | string | may be written as a native date type depending on the destination |
timestamp without timezone | string | may be written as a native date type depending on the destination |
uuid | string |
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
| Feature | Supported | Notes |
|---|---|---|
| Full Refresh Sync | Yes | |
| Incremental Sync | Yes | |
| Change Data Capture | No | |
| SSL Support | Yes |
🐕🦺 Setup guide
Prerequisites
- CockroachDb
v1.15.xor above- Allow connections from Zenskar to your CockroachDb database
- 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
- Log into your Zenskar account.
- In the left navigation bar, navigate to Metering > Data Sources. In the top-right corner, click + ADD DATA SOURCE.
- In the Set Up Source section of the Add New Data Source page, enter a name for the CockroachDB data source connection.
- 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.
Updated 4 months ago