Impala GROUP BY Clause – Zero-Length Strings

It is possible to arrange identical data into groups in Impala. For that, we use Impala Group BY clause. So, in this article, we will discuss, how we can arrange data with Impala Group BY clause. Also, we will cover its syntax, usage as well as an example to understand it well.

So, let’s start Impala Group by Clause.

Impala GROUP BY Clause

Basically, Impala GROUP BY Clause specifies the GROUP BY clause in queries that use aggregation functions. Like COUNT(), SUM(), AVG(), MIN(), and MAX(). Moreover, specify the names of all the columns that do not participate in the aggregation operation in the GROUP BY clause.

In other words, to arrange identical data into groups, we use the Impala GROUP BY clause in collaboration with SELECT statement.

Syntax of Group By Clause in Impala

Here, is the syntax of Impala GROUP BY clause, below;

select data from table_name Group BY col_name;

Complex Type Considerations

There are several complex data types available in CDH 5.5 / Impala 2.3 and higher. Like STRUCT, ARRAY, and MAP.

Basically, we cannot reference these columns directly in the ORDER BY clause. So, we use join notation to “unpack” the elements of the complex type when we query a complex type column.

Moreover, we can include an ORDER BY clause to control the order in the result set of the scalar elements from the complex type within the join query.

Zero-Length Strings in Group By

Impala considers zero-length strings (“”), NULL, and space to all the different values for purposes of several clauses. Like DISTINCT and GROUP BY.

Examples of Impala Group By Clause

Let’s Understand by Impala Group By example,

Let us suppose we have a table named Employees in the database my_db. Its contents are −

[quickstart.cloudera:21000] > select * from Employees;
Query: select * from Employees

id	name	age	address	salary
1	shubham	32	delhi	20000
2	monika	25	mumbai	15000
3	kajal	27	alirajpur	40000
4	revti	25	indore	35000
5	shreyash	23	pune	30000
6	mehul	22	hyderabad	32000

Fetched 6 row(s) in 0.51s
Further, using GROUP BY query, we can get the total amount of salary of each Employee.

[quickstart.cloudera:21000] > Select name, sum(salary) from Employees Group BY name;

On executing, the above query we get the following output.
Query: select name, sum(salary) from Employees Group BY name

name	sum(salary)
shubham	20000
mehul	32000
Shreyash	30000
monika	15000
revti	35000
kajal	40000

Fetched 6 row(s) in 1.75s
However, now suppose that this table has multiple records.

id	name	age	address	salary
1	shubham	32	delhi	20000
2	shubham	32	delhi	2000
3	monika	25	mumbai	15000
4	kajal	27	alirajpur	40000
5	revti	25	indore	35000
6	revti	25	indore	3000
7	shreyash	23	pune	30000
8	mehul	22	hyderabad	32000

So, using the Group By clause again, we can get the total amount of salaries of the employees, considering the repeated entries of records.

Select name, sum(salary) from Employees Group BY name;

So, above query gives the following output while execution.

Query: select name, sum(salary) from Employees Group BY name 

name	sum(salary)
shubham	22000
mehul	32000
Shreyash	30000
monika	15000
revti	38000
kajal	40000

Fetched 6 row(s) in 1.75s

So, this was all in the Impala Group By Clause tutorial. Hope you like our explanation.

Conclusion

As a result, we have seen the whole concept of Impala GROUP BY Clause. Still, if any doubt occurs in how to arrange data in groups, feel free to ask in the comment section.