9.2、创建数据库
impala-shell进入到impala的交互窗口
9.1.2查看所有数据库
show databases;
9.1.3创建与删除数据库
创建数据库
CREATE DATABASE IF NOT EXISTS mydb1;
drop database if exists mydb;
创建数据库表并指定数据库表数据存放hdfs的位置(与hive建表语法类似)
hdfs dfs -mkdir -p /input/impala
create external table t3(id int ,name string ,age int ) row format delimited fields terminated by '\t' location '/input/impala/external';
9.3、 创建数据库表
创建student表
CREATE TABLE IF NOT EXISTS mydb1.student (name STRING, age INT, contact INT );
创建employ表
create table employee (Id INT, name STRING, age INT,address STRING, salary BIGINT);
9.3.2、 数据的查询
select * from employee;
select name,age from employee;
9.3.3、 删除表
DROP table mydb1.employee;
9.3.4、 清空表数据
truncate employee;
9.3.5、 创建视图
CREATE VIEW IF NOT EXISTS employee_view AS select name, age from employee;
9.3.6、 查看视图数据
select * from employee_view;
9.4、 order by语句
基础语法
select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]
Select * from employee ORDER BY id asc;
9.5、group by 语句
Select name, sum(salary) from employee Group BY name;
9.6、 having 语句
基础语法
select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]
按年龄对表进行分组,并选择每个组的最大工资,并显示大于20000的工资
select max(salary) from employee group by age having max(salary) > 20000;
9.7、 limit语句
select * from employee order by id limit 4;
10、impala当中的数据表导入几种方式
第一种方式,通过load hdfs的数据到impala当中去
create table user(id int ,name string,age int ) row format delimited fields terminated by "\t";
准备数据user.txt并上传到hdfs的 /user/impala路径下去
1 hello 15
2 zhangsan 20
3 lisi 30
4 wangwu 50
加载数据
load data inpath '/user/impala/' into table user;
查询加载的数据
select * from user;
如果查询不不到数据,那么需要刷新一遍数据表
refresh user;