At the end of this video the student will know how to extend the GROUPING SETS clause with ROLLUP and CUBE options.
- [Instructor] Now with Hive, there's some additional ways…to perform these aggregations, using CUBE an ROLLUP.…These are almost like shortcuts,…and the first one, CUBE,…returns all possible aggregation combinations,…and the second one, ROLLUP, does it,…but in a more hierarchical fashion.…Let's take a look, and we're going to replace grouping sets…with CUBE, and then similarly,…replace grouping sets with ROLLUP.…Okay, so back here in my Cloud Air environment,…I'm going to open up 4-3.sql.…
And I'm going to copy everything out of here,…and go back over to my hue editor.…And on top let's take a look, with using CUBE.…So it essentially returns subtotals of all…possible combinations.…That can be dangerous if you have a large data set,…so be very careful about it.…Now if you're not sure which aggregation level you want,…you can use with CUBE at the end of your query,…of an aggregate query that has a group by,…and it will automatically return all of them,…but be careful because it is going to increase the delay,…it's going to increase some of the work that has to be done…
This course shows how to use Hive to process data. Instructor Ben Sullins starts by showing you how to structure and optimize your data. Next, he explains how to get Hue, the Hadoop user interface, to leverage HiveQL when analyzing data. Using the newly configured option, he then demonstrates how to load data, create aggregate tables for fast query access, and run advanced analytics. He also takes you through managing tables and putting functions to use. This course is designed to help you find new ways to work with datasets so you can answer the tough data science questions that come your way.
- Defining data structures in Hive
- Selecting data
- Joining tables
- Manipulating data
- Filtering results
- Aggregating data
- Using built-in aggregate functions
- Mastering built-in table-generating functions
- Using CUBE and ROLLUP
- Using clauses: WHERE and HAVING
- Using LIKE, JOIN, and SEMI JOIN
- Using functions: String, math, date, and conditional