Search docs.molo17.com
    Configuration guide Connectors

    Set up an Apache Cassandra target

    Apache Cassandra is a wide-column database that spreads each table over nodes by its partition key. The Cassandra agent of Gluesync is a target only: it writes every source row with the DataStax Java driver, for the snapshot and for live changes. This guide prepares a keyspace, a role and its permissions, creates the target connection, maps the primary key of a PostgreSQL table to a Cassandra primary key, and copies the data. It shows how inserts, updates and deletes land, which consistency level is used, how to size the write batches, and how to read the errors you are most likely to meet.

    In this guide

    You will find the datacenter name, create a keyspace with a replication setting, and create a role that has only the permissions Gluesync needs. You will create the Cassandra connection, let Gluesync create two tables from the field mapping (one with a single key, one with a composite key), run a snapshot and CDC, and check the rows with cqlsh. Then you will see how the key columns become the partition key and the clustering columns, what an update of a missing row does, how Cassandra answers deletes, and how to change the batch settings. A symptom, cause and fix table closes the guide.

    Quick information

    Goal

    Replicate PostgreSQL tables to Cassandra tables and confirm inserts, updates and deletes

    Audience

    Data engineers and Cassandra administrators

    Level

    Intermediate

    Time required

    About 60 minutes

    Area

    Control Plane → Data pipelines → Entities; Cassandra → cqlsh

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have a source that Core Hub can read with CDC. This guide uses PostgreSQL with wal_level = logical (see Set up a PostgreSQL WAL source) and two tables, public.drivers and public.results;

    2. You have an Apache Cassandra cluster that Core Hub can reach on port 9042. The agent supports Cassandra 2.1 and later. This guide was tested with Cassandra 5.0.3 on one node;

    3. The cluster has password authentication on. The agent needs a user name and a password. To restrict the role as shown in step 1, authenticator is PasswordAuthenticator and authorizer is CassandraAuthorizer in cassandra.yaml. A change of these settings needs a restart of the node;

    4. You can sign in to the Control Plane with a role that can create pipelines, and you can run cqlsh as a superuser.

    Gluesync only writes to Cassandra. The agent has no source role, so it cannot read changes from Cassandra. See Cassandra agent and Target Cassandra for the reference.

    Guide steps

    Prepare the keyspace and the role

    CLI

    1. Find the name of the datacenter. You enter it in the connection. A wrong name does not stop the connection test (see the troubleshooting table):

      SELECT data_center FROM system.local;

    2. Create a keyspace. Use the datacenter name of step 1 in the replication map, with the replication factor that your availability needs. A test node takes 1. A production datacenter of three nodes usually takes 3:

      CREATE KEYSPACE gluesync
  WITH replication = {'class': 'NetworkTopologyStrategy', 'dc1': 1};

    3. Create a role that can sign in, and give it the permissions on that keyspace:

      CREATE ROLE gluesync WITH PASSWORD = '<a strong password>' AND LOGIN = true;
GRANT SELECT ON KEYSPACE gluesync TO gluesync;
GRANT MODIFY ON KEYSPACE gluesync TO gluesync;
GRANT CREATE ON KEYSPACE gluesync TO gluesync;

    4. Check the grants:

      LIST ALL PERMISSIONS OF gluesync;
    Expected result

    SELECT data_center returns your datacenter (dc1 in our test), and the list shows CREATE, SELECT and MODIFY on <keyspace gluesync>. MODIFY covers INSERT, UPDATE, DELETE and TRUNCATE. SELECT lets Gluesync read a row by its key before it updates it. CREATE is only for the Create table button of the field mapping. If you create every table yourself, leave CREATE out. We did not need ALTER or DROP.

    When the role gluesync creates a table, Cassandra gives that role all permissions on the new table. A table that a superuser creates has no grant for gluesync of its own: the keyspace grants of this step apply.
    Prepare the source tables

    SQL

    Create two tables in PostgreSQL. public.drivers has a single-column key. public.results has a composite key and a text column, which needs a choice in the field mapping (see Data type notes):

    CREATE TABLE public.drivers (
  id         int PRIMARY KEY,
  first_name varchar(1000),
  last_name  varchar(1000),
  email      varchar(1000)
);
INSERT INTO public.drivers
  SELECT g, 'First' || g, 'Last' || g, 'driver' || g || '@example.com'
  FROM generate_series(1, 1000) g;

