Using Integrate.io with Heroku Connect Pt. 1

Hi there. My name is Edsil Villadas, and I am a success engineer here at X Plenty. We provide an ETL platform that is available in the elements marketplace, which allows you to create automated data pipelines running to and from Heroku Postgres.

In this video, I will show an example of how customers that use Heroku Connect can leverage X Plenty to quickly and easily extend the benefits of Heroku Connect's bidirectional sync to Salesforce out to external services and data stores.

So I have an app set up with Heroku Connect provisioned along with Heroku Postgres database.

This is connected to my Salesforce org and has been set up to sync a few select objects down to Postgres that I maybe want to bring external data into or take data that's come from Salesforce and send that out via Integrate.io to an external data warehouse or data lake or even a flat file for a partner or customer.

So let's jump into Integrate.io from here by clicking on the Integrate.io app. Alternatively, we can set up some logins and head over to Integrate.io dot com and access it that way.

So what I will be working through is a really simple use case of having budget data in regards to a few of the accounts that are being reached out to over at MySQL database as well as a flat file in an s three that contains x current exchange rates. So what we're going to do is use those two sources. We're gonna go ahead and convert foreign currency into USD, and we are going to push that data over into Salesforce. Alright. So first things first is we are going to have to build out our connections, and I'll just go ahead and press the new connection button here.

And as you can see, this brings up a list of our native connectors. We have the ability to connect to relational databases like MySQL and Postgres, as well as the ability to connect to a few API services, such as Facebook and YouTube.

We have the ability to connect to data warehouses as well, such as BigQuery.

And we are not limited to the amount of connectors that you see here. We do utilize a REST API component. So really most things that have an API built on top of it, we will be able to access. So we'll go ahead and choose Heroku Postgres here. As you can see, there are a few fields that we just have to fill in here. So go ahead and give it a name.

Now we have two access types that we can utilize here. We have the direct connection as well as a tunnel connection that we can set up in order to connect to this database.

Now what we just need is the host name. And we if we have SSL setup, which we do have for Heroku Postgres, we wanna ensure that we go ahead and check this box here.

Alright. After that, we'll go ahead and enter our database name.

And then after that, we'll go ahead and insert our username and password.

Alright. And once we have everything filled out, you see that there is a test connection button here, so we'll just go ahead and test the connection.

Great. It looks like everything tested successfully, so we'll go ahead and create this connection.

Alright. Now one more database we need is our MySQL database, so we'll go ahead and build a connection to that.

So same thing here, really similar in the case that you we have to fill out a few fields.

And, you know, it's really similar in that we need the host name, the database name, the username, and password.

Just go ahead and fill those in.

And once we have these filled out, we can go ahead and test connection again. And great. Another successful test, so we'll go ahead and create connection.

Alright. We have one more to set up here, which is our s three connection. So we'll go ahead and choose Amazon s three.

And for s three, all we'll need is give it a name, select the proper region, give it our access key, and our secret. So we'll just go ahead and do that. And once we have everything filled in here, we can again test the connection.

Great. And we can create the connection.

And that is everything in terms of building a connection. So from here, we'll go ahead and build out our pipeline by going into our packages.

Just hit the little package icon on the left toolbar here and hit the new package button on the right. So we'll go ahead and give this package a name.

We can also add a description, if you'd like, and we'll choose our type of flow here. Now we have two flows that we can choose from here at Integrate.io, and they are data flows and workflows. So a data flow is what we would use to build our standard data pipeline.

Now the workflows are bit different in that this is where you can set up the order in which you want packages to run and set dependencies between these packages. And I'll go over workflows more in detail in part two of this video series.

Alright. So moving on, we'll create package, and we are brought over to our blank slate here. So at the top, we can go ahead and hit add component. And here you can see that we have a few choices as far as our sources go. We have our file stores, our database. You can see our REST API component there.

NetSuite, for example. So for this, we'll go ahead and choose database. And our UI is all drag and drop. So once you have a component in, you can just drag it wherever you'd like. You just double click to open. Let's go ahead and rename this since we're setting this up to be our MySQL source component.

We'll go ahead and just choose MySQL connection from the drop down there and hit next.

So on step two, we have two access modes. These access modes are table and query. So with table, all we would have to do is specify the schema name and the table name, and I don't know which table to put in. We can also include where clauses in the table access mode.

