當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Hadoop学习笔记03_Hive练习

發布時間：2023/12/20 编程问答 21 豆豆

生活随笔收集整理的這篇文章主要介紹了 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 # 進入shellshow 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.5vim 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/hiveshow 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 # 連接方式，默認端口10000bin/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_t1create 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_t2select * 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 donefor f in $HADOOP_HOME/lib/*.jar; do CLASSPATH=${CLASSPATH}:$f donefor 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,62create 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';

轉載于:https://www.cnblogs.com/frx9527/p/hive.html

總結

以上是生活随笔為你收集整理的Hadoop学习笔记03_Hive练习的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：【转载】Python操作Excel的读取
下一篇： JVM内存管理机制和垃圾回收机制