Apache Hive是提供了一种数据映射去读取文本数据，以及提供了类SQL的语句来执行MapReduce。
也就是一种更简化操作的MR。
之前的练习是跟着视频学的hive-1.2.2 而这几天自己练习 hive-2.3.2 才发现很多问题，需要增加一些配置。
以下是笔记：
# Hive是基于Hadoop的一个数据仓库工具，作为数据仓库工具，一般要具备2个方面的能力：一是数据的存储，另一个是ETL。
# Hive以HDFS作为存储，MapReduce作为数据分析。
# 在整个生态圈中，hive是为了简化MapReduce的开发难度。 MR实现复杂查询逻辑开发难度太大。主要用来做离线数据分析。
# 操作接口采用类SQL语法，叫HQL，提供快速开发能力；扩展功能方便。
# Hive 的主要三个特点：
# 可扩展 ：可自由扩展集群规模，一般不需要重启服务。
# 延展性 ：支持用户自定义函数，用户可根据需求实现自己的函数
# 容错 ：良好的容错性，节点出问题SQL仍可完成。
#在已经安装配置好JDK以及Hadoop的前提下，安装mysql, 再安装Hive-1.2.2 相对容易配置。
########################## Hive & derby #不推荐使用此方式
tar zxvf apache-hive-1.2.2-bin.tar.gz
mv apache-hive-1.2.2-bin hive-1.2.2
cd hive/bin
# 使用derby数据库的最大问题是：不同的路径启动，元数据无法共享。
# hadoop需要先正常启动运行
./hive # 进入shell
show databases;
create database abc;
use abc;
create table t_test(id int);
show tables;
############################ 使用 mysql 的方式 生产环境使用。
yum install mysql mysql-server mysql-devel # yum安装mysql及其依赖
cd /var/lib/mysql #这是mysql所在目录
cd /usr/share/mysql/ #相关文件
vim /etc/my.cnf #主要配置文件
# 更详细的安装与配置 https://www.cnblogs.com/chinesern/p/8440206.html
/etc/init.d/mysqld start #启动
mysql #mysql控制台
USE mysql;
update user set password=PASSWORD('newpassword') where user='root';
grant all PRIVILEGES on *.* to 'root'@'%' IDENTIFIED BY 'newpassword' with grant option;
FLUSH PRIVILEGES; --更改root密码，授权远程连接，生效。
service mysqld status # 检查mysqld是否运行
chkconfig mysqld on
mysql -u root -p #使用新密码连接
########## 配置hive
vim conf/hive-env.sh
export HADOOP_HOME=/usr/local/src/hadoop-2.7.5
vim conf/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>111111</value>
<description>passowrd to use against metastore database</description>
</property>
</configuration>
# 如果是hive-default.xml复制的，以及高版本的。修改太复杂，就sz到本机来操作。
# 安装mysql连接器驱动 到hive/lib中 https://dev.mysql.com/downloads/connector/
cd hive/lib
rz mysql-connector-java-5.1.46.jar
# 启动 bin/hive
show databases;
create database test;
describe database test; # 查看库信息
use test; ## 使用库
show tables; # 显示表
show partitions table_name; # 显示表分区信息
show functions; # 显示hive支持的所有方法
desc formatted table_name; # 查看表信息
### Hive的几种使用方式：
# 1. Hive 交互shell bin/hive
# 2. Hive JDBC服务(参考java jdbc连接mysql)
# 3. hive启动为一个服务器，对外提供服务 会启动一个进程 Runjar
bin/hiveserver2 #启动服务后，使用beeline连接。生产环境中最常用的方式。
bin/beeline -u jdbc:hive2://master:10000 -n root # 连接方式，默认端口10000
bin/beeline
! connect jdbc:hive2://master:10000 # 另一种连接方式
# 4. hive命令 # 不常用
hive -e 'sql'
bin/hive -e 'show databases'
### 以上4种方法，第3种方法使用时在命令行方式下最美观。
###################### Hive 数据映射
# Hive本身没有存储功能，所有的数据都存在HDFS中。
# 1. 采用cp文件到数据表文件位置的方法
hdfs dfs -put 1.txt /hivedata
hive:
show databases; use test;
create table t_t1(id int, name string, age int);
# http://master:50070 下，会有对应的文件/user/hive/warehouse/test.db/t_t1
hdfs dfs -cp /hivedata/1.txt /user/hive/warehouse/test.db/t_t1
create table t_t2(id int, name string, age int) row format delimited fields terminated by ','; # 带有分隔符创建表
hdfs dfs -cp /hivedata/1.txt /user/hive/warehouse/test.db/t_t2
select * from t_t2;
select count(*) from t_t2; # 此句将执行mr程序 http://master:8088 将可以看到进程
# 2. 采用load 文件数据到 数据表的方法 ，不必先上传文件
create table t_t3(id int, name string, age int) row format delimited fields terminated by '\t'; # 带有\t分隔符创建表
load data local inpath '/test/data2' into table t_t3; # 载入也有同样\t分隔符的数据
######################## 复杂类型的数据表指定分隔符
# 建表的时候一定要根据结构化数据文件的分隔符类型 指定分隔符
# 建表的字段个数和字段类型 要跟结构化数据中的个数类型一致
# 分隔符一般使用内置的来指定 ROW FORMAT DELIMITED 分割字段还是分割集合，等。
#### 示例1.txt 数据：
Alex BJ,JS,SH,HZ
Jerry SH,CD,WH,HEB
# 建表语句：
use test;
create table complex_array(name string, city array<string>) ROW FORMAT delimited fields terminated by '\t' collection items terminated by ',';
# 上传文件
hdfs dfs -put 1.txt /user/hive/warehouse/test.db/complex_array
# 查看数据 select * from complex_array;
+---------------------+-------------------------+
| complex_array.name | complex_array.city |
+---------------------+-------------------------+
| Alex | ["BJ","JS","SH","HZ"] |
| Jerry | ["SH","CD","WH","HEB"] |
+---------------------+-------------------------+
#### 示例2.txt 数据：
1,Alex,唱歌:非常喜欢-跳舞:喜欢-游戏:一般般
2,Melin,自行车:非常喜欢-足球:不喜欢
# 建表语句
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 ':';
# 上传文件
hdfs dfs -put 2.txt /user/hive/warehouse/test.db/t_map
# 查看数据 select * from t_map
+-----------+-------------+---------------------------------------------------+
| t_map.id | t_map.name | t_map.hobby |
+-----------+-------------+---------------------------------------------------+
| 1 | Alex | {"唱歌":"非常喜欢","跳舞":"喜欢","游戏":"一般般"} |
| 2 | Melin | {"自行车":"非常喜欢","足球":"不喜欢"} |
+-----------+-------------+---------------------------------------------------+
############################ 不指定分隔符时的默认分隔符
# 默认分隔符是'\001' 在vi下,Ctrl+v再Ctrl+a即可输入这个符号
# 示例数据 3.txt
1^A砖家^A28
2^A明月^A33
# 建表语句
create table t_t5(id int, name string, age int);
# 上传文件
hdfs dfs -put 3.txt /user/hive/warehouse/test.db/t_t5
# 查看数据 可以看到映射成功.
以下是Hive-2.3.2的部分，有很多不一样：
# 试验了 hive2.3.2版本，修改以下6步就比较麻烦。参考了这位仁兄https://www.cnblogs.com/garfieldcgf/p/8134452.html
# 1. vi /etc/profile 加:
export HIVE_HOME=/home/sri_udap/app/apache-hive-2.3.2-bin
export PATH=$PATH:$HIVE_HOME/bin
# 2. 生效: source /etc/profile
# 3. # vim /usr/local/src/hadoop-2.7.5/etc/hadoop/hadoop-env.sh 加：
for f in $HADOOP_HOME/hadoop-*.jar; do
CLASSPATH=${CLASSPATH}:$f
done
for f in $HADOOP_HOME/lib/*.jar; do
CLASSPATH=${CLASSPATH}:$f
done
for f in $HIVE_HOME/lib/*.jar; do
CLASSPATH=${CLASSPATH}:$f
done
# 4. 修改文件hive-env.sh 加：
export HIVE_CONF_DIR=/usr/local/src/hive2.3.2/conf
export HIVE_AUX_JARS_PATH=/usr/local/src/hive2.3.2/lib
export HADOOP_HOME=/usr/local/src/hadoop-2.7.5
# 5. 除上同上面hive-1.2.2的配置外，还：替换了所有的 ${system:java.io.tmpdir} 为/usr/local/src/hive2.3.2/tmp 去掉了所有 ${system:user.name} 中的system:
# 6. 初始化：
./schematool -initSchema -dbType mysql
# 如果报错 Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'.
# 解决办法：vim /etc/my.cnf添加参数：
inlog_format = ROW
innodb_locks_unsafe_for_binlog = 1
# 启动 bin/hive
# hive2.3多出了这句： Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
######## 而hadoop2.7.5之上的hive2.3.2的beeline 连接还需要增加设置：
# 修改hadoop的core-site.xml增加如下配置. 保存并重启hadoop
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
# 主要原因是hadoop引入了一个安全伪装机制，使得hadoop 不允许上层系统直接将实际用户传递到hadoop层，
# 而是将实际用户传递给一个超级代理，由此代理在hadoop上执行操作，避免任意客户端随意操作hadoop
以下是分区、分桶、动态插入、多重插入：
########################################################################
# 分区表(Partitioned by) 辅助查询,缩小查询范围,
# 加快数据的检索速度和对数据按照一定的规格和条件进行管理.
# 单分区: 在表文件夹下只有一级文件夹.
# 多分区: 表文件夹下有多级文件夹.
########################################################################
# row format delimited(指定分隔符) 创建分区表. 单分区表
create table t_user(id int, name string) partitioned by(country string) row format delimited fields terminated by ',';
# 示例数据 4.txt
1,Alex
2,tom
3,jerry
# 上传数据不起作用. 所以载入数据. 需要注意本地文件必须在提供hive服务的机器上,不能是cli 否则会报路径错误.
# 原因是 local 表示的路径指的是server的,而不是beeline的.
load data local inpath '/usr/local/src/hive2.3.2/4.txt' into table t_user partition(country='USA');
# 上传另一批数据 5.txt 使用不同的分区
load data local inpath '/usr/local/src/hive2.3.2/5.txt' into table t_user partition(country='China');
# 查看数据: select * from t_user; 可以看到分区字段也出现在表中.
# 并且hdfs中以不同目录country=China 和 country=USA 来分别存储数据
+------------+--------------+-----------------+
| t_user.id | t_user.name | t_user.country |
+------------+--------------+-----------------+
| 4 | zhangsan | China |
| 5 | lisi | China |
| 6 | wangwu | China |
| 1 | Alex | USA |
| 2 | tom | USA |
| 3 | jerry | USA |
+------------+--------------+-----------------+
## 分区表字段不能在表中已存在;
## 分区字段是一个虚拟的字段,不存放任何数据;
## 分区字段的数据来自于装载分区表数据的时候指定的.
# 按分区查看数据 select * from t_user where country ='China';
################## 双分区表:
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 '/usr/local/src/hive2.3.2/4.txt' into table day_hour_table partition(dt='20180521',hour='09');
load data local inpath '/usr/local/src/hive2.3.2/5.txt' into table day_hour_table partition(dt='20180521',hour='10');
load data local inpath '/usr/local/src/hive2.3.2/6.txt' into table day_hour_table partition(dt='20180522',hour='09');
load data local inpath '/usr/local/src/hive2.3.2/7.txt' into table day_hour_table partition(dt='20180522',hour='10');
# 查看数据时, select * from day_hour_table; 将显示所有分区. 且HDFS上的目录将呈树形结构
+--------------------+----------------------+--------------------+----------------------+
| day_hour_table.id | day_hour_table.name | day_hour_table.dt | day_hour_table.hour |
+--------------------+----------------------+--------------------+----------------------+
| 1 | Alex | 20180521 | 09 |
| 2 | tom | 20180521 | 09 |
| 3 | jerry | 20180521 | 09 |
| 4 | zhangsan | 20180521 | 10 |
| 5 | lisi | 20180521 | 10 |
| 6 | wangwu | 20180521 | 10 |
| 1 | laowang | 20180522 | 09 |
| 2 | xiaoma | 20180522 | 09 |
| 3 | shanshan | 20180522 | 09 |
| 1 | Allen | 20180522 | 10 |
| 2 | space | 20180522 | 10 |
| 3 | LV | 20180522 | 10 |
+--------------------+----------------------+--------------------+----------------------+
## 目的是把数据划分得更细，减少了查询时全表扫描的成本，按指定分区查询即可。
## select * from day_hour_table where hour='09';
########################################################################
# 分桶
# 分桶表(cluster by into num buckets)
########################################################################
# 需要先指定开启分桶
set hive.enforce.bucketing = true;
set mapreduce.job.reduces = 4;
# 创建桶表
create table stu_buck(sno int,sname string,sgender string,sage int,sdept string) clustered by (sno) into 4 buckets row format delimited fields terminated by ',';
# 示例数据
18001,李三,男,29,HR
18002,李四,男,30,AD
18003,李五,女,22,CV
18004,经式,男,12,AD
18005,张春,男,32,CV
18006,工仍,男,32,CV
18007,左欠春,男,42,HR
18008,左在上,女,52,AD
18009,昌要在,女,17,AD
18010,琦有春,男,27,CV
18011,中左鉁,女,37,AD
18012,戒左脸,女,47,AD
18013,高科技,女,57,AD
18014,填权,女,66,CV
18015,框框,女,65,AD
18016,奇巧,女,64,HR
# 分桶表使用load data方式没有效果,原在在于本质上是执行hdfs dfs -put , 所以需要先创建普通临时表
create table student(sno int,sname string,sgender string,sage int,sdept string) row format delimited fields terminated by ',';
# 载入数据到临时表
load data local inpath '/usr/local/src/hive2.3.2/students.txt' into table student;
select * from student cluster by(sno); # 先试试看分桶效果
# 临时表数据经过分桶后进入 stu_buck 对应MR当中的partitioner
insert overwrite table stu_buck select * from student cluster by (sno); # overwrite表示覆盖
## 运行过程中,发现 hive2.3.2已经提示MR的支持已经太旧了,建议使用spark. MR运行完毕,得到结果
+---------------+-----------------+-------------------+----------------+-----------------+
| stu_buck.sno | stu_buck.sname | stu_buck.sgender | stu_buck.sage | stu_buck.sdept |
+---------------+-----------------+-------------------+----------------+-----------------+
| 18004 | 经式 | 男 | 12 | AD |
| 18008 | 左在上 | 女 | 52 | AD |
| 18012 | 戒左脸 | 女 | 47 | AD |
| 18016 | 奇巧 | 女 | 64 | HR |
| 18001 | 李三 | 男 | 29 | HR |
| 18005 | 张春 | 男 | 32 | CV |
| 18009 | 昌要在 | 女 | 17 | AD |
| 18013 | 高科技 | 女 | 57 | AD |
| 18002 | 李四 | 男 | 30 | AD |
| 18006 | 工仍 | 男 | 32 | CV |
| 18010 | 琦有春 | 男 | 27 | CV |
| 18014 | 填权 | 女 | 66 | CV |
| 18003 | 李五 | 女 | 22 | CV |
| 18007 | 左欠春 | 男 | 42 | HR |
| 18011 | 中左鉁 | 女 | 37 | AD |
| 18015 | 框框 | 女 | 65 | AD |
+---------------+-----------------+-------------------+----------------+-----------------+
## 分桶表的好处是: 把结构化数据分成更细的部分,更多地用于join查询提高效率, 只需要把join的字段在各自表当中进行分桶操作即可.
########################################################################
# 创建外部表
# 即直接映射warehouse外部的数据.
########################################################################
# 上传外部数据 hdfs dfs -put stu_ext /data/
# 创建外部表. 注意: 映射的目录下不能有别的不相干的文件和目录,否则报错.
create external table stu_ext (sno int,sname string,sgender string,sage int,sdept string) row format delimited fields terminated by ',' location '/data';
# 创建相同结构的没有数据的空表.
create table t_t2 like stu_ext;
########################################################################
###### 修改表
########################################################################
# 增加分区:
ALTER TABLE table_name ADD PARTITION (dt='2018') location '/user/hive/warehouse/test.db/table_name/dt=2018'; # 一个分区
ALTER TABLE table_name ADD PARTITION (dt='2018',cty='us') location '/user/hive/warehouse/test.db/table_name/dt=2018' PARTITION (dt='2017',cty='us') location '/user/hive/warehouse/test.db/table_name/dt=2017'; # 多个分区
# 删除分区:
ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2018'); # 一个分区
ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2018',cty='us'); # 多个分区
# 修改分区:
ALTER TABLE table_name PARTITION (dt='2018') RENAME TO PARTITION (dt='201805');
# 添加列: ADD新增一个字段,且在所有列最后,在partition前面. REPLACE则是替换表中所有字段.
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name string);
# 修改列
ALTER TABLE table_name CHANGE sno sid int; # 改字段名sno为sid
ALTER TABLE table_name CHANGE sno sid string AFTER sname; # 改字段名sno为sid 且类型为string,且放在sname列后
ALTER TABLE table_name CHANGE sno sid string FIRST; # 改字段名sno为sid 且类型为string,且放第一列
# 表重命名
ALTER TABLE table_name RENAME TO new_table_name;
########################################################################
# 多重插入:
########################################################################
# 拿前面用过的 student 表为源表, 练习多重插入.
# 创建两个测试目标表
create table tar1(sno int,sname string,sgender string) row format delimited fields terminated by ',';
create table tar2(sno int,sage int,sdept string) row format delimited fields terminated by ',';
# 开始插入, 执行MR程序
from student
insert overwrite table tar1
select sno,sname,sgender
insert overwrite table tar2
select sno,sage,sdept;
# 执行结束,查看两个目标表,即可看到各自的数据.
########################################################################
# 动态分区插入:
########################################################################
set hive.exec.dynamic.partition=true; #开启动态分区功能
set hive.exec.dynamic.partition.mode=nonstrict; # 默认strict,即必须指定至少一个静态分区; nonstrict 允许所有分区字段都可以使用动态分区.
# 还是以前面用过的student表为源表, 动态分区插入另一个表中.
# 目标表
create table tar3(sno int,sname string,sage int) partitioned by(gender string,dept string);
# 动态插入 等待MR执行完毕.
insert overwrite table tar3 partition(gender,dept)
select sno, sname,sage, sgender,sdept
from student;
## 此过程遇到了错误。提示gender字段中文不能识别。所以又重新编辑了students.txt文件，替换了性别字段为英文。
## 然后重新载入数据 且使用了覆盖。
load data local inpath '/usr/local/src/hive2.3.2/students.txt' overwrite into table dynamic_partition_table;
# 查看结果：
+-----------+-------------+------------+--------------+------------+
| tar3.sno | tar3.sname | tar3.sage | tar3.gender | tar3.dept |
+-----------+-------------+------------+--------------+------------+
| 18008 | 左在上 | 52 | female | AD |
| 18009 | 昌要在 | 17 | female | AD |
| 18011 | 中左鉁 | 37 | female | AD |
| 18012 | 戒左脸 | 47 | female | AD |
| 18013 | 高科技 | 57 | female | AD |
| 18015 | 框框 | 65 | female | AD |
| 18003 | 李五 | 22 | female | CV |
| 18014 | 填权 | 66 | female | CV |
| 18016 | 奇巧 | 64 | female | HR |
| 18002 | 李四 | 30 | male | AD |
| 18004 | 经式 | 12 | male | AD |
| 18005 | 张春 | 32 | male | CV |
| 18006 | 工仍 | 32 | male | CV |
| 18010 | 琦有春 | 27 | male | CV |
| 18001 | 李三 | 29 | male | HR |
| 18007 | 左欠春 | 42 | male | HR |
+-----------+-------------+------------+--------------+------------+
2018年6月25日 更新：
#### 另一个例子(源数据是日期和IP地址):
2018-05-21,192.168.123.11
2018-05-21,192.168.123.12
2018-05-22,192.168.123.13
2018-05-22,192.168.123.14
2018-05-23,192.168.123.11
2018-05-23,192.168.123.12
# 数据装载
create table dynamic_partition_table(day string,ip string) row format delimited fields terminated by ",";
load data local inpath '/root/dynamic_partition_table.txt' into table dynamic_partition_table;
# 建目标表
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;
# 结果：
+-----------------+--------------+-------------+
| d_p_t.ip | d_p_t.month | d_p_t.day |
+-----------------+--------------+-------------+
| 192.168.123.11 | 2018-05 | 2018-05-21 |
| 192.168.123.12 | 2018-05 | 2018-05-21 |
| 192.168.123.13 | 2018-05 | 2018-05-22 |
| 192.168.123.14 | 2018-05 | 2018-05-22 |
| 192.168.123.11 | 2018-05 | 2018-05-23 |
| 192.168.123.12 | 2018-05 | 2018-05-23 |
+-----------------+--------------+-------------+
########################################################################
# 查询结果导出到文件系统:
########################################################################
# 将查询结果保存到指定目录（本地或HDFS）
# 注意：overwrite 会清空目标目录下的所有文件 默认分隔符是'\001'
insert overwrite local directory '/root/abc'
select * from tar3;
# 写入hdfs
insert overwrite directory '/test/abc'
select * from d_p_t;
########################################################################
# select : cluster distribute sort order by
########################################################################
# 需要先指定开启分桶
set hive.enforce.bucketing = true;
set mapreduce.job.reduces = 3;
set mapreduce.job.reduces; # 查看当前hive进程分桶个数
select * from student cluster by(sno); # 分桶查询
# 分桶结果写入本地目录
insert overwrite local directory '/root/abc'
select * from student cluster by(sno);
# cluster by 和 sort by不能同时用
# 对某列分桶(distribute)的同时，根据另一列进行排序(sort)
insert overwrite local directory '/root/abc'
select * from student distribute by(sno) sort by(sage asc);
# cluster(分桶且排序，必须一样) == distribute(分) + sort(排序) (可以不一样)
# 全局排序 order by 最终的结果只有一个reduce
select * from student order by(sage);
########################################################################
# join 连接 不支持非等值运算条件
########################################################################
#准备数据并载入，成绩表字段：sno 和 course，可以与前面的 student 表连接
18001,80
18002,93
18003,77
18006,88
18007,66
18018,55
18011,59
18012,47
18013,82
18014,99
18017,62
create table course(sno int,course int) row format delimited fields terminated by ",";
load data local inpath '/usr/local/src/hive2.3.2/course' into table course;
# 小技巧：如果不想经历MR程序的慢速，可以使用本地模式进行测试
set hive.exec.mode.local.auto=true;
# inner join 查询
select * from student as a inner join course as b on a.sno=b.sno;
# left join
select * from student as a left join course as b on a.sno=b.sno;
# right join
select * from student as a right join course as b on a.sno=b.sno;
# full outer join
select * from student as a full outer join course as b on a.sno=b.sno;
### hive中特别的join
# left semi join 相当于 inner join
select * from student as a left semi join course as b on a.sno=b.sno;
# cross join (慎用，笛卡尔积)
########################################### hive的参数：
# 配置文件： 全局有效 (低优先级)
# 命令行参数： 对hive启动实例有效 (高优先级)
# 参数声明： 对hive的连接session有效 (最高优先级)
###### hive的常用运算和函数
# 支持几乎和传统sql一样的运算和函数
# dual 测试表 测试各种内置函数的快捷方法：
create table dual(id string);
# load 一个文件（只有一行内容，内容为一个空格）到 dual 表
load data local inpath '/usr/local/src/hive2.3.2/dual' into table dual;
# 测试
select substr('awsome',2,3) from dual; # wso
############################################## 自定义函数开发 Transform
# java maven 项目
####################################### 特殊分隔符处理
# 数据：
1||alex
2||jerry
3||karl
# 建表 利用了正则表达式
create table t_bi_reg(id int, 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;
load data local inpath '/usr/local/src/hive2.3.2/shuang' into table t_bi_reg;
############### group by
select sgender, max(sage) from student group by sgender;
+----------+------+
| sgender | _c1 |
+----------+------+
| female | 66 |
| male | 42 |
+----------+------+
## 示例数据：t_access
192.168.33.3,http://www.edu360.cn/stu,2017-08-04 15:30:20
192.168.33.3,http://www.edu360.cn/teach,2017-08-04 15:35:20
192.168.33.4,http://www.edu360.cn/stu,2017-08-04 15:30:20
192.168.33.4,http://www.edu360.cn/job,2017-08-04 16:30:20
192.168.33.5,http://www.edu360.cn/job,2017-08-04 15:40:20
192.168.33.3,http://www.edu360.cn/stu,2017-08-05 15:30:20
192.168.44.3,http://www.edu360.cn/teach,2017-08-05 15:35:20
192.168.33.44,http://www.edu360.cn/stu,2017-08-05 15:30:20
192.168.33.46,http://www.edu360.cn/job,2017-08-05 16:30:20
192.168.33.55,http://www.edu360.cn/job,2017-08-05 15:40:20
192.168.133.3,http://www.edu360.cn/register,2017-08-06 15:30:20
192.168.111.3,http://www.edu360.cn/register,2017-08-06 15:35:20
192.168.34.44,http://www.edu360.cn/pay,2017-08-06 15:30:20
192.168.33.46,http://www.edu360.cn/excersize,2017-08-06 16:30:20
192.168.33.55,http://www.edu360.cn/job,2017-08-06 15:40:20
192.168.33.46,http://www.edu360.cn/excersize,2017-08-06 16:30:20
192.168.33.25,http://www.edu360.cn/job,2017-08-06 15:40:20
192.168.33.36,http://www.edu360.cn/excersize,2017-08-06 16:30:20
192.168.33.55,http://www.edu360.cn/job,2017-08-06 15:40:20
# 建表
create table t_access(ip string,url string,access_time string) partitioned by (dt string) row format delimited fields terminated by ',';
# 装载
load data local inpath '/usr/local/src/hive2.3.2/t_access.txt' overwrite into table t_access partition(dt='2017-08-04');
# 求不同url访问次数且大于2次
select dt,url,count(1) as cnts, max(ip)
from t_access
where dt='2017-08-04'
group by dt,url having cnts>2;
+-------------+---------------------------------+-------+----------------+
| dt | url | cnts | _c3 |
+-------------+---------------------------------+-------+----------------+
| 2017-08-04 | http://www.edu360.cn/excersize | 3 | 192.168.33.46 |
| 2017-08-04 | http://www.edu360.cn/job | 7 | 192.168.33.55 |
| 2017-08-04 | http://www.edu360.cn/stu | 4 | 192.168.33.44 |
+-------------+---------------------------------+-------+----------------+
########################################### 复合类型示例：
# -- 数组
# -- 有如下数据：
战狼2,吴京:吴刚:龙母,2017-08-16
三生三世十里桃花,刘亦菲:痒痒,2017-08-20
普罗米修斯,苍老师:小泽老师:波多老师,2017-09-17
美女与野兽,吴刚:加藤鹰,2017-09-17
# -- 建表映射：
create table t_movie(movie_name string,actors array<string>,first_show date)
row format delimited fields terminated by ','
collection items terminated by ':';
+---------------------+----------------------------+---------------------+
| t_movie.movie_name | t_movie.actors | t_movie.first_show |
+---------------------+----------------------------+---------------------+
| 战狼2 | ["吴京","吴刚","龙母"] | 2017-08-16 |
| 三生三世十里桃花 | ["刘亦菲","痒痒"] | 2017-08-20 |
| 普罗米修斯 | ["苍老师","小泽老师","波多老师"] | 2017-09-17 |
| 美女与野兽 | ["吴刚","加藤鹰"] | 2017-09-17 |
+---------------------+------------------------+-------------------------+
# 装载
load data local inpath '/usr/local/src/hive2.3.2/movie' into table t_movie;
# 数组中的第一个
select movie_name,actors[0],first_show from t_movie;
# 数组中包含
select movie_name,actors,first_show from t_movie where array_contains(actors,'吴刚');
# 数组中元素个数
select movie_name,actors,first_show, size(actors) from t_movie;
select movie_name, size(actors) as actor_number, first_show from t_movie;
# -- map
# -- 数据：
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26
# -- 建表映射上述数据
create table family(id int,name string,family_members map<string,string>,age int)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';
# -- 导入数据
load data local inpath '/usr/local/src/hive2.3.2/fm.dat' into table family;
+------------+--------------+----------------------------------------------------+-------------+
| family.id | family.name | family.family_members | family.age |
+------------+--------------+----------------------------------------------------+-------------+
| 1 | zhangsan | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28 |
| 2 | lisi | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22 |
| 3 | wangwu | {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"} | 29 |
| 4 | mayun | {"father":"mayongzhen","mother":"angelababy"} | 26 |
+------------+--------------+----------------------------------------------------+-------------+
# -- 查出每个人的 爸爸、姐妹
select id,name,family_members["father"] as father,family_members["sister"] as sister,age from family ;
select id,name,family_members["father"] as father,family_members["sister"] as sister,age from family where family_members["sister"] is not null;
###### 表生成函数 explode
# 表数据：
1,zhangsan,数学:语文:英语:生物
2,lisi,数学:语文
3,wangwu,化学:计算机:java编程
# 建表
create table xuanxiu(uid string,name string,kc array<string>) row format delimited fields terminated by ',' collection items terminated by ':';
# 装载
load data local inpath '/usr/local/src/hive2.3.2/text' into table xuanxiu;
# 求所有课程名称 去重
select distinct sub from(select explode(kc) as sub from xuanxiu) tmp;
###### 自定义函数 json
# 有如下json数据：rating.json
{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}
{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}
{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}
# -- 建表映射上述数据
create table ratingjson(json string);
load data local inpath '/usr/local/src/hive2.3.2/rating.json' into table ratingjson;
select * from ratingjson limit 10;
+---------------------------------------------------------------+
| ratingjson.json |
+---------------------------------------------------------------+
| {"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"} |
| {"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"} |
| {"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"} |
| {"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"} |
| {"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"} |
| {"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"} |
| {"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"} |
| {"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"} |
| {"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"} |
| {"movie":"919","rate":"4","timeStamp":"978301368","uid":"1"} |
+---------------------------------------------------------------+
# 想要解析 json 格式，得到如下数据：
1193,5,978300760,1
661,3,978302109,1
914,3,978301968,1
3408,4,978300275,1
# 思路：如果能够定义一个json解析函数，则很方便了
create table t_rate
as
select myjson(json,1) as movie,cast(myjson(json,2) as int) as rate,myjson(json,3) as ts,myjson(json,4) as uid from t_ratingjson;
# 解决：
# hive中如何定义自己的函数：
# 1、先写一个java类（extends UDF,重载方法public C evaluate(A a,B b)），实现你所想要的函数的功能（传入一个json字符串和一个脚标，返回一个值）
# 2、将java程序打成jar包，上传到hive所在的机器
# 3、在hive命令行中将jar包添加到classpath ：
hive> add jar /usr/local/src/hive2.3.2/lib/MyJsonParser.jar;
# 4、在hive命令中用命令创建一个函数叫做myjson，关联你所写的这个java类
hive> create temporary function myjson as 'cn.lshm.hive.udf.MyJsonParser';
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