And, alternatively, we have the query access mode, which allows us to just run a entire SQL query in order to hit that data. So for example, we'll go ahead and choose table. And if we leave the schema name blank, it'll just use the default schema. And if, we'll go ahead and enter our table name here.

Okay. And we'll go ahead and, nowhere clause for this example. We'll go ahead and scroll down and hit next.

Okay. Once we hit next, step three is just a matter of choosing the fields that we would like to bring in. And here, you also get a little data preview of the table. Great feature, I think, because and it gives us a little quick insight into, you know, exactly what we're gonna be pushing into Integrate.io and just to make sure we have the correct table with the correct fields.

For the fields, we can either just go ahead and bring them in individually depending on need, or we can just select every single field and bring them all in at once. So we'll go ahead and do that and click save.

Next, we will add another source component here, and this one being a file store since we would like to access our Amazon s three.

Bring that in. We'll just drag it over, and we'll go ahead and rename this to s three. Go ahead hit next. And the s three component is a bit different in which that we have to specify the source bucket and the source path. So go ahead and enter our source bucket and the path to the file we'd like to bring in here.

Awesome. And once we have that, we have a few options that we can play around with as well. We there we want the record to be delimited every new line or just at the end of line. We have different record types that we can pull in via the source component as well, and we can choose our delimiter depending on the format of our file.

Choose our string qualifiers as well. I'm gonna go ahead and leave this first row contains field names checked and a few source action options that we can choose as well. Go ahead and hit next. And, again, similar to the MySQL database source component, we get a quick preview of what our tables will look like here.

So everything looks good.

One thing I do wanna point out here as well is that you can change the data types. As you can see, the currency code is coming in as a string. There is coming in as a float, but we can just, you know, change those data types accordingly on this level as well.

Alright. So now to add some transformations. As you can see here, we have a few transformations to choose from. Most of these, except for the select component up here, are gonna be no code transformations, meaning that all you have to do is just punch in a few inputs, and you'll be good to go. Let me show you what that would look like here by using the joint component.

I'm gonna go ahead and drag that over to the middle. And to connect between components, we just drag and drop.

Alright. So let's go ahead and open this up.

As you can see, we can define what is on the left or the right of this join. We can also define the join type. We have our left joins, right joins, full joins, inner joins, of course.

Join optimization, default, which uses a hash join. We have a replicated or skewed as well. Here, we will join on currency code. Go ahead and hit actually, let's rename this to left join on currency code.

Actually, it's right there. So let me just click it. Alright. And we'll go ahead and save it.

And here, we could add another transformation. We'll go ahead and do the select, which is our low code component, and this will allow us to do transformations at the field level.

We'll go ahead and hit autofill, and we will start performing our field level transformations.

Let me go ahead and open up the expression editor.

Alright. And now we have the expression editor open. So you can see on the left hand side, we have a variety of functions all organized into folders. We have a hundred fifty functions to be exact, actually, all ranging from string functions to cast functions and things like that. So for this example, we're gonna go ahead and use the round function.

And as you can see, when I highlight over the function name, it gives me a quick description of what it does and the parameters it accepts and exactly how many we would need to put in here. So let me go ahead and click round.

Go ahead and just add in our parentheses.

And here, we can on this level, we can actually also perform mathematical functions, for example, doing this divide. So we're gonna go ahead and divide the budget field by the rate.

Go ahead and close off the parenthesis there. Hit save. There we go. So now this on this budget field here, we're going to divide that by rate, and we're going to round it.

Awesome. And then we'll go ahead and take a currency code. We only need one, so let me get rid of that one there. And over on the left hand side, since we're changing all of the currency to USD, I'm just gonna go ahead and fill this entire column with USD, and we'll go ahead and rename the column to this currency code.

I'll go ahead and hit save. We'll need to actually, let me go ahead and change the name here also to conversion. Go ahead and hit save, and we'll go ahead and add our destination component finally. So as you can see, we have a number of options in regards to destination as well.

So for this example, we'll go ahead and choose database, and we'll open it up. We'll rename this to Postgres since we're sending it to Heroku Postgres. We'll go ahead and choose Heroku from our drop down.

And here, we have to specify the schema and the table that we will be pushing into. So the table already exists, so I'm not gonna have it automatically create. The operation type, we're gonna go ahead with the merge with an existing update and insert. So we're gonna do an cert to our table in Postgres. Here, you can see we also allow for preaction and postaction SQL statements. Few advanced options, and we'll go ahead and hit next.

And here, it says we must select a merge key. So let's autofill with all of our fields. We'll go ahead and let's remove some of these fields that we're not gonna map. We need this ID, budget, and currency code. Gonna go ahead and hit ID as the merge key, and we're going to rename this just so it maps appropriately to our Salesforce database.

It's gonna be deal size, and we're gonna go ahead and new currency. You notice that we have the underscore underscore c here, meaning that they are custom columns custom fields, I should say, over at Salesforce. We'll go ahead and hit save.

Alright. So we have our pipeline finished up here. There's a few more things we can do before the we run this job. So at the very top, we have a few other options.

We have this one that says save and validate. So we'll go ahead and click that. What this will do is it will save your pipeline, and it will go through your pipeline just to make sure everything is valid. So if you're using any functions, just making sure that the parameters you're patching matching or I'm sorry, passing in match correctly, and they are in the data types that they should be, mapping errors or typos, things like that.

Everything looks like it's all good, so we'll go ahead and close this.

Now that we saved it, you can see here our rollback feature has been activated. So we do have the ability to save previous versions, and you can go back to them anytime you need.

Okay. And from here, we can go ahead and we can actually create a major version as well. And this will allow you to add descriptions when you create a major version.

We can set variables up as well. We have any user defined variables, which we're not using. But if you were to if you're looking to make things a little bit more dynamic, you can definitely utilize that as well. We have a few system variables that you can edit depending on your needs.

And from here, we'll go ahead and run job.

So here, it asks us to create a cluster. So what clusters are, they are the resource that we use over here in X Plenty to spin up and run those jobs for you. So we have two different instances. We have Sandbox clusters and production clusters.

So Chorus production is great for once you've finished developing and you're ready to put something in production, we'll be using your production cluster. For now, we will just go ahead and use the sandbox cluster here. But you can see we have the automatically terminate clusters when jobs complete checked, and we'll go ahead and choose one hour of inactivity here. Go ahead and save that.

And now we just have to choose a package. And, of course, the package that we're currently in is the one that will be attached to this cluster.

And, again, you can make any last minute user variable or system variable changes that you'd like. Go ahead and run job here.

Awesome. Alright. While that's running, we're gonna hop over into our scheduler. So hop into the schedules here.

As you can see, I've had one schedule already made, but let me just go through and create a new schedule just to show you how that look like. So, of course, you'd we'd give it a name, and we can switch this on or off depending on if we want the schedule to run or not. When setting up a schedule, really, it's as simple as, you know, choosing a number or whether you want it to run daily, hourly, minutes, every minute. And you have the ability to run concurrent schedules if you have more than one schedule.

Up. Go ahead and hit next. And, again, we can create a cluster at this level. So all the clusters that will be attached attached to schedules will all be production clusters, and we can add packages to this schedule. So we'll go ahead and choose MySQL to Heroku Postgres. We can choose a version of this package to run once the schedule begins.

And, again, last minute editing two variables there. And, you know, you can really add as many packages as you'd like to one schedule, and they would all run concurrently.

A few other things while our job is running is our settings.

So in our settings, a few options you could do is set up service hooks so you can get alerts to your email, to Slack, whenever a job fails or if it succeeds. Here, we can also edit our members. We can invite new members just by typing their email, and we can assign them roles.

Alright. So let's check on how our job is doing.

K. Looks like we've completed the job. So let me jump over to Heroku Postgres here. So as you can see, we have our data clip of what's currently in our Salesforce. This should update with a refresh. But as you can see, we have deal size and currency, both custom labels as null.

So let me go ahead and run this query again here in Postgres. And once we get that run, it should update.

Awesome. And now you see that we have our deal size column and our currency column populated with the appropriate and converted values and as well as our currency all in USD. Now let's jump over to our Salesforce. You can see our Salesforce, once everything dates and syncs over on the Heroku Postgres side, once I refresh this, these deals that are empty that you see there, like deal size and currency, should be populated. So let me go ahead and refresh this.

And there you go. So we were able to capitalize on the benefits of the bidirectional sync that Heroku Connect utilizes and leverage XPlanet to create more transformations that we then synced back into Salesforce.

And that concludes part one of using xFenty with Heroku Connect. Please join us again for part two where we will be using Salesforce as a source and sending data out to an external database. Thank you.

‍