Data export and replication

Vertica supports transferring data by exporting or replicating it.

Vertica supports transferring data by exporting or replicating it. These two methods differ slightly:

Exporting data
Transforms the data into a new format. You can export data to files to use with other tools or as Vertica external tables. You can also export data to another Vertica database using the COPY FROM VERTICA or EXPORT TO VERTICA statements.
Replicating data
Copies data in the Vertica native format. Replicating data is more efficient than exporting because Vertica does not need to decompress, sort, and recompress the data. However, unlike exporting, replication only copies data between Vertica databases running in the same mode (Eon or Enterprise).

Data export

Reasons you might want to export data from Vertica include:

  • To use the data in external tables; see Working with external data.

  • To share data with other clients or consumers in an ecosystem.

  • To copy data to another Vertica cluster.

Vertica provides two ways to export data:

  • Export data to files using data exporters. You can export the results of a SELECT query to files in Parquet, ORC, or delimited format. You can use partitioning in the export to reduce the file size and improve performance when reading the data in external tables. For more information, see File export.

  • Export data directly between Vertica clusters. Direct export lets you copy data between databases without having to save it to an intermediate file. You can perform the operation as either an import from another database or an export to another database; aside from the direction of data travel, these two operations are equal. Direct export lets you copy data between Vertica databases which have different configurations, such as clusters running in different modes or have a different number of nodes or shards. For more information, see Database export and import.

Data replication

Replication transfers data between Vertica databases in native format. Vertica supports two types of replication:

  • vbr-based replication uses the Vertica backup script to copy ROS containers from one database to another. This type of replication supports both Eon Mode and Enterprise Mode databases. However, the two databases must be running in the same mode and have the same number of nodes (for Enterprise Mode databases) or primary nodes (for Eon Mode databases). See Replicating objects to another database cluster for more information about this replication method.

  • Server-based replication is an Eon Mode-only method that copies data directly from the communal store of one database to another. Both databases must have the same shard segmentation layout and run compatible versions of the Vertica server. The replication feature is backwards compatible on the target side of the replication. The target database can run a later version of the database than the source database.