Apache Spark

What is SQL Cursor Alternative in Spark SQL?

SQL Cursor is a database object to retrieve data from a result set one row at a time. You can also consider cursor as a temporary workspace created in database system memory when a SQL query is executed. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. Cursors are usually written using SQL procedural language such as Oracle PL/SQL, Netezza NZPL/SQL. Sample SQL Cursor Example Below is the sample Oracle PL/SQL procedure with cursor defined: CREATE OR replace PROCEDURE Sample_proc IS str1…

Continue ReadingWhat is SQL Cursor Alternative in Spark SQL?
Comments Off on What is SQL Cursor Alternative in Spark SQL?
April 15, 2019
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
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…

Continue ReadingSQL SET Operator MINUS Alternative in Hive and Examples
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…

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