Pentaho Data Integration
Pentaho Data Integration (PDI) is a part of the Pentaho Open Source Business intelligence suite. In this article we describe “How to implement data sync from Postgres to Salesforce Big Objects”.
Big objects are used to store huge amounts of data, the number of records amounting to millions or more. These records can’t be viewed conveniently on the object tab. Also, Big Objects don’t support the upsert operation, which allows you to avoid duplicating records. Instead, you can index some of the fields that together form the record's unique key and just do an insert of the record.
To implement the synchronization process with Pentaho DI, firstly you need to create a new Job. Then create a new Transformation, which will contain all elements of the data sync process. Jobs can contain many transformations, and you can set their execution order. Also you can configure a job execution, its daily or weekly work with selected intervals.
To configure data sync in transformation, add a new element “Table input” and create Database connection. It should contain the name of host, database, port number, username and password.
To establish a connection with a Salesforce organization, 3 parameters are required: Salesforce Webservice URL, Username and Password. It is important to remember that when authorizing from an unfamiliar IP address, you must use a Security Token, which is added to the end of the password. If you do not do this, you may receive an error stating that the authorization credentials are incorrect.
For more convenient entry of credentials for authorization, it is better to use a csv file and global variables that will be used in the next transformations. Firstly you need to create a new Transformation, which is executed immediately after the start element.
Then you should add elements to retrieve variables from csv and save them for usage in next transformations.
All these variables can be used in the same transformation and all next related. Need to use the format ${<variable_name>}. For example, ${SFLINK}
Now create a next transformation to sync SQL data between PostgreSQL and SF organization.
Firstly you need to create a new Table Input element, which is used for data extraction from PostgreSQL.
Then create a connection with the database using parameters from the csv file. I recommend filling them manually first, to check that credentials are correct. If the Test button shows a success message, credentials are actual and can be replaced with global variables from the previous transformation, but it’s better to do this on the last step.
If the test connection completed successfully, you need to create a SQL request to retrieve all fields from the selected table. To check the syntax of SQL, you can click the Preview button and see existing records.
After that you need to create the element to insert these records in the Salesforce object. In our case, it will be a Big Object with the suffix “__b” in the end. Add the element “Salesforce insert” and create a relation from table input.
Then fill the SF credentials for authorization, test it, then choose your object and map its fields with the columns from PostgreSQL. It should look like this
So, these 2 elements are enough for data synchronization in theory. But for correct debug you should add a results output to the csv file, for success and error cases.
Need to configure both of these output elements, add a path for the output csv file, and also need to set a full list of fields for output. All of them can be automatically retrieved with the button Get fields, and for Error output there should be one more field, named Error, to display an error message.
In some cases, you can get an errors like this:
Errors were found on item <item_number> Error code is: INVALID_TYPE Error message: <field_name>: data value too large
But you know that it’s not true, and the field's value is not too long. This can happen due to a conflict of data types in the fields, when, for example, in a PostgreSQL database it is numeric, but in Salesforce it is text. The best solution is to convert it to a string before passing it to Salesforce Insert.
You need to create a new element, Modified Javascript value, and put it between PostgreSQL and SF statements.
This operator looks like this.
Then you can use the variables from this operator instead of SQL columns, and the type conflict will not appear again.
So, when you completed script testing successfully, you can replace all credentials with variables from separate transformation for more flexible configuration.
Provided implementation is suitable for data synchronization of a big number of the records. This process can be launched according to a condition or schedule on a local machine or a dedicated server from a remote desktop. This is a very useful way for Salesforce developers and others.
If you still have questions, feel free to ask us or we can help you with this implementation quickly.
