Hadoop学习笔记03_Hive练习
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';
