导出表

Vertica 函数 EXPORT_TABLES 为当前数据库中的表和相关对象导出 DDL。生成的 SQL 包含您有权访问的所有非虚拟表对象。您可以使用此 SQL 在不同的群集中重新创建表和相关的非虚拟对象。

按如下方式执行 EXPORT_TABLES：

EXPORT_TABLES( ['[destination]' [, '[scope]']] )

SQL 脚本符合以下要求：

  • 仅包括用户有权访问的对象。

  • 根据对象依赖关系对 CREATE 语句进行排序，以便可以按正确的顺序重新创建它们。例如，如果一个表引用了一个命名序列，则 CREATE SEQUENCE 语句在 CREATE TABLE 语句之前。同样，表的 CREATE ACCESS POLICY 语句跟在表的 CREATE TABLE 语句之后。

设置导出范围

EXPORT_TABLES scope 实参指定导出操作的范围：

导出所有表对象

如果将范围参数设置为空字符串 ('')，EXPORT_TABLES 将导出所有表及其相关对象。例如，以下对 EXPORT_TABLES 的调用将 VMart 数据库中的所有表对象导出到指定的输出文件。

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

导出的 SQL 包含以下类型的语句，具体取决于重新创建表和任何相对对象（例如架构、序列和访问策略）时的需求：

  • CREATE SCHEMA

  • CREATE TABLE

  • CREATE SEQUENCE

  • CREATE ACCESS POLICY

  • ALTER TABLE（用于添加外键约束）

导出单个表对象

EXPORT_TABLES 可以指定表和表相关对象（例如要导出的序列或架构）的逗号分隔列表。生成的 SQL 脚本包含指定对象及其依赖对象的 CREATE 语句：

  • CREATE SCHEMA

  • CREATE TABLE

  • CREATE SEQUENCE

  • CREATE ACCESS POLICY

  • ALTER TABLE（用于添加外键）

例如，以下对 EXPORT_TABLES 的调用会导出两个 VMart 表： store.store_sales_fact 和 store.store_dimension：

=> SELECT export_tables('','store.store_sales_fact, store.store_dimension');
                              export_tables
-------------------------------------------------------------------------

CREATE TABLE store.store_dimension
(
    store_key int NOT NULL,
    store_name varchar(64),
    store_number int,
    store_address varchar(256),
    store_city varchar(64),
    store_state char(2),
    store_region varchar(64),
    floor_plan_type varchar(32),
    photo_processing_type varchar(32),
    financial_service_type varchar(32),
    selling_square_footage int,
    total_square_footage int,
    first_open_date date,
    last_remodel_date date,
    number_of_employees int,
    annual_shrinkage int,
    foot_traffic int,
    monthly_rent_cost int,
    CONSTRAINT C_PRIMARY PRIMARY KEY (store_key) DISABLED
);

CREATE TABLE store.store_sales_fact
(
    date_key int NOT NULL,
    product_key int NOT NULL,
    product_version int NOT NULL,
    store_key int NOT NULL,
    promotion_key int NOT NULL,
    customer_key int NOT NULL,
    employee_key int NOT NULL,
    pos_transaction_number int NOT NULL,
    sales_quantity int,
    sales_dollar_amount int,
    cost_dollar_amount int,
    gross_profit_dollar_amount int,
    transaction_type varchar(16),
    transaction_time time,
    tender_type varchar(8)
);

ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_date FOREIGN KEY (date_key) references public.date_dimension (date_key);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_product FOREIGN KEY (product_key, product_version) references public.product_dimension (product_key, product_version);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_store FOREIGN KEY (store_key) references store.store_dimension (store_key);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_promotion FOREIGN KEY (promotion_key) references public.promotion_dimension (promotion_key);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_customer FOREIGN KEY (customer_key) references public.customer_dimension (customer_key);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_employee FOREIGN KEY (employee_key) references public.employee_dimension (employee_key);

以下对 EXPORT_TABLES 的调用指定要导出 VMart 架构 store 中的所有表：

=> select export_tables('','store');
                          export_tables
-----------------------------------------------------------------
CREATE SCHEMA store;

CREATE TABLE store.store_dimension
(
 ...
);

CREATE TABLE store.store_sales_fact
(
 ...
);

CREATE TABLE store.store_orders_fact
(
  ...
);

ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_date FOREIGN KEY (date_key) references public.date_dimension (date_key);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_product FOREIGN KEY (product_key, product_version) references public.product_dimension (product_key, product_version);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_store FOREIGN KEY (store_key) references store.store_dimension (store_key);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_promotion FOREIGN KEY (promotion_key) references public.promotion_dimension (promotion_key);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_customer FOREIGN KEY (customer_key) references public.customer_dimension (customer_key);
ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_employee FOREIGN KEY (employee_key) references public.employee_dimension (employee_key);
ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_product FOREIGN KEY (product_key, product_version) references public.product_dimension (product_key, product_version);
ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_store FOREIGN KEY (store_key) references store.store_dimension (store_key);
ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_vendor FOREIGN KEY (vendor_key) references public.vendor_dimension (vendor_key);
ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee FOREIGN KEY (employee_key) references public.employee_dimension (employee_key);

(1 row)

另请参阅