Usage of Collect_List function in Apache Hive

Collect_List is one of the aggregate functions that help in return a set of objects with duplicate elements. This is similar to COLLECT_SET and will help in bringing the column values into a single row. All values will be displayed in an array format.

The datasets on which we are about to practice the aggregate function “Collect_List” are shown below.

Dataset1:
Table: tbSalesData

Dataset2:
Table: StudentMarks

Let’s begin the exercises:

SELECT COLLECT_LIST(city) from tbSalesData;

SELECT COLLECT_LIST(id), COLLECT_LIST(city) from tbSalesData;



It can be used along with other aggregate functions such as SUM, AVG, MAX, MIN, etc.

SELECT
           StudentName,
           COLLECT_LIST(Subject1),
           COLLECT_LIST(Subject2),
           COLLECT_LIST(Subject3),
           SUM(Subject1)+SUM(Subject2)+SUM(subject3) FROM StudentMarks
GROUP BY StudentName;



SELECT
           COLLECT_LIST(Subject1),
           SUM(Subject1),
COLLECT_LIST(Subject2),
           SUM(Subject2),
COLLECT_LIST(Subject3),
SUM(Subject3),
FROM StudentMarks

Hope you liked this post.

Please do follow for more interesting updates.