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');
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