variable in vertica

Create and drop foreign key

Vertica doesn't enforce the constraints by default.Here is an example that shows drop foreign key/tables , creating tables, primary key and then adding foreign key relationship to the table.

Drop foreign key:

ALTER TABLE sales  DROP CONSTRAINT IF EXISTS FK_Sales_product;


DROP TABLE IF EXISTS product cascade;
Drop table if exists sales cascade;

create table product(
product_id int NOT NULL ,
description  varchar(100) ,
date_added datetime);

ALTER TABLE product ADD PRIMARY KEY (product_id) ;


Drop table if exists sales;
CREATE TABLE sales (
sales_id integer NOT NULL,
code varchar(20),
quantity smallint,
amount money,
product_id int NOT NULL,
sales_date datetime);

ALTER TABLE sales ADD PRIMARY KEY (sales_id) ;

INSERT INTO sales
select 1,'RC012',1,50,1001,now();


INSERT INTO product
select 1001,'Wagon',now();
 

Vertica 7.1 does not enforce constraint yet. Let us try to violate the primary key constraint. Then detect the constraint violation using analyse_constraints.

Insert into sales
select 1,'RC999',1,50,1001,now();

 12:21:52  [INSERT - 1 row(s), 0.026 secs]  Command processed
... 1 statement(s) executed, 1 row(s) affected, exec/fetch time: 0.026/0.000 sec  [1 successful, 0 warnings, 0 errors]


dbadmin=> SELECT ANALYZE_CONSTRAINTS('sales');
 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
 public      | sales      | sales_id     | C_PRIMARY       | PRIMARY         | ('1')
(1 row)


 

Creating foreign key:

dbadmin=> ALTER TABLE sales    ADD CONSTRAINT fk_sales_product FOREIGN KEY(product_id)     REFERENCES product(product_id);

WARNING 4887:  Table sales has data. Queries using this table may give wrong results if the data does not satisfy this constraint
HINT:  Use analyze_constraints() to check constraint violation on data
ALTER TABLE

 

Now let us try to violate integrity constraint .

Insert into sales
select 2,'RC999',1,50,1002,now();

It doesnt give any error.


dbadmin=>  SELECT ANALYZE_CONSTRAINTS('sales');
 Schema Name | Table Name | Column Names | Constraint Name  | Constraint Type | Column Values
-------------+------------+--------------+------------------+-----------------+---------------
 public      | sales      | sales_id     | C_PRIMARY        | PRIMARY         | ('1')
 public      | sales      | product_id   | fk_sales_product | FOREIGN         | ('1002')


 

Join of both tables constrainted by PK-FK relationship also doesnt return any error.

dbadmin=>select s.code, p.description from 
sales s join product p on s.product_id=p.product_id
 
 code  | description
-------+-------------
 RC012 | Wagon
 RC999 | Wagon
 RC999 | Wagon
 RC999 | Wagon

 

Live Aggregate Projection

Create live aggregate projections for large tables that you add data to frequently. 

• When anchor table is large.
• The data need to be aggregated.
• No update, delete, or merge data in the anchor table.

Anchor Projection

An anchor table is a database table that is the source for data in a projection. All projections have anchor tables. The projections are the physical storage of the table data. When you drop an anchor table and specify the CASCADE keyword, HP Vertica drops the anchor projection and the anchor table.

An anchor projection is the physical storage for an anchor table that has a live aggregate projection. You must create an anchor projection before you create a live aggregate projection, including Top-K projections. The anchor projection segmentation must be a subset of the live aggregate projection.

When HP Vertica loads data into an existing anchor table, it appends the data to the anchor projection. Then it aggregates the data and adds it to the live aggregate projection. After you create a live aggregate or Top-K projection, you cannot perform any update, delete, or merge operations on the anchor table or anchor projection.

When you drop the anchor table and specify the CASCADE keyword, HP Vertica drops the anchor table, the anchor projection, and the live aggregate projection.

