impala学习笔记

impala学习笔记
-- 建库
CREATE DATABASE IF NOT EXISTS database_name;
-- 在HDFS文件系统中创建数据库，需要指定要创建数据库的位置。
CREATE DATABASE IF NOT EXISTS database_name LOCATION hdfs_path;
-- 删库
DROP DATABASE IF EXISTS sample_database;
-- 删除数据库并删除表
DROP database sample cascade; 
-- 建表
CREATE TABLE IF NOT EXISTS my_db.student(name STRING, age INT, contact INT );
-- 插入数据
insert into employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ‘Ramesh‘, 32, ‘Ahmedabad‘, 20000 );
insert into employee values (2, ‘Khilan‘, 25, ‘Delhi‘, 15000 );
-- 插入新数据并覆盖原有数据
Insert overwrite table_name values (value1, value2, value2);
-- 显示表结构
describe customer;
-- 改表名
ALTER TABLE my_db.customers RENAME TO my_db.users;
-- 添加列
ALTER TABLE users ADD COLUMNS (account_no BIGINT, phone_no BIGINT);
-- 删除列
ALTER TABLE users DROP account_no;
-- 更改列名或列类型
ALTER TABLE users CHANGE phone_no e_mail string;
-- 删表
drop table if exists my_db.student;
-- 删除表记录保留表结构
truncate customers;
-- 创建视图
CREATE VIEW IF NOT EXISTS customers_view AS select name, age from customers;
-- 更新视图
Alter view customers_view as select id, name, salary from customers;
-- 删除视图
Drop view customers_view;
-- 查询结果排序
Select * from customers ORDER BY id asc;
-- 查询结果分组
Select name, sum(salary) from customers Group BY name;
-- 聚合结果过滤
select max(salary) from customers group by age having max(salary) > 20000;
-- 查询结果偏移
select * from customers order by id limit 4 offset 5;
-- 查询太复杂，我们可以为复杂部分定义别名，并使用Impala的with子句将它们包含在查询中
with t1 as (select * from customers where age>25), t2 as (select * from employee where age>25) (select * from t1 union select * from t2)
-- 删除重复值
select distinct name, age, address from customers;