Using Integrate.io with Heroku Connect Pt. 2

Hi there. My name is Edsel Villadas, and I am a success engineer here at Integrate.io. We provide an ETL platform that is available in the elements marketplace, which allows you to create automated data pipelines running to end from Heroku Postgres. In this video, which is part two of a two part video series on how to use xPlinty with Heroku Connect, I will show another example of how customers that use Heroku Connect can leverage Integrate.io to quickly and easily extend the benefits of Heroku Connect's bidirectional sync to Salesforce out to external services and data stores. So here again, I have my app set up with Heroku Connect provisioned alongside Heroku Postgres, and this is connected to my Salesforce org and has been set up to sync a few select objects down to Postgres that, you know, I may want to bring external data into or take data that's come from Salesforce and send that out to an external database or data warehouse or even a flat file via X Plenty.

So this use case for this part two video here will be taking the account object and the contact object from Salesforce and joining those two tables together and pushing them out into MySQL database.

Alright. And from here, we'll just quickly jump over to Salesforce and check out the accounts.

As you can see, I have a table here with a few fields that we are looking to bring in. I also want to bring in the contacts object. So slightly larger table, and what we'll do is we're going to join these two on XPlanet using their ID.

Alright. So let's hop back in over to Heroku Connect. And from here, we can jump into XPlanet by clicking the XPlanet app.

And, again, alternatively, we can set up some logins, and head to Integrate.io dot com, and we can access Integrate.io that way as well. Alright. So we are here on the connections page. We're at Integrate.io.

As you can see, we have three connections built out earlier from part one of this video series, and we've built out a Heroku Postgres, MySQL, and s three connection. But let me go ahead and open the connections page here again.

As you can see, we have a plethora of native connectors that you can utilize to connect to whatever source you are needing to use. For example, we have analytical databases, data warehouse such as Google BigQuery and Amazon Redshift. Of course, you have the ability to connect to other relational databases as well, such as AWS post Postgres or Heroku, and a list of services that leverage APIs. And, of course, you're not limited to what you see here on this page.

If it has an API built on top of the service, then we will most likely be able to access it. And I just wanna go over a quick example for those that missed part one on what a, setting up a connection looks like. So let me just go ahead and open the Heroku Postgres connection here. As you can see, we have a few fields, that we needed to just fill out here.

We have our name. We are able to choose an access type. And here, we can choose whether we want a direct connection or a tunnel connection.

We need our host name. And if your database utilizes SSL, you wanna make sure that you have connect using SSL checked. We have our database that we need to provide, our username, and our password.

And once we have those all filled in, you'll notice that we have the test connection button there that we can use to test our connection before we create it just to make sure that we have our connection established one hundred percent.

Alright. And from here, let's go ahead and build our packages. We'll hop over to the package page. You see see we have one package built out already from part one. Let's go ahead and create a new package. We'll go ahead and give our package a name here.

Call this on Heroku. House goes to my SQL. We can add a description if we'd like, and we can choose our types of flows. So as I mentioned earlier in part one, we have two types of flows here in X Plenty. And just to summarize them again, our data flows is what utilizes your your standard data pipelines, meaning that this is what will have your source transformations and your destination, whereas workflow allows you to set dependencies between packages that you've built so that you can provide a sequence, in which order you would like these packages to run. And I will get into workflows a little bit more later into this video.

Let's go ahead and create a package.

So, again, we have our blank slate here. And what we wanna do is we are, again, going to take data from Salesforce and push that into an external database, this being MySQL. So let's go ahead and add a component.

And looking through our list of sources here, again, you can see that we have bunch of options to choose from. We have rest a our rest API source component, our NetSuite component, Amazon Redshift, and things like that. But for our example here, we'll go ahead and select database and open it up. We'll go ahead and rename this.

So we wanna take our account object and our contact object that we cannot do the dash. So let me put the underscore. So we'll call this s f account. We'll choose our Heroku Postgres connection.

And we have two modes of access here. We have our table, access mode, and query. So table, as you can see, is just a matter of specifying a schema name and a table name. This query is where you can actually just run an entire SQL query if you choose to do so.

But for example, here, we'll go ahead and use table, and we'll enter our schema.

And, again, we can include where clauses if we'd like.

