1. 借鉴

Hive 官网 LanguageManualDDL-CreateTableCreate/Drop/TruncateTable
Hive
完整建表语句
HIVE表索引，分区和分桶的区别
11-大数据-hive分区表和分桶表的区别
hive-group by的时候把两个字段变成map
hive 中 统计某字段json数组中每个value出现的次数

2. 开始

常用数据类型

Hive 类型	Java 类型
tinyint	byte
smalint	short
int	int
bigint	long
boolean	boolean
float	float
double	double
string	String
char	char
date	Date
struct
map	Map
array	List

建库

• 不指定hdfs位置
  此时库会存在/user/hive/warehouse目录下，并取名为db_company

  create database db_company;
  

• 指定hdfs位置
  此时库会存在/hotel下

  create database db_company location '/hive.db/company'
  

• 不存在则创建

  create database if not exists db_company;
  

查看库

• 查询全部

  show databases;
  

• 模糊查询

  show databases like 'db_*';
  

• 查看数据库详情

  desc database db_company;
  

切换数据库

use db_company;

删除数据库

drop database db_company;

• 注意：
  如果要删除一个已经有数据的库，会报以下错误

  hive (db_hotel)> drop database db_company;
  FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_company is not empty. One or more tables exist.)
  

  解决方法是使用cascade关键字，强制删除

  drop database db_company cascade;
  

创表语句

常用建表语句如下：

# 方式1
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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]

# 方式2
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

关键字解析

关键字	可选	释义
TEMPORARY	√	临时表，当sesssion关闭时，表会被删除
EXTERNAL	√	表存储类型分为 MANAGED => (管理表，内部表) EXTERNAL => (外部表)
COMMENT	√	注释
PARTITIONED BY	√	分区
CLUSTERED BY	√	分捅
SORTED BY	√	结合 CLUSTERED BY使用
ROW FORMAT	√	指定规则，行，列，array，map，struct切分格式
STORED AS	√	存储文件类型
LOCATION	√	指定hdfs存储位置
LIKE	√	复制现有的表结构，但是不复制数据

• ①. row_format

  在建表示可以指定 序列化/反序列化(SERDE => Serialize/Deserilize)，如果没有指定row_format或者ROW FORMAT DELIMITED，使用默认的SERDE

  # 语法如下：
  DELIMITED # 关键字，表明以下为当前表的规则
  [FIELDS TERMINATED BY char [ESCAPED BY char]] # 指定列分隔符
  [COLLECTION ITEMS TERMINATED BY char]  # 指定array, map, struct的分隔符
  [MAP KEYS TERMINATED BY char] # 指定map的key和value的分隔符
  [LINES TERMINATED BY char] # 指定行分隔符
  [NULL DEFINED AS char] # 指定NULL数据默认值
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] # 指定序列化
  

• ②. MANAGED(内部表) / EXTERNAL(外部表)

  MANAGED: 默认创建的表都是内部表，当我们删除内部表时，数据也会同时被删除，不适合和其他组件进行数据共享

  EXTERNAL: 外部表，删除该表并不会删除掉数据，只会删除meatestore中的元信息，适合数据共享

• ③. 分区和分捅的区别
  因见解十分精确，以下区别摘自这里，十分感谢
  分区：细化数据管理，直接读对应目录，缩小mapreduce程序要扫描的数据量
  分桶：1、提高join查询的效率（用分桶字段做连接字段） 2、提高采样的效率

  Ⅰ. 从表现形式上：
  分区表是一个目录，分桶表是文件

  Ⅱ. 从创建语句上：
  分区表使用partitioned by 子句指定，指定字段为伪列，需要指定字段类型
  分桶表由clustered by 子句指定，指定字段为真实字段，需要指定桶的个数

  Ⅲ. 从数量上：
  分区表的分区个数可以增长，分桶表一旦指定，不能再增长

  Ⅳ. 从作用上：
  分区避免全表扫描，根据分区列查询指定目录提高查询速度
  分桶保存分桶查询结果的分桶结构（数据已经按照分桶字段进行了hash散列）。
  分桶表数据进行抽样和JOIN时可以提高MR程序效率

创建表

我们基于以下数据创建一个hotel内部表

数据

# 酒店名称，标签，省_市_区，cid_pId
互欣商务酒店,商务出行_浪漫情侣,北京_北京_昌平区,cId:13452010_pId:16245202
通城商务酒店,观影房_空调_免费停车,北京_北京_东城区,cId:13452011_pId:16245203

建表

create table db_company.hotel (
name string,
tags array<string>,
address struct<province:string, city:string, district :string>,
ids map<string, bigint>
)
row format delimited 
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

加载数据

加载本地文件

load data local inpath '/opt/datas/hotel-2020-06-01.txt' into table hotel;

加载hdfs文件

load data inpath '/opt/datas/hotel.txt' into table hotel;

修改表

重命名

 alter table hotel rename to test_hotel;

修改列

语法如下：

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

hql举例：

# 更新test_hotel表，address列类型为string
alter table test_hotel change column address address string;

# 更新test_hotel表，address列为newname，类型为string
alter table test_hotel change column address newname string;

增加列

语法如下：

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...) 

添加的字段位置在所有列后面
hql举例：

# 添加一列last_modifier
alter table test_hotel add columns(last_modifier date);

替换列

语法如下：

ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

