Impala Walkthrough with an Example - Learning Cloudera Impala - page 105

Using various types of SQL statements
In the automobiles table, I have included a field named city_hwy_mpg . It in-
cludes miles per gallon in the city and highway, separated by a dash ( - ). Here, I want
to show you how to use the STRING manipulation to get both highway and city miles
per gallon values along with the LIMIT and WHERE clauses:
[Hadooptestdomain:21000] > select city_hwy_mpg,
substr(city_hwy_mpg,1,2), substr(city_hwy_mpg,
instr(city_hwy_mpg, "-")+1 , 5) from
automobiles WHERE price > 15000 LIMIT 2;
Query finished, fetching results ...
+--------------+----------------------------+-------------------------------------------------------+
| city_hwy_mpg | substr(city_hwy_mpg, 1, 2) |
substr(city_hwy_mpg, instr(city_hwy_mpg, '-') +
1, 5) |
+--------------+----------------------------+-------------------------------------------------------+
| 22-30 | 22 |
30
|
| 24-32 | 24 |
32
|
+--------------+----------------------------+-------------------------------------------------------+
Returned 2 row(s) in 0.35s
In the preceding code snippet, the substr SQL command is used to select the first
two letters that represent the city_mpg value. After this, instr is used to find the
location of - and then the same substr is used to get the hwy_mpg part of the
value.
Now, let's use the COUNT and DISTINCT clause to collect unique automakers as fol-
lows:
[Hadoop.testdomain:21000] > select
count(distinct(make)) from automobiles;