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…

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…

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…

Comments Off on Vertica Sequence- How to Create and Use it?
April 8, 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
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

Vertica WITH Clause, Syntax, Usage and Examples

Let us say you have a requirement to use the results of a piece of code in your complex query. Instead of writing that piece of code everywhere, you can create a temporary table of use Vertica WITH clause. This WITH clause can be used to improve your complex SQL queries that have complex sub-queries, thus improving overall execution speed of your queries. SQL WITH clause will allow you to name your complex, repeat sub-query. You can use that name anywhere in your query just like normal database table. Vertica…

Comments Off on Vertica WITH Clause, Syntax, Usage and Examples
March 28, 2019
Apache Spark

Spark SQL Cumulative Average Function and Examples

Spark SQL supports Analytics or window functions. You can use Spark SQL to calculate certain results based on the range of values. Result might be dependent of previous or next row values, in that case you can use cumulative sum or average functions. Databases like Netezza, Teradata, Oracle, even latest version of Apache Hive supports analytic or window functions. In this article, we will check Spark SQL cumulative Average function and how to use it with an example. Spark SQL Cumulative Average Function There are two methods to calculate cumulative…

Comments Off on Spark SQL Cumulative Average Function and Examples
March 16, 2019
Apache Spark

Spark SQL Analytic Functions and Examples

Spark SQL analytic functions sometimes called as Spark SQL windows function compute an aggregate value that is based on groups of rows. These functions optionally partition among rows based on partition column in the windows spec. Like other analytic functions such as Hive Analytics functions, Netezza analytics functions and Teradata Analytics functions, Spark SQL analytic functions works on groups of rows. These functions optionally ignore NULL values in the data. Spark SQL Analytic Functions There are two types of Spark SQL windows functions: Ranking functions and Analytic functions Related Articles:…

Comments Off on Spark SQL Analytic Functions and Examples
March 15, 2019
Apache Spark

Spark SQL Performance Tuning – Improve Spark SQL Performance

You can improve the performance of Spark SQL by making simple changes to the system parameters. It requires Spark knowledge and the type of file system that are used to tune your Spark SQL performance. In this article, we will check the Spark SQL performance tuning to improve Spark SQL performance. Related Article: Apache Spark SQL Introduction and Features Apache Spark Architecture, Design and Overview Data Storage Consideration for Spark Performance Before going into Spark SQL performance tuning, let us check some of data storage considerations for spark performance. Optimize…

Comments Off on Spark SQL Performance Tuning – Improve Spark SQL Performance
January 21, 2019