Data Integration on the Cloud with Heroku PostgreSQL and Xplenty

Data Integration on the Cloud with Heroku PostgreSQL and Xplenty



What can you do with data collected on Heroku PostgreSQL? How will you analyze it and integrate it? With Xplenty, of course! Xplenty lets you connect to a PostgreSQL database on Heroku, design a dataflow via an intuitive user interface, aggregate the data, and even save it back to PostgreSQL on Heroku or other databases and cloud storage services.

Integrating Heroku PostgreSQL with Xplenty

Heroku Instructions

  1. Login to Heroku or signup for free if you don’t have an account yet.

  2. Go to ‘Databases’ and open the relevant database. If you don’t have a database yet, you can create one for free with the dev plan (bottom screen when creating a new DB).heroku-create-db2.png

  3. After opening the relevant database, take a look at ‘Connection Settings’ and note down the host, database, user, port, and password.

    heroku-create-db3.PNG

Xplenty Instructions

  1. Login to your Xplenty account or create one for free.

  2. Click the user image at the top right and choose ‘Account settings’.

  3. Click the ‘Connections’ menu on the left, ‘New Database Connection’, and choose ‘Heroku Postgres’.xplenty-heroku-postgre.PNG

  4. Fill in all the Heroku PostgreSQL connection details as noted down in the previous section.

    xplenty-heroku-postgresql2.png

  5. Click the ‘test connection’ button to make sure that the database integration works. If it fails, then one of the connection details is incorrect. Go back to Heroku and re-check all the PostgreSQL database details.

  6. Click ‘Create heroku postgres connection’.

  7. To define your new dataflow, create a new Xplenty package via ‘My Packages’ - ‘New Package’.

  8. Click the ‘New Source’ button at the top and choose ‘Database’

    xplenty-heroku-postgresql3.png

  9. Click the component that was just added - the ‘database source’ dialog should appear. Choose the Heroku PostgreSQL connection in the ‘database connection’ dropdown and enter the following info (also see database source help):

    • Source table - relevant DB table name
    • Split by column - primary key column which should be used to split the import workload over several tasks/connections, e.g. ‘id’
    • Max connections - number of tasks/connections which will be used to import the data, e.g. 4
    • WHERE clause (optional) - WHERE clause to run to limit which data should be imported, e.g. prod_category = 1 AND prod_color = 'red'
    • Null string (optional) - string to replace null values in char columns. Nulls in numeric columns are not replaced by this value.
  10. Click the green auto-detect schema button on the right (the circling arrows) to fill the schema automatically. You can also fill or edit it manually.

  11. Click ‘okay’ to close the component.

  12. Add components back in the package screen to process the data as necessary.

  13. To save the processed data back to a Heroku PostgreSQL database, add a database destination component. Note that you can also store the dataflow output to MongoDB, delimited files, or integrate with other services using the relevant destination component.

  14. Click the destination component. Choose the relevant connection (probably a different one than the source connection) and enter the target table.

  15. All done! Setup a cluster to run the job. Please refer to the documentation for many more Xplenty features available for easy data integration.


Integrate Your Data Today!

Try Xplenty free for 7 days. No credit card required.