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 #

From $REPLICANT_HOME, navigate to the sample SingleStore connection configuration file:
```
vi conf/conn/singlestore.yaml
```
For connecting to the SingleStore server, you can choose between two methods for an authenticated connection:
- Using basic username and password authentication.
- Using SSL.
Connect with username and password #

For connecting to SingleStore using via basic username and password authentication, you have the following two options:
Specify credentials in plain text
You can specify your credentials in plain form in the connection configuration file like the folowing sample:
```
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
Fetch credentials from AWS Secrets Manager
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.
Connect using SSL #

To connect to SingleStore using SSL, follow these steps:
1. Configure the server-side requirements by following the instructions in Server Configuration for Secure Client and Intra-Cluster Connections.
2. Specify the SSL parameters to Replicant in the ssl section of the connection configuration file in the following format:
```
ssl:
  enable: true
  root-cert: "PATH_TO_CA_CERTIFICATE_FILE" 
  hostname-verification: {true|false}      
  trust-store:                    
    path: PATH_TO_CA_TRUSTSTORE
    password: TRUSTSTORE_PASSWORD
  key-store:                       
    path: PATH_TO_KEYSTORE
    password: KEYSTORE_PASSWORD
  ssl-key-password: KEYSTORE_CERT_PASSWORD
```
  In the preceding configuration:
  - root-cert holds the full path to your SSL CA certificate file—for example, "/home/alex/workspace/ca-cert.pem". Keep in mind that the trust-store configuration overrides root-cert.
  - hostname-verification enables hostname verification against the server identity according to the specification in the server’s certificate. Defaults to true.
  - trust-store holds the SSL CA certificate that the client uses to authenticate the server. This configuration overrides root-cert.
    
    Replace PATH_TO_CA_TRUSTSTORE and TRUSTSTORE_PASSWORD with the path to the TrustStore and the TrustStore password respectively.
  - The server uses key-store to authenticate the client. Replace PATH_TO_KEYSTORE and KEYSTORE_PASSWORD with the path to the KeyStore and the KeyStore password respectively.
  - As an optional parameter, ssl-key-password holds the password of the certificate inside the KeyStore.

II. Setup Applier Configuration #

Edit the applier configurations if required.

From $REPLICANT_HOME, navigate to the sample Applier Configuration File:
```
vi conf/dst/singlestore.yaml
```

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}'

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

Destination SingleStore #

Prerequisites #

I. Setup Connection Configuration #

Connect with username and password #

Connect using SSL #

II. Setup Applier Configuration #

Parameters related to snapshot mode #

Parameters related to realtime mode #