Impala Tutorial
Database Specific Statements
Table Specific Statements
Impala - Clauses
Impala Useful Resources
Selected Reading
- Impala - Query Language Basics
- Impala - Shell
- Impala - Architecture
- Impala - Environment
- Impala - Overview
- Impala - Home
Database Specific Statements
Table Specific Statements
- Impala - Drop a View
- Impala - Alter View
- Impala - Create View
- Impala - Show Tables
- Impala - Truncate a Table
- Impala - Drop a Table
- Impala - Alter Table
- Impala - Describe Statement
- Impala - Select Statement
- Impala - Insert Statement
- Impala - Create Table Statement
Impala - Clauses
- Impala - Distinct Operator
- Impala - With Clause
- Impala - Union Clause
- Impala - Offset Clause
- Impala - Limit Clause
- Impala - Having Clause
- Impala - Group By Clause
- Impala - Order By Clause
Impala Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Impala - With Clause
Impala - With Clause
In case a query is way too complex, we can define apases to complex parts and include them in the query using the with clause of Impala.
Syntax
Following is the syntax of the with clause in Impala.
with x as (select 1), y as (select 2) (select * from x union y);
Example
Assume we have a table named customers in the database my_db and its contents are as follows −
[quickstart.cloudera:21000] > select * from customers; Query: select * from customers +----+----------+-----+-----------+--------+ | id | name | age | address | salary | +----+----------+-----+-----------+--------+ | 1 | Ramesh | 32 | Ahmedabad | 20000 | | 9 | robert | 23 | banglore | 28000 | | 2 | Khilan | 25 | Delhi | 15000 | | 4 | Chaitap | 25 | Mumbai | 35000 | | 7 | ram | 25 | chennai | 23000 | | 6 | Komal | 22 | MP | 32000 | | 8 | ram | 22 | vizag | 31000 | | 5 | Hardik | 27 | Bhopal | 40000 | | 3 | kaushik | 23 | Kota | 30000 | +----+----------+-----+-----------+--------+ Fetched 9 row(s) in 0.59s
In the same way, suppose we have another table named employee and its contents are as follows −
[quickstart.cloudera:21000] > select * from employee; Query: select * from employee +----+---------+-----+---------+--------+ | id | name | age | address | salary | +----+---------+-----+---------+--------+ | 3 | mahesh | 54 | Chennai | 55000 | | 2 | ramesh | 44 | Chennai | 50000 | | 4 | Rupesh | 64 | Delhi | 60000 | | 1 | subhash | 34 | Delhi | 40000 | +----+---------+-----+---------+--------+ Fetched 4 row(s) in 0.59s
Following is an example of the with clause in Impala. In this example, we are displaying the records from both employee and customers whose age is greater than 25 using with clause.
[quickstart.cloudera:21000] > with t1 as (select * from customers where age>25), t2 as (select * from employee where age>25) (select * from t1 union select * from t2);
On executing, the above query gives the following output.
Query: with t1 as (select * from customers where age>25), t2 as (select * from employee where age>25) (select * from t1 union select * from t2) +----+---------+-----+-----------+--------+ | id | name | age | address | salary | +----+---------+-----+-----------+--------+ | 3 | mahesh | 54 | Chennai | 55000 | | 1 | subhash | 34 | Delhi | 40000 | | 2 | ramesh | 44 | Chennai | 50000 | | 5 | Hardik | 27 | Bhopal | 40000 | | 4 | Rupesh | 64 | Delhi | 60000 | | 1 | Ramesh | 32 | Ahmedabad | 20000 | +----+---------+-----+-----------+--------+ Fetched 6 row(s) in 1.73sAdvertisements