SingleStore

Source SingleStore#

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

I. Set up Connection Configuration#

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

   vi conf/conn/singlestore.yaml

2. 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 following sample:

   type:SINGLESTOREhost:HOSTNAMEport:PORT_NUMBERusername:'USERNAME'password:'PASSWORD'max-connections:30max-retries:10retry-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 withUSERNAME

   Use a secrets management service

   You can store your connection credentials in a secrets management service and tell Replicant to retrieve the credentials. For more information, seeSecrets management.

   Connect using SSL#

   To connect to SingleStore using SSL, follow these steps:

   1. Configure the server-side requirements by following the instructions inServer Configuration for Secure Client and Intra-Cluster Connections.

   2. Specify the SSL parameters to Replicant in thessl section of the connection configuration file in the following format:

      ssl:enable:trueroot-cert:"PATH_TO_CA_CERTIFICATE_FILE"hostname-verification: {true|false}trust-store:path:PATH_TO_CA_TRUSTSTOREpassword:TRUSTSTORE_PASSWORDkey-store:path:PATH_TO_KEYSTOREpassword:KEYSTORE_PASSWORDssl-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 thetrust-store configuration overridesroot-cert.

      • hostname-verification enables hostname verification against the server identity according to the specification in the server’s certificate. Defaults totrue.

      • trust-store holds the SSL CA certificate that the client uses to authenticate the server. This configuration overridesroot-cert.

        ReplacePATH_TO_CA_TRUSTSTORE andTRUSTSTORE_PASSWORD with the path to the TrustStore and the TrustStore password respectively.

      • The server useskey-store to authenticate the client. ReplacePATH_TO_KEYSTORE andKEYSTORE_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. Set up Extractor Configuration#

1. From$REPLICANT_HOME, navigate to the Extractor configuration file:

   vi conf/src/singlestore.yaml

   a. For snapshot mode, make the necessary changes as follows in thesnapshot section of the configuration file:

   snapshot:#threads: 32#fetch-size-rows: 10_000#min-job-size-rows: 1_000_000max-jobs-per-chunk:32#verify-row-count: false_traceDBTasks:trueper-table-config:  -catalog:tpchtables:#     testTable#       split-key: split-key-columnpart:split-key:partkeypartsupp:split-key:partkeysupplier:orders:split-key:orderkeylineitem:row-identifier-key: [l_orderkey, l_linenumber]split-key:l_orderkey#        split-hints:#          row-count-estimate: 15000#          split-key-min-value: 1#          split-key-max-value: 60_000

   b. For delta snapshot mode, you can adddelta-snapshot-key column to SingleStore tables if not present already with the followingALTER (andUPDATE) statement per table.

   ALTERTABLE tpch.LINEITEM_CSADDCOLUMN replicate_io_delta_snapshot_keyTIMESTAMPNOTNULLDEFAULTcurrent_timestampONUPDATEcurrent_timestamp;

   UPDATE tpch.LINEITEM_CSset replicate_io_delta_snapshot_key=current_timestamp;

   Then make the necessary changes as follows in thedelta-snapshot section of the configuration file:

   delta-snapshot:#threads: 32#fetch-size-rows: 10_000#min-job-size-rows: 1_000_000max-jobs-per-chunk:32_max-delete-jobs-per-chunk:32delta-snapshot-key:last_update_timedelta-snapshot-interval:10delta-snapshot-delete-interval:10_traceDBTasks:truereplicate-deletes:falseper-table-config:  -schema:tpchtables:#      testTable#        split-key: split-key-column  # Any numeric/timestamp column with sufficiently large number of distincts#        split-hints:#          row-count-estimate: 100000  # Estimated row count, if supplied replicant will leverage#          split-key-min-value: 1      #Lower bound of split key value#          split-key-max-value: 60_000 #Upper bound of split key value, if supplied replicant will leverage and avoid querying source database for the same#        delta-snapshot-key: delta-snapshot-key-column  # A monotonic increasing numeric/timestamp column which gets new value on each INSERT/UPDATE#        row-identifier-key: [col1, col2]   # A set of columns which uniquely identify a row#        update-key: [col1, col2]  # A set of columns which replicant should use to perform deletes/updates during incremental replicationpart:split-key:partkeypartsupp:split-key:partkeysupplier:orders:split-key:orderkeyparts_view:update-key: [partkey]delta-snapshot-key:last_update_timesplit-key:last_update_timepartsupp_macro:update-key: [partkey]delta-snapshot-key:last_update_timesplit-key:last_update_time

For a detailed explanation of configuration parameters in the extractor file, readExtractor Reference.