数据类型

存储格式

数据格式

内部表与外部表区别

基础操作语句

库

查看所有库

show datebases;

切换

use database_name;

查看信息

desc datebase database_name;
-- 查看数据库的键值对信息
desc datebase extended database_name;

创建

CREATE DATABASE if not exists database_name;
-- 指定hdfs存储路径
CREATE DATABASE database_name location '/myhive2';
-- 指定键值对
CREATE DATABASE database_name with dbproperties ('owner'='Zakikun', 'date'='20221002');

修改信息

ALTER DATABASE database_name set dbproperties ('owner'='Zakikun');

删除

-- 删除一个空数据库，如果数据库下面有数据表，那么就会报错
DROP DATABSE database_name;

-- 强制删除数据库，包含数据库下面的表一起删除
DROP DATABSE database_name cascad

表

创建

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
   [(col_name data_type [COMMENT col_comment], ...)] 
   [COMMENT table_comment] 
   [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
   [CLUSTERED BY (col_name, col_name, ...) 
   [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
   [ROW FORMAT row_format] 
   [STORED AS file_format] 
   [LOCATION hdfs_path]

-- 创建一个指定名字的表：如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常
CREATE TABLE 

-- 创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
-- 内部表: 数据移动到指定路径，删除内部表的时候会一并删除metadata和实际数据
-- 外部表：数据不移动，仅记录这个外部表数据所在的路径，删除该外部表的时候只删除metadata，不影响实际数据
EXTERNAL

-- 表示注释，默认不能使用中文
COMMENT

-- 表示使用分区，一个表可以拥有一个或者多个分区，每个分区单独存在一个目录下
PARTITIONED BY

-- 允许用户复制现有的表结构，但是不复制数据
LIKE

-- 可用来指定行分隔符，默认分隔符为 '^A' ，在八进制中为 '\001'
ROW FORMAT DELIMITED 

-- 指定该表数据的存储格式，hive 中，表的默认存储格式为 TextFile
STORED AS  SEQUENCEFILE | TEXTFILE | RCFILE | orc | PARQUET

-- 对于每一个表（table）进行分桶(MapReuce中的分区），桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
CLUSTERED BY

-- 指定表在 HDFS 上的存储路径
LOCATION

-- 指定压缩算法
tblproperties ('orc.compress'='SNAPPY')

删除

-- 习惯性加上if exists判断是否存在, 会将hdfs一并删除
DROP TABLE if exists table_name

查看表结构

-- 格式化表格
desc formatted table_name;
-- 简单表格
desc table_name;

查看分区信息

show partitions table_name;

复制表结构

CREATE TABLE if not exists new_table_name LIKE database.origin_table_name;

复制表（包括数据）

-- 方法1
CREATE TABLE if not exists new_table_name as select * from database.origin_table_name;

-- 方法2
CREATE TABLE if not exists new_table_name LIKE database.origin_table_name;
INSERT INTO new_table_name SELECT * FROM database.origin_table_name;

表改名

ALTER TABLE old_table_name rename to new_table_name;

清空表数据

TRUNCATE TABLE table_name;

插入数据（追加）

-- 将查询数据追加插入到表中
INSERT INTO TABLE table_name [partition(partcol1=val1,partclo2=val2)] select_statement;

插入数据（覆盖/重写）

-- 将查询数据覆盖插入到表中
NISERT OVERWRITE table table_name [partition(partcol1=val1,partclo2=val2)] select_statement;

修改表注释

ALTER TABLE db.table_name SET TBLPROPERTIES('comment' = '新的表备注');

分区

增加分区

正常情况下需要分区不存在，如果为了防止分区存在报错可以加 IF NOT EXISTS 关键字

ALTER TABLE database.table_name ADD [IF NOT EXISTS] PARTITION (分区字段名 = 'XXX') LOCATION '$PATH'

删除分区

ALTER TABLE database.table_name DROP IF EXISTS PARTITION (分区字段名 = 'XXX');

修改分区

ALTER TABLE class_info_partition PARTITION (分区字段名 = 'XXX') SET LOCATION '$PATH' ;

字段

增加/创建

ALTER TABLE table_name ADD COLUMNS(
    新增字段名 新增字段类型 comment '新增字段注释',
    新增字段名 新增字段类型 comment '新增字段注释'
    );

删除字段/反向保留

ALTER TABLE table_name REPLACE COLUMNS(
    保留字段1 保留字段类型1 comment '保留字段类型1注释',
    保留字段2 保留字段类型2 comment '保留字段类型2注释',
    保留字段3 保留字段类型3 comment '保留字段类型3注释',
    保留字段4 保留字段类型4 comment '保留字段类型4注释',
    保留字段5 保留字段类型5 comment '保留字段类型5注释',
    保留字段6 保留字段类型6 comment '保留字段类型6注释',
    ......
);

修改名称/类型/位置/注释

-- 修改字段名
ALTER TABLE table_name RENAME COLUMN 现字段名 TO 新字段名

-- 修改字段名,类型,注释
ALTER TABLE table_name CHANGE column 现字段名 修改后字段名 修改后类型 comment '修改后注释';

-- 修改后字段位置
ALTER TABLE table_name CHANGE column 现字段1名 修改后字段1名 修改后类型 comment '修改后注释' after 改为在某字段后的某字段名;

ALTER TABLE table_name RENAME COLUMN 现字段名 TO 新字段名

-- 修改字段名,类型,注释
ALTER TABLE table_name CHANGE column 现字段名 修改后字段名 修改后类型 comment '修改后注释';

-- 修改后字段位置
ALTER TABLE table_name CHANGE column 现字段1名 修改后字段1名 修改后类型 comment '修改后注释' after 改为在某字段后的某字段名;

数据

select

SELECT [ ALL | DISTINCT ] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING where_condition]
[ORDER BY col_list]
[CLUSTER BY col_list]
[DISTRIBUTE BY col_list]
[SORT BY col_list]
[LIMIT number]

join

full outer join 等价于 full join

with...as...

WITH temp1 as (
    select * from xxx
),temp2 as (
    select * from xxx
)
select * from temp1,temp2;

table_reference [INNER] JOIN table_factor [join_condition]
table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
table_reference LEFT SEMI JOIN table_reference join_condition
table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)

运算符

逻辑运算符

算术运算符

分组

group by

select s_id ,max(s_score) from score group by s_id;

having

select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

where和having区别

having与where不同点
1.where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据
2.where后面不能写分组函数，而having后面可以使用分组函数
3.having只用于group by分组统计语句

排序

order by

全局排序

select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;

对 NULL 值的处理

-- 将 null 值列在最前
select * from student order by student.s_birth nulls first;

-- 将 null 值列在最后
select * from student order by student.s_birth nulls last;

Sort By

每个MapReduce内部局部排序

Distribute By

分区排序

cluster by

合并

union

去重合并select

union all

不去重合并select

常用函数

数值

时间

条件判断

case 
  when age < 20 then '20岁以下'
  when age < 30 then '20-30岁'
  when age < 40 then '30-40岁'
  else '40岁以上'
end as age_type,

字符串

substr( stringA, INT start, INT len)

数据类型转换

cast (value as type)

-- eg.
cast ('2019' as int)

版权属于：Zakikun

本文链接：https://blog.zakikun.com/archives/44.html

本文采用 知识共享署名-非商业性使用 4.0 国际许可协议 进行许可。
您可以自由的转载和修改，但请务必注明文章来源并且不可用于商业目的。