大数据单机学习环境搭建(5)Hive建表DDL详解

大数据单机学习环境搭建(5)Hive建表DDL详解

1. Hive建表简单示例

1.1.Hive建表语句

DROP TABLE IF EXISTS employee;
CREATE TABLE employee(
	id bigint comment '编号',
	region_id int comment '区域ID',
	name string comment '员工姓名',
	age int comment '年龄',
	shop_price map<string,int> -- 注意map复杂类型
	)
COMMENT '员工基础表'
PARTITIONED BY (dt string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION '/data'  -- 一般情况下建议使用默认路径
;

1.2.表详细信息

desc formatted employee;

1.3.数据展示

存入HDFS的数据如下

2,10,张三,18,洗衣粉:15-西瓜:20-衣架:30
3,5,李四,20,奶粉:300-足球:200-烧烤架:70

数据提交到tmp.employee_org表下，使用insert + select插入employee表中。
tmp.employee_org的建表见 拓展1：复杂数据分割

-- orc格式的存储只能insert + select，不能load
INSERT INTO employee PARTITION(dt='2022-04-13') SELECT * FROM caw.employee_org;
-- 查询结果
SELECT * FROM employee;

Hive读取出来的数据如下

+-------+--------------+---------+--------+-------------------------------+-------------+
| s.id  | s.region_id  | s.name  | s.age  |         s.shop_price          |    s.dt     |
+-------+--------------+---------+--------+-------------------------------+-------------+
| 2     | 10           | 张三      | 18     | {"洗衣粉":15,"西瓜":20,"衣架":30}    | 2022-04-13  |
| 3     | 5            | 李四      | 20     | {"奶粉":300,"足球":200,"烧烤架":70}  | 2022-04-13  |
+-------+--------------+---------+--------+-------------------------------+-------------+

2. Hive建表语法详解

尽可能详细的描述了各关键字的作用，下方语句不能直接执行，具体使用要看各部分的注释信息做修改和删减

-- DDL：不涉及表内数据的操作
-- 建表：基本语法 EXTERNAL:外表，不写就是内表（外部表删除时不受控制，删除的只是元数据）
CREATE EXTERNAL TABLE IF NOT EXISTS tmp.test(
-- hive数据类型：原生数据类型：int string	timestamp date 	decimal bigint float等
--				复杂数据类型：array<datatype> \ map<key_type,value_type> \struct<col_name,data_type,..>
-- hive数据类型转换：隐式转换：原生类型从窄类型到宽类型的转换
--					显式转换：cast(id as string)
	id bigint comment '编号',
	region_id int comment '区域ID',
	name string comment '员工姓名',
	age int comment '年龄',
	shop_price map<string,int> -- 注意map复杂类型
	)

-- 以下关键字皆可省略
-- 对表进行说明
COMMENT '员工基础表'

-- SQL优化的重要内容：where指定分区避免全表扫描，这种查询方式叫做分区裁剪
-- 分区可以理解为表下的子文件夹(是目录，不是文件)，并不是真正的把文件分开，逻辑分隔
-- 静态分区和动态分区：由 手动指定 还是 自动推断 来区分
	-- 静态分区：用户指定的分区，写死的，例如dt日期分区
		-- 数据加载到文件中，'hive > '是指load命令在hive客户端执行
		-- hive > load data local inpath '/home/dws_app_data/dws_app_demo_data.txt' overwrite into table dws_app.dws_app_demo_data partition (dt='2022-04-01');
	-- 动态分区：分区字段基于查询结果自动推断出来的，核心语法是insert + select
		-- 使用动态分区需要在hive中设置两个参数（执行在CREATE建表之前）
			-- 开启动态分区
			-- set hive.exec.dynamic.partition=true 
			-- 动态分区模式，nonstrict非严格模式，strict严格模式(至少有一个静态分区)
			-- set hive.exec.dynamic.partition.mode=nonstrict
		-- 分区字段依赖位置，最后的a.region_id被用作分区
		-- insert into table dws_app.dws_app_demo_data partition(region)
		-- select a*, a.region_id from dws_app.dws_app_demo_data a
-- 单分区和多重分区
	-- 单分区：实际业务中通常只有时间分区
	-- 多重分区：分区字段间是递进分区，要注意顺序。嵌套文件夹，例如一个时间分区下划分有各区域分区
-- 注意：分区字段不能是表中已存在的字段，分区字段是虚拟字段并不存储在底层的文件中，但查询会显示在结果末尾列
PARTITIONED BY (dt string, region int)

-- 分桶是对分区进一步的更细粒度的划分，是把文件真实分开，物理分隔
-- 使用分桶功能时，2.8版本以前的需要手动开启分桶功能，放在建表语句前set hive.enforce.bucketing=true
-- clustered by (id) into 5 bucket 根据hash(id)对5取模分成5个区：mod(id,5)取模结果0，1，2，3，4
-- sort by (age)即指定每个分桶内根据age倒序排序
-- 分桶好处：1加快查询速度，2join提高MR效率减少笛卡尔积数量，3高效抽样
-- 分桶弊端：数据结构更加复杂，存入时不能自动分桶还需要维护，数据量小还会生成小文件
CLUSTERED BY (id) SORTED BY (age desc) INTO 5 BUCKET

-- 设置文件读写机制：1指定SerDe类，2指定分隔符（使用delimited表示使用默认的LazySimpleSerDe，满足90%的使用场景）
	-- Hive的读写文件机制，SerDe:Serializer、Deserializer 的简称(序列化和反序列化：序列化是对象转化为字节码的过程，而反序列化是字节码转换为对象的过程)
	-- Read:HDFS files -> InputFileFormat -> <key,value> -> Deserializer(反序列化) -> Row object
	-- Write:Row object -> Serializer(序列化) -> <key,value> -> OutputFileFormat -> HDFS files
	-- Hive使用SerDe读取和写入表行对象，需要注意，key部分在读取时会被忽略，而在写入时 key 始终是常数，基本上行对象存储在 value 中
	-- 读取步骤1：首先调用InputFormat(默认情况下读取是TextInputFormat一行一行读取)，返回一条一条kv键值对记录(默认是一行对应一条键值对)
	-- 读取步骤2：调用SerDe(默认SerDe Library是 LazySimpleSerDe)的Deserializer进行反序列化
	-- 读取步骤3：将一条记录中的value根据分隔符语法细节切分为各个字段
-- 下面两种场景只能选择一种，即一个表只能设置一种文件读写机制
-- 使用场景1：指定SERDE为OrcSerde
ROW FORMAT SERDE
	'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
-- 使用场景2：使用delimited表示使用默认的LazySimpleSerDe，满足90%的使用场景
-- 默认分隔符是'\001',一种特殊字符使用ASCII键盘打不出来。vim中连续按下Ctrl+v/Ctrl+a即可输入'\001'显示为'^A';文本编辑器中以SOH形式显示
-- ROW FORMAT DELIMITED 
	-- 没有子语法的话默认使用分隔符是'\001'
	-- 包括4个子语法 字符之间分隔、集合元素间分隔、MAP元素KV之间分隔、行数据之间分隔
	-- 示例数据：
		-- HDFS文本数据：2,西南,张三,18,洗衣粉:15-西瓜:20-衣架:30\n3,东南,李四,20,奶粉:300-足球:200-烧烤架:70
		-- Hive查询结果：分割为多行
		-- 2 西南 张三 18 {"洗衣粉":15,"西瓜":20,"衣架":30}
		-- 3 东南 李四 20 {"奶粉":300,"足球":200,"烧烤架":70}
	-- 指定字符之间分隔符为','
	-- FIELDS TERMINATED BY ',' 
	-- 指定集合元素间分隔符'-'
	-- COLLECTION ITEMS TERMINATED BY '-'
	-- 指定MAP元素KV之间分隔符
	-- MAP KEYS TERMINATED BY ':'
	-- 行数据之间分隔符
	-- 目前只支持'\n':SemanticException 15:20 LINES TERMINATED BY only supports newline '\n' right now. Error encountered near token '';''
	-- LINES TERMINATED BY '\n'

-- 文件存储：4种格式TextFile(默认)、 SequenceFile、 Parquet、ORC(建议)
-- 默认TextFile格式，如果要写入Parquet和ORC，不能load而是要insert
-- 最推荐的是ORC，来源于Hive，且占用小、存储和查询效率都高
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'

-- 指定文件保存路径，LOCATION关键字不受内外表影响，使用相同
-- 默认/user/hive/warehouse，由${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定
LOCATION '/data' --即/user同目录下data存放

-- TBLPROPERTIES是表的一些属性，HIVE内置了一部分属性，使用者也可以在创建表时进行自定义；
-- 拓展(亲测)：建表后也可修改内部表为外部表（外转内同理）alter table test set TBLPROPERTIES ('EXTERNAL'='true');
-- 例如：开启事务支持（默认关闭），关于事务表下方有单独示例
TBLPROPERTIES('transactional'='true')
;

3.拓展1：复杂数据分割

DROP TABLE IF EXISTS tmp.employee_org;
CREATE TABLE caw.employee_org(
	id bigint comment '编号',
	region_id int comment '区域ID',
	name string comment '员工姓名',
	age int comment '年龄',
	shop_price map<string,int> -- 注意map复杂类型
	)
COMMENT '员工基础表'
-- 指定分隔符
ROW FORMAT DELIMITED 
-- 指定字符之间分隔符为','
FIELDS TERMINATED BY ',' 
-- 指定集合元素间分隔符'-'
COLLECTION ITEMS TERMINATED BY '-'
-- 指定MAP元素KV之间分隔符
MAP KEYS TERMINATED BY ':'
;

4.拓展2：事务表

-- 事务表：局限性很强，必须先set开启一堆配置参数，必须是内部表，必须是分桶表、必须是orc格式，必须开启事务功能
set hive.support.concurrency=true;  -- Hive是否支持并发
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; -- 配置事务管理类，由默认的类支持
-- 外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表
	-- ACID:数据库管理系统在写入或更新资料的过程中，为保证事务(transaction)时正确可靠的，必须具备4个特性：
		-- 原子性：一个事务中所有操作，要么全部完成，要么全部不完成。事务执行过程中发生错误，会被恢复到事务开始前状态
		-- 一致性：事务开始前和事务结束后，数据库的完整性没有被破坏，写入的资料必须完全符合所有的预设规则
		-- 隔离性：允许多个并发事务同时对其数据进行读写和修改的能力，防止多个事务并发执行由于交叉执行而导致的数据不一致
		-- 持久性：事务处理结束后，对数据的修改时永久的，即便系统故障也不会丢失
	-- 不允许从非ACID会话读取/写入ACID表
		-- 当前会话开启了事务，可以进行查询写入等操作，新建的未开启事务的会话不能做查询写入操作
-- 事务的本质只是通过标记机制标记为delete，并没有真正删除
create table if not exists tmp.test
	(id bigint comment '编号',
	name string comment '姓名',
	age int comment '年龄',
	shop_price map<string,int> -- 注意map复杂类型
	)
-- 必须是分桶表
clustered by (id) into 2 bucket 
-- 必须是orc格式
stored as orc 
-- 必须开启事务功能
TBLPROPERTIES('transactional'='true')
;

声明：本文所载信息不保证准确性和完整性。文中所述内容和意见仅供参考，不构成实际商业建议，如有雷同纯属巧合。