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;