Sink data from RisingWave to CockroachDB

You can test out this process on your own device by using the cockroach-sink demo in the integration_test directory of the RisingWave repository.

​

Prerequisites

• Ensure you already have a CockroachDB table that you can access and sink data to. For additional guidance on setting up CockroachDB, refer to this quick start guide.
• Ensure you have an upstream materialized view or source that you can sink data from.
• If you are running RisingWave locally from binaries and intend to use the native CDC source connectors or the JDBC sink connector, make sure you have JDK 11 or a later version installed in your environment.

​

Syntax and Parameters

Since CockroachDB is PostgreSQL-compatible, the syntax and parameters are the same as creating a PostgreSQL sink. For the syntax and parameters, see Create a sink.

​

Examples

Assume that we have a materialized view, mv1, and we want to sink data from mv1 to a table in CockroachDB called target_count. The following SQL query achieves this by creating a sink in RisingWave called sink1.

CREATE SINK sink1
FROM mv1
WITH (
  connector = 'jdbc',
  jdbc.url = 'jdbc:postgresql://cockroachdb:26257/defaultdb',
  user = 'root',
  password = '...',
  table.name = 'target_count',
  type = 'upsert',
  primary_key = 'target_id'
);

​

Data type mapping

Only one-dimensional arrays in RisingWave can be sinked to CockroachDB.

For array type, we only support smallint, integer, bigint, real, double precision, and varchar type now.