SingleStore

Destination SingleStore #

The extracted replicant-cli will be referred to as the $REPLICANT_HOME directory in the proceeding steps.

Prerequisites #

You must have a user configured in SingleStore for replication with SELECT, INSERT, UPDATE, DELETE, CREATE, DROP permissions on application databases.

If SingleStore user does not have create database permission then you must create a database named io_replicate and provide SELECT, INSERT, UPDATE, DELETE, CREATE, DROP privileges to SingleStore user.

I. Setup Connection Configuration #

  1. From $REPLICANT_HOME, navigate to the sample SingleStore connection configuration file:

    vi conf/conn/singlestore.yaml

  2. If you store your connection credentials in AWS Secrets Manager, you can tell Replicant to retrieve them. For more information, see Retrieve credentials from AWS Secrets Manager.

    Otherwise, you can put your credentials like usernames and passwords in plain form like the sample below:

    type: SINGLESTORE

host: HOSTNAME
port: PORT_NUMBER

username: 'USERNAME'
password: 'PASSWORD'

max-connections: 30
max-retries: 10
retry-wait-duration-ms: 1000

    Replace the following:

    • HOSTNAME: hostname of the SingleStore server
    • PORT_NUMBER: port number of the SingleStore server
    • USERNAME: the SingleStore username
    • PASSWORD: the password associated with USERNAME

II. Setup Applier Configuration #

Edit the applier configurations if required.

  1. From $REPLICANT_HOME, navigate to the sample Applier Configuration File:

    vi conf/dst/singlestore.yaml

  2. The configuration file has two parts:

    • Parameters related to snapshot mode.
    • Parameters related to realtime mode.

    For snapshot mode, notice the sample configuration below:

    snapshot:
  table-store: {ROW|COLUMN}
  per-table-config:
  - catalog: tpch
    tables:
      singlestore_orders:
        table-store: COLUMN
        sort-key: [singlestore_orderkey] 
        shard-key: [c2]

    In the sample above, notice the following:

    • You can provide SingleStore LOAD configuration string by setting the native-load-configs parameter. These configurations will be appended to the generated LOAD SQL command. For example:

      native-load-configs: "ERRORS HANDLE 'orders_errors'"

    • You can specify the table store to use by setting the table-store parameter to any of the following two values:

      • ROW
      • COLUMN

    • Replace singlestore_orderkey with a list of columns to be created as the sort-key.

    • Replace c2 with a list of columns to be created as the shard-key.

    For realtime mode, notice the following sample:

    realtime:
  threads: 8
  txn-size-rows: 10000
  batch-size-rows: 1000
  _oper-queue-size-rows: 20000
  skip-tables-on-failures : false
  replay-shard-key-update-as-delete-insert: true
  retry-failed-txn-idempotently: true
    perTableConfig:
    - schema: tpch
      tables:
        CUSTOMER:
          skip-upto-cursor: '{"extractorId":0,"timestamp":1599201466000,"log":"mariadb-bin.000200","position":36574666,"logSeqNum":1000,"slaveServerId":1,"v":1}'

  3. Below is a sample applier file with commonly used configuration parameters:

    snapshot:
  threads: 32
  batch-size-rows: 10_000
  txn-size-rows: 1_000_000

  bulk-load:
    enable: true
    type: FILE


  table-store: ROW
  per-table-config:
  - catalog: tpch
    tables:
      singlestore_orders:
        table-store: COLUMN
        sort-key: [singlestore_orderkey]
        shard-key: [c2]
      partsupp:
        table-type: REFERENCE
        table-store: ROW
      partsupp_macro_delta:
        table-store: COLUMN
        sort-key: [partkey, suppkey]

realtime:
  threads: 8
  txn-size-rows: 10000
  batch-size-rows: 1000
  _oper-queue-size-rows: 20000
  replay-shard-key-update-as-delete-insert: true
  retry-failed-txn-idempotently: true

For a detailed explanation of configuration parameters in the Applier file, read: Applier Reference