CockroachDB Private Preview
CockroachDB is a horizontally-scaling, distributed SQL database. Fivetran's integration platform replicates data from your CockroachDB cluster and loads it into your destination.
Supported configurations
Fivetran supports the following CockroachDB configurations:
Supportability Category | Supported Values |
---|---|
Database versions | 22.1.0 - 23.2.3 |
Transport Layer Security (TLS) | TLS 1.3 |
Server encoding | UTF-8 |
CockroachDB feature level | Core, Enterprise |
Features
Feature Name | Supported | Notes |
---|---|---|
Capture deletes | check | All tables and fields |
History mode | check | Selectable for all tables |
Custom data | check | All tables and fields |
Data blocking | check | Column level, table level, and schema level |
Column hashing | check | |
Re-sync | check | Table level |
API configurable | check | API configuration |
Priority-first sync | ||
Fivetran data models | ||
Private networking |
Setup guide
In your CockroachDB cluster, you need to do the following:
- Allow access to your CockroachDB cluster via Fivetran's IP.
- Create a Fivetran-specific CockroachDB user with read-level permissions.
- Enable changefeeds on your CockroachDB cluster. Learn how to enable changefeeds in CockroachDB's Create and Configure Changefeeds documentation.
For specific instructions on how to set up your cluster, follow our step-by-step CockroachDB setup guide.
Sync overview
Once Fivetran is connected to your CockroachDB cluster, we pull a full dump of all selected data from your database. Using the CockroachDB changefeed mechanism, we pull all your new and changed data at regular intervals.
NOTE: If your CockroachDB cluster has an Enterprise license, Fivetran utilizes the follower reads feature during the initial sync. Learn more in the Initial sync section.
If data in the source changes (for example, you add new tables or change a data type), Fivetran automatically detects and persists these changes into your destination. For every schema in your CockroachDB source database, we create a schema in your destination that maps directly to its native schema. This ensures that the data in your destination is in a familiar format to work with.
Schema information
Fivetran tries to replicate the exact schema and tables from your CockroachDB source database to your destination.
Fivetran-generated columns
Fivetran adds the following columns to every table that is added to your destination:
_fivetran_synced
(UTC TIMESTAMP) indicates the time when Fivetran last successfully synced the row._fivetran_deleted
(BOOLEAN) marks rows that were deleted in the source database.
We add these columns to give you insight into the state of your data and the progress of your data syncs. For more information about these columns, see our System Columns and Tables documentation.
Type transformation and mapping
As we extract your data, we match CockroachDB data types to types that Fivetran supports. If we don't support a certain data type, we automatically change that type to the closest supported type or, in some cases, don't load that data at all. Our system automatically skips columns of data types that we don't accept or transform.
The following table illustrates how we transform your CockroachDB data types into Fivetran supported types:
CockroachDB Type | Fivetran Type | Fivetran Supported | Notes |
---|---|---|---|
ARRAY | False | ||
BIT | False | ||
BOOL | BOOLEAN | True | |
BOX2D | False | ||
BYTES | BINARY | True | Not supported as a primary key column. |
CHAR | STRING | True | |
DATE | LOCALDATE | True | |
DOUBLE PRECISION | DOUBLE | True | |
ENUM | False | ||
FLOAT | DOUBLE | True | |
GEOGRAPHY | False | ||
GEOMETRY | False | ||
INET | STRING | True | |
INT2 / SMALLINT | SHORT | True | |
INT2VECTOR | False | ||
INT4 | INTEGER | True | |
INT8 / BIGINT / INT | LONG | True | |
INTERVAL | STRING | True | |
JSONB | JSON | True | Not supported as a primary key column. |
LINESTRING | False | ||
MULTILINESTRING | False | ||
MULTIPOINT | False | ||
MULTIPOLYGON | False | ||
NUMERIC / DECIMAL | BIGDECIMAL | True | NUMERIC without specified precision or scale is synced as FLOAT. |
OID | LONG | True | |
OIDVECTOR | False | ||
POINT | False | ||
POLYGON | False | ||
REAL | DOUBLE | True | |
REGCLASS | False | ||
REGNAMESPACE | False | ||
REGPROC | False | ||
REGPROCEDURE | False | ||
REGROLE | False | ||
REGTYPE | False | ||
STRING | STRING | True | |
TEXT | STRING | True | |
TIME | STRING | True | |
TIMESTAMP | LOCALDATETIME | True | |
TIMESTAMPTZ | INSTANT | True | |
TIMETZ | STRING | True | |
TSQUERY | False | ||
TSVECTOR | False | ||
UUID | STRING | True | |
VARBIT | False | ||
VARCHAR | STRING | True |
In some cases, when loading data into your destination, we may need to convert Fivetran data types into data types that are supported by the destination. For more information, see the individual data destination pages.
Excluding source data
If you don’t want to sync all the data from your source database, you can exclude schemas, tables, or columns from your syncs on your Fivetran dashboard. To do so, go to your connector details page and uncheck the objects you would like to omit from syncing. For more information, see our Data Blocking documentation.
NOTE: You cannot exclude columns which are primary keys.
Alternatively, you can restrict the Fivetran user's access to certain tables. For more information, see the CockroachDB setup guide.
Initial sync
When Fivetran connects to a new database, we first copy all rows from every table in every schema for which we have SELECT permissions (except for those that you excluded on your Fivetran dashboard) and add Fivetran-generated columns. We copy rows by performing a SELECT statement on each table. We copy a limited number of rows at a time so that we don't have to start the sync over from the beginning if our connection is lost midway.
If your CockroachDB cluster has an Enterprise license, Fivetran utilizes the follower reads feature while performing initial syncs. Using this feature during initial syncs reduces read latencies and increases throughput.
Updating data
Fivetran performs incremental updates of any new or modified data from your source database using CockroachDB's changefeed mechanism. Fivetran uses the nobackfill changefeed option during incremental updates. This option prevents the changefeed from emitting the new version of every row in a table whenever you execute an ALTER COLUMN statement. Therefore, your destination table may not exactly match your source table.
For example, if you add a new column with a default value, any rows that were already synced to your destination would not reflect the default value of the new column. Therefore, you wouldn't incur MAR for every row in your table when an you execute an ALTER COLUMN statement. However, you may incur MAR in other scenarios, such as when you execute multiple schema change statements within a single transaction.
TIP: CockroachDB recommends executing schema changes as single statements. Learn more in their Schema changes with transactions documentation.
Limitations
We don't support syncing CockroachDB virtual columns. To access these columns, you can recreate the virtual column in your destination. Fivetran cannot sync tables that have a virtual column in the primary key.