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]'] [, 'mark-ksafe']] )
The SQL script conforms to the following requirements:
-
Only includes objects to which the user has access.
-
Orders CREATE statements according to object dependencies so they can be recreated in the correct sequence. For example, if a table is in a non-PUBLIC schema, the required CREATE SCHEMA statement precedes the CREATE TABLE statement. Similarly, a table's CREATE ACCESS POLICY statement follows the table's CREATE TABLE statement.
-
If possible, creates projections with their KSAFE clause, if any, otherwise with their OFFSET clause.
Setting export scope
The EXPORT_OBJECTS scope
argument specifies the scope of the export operation:
To export... | Set scope to... |
---|---|
All objects to which you have access | Empty string ('') |
One or more named database objects and related objects |
Comma-delimited list of objects. For example:
You can optionally qualify the schema with the name of the current database:
If you specify a schema, EXPORT_TABLES exports all objects in that schema to which you have access. If you name a table that references a sequence, the generated script shows the sequence, then the table that references the sequence, and finally any projections of that table. |
Note
EXPORT_OBJECTS does not export grants. Preserving grants on libraries can be especially important when you upgrade your database: if the prototypes of UDx libraries change, Vertica drops the grants on them. In order to preserve grants on UDx libraries, back up the grants before upgrading, and then restore them in the upgraded database. For details, see Backing up and restoring grants.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 of dependencies. 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 replicates K-safety 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 PROJECTION
- CREATE VIEW
- CREATE SEQUENCE
- CREATE ACCESS POLICY
- CREATE PROCEDURE (stored)
- CREATE SCHEDULE
- CREATE TRIGGER
The following output 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 individual objects
You can specify one or more objects as the function scope, where multiple objects are specified in a comma-delimited list. The names of objects in any non-PUBLIC schema must include their respective schemas. The objects to export can include schemas, tables, views, and sequences. Accordingly, the SQL script includes the following statements, depending on what objects you list and their dependencies:
-
CREATE SCHEMA
-
CREATE TABLE
-
ALTER TABLE (to add constraints)
-
CREATE VIEW
-
CREATE SEQUENCE
-
CREATE ACCESS POLICY
-
CREATE PROCEDURE (Stored)
-
CREATE SCHEDULE
-
CREATE TRIGGER
If listed objects have overlapping scopes—for example, the list includes a table and one of its projections—EXPORT_OBJECTS exports the projection only once:
=> select export_objects ('','customer_dimension, customer_dimension_super');
export_objects
--------------------------------------------------------------------------
CREATE TABLE public.customer_dimension
(
customer_key int NOT NULL,
customer_type varchar(16),
customer_name varchar(256),
...
CONSTRAINT C_PRIMARY PRIMARY KEY (customer_key) DISABLED
);
CREATE ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END ENABLE;
CREATE PROJECTION public.customer_dimension_super /*+basename(customer_dimension),createtype(L)*/
(
customer_key,
customer_type,
customer_name,
...
)
AS
SELECT customer_dimension.customer_key,
customer_dimension.customer_type,
customer_dimension.customer_name,
...
FROM public.customer_dimension
ORDER BY customer_dimension.customer_key
SEGMENTED BY hash(customer_dimension.customer_key) ALL NODES OFFSET 0;
SELECT MARK_DESIGN_KSAFE(0);
You can export stored procedures by specifying their name and the types of their formal parameters. For stored procedures with the same name but different formal parameters, you can export all implementations by exporting its parent schema.
To export a particular implementation, specify either the types or both the names and types of its formal parameters. The following example specifies the types:
=> SELECT EXPORT_OBJECTS('','raiseXY(int, int)');
EXPORT_OBJECTS
----------------------
CREATE PROCEDURE public.raiseXY(x int, y int)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';
SELECT MARK_DESIGN_KSAFE(0);
(1 row)
To export all implementations of the overloaded stored procedure raiseXY, export its parent schema:
=> SELECT EXPORT_OBJECTS('','public');
EXPORT_OBJECTS
----------------------
...
CREATE PROCEDURE public.raiseXY(x int, y varchar)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';
CREATE PROCEDURE public.raiseXY(x int, y int)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';
SELECT MARK_DESIGN_KSAFE(0);
(1 row)