PowerPoint 簡報

Size: px

Start display at page:

Download "PowerPoint 簡報"

玮乔
7 years ago
Views:

1 Apache Hive 陳威宇

2 Agenda What is Apache Hive How to Setup Tutorial Examples

storage data warehouse (DW) is a database specific for analysis and

3 Hive Introduction Hive is a data warehouse infrastructure built on top of hadoop Compile SQL queries as MapReduce jobs and run on hadoop HDFS for storage data warehouse (DW) is a database specific for analysis and reporting purposes HiveQL Hive web cli JDBC ODBC Thrift Server Driver MetaStore

能運算的事特色 : 可擴展可自訂函數容錯限制 : 執行時間較久資料結構固定無法修改 See : http://www.

4 Hadoop 也有 RDB 可以用 : Hive Hive = Hadoop 的 RDB 將結構化的資料檔案映射為資料庫表提供 SQL 查詢功能 ( 轉譯 SQL 語法成 MapReduce 程式 ) 適合 : 有 SQL 基礎的使用者且基本 SQL 能運算的事特色 : 可擴展可自訂函數容錯限制 : 執行時間較久資料結構固定無法修改 See : 4

5 Hive Performance See :

6 Hive 架構提供了.. 介面 CLI WebUI API JDBC and ODBC Thrift Server (hiveserver) 使遠端 Client 可用 API 執行 HiveQL Metastore DB, table, partition figure Source : 6

7 大象遇到蜂 ( setup ) 解壓縮修改 ~/.bashrc 修改 conf/hive-env.sh 設定 metastore on hdfs 啟動 pig shell export JAVA_HOME=/usr/lib/jvm/java-7-oracle export HADOOP_HOME=/home/hadoop/hadoop export HADOOP_CONF_DIR=$HADOOP_HOME/etc/hadoop export HIVE_HOME=/home/hadoop/hive export PATH=$PATH:$HIVE_HOME/bin export HADOOP_HOME=/home/hadoop/hadoop export HIVE_CONF_DIR=/home/hadoop/hive/conf hadoop fs -mkdir -p /user/hive/warehouse hadoop fs -chmod g+w /tmp hadoop fs -chmod g+w /user/hive/warehouse $ hive hive> Ps : 若沒有要用 mysql 當 metastore_db, 可以不用改 hive-site.xml

y>10000 hive> insert table B select * from A where y>10000 figure

8 蜂也會的程式設計 $ hive hive> create table A(x int, y int, z int) hive> load data local inpath file1 into table A; hive> select * from A where y>10000 hive> insert table B select * from A where y>10000 figure Source : 8

9 Hive 和 SQL 比較 Hive 查詢語法 HQL SQL 儲存體 HDFS RDMS Raw Device or Local FS 運算方法 MapReduce Excutor 延遲非常高低處理數據規模大小修改資料 NO YES 索引 Index, Bigmap index 複雜健全的索引機制 Source : 9

10 練習一 : 場景 : 組織內有統一格式的出勤紀錄資料表, 分散在全台各縣市的各個部門的資料庫中老闆要我蒐集全台的資料統計所有員工的平均工時 DB 內的 table 都轉成 csv 檔, 並且餵進去 Hadoop 的 HDFS 了,.. 問題 : 雖然我知道 PIG 可以降低 MapReduce 的門檻, 但我還是習慣 SQL 語法來實作, 如果有一台超大又免費的 DB 就好了解法 : 編列經費買台高效伺服器再裝個大容量的 sql server 使用 Hive 10

用 Hive 整形後 nm dp Id id dt hr 劉北 A1 A1 7/7 13 李中 B1 A1 7/8 12 王中 B2 A1 7/9 4 HiveQL > create table A (nm String, dp String, id String); > create table B (id String, dt Date, hr int); > create table

11 用 Hive 整形後 nm dp Id id dt hr 劉北 A1 A1 7/7 13 李中 B1 A1 7/8 12 王中 B2 A1 7/9 4 HiveQL > create table A (nm String, dp String, id String); > create table B (id String, dt Date, hr int); > create table final (dp String, id String, nm String, avg float); > load data inpath file1 into table A; > load data inpath file2 into table B; > Insert table final select a.id, collect_set(a.dp), collect_set(a.nm), avg(b.hr) from a,b where b.hr > 8 and b.id = a.id group by a.id; 北 A1 劉 12.5 Tips : 沒有 local 模式 ; hive load 資料完後會將 input file 刪掉 ; 資料格式須嚴格檢查 ; 分欄位元僅 1 byte; create table & load data 建議用 tool 匯入 11 資料較不會錯

