Impala - Offset Clause

In general, the rows in the resultset of a select query starts from 0. Using the offset clause, we can decide from where the output should be considered. For example, if we choose the offset as 0, the result will be as usual and if we choose the offset as 5, the result starts from the fifth row.

Syntax

Following is the syntax of the offsetclause in Impala.

select data from table_name Group BY col_name;

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 | 
+----+----------+-----+-----------+--------+ 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 8  | ram      | 22  | vizag     | 31000  | 
| 9  | robert   | 23  | banglore  | 28000  |
| 7  | ram      | 25  | chennai   | 23000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.51s

You can arrange the records in the table in the ascending order of their id’s and pmit the number of records to 4, using pmit and order by clauses as shown below.

Query: select * from customers order by id pmit 4 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
+----+----------+-----+-----------+--------+ 
Fetched 4 row(s) in 0.64s

Following is an example of the offset clause. Here, we are getting the records in the customers table in the order of their id’s and printing the first four rows starting from the 0^th row.

[quickstart.cloudera:21000] > select * from customers order by id pmit 4 offset 0;

On executing, the above query gives the following result.

Query: select * from customers order by id pmit 4 offset 0 
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
+----+----------+-----+-----------+--------+ 
Fetched 4 row(s) in 0.62s

In the same way, you can get four records from the customers table starting from the row having offset 5 as shown below.

[quickstart.cloudera:21000] > select * from customers order by id pmit 4 offset 5; 
Query: select * from customers order by id pmit 4 offset 5 
+----+--------+-----+----------+--------+ 
| id | name   | age | address  | salary | 
+----+--------+-----+----------+--------+ 
| 6  | Komal  | 22  | MP       | 32000  | 
| 7  | ram    | 25  | chennai  | 23000  | 
| 8  | ram    | 22  | vizag    | 31000  |
| 9  | robert | 23  | banglore | 28000  | 
+----+--------+-----+----------+--------+ 
Fetched 4 row(s) in 0.52s