Apache Hive基础实战
–>
什么是Hive：
建立在Hadoop之上的数据仓库解决方案
提供类似sql的查询语言，命名为Hive查询语言HQL，它有最小的学习曲线
早期的Hive开发工作于2007年在Facebook开始
Hive让更多的人使用Hadoop
今天Hive是Hadoop下的*Apache项目，网址是hive.apache.org
Hive可以看成是基于Hadoop的mysql
Hive把Hadoop中的大数据文件映射成SQL数据，并通过SQL语句进行操作
Hive的优势和特点：
提供一个比MR编码更少的简单优化模型
HQL和SQL具有类似的语法和高生产率
Hive支持在不同的计算框架上运行
Hive支持在HDFS和HBase上特别查询数
Hive支持用户定义的函数、脚本和自定义格式
用于ETL和BI工具的成熟JDBC和ODBC驱动程序
稳定可靠（可生产）进行批量加工
Hive拥有一个庞大而活跃的社区
当Hive和MapReduce同时执行WordCount时，MapReduce执行速度更快，但是项目开发时要求开发速度要快，Hive很简洁，在实际开发中不需要配置，也不会出现maven的问题运行更便利，Hive有一个优化器，会执行最有用的代码。
Hive元数据管理：
为了支持模式和数据分区等特性，Hive将其元数据保存在一个关系型数据库中，不是在Hadoop里
默认情况下，Hive是由轻量级内嵌SQL数据库打包的
默认的基于Derby的方法适合于评估测试
模式不是在用户之间共享的，因为每个用户都有自己的嵌入式Derby实例
存储在.metastore_db目录中，该目录驻留在hive启动时所在的目录中
可以轻松切换另一个SQL装置，如MySQL，Oracle
HCatalog将Hive元数据作为Hive的一部分公开给其他生态系统
Hive 体系架构：
Hive借鉴了很多关系型数据库，关系型数据库和Hive可以通用，可以进行数据迁移，但是关系型数据库更加细致，但是Hive功能并不是很细致，只是针对大型数据，Hive适合批量处理数据
Metastore：关系型数据库
Driver：核心组件，包括编译器，优化器，查询执行器
Hive Server2：提供JDBC或者ODBC的连接方式
Hive接口 — 命令行模式
有两种工具：Beeline和命令行（CLI）
有两种模式：命令行模式和交互模式
|
目的 |
HiveServer2 Beeline |
HiveServer1 CLI（命令行） |
|
服务连接 |
beeline –u <jdbcurl> -n <username> -p <password> |
hive –h <hostname> -p <port> |
|
帮助 |
beeline -h or beeline –help |
hive -H |
|
执行查询 |
beeline -e <query in quote> beeline -f <query file name> |
hive -e <query in quote> hive -f <query file name> |
|
定义变量 |
beeline –hivevar key=value |
hive –hivevar key=value |
交互模式：
|
目的 |
HiveServer2 Beeline |
HiveServer1 CLI |
|
输入方式 |
beeline |
hive |
|
连接 |
!connect <jdbcurl> |
N/A（不适用） |
|
List Tables |
!table |
show tables; |
|
List Columns |
!column <table_name> |
desc table_name; |
|
运行查询 |
<HQL>;（一定要有分号） |
<HQL>; |
|
保存结果 |
!record <file_name> !record |
N/A（不适用） |
|
Run Shell CMD |
!sh ls |
!ls; |
|
Run DFS CMD |
dfs -ls |
dfs -ls ; |
|
Run SQL File |
!run <file_name> |
source <file_name>; |
|
检查版本 |
!dbinfo |
!hive –version; |
|
退出模式 |
!quit |
quit; |
如果hiveserver2服务没有启动，应该用命令行模式访问hive
Hive JDBC URL：
URL语法：
jdbc:hive2://zookeeper_quorum|hs2_host:port/[db][;principal=<hs2_principal>/<hs2_host>|[email protected]<KDC_REALM>][;transportMode=binary|http][;httpPath=<http_path>][;serviceDiscoveryMode=zookeeper;zooKeeperNamespace=<zk_namespace>][;ssl=true|false][;sslKeyStore=<key_store_path>][;keyStorePassword=<key_store_password][;sslTrustStore=<trust_store_path>][;trustStorePassword=<trust_store_password>][;twoWay=true|false]
一个具体项目环境的例子：
jdbc:hive2://abcnf03.devfg.xxx.com:2181/dev_xxx_hive;serviceDiscoveryMode=zooKeeper;zooKeeper Namespace=hiveserver2;principal=hive/[email protected]?tez.queue.name=D_NO_SLA; hive.exec.dynamic.partition.mode=nonstrict”
虚拟机里的URL：
jdbc:hive2://localhost:10000/default
Hive的其他使用环境：
Hive Web Interface (As part of Apache Hive)
Hue (Cloudera)
Ambari Hive View (Hortonworks)
JDBC/ODBC（ETL 工具，商业智能工具， 集成开发环境）
Informatica，Talend等等
Tableau，QlikView，Zeppelin等等
Oracle SQL Developer，DB Visualizer等等
执行Hive：
命令行连接：
Hive
beeline连接：
hiveserver2
beeline
!connect jdbc:hive2://192.168.80.109:10000
bda
123mao=MAO
hive的HiveServer2/beeline配置及使用
第一：修改 hadoop 集群的 hdfs-site.xml 配置文件：加入一条配置信息，表示启用 webhdfs
cd /home/hadoop/apps/hadoop-2.7.5/etc/hadoop
<property>
<name>dfs.webhdfs.enabled</name>
<value>true</value>
</property>
第二：修改 hadoop 集群的 core-site.xml 配置文件：加入两条配置信息：表示设置 hadoop的代理用户
<property>
<name>hadoop.proxyuser.bda.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.bda.groups</name>
<value>*</value>
</property>
CREATE TABLE IF NOT EXISTS flowrec(
idx int,
dttm timestamp,
phn string,
upflow int,
downflow int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
STORED AS TEXTFILE;
查看表：describe 表名;
查看当前的数据表：select current_database();
Like创建一个相同的表，但是照抄原来表的结构，并不复制表数据
Hive数据类型：
|
类型 |
例子 |
类型 |
例子 |
|
TINYINT |
10Y |
SMALLINT |
10S |
|
INT |
10 |
BIGINT |
100L |
|
FLOAT |
1.342 |
DOUBLE |
1.234 |
|
DECIMAL |
3.14 |
BINARY |
1010 |
|
BOOLEAN |
TRUE |
STRING |
Book’ or “Book” |
|
CHAR |
‘YES’ or “YES” |
VARCHAR |
‘Book’ or “Book” |
|
DATE |
‘2013-01-31’ |
TIMESTAMP |
‘2013-01-31 00:13:00.345’ |
数据类型表达补充：
十进制小数转化成二进制小数
十进制的小数转换为二进制，主要是小数部分乘以2，取整数部分依次从左往右放在小数点后，直至小数点后为0。例如十进制的0.125，要转换为二进制的小数：转换为二进制，将小数部分0.125乘以2，得0.25，然后取整数部分0，再将小数部分0.25乘以2，得0.5，然后取整数部分0，再将小数部分0.5乘以2，得1，然后取整数部分1，则得到的二进制的结果就是0.001
小数点是浮动的叫做浮点数
0.00000000018 表示为：18e-12
190000000 表示为：19e7
复杂数据类型：
|
类型 |
例子 |
定义 |
例子 |
|
ARRAY |
[‘Apple’,’Orange’,’Mongo’] |
ARRAY<string> |
a[0] = ‘Apple’ |
|
MAP |
{‘A’:’Apple’,’O’:’Orange’} |
MAP<string, string> |
b[‘A’] = ‘Apple’ |
|
STRUCT |
{‘Apple’, 2} |
STRUCT<fruit:string,weight:int> |
c.weight = 2 |
ARRAY对所有元素具有相同的类型等于MAP使用从0开始的序列作为键
MAP具有相同类型的键值对
STRUCT就像table/records（表/记录）
大数据数据建模与数据仓库数据建模相比较在数据类型上不需要注意太多，使得数据建模更加简单，更关注数据类型使用的场合和作用。
Hive的元数据结构：
|
数据结构 |
逻辑 |
物理（HDFS） |
|
Database |
表的集合 |
文件夹和文件 |
|
Table |
数据行的集合 |
文件夹和文件 |
|
Partition |
要分割数据的列 |
文件夹 |
|
Buckets |
用于分发数据的列 |
文件 |
|
Row |
行记录 |
文件行 |
|
Columns |
片记录 |
每一行中指定的位置 |
|
Views |
数据行的快捷方式 |
不适用 |
|
Index |
|
文件夹和文件 |
Hive数据库：
数据库是用于类似目的或属于同一组的表的集合。
如果没有指定数据库（使用database_name），则默认使用默认数据库。
Hive为/user/ Hive /warehouse中的每个数据库创建一个目录，可以通过hive.metastore.warehouse.dir这个目录进行定义，除了默认数据库。（默认数据库表直接建立在该目录下）
创建表：
create database if not exists myhivebook;
使用表：
use myhivebook;
显示表：
show databases;
描述表：
describe database default;
改变表属性：
alter database myhivebook set owner user dayongd;
删除表：
drop database if exists myhivebook cascade;
select current_database(); 来知道和显示当前所在数据库
Hive Tables：
外部表：
数据保存在由LOCATION关键字指定的HDFS路径中。Hive不完全管理数据，因为删除表（元数据）不会删除数据，对数据进行引入，但是不对数据进行管理，对数据有保护作用
内部表/管理表：
数据保存在默认路径中，例如/user/hive/warehouse/employee。数据完全由Hive管理，因为删除表（元数据）也会删除数据
Hive的问题：
什么是内部表和外部表？
外部表数据保存在由LOCATION关键字指定的HDFS路径中。
内部表/管理表数据保存在默认路径中，例如/user/hive/warehouse/employee。
内部表和外部表的关键区别是什么？
删除文件角度：删除表（元数据）不会删除数据，删除表（元数据）也会删除数据
从管理角度：data的完全管理是通过内部表，外部表Data不需要管理
内部表和外部表的最佳实践是什么？
一些数据来源是外部，比如客户提供的数据，或者元数据不能因为误操作而删除，就使用外部表；如果有共享数据的需求，想处理数据之后，分享给其他用户，这时不想让其他人查看自己核心数据库，一般使用外部表。
当进行数据转换的时候，做数据清洗的时候，一般建立内部表；
建表语法：
CREATE EXTERNAL TABLE IF NOT EXISTS employee_external (
// IF NOT EXISTS是个选项，TEMPORARY还支持临时表
name string,
work_place ARRAY<string>,
sex_age STRUCT<sex:string,age:int>,
skills_score MAP<string,int>,
depart_title MAP<STRING,ARRAY<STRING>>
)
//具有数据类型的列的列表，没有约束支持
COMMENT ‘This is an external table’
//表注释是可选的
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘|’
//如何分隔列
COLLECTION ITEMS TERMINATED BY ‘,’
MAP KEYS TERMINATED BY ‘:’
//分割MAP和集合
STORED AS TEXTFILE
//设置文件格式
LOCATION ‘/user/dayongd/employee’;
// HDFS中的数据文件路径
分隔符：
Hive中的默认分隔符：
字段分隔符： 可以使用Ctrl + A或^A（创建表时使用 \001）
收集项目分隔符：可以使用Ctrl + B或^B（\002）
map主要分隔符：可以使用Ctrl + C或^C（\003）
问题：
如果在表创建过程中重写了分隔符，那么它只能在平面结构JIRA Hive-365中工作。对于嵌套类型，嵌套的级别决定分隔符。
示例：
数组的数组，外层数组是^B，内层数组是^C
Map的数组，外层Map是^C，内层数组是^D
存储序列：
SERDE：序列化和反序列化类
Serialize，Deserialize
Hive支持使用不同类型的存储serde，以正确的格式保存文件后，存储为语句。
LazySimpleSerDe：TEXTFILE
BinarySerializerDeserializer：SEQUENCEFILE
ColumnarSerDe：ORC，RCFILE
ParquetHiveSerDe：PARQUET
AvroSerDe：AVRO
OpenCSVSerDe：用在CST/TSV
示例：
CREATE TABLE my_table(a string, b string, …)
ROW FORMAT
SERDE ‘org.apache.hadoop.hive.serde2.OpenCSVSerde’
WITH SERDEPROPERTIES (
“separatorChar” = “\t”,
“quoteChar” = “‘”,
“escapeChar” = “\\”
)
STORED AS TEXTFILE;
JSONSerDe：
CREATE TABLE my_table(a string, b bigint, …)
ROW FORMAT SERDE ‘org.apache.hive.hcatalog.data.JsonSerDe’
STORED AS TEXTFILE;
RegExSerDe：
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.RegexSerDe’
WITH SERDEPROPERTIES (
“input.regex” = “<regex>”
)
STORED AS TEXTFILE;
HBaseSerDe：
CREATE TABLE test_serde_hb(
id string,
name string,
sex string,
age string
)
ROW FORMAT SERDE’org.apache.hadoop.hive.hbase.HBaseSerDe’
STORED BY’org.apache.hadoop.hive.hbase. HBaseStorageHandler’
WITH SERDEPROPERTIES (“hbase.columns.mapping”=”:key,info:name,info:sex,info:age”)
TBLPROPERTIES(“hbase.table.name” = “test_serde”);
查找表命令练习：
show tables; show tables ‘*sam*’; show tables ‘*sam|lily*’ ;
show table extended like ‘o*’;
desc [formatted|extended] table_name
show create table table_name;
show columns table_name;
show tblpropertiestblname;
Hive高阶建表语句（CTAS and WITH）
CTAS — Create Table As Select
CREATE TABLE ctas_employee as SELECT * FROM employee
CTAS不能创建分区、外部表或桶表
CTAS和Common Table Expression（CTE）
CTE就是CTAS加上WITH，子查询嵌套使用Common Table Expression，把每个临时表提前定义好，比如说定义好r2，r1的定义里就可以引用r2，最终通过一个查询来建立新表。
Explain CREATE TABLE cte_employee AS WITH
r1 AS (SELECT name FROM r2 WHERE name = ‘Michael’),
r2 AS (SELECT name FROM employee WHERE sex_age.sex= ‘Male’),
r3 AS (SELECT name FROM employee WHERE sex_age.sex= ‘Female’)
SELECT * FROM r1 UNION ALL SELECT * FROM r3;
Union进行表连接，将r1中的所有元素和r3中的所有元素查询连接到一起
复制一个和其他表一样的表（快速建表）
CREATE TABLE employee_like LIKE employee
Hive临时表：
应用程序自动管理在复杂查询期间生成的中间数据的一种方便方法（类似于CTE，它只有一条语句）
仅会话，自动删除，相同的名称在不同的会话
表空间位于/tmp/hive-<user_name>（安全考虑）
当普通表和临时表拥有相同的名称时，临时表会被优先调用
CREATE TEMPORARY TABLE tmp_table_name1 (c1 string);
CREATE TEMPORARY TABLE tmp_table_name2 AS..
CREATE TEMPORARY TABLE tmp_table_name3 LIKE..
查看表所在的位置和空间以及建表语句就知道是不是临时表
Hive表 — Drop/Truncate/Alter Table：
DROP TABLE IF EXISTS employee语句完全删除元数据，并在配置后将数据移动到HDFS中用户主目录中的.garbage文件夹中。使用PERGE选项，数据将被完全删除。当删除外部表时，数据不会被删除。
TRUNCATE TABLE employee语句删除内部表（外部表会失败）中的所有数据行。
ALTER TABLE employee RENAME TO new_employee语句重命名表（经常用于数据修复前的暂时备份）
ALTER TABLE c_employee SET TBLPROPERTIES (‘comment’=’New name, comments’)语句设置TABLE属性
ALTER TABLE employee_internal SET SERDEPROPERTIES (‘field.delim’ = ‘$ ‘)语句设置SerDe（序列化和反序列化）属性，设置字符分隔符
ALTER TABLE c_employee SET FILEFORMAT RCFILE语句设置文件格式。
（alter在元数据层面进行修改，并不改变真实的数据，只是修正）
ALTER TABLE employee_internal CHANGE old_name new_name STRING [BEFORE|AFTER] sex_age这个语句可以用于更改列名、位置或类型
ALTER TABLE c_employee ADD COLUMNS (work string) 该语句在最后向表中添加另一列和类型
ALTER TABLE c_employee REPLACE COLUMNS (name string) 此语句用指定的列和类型替换表中的所有列。在本例中的ALTER之后，表中只有一列
ALTER TABLE语句只修改Hive的元数据，而不是实际的数据。用户应确保实际数据符合元数据定义。
Hive分区概述：
分区对应的是目录，不能从文件中找数据分区
为了提高性能，Hive可以对数据进行分区
分区列的值将表划分为段（文件夹）
可以在查询时忽略整个分区
分区必须由用户正确创建。插入数据时必须指定分区
在查询中使用“分区”列和常规列在模式上没有区别
在查询时，Hive将自动过滤掉没有用于提高性能的分区
分区的定义和操作：
CREATE TABLE employee_partitioned(
name string,
work_place ARRAY<string>,
sex_age STRUCT<sex:string,age:int>,
skills_score MAP<string,int>,
depart_title MAP<STRING,ARRAY<STRING>>
)
PARTITIONED BY (Year INT, Month INT)
（分区键不能和任何列重名）
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘|’
COLLECTION ITEMS TERMINATED BY ‘,’
MAP KEYS TERMINATED BY ‘:’;
Hive建立静态分区：
静态分区不是自动启用的。我们必须通过ALTER TABLE语句添加/删除分区
alter table employee_p add
partition (year=2017, month=4)
partition (year=2017, month=5);
显示分区信息：show partitions employee_p;
删除分区：alter table employee_p drop partition (year=2107, month=4);
Hive建立动态分区：
Hive还支持动态地提供分区值。当数据量很大而我们不知道分区值是多少时，这是非常有用的。
默认情况下，用户必须指定至少一个静态分区列。这是为了避免意外地覆盖分区。要禁用此限制，可以将分区模式从默认严格模式设置为nonstrict
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table employee_p partition(year, month)
select
name,
array(‘Toronto’) as work_place,
named_struct(“sex”,”male”,”age”,30) as sex_age,
map(“python”,90) as skills_score,
map(“r&d”, array(‘developer’)) as depart_title,
year(start_date) as year,
month(start_date) as month
from employee_hr eh
where eh.empployee_id = 102;
动态分区是动态写入，通常用在数据转换和重新组织数据时使用；静态分区一般用在数据加载的时候，每次加载一个新数据就建立一个静态分区，进行数据的重新组合
Hive分桶概述：
分桶需要语句写入，分桶之后动态加载，不能静态加载，只能加载数据之后分桶。
bucket对应于HDFS中的文件段
随机采样数据或加速连接的机制
基于哈希函数将数据分解为一组桶的“桶列”
Hive不会自动执行嵌套。要求设置强制装桶：SET hive.enforce.bucketing = true;
bucket列的选择与业务逻辑密切相关
为了定义桶的数量，应该避免每个桶中有太多或太少的数据。更好的选择是接近两个数据块的地方。使用2N作为桶的数量
（两个数据块的大小是Hadoop HDFS中的默认块大小）
Hive分桶建表语句：
使用CLUSTERED BY语句来定义bucket
与分区不同，分桶列名出现在列定义
支持多个列
要将数据填充到桶中，我们必须使用INSERT语句而不是LOAD语句，因为它不能根据元数据定义验证数据（仅将文件复制到文件夹中）
bucket是文件段的列表
CREATE TABLE employee_id_buckets(
name string,
employee_id int,
work_place ARRAY<string>,
sex_age STRUCT<sex:string,age:int>,
skills_score MAP<string,int>,
depart_title MAP<STRING,ARRAY<STRING>> )
CLUSTERED BY (employee_id) INTO 2 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘|’
COLLECTION ITEMS TERMINATED BY ‘,’
MAP KEYS TERMINATED BY ‘:’;
分桶抽样：
随机抽样基于整行数据：
SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;
随机抽样基于指定列（使用分桶列更高效）：
SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON id) s;
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。
注意：x的值必须小于等于y的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck
如果y不是table总bucket数的倍数或者因子。就会取出数据中y的倍数
随机抽样基于block size：
SELECT * FROM table_name TABLESAMPLE(10 PERCENT) s;
SELECT * FROM table_name TABLESAMPLE(1M) s;
SELECT * FROM table_name TABLESAMPLE(10 rows) s;
使用分桶可以提高数据关联，提高join的性能，帮助抽样
本文来源 互联网收集，文章内容系作者个人观点，不代表 本站 对观点赞同或支持。如需转载，请注明文章来源,如您发现有涉嫌抄袭侵权的内容，请联系本站核实处理。
相关文章