Pre-Join Projection

A subset of query specific projections that are used to store the results of a join between a single large (fact/anchor) table in the logical schema with one or more dimension tables. As with other query-specific projections, HP Vertica calculates and stores a new result set each time data is inserted or loaded into these tables. This provides a significant performance advantage over joining tables when queries are run.

The result set of a pre-join projection is typically sorted for a specific query or commonalities in a class of queries based on the query predicate. This provides optimal query performance.

Pre-join projections can have only inner joins between tables on their primary and foreign key columns. Outer joins are not allowed in pre-join projections

Before creating the pre-join projection, we need following steps.

1. Create table with primary keys and add foreign key constraints
2. Create anchor tables
3. Create pre-join projection

Here is the example.


<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: &quot;andale mono&quot; , &quot;lucida console&quot; , &quot;monaco&quot; , &quot;fixed&quot; , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code style="color: black; word-wrap: normal;">
ALTER TABLE sales  DROP CONSTRAINT IF EXISTS FK_Sales_product;

DROP TABLE IF EXISTS product cascade;
Drop table if exists sales cascade;

create table product(
product_id int NOT NULL ,
description  varchar(100) ,
date_added datetime);

ALTER TABLE product ADD PRIMARY KEY (product_id) ;


Drop table if exists sales;
CREATE TABLE sales (
sales_id integer ,
code varchar(20),
quantity smallint,
amount money,
product_id int NOT NULL,
sales_date datetime);

ALTER TABLE sales ADD PRIMARY KEY (sales_id) ;


ALTER TABLE sales    ADD CONSTRAINT fk_sales_product FOREIGN KEY(product_id)     REFERENCES product(product_id);

INSERT INTO sales
select 1,'RC012',1,50,1001,now();


INSERT INTO product
select 1001,'Wagon',now();


--SELECT ANALYZE_CONSTRAINTS('sales');


CREATE PROJECTION sales_anc (
        sales_id,
        code,
        quantity,
        amount,
        product_id,
        sales_date)
   AS SELECT * FROM public.sales UNSEGMENTED ALL NODES;

 CREATE PROJECTION product_anc (
       product_id,
       description,
       date_added)
   AS SELECT * FROM public.product UNSEGMENTED ALL NODES;
 
   select refresh('sales');
   select refresh('product');
 
   select * from projections where anchor_table_name='sales';
 
 
 
   CREATE PROJECTION Sales_product (
       sales_id,
       code,
       amount,
       description,
       sales_date
       )
   AS SELECT sales_id,code,amount,description,sales_date
   FROM public.sales s join product p ON s.product_id=p.product_id
   ORDER BY s.sales_date;
 
 
   explain SELECT count(*)
   FROM public.sales s join product p ON s.product_id=p.product_id
   ORDER BY s.sales_date;
 
 
</code> </pre>

Types of Vertica Projections

Types of projections:

·       Super Projection

·       Query specific Projection

·       Pre-Join Projection

·       Anchor Projection

·       Live-AggregateProjection

·       Top-K Projection

Super projection

When you create a table, by default Vertica creates a super projection consisting of all columns.

CREATE TABLE public.sales(
sales_id int NOT NULL,
code varchar(20) NOT NULL,
quantity int,
amount numeric(18,4),
product_id int,
sales_date timestamp);

 ALTER TABLE public.sales ADD CONSTRAINT pk_sales PRIMARY KEY (sales_id, code);

SELECT ps.node_name,p.projection_name, row_count, p.is_super_projection

FROM projection_storage PS join projections p ON

p.projection_name=ps.projection_name

WHERE ps.anchor_table_name='sales' and p. projection_basename='sales'

ORDER BY p.projection_name ,ps.node_name;

As k safe =1, Vertica creates 2 super projections (_b0 and _b1) and distributes evenly across available nodes.

Check in projection_columns  tables for the columns inside a projection.