How to Export the Catalog in Vertica

To start with we need to know what is the catalog and what does it serves for ? Database Catalog The catalog is a set of data about data also called metadata it stores information about the database objects. To export the catalog in Vertica we need to use the export_catalog() function. Basic syntax :

select export_catalog('/path/file.sql','option');
Where the options that can be passed to the function are: DESIGN - is the default option and it will generate the DDL of schemas, tables, constraints, views, and projections. DESIGN_ALL - will generate the DDL of design contexts and their tables. TABLES - will generate the DDL of tables and constraints.

Examples of exporting the DDL of your catalog.

  • Using the default(DESIGN) option
(dbadmin@:5433) [dbadmin] * select export_catalog('/tmp/catalog.sql');
export_catalog
-------------------------------------
Catalog data exported successfully

(1 row)

-- Read from the generated file
(dbadmin@:5433) [dbadmin]  ! head /tmp/catalog.sql
CREATE SCHEMA TxtIndex;
CREATE SCHEMA v_demo;
CREATE SCHEMA dba;
CREATE TABLE public.customer_dimension
(
customer_key int NOT NULL,
customer_type varchar(16),
customer_name varchar(256),
  • Using the DESIGN_ALL option.
(dbadmin@:5433) [dbadmin]  select export_catalog('/tmp/catalog_ALL.sql','DESIGN_ALL');
export_catalog
-------------------------------------
Catalog data exported successfully
  • Using the TABLES option.
  • the exported file will contain only the create table and the constraints definition.
(dbadmin@:5433) [dbadmin]  select export_catalog('/tmp/catalog_tables.sql','TABLES');
export_catalog
-------------------------------------
Catalog data exported successfully
You can use the export_catalog function to export your metadata regularly and maintain a version control using Git or any other version control software.