Big Data Hadoop and Spark Developer
Lesson 4—Basics of Hive and Impala
© Simplilearn. All rights reserved.
Learning Objectives
Identify the features of Hive and Impala
Understand the methods to interact with Hive and Impala
Basics of Hive and Impala
Topic 1: Features of Hive and Impala
Introduction to Hive and Impala
Batch Interactive
Processing SQL
SELECT t1.a1 as c1, t2.b1 as c2
FROM t1 JOIN t2 ON (t1.a2=t2.b2);
• Hive and Impala provide an SQL-like interface
for users to extract data from the Hadoop
system.
Resource Management • They reside on top of Hadoop and can be used
to query data from the underlying storage
Storage components.
HDFS HBase
Hive and Impala: Similarities
• Hive is very similar to Impala in the following ways:
Hive and Impala: Differences
Hive was developed by Facebook around 2007. Impala was developed by Cloudera around
2012.
It is an Open source Apache project. It is an incubating Apache project.
It has a high level abstraction layer on top of It has a high performance dedicated SQL
MapReduce and Apache Spark.
engine.
It uses HiveQL to query the structured data in a
metastore. It uses Impala SQL for ad hoc queries.
It is suitable for structured data. It is designed for high concurrency and ad
z
hoc queries.
Hive and Impala: Comparison
Hive Impala
• Comprises a specialized SQL
• Provides more features than engine that offers five to fifty
Impala times faster performance
than Hive
• Is highly extensible
• Used mainly for interactive
queries and data analysis
• Used mostly for batch
processing • Accommodates many
concurrent users
Relational Databases vs. Hive vs. Impala
Use Case: Hive and Impala
Hive and Impala are commonly used to analyze social media coverage.
Basics of Hive and Impala
Topic 2: Interacting with Hive and Impala
Executing a Query in Hive and Impala
Receive SQL query Receive SQL query
Parse Hive QL 1 Parse Impala SQL
Make optimizations 2 Make optimizations
Plan execution 3 Plan execution
Submit job(s) to cluster 4 Execute query on cluster
Monitor progress 5 Store the data in HDFS
Process data—
6
MapReduce or Apache Spark
Store the data in HDFS 7
Hive Query Editor
Interfaces to Run Hive and Impala Queries
Hive and Impala offer numerous interfaces to run queries:
• Command-line shell:
– Impala: Impala shell
– Hive: Beeline
Impala Query Editor
• Hue Web UI:
– Hive Query Editor
– Impala Query Editor
• Metastore Manager:
– ODBC/JDBC
Impala Lab Access Details
• The steps to start Impala in lab are as follows:
Step 1 Step 2
• Log in to cloud lab • Connect to any
web console with daemon server with
your credentials the help of the
command below:
•impala-shell -i
cloudera-
slavenode3.cloudlab.
com
Demo
Starting Impala Lab
Demonstrate the method to start and connect to the Impala lab from command.
Impala Lab Access Details
Connecting with Hive and Impala Shell
• To execute Impala commands from Impala shell:
• To run Hive using Beeline:
Running Impala Queries from Command Line
To check all options of Impala using the help option: Impala-shell –help
Impala-shell –q ‘select *
To run direct queries from shell using the –q option: from simple’
Impala-shell –d
To issue a use database on startup using the –d option: Simplilearn
Demo
Connecting with Hive and Impala Shell
Demonstrate the method to connect with Hive and Impala shell, along with some basic
operations.
Sample Queries
SELECT version();
To explore a new Impala instance:
SELECT current_database();
CREATE DATABASE IF NOT EXISTS
To create a database: sample;
To verify a database: SHOW databases;
To specify the location where the database is CREATE DATABASE IF NOT EXISTS
to be created: database_name LOCATION hdfs_path;
Sample Queries
To switch the current session to another
USE db_name;
database:
CREATE TABLE stockprice
(stock_id INT,
date STRING,
open_price FLOAT,
high_price FLOAT,
low_price FLOAT,
close_price FLOAT,
To create a table in Parquet format: stock_volume INT,
adjclose_price FLOAT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION
'/home/singh25nov_gmail/input'
stored as parquet ;
Sample Queries
CREATE EXTERNAL TABLE stop_loss
(
stock_id INT,
stock_volume FLOAT,
stock_current_rate DOUBLE,
To load csv data from local files: stock_trigger_price DOUBLE
)
ROW FORMAT DELIMITED FIELDS
TERMINATED BY ','
LOCATION
'/user/cloudera/sample_data/tab1';
To list all tables in the current database in SHOW tables;
Impala:
Sample Queries
INSERT INTO stockprice
(date,open_price,high_price,low_price,c
To insert a single row: lose_price,stock_volume,adjclose_price
) VALUES ('15112017',102
,105,98.6,100154711,100);
impala-shell -i <impala-daemon-uri> -f
To migrate from SQL:
<filename>.sql;
Sample Queries
SELECT stockprice.open_price,
MAX(stockprice.stock_volume),
MIN(stop_loss.stock_current_rate)
To aggregate and join: FROM stop_loss JOIN stockprice USING
(stock_id)
GROUP BY high_price ORDER BY 1
LIMIT 5;
DROP (DATABASE|SCHEMA) [IF EXISTS]
To drop a database: database_name [RESTRICT | CASCADE]
[LOCATION hdfs_path];
Sample Queries
• Interactive mode:
SELECT count(*) FROM stockprice;
To query the Impala table: • Set of commands contained in a file:
impala-shell-i impala-host -f <filename>.sql;
• Single command to the impala-shell:
impala-shell-i impala-host-q 'select count(*) from stockprice‘;
Executing Queries in the Impala Shell
localhost.localdomain:21000] > select * from webpage where page_id > 40
> LIMIT 5;
Query: select * from webpage where page_id > 40
LIMIT 5
+---------+--------------------------+--------------------------------------+
| page_id | name | assoc_files |
+---------+--------------------------+--------------------------------------+
| 41 | sorrento_f31l_sales.html | theme2.css,code.js,sorrento_f31l.jpg |
| 42 | sorrento_f40l_sales.html | theme1.css,code.js,sorrento_f40l.jpg |
| 43 | sorrento_f01l_sales.html | theme1.css,code.js,sorrento_f01l.jpg |
| 44 | sorrento_f11l_sales.html | theme1.css,code.js,sorrento_f11l.jpg |
| 45 | titanic_2400_sales.html | theme1.css,code.js,titanic_2400.jpg |
+---------+--------------------------+--------------------------------------+
Fetched 5 row(s) in 1.32s
Demo
Impala Queries
Demonstrate the sample Impala queries.
Running Hive Queries Using Beeline
• The character “!” is used to execute Beeline
commands.
The commands used to run Beeline:
• !exit: Used to exit the shell
• !help: Shows list of all commands
• !verbose: Shows added details of queries
Demo
Running Hive Queries Using Beeline
Demonstrate the method to connect with Beeline and execute basic queries.
Running Beeline from Command Line
beeline –u … -f
To execute file using the –u option: simplilearn.hql
To use HiveQL directly from the command line using the -e beeline –u ... -e 'SELECT *
option: FROM users‘
To continue running script even after an error: beeline –u … -force=TRUE
Running Hive Query
Hive> select * from device
> LIMIT 5;
OK • All SQL commands are terminated
1 2008-10-21 00:00:00 Sorrento F00L phone with a semicolon “;”
2 2010-04-19 00:00:00 Titanic 2100 phone
3 2011-02-18 00:00:00 MeeToo 3.0 phone
4 2011-09-21 00:00:00 MeeToo 3.1 phone
5 2008-10-21 00:00:00 iFruit 1 phone
Time taken: 0.296 seconds, Fetched: 5 row(s)
Connecting Hive and Impala Shell with Hue
• Hue can be used to write Hive and Impala
queries from the User Interface.
Demo
Connecting Hive and Impala Shell with Hue
Demonstrate the method to connect Hive and Impala shell using Hue.
Hive and Impala Editors in Hue
Diagram 1 Diagram 2
Key Takeaways
Hive and Impala are tools to perform SQL queries on data residing on HDFS
or HBase.
Hive and Impala are easy to learn for experienced SQL developers.
Hive and Impala solve the Big Data problem but cannot replace a traditional
RDBMS.
Hive runs MapReduce or Spark jobs on Hadoop based on HiveQL statements.
Impala uses a very fast specialized SQL engine that is faster than MapReduce.
Quiz
QUIZ
Which of the following components can be used to accept command inputs from users?
1
a. Command Line Interface
b. Query compiler
c. Execution engine
d. Thrift server
QUIZ
Which of the following components can be used to accept command inputs from users?
1
a. Command Line Interface
b. Query compiler
c. Execution engine
d. Thrift server
The correct answer is a.
The Command Line Interface is used as an input medium to accept command input from users.
QUIZ
Hive can be accessed from Hue using ________.
2
a. Impala editor
b. Hive Editor
c. File browser
d. YARN UI
QUIZ
Hive can be accessed from Hue using ________.
2
a. Impala editor
b. Hive Editor
c. File browser
d. YARN UI
The correct answer is b.
Hive can be accessed through the Hive editor in Hue.
QUIZ
Impala can be accessed from Hue using ________.
3
a. Impala editor
b. Hive Editor
c. File browser
d. YARN UI
QUIZ
Impala can be accessed from Hue using ________.
3
a. Impala editor
b. Hive Editor
c. File browser
d. YARN UI
The correct answer is a.
Impala can be accessed through the Impala editor in Hue.
QUIZ
Updating an individual record is possible in______.
4
a. Impala
b. Hive
c. RDBMS
d. All of the above
QUIZ
Updating an individual record is possible in______.
4
a. Impala
b. Hive
c. RDBMS
d. All of the above
The correct answer is c.
Hive and Impala cannot update individual records, but an RDBMS can.
QUIZ
Deleting an individual record is possible in_______.
5
a. RDBMS
b. Hive
c. Impala
d. All of the above
QUIZ
Deleting an individual record is possible in_______.
5
a. RDBMS
b. Hive
c. Impala
d. All of the above
The correct answer is a.
Hive and Impala cannot delete individual records, but an RDBMS can.
This concludes “Basics of Hive and Impala.”
The next lesson is “Working with Hive and Impala.”
©Simplilearn. All rights reserved
