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_companycreate 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
网友评论