- 进入impala shell
impala-shell -i localhost --quiet - 查看impala版本
[localhost:21000] > select version(); - 查看数据库
[localhost:21000] > show databases; - 查看当前的数据库
[localhost:21000] > select current_database(); - 列出当前数据库的表
[localhost:21000] > show tables; - 列出某个数据库的表
[localhost:21000] > show tables in dwt - 模糊查询某个数据库的表
[localhost:21000] > show tables in dwt like 'vcc*'; - 进入某个数据库
[localhost:21000] > use dwt; - 显示一张表的结构
[localhost:21000] > describe vcc_b_dept;
或者
[localhost:21000] > desc vcc_b_dept; - 显示一张表的行数
[localhost:21000] > select count(*) from vcc_b_dept; - 显示某一列不同值的个数
[localhost:21000] > select count(distinct dept_no) from vcc_b_dept; - 显示dept_no is null的行数
[localhost:21000]>select count(*) from vcc_b_dept where dept_no is null; - 显示某一列10个不同的值
[localhost:21000] > select distinct dept_no from vcc_b_dept limit 10; - 创建数据库
[localhost:21000] > create database test_impala; - 显示数据库
[localhost:21000] > show databases; - 模糊查询数据库
[localhost:21000] > show databases like 'test*'; - 创建一张表
[bigdata1:21000] > create table t1 (x int); - 将表移动到另一个数据库中
[bigdata1:21000] > alter table t1 rename to test_impala1.t1; - 添加数据到表里
[bigdata1:21000] > insert into t1 values(1), (2), (3), (4); - 执行函数
[bigdata1:21000] > select min(x), max(x), sum(x), avg(x) from t1; - join表
[bigdata1:21000] > select word from t1 join t2 on t1.x = t2.id - 删除表
[bigdata1:21000] > drop table if exists tab1; - 根据HDFS创建外表
create external table tab1(id int, col_1 boolean,col_2 double,col_3 timestamp) row format delimited fields terminated by ',' location '/user/impala/sample_data/tab1'; - 创建表
create table tab3(id int,col_1 boolean,col_2 double,month int,day int)row format delimited fields terminated by ','; - 执行sql脚本
impala-shell -i localhost -f customer_setup.sql - 执行一条命令
impala-shell -i impala-host -q 'select count(*) from customer_address' - 汇总查询
SELECT tab2.col_1, MAX(tab2.col_2), MIN(tab2.col_2) FROM tab2 JOIN tab1 USING (id) GROUP BY col_1 ORDER BY 1 LIMIT 5; - 子查询
select tab2.* from tab2, (select tab1.col_1, max(tab2.col_2) as max_col2 from tab2, tab1 where tab2.id = tab1.id group by col_1) subquery1 where subquery1.max_col2 = tab2.col_2; - insert查询
insert overwrite table tab3 select id, col_1, col_2, month(col_3), dayofmonth(col_3) from tab1 where year(col_3) = 2012; - 创建分区表
create table logs (field1 string, field2 string, field3 string) partitioned by (year string, month string, day string, host string); - 加载数据到分区表
insert into logs partition(year="2018", month="08", day="28", host="host1") values("foo1", "foo2", "foo3"); - 创建外表
create external table external_logs (field1 string, field2 string, field3 string) partitioned by(year string, month string, day string, host string) location '/user/hive/warehouse/test_impala1.db/logs'; - 添加分区
alter table external_logs add partition(year="2018", month="08", day="28", host="host1");
网友评论