Exporting Tables
Use the Vertica function EXPORT_TABLES
to recreate one or more tables, and related non-virtual objects, on a different cluster. EXPORT_TABLES
has the following syntax:
EXPORT_TABLES( [ 'destination'] [, 'scope' ] )
Setting Scope of Export
You can set the scope of the export operation to various levels:
To export... | Use this scope... |
---|---|
All objects to which the user has access, including constraints. |
An empty string (' ') |
One or more named objects, such as tables or sequences in one or more schemas. You can optionally qualify the schema with a database prefix, |
A comma-delimited list of table objects. For example: 'myschema.newtable, yourschema.oldtable' |
A named table object in the current search path. You can specify a schema, table, or sequence. If you specify a schema, |
The table object's name and, optionally, its path: 'VMart.myschema' |
The SQL script includes only the non-virtual objects to which the current user has access.
Note: EXPORT_TABLES
does not export views. If you specify a view name, Vertica silently ignores it and the view is omitted from the generated script. To export views, use EXPORT_OBJECTS
.
Exporting All Table Objects
If you set the scope parameter to an empty string (''
), Vertica exports all tables and their related objects:
=> SELECT EXPORT_TABLES( '/home/dbadmin/xtest/sql_tables_empty.sql', ''); EXPORT_TABLES ------------------------------------- Catalog data exported successfully (1 row)
The SQL script includes the following types of statements, depending on what is required to recreate the tables and any related objects (such as sequences):
CREATE SCHEMA
CREATE TABLE
ALTER TABLE
(to add constraints)CREATE SEQUENCE
PARTITION BY
Exporting a List of Tables
Use EXPORT_TABLE
with a comma-separated list of objects, including tables, views, or schemas:
=> SELECT EXPORT_TABLES( '/home/dbadmin/xtest/sql_tables_del.sql' 'public.student, public.test7'); EXPORT_TABLES ------------------------------------- Catalog data exported successfully (1 row)
The SQL script can include the following types of statements, depending on what is required to recreate the exported objects:
CREATE SCHEMA
CREATE TABLE
ALTER TABLE
(to add constraints)CREATE SEQUENCE
Exporting a Single Table Object
Use EXPORT_TABLES
to export one or more database table objects.
This example exports a named sequence, my_seq
, qualifying the sequence with the schema name (public
):
=> SELECT EXPORT_TABLES( '/home/dbadmin/xtest/export_one_sequence.sql', 'public.my_seq'); EXPORT_TABLES ------------------------------------- Catalog data exported successfully (1 row)
Following are the contents of the export_one_sequence.sql
output file using a more
command:
$ more export_one_sequence.sql CREATE SEQUENCE public.my_seq ;