Preserving Optimized Projections Across Drop Table Statements

nartz

Hi -

 

We sync a vertica cluster with tables from mysql daily by dropping the tables and reloading them into vertica. To optimize our vertica queries, we run db designer to tailor projections to our query needs.

 

However, when we drop/reload tables daily, we will have to re-run db designer?

 

Is there a way to 'preserve' the db designer 'strategy' so that when we drop / rebuild / reload a table it uses the same projections as before? 

Rahul_Choudhary

Hi

 

Before dropping/reloading tables you can run export_objects for the tables involved which can help you preserve the DDL of the same object/table which it has earlier.

 

You can use below command to perform the same:

 

vsql=> select export_objects('/<path>/<file_name>',<schema_name.table_name>');

 

For example:

 

dbadmin=> \dt
List of tables
Schema | Name | Kind | Owner | Comment
--------+-------+-------+---------+---------
public | test | table | dbadmin |
public | test2 | table | dbadmin |

dbadmin=> select export_objects('/home/dbadmin/test.sql','public.test');
export_objects
-------------------------------------
Catalog data exported successfully

(1 row)

dbadmin=>

 

[dbadmin@~]$ cat test.sql

CREATE TABLE public.test
(
node_name varchar(128),
node_id int,
node_state varchar(128),
node_address varchar(80),
node_address_family varchar(10),
export_address varchar(8192),
export_address_family varchar(10),
catalog_path varchar(8192),
node_type varchar(9),
is_ephemeral boolean,
standing_in_for varchar(128),
node_down_since timestamptz
);

 

CREATE PROJECTION public.test /*+createtype(A)*/
(
node_name,
node_id,
node_state,
node_address,
node_address_family,
export_address,
export_address_family,
catalog_path,
node_type,
is_ephemeral,
standing_in_for,
node_down_since
)
AS
SELECT test.node_name,
test.node_id,
test.node_state,
test.node_address,
test.node_address_family,
test.export_address,
test.export_address_family,
test.catalog_path,
test.node_type,
test.is_ephemeral,
test.standing_in_for,
test.node_down_since
FROM public.test
ORDER BY test.node_id
SEGMENTED BY hash(test.node_id, test.is_ephemeral, test.node_down_since, test.node_type, test.node_address_family, test.export_address_family, test.node_address, test.node_name, test.node_state, test.standing_in_for, test.export_address, test.catalog_path) ALL NODES KSAFE 1;

CREATE PROJECTION public.abc
(
node_name,
node_id,
node_state,
node_address,
node_address_family,
export_address,
export_address_family,
catalog_path,
node_type,
is_ephemeral,
standing_in_for,
node_down_since
)
AS
SELECT test.node_name,
test.node_id,
test.node_state,
test.node_address,
test.node_address_family,
test.export_address,
test.export_address_family,
test.catalog_path,
test.node_type,
test.is_ephemeral,
test.standing_in_for,
test.node_down_since
FROM public.test
ORDER BY test.node_name,
test.node_id,
test.node_state,
test.node_address,
test.node_address_family,
test.export_address,
test.export_address_family,
test.catalog_path,
test.node_type,
test.is_ephemeral,
test.standing_in_for,
test.node_down_since
UNSEGMENTED NODE v_test_node0001;

SELECT MARK_DESIGN_KSAFE(1);

 

 

Hope this helps!!

 

Regards

Rahul Choudhary

K_Krutiy

This post have very detailed description on how to use Database Designer to build projections and explains how to get SQL scripts at the end.

 

http://www.dbjungle.com/using-database-designer-to-build-projections-with-optimal-sort-order-in-hp-vertica/

 

Hope this helps