Vertica

Vertica NULL Handling Functions: ISNULL, IFNULL, NULLIF, COALESCE, NVL, NVL2, ZEROIFNULL, NULLIFZERO

The NULL values are ‘unknown’ or unavailable values. You cannot treat these values like how you treat blank values. The data generation system may add NULL values if values are missing or unknown value. Vertica provides various NULL functions using those you can tell the database how to treat NULL values. In this article, we will check Vertica NULL handling functions usage with some examples. Vertica NULL Handling Functions As mentioned earlier, you can tell Vertica database on how to treat NULL values using NULL handling functions. Below is the…

Continue ReadingVertica NULL Handling Functions: ISNULL, IFNULL, NULLIF, COALESCE, NVL, NVL2, ZEROIFNULL, NULLIFZERO
Comments Off on Vertica NULL Handling Functions: ISNULL, IFNULL, NULLIF, COALESCE, NVL, NVL2, ZEROIFNULL, NULLIFZERO
April 9, 2019
Vertica

Vertica Sequence- How to Create and Use it?

A sequence in Vertica is named object in an individual Vertica database schema, which provides unique incremental values. In most of the cases, sequence in Vertica are used to generate unique numbers that can use used as a primary key or surrogate key. In this article, we will check what is Vertica sequence, how to create and use it, types of sequences and restriction on using sequences. Vertica Sequence Overview A sequence output value is an integer. You can use this integer values wherever you would use numeric values. You…

Continue ReadingVertica Sequence- How to Create and Use it?
Comments Off on Vertica Sequence- How to Create and Use it?
April 8, 2019
Vertica

How to Connect Vertica Database using JDBC Driver?

The Vertica is one of the widely used analytics database clusters. You can connect to Vertica using many methods such as JDBC, ODBC, etc. You can use any programming language that supports JDBC connection string to connect Vertica database using JDBC driver. Almost all modern programming language provides api to use jdbc drivers. In this article, we will check how to connect to Vertica using JDBC driver. We have used Python as a programming language to demonstrate jdbc connection. Vertica JDBC Driver The HP Vertica comes with support to JDBC…

Continue ReadingHow to Connect Vertica Database using JDBC Driver?
Comments Off on How to Connect Vertica Database using JDBC Driver?
April 5, 2019
Vertica

What are Vertica Query History Tables? – Explanation

The Vertica query history tables are used for various applications, for example, identify long running queries, identify skewed node, identify the load on Vertica cluster. Vertica supports management console to identify the cluster status. The Vertica database also provides tables that stores information about the requests that are made to server. Information includes query history, time, username, userid, etc. In this article, we will check Vertica query history tables available in the Vertica analytics database. Before jumping into query history tables, let us check why query historical data is required?…

Continue ReadingWhat are Vertica Query History Tables? – Explanation
Comments Off on What are Vertica Query History Tables? – Explanation
April 5, 2019
Vertica

Vertica Type Conversion Functions and Examples

Vertica analytics data warehouse system supports various types of conversion functions. These Vertica type conversion functions are commonly used to changed data type of values stores in database tables. In SQL, type conversion is sometimes called type casting as well. These type conversion functions use common calling functions i.e. the first argument is the value to be formatted which will be either expression or table column name, and the second argument is a template that defines the output or input format. Related articles: Vertica Regular Expression Functions and ExamplesVertica Set…

Continue ReadingVertica Type Conversion Functions and Examples
Comments Off on Vertica Type Conversion Functions and Examples
April 4, 2019
Vertica

Vertica Regular Expression Functions and Examples

The Vertica regular expression functions are used to identify a precise pattern from the given string. The string could be variable or database table column name. The regular expressions are used in various applications such as extracting some numbers from string values, extracting alphanumeric values from string, extracting particular pattern from string, validating received data. You can also use regular expression to validate date, range check, extract only digits from given string etc. You can use regular expression functions along with any built-in functions such as date type conversion functions,…

Continue ReadingVertica Regular Expression Functions and Examples
Comments Off on Vertica Regular Expression Functions and Examples
April 4, 2019
Vertica

Vertica Extract Numbers using Regular Expressions

In a data warehouse environment, being a heterogeneous source, you may get lot of different kinds of data in terms of pipeline, flat files extracted from these data sources. Received data may be corrupted during transfer, or may have unwanted characters. You can use Vertica built in functions and regular expression to clean such corrupted and unwanted data. HP Vertica Support many built in functions, you can use those built in functions to extract numbers, specific string, or alphanumeric values from the string. In this article, we will discuss one…

Continue ReadingVertica Extract Numbers using Regular Expressions
Comments Off on Vertica Extract Numbers using Regular Expressions
April 3, 2019
Vertica

Vertica NVL and NVL2 Functions with Examples

Data warehouse basically integrates data from various heterogeneous sources. You may not receive perfect data every time you process or refresh data warehouse tables. It is very common to receive NULL values as most of the time source system will generate NULLs for any unknown or blank values. NULL values should be handled properly when you generate reports out of tables. In this article, we will check how to handle NULL values using Vertica NVL and NVL2 functions with some examples to demonstrate the same. Vertica NVL Function The NVL…

Continue ReadingVertica NVL and NVL2 Functions with Examples
Comments Off on Vertica NVL and NVL2 Functions with Examples
April 2, 2019
Vertica

Different Vertica Join Types and Examples

Joins in SQL are used to combine specific columns from two or more tables based on common data columns available. Multiples tables are integrated using joins. In general, joins are used to combine multiple tables. In this article, we will check different Vertica join types with an example. Test Data Below are the tables and associated data that we will be using to demonstrate different joins available in Vertica database: Stud: VMart=> select * from testdb.stud; Id | name | city ----+-------+----------- 1 | Bill | London 2 | Ram…

Continue ReadingDifferent Vertica Join Types and Examples
Comments Off on Different Vertica Join Types and Examples
April 1, 2019
Vertica

Run SQL Script File using Vertica vsql Variable Substitution

Variable substitution is nothing but passing value to a variable referred in Vertical SQL queries. Let me explain this using an example. Imagine you have written a general SQL script to generate monthly aggregated data for a particular report, you have to provide a current month values to a filter variable referred in SQL script. Another instance could be executing Vertica SQL queries from Linux shell script and passing shell variable value to a variable referred in SQL script. You can accomplish this task using Vertica vsql variable substitution method.…

Continue ReadingRun SQL Script File using Vertica vsql Variable Substitution
Comments Off on Run SQL Script File using Vertica vsql Variable Substitution
March 29, 2019