Importing and Exporting Data Across Databases
Vertica can easily import data from and export data to other Vertica databases. Importing and exporting data is useful for common tasks such as moving data back and forth between a development or test database and a production database, or between databases that have different purposes but need to share data on a regular basis.
Moving Data Directly Between Databases
Use the following statements move data to and from another Vertica database:
To execute either of these statements requires first creating a connection to the other Vertica database.
Creating SQL Scripts to Export Data
Three functions return a SQL script you can use to export database objects to recreate elsewhere:
While copying and exporting data is similar to Backing Up and Restoring the Database, you should use them for different purposes, outlined below:
Task | Backup and Restore | COPY and EXPORT Statements |
---|---|---|
Back up or restore an entire database, or incremental changes |
YES |
NO |
Manage database objects (a single table or selected table rows) |
YES |
YES |
Use external locations to back up and restore your database |
YES |
NO |
Use direct connections between two databases |
NO |
YES |
Use external shell scripts to back up and restore your database |
YES |
NO |
Use SQL commands to incorporate copy and export tasks into DB operations |
NO |
YES |
The following sections explain how you import and export data between Vertica databases.
When importing from or exporting to a Vertica database, you can connect only to a database that uses trusted- (username-only) or password-based authentication, as described in Security and Authentication. SSL authentication is not supported.
Other Exports
This section is about exporting data to another Vertica database. For information about exporting data to Parquet files in HDFS, see Exporting Data in Integrating with Apache Hadoop.