CREATE TABLE public.results (
  race_id     int,
  driver_id   int,
  position    int,
  notes       text,
  points      numeric(6,2) DEFAULT 12.5,
  fastest     boolean DEFAULT false,
  finished_at timestamp DEFAULT now(),
  PRIMARY KEY (race_id, driver_id)
);
INSERT INTO public.results (race_id, driver_id, position, notes)
  SELECT 1, g, g, 'n' FROM generate_series(1, 20) g;

    The Gluesync user of the PostgreSQL connection needs SELECT on both tables and the REPLICATION attribute (see Set up a PostgreSQL WAL source).

    Expected result

    SELECT count(*) FROM public.drivers; returns 1000 and SELECT count(*) FROM public.results; returns 20.

    Create the pipeline and the Cassandra connection

    UI

    1. Open Data pipelines;

    2. Select the + button (Create pipeline), or Create your first pipeline on a new installation. The Agents setup wizard opens on 1. Setup source;

    3. Select the PostgreSQL CDC agent tile, enter the connection of your PostgreSQL database, then select Test and continue and Next;

    4. On 2. Setup target(s), search for cassandra and select the Cassandra agent tile. The form that opens is titled Cassandra;

    5. Enter Host name / Address (a node of the cluster) and Port (9042);

    6. Enter Keyspace (gluesync), Username (gluesync) and Password;

    7. Enter Datacenter (dc1). The field is required: the form shows Required until you fill it;

      Edit target form for Cassandra with the host name

    8. Optional: turn on Enable TLS under Security and use Upload 'trust store' file when the cluster uses TLS. Additional hosts takes more nodes of the same cluster. Under Advanced connection properties, Add property offers Connection Timeout (30 seconds by default);

    9. Select Test and continue, then Next;

    10. On 3. Ready, enter a Pipeline name and select Finish.

    Expected result

    The target card shows a green check mark and <user>@<host>:9042, and the pipeline opens with the message No entities found. The test opens a session with the driver, so a wrong address, a wrong password or a wrong keyspace fails here, with the message in a Failed to save banner (see the troubleshooting table).

    localhost in the connection means the machine that runs Core Hub. If Core Hub runs in a container, enter the name that the container can resolve, for example the service name of Cassandra in the same Docker network.
    Add the entities and let Gluesync create the tables

    UI

    1. In the pipeline, select Entities. The Add entities page opens;

    2. In the Object browser, expand the schema public and tick drivers. The Field mapping dialog opens;

    3. Under Target, open Target Database. The list shows every keyspace of the cluster. Select gluesync;

    4. Open Target Table, type drivers and select Create table "drivers". The mapping shows the Cassandra type of every column. The varchar(1000) columns become text;

    5. Select Confirm. The dialog Create table drivers shows the CREATE TABLE statement that Gluesync will run. Review it, then select Create table drivers;

    6. Repeat for results: Target Database gluesync, Target Table results, Create table "results". The field notes shows the error Missing required field (see Data type notes). Open its Target type, select varchar, and enter 255 in Length. The error goes away;

    7. Select Confirm. The dialog Create table results shows the statement. Check the PRIMARY KEY line, then select Create table results;

      Create table results dialog with the CREATE TABLE statement for gluesync.results and PRIMARY KEY (race_id

    8. Select Confirm and continue. The entities public.drivers and public.results are in the Hold group.

    Expected result

    Cassandra now has both tables. DESCRIBE TABLE gluesync.results; shows PRIMARY KEY (race_id, driver_id) and WITH CLUSTERING ORDER BY (driver_id ASC). A notification Hybrid Keys Conflict Detected can appear for public.results (see Primary keys). It is a warning: the entity still runs.

    To map to a table that already exists, select it in Target Table instead of Create table. The field list of such a table is alphabetical, because Cassandra returns the columns in that order. Gluesync links the fields by name.

    Start the snapshot and CDC

    UI

    1. In the Hold group header, select the start icon (Start). The dialog Sync changes (CDC) opens for both entities;

    2. In Start from, keep Full copy (marked Recommended for the first start) and keep Keep and merge under Data already in the target;

    3. Select Start entity.

    Expected result

    Both entities move to the Active group, with Last rows 1000 for public.drivers and 20 for public.results. The dialog shows This target has no bulk load: the agent writes with batches of CQL statements only, for the snapshot and for live changes.

    Verify the rows

    CLI

    1. Count the rows in Cassandra:

      SELECT count(*) FROM gluesync.drivers;
SELECT count(*) FROM gluesync.results;

    2. Change the source. These statements update, insert and delete rows, and the last two work on the composite key:

      UPDATE public.drivers SET first_name = 'Changed' WHERE id = 1;
INSERT INTO public.drivers VALUES (2001, 'New', 'Driver', 'new@example.com');
DELETE FROM public.drivers WHERE id = 2;
UPDATE public.results SET points = 25.00, notes = 'podium'
  WHERE race_id = 1 AND driver_id = 3;
DELETE FROM public.results WHERE race_id = 1 AND driver_id = 20;

    3. A few seconds later, read the rows in Cassandra:

      SELECT id, first_name FROM gluesync.drivers WHERE id IN (1, 2, 2001);
SELECT race_id, driver_id, points, notes FROM gluesync.results
  WHERE race_id = 1 AND driver_id IN (3, 20);
SELECT count(*) FROM gluesync.drivers;
SELECT count(*) FROM gluesync.results;
    Expected result

    The first SELECT count(*) statements return 1000 and 20. After the changes, drivers has the rows 1 (Changed) and 2001 (New) and no row 2, and results has the row (1, 3) with points 25.00 and notes podium and no row (1, 20). The counts are 1000 for drivers (one row added and one removed) and 19 for results (one row removed). cqlsh warns that an aggregate without a partition key is slow: that is expected on a test table.

    Pipeline page with several entities in the Active group
    Optional: change the write batch settings

    UI

    The agent writes each group of changes as one logged batch (see How the agent writes). Two entity settings size it.

    1. In the pipeline, open the three-dot menu of an entity and select Edit settings;

    2. Open the Entity tab. Max batch size in kb is 5 and Max Write Batch Size is 1000 by default;

      Settings dialog for public.results on the Entity tab with Max batch size in kb 5 and Max Write Batch Size 1000

    3. Change a value. Pause the entity before you edit it and start it again after you confirm;

    4. Select Confirm and continue.

    On the target, Edit target has an Advanced settings tab with the key Disable truncate on target table before snapshot inserts. It is off by default. Turn it on to stop Gluesync from running TRUNCATE on the table before the snapshot inserts.

    Expected result

    Cassandra no longer logs BatchStatement warnings for the table, or the entity no longer stops with Batch too large (see the troubleshooting table). In our test, Max batch size in kb set to 2 copied 1000 rows of drivers without any batch warning.

    Primary keys

    A Cassandra primary key is the partition key plus the clustering columns. Gluesync takes the key of the field mapping, which is the primary key of the source table, and writes the same columns in every UPDATE and DELETE:

    • Create table builds the PRIMARY KEY clause from the source key, in its order. In our test, the key (race_id, driver_id) gave PRIMARY KEY (race_id, driver_id): race_id is the partition key and driver_id is a clustering column. A one-column key is the partition key alone;

    • For a different layout, such as a composite partition key, create the table with cqlsh and select it in Target Table. The key columns of the mapping must be the whole Cassandra primary key, partition key and clustering columns together. A mismatch fails with PRIMARY KEY part …​ found in SET part (see the troubleshooting table);

    • Cassandra does not let you change a primary key. A source update of a key column is written as a delete of the old row and an insert of the new one. In our test, setting id = 9005 on the row id = 6 removed the row 6 and created the row 9005, and the same worked for a clustering column of results;

    • For discovery, the agent reports the partition key as the physical key of a table. For a table with a composite key, Core Hub then shows Hybrid Keys Conflict Detected with a message such as We detected a legacy hybrid key configuration for table gluesync.results. Physical keys: [race_id], Configured logical keys: [driver_id, race_id]. Inserts, updates and deletes on the composite key worked in our test, so you can leave the warning. For tables without a primary key, see Choose keys for tables without a primary key.

    How updates and deletes land

    Cassandra writes are upserts, and Gluesync uses that:

    • An insert is a CQL INSERT, an update is a CQL UPDATE …​ SET …​ WHERE <key>, and a delete is a DELETE …​ WHERE <key>;

    • A CQL UPDATE creates a row that does not exist, with only the columns it sets. To avoid a row that misses its other columns, the agent reads the target row by its key first when a change carries only part of the columns, and writes the whole row with an INSERT when the row is not there. In our test, we deleted the row 5 in Cassandra, updated its last_name in PostgreSQL, and the row came back with all its columns;

    • A delete leaves a tombstone. Cassandra removes it at a compaction after gc_grace_seconds, which is 864000 (10 days) on the tables Gluesync created in our test. Until then, a read of the table skips the row but the tombstone still counts toward the tombstone thresholds of a read;

    • A snapshot that starts with Empty the table first runs TRUNCATE on the table. Keep and merge overwrites the rows with the same key.

    How the agent writes

    The agent writes each group of changes of the same operation as one LOGGED batch, which Cassandra applies atomically. A batch is cut by payload and by rows: no batch holds more than Max Write Batch Size rows, and the chunks follow Max batch size in kb.

    Cassandra has two limits on a batch, batch_size_warn_threshold (5 KiB by default) and batch_size_fail_threshold (50 KiB by default). In our test, on one node with the defaults:

    Max batch size in kb Batch size that Cassandra measured Result

    5 (default)

    11 to 12 KiB

    Cassandra logged a warning BatchStatement ... exceeding specified threshold of 5.000KiB. The rows were written.

    200

    112 KiB

    Cassandra refused the batch with exceeding specified threshold of 50.000KiB ... (see batch_size_fail_threshold) and Gluesync stopped the entity with Batch too large.

    2

    Not measured

    No batch warning in the Cassandra log. 1000 rows copied.

    Cassandra counts the size of a batch with its own overhead, so it is larger than the value you set. Keep Max batch size in kb well below the batch_size_warn_threshold of your cluster. The reference page Target Cassandra says the same.

    Consistency level

    Gluesync has no setting for the consistency level and does not set one in the agent. Every statement uses the default of the DataStax Java driver bundled with the agent (version 4.19.3 in our test), which is LOCAL_ONE, with the datacenter that you entered in Datacenter as the local datacenter. A write is acknowledged when one replica of that datacenter has it. For stronger guarantees, repair the cluster on a schedule and keep a replication factor of 3 on each datacenter.

    Data type notes

    The Field mapping dialog proposes a Cassandra type for each source column. These are the types that our PostgreSQL columns got by default, and the value that arrived in Cassandra:

    PostgreSQL type Cassandra type Note

    smallint, int, bigint

    smallint, int, bigint

    Same range.

    real, double precision

    float, double

    numeric(12,3)

    decimal

    12345.678 arrived unchanged.

    boolean

    boolean

    varchar(n), char(n)

    text

    describe shows varchar as text. char(3) with abc arrived as abc.

    date, time

    date, time

    timestamp (without time zone)

    timestamp

    The mapping shows Data type mismatch detected: Cassandra keeps an instant in UTC, with millisecond precision. 2026-11-09 10:20:30.123456 arrived as 10:20:30.123.

    timestamptz

    timestamp

    Converted to UTC: 10:20:30+02 arrived as 08:20:30.

    bytea

    blob

    \xdeadbeef arrived as 0xdeadbeef.

    uuid

    uuid

    The default is uuid, with the length error described below. In our test we chose varchar, so the column was text and held 123e4567-e89b-12d3-a456-426614174000.

    jsonb

    text

    Arrives as JSON text.

    inet

    inet

    10.1.2.3 arrived unchanged.

    The target type list offers text, inet, varchar, uuid, timeuuid, boolean, blob, smallint, int, tinyint, varint, float, double, bigint, decimal, date, time and timestamp.

    For the source types text, bytea, uuid, jsonb and inet, the field mapping starts with the error Missing required field and a Length of 0, and Confirm stays disabled. In our test, this cleared the errors:

    • text, uuid and jsonb: set Target type to varchar and enter a Length, such as 255, 36 and 4000. The column is created as text, which has no limit in Cassandra;

    • bytea and inet: keep blob and inet and enter any Length, such as 255 and 45. The generated CREATE TABLE has no length for these types.

    Troubleshooting

    Problem Possible cause What to do

    Failed to save with Could not reach any contact point …​ AuthenticationException: Authentication error on node <host>:9042: server replied with 'Provided username <user> and/or password are incorrect' to AuthResponse request

    The user name or the password is wrong.

    Check them with cqlsh -u <user>. Select Test and continue again.

    Failed to save with Could not reach any contact point …​ java.net.UnknownHostException: <host>

    The host name does not resolve from Core Hub.

    Use a name or address that Core Hub can resolve. Inside a container, localhost is the container.

    Failed to save with Could not reach any contact point …​ failed to send request (io.netty.channel.StacklessClosedChannelException) and an address ending in :0

    The Port field is empty. The edit form of an existing connection does not fill it with the default.

    Enter 9042 in Port and test again.

    Failed to save with did not accept the new credentials: Invalid keyspace <name>

    The keyspace does not exist, or its name is misspelled.

    Run DESCRIBE KEYSPACES; and enter the exact name in Keyspace.

    Test and continue succeeds, but an entity stops with No node was available to execute the query

    Datacenter does not match a datacenter of the cluster. The driver finds no node in the local datacenter. In our test, the connection test passed with a wrong name.

    Run SELECT data_center FROM system.local;, enter that name in Datacenter and select Test and continue. The entity writes again by itself.

    The field mapping shows Missing required field and Confirm is disabled

    A target type has a Length of 0. This happens for source columns of type text, bytea, uuid, jsonb and inet.

    See Data type notes.

    The field mapping shows Invalid additional column fields

    A Length is set on a text column. In our test, text with a length of 255 gave this error.

    Change Target type to varchar for a column with a length, or clear the length.

    Create Target Table notification: Create target table execution error: User <user> has no CREATE permission on <all tables in <keyspace>> or any of its parents, and the dialog says Failed to create table

    The role has no CREATE on the keyspace.

    Run GRANT CREATE ON KEYSPACE <keyspace> TO <user>; and select Create table again. You can also run the CREATE TABLE statement of the dialog yourself: select Copy snippet.

    Query Validation Error notification: User <user> has no MODIFY permission on <table <keyspace>.<table>> or any of its parents. The entity is in the Error group

    The table was created by another role, and the role of Gluesync has no MODIFY on it.

    Run GRANT MODIFY ON KEYSPACE <keyspace> TO <user>;. The entity resumes by itself: in our test it copied its 1000 rows after the grant.

    Query Validation Error notification Batch too large, and Cassandra logs Batch for [<keyspace>.<table>] is of size …​ exceeding specified threshold of 50.000KiB …​ (see batch_size_fail_threshold)

    Max batch size in kb is too high for the cluster. A batch of 112 KiB was refused in our test.

    Pause the entity, set Max batch size in kb lower, for example 2, then start it again. See How the agent writes.

    Cassandra logs Batch for [<keyspace>.<table>] is of size 12.597KiB, exceeding specified threshold of 5.000KiB

    The batches are above batch_size_warn_threshold. The rows are written.

    Lower Max batch size in kb until the warning stops, or raise batch_size_warn_threshold in cassandra.yaml after you check the load on the nodes.

    InvalidQueryException: PRIMARY KEY part <column> found in SET part in the Core Hub log, on an update, with an entity in the Error group

    The Cassandra table has a primary key with more columns than the key of the mapping, for example PRIMARY KEY (id, name) against the source key id. The agent lists only the partition key as a key of an existing table. Inserts work and updates fail.

    Make the Cassandra primary key the same columns as the source key. Create the table from the field mapping, or drop it and create it again with the same key, then run a snapshot with Empty the table first.

    Hybrid Keys Conflict Detected notification on an entity with a composite key

    The agent reports the partition key as the physical key, and the mapping has all key columns.

    None needed. Writes work. See Primary keys.

    A source update of a row that is missing in Cassandra creates the whole row

    This is the designed behavior: the agent writes the row image when the row is not on the target.

    None needed.

    A deleted row is still counted by a very large scan, or the disk does not shrink after a big delete

    Cassandra keeps tombstones until a compaction after gc_grace_seconds.

    Wait for the compaction. Do not lower gc_grace_seconds without a repair plan.

    The notification for a missing MODIFY looks like this:

    Pipeline page with a red Query Validation Error notification saying that user gluesync has no MODIFY permission on a table

    Related guides