CodeBlanks Logo Icon CodeBlanks
Login Register

SQL on Hadoop

Run SQL over big data.

SQL on Hadoop

SQL on Hadoop reaches big data on HDFS with SQL. Hive compiles SQL into MapReduce; Presto runs an in-memory distributed query instead.

SELECT customer_id, COUNT(*)
FROM hive.orders
GROUP BY customer_id;

Hive turns SQL into MapReduce.

customer_id, COUNT(*) is selected from hive.orders. Rows are grouped by customer_id; aggregate functions are computed per group.

SQL on Hadoop — details

Hive is slow but handles very large data; Presto is fast but bound by memory. They pair up: Hive for ETL, Presto for interactive analysis.

Practice SQL on Hadoop

Select 10 rows from the hive.orders table: SELECT * FROM hive.orders LIMIT 10.

Starter code

query.sql
-- Hadoop query
SELECT * FROM

Sample solution

query.sql
SELECT * FROM hive.orders LIMIT 10;

Expected output

10 rows

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 What does Hive turn SQL into?

  • JSON
  • XML
  • MapReduce Correct answer
  • HTML

Explanation: Hive turns SQL into MapReduce.

2 Hive catalog

Question 
SELECT * FROM ____.orders LIMIT 10
Answer 
SELECT * FROM hive.orders LIMIT 10

Explanation: hive.orders refers to the orders table in the Hive catalog (catalog.table).

3 SQL on Hadoop: match the terms

Term Meaning
Hive compiler
Presto in-memory
HDFS file system
MapReduce parallel

All lessons in Expert

1 Database design

  1. ER model
  2. Cardinality
  3. Surrogate keys
  4. Natural keys
  5. Denormalization
  6. Summary - Design

2 Index tuning

  1. B-tree structure
  2. Covering index
  3. Partial index
  4. Index selectivity
  5. Index maintenance
  6. Summary - Index tuning

3 Query optimizer

  1. What is an optimizer?
  2. Statistics
  3. Cost model
  4. Join strategies
  5. Plan cache
  6. Summary - Optimizer

4 Project: Performance tuning

  1. Scenario design
  2. Reading plans
  3. Adding indexes
  4. Rewriting queries
  5. Comparing results

5 Replication and sharding

  1. What is replication?
  2. Master-slave
  3. Sync vs async
  4. What is sharding?
  5. Shard key
  6. Summary - Replication and sharding

6 Distributed and big data

  1. Distributed queries
  2. MPP
  3. Columnar storage
  4. SQL on Hadoop
  5. SQL vs NoSQL
  6. Summary - Distributed and big data

7 Security and backup

  1. SQL injection
  2. Parameterised queries
  3. Authorisation
  4. Backup
  5. Recovery
  6. Summary - Security and backup

8 Expert final

  1. Design challenge
  2. Optimization challenge
  3. Security challenge
  4. Backup challenge