Exporting Objects

The Vertica function EXPORT_OBJECTS generates a SQL script that you can use to recreate non-virtual catalog objects on a different cluster, as follows:

EXPORT_OBJECTS( [ 'destination'] [, 'scope' ] [, 'ksafe' ] )

This function and EXPORT_CATALOG return equivalent output.

Setting Scope of Export

You can set the scope of the export operation to various levels:

To export... Use this scope...

All non-virtual objects to which the user has access, including constraints.

An empty string (' ')

One or more named objects, such as tables or views in one or more schemas. You can optionally qualify the schema with a database prefix, myvertica.myschema.newtable.

A comma-delimited list of items. For example:

'myschema.newtable, yourschema.oldtable'

A named database object in the current search path. You can specify a schema, table, or view. If the object is a schema, the script includes objects to which the user has access.

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.

EXPORT_OBJECTS always tries to recreate projection statements with their KSAFE clauses, if any; otherwise, with their OFFSET clauses.

Function Syntax

EXPORT_OBJECTS( [ 'destination'  ] , [ 'scope'  ] , [  'ksafe' ] )

Exporting All Objects

If you set the scope parameter to an empty string (''), Vertica exports all non-virtual objects from the source database in order dependency. Running the generated SQL script on another cluster creates all referenced objects and their dependent objects.

By default, the function's KSAFE argument is set to true. In this case, the generated script calls MARK_DESIGN_KSAFE, which propagates the K-safety setting of the original database.

=> SELECT EXPORT_OBJECTS(
     '/home/dbadmin/xtest/sql_objects_all.sql',
     '',
     'true');
           EXPORT_OBJECTS           
-------------------------------------
 Catalog data exported successfully
(1 row)

The SQL script includes the following types of statements:

  • CREATE SCHEMA
  • CREATE TABLE
  • CREATE VIEW
  • CREATE SEQUENCE
  • CREATE PROJECTION (with ORDER BY and SEGMENTED BY)
  • ALTER TABLE (to add constraints)
  • PARTITION BY

Here is a snippet that includes the start and end of the output SQL file, including the MARK_DESIGN_KSAFE statement: 

CREATE SCHEMA store;
CREATE SCHEMA online_sales;
CREATE SEQUENCE public.my_seq ;
CREATE TABLE public.customer_dimension
(
    customer_key int NOT NULL,
    customer_type varchar(16),
    customer_name varchar(256),
    customer_gender varchar(8),
    title varchar(8),
    household_id int,
...

);
. 
.
.
SELECT MARK_DESIGN_KSAFE(1);

Exporting a List of Objects

Use a comma-separated list of objects as the function scope. The list can include one or more tables, sequences, and views in the same, or different schemas, depending on how you qualify the object name. For instance, specify a table from one schema, and a view from another (schema2.view1).

The SQL script includes the following types of statements, depending on what objects you include in the list:

  • CREATE SCHEMA
  • CREATE TABLE
  • ALTER TABLE (to add constraints)
  • CREATE VIEW
  • CREATE SEQUENCE

If you specify a view without its dependencies, the function displays a WARNING. The SQL script includes a CREATE statement for the dependent object, but will be unable to create it without the necessary relations: 

=> SELECT EXPORT_OBJECTS(
     'nameObjectsList', 
     'test2, tt, my_seq, v2' );
WARNING 0:  View public.v2 depends on other relations
           EXPORT_OBJECTS           
-------------------------------------
 Catalog data exported successfully
(1 row)

This example explicitly sets the ksafe argument explicitly to true.

=> SELECT EXPORT_OBJECTS(
     '/home/dbadmin/xtest/sql_objects_table_view_KSAFE.sql',
     'v1, test7', 
     'true');
           EXPORT_OBJECTS           
-------------------------------------
 Catalog data exported successfully
(1 row)

Here are the contents of the output file of the example, showing the sample table test7 and the v1 view: 

CREATE TABLE public.test7
(
    a int,
    c int NOT NULL DEFAULT 4,
    bb int
);
CREATE VIEW public.v1 AS
 SELECT tt.a
 FROM public.tt;

SELECT MARK_DESIGN_KSAFE(1);

Exporting a Single Object

Specify a single database object as the function scope. The object can be a schema, table, sequence, or view. The function exports all non-virtual objects associated with the one you specify. 

=> SELECT EXPORT_OBJECTS(
     '/home/dbadmin/xtest/sql_objects_viewobject_KSAFE.sql',
     'v1', 
     'true');
           EXPORT_OBJECTS           
-------------------------------------
 Catalog data exported successfully
(1 row)

The output file contains the v1 view: 

CREATE VIEW public.v1 AS
 SELECT tt.a
 FROM public.tt;

SELECT MARK_DESIGN_KSAFE(1);

See Also

Exporting Tables