Hive
一 数据仓库与数据库的区别
数据库与数据仓库的区别实际讲的是 OLTP 与 OLAP 的区别。
操作型处理，叫联机事务处理 OLTP（On-Line Transaction Processing，），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理。
分析型处理，叫联机分析处理 OLAP（On-Line Analytical Processing）一般针对某些主题的历史数据进行分析，支持管理决策。
首先要明白，数据仓库的出现，并不是要取代数据库。
数据库是面向事务的设计，数据仓库是面向主题设计的。
数据库一般存储业务数据，数据仓库存储的一般是历史数据。
数据库设计是尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的 User 表，记录用户名、密码等简单数据即可，符合业务应用，但是不符合分析。数据仓库在设计是有意引入冗余，依照分析需求，分析维度、分析指标进行设计。
数据库是为捕获数据而设计，数据仓库是为分析数据而设计。以银行业务为例。数据库是事务系统的数据平台，客户在银行做的每笔交易都会写入数据库，被记录下来，这里，可以简单地理解为用数据库记账。数据仓库是分析系统的数据平台，它从事务系统获取数据，并做汇总、加工，为决策者提供决策的依据。比如，某银行某分行一个月发生多少交易，该分行当前存款余额是多少。如果存款又多，消费交易又多，那么该地区就有必要设立 ATM 了。显然，银行的交易量是巨大的，通常以百万甚至千万次来计算。事务系统是实时的，这就要求时效性，客户存一笔钱需要几十秒是无法忍受的，这就要求数据库只能存储很短一段时间的数据。而分析系统是事后的，它要提供关注时间段内所有的有效数据。这些数据是海量的，汇总计算起来也要慢一些，但是，只要能够提供有效的分析数据就达到目的了。数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它决不是所谓的“大型数据库”。
二 数据仓库的分层模型
数据仓库的数据来源于不同的源数据，并提供多样的数据应用，数据自下而上流入数据仓库后向上层开放应用，而数据仓库只是中间集成化数据管理的一个平台。
源数据层（ODS）：此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。
数据仓库层（DW）：也称为细节层，DW 层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗（去除了杂质）后的数据。
数据应用层（DA 或 APP）：前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是 ETL（抽取 Extra, 转化 Transfer, 装载 Load）的过程，ETL 是数据仓库的流水线，也可以认为是数据仓库的血液，它维系着数据仓库中数据的新陈代谢，而数据仓库日常的管理和维护工作的大部分精力就是保持 ETL 的正常和稳定。
为什么要对数据仓库分层？
用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据；不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。通过数据分层管理可以简化数据清洗的过程，因为把原来一步的工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误的时候，往往我们只需要局部调整某个步骤即可。
三 Hive介绍
Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件 映射为一张数据库表，并提供类 SQL 查询功能。本质是将 SQL 转换为 MapReduce 程序。主要用途：用来做离线数据分析，比直接用 MapReduce 开发效率更高。
3.1 为什么使用Hive
直接使用 Hadoop MapReduce 处理数据所面临的问题：人员学习成本太高。MapReduce 实现复杂查询逻辑开发难度太大。使用 Hive ：操作接口采用类 SQL 语法，提供快速开发的能力避免了去写 MapReduce，减少开发人员的学习成本功能扩展很方便
四 Hive的架构
4.1 Hive的组件
用户接口：包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为 shell 命令行；JDBC/ODBC 是 Hive 的 JAVA 实现，与传统数据库JDBC 类似；WebGUI 是通过浏览器访问 Hive。
元数据存储：通常是存储在关系数据库如 mysql/derby 中。Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
解释器、编译器、优化器、执行器:完成 HQL 查询语句从词法分析、语法分
析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随
后有 MapReduce 调用执行。
4.2 Hive与Hadoop的关系
Hive 利用 HDFS 存储数据，利用 MapReduce查询分析数据。
五 Hive与传统关系型数据库对比
hive 用于海量数据的离线数据分析。hive 具有 sql 数据库的外表，但应用场景完全不同，hive 只适合用来做批量数据统计分析。
5.1 Hive的目录结构
Hive 中所有的数据都存储在 HDFS 中，表结构存储在关系行数据库中。没有专门的数据存储格式在创建表时指定数据中的分隔符，Hive 就可以映射成功，解析数据。
Hive 中包含以下数据模型：
db ：在 hdfs 中表现为 hive.metastore.warehouse.dir 目录下一个文件夹
table ：在 hdfs 中表现所属 db 目录下一个文件夹
external table ：数据存放位置可以在 HDFS 任意指定路径
partition ：在 hdfs 中表现为 table 目录下的子目录
bucket ：在 hdfs 中表现为同一个表目录下根据 hash 散列之后的多个文
六 Hive的安装与启动
6.1 Hive安装
1.上传tar包到指定路径
2.解压
tar -zxvf tar包
3.安装mysql数据库
推荐yum 在线安装
4.配置hive
（a）配置HIVE_HOME环境变量
vi conf/hive-env.sh
配置其中的hadoop的环境变量
（b）配置元数据库信息
vi hive-site.xml
添加如下内容：
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
<description>password to use against metastore database</description>
</property>
</configuration>
5.安装hive和mysq完成后，将mysql的连接jar包拷贝到$HIVE_HOME/lib目录下
如果出现没有权限的问题，在mysql授权(在安装mysql的机器上执行)
mysql -uroot -p
#(执行下面的语句 *.*:所有库下的所有表 %：任何IP地址或主机都可以连接)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;
6. Jline包版本不一致的问题，需要拷贝hive的lib目录中jline.2.12.jar的jar包替换掉hadoop中的
/home/hadoop/app/hadoop-2.6.4/share/hadoop/yarn/lib/jline-0.9.94.jar
6.2 启动Hive
Hive几种使用方式：
1.Hive交互shell bin/hive ----执行hive命令后仍然处于hive交互中
2.Hive JDBC服务(参考java jdbc连接mysql)
3.hive启动为一个服务器，来对外提供服务
bin/hiveserver2（在安装了hive的其中一台机器上）
或者
nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err &
启动成功后，可以在别的节点上用beeline去连接
bin/beeline -u jdbc:hive2://主机名或者ip:10000 -n root
或者
bin/beeline
! connect jdbc:hive2://主机名或者ip:10000
4.Hive命令 ---执行hive命令后返回Linux系统中
hive -e ‘sql’（-e 后接一条sql语句）
bin/hive -e 'select * from t_test'
或者
hive -f 一个sql文件
七 Hive基本操作
在学习过程中如果程序集群模式效率低，可以切换到本地模式(再Linux中模拟一套分布式环境来跑程序)。线上环境不要使用本地模式。
set hive.exec.mode.local.auto=true;
7.1 DDL(数据库定义语言)操作
7.1.1 创建表
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]
说明：
1、 CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
用户可以用 IF NOT EXISTS S 选项来忽略这个异常。
2、EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（ LOCATION）,重点在于这种创建方式无需移动数据。Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3、LIKE 允许用户复制现有的表结构，但是不复制数据。
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table;
4、 ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char] | SERDE serde_name
[WITH SERDEPROPERTIES
(property_name=property_value, property_name=property_value,...)]
hive 建表的时候默认的分割符是'\001'，若在建表的时候没有指明分隔符，load 文件的时候文件的分隔符需要是'\001'；若文件分隔符不是'001'，程序不会报错，但表查询的结果会全部为'null'；
用 vi 编辑器 Ctrl+v 然后 Ctrl+a 即可输入'\001' -----------> ^A
SerDe 是 Serialize/Deserilize 的简称，目的是用于序列化和反序列化。
Hive 读取文件机制：首先调用 InputFormat（默认 TextInputFormat），返回一条一条记录（默认是一行对应一条记录）。然后调用SerDe （默认LazySimpleSerDe）的 Deserializer，将一条记录切分为各个字段（默认'\001'）。
Hive 写文件机制：将 Row 写入文件时，主要调用 OutputFormat、SerDe 的
Seriliazer，顺序与读取相反。
可通过 desc formatted 表名；进行相关信息查看。当我们的数据格式比较特殊的时候，可以自定义 SerDe。
5、PARTITIONED BY
在 hive Select 查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了 partition 分区概念。分区表指的是在创建表时指定的 partition 的分区空间。一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。表和列名不区分大小写。分区是以字段的形式在表结构中存在，通过describe table 命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。
6、STORED AS SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。TEXTFILE 是默认的文件格式，使用 D DELIMITED 子句来读取分隔的文件。
7、CLUSTERED BY INTO num_buckets BUCKETS
对于每一个表（table）或者分，Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。把表（或者分区）组织成桶（Bucket）有两个理由：
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如 JOIN 操作。对于 JOIN 操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行 JOIN 操作就可以，可以大大较少 JOIN 的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
内/外部表建表区别：
建内部表
create table student(Sno int,Sname string,Sex string,Sage int,Sdept string)
row format delimited fields terminated by ',';
建外部表
create external table student_ext(Sno int,Sname string,Sex string,Sage int,Sdept string)
row format delimited fields terminated by ','
location '/stu';------需要指定对应的数据路径
-----------------------------------------------------------
分隔符注意事项：
简单分隔符：
row format delimited fields terminated by ','
无需多言
复杂数据类型分隔符：
数组：
数据如下：
zhangsan beijing,shanghai,tianjin,hangzhou
wangwu shanghai,chengdu,wuhan,haerbin
create table complex_array(name string,work_locations array<string>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
集合：
数据：
1,zhangsan,唱歌:非常喜欢-跳舞:喜欢-游泳:一般般
2,lisi,打游戏:非常喜欢-篮球:不喜欢
create table t_map(id int,name string,hobby map<string,string>)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':' ;
-----------------------------------------------------------
分区注意事项：
- 分区的字段是虚拟字段，不能是表中的字段。但是在查询数据的时候，会将分区字段及其数据显示出来。
- 分区表的效果就是在表文件夹下按照分区的字段及字段值又建立了文件夹，这样做在查询时不需查找全表，提高了效率。
- 分区表在装载数据的时候不能使用hadoop的put命令，而是使用load data命令。
单分区表：
建表 create table day_table (id int, content string) partitioned by (dt string);
导入数据 load data local INPATH '/root/hivedata/dat_table.txt' INTO TABLE day_table partition(dt='2017-07-07');
对于local：有local表示在hiveservwer2服务启动的机器所在的linux系统中，没有表示在该机器的hdfs文件系统中。
查询 SELECT day_table.* FROM day_table WHERE day_table.dt = '2017-07-07';
多分区表：
建表 create table day_hour_table (id int, name string) partitioned by (dt string, hour string)row format delimited fields terminated by ',';多分区表在文件目录上的表现形式就是在表文件下多了多层文件。
导入数据 LOAD DATA local INPATH '/root/hivedata/dat_table.txt' INTO TABLE day_hour_table PARTITION(dt='2017-07-07', hour='08');
查询 SELECT day_hour_table.* FROM day_hour_table WHERE day_hour_table.dt = '2017-07-07' and hour =’08’;
总的说来partition就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。
-----------------------------------------------------------
分桶注意事项：
- 默认情况下hive不支持分桶，所以要开启分桶功能，并且还需指定分桶的个数。
set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;
- 分桶是要指定分桶的字段，且该字段必须是在表中的字段(与分区不同)。
- 分桶能够提高操作效率，只要针对的是多张表join查询时。
- 经过分桶之后文件会被分成多各子文件保存在表目录下。
- 分桶的规则也是按照分桶字段值和分桶个数进行取模运算。
- 分桶之后还会自动按照分桶字段进行排序。
对于分桶且排序：
Cluster by 字段具有按照该字段分桶，分桶之后，对于每一桶再按照改字段进行排序。
那么当我们要求分桶字段和排序字段不同时应该怎么办？
这个时候应该用distribute by 字段1进行分桶，用sort by 字段2进行排序。也就是说：当分桶和排序的字段相同时：
cluster by = distribute by + sort by
建表语句：
create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)
clustered by(Sno)
sorted by(Sno DESC)
into 4 buckets
row format delimited
fields terminated by ',';
导入数据：
对于分桶表导入数据不能够使用load data命令。原因是分桶实际上是设置了reducetask的数量，也就是说设置多少个reducetask就会分成多少个文件。底层会调用mr程序进行任务操作。但是load data方式实际上是借助hive帮hadoop(hadoop fs -put)去上传文件。load data 命令实际上实在hadoop put命令的基础上封装了一些创建文件夹的命令。
实际导入数据的方式需要借助第三张表。
- 创建第三张表或者是借助子查询结果
create table student (Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ‘,’;
- 给中间表装载数据
load data inpath ‘文件路径’ into table student;
- 创建分桶表(前提是开启了分桶功能和指定分桶数)
create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)
clustered by(Sno)
sorted by(Sno DESC)
into 4 buckets
row format delimited
fields terminated by ',';
- 借助insert+select给分桶表装载数据
insert table stu_buck select * from student cluster by(Sno);
7.1.2 修改表
增加分区：
ALTER TABLE table_name ADD PARTITION (dt='20170101') location
'/user/hadoop/warehouse/table_name/dt=20170101'; //一次添加一个分区
ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location
'/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location
'/path/to/us/part080809'; //一次添加多个分区
删除分区
ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2008-08-08');
ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2008-08-08', country='us');
修改分区
ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');
添加列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name STRING);
注： ADD 是代表新增一个字段，新增字段位置在所有列后面 (partition 列前 )REPLACE 则是表示替换表中所有字段。
修改列
有表test_change (a int, b int, c int);
ALTER TABLE test_change CHANGE a a1 INT; //修改 a 字段名
ALTER TABLE test_change CHANGE a a1 STRING AFTER b;
ALTER TABLE test_change CHANGE b b1 INT FIRST;
表重命名
ALTER TABLE table_name RENAME TO new_table_name
7.1.3 查询数据库或表的信息
show tables;
显示当前数据库所有表
show databases |schemas;
显示所有数据库
show partitions table_name;
显示表分区信息，不是分区表执行报错
show functions;
显示当前版本 hive 支持的所有方法
desc extended table_name;
查看表信息
desc formatted table_name;
查看表信息（格式化美观）
describe database database_name;
查看数据库相关信息
7.2 DML(数据库操作语言)操作
7.2.1 insert操作
用法一：insert+select用于为分桶表装载数据，具体见上面叙述。
用法二：用于多重插入。
有三张表：
create table source_table (id int, name string) row format delimited fields terminated by',';
create table test_insert1 (id int) row format delimited fields terminated by ',';
create table test_insert2 (name string) row format delimited fields terminated by ',';
from source_table
insert overwrite table test_insert1
insert overwrite table test_insert2
select name;
也就是从一张表中选出需要的字段赋给其他不同的表。
用法三：导出表数据到L指定目录
insert overwrite local directory '/home/hadoop/test'
select * from t_p;-----有local，导出到Linux系统
insert overwrite directory '/aaa/test'
select * from t_p;----没有local，导出到hdfs中
有local时：导出在开启hiveserver2的主机的linux系统中；
无local时：导出到hdfs中
用法四：动态分区
动态分区的前提：
set hive.exec.dynamic.partition=true; #是否开启动态分区功能，默认false关闭。
set hive.exec.dynamic.partition.mode=nonstrict; #动态分区的模式，默认strict，
表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。
需求：
将dynamic_partition_table中的数据按照时间(day)，插入到目标表d_p_t的相应分区中。
原始表：
create table dynamic_partition_table(day string,ip string)row format delimited fields terminated by ",";
load data local inpath '/root/hivedata/dynamic_partition_table.txt' into table dynamic_partition_table;
2015-05-10,ip1
2015-05-10,ip2
2015-06-14,ip3
2015-06-14,ip4
2015-06-15,ip1
2015-06-15,ip2
目标表：
create table d_p_t(ip string) partitioned by (month string,day string);
动态插入：
insert overwrite table d_p_t partition (month,day)
select ip,substr(day,1,7) as month,day
from dynamic_partition_table;
动态分区的字段时根据select语句中的字段顺序确定的，这一点一定要注意。
7.2.2 Hive join
Hive 中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持 LEFT
SEMI JOIN 和 CROSS JOIN，但这两种 JOIN 类型也可以用前面的代替。Hive 支持等值连接（a.id = b.id ），不支持非等值( (a.id>b.id) ) 的连接，因为非等值连接非常难转化到 map/reduce 任务。另外，Hive 支持多 2 个以上表之间的 join。写 join 查询时，需要注意：join时，每次 map/reduce 任务的逻辑：reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。
有两各文件数据
1,a 2,b 3,c 4,d 7,y 8,u
2,b b3,cc 7,yy9,pp
建表：
create table a(id int,name string)
row format delimited fields terminated by ',';
create table b(id int,name string)
row format delimited fields terminated by ',';
导入数据：
load data local inpath '/root/hivedata/a.txt' into table a;
load data local inpath '/root/hivedata/b.txt' into table b;
*** inner join
select * from a inner join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
+-------+---------+-------+---------+--+
***left join
select * from a left join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
+-------+---------+-------+---------+--+
***right join
select * from a right join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+
select * from a full outer join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+
***hive中的特别join
select * from a left semi join b on a.id = b.id;
+-------+---------+--+
| a.id | a.name |
+-------+---------+--+
| 2 | b |
| 3 | c |
| 7 | y |
+-------+---------+--+
相当于
select a.id,a.name from a where a.id in (select b.id from b); 在hive中效率极低
select a.id,a.name from a join b on (a.id = b.id);
cross join（##慎用）
返回两个表的笛卡尔积结果，不需要指定关联键。
select a.*,b.* from a cross join b;
八 Hive参数配置
8.1 Hive参数配置方式
Hive 参数大全：
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
开发 Hive 应用时，不可避免地需要设定 Hive 的参数。设定 Hive 的参数可以调优 HQL 代码的执行效率，或帮助定位问题。然而实践中经常遇到的一个问题是，为什么设定的参数没有起作用？这通常是错误的设定方式导致的。
对于一般参数，有以下三种设定方式：
配置文件 （全局有效）
命令行参数 （对 hive 启动实例有效）
参数声明 （对 hive 的连接 session 有效）
配置文件
用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml
默认配置文件：$HIVE_CONF_DIR/hive-default.xml
用户自定义配置会覆盖默认配置。
另外，Hive 也会读入 Hadoop 的配置，因为 Hive 是作为 Hadoop 的客户端启动的，Hive 的配置会覆盖 Hadoop 的配置。配置文件的设定对本机启动的所有 Hive 进程都有效。
命令行参数
启动 Hive（客户端或 Server 方式）时，可以在命令行添加-hiveconf 来设定参数
例如：bin/hive -hiveconf hive.root.logger=INFO,console
设定对本次启动的 Session（对于 Server 方式启动，则是所有请求的 Sessions）有效。
参数声明
可以在 HQL 中使用 SET 关键字设定参数，这一设定的作用域也是 session 级的。
比如：
set hive.exec.reducers.bytes.per.reducer=<number> 每个 reduce task 的平均负载数据量
set hive.exec.reducers.max=<number> 设置 reduce task 数量的上限
set mapreduce.job.reduces=<number> 指定固定的 reduce task 数量
但是，这个参数在必要时<业务逻辑决定只能用一个 reduce task> hive 会忽略上述三种设定方式的优先级依次递增。即参数声明覆盖命令行参数，命令行参数覆盖配置文件设定。注意某些系统级的参数，例如 log4j 相关的设定，必须用前两种方式设定，因为那些参数的读取在 Session 建立以前已经完成了。
8.2 快速测试内置函数的结果
内置函数
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
测试各种内置函数的快捷方法：
创建一个 dual 表
create table dual(id string);
load 一个文件（只有一行内容：内容为一个空格）到 dual 表
select substr('angelababy',2,3) from dual;
8.3自定义UDF函数
8.3.1 Java代码实现
新建 JAVA maven 项目
添加 hive-exec-1.2.1.jar 和 hadoop-common-2.7.4.jar 依赖
1、写一个 java 类，继承 UDF，并重载 evaluate方法，evaluate方法中就是这个自定义函数的功能。
2、先打成 jar 包上传到Linux，在利用hive命令上传。
hive>add jar jar包在Linux中的路径;
4、创建临时函数与开发好的 java class 关联
Hive>create temporary function tolowercase as ' evaluate方法所在类的全路径';
5、即可在 hql 中使用自定义的函数 tolowercase
Select tolowercase(name),age from t_test;
8.3.2 Transform实现
Hive 的 TRANSFORM 关键字 提供了在 SQL 中调用自写脚本的功能
适合实现 Hive 中没有的功能又不想写 UDF 的情况
使用示例 1：下面这句 sql 就是借用了 weekday_mapper.py 对数据进行了处理.
add FILE weekday_mapper.py;
INSERT OVERWRITE TABLE u_data_new
SELECT
TRANSFORM (movieid , rate, timestring,uid)
USING 'python weekday_mapper.py'
AS (movieid, rating, weekday,userid)
FROM t_rating;
其中 weekday_mapper.py 内容如下
#!/bin/python
import sys
import datetime
for line in sys.stdin:
line = line.strip()
movieid, rating, unixtime,userid = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movieid, rating, str(weekday),userid])
8.4 特殊分割字符
hive读取数据的机制：
首先用 InputFormat< 默认是：org.apache.hadoop.mapred.TextInputFormat >的一个具体实现类读入文件数据，返回一条一条的记录（可以是行，或者是你逻辑中的“行”）然后利用 SerDe（默认：org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe，就是delimited）的一个具体实现类，对上面返回的一条一条的记录进行字段切割 。Hive 对文件中字段的分隔符默认情况下只支持单字节分隔符，如果数据文件中的分隔符是多字符的，如下所示：
01||zhangsan
02||lisi
可用使用 RegexSerDe 通过正则表达式来抽取字段
drop table t_bi_reg;
create table t_bi_reg(id string,name string)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
'input.regex'='(.*)\\|\\|(.*)',
'output.format.string'='%1$s %2$s'
)
stored as textfile;
hive>load data local inpath '/root/hivedata/bi.dat' into table t_bi_reg;
hive>select * from t_bi_reg;
其中：
input.regex：输入的正则表达式
表示 || 左右两边任意字符被抽取为一个字段
output.format.string：输出的正则表达式
%1$s %2$s 则分别表示表中的第一个字段、第二个地段
注意事项：
a、使用 RegexSerDe 类时，所有的字段必须为 string
b、input.regex 里面，以一个匹配组，表示一个字段
856
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