Go ahead and click next.

And here we are. So we brought our table over from Heroku Postgres, and we can now see a preview of that data and what that looks like, which is really great, of course, just so we know that we're bringing in the correct table and all of our fields match up here. So we can choose to bring in all of our fields by hitting the select all, option there.

And once we choose select all, it looks like it doesn't like the underscores starting off the column names there. So go ahead and change that. But we don't need, all the fields, so we'll go ahead and choose a few. Choose name, ID, billing country, currency, and deal size, and phone.

And those are what we need for the example. I think I do wanna quickly point out here is that you can see the data types of the fields that we're pulling in here. At this level, we can actually just change those data types into something else. So for example, the ID field there, if you wanna change it from an integer to just a string, we can do that.

But we can go ahead and save.

Okay. And what we can do here actually is copy this component by either highlighting it and pressing control c or just pressing the copy button.

And we'll and really easily, we just get another copy of the component, move it around, we'll pop it open, and this one will be taking the contact object instead of the account.

Same connection. We'll go ahead and do same schema. We'll just change the table name here.

No further settings. Go ahead and hit next.

And now that we have a few different fields in, we'll go ahead and just click that. So the tool will replace that for you automatically.

But, again, we don't need all of the fields here, so go ahead and remove. And we'll just choose the individual fields we need, which for this example will be name will be name, title, email, and ID, and the mailing country. So another quick preview of our table. Go ahead and hit save here.

Alright. Now we'll add our transformation component. So for our transformation components, you can see we have a plethora of options to choose from here as well. Now everything but the select component is gonna be no code table level transformations.

So these are really great in that, you know, to utilize them, all you really need to do is plug in a few fields, and you are good to go. So let me go ahead and show an example of that by, using the join.

And we'll go ahead and drag that over. And to connect let me to connect the components together, we just need to drag and drop the line here. Once we have our joint component open, as you can see, we can choose on as whether we want the SF account on the left or the right. We have a few join types to choose from.

Inner join, of course, left, right, and full. So go ahead and choose full for the example.

We have a few optimizations, default replicated and skewed. We're gonna go ahead and use the default as that utilizes the hash join. And we need to just identify our I key that we want to join on, here being ID. And let's go ahead and rename this to full join on ID.

Go ahead and save that. Great. So after our join, we'll go ahead and do some field level transformations by using the select component. We'll go ahead and rename this to transformations.

We'll go ahead and auto fill.

Alright. We'll bring in all of the fields that we wanna play around with in this table. And we can go ahead and actually reorganize the order in which these columns appear in the table. So we'll go ahead and do that.

Title. Got email up there and phone. Perfect.

Awesome. And we went ahead and reorganized the table.

Now we can start doing some transformations, and let me open up the expression editor here.

Alright. And if you look to the left, once we're on our expression editor, you'll see a bunch of folders. These organize our one hundred fifty functions that you can use to perform those transformations on your data here in XPlanzi.

We have a bunch of functions ranging from string functions to mathematical functions. But for this example, we'll go ahead and look for the replace.

And as you can see, once I highlight over this function, you can see that it offers a quick description of what it does and the parameters that it takes. The second argument, you can either input a string or put a regex there. So that's what I'll go ahead and do. And what I'm trying to do here with this function is we're going to replace every character in the email except for the at symbol with an asterisk just so we can hide the contact or account name's email address for security purposes.

I'll do the same thing with the phone, but instead of using the at symbol, we're gonna go ahead and use a dash.

And what we can do here also is add another column.

And here, let's use our predefined variable package last successful. And what this will do is it'll give us a time stamp on, you know, when exactly we move the data from Salesforce over to MySQL.

Alright. And so it looks like we have everything we need. We'll go ahead and save that. And what we need to do now is send this to our destination.

So I actually wanna send this to do two destinations. So I'll go ahead and choose the clone component. And what this will do is it will let me send it to my SQL by selecting the database destination. We'll open this up, rename this to my SQL, and choose MySQL connection.

We'll go ahead and enter the table we wanted in. Here we want Hiroko test.

I can choose our operation type. I'm gonna go over right here. We can perform some pre action and post action SQL statements if we so choose, and we have a few advanced options we could play around with as well. So step three is just mapping, and, we can just autofill here and get everything we need.

