User Defined SQL Function in Vertica

               Today, we will have a look at creating another Vertica SQL Function and the clauses and restrictions while creating them, Function are the a good method of reducing your  SQL codes and using the again and again ( Re usability ). First, we will list down the rules for writing a SQL function in Vertica and then explain it with an example.

Read more ...

Estimating Database and Table Size in Vertica

Hello All,

Today we will be looking at how to calculate size of database / tables in a Vertica Cluster.

So how is Vertica different in terms of storage – It is columnar

So Vertica stores data in Columnar format and hence the compression ratio is much better than other databases. I will not emphasize on Columnar storage of Vertica in this article (Will surely have a complete different article for this).

Below are some snippets through which you can get to know how much data is residing in your database or how much space is consumed by your data in database.

Read more ...

Vertica SQL function Words_to_number

Hi There,

Just a quick update where we left last time - Vertica SQL Function

Here is a good example of Vertica SQL Function

The name of this function is words_to_number
This function takes arguments as a two word digit say 'seventy one' and gives the result in numbers .i.e 71

Just observe the way we can use the SQL function in Vertica to optimize the reusable codes.

Function Definition

CREATE OR REPLACE FUNCTION WORDS_TO_NUMBER(x VARCHAR) RETURN VARCHAR
AS BEGIN
RETURN
CASE
WHEN REGEXP_COUNT(SPLIT_PART(x, ' ', 1), 'ty') = 1 then
(CASE 
WHEN SPLIT_PART(lower(x), ' ', 1) = 'twenty' THEN 2*10 
WHEN SPLIT_PART(lower(x), ' ', 1) = 'thirty' THEN 3*10 
WHEN SPLIT_PART(lower(x), ' ', 1) = 'forty' THEN 4*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'fifty' THEN 5*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'sixty' THEN 6*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'seventy' THEN 7*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'eighty' THEN 8*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'ninety' THEN 9*10 ELSE '0' END)
ELSE '0' END +
CASE
WHEN REGEXP_COUNT(x, ' [a-z]') = 1 then
(CASE 
WHEN SPLIT_PART(lower(x), ' ', 2) = 'one' THEN 1*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'two' THEN 2*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'three' THEN 3*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'four' THEN 4*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'five' THEN 5*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'six' THEN 6*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'seven' THEN 7*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'eight' THEN 8*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'nine' THEN 9*1
ELSE '0' END)
WHEN REGEXP_COUNT(x,' ') = 0 THEN
(CASE
WHEN lower(x) = 'one' THEN 1
WHEN lower(x) = 'two' THEN 2
WHEN lower(x) = 'three' THEN 3
WHEN lower(x) = 'four' THEN 4
WHEN lower(x) = 'five' THEN 5
WHEN lower(x) = 'six' THEN 6
WHEN lower(x) = 'seven' THEN 7
WHEN lower(x) = 'eight' THEN 8
WHEN lower(x) = 'nine' THEN 9
ELSE '0' END) 
ELSE 0 END;

END;

Always remember to use BEGIN and END statements as in Function definition.
And the most important or you can say limit to Vertica SQL Function is no usage of Select statement is allowed in Function statement.

Some example to use this function
Function Usage

nnani=> select WORDS_TO_NUMBER('twenty three');

 WORDS_TO_NUMBER
-----------------
 23
(1 row)

You can also try some more example with this function

select WORDS_TO_NUMBER('ninety nine');

select WORDS_TO_NUMBER('seventy one');

select WORDS_TO_NUMBER('one');

The rules / limits when using this functions are the words can only extend till two digits, you cannot have a three word digit in the arguments.

Go ahead and use this function. Let me know your reviews on this.

Read more ...

Vertica SQL Functions

Hi all,

Unlike other databases, vertica is not so flexible when it comes to User defined functions.

Function can be defined through two ways in Vertica
1. Vertica SQL Function
2. Vertica UDF ( User Defined Function )

Today, we will be looking at an example of 1 type Vertica SQL Function.

Vertica SQL Function

They can be defined using the CREATE FUNCTION statement and with a return type mandatory

Syntax for SQL Function

CREATE [ OR REPLACE ] FUNCTION
[[db-name.]schema.]function-name ( [ argname argtype [, ...] ] )
RETURN rettype
AS
BEGIN
RETURN expression;
END;


Now an example to elaborate the above syntax

CREATE FUNCTION ADD_DAYS(x TIMESTAMP, y INT) RETURN TIMESTAMP
AS BEGIN
RETURN ( CAST (x AS TIMESTAMP) + y )
END;

This example will create ADD_DAYS Function which adds the number of days in int to a date / timestamp value.

The arguments defined are of two types TIMSTAMP and INT.

So examples of how this function works

EXAMPLE USAGE :

nnani=> select ADD_DAYS('2013-01-20',1);
      ADD_DAYS
---------------------
 2013-01-21 00:00:00
(1 row)

nnani=> select ADD_DAYS('2013-01-20',60);
      ADD_DAYS
---------------------
 2013-03-21 00:00:00
(1 row)

Read more ...

History Of Vertica

Hello All,

This is my first blog with Vertica-database blogs.
I think starting with the history of Vertica database could be the best option at this time.

For people new, Hope you all find this blog interesting.

Vertica was founded in 2005 by database researcher Michael Stonebraker and Andrew Palmer.
Vertica is the only pure Columnar Database existing till date.

Did you know Vertica before commercializing was called as  C- Store database.
Yes  C- Store ( Column Store )
The C-Store project was a collaboration between MIT, Yale, Brandeis University. Brown University, and UMass Boston .


A Short note to C -Store

C-Store is a read-optimized relational DBMS that contrasts sharply with most current systems, which are write-optimized. Among the many differences in its design are: storage of data by column rather than by row, careful coding and packing of objects into storage including main memory during query processing, storing an overlapping collection of column-oriented projections, rather than the current fare of tables and indexes, a non-traditional implementation of transactions which includes high availability and snapshot isolation for read-only transactions, and the extensive use of bitmap indexes to complement B-tree structures. 



More detail about this project can be found here

C-Store project

The last release for C-Store project was C-Store version 0.2 is now publicly available. Last released in October 2006, It is built with open source tools and runs on Linux x86 computers.


There is also a White paper release by Michael Stonebreaker and team

C-Store whitepaper

Enjoy reading.
Appreciate your comments.

Read more ...