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

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…

Continue ReadingVertica WITH Clause, Syntax, Usage and Examples
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…

Continue ReadingSpark SQL Cumulative Average Function and Examples
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:…

Continue ReadingSpark SQL Analytic Functions and Examples
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…

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

Steps to Optimize SQL Query Performance – Best Practices

We pay lots of attention to improve the performance of the web application, but ignore back-end SQL performance tuning. Even experts like application architects and the developer does not have an idea on how databases process the SQL queries internally. This could be because of lack of SQL and database knowledge. In this post, we will check best practices to optimize SQL query performance. How to Select SQL Query for Optimization? Identifying the query to optimize is a crucial step. Even today's most advance SQL engines require optimization. Simple SQL query tweak may increase the…

Continue ReadingSteps to Optimize SQL Query Performance – Best Practices
Comments Off on Steps to Optimize SQL Query Performance – Best Practices
January 9, 2019
Apache Spark

Spark SQL EXPLAIN Operator and Examples

Spark SQL uses Catalyst optimizer to create optimal execution plan. Execution plan will change based on scan, join operations, join order, type of joins, sub-queries and aggregate operations. In this article, we will check Spark SQL EXPLAIN Operator and some working examples. Spark SQL EXPLAIN Operator Spark SQL EXPLAIN operator provide detailed plan information about sql statement without actually running it. You can use the Spark SQL  EXPLAIN operator to display the actual execution plan that Spark execution engine will generates and uses while executing any query. You can use this execution plan…

Continue ReadingSpark SQL EXPLAIN Operator and Examples
Comments Off on Spark SQL EXPLAIN Operator and Examples
December 11, 2018
General

Quick and best way to Compare Two Tables in SQL

Say you have requirement to compare two tables. You have two tables in same database or server that you wish to compare, and check if any changes in the column values or see if any row is missing in either of tables. Below are some of the methods you can use to compare two tables in SQL. Compare Two Tables using UNION ALL UNION allows you to compare data from two similar tables or data sets. It also handles the NULL values to other NULL values which JOIN or WHERE…

Continue ReadingQuick and best way to Compare Two Tables in SQL
Comments Off on Quick and best way to Compare Two Tables in SQL
August 29, 2016