- Impala Tutorial
- Impala - Home
- Impala - Overview
- Impala - Environment
- Impala - Architecture
- Impala - Shell
- Impala - Query Language Basics
- Database Specific Statements
- Impala - Create a Database
- Impala - Drop a Database
- Impala - Select a Database
- Table Specific Statements
- Impala - Create Table Statement
- Impala - Insert Statement
- Impala - Select Statement
- Impala - Describe Statement
- Impala - Alter Table
- Impala - Drop a Table
- Impala - Truncate a Table
- Impala - Show Tables
- Impala - Create View
- Impala - Alter View
- Impala - Drop a View
- Impala - Clauses
- Impala - Order By Clause
- Impala - Group By Clause
- Impala - Having Clause
- Impala - Limit Clause
- Impala - Offset Clause
- Impala - Union Clause
- Impala - With Clause
- Impala - Distinct Operator
- Impala Useful Resources
- Impala - Quick Guide
- Impala - Useful Resources
- Impala - Discussion
Impala - With Clause
In case a query is way too complex, we can define aliases 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 | Chaitali | 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.73s
Advertisements
To Continue Learning Please Login
Login with Google