SingleStore Managed Service

Load Data from Informatica Cloud

This topic describes how to use the SingleStore Connector to connect Informatica Cloud to SingleStore.

The certification matrix below shows the latest versions tested for SingleStore DB and Informatica Cloud:

Certification Matrix

Versions

SingleStore DB

7.0 and newer

Prerequisites

You should have already installed the Informatica Secure Agent (either On-Prem or the Cloud Hosted version).

You will need a license from Informatica for the SingleStore Cloud Connector.

Creating the Connection
  1. Go to your Informatica Administrator console and click Connections. In the top-right corner click New Connection to create the new connection.

  2. Provide the connection details and then click Save.

    16083b5774649c.png

    The SingleStore connection should now be in the list of Connections.

Create and Run Mappings

This is the same process used for any Connection.

  1. On the Data Integration panel, click Create Asset or create the asset by clicking New on the left pane.

  2. Choose the type "Mapping" and click Create. Open a mapping to see the Source and the Target objects.

  3. Click on the Source and configure the source information in the bottom pane. Choose the connection you created, and the table to act as a source. Do the same for the Target.

  4. Map the fields in the Field Mapping section of the bottom pane of the Target:

  5. Save the mapping by clicking Save in the top right corner.

  6. Click Run to run the mapping. It will appear in the MyJobs section.

Additional Information about Sources and Targets
Source

In the Advanced Source Properties there are several fields to configure the mapping:

  • Catalog Name Override - overrides the source database name in the SELECT query. For example, if the source in the mapping is built from the definition of the table db.t, one can SELECT the rows from db2.t if that table exists and has the same DDL. If the table does not exist or the DDLs do not match this will lead to session errors.

  • User Defined Join - overrides the join that is used when multiple tables are combined in a single source. Important note: only SQL is supported as the join condition, attempting to use Informatica-specific syntax will lead to session errors. Example: when the user joins tables db.t1 and db.t2 in the User Defined Join field or in the Source Objects Relationship field they should specify the following SQL: db.t1 INNER JOIN db.t2 ON db.t1.t1_a=db.t2.t2_a

  • Source Filter - overrides the source filter from the Query Options section. This is also SQL, but does not require a WHERE clause. Example: t.a > 5

Limitations
  • Even though the Source Query Options contain a Sort option, this is currently not implemented and should not be used.

  • Filter and Join conditions support only SQL. Therefore, User Defined Join and Source Filter should contain only SQL syntax. Filter condition in the Query Options supports only SQL syntax in the Advanced Options. Source Object Relationship supports only SQL syntax in the Advanced Options.

Target

In the Advanced Target Properties there are several fields to configure the mapping:

  • Catalog Name Override - overrides the target database name in the INSERT, UPDATE, or DELETE query. For example, if the target in the mapping is built from the definition of the table db.t, one can INSERT, UPDATE, or DELETE the rows of db2.t if that table exists and has the same DDL. If the table does not exist or the DDLs do not match this will lead to session errors.

  • Bulk Insert - write rows in bulk. This is observed to be writing rows in a faster way than the regular INSERT.

  • Key Columns - override columns that are primary/unique in the target table. Used for UPDATE and DELETE statements.

  • Truncate Table - truncates the table before writing to it.

Limitations

Upsert Target operation is not supported currently. Only Insert, Update, and Delete are supported.