Connecting to Informatica PowerCenter
This topic describes how to use the SingleStore Connector for Informatica PowerCenter to connect Informatica PowerCenter to SingleStore.
The certification matrix below shows the latest versions tested for SingleStore DB and Informatica PowerCenter:
|Informatica PowerCenter||10.4 and newer|
|SingleStore DB||7.0 and newer|
The following prerequisites need to be satisfied before making the connection:
- You have a working SingleStore cluster.
- You have downloaded the configuration .zip from here: https://informatica-connector-releases.s3.eu-central-1.amazonaws.com/SingleStorePowerCenter1.0.0.zip
Installing the Connector
Unzip the PowerCenter configuration .zip file into an empty directory on the Windows server. The directory should contain the following:
/client /server installClient.bat installServer.bat README.txt
Run the installServer.bat script, supplying it with the path to your Informatica Server directory. For example:
cmd> installServer.bat C:\Informatica\10.4.0
The script copies the connector’s .jar files, plugin .xml file, and third party libraries into the relevant server directories and registers the plugin.
Go to your Informatica admin console -> PowerCenter Repository Service (PCRS) and set the Operating Mode to “Exclusive”.
After the PCRS is restarted, register the plugin using the
pmrepis located in the
cmd> pmrep.exe registerplugin -i $INFA_SERVER\server\bin\Plugin\SingleStorePlugin.xml
cmd> pmrep.exe registerplugin -i C:\Informatica\10.4.0\server\bin\Plugin\SingleStorePlugin.xml
Now that the server installation is complete, set the PowerCenter Repository Service Operation Mode back to “Normal”.
To install and configure the windows client:
Run the installClient.bat script, supplying it with the path to your Informatica clients directory. For example:
cmd> installClient.bat C:\Informatica\client\10.4.0\clients
The script copies the connector’s .jar files, registry files, and third party libraries into the relevant client directories and adds the registry entries.
Verify that PowerCenterClient received the registry files (SingleStore.reg and SingleStore_64.reg). Go to the
$INFA_CLIENT\PowerCenterClient\client\bindirectory and create the registry entry by double-clicking on the .reg file that corresponds to your OS architecture. SingleStore.reg for 32-bit OS and SingleStore_64.reg for 64-bit OS.
Example of the full-path:
Using the Connector
The connector can be used in both read and write scenarios.
To load the connector as a source, go to the Informatica PowerCenter Source Analyzer, click “Sources” and then click “Create SingleStore Source”.
To load the connector as a target, go to the Informatica PowerCenter Target Designer, click “Targets” and then click “Create SingleStore Target”.
With either scenario, you are taken to a connection window. Fill out the connection details and choose the appropriate tables definitions to load into Informatica.
The mappings and workflows are created in the same way as for any other sources and targets.
Source Join/Filter Example
To join multiple sources in the designer, connect those sources with a single Application Source Qualifier and specify the join conditions.
In the Application Source Qualifier go to Edit Conditions, click Edit Join Conditions, and specify the Join Condition.
Note: Only the Native Expression option is supported – that is the SQL syntax which is recognized by SingleStore. The Join Condition set in the PowerCenter Designer can be overridden for each session by setting a session attribute “User Defined Join” in the Workflow Manager for the corresponding workflow.
You can also specify the Filter Condition for the Application Source Qualifier -> Edit Conditions. Click “Edit Filter Conditions” and specify the Filter Condition.
Again, as in the Join case, only the Native Expression option is supported. The Filter Condition set in the PowerCenter Designer can be overridden for each session by setting a session attribute “Source Filter” in the Workflow Manager for the corresponding workflow.
Target Bulk Insert Example
The connector provides the ability to write data in the bulk mode. To enable the bulk insert mode, check the session attribute “Bulk Insert”.
Avoiding JVM Memory limits
When working with larger tables (100s of MB to TB), PowerCenter can hit JVM memory issues. To avoid potential out-of-memory issues:
- Go to the Administrator console -> PowerCenter Integration Service -> “Processes” Tab, and increase the Java SDK Minimum Memory and Java SDK Maximum Memory.
Depending on your workloads, values like
2048M may be reasonable.
Note: Informatica PowerCenter does not recognize
Gb, it expects the memory number to be formatted like this:
1024M (rather than