How to Export/Import object DDL and data in Vertica

In this tutorial we will learn how to export one table and restore it using vsql commands.

• Backup:

In this step we will make use of "EXPORT_TABLES" Vertica native function that will export the DDL of our table to the file we tell it to buy using the "-o" vsql option. Make sure you use full path description. Next we will export the content of the table we choose to export and we zipp it in the same process.

vsql -U dbadmin -w dbadmin -At -c "SELECT EXPORT_TABLES('table_one')" -o /vertica/backup/table_one.sql
vsql -U dbadmin -w dbadmin -F $'|' -At -c "SELECT * FROM table_one" | gzip -c  '/vertica/backup_table_one.gz'

Time to restore our objects. First create the table by running the script. Next use the "COPY" command to load the table with the data from the file table_one.gz, good to remember that Vertica will work with zipped file.(gret option to save netwok IO).

• Restore :

vsql -U dbadmin -w dbadmin -f /vertica/backup/table_one.sql
vsql -U dbadmin -w dbadmin -c "COPY table_one FROM '/vertica/backup/table_one.gz' GZIP DELIMITER '|';"

More on how to export and import objects in Vertica in the next tutorials.