其语法跟添加列类似，只是将ADD替换成了REPLACE，另外需要注意的是，replace会替换表中的所有列，如果有的列不需要变更，还是需要列举出来
hql举例：
如果你按照以下写hql，则会发现最后表只有一列了

alter table test_hotel replace columns(name string);

我们使用desc看下表

desc test_hotel;

结果如下：

Time taken: 0.101 seconds
hive (db_company)> desc test_hotel;
OK
col_name        data_type       comment
name            string                                      
Time taken: 0.062 seconds, Fetched: 1 row(s)

看到上边只剩下一列name了，如果我们有的列不想被替换，则需要全部列举出来，以下为正解:

alter table test_hotel replace 
columns(
name string, 
tags array<string>, 
ids map<string,bigint>, 
last_modifier timestamp
);

我们列一个表格来看下这个语句我们干了啥，变更项我用红色标注了

列名 类型	变更前	变更后
name string	name string	name string
tags array<string>	tags array<string>	tags array<string>
address string	address string	因为不在语句的columns中，所以被删除了
ids map<string,bigint>	ids map<string,bigint>	ids map<string,bigint>
last_modifier date	last_modifier date	ids timestamp

另外，删除列也需要用这种方式。

删除表

drop table hotel;

内外部表相互转换

查看表的详细信息

desc formatted hotel

我们看下执行结果

hive (db_company)> desc formatted hotel;
OK
col_name        data_type       comment
# col_name              data_type               comment             
                 
name                    string                                      
tags                    array<string>                               
address                 struct<province:string,city:string,district:string>                         
ids                     map<string,bigint>                          
                 
# Detailed Table Information             
Database:               db_company               
Owner:                  root                     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://hadoop01:9000/hive.db/company/hotel       
Table Type:             MANAGED_TABLE            
Table Parameters:                
        transient_lastDdlTime   1587363395          
                 
# Storage Information            
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe       
InputFormat:            org.apache.hadoop.mapred.TextInputFormat         
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat       
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:             
        colelction.delim        _                   
        field.delim             ,                   
        line.delim              \n                  
        mapkey.delim            :                   
        serialization.format    ,                   
Time taken: 0.226 seconds, Fetched: 33 row(s)

可以看到TableType为 MANAGED_TABLE

修改内部表变为外部表

# 其中EXTERNAL=TRUE全为大写
alter table hotel set tblproperties('EXTERNAL'='TRUE');

修改外部表变为内部表

# 其中EXTERNAL=FALSE全为大写
alter table hotel set tblproperties('EXTERNAL'='FALSE');

查询

总的来说，hive的语句和mysql的大同小异，区别在于join，分区，分捅，和其他一些函数的运用

查看建表语句

show create table hotel;

结果如下：

createtab_stmt
CREATE TABLE `hotel`(
  `name` string, 
  `tags` array<string>, 
  `address` struct<province:string,city:string,district:string>, 
  `ids` map<string,bigint>)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
  COLLECTION ITEMS TERMINATED BY '_' 
  MAP KEYS TERMINATED BY ':' 
  LINES TERMINATED BY '\n' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://hadoop01:9000/hive.db/company/hotel'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false', 
  'EXTERNAL'='FALSE', 
  'last_modified_by'='root', 
  'last_modified_time'='1592747989', 
  'numFiles'='0', 
  'numRows'='-1', 
  'rawDataSize'='-1', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1592747989')
Time taken: 0.04 seconds, Fetched: 26 row(s)

基本查询

# 查询全部
select * from hotel;

# 查询总量
select count(*) from hotel;

# ids是一map，可以使用[]指定key
select name, tags, address.city, ids['cId'] from hotel;

# tags是一个数组，数组使用下标获取，从0开始计数
select name, tags[2], address.city, ids['cId'] from hotel;

# address是一个struct，使用.语法指定属性
select address.city from hotel;

# 查询最大值
select max(ids['cId']) from hotel;

# 查询tags标签的总数
select sum(size(tags)) from hotel;

# 查询平均值
select avg(ids["pId"]) from hotel;

# 限制条数
select * from hotel limit 1;

条件查询

# 查询标签数量大于2的酒店
select * from hotel where size(hotel.tags) > 2;

# 查询没有标签的酒店
select * from hotel where tags is null;

# 查询用户ID在13452010, 13452011中的数据
select * from hotel where ids['cId'] in (13452010, 13452011);

模糊查询

类型	通配符
like	可以使用以下字符： % 代表零个或多个字符(任意个字符) _ 代表一个字符
rlike	支持java正则表达式

# 查询cId匹配正则的数据
 select * from hotel where ids['cId'] rlike '[1-9]+';

分组条件

# 按照区进行划分，并统计数量
select address.district, count(address.district) from hotel group by address.district;

# 按照 地址进行分组，过滤是北京的地址
select address from hotel group by address having address.city = '北京';

join

hive的join只支持等值查询
这里我们在创建一张表
数据如下：

13452010,孙瑞锴
13452011,EDG

DDL如下:

create table db_company.muser(id bigint, username string) 
row format delimited 
fields terminated by ',' 
lines terminated by '\n';

加载数据

load data local inpath '/opt/envs/datas/user.txt' into table muser;

查询入住酒店客户名称

select h.*, u.username from hotel h
left join muser u on h.ids['cId'] = u.id;

3. 大功告成

排序，分区，分捅是十分重要的概念和操作，我们放到下一篇中重点整理。
thx