SQL over big data with Hive
You query files in HDFS with SQL, partition them and turn them into report tables.
Level 201 · 18 lessons · 15 practice labs
Lessons
Getting started
- What Hive is and your first query: You see that Hive runs SQL over files and write your first query with Beeline.
- Creating databases and tables: You define Hive databases and tables and choose data types.
- External tables over CSV files: You attach existing CSV files to a table without moving them and know external versus managed tables.
- Quick task: build a web log table
Querying
- Filtering and handling missing values: You filter rows with WHERE and drop or fill NULL values.
- Aggregating and summarising with GROUP BY: You count and summarise events by source, day and type.
- Combining tables with JOIN: You match an event table with an asset table and examine unmatched records separately.
- Ranking with window functions
- Quick task: failed logins per user
Storage and layout
- Partitioning by date: You split a table into days so queries read only the partitions they need.
- Safely rewriting a single day: You replace only one day’s partition with INSERT OVERWRITE and leave other days untouched.
- Saving results as ORC and Parquet: You write query results to a new table in columnar formats.
- Quick task: a partitioned archive table
Access and operations
- Views and column masking: You build views that hide sensitive columns and limit access to personal data.
- Hive authorization: You decide who can read which table using roles and GRANT.
- Diagnosing common Hive data problems: You find and fix problems such as shifted columns, broken dates and empty results.
- Scripted queries and parameters with Beeline
- Capstone: a daily security event report: You produce a summary report table from partitioned event data that refreshes daily.
Practice labs
- An external table over a CSV: Define an external table with the right column types over badge-reader logs in HDFS and check its row count.
- Header rows and quoted fields: Redefine a table that reads a file with a header row and commas inside quotes wrongly, using the right SerDe and table properties.
- Before dropping a managed table: Convert a table created as managed by mistake into an external one without losing data and prove the files survive a DROP.
- Errors per endpoint: Use HiveQL with conditional counts to produce 4xx and 5xx counts per endpoint from a web table in one query.
- Link users to departments with a JOIN: Join the VPN session table with the staff table and show night sessions per department along with the users that did not match.
- Consecutive failures with a window function: Use LAG and ranking functions to find cases where the same user failed five times in a row within five minutes.
- Build a date-partitioned table: Create the event table partitioned by day, load three days of data and verify with SHOW PARTITIONS.
- Safely rewrite a single day: Fix a wrongly loaded day with INSERT OVERWRITE on that partition only and show the other days' row counts are unchanged.
- Dynamic partitioning and moving to ORC: Move a large unpartitioned text table into an ORC table with dynamic partitioning and report the difference in query time and size.
- A view that hides personal fields: Create a view on the customer table that leaves out the national ID and phone columns and grant the analyst role access to that view only.
- Partial display with column masking: Write a view that masks e-mail and card numbers so only the last four characters show and verify the result.
- Find and revoke excess privileges: Find roles with more privileges than they need in Hive's grant tables and write a GRANT/REVOKE script that applies least privilege.
- A parameterised Beeline query: Export a day's event count as CSV with a Beeline command that takes the date as a variable.
- Why a column is full of NULLs: Work out why a column returns nothing but NULLs, tracing it to a delimiter and type mismatch, and fix the table.
- Repair missing partitions: Add days that were copied into HDFS by hand but do not show in queries with MSCK REPAIR and verify no day is missing.
About this course
You query files in HDFS with SQL, partition them and turn them into report tables.
What you will learn
- Getting started
- Querying
- Storage and layout
- Access and operations
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 201; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.