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…

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?…

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…

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,…

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…

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

SQL SET Operator MINUS Alternative in Hive and Examples

The set operators in SQL are used to combine similar data set of two or more SELECT statements. Here similar data set literally means the number of columns and its data type should match, otherwise you must explicitly type cast the data types of the values in SELECT statements. Hive does support UNION and UNION ALL set operator, INTERSECT and MINUS are not supported as of now. In this article, we will check SQL set operator MINUS alternative in Hive with an example. SQL SET Operator MINUS Alternative in Hive…

Comments Off on SQL SET Operator MINUS Alternative in Hive and Examples
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…

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…

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

What are SQL Features Missing in Hive?

Apache Hive syntax looks similar to SQL-92 standards but does not fully compatible to SQL-92. Storage and querying underlying table’s closes resembles traditional databases available in industry. HiveQL provides some of the extensions that are not present in traditional databases. There are some features gap between traditional SQL and Apache Hive. In this article, we will check some basic and import SQL features missing in Hive. SQL features Missing in Hive Below are some of important yet basic SQL features missing in Hive: Online Transaction Processing (OLTP)Correlated Sub-queriesMaterialized ViewsTruncate TableIndexes…

Comments Off on What are SQL Features Missing in Hive?
March 31, 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.…

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