SingleStore

The SingleStore target uses a JDBC connection to write data to a SingleStore database table. For information about supported versions, see Supported systems and versions.

To configure a SingleStore target, you specify connection information, a table name, and other properties that the driver requires. You can also use a connection to configure the target. By default, the SingleStore target writes data to the specified table based on the matching field names. You can override the default field mappings by defining specific mappings.

The target can use CRUD operations defined in the sdc.operation.type record header attribute to write data. You can define a default operation for records without the header attribute or value. You can also configure how to handle records with unsupported operations.

You can specify the format of the change data capture log used to process data from a CDC-enabled source. For information about Data Collector change data processing and a list of CDC-enabled sources, see Processing changed data.

CRUD operation processing

The SingleStore target can insert, update, or delete data. The target writes the records based on the CRUD operation defined in a CRUD operation header attribute or in operation-related stage properties.

The target uses the header attribute and stage properties as follows:
CRUD operation header attribute
The target looks for the CRUD operation in the sdc.operation.type record header attribute.
The attribute can contain one of the following numeric values:
  • 1 for INSERT
  • 2 for DELETE
  • 3 for UPDATE
If your flow has a CRUD-enabled source that processes changed data, the target simply reads the operation type from the sdc.operation.type header attribute that the source generates. If your flow has a non-CDC source, you can use the Expression Evaluator processor or a scripting processor to define the record header attribute. For more information about Data Collector changed data processing and a list of CDC-enabled sources, see Processing changed data.
Operation stage properties
If there is no CRUD operation in the sdc.operation.type record header attribute, the target uses the operation configured in the Default Operation property.
If the sdc.operation.type record header attribute contains an unsupported value, the target takes the action configured in the Unsupported Operation Handling property. The target can discard the record, send the record for error handling, or write the record using the default operation.

Configuring a SingleStore target

About this task

Configure the SingleStore target to use JDBC to insert, update, or delete data in a SingleStore database table.

Procedure

  1. In the Properties panel, on the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Required Fields Fields that must include data for the record to be passed into the stage.
    Tip: You might include fields that the stage uses.

    Records that do not include all required fields are processed based on the error handling configured for the flow.
    Preconditions Conditions that must evaluate to TRUE to allow a record to enter the stage for processing. Click Add to create additional preconditions.

    Records that do not meet all preconditions are processed based on the error handling configured for the stage.
    On Record Error Error record handling for the stage:
    • Discard - Discards the record.
    • Send to Error - Sends the record to the flow for error handling.
    • Stop Flow - Stops the flow.
  2. On the JDBC tab, configure the following properties:
    JDBC Property Description
    Connection

    7.4 and later

    		 Connection that defines the information that is required to connect to an external system.

    To connect to an external system, you can select a connection that contains the details, or you can specify the details in local properties. When you select a connection, the flow canvas hides properties that are defined in the connection.

    JDBC Connection String Connection string used to connect to the database. Enter in the following format:

    jdbc:singlestore://<host>[:<port>]/[<database_name>]

    You can optionally include the user name and password in the connection string.

    For more information, see the SingleStore documentation.
    Schema Name Optional database or schema name to use. Required if the connection string does not include a database. If specified, overrides any database specified in the connection string.

    If the database or schema depends on the record or time, enter an expression that contains record or time functions.

    Table Name Database table name to use.

    If the table depends on the record or time, enter an expression that contains record or time functions.

    Duplicate-key Error Handling Action to take when the record duplicates the primary key of a row in the table:
    • Ignore – Discard the record and retain existing row.
    • Replace – Overwrite the existing row with the record.
    Use Credentials Enables entering credentials on the Credentials tab. Select when you do not include credentials in the JDBC connection string.
    Field to Column Mapping Use to override the default field to column mappings. By default, fields are written to columns of the same name.
    When you override the mappings, you can define parameterized values to apply SQL functions to the field values before writing them to columns. For example, to convert a field value to an integer, enter the following for the parameterized value:
    CAST(? AS INTEGER)

    The question mark (?) is substituted with the value of the field. Leave the default value of ? if you do not need to apply a SQL function.

    Using simple or bulk edit mode, click the Add icon to create additional field to column mappings.
    Change Log Format Format of change capture data log produced by the CDC-enabled source. Used to process change capture data.
    Use Fast Load Uses the LOAD DATA command to insert data. This command bulk loads new records, resulting in faster processing.

    For small batches, clearing this property might result in improved performance.
    Default Operation Default CRUD operation to perform if the sdc.operation.type record header attribute is not set.
    Unsupported Operation Handling Action to take when the CRUD operation type defined in the sdc.operation.type record header attribute is not supported:
    • Discard - Discards the record.
    • Send to Error - Sends the record to the flow for error handling.
    • Use Default Operation - Writes the record to the target system using the default operation.
    Additional JDBC Configuration Properties Additional JDBC configuration properties to use. To add properties, click Add and define the JDBC property name and value.

    Use the property names and values as expected by JDBC.

  3. If you configured the source to enter JDBC credentials separately from the JDBC connection string on the JDBC tab, then configure the following properties on the Credentials tab:
    Credentials Property Description
    Username User name for the JDBC connection.

    The user account must have the correct permissions or privileges in the database.

    Password Password for the JDBC user name.
    Tip: To secure sensitive information such as user names and passwords, you can use runtime resources or credential stores.
  4. On the Advanced tab, optionally configure advanced properties.
    The defaults for these properties should work in most cases:
    Advanced Property Description
    Maximum Pool Size Maximum number of connections to create.

    Default is 1. The recommended value is 1.
    Minimum Idle Connections Minimum number of connections to create and maintain. To define a fixed connection pool, set to the same value as Maximum Pool Size.

    Default is 1.

    Connection Timeout (seconds) Maximum time to wait for a connection. Use a time constant in an expression to define the time increment.
    Default is 30 seconds, defined as follows: 
    ${30 * SECONDS}
    Idle Timeout (seconds) Maximum time to allow a connection to idle. Use a time constant in an expression to define the time increment.

    Use 0 to avoid removing any idle connections.

    When the entered value is close to or more than the maximum lifetime for a connection, Data Collector ignores the idle timeout.

    Default is 10 minutes, defined as follows: 
    ${10 * MINUTES}
    Max Connection Lifetime (seconds) Maximum lifetime for a connection. Use a time constant in an expression to define the time increment.

    Use 0 to set no maximum lifetime.

    When a maximum lifetime is set, the minimum valid value is 30 minutes.

    Default is 30 minutes, defined as follows: 
    ${30 * MINUTES}
    Transaction Isolation Transaction isolation level used to connect to the database.

    Default is the default transaction isolation level set for the database. To override the database default, select one of the following levels:

    • Read committed
    • Read uncommitted
    • Repeatable read
    • Serializable
    Init Query SQL query to perform immediately after the stage connects to the database. Use to set up the database session as needed.

    The query is performed after each connection to the database. If the stage disconnects from the database during the flow run, for example if a network timeout occurrs, the stage performs the query again when it reconnects to the database.