Database Export

Whether you want to copy a single table or multiple tables at the same time, Database Export allows you to easily export data from your Civis Redshift cluster into a destination database.

Getting Started

1. Go to Data.
2. Under Exports, select Database and that will open up a new database export job.

Selecting Your Source and Destination Databases

1. Under Source on the left side of the page, select the database and credential you would like to sync into your destination database.
2. Under Destination on the right side of the page, select the database and credential you would like to sync data into.

Adding a Table to Your Export

1. For each table that you would like to export, under Source on the left side of the page, type the schema and table name of the source table.
2. Under Destination on the other side of the page, type the schema and table you would like to sync that data into.
3. If you'd like to access advanced export settings (such as verifying that source and destination table row counts match or wiping the destination table on each job run), click on the ellipsis to the right of the destination schema and table field.
4. If you'd like to export multiple tables in the same job, select +Add Another at the bottom of the page.
5. Once your job is set up, click Run in the top-right corner of the page.

Exporting to a MySQL Database

You may encounter data encoding problems when exporting data from a database like Redshift into MySQL.  For example, when a value like Colón becomes Colón, take the following steps.

1.  You should first check the character_set_database of your MySQL database.
2.  Run the following in order to display the character set of the table:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,

information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA

WHERE CCSA.collation_name = T.table_collation

AND T.table_name = "<table>";

1. Run the following in order to display the character set of the table’s columns:

SELECT column_name, character_set_name FROM information_schema.`COLUMNS`

WHERE table_name = "tablename"

After doing so, if the character_set_database is latin1, and you get utf-8 as values in steps 2 and 3, chances are there is double encoding going on and character_set_database will need to be updated.  Take the following steps:

• 1a)  Update the database's character_set_database variable if possible

 ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

 1b) It is recommended to use utf8mb4 for the database variable and columns instead of utf8, since utf8 only supports 3-byte characters. Instead of 1a, this is recommended.

ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

• 2)  Assuming the above is not possible, go to the dropdown with your initials at the top rightmost corner of the Platform homepage and click Remote Hosts.  Find your MySQL remote host and update the jdbc connection url as follows: 

jdbc:mysql://your.host.com:port/yourDbName?sessionVariables=character_set_database=utf8

Scheduling Your Export

If you'd like to set your export to run on a schedule, click on the clock icon in the top-right corner of the page.  From there you can have the export run on a schedule of your choosing, or else incorporate it into a workflow.  For more information about job automation, see the Automate and Workflow help documentation.