Transpose & Pivot In Hive Query

Apache Hive does not have direct standard UDF for transposing rows into columns. Transpose & Pivot in Hive Query can be achieved using multi-stage process.  You can use collect_list() or collect_set() function and merge the multiple rows into columns and then get the result.

collect_list() and collect_set() are part of  Built-in Aggregate Functions (UDAF).  collect_list(col_name) returns a set of objects … read the rest

Apache Hive Analytical Functions

Apache Hive Analytical Functions available since Hive 0.11.0, are a special group of functions that scan
the multiple input rows to compute each output value. Apache Hive Analytical Functions are usually used with OVER, PARTITION BY, ORDER BY, and the windowing specification. Different from the regular aggregate functions used with the GROUP BY clause that is limited to one … read the rest

Apache Hive Vectorization

Apache Hive Vectorization was introduced newly in Apache Hive to improve query performance. By default, the Apache Hive query execution engine processes one row of a table at a time. The one row of data goes through all the operators in the query before the next row is processed, resulting in very inefficient CPU usage. In vectorized query execution, … read the rest

Apache Hive Cheat Sheet

Apache Hive Cheat Sheet is a summary of all functions and syntax for big data engineers and developers reference. It is divided into 5 parts.

Apache Hive Cheat Sheet – Query Syntax

Apache Hive Cheat Sheet – Metadata

Apache Hive Cheat Sheet – Query Compatibility

Apache Hive Cheat Sheet – Command Line

Apache Hive Cheat Sheet – Shell &  CLI

read the rest

Apache Hive CLI vs Beeline

Apache Hive development has shifted from the original Hive server (HiveServer1) to the new server (HiveServer2), and hence users and developers need to move to the new access tool. However, there’s more to this process than simply switching the executable name from “hive” to “beeline”.  Apache Hive was a heavyweight command-line tool that accepted the command and runs … read the rest

Apache Sqoop Introduction

In this article, Apache Sqoop Introduction, we will primarily discuss why this tool exists. Apache sqoop is part of Hadoop Core project or part of Hadoop Ecosystem project.

Bigdata tools which we use for transferring data between Hadoop and relational database servers is what we call Sqoop. Sqoop primarily stands for Sql for Hadoop.

In addition, there are … read the rest

Beginners Impala Tutorial

The Beginners Impala Tutorial covers key concepts of in-memory computation technology called Impala. It is developed by Cloudera. MapReduce based frameworks like Hive is slow due to excessive I/O operations. Cloudera offers a separate tool and that tool is what we call Apache Impala. This Beginners Impala Tutorial will cover the whole concept of Cloudera Impala and how this Massive … read the rest