12 練習一 : 實作 cd ~ git clone cd ~/hadoop_example/hive/ex1 hadoop fs -put *.txt./ hive -f exc1.hive 練習 : 將 exc1.hive 每一行單獨執行, 並搭配 select * from table_name 來看結果, 如 : hive> select * from final Q : 資料是否有改進空間? Q : 如何改進?

更多 :table Create Table CREATE TABLE page_view( viewtime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING ) COMMENT 'This is the page view table' PARTITIONED BY(dt STRING, country

13 更多 :table Create Table CREATE TABLE page_view( viewtime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING ) COMMENT 'This is the page view table' PARTITIONED BY(dt STRING, country STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY, tblproperties ("skip.header.line.count"="1"); STORED AS TEXTFILE LOCATION '/user/data/staging/page_view'; Alter Table ALTER TABLE old_table_name REPLACE COLUMNS (col1 TYPE,...); Drop Table DROP TABLE pv_users;

14 更多 :data Import data LOAD DATA INPATH '/user/data/pv_ _us.txt' INTO TABLE page_view PARTITION(date=' ', country='us') Insert Table INSERT OVERWRITE TABLE xyz_com_page_views SELECT page_views.* FROM page_views WHERE page_views.date >= ' ' AND page_views.date <= ' ' AND page_views.referrer_url like '%xyz.com';

15 更多 :Query Query INSERT OVERWRITE TABLE user_active SELECT user.* FROM user WHERE user.active = 1; Partition Based Query SELECT page_views.* FROM page_views WHERE page_views.date >= ' ' AND page_views.date <= ' ' AND page_views.referrer_url like '%xyz.com';

16 更多 :aggregate Joins INSERT OVERWRITE TABLE pv_users SELECT pv.*, u.gender, u.age FROM user u JOIN page_view pv ON (pv.userid = u.id) WHERE pv.date = ' '; Aggregations INSERT OVERWRITE TABLE pv_gender_sum SELECT pv_users.gender, count (DISTINCT pv_users.userid), collect_set (pv_users.name) FROM pv_users GROUP BY pv_users.gender;

17 更多 :union UNION ALL INSERT OVERWRITE TABLE actions_users SELECT u.id, actions.date FROM ( SELECT av.uid AS uid FROM action_video av WHERE av.date = ' ' UNION ALL SELECT ac.uid AS uid FROM action_comment ac WHERE ac.date = ' ' ) actions JOIN users u ON(u.id = actions.uid); Tips: UNION : 兩個 SQL 語句所產生的欄位需要是同樣的資料種類 JOIN : 透過相同鍵值將兩 table 合併成大的

18 更多 : 動動腦 name sex age michael male 30 will male 35 shelley female 27 lucy female 57 steven male 30 sex_age.se x age name female 57 lucy male 35 will Solution SELECT employee.sex_age.sex, employee.sex_age.age, name FROM employee JOIN ( SELECT max(sex_age.age) as max_age, sex_age.sex as sex FROM employee GROUP BY sex_age.sex ) maxage ON employee.sex_age.age = maxage.max_age AND employee.sex_age.sex = maxage.sex; See :

練習二說明 : 使用實價登錄資訊, 算出最高與最低房價 Source : http://plvr.land.

19 練習二說明 : 使用實價登錄資訊, 算出最高與最低房價 Source : p&filename=lvr_landcsv.zip

20 練習二 cd ~/hadoop_example/hive/ex2./get_taiwan_landprice.sh hadoop fs -rmr./hive_2_input hadoop fs -put input./hive_2_input hive hive> Ex1 : 算出 top 5 最高房價文山區臺北市文山區汀州路四段 181~210 號文山區臺北市文山區新光路一段 65 巷 31~60 號文山區臺北市文山區政大二街 121~150 號 Ex2 : 算出 top 5 最高每坪價格文山區臺北市文山區汀州路四段 181~210 號文山區臺北市文山區新光路一段 65 巷 31~60 號文山區臺北市文山區政大二街 121~150 號

21 Reference Hive 官方範例說明 Tutorial Hive 練習

ebook46-23

ebook46-23 23 Access 2000 S Q L A c c e s s S Q L S Q L S Q L S E L E C T S Q L S Q L A c c e s s S Q L S Q L I N A N S I Jet SQL S Q L S Q L 23.1 Access 2000 SQL S Q L A c c e s s Jet SQL S Q L U N I O N V B A S