Import Tables For Cross Database Joins (Legacy)

Holistics Data Imports is able to migrate database tables between databases, such as when you want to move data from your production databases to your analytics/reporting database, without having to write code or time-consuming extract, transform and load (ETL) scripts! This allows you to build SQL queries to join tables from different databases together. In this example, a database table was imported from the source MySQL database into a destination PostgreSQL database, which could then be queried and joined with tables in the destination PostgreSQL database.

Once you have connected your databases to Holistics, Data Imports allows you to copy your database tables of choice from a source database to a destination database. Under Data Imports, simply select 'Database Table' as your import source option, and choose your connected database from the dropdown list that contains the database table that you want to move under 'Data Source', which is the MySQL database in this example. Select the database table concerned.

Next, under Import Destination, select the destination database as the Data Source, your Schema Name of choice, and the Table Name you prefer for the new table. The destination data source in this example is a PostgreSQL database called 'PostgresDemo'. You can then specify your sync configurations, such as the scheduled frequency for how often this Data Import job should occur, which can be every few minutes, hours, days, weeks or months and so on. In this example, the job is scheduled to run every day at 3:00 in the morning.

You can also specify the data types, for how you want the data to be stored and formatted in the new destination table. Once you're ready, validate the sync configuration and save the job.

Your SQL database Data Import job is now ready to go! You can either wait for the job to run at its scheduled time, or you can click on 'Execute' for the job to run immediately. Once the job has completed successfully, you can click on the link to the desination table under the 'Destination' column, to view your new database table. You've successfully migrated your database table from one SQL database to another!

You can now build queries to join your new database table with existing tables in the destination database. For example, using the Holistics Query Editor under the Analyze tab, you can select the database under Data Source, then build your query that joins your new database table to another table, to pull out the columns and data that you need from both tables!

For more information on the Holistics Data Preparation process, please have a look at our supporting documentation on building your data pipelines.