impala学习

1impala原理

2impala类型转换

2、cast函数

　　　　cast(expr AS type), 类型转换函数, 比如将number转成string, 或相反. 

　　　　select cast(length as int) len from dw_bill_his where length != '无' and startdate='2018-09-01' order by cast(length as int);

 

3impala分页查询

[localhost:21000] > create table numbers (x int);
[localhost:21000] > insert into numbers select x from very_long_sequence;
Inserted 1000000 rows in 1.34s
[localhost:21000] > select x from numbers order by x limit 5 offset 0;
+----+
| x  |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
+----+
Returned 5 row(s) in 0.26s
[localhost:21000] > select x from numbers order by x limit 5 offset 5;
+----+
| x  |
+----+
| 6  |
| 7  |
| 8  |
| 9  |
| 10 |
+----+
Returned 5 row(s) in 0.23s

自己踩坑：分页排序的时候没有order by多字段，造成分页查询字段多

4----20190808相互转换2019-08-08

select cast(concat(substr('20190808',1,4),'-',substr('20190808',5,2),'-',substr('20190808',7,2)) as String) from table1

select regexp_replace(substr('2019-08-08',1,10),'-','') from table1 

5大小写转换

--使用lower返回全小写的hello world
[master:21000] > select lower('Hello World') as lower;
+-------------+
| lower       |
+-------------+
| hello world |
+-------------+
--使用lcase返回全小写的hello world
[master:21000] > select lcase('Hello World') as lcase;
+-------------+
| lcase       |
+-------------+
| hello world |

--使用upper返回全小写的hello world
[master:21000] > select upper('hello world') as upper;
+-------------+
| upper       |
+-------------+
| HELLO WORLD |
+-------------+
--使用ucase返回全小写的hello world
[master:21000] > select ucase('hello world') as ucase;
+-------------+
| ucase       |
+-------------+
| HELLO WORLD |
+-------------+