It looks like we want the yeah. No. That's everything we need. So we'll go ahead and save.

And with the clone component, I can send it to another destination. And for debugging, I'm gonna go ahead and send it to s three And change the name of this guy. Hit next. Now here, I just have to specify the target bucket in my SC that this is going into, as well as define the directory.

Okay. We can choose our delimiter. I'm gonna go ahead and enter some string qualifiers here.

And some escape characters, settings. We go ahead write the field names in the header. We can choose to compress this output if we would like. You can also merge outputs. If you have multiple outputs that you plan on exporting here, we can all merge them into one single file. And we'll go ahead and save that.

Great. Awesome. And that is our pipeline. Before we run our job here, we can go ahead and do a few more things. So if we go to the top here and click save and validate, so this will save our pipeline while also going through it to make sure we don't have any immediate errors that we can fix right away, such as typos and data type mismatches, things like that.

We can go ahead and hit run jobs since we validated it.

One more thing actually is, now that we've saved it, we can now utilize the rollback feature. So every time you save, it'll go ahead and increment the version of this pipeline. And if we would like to go back to a previous version, we can definitely go ahead and do that. So now let's go ahead and run the job.

Again, here, we just need to create a cluster. And what cluster is is the resource that we spin up here on our side to be able to run these jobs for you. So we have the two environments, the sandbox and production. Sandbox, of course, is where your dev, will be taking place.

So anything that you're doing, in testing, you'd wanna attach a sandbox cluster to. Whereas a production cluster is something you would use once, testing is completed and you're running things in production. So we'll go ahead and hit next. Once we have our, cluster built, we can choose a package again and make any last minute changes to, variables that we've set up.

This example, specifically, we do not have variables, so we don't have to worry about that.

Okay. So let's jump in over to our packages because now I wanna talk about workflows. So let's create a new package.

Go ahead and give this a name.

Alright. And we're gonna choose workflow and create the package.

Okay. So it looks really similar, but the first thing you'll notice is that instead of adding components, we add tasks at the very top here. So let me go ahead and do that. We have two tasks to choose from. You can either run a package that you've built or execute a SQL query. So let me just show what the execute SQL query look like.

Open this up, and we choose a connection that we'd run it like to run the query on. So for example, we'd like to run the query in the Heroku Postgres connection. We can specify that query here in this field, and we can match any results to a data type and push that into a variable and pass that variable throughout different packages here on this workflow.

So going on to the next step here, step three, we have the option to, execute this task if all preceding conditions evaluate to true or one of them, evaluates to true. So we have that option as well.

Alright. And that is the execute SQL task. Let's go ahead and run the package here.

And so let's open that guy up.

We can name this to package a.

A. There you go. And then choose the Heroku Postgres MySQL package. Hit next.

And add any variables here. And again, settings whether or not you want that to run and or or.

Alright. We'll go do another one.

Call this package b.

We'll go ahead choose the other package.

Again, two and three, customize it as you see fit.

Go ahead and add a third package here.

Open it up and call it package.

Let's And we'll choose, the first package again here. Go ahead and hit next. Hit next one more time. We don't need any changes there.

So save it. And if we click on the green arrow, then we can choose on failure. So now what this will do is package a, if it were to succeed, it will run package b. If not, it'll run package c.

So that is what a workflow looks like and how you can leverage workflows to better customize your data pipelines.

Go ahead and click save this.

We'll go ahead and jump over to our jobs and see how that's doing.

Okay. It looks like everything has completed. And so that's great. Let's go jump over to s three since we went ahead and push this table to s three as well for debugging. Go refresh this, and we should have our file pop up here.

Awesome. Heroku test. Let's open that guy up.

So open here, and let's open this file.

Alright.

Great. Awesome. And here we go. We have a full join of our account and contact object here.

As you can see, not every contact had an account name attached to it, and not every account name had contacts attached to it. But, you know, we were able to hide those emails and those phone numbers as well as included times daytime field for when we exported the this data from Salesforce to MySQL. And there you go. That concludes part two of our two part series.

Thank you very much for joining me today, and I hope this was useful and showed a great deal as to how powerful a tool XPlanet really is. Thank you, and have a great day.

‍