Skip to content

antony@notes:~/data-platform$ cat "Hive-資料倉儲工具.md"

Hive 資料倉儲工具

2023-02-19· data-platform ·資料科技平台

Hive 資料倉儲工具

:::spoiler 目錄 ( 點我可展開 )

[TOC]

:::

現在的資料科技世代

Schema on Read

  • 資料庫本身沒有定義schema結構，寫入資料時不受schema限制，只有在讀取資料(Raw Data)時，由使用者做Code to schema的動作

補充傳統ＩＴ世代：Schema on Write 先產生Database及Table，才能做後續的資料儲存及分析

認識Hive

Apache Hive 是Hadoop 生態系統中的第一個 SQL 框架。Facebook 的工程師在2007年介紹了Hive，並在2008年將代碼捐獻給Apache 軟體基金會。2010年9月，Hive 畢業成為Apache 頂級項目。Hadoop 生態系統中的每個主要參與者都發布和支持Hive，包括Cloudera、MapR、Hortonworks 和IBM。Amazon Web Services 在Elastic MapReduce（EMR）中提供了Hive 的修改版作為雲服務。

早期發布的Hive 使用MapReduce 運行查詢。複雜查詢需要多次傳遞數據，這會降低性能。所以Hive 不適合交互式分析。由Hortonworks 領導的Stinger 明顯的提高了Hive 的性能，尤其是通過使用Apache Tez，一個精簡MapReduce 代碼的應用框架。Tez 和ORCfile，一種新的存儲格式，對Hive 的查詢產生了明顯的提速。

Cloudera 實驗室帶領一個並行項目重新設計Hive 的後端，使其運行在Apache Spark 上。經過長期測試後，Cloudera 在2016年初發布了Hive-on-Spark 的正式版本。

在2016年，Hive 有100多人的貢獻者。該團隊在2月份發布了Hive 2.0，並在6月份發布了Hive 2.1。Hive 2.0 的改進包括了對Hive-on-Spark 的多個改進，以及性能、可用性、可支持性和穩定性增強。Hive 2.1 包括了Hive LLAP（Live Long and Process），它結合持久化的查詢伺服器和優化後的內存緩存，來實現高性能。該團隊聲稱提高了25倍。

9月，Hivemall 項目進入了Apache 孵化器，正如我在我的機器學習年度總結的第二部分中指出的。Hivemall 最初由Treasure Data 開發並捐獻給Apache 軟體基金會，它是一個可擴展的機器學習庫，通過一系列的Hive UDF 來實現，設計用於在Hive、Pig 和Spark SQL 上運行MapReduce。該團隊計劃在2017年第一季度發布了第一個版本。

Apache Hive 運作架構圖

  • Hive CLI，全名：Hive Command Line Interface
    • 在這個命令操作介面裡面，我們執行Hive的命令檔，有 create、select、drop、show table 和 show database…等，這些指令通通會交給 Driver
  • Driver ， parser ( 解析器 )
    • 會先做 Optomizer（最佳化），分析這個命令，底層要怎麼做，看是否會用到 MapReduce 來做，還是用 HDFS 的功能來做就可以了。
    • Optomier 做完後，會做 Compiler，把原本的 Hive 命令翻譯成 Java 的 Map 跟 Reduce 程式。
    • 翻譯好後再做 Exector （執行），執行翻譯好的 Map 和 Reduce 程式。
  • MapReduce
    • hive 內建的資料處理引擎。
    • disk killer 。
  • Tez
    • 我們可以改裝 Hadoop 的資料處理引擎：Tez ( 渦輪增壓引擎 )，它的速度可以比原廠的 MapReduce 快一級到兩級。或是整顆 MapReduce 換掉，變成 Spark，但是 Spqrk 需要有一定檔次的設備。
  • Spark
  • Metastore
    • 他的名字叫做 derby，一個關連式資料庫（資訊界第一個用 Java 語言寫出來的資料庫），這個資料庫負責儲存 schema 。
    • hive -f *.hsql 產生出來的 table ，像是內部資料表、外部資料表、分割表，最後再加 parquet 的 九宮格資料表，這些表格都有 schema 的宣告。
    • schema ，資料表的名字、有幾個欄位、資料表有沒有分割，資料表有沒有 parquet 、外部資料表指定的資料來源目錄區。
    • 資料表的內容一樣會存放在 hdfs 檔案系統中。

hdfs 檔案系統的檔名存在哪裡？ 檔案存在哪裡？ hdfs 目錄資料存 fsimage（Name Node 的 nn 目錄區） hdfs 資料 block 存 dtw1、dtw2、dtw3 ( Data Node 的 dn 目錄區 )

如果在 bigred 產生一個資料表叫 a，那麼 rbean 和 gbean 可不可以也產生一個資料表叫 a ？ 答：可以，因為儲存的位置不一樣

  • Web UI
    • 透過瀏覽器的介面對 hive 進行操作，點點選選 or 有文字框可以打命令進去。
    • Apache Zeppelin ，它可以幫我們把分析的結果透過圖形表達出來，在上面操作一樣適用上圖的運作。
    • Apache Ambari ，吃的資源較 Zeppelin 多
  • Thrift Server，他有另外一個名稱：Hive Server 2，他的 Server 可以連接 OBDC 和 JDBC ( 傳統 Relation Database 的程式介面 )
    • JDBC ，Java 語言連接 Relation Database 的標準介面
    • OBDC ，C# 語言連接 Relation Database 的標準介面
    • 國內目前在推的是 Microsoft Power BI，可以連接到 Hive Server 2，來使用 Hive 的分析能力
  • Metastore_db | apache derby
    • Java 語言的資料庫，由 sybase 公司開發，用來儲存 hive 產生 table 的 schema 資料，地位相當於 name node
    • hive 建完 table 的資料會存在 hdfs 分散式檔案系統
  • sybase SQL Server | msSQL 的資料引擎

啟動 Hive 並建立資料表

在 Windows 系統的 cmd.exe 視窗, 執行以下命令

$ ssh rbean@<dta1 ip> 
rbean@172.16.119.3 s password: rbean  
# -S，代表等等進入 hive 交談模式時，螢幕上的訊息會比較少，但還是會有很多話
rbean@dta1:~$ hive -S

# default 是預設的 database ，預設的資料庫名稱
hive (default)>  

# 建立 table
# x為欄位名稱
# string = varvher 資料型態 ( Java 的影子 )
hive (default)> create table dummy (x string);

第一次建立表格, Hive 會在 HDFS 建立 /user/帳號/hive 目錄, 用來儲存資料表的資料, 檢視命令如下

hive (default)> dfs  -ls  /user/rbean/hive;
Found 1 items
drwxr-xr-x   - rbean rbean  0 2022-05-05 17:53 /user/rbean/hive/dummy

 
顯示目前所有表格名稱
hive> show tables;
dummy

==只要在hive產生一個表格(table)，Hdfs分散式檔案系統裡面就會有一個同名的目錄，這個目錄會存放table的資料==

Hive 基本命令 

$ hive -S 2>/dev/null
hive (default)>

2 ，警告和錯誤的輸出裝置

增加資料

雖然是用SQL語法新增資料，真正執行的人還是Map和Reduce

hive (default)> insert into dummy values('abc');
hive (default)> insert into dummy values('xyz');

在hive裡面看Hdfs檔案系統的目錄

hive (default)> dfs -ls /user/rbean/hive/dummy; 
Found 2 items
-rw-r--r--   2 rbean rbean  4 2020-09-27 01:40 /user/rbean/hive/dummy/000000_0
-rw-r--r--   2 rbean rbean  4 2020-09-27 01:41 /user/rbean/hive/dummy/000000\_0\_copy_1

嚇死人了！！！在hive裡面執行insert into一筆資料，在hive工具裡面他是一個檔案，一筆資料一個檔案，假如有1000萬筆資料insert into，就代表會有1000萬個檔案，所以Hadoop處理小檔案是他的致命傷，==在外面使用 Hive 建 table，不會使用 insert into 來增加資料。==

查詢 dummy 表格資料 

hive> select * from dummy;
abc
xyz
hive (default)> select * from dummy where x = 'abc';
abc
hive (default)> select * from dummy where x = 'xyz';
xyz
  • 上面的 SQL Command ，會被轉成 map 和 reduce 程式，再丟給 Resource manager ，它會看 map 和 reduce 的程式需要的運算資源是否符合標準，再丟給 MRappmaster ，它會看資料有幾個 block ，最後再丟給 Container ，也就是 YarnChild

刪除 dummy 表格 

hive (default)> drop table dummy;
hive (default)> dfs -ls /user/rbean/hive/dummy; 
ls: `/user/rbean/hive/dummy`: No such file or directory
...........
hive> quit;

建立 Hive Customers 資料表

題目： 

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 2000.00  |  
| 2  | Khilan   | 25  | Delhi     | 1500.00  |  
| 3  | kaushik  | 23  | Kota      | 2000.00  | 
| 4  | Chaitali | 25  | Mumbai    | 6500.00  | 
| 5  | Hardik   | 27  | Bhopal    | 8500.00  | 
| 6  | Komal    | 22  | MP        | 4500.00  | 
| 7  | Muffy    | 24  | Indore    | 10000.00 | 
+----+----------+-----+-----------+----------+

# 傳統關連式資料庫的運作流程
# 先建立好資料庫及表格的欄位及屬性，才能將資料放進去
mysql -> create db/table(schema) -> insert data
* schema on write (IT)

# 資料科技世代
# 先將資料寫入後，當有需要分析需求時，才去定義schema，建立資料庫/資料表
hive -> insert data (hdfs dfs -put) -> create db/table
* schema on read (Big Data)

答案： 

# 匯入資料
rbean@dta1:~$ echo '1,Ramesh,32,Ahmedabad,2000.00
2,Khilan,25,Delhi,1500.00
3,kaushik,23,Kota,2000.00
4,Chaitali,25,Mumbai,6500.00
5,Hardik,27,Bhopal,8500.00
6,Komal,22,MP,4500.00
7,Muffy,24,Indore,10000.00' > customers.csv

# 在 HDFS 檔案系統裡建立目錄
rbean@dta1:~$ hdfs dfs -mkdir -p mydataset/customers

# 把在 linux 檔案系統的檔案上傳到 HDFS 檔案系統
rbean@dta1:~$ hdfs dfs -put customers.csv  mydataset/customers

# 產生一個 hsql 檔（h 是 Hive 的意思），也就是說產生一個 Hive 的 SQL 命令檔，
# 這個檔案名稱為：customers.hsql
# 檔案內容為，產生一個叫做 customers 的 table，
# 裡面有五個欄位，對應剛才輸入的資料。
rbean@dta1:~$ echo $'CREATE TABLE customers (
>    id int,
>    name string,
>    age int,
>    address string,
>    salary float
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'
> STORED AS TEXTFILE LOCATION \'/user/$USER/mydataset/customers\' ; ' |  envsubst > customers.hsql

# ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'，
這行是說在 table 裡面的每一筆資料用逗號做切割，相當於 cut -d 指定的分隔符號

# STORED AS TEXTFILE LOCATION \'/user/$USER/mydataset/customers\'，
這行是在宣告 table 的資料會在 HDFS 檔案系統的家目錄裡面的 mydataset/customers/ 目錄裡面

# envsubst | 會幫我們把前面文字的 $USER 替換成當前登入的帳號

# 以上從 CREATE TABLE 開始，到最後宣告資料來源的目錄在哪，都會記錄在 ~/metastore_db 裡面

# 執行 hive 的 SQL 命令檔，來產生 customers 這個 table
rbean@dta1:~$ hive -S -f customers.hsql
# -f <filename> | SQL from files
# -S,--silent | Silent mode in interactive shell

# 檢查
rbean@dta1:~$ hive -S -e "show tables" 2>/dev/null
customers
# 利用 linux 的二號裝置，也就是警告和錯誤訊息的資訊，全部丟到黑洞，只看正常的資訊
# -e <quoted-query-string> | SQL from command line

# 練習，新增一筆資料進去
$ nano 20220721.txt
9,Mffy,4,Idore,1000.00

# 將檔案 append 到 hdfs 
$ hdfs dfs -appendToFile 20220721.txt mydataset/customers/customers.csv

# 檢查是否符合預期
$ hive -e "select * from customers" 2>/dev/null
1       Ramesh  32      Ahmedabad       2000.0
2       Khilan  25      Delhi   1500.0
3       kaushik 23      Kota    2000.0
4       Chaitali        25      Mumbai  6500.0
5       Hardik  27      Bhopal  8500.0
6       Komal   22      MP      4500.0
7       Muffy   24      Indore  10000.0
8       hehe    25      taiwan  50000.0
9       Mffy    4       Idore   1000.0
  • ==只要是 Hive 的 table 一定有一個對應的資料夾==
  • hadoop 被設計出來是處理 RAW data ，所以不能在 hive 下達 update 或 delete 的命令， 硬要下，你會得到以下錯誤訊息 : Attempt to do update or delete using transaction manager that does not support these operations.
  • 如果要對 hdfs 檔案系統的資料做局部的修改，要動用到 HBase ，他可以對 hdfs 系統的檔案，進行 新增、刪除、修改和查詢檔案。

查詢與刪除 Customers 資料表

使用簡易的SQL語法分析Hive裡面的資料 查看customers這個table裡面有幾筆資料

rbean@dta1:~$ hive -S -e "select count(*) from customers" 2>/dev/null
7

查看薪水 > 4500 的id,姓名和薪資的資料

rbean@dta1:~$ hive -S -e "select id,name,salary from customers where salary >= 4500" 2>/dev/null
4       Chaitali        6500.0
5       Hardik  8500.0
6       Komal   4500.0
7       Muffy   10000.0

刪除table

rbean@dta1:~$ hive -e "drop table customers"

因是內部資料表, 所以 mydataset/customers 資料夾會被刪除

檢查

rbean@dta1:~$ hdfs dfs -ls mydataset/

分析102 年~ 110 年各鄉鎮市區人口密度資料集 

下載資料
bigred@dta1$ nano dt.sh
#!/bin/bash
wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=C76A7B39-5BE7-4D6B-B797-0D52AACE11EB' | grep -e '^102' > p102.csv
[ "$?" == "0" ] && echo "p102.csv ok"

wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=37AB8547-043D-4F53-9ADB-594EAE4358F4' | grep -e '^103' > p103.csv
[ "$?" == "0" ] && echo "p103.csv ok"

wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=EC708E79-7160-4148-AB36-C055F4D55CC5' | grep -e '^104' > p104.csv
[ "$?" == "0" ] && echo "p104.csv ok"

wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=5B23A53B-C25A-4288-B24C-C8E65F76FDBB' | grep -e '^105' > p105.csv
[ "$?" == "0" ] && echo "p105.csv ok"

wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=E7CE3815-30E1-4B92-A337-9C026EBC017F' | grep -e '^106' > p106.csv
[ "$?" == "0" ] && echo "p106.csv ok"

wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=B54BD944-50DE-4618-8DB0-FC66EE5B64C1' | grep -e '^107' > p107.csv
[ "$?" == "0" ] && echo "p107.csv ok"

wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=47072428-5C8F-4897-8535-DBF583572597' | grep -e '^108' > p108.csv
[ "$?" == "0" ] && echo "p108.csv ok"

wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=F6DC3228-CA65-4F0C-A2B1-39D18D6D29DA' | grep -e '^109' > p109.csv
[ "$?" == "0" ] && echo "p109.csv ok"

wget --no-check-certificate -q -O - 'https://data.moi.gov.tw/MoiOD/System/DownloadFile.aspx?DATA=C45AD94C-ED23-4381-8F3E-359CD5C276F0' | grep -e '^110' > p110.csv

[ "$?" == "0" ] && echo "p110.csv ok"

[ ! -d opendata/p ] && mkdir -p opendata/p
mv p*.csv opendata/p

bigred@dta1:~$ sudo chmod +x dt.sh
	
bigred@dta1:~$ ./dt.sh
p102.csv ok
p103.csv ok
p104.csv ok
p105.csv ok
p106.csv ok
p107.csv ok
p108.csv ok
p109.csv ok
p110.csv ok

bigred@dta1:~$ tree -L 1 opendata
opendata
└── p

1 directory, 0 files

bigred@dta1:~$ hdfs dfs -mkdir /dataset/opendata

bigred@dta1:~$ hdfs dfs -put opendata/* /dataset/opendata

bigred@dta1:~$ hdfs dfs -ls /dataset/opendata
Found 1 items
drwxr-xr-x   - bigred bigboss          0 2022-05-05 22:17 /dataset/opendata/p

建立外部人口密度資料表

  • 外部資料表 EXTERNAL TABLE | 標料表被 drop 掉，資料還會存在

宣告和執行hive的SQL檔

## 宣告
bigred@dta1:~$ echo $'CREATE EXTERNAL TABLE twpop (
> year int,
> id string,
> total float,
> area float,
> density int
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'
> STORED AS TEXTFILE LOCATION \'/dataset/opendata/p\';' > twpop.hsql

## 執行
bigred@dta1:~$ hive -f  twpop.hsql  2>/dev/null

分析102年~110年台灣人口密度的變化

igred@dta1:~$ hive -e 'select year,sum(total)/10000 from twpop group by year order by year'  2>/dev/null
102     2337.3517
103     2343.3753
104     2349.2074
105     2353.9816
106     2357.1227
107     2358.8932
108     2360.3121
109     2356.1236
110     2337.5314

協同作業範例

人在rbean，讀取的資料與bigred是同一份

rbean@dta1:~$ nano twpop.hsql
CREATE EXTERNAL TABLE twpop (
a int,
b string,
c float,
d float,
e int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE LOCATION '/dataset/opendata/p';
	
rbean@dta1:~$ hive -f twpop.hsql 2>/dev/null
	
rbean@dta1:~$ hive -e 'select a,sum(c)/10000 from twpop group by a order by a'  2>/dev/null
102     2337.3517
103     2343.3753
104     2349.2074
105     2353.9816
106     2357.1227
107     2358.8932
108     2360.3121
109     2356.1236
110     2337.5314

==大數據平台觀念==

  • 很少有現成的大量資料，隨著時間的推移，持續更新資料與擴充來源，不是一口氣突然出現的
  • 並且資料需要經過一定的整理，不是直接使用

刪除twpop table 

rbean@dta1:~$ hive -e "drop table twpop" 2>/dev/null
rbean@dta1:~$ hive -e "show tables" 2>/dev/null

問題：HDFS檔案系統裡的資料夾還在嗎？

rbean@dta1:~$ hdfs dfs -ls /dataset/opendata/p
Found 9 items
-rw-r--r--   3 bigred bigboss      16677 2022-05-05 22:17 /dataset/opendata/p/p102.csv
-rw-r--r--   3 bigred bigboss      15604 2022-05-05 22:17 /dataset/opendata/p/p103.csv
-rw-r--r--   3 bigred bigboss      15603 2022-05-05 22:17 /dataset/opendata/p/p104.csv
-rw-r--r--   3 bigred bigboss      15601 2022-05-05 22:17 /dataset/opendata/p/p105.csv
-rw-r--r--   3 bigred bigboss      15604 2022-05-05 22:17 /dataset/opendata/p/p106.csv
-rw-r--r--   3 bigred bigboss      15598 2022-05-05 22:17 /dataset/opendata/p/p107.csv
-rw-r--r--   3 bigred bigboss      15597 2022-05-05 22:17 /dataset/opendata/p/p108.csv
-rw-r--r--   3 bigred bigboss      15596 2022-05-05 22:17 /dataset/opendata/p/p109.csv
-rw-r--r--   3 bigred bigboss      15594 2022-05-05 22:17 /dataset/opendata/p/p110.csv

答案：在，這就是外部資料表（EXTERNAL TABLE），他在HDFS檔案系統裡面的資料不會因為資料表被刪除而被刪除，會保留著，這種運作模式，在傳統關連式資料庫叫做view。

==如果在*.hsql檔案裡面，沒有宣告EXTERNAL TABLE，那麼資料表被刪除，在HDFS檔案系統裡面的資料夾以及裡面存放的檔案都會被刪掉。==

Hive 分割資料表 (Partition)

在傳統關連式資料庫中，把重複的資料用最精簡的辦法來處理，就叫做正規化，而在Hive我們們叫做Partition

建立以年分割的人口密度外部資料表 

bigred@dta1:~$ hdfs dfs -mkdir mydataset

bigred@dta1:~$ echo $'CREATE EXTERNAL TABLE twpop_yp (
> id string,
> total int,
> area float,
> density int
> )
> PARTITIONED BY (year string)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'
> STORED AS TEXTFILE LOCATION \'/user/$USER/mydataset/twpop_yp\';' | envsubst > twpop_yp.hsql
## PARTITIONED BY (year string)，這行是宣告，等等用年來做切割，
## 因為我們是用年來收集和產生資料
## 上面命令會自動建立 mydataset/twpop_yp 目錄

bigred@dta1:~$ hive -f twpop_yp.hsql  2>/dev/null

# 查看外部資料表對應的資料夾
bigred@dta1:~$ hdfs dfs -ls mydataset/twpop_yp
	
bigred@dta1:~$ hive -e 'show tables' 2>/dev/null
twpop
twpop_yp

手動建立 twpop_yp 資料表的分割目錄 

bigred@dta1:~$ hive -S -e "alter table twpop_yp add if not exists partition (year='102')"
## 因為已經有宣告這個table可以做partition，所以這邊可以手動建立，
## 條件是如果year=102這個partition不存在，就建立year=102這個目錄。

bigred@dta1:~$ hdfs dfs -ls mydataset/twpop_yp
Found 1 items
drwxr-xr-x   - bigred bigboss          0 2022-05-05 23:53 mydataset/twpop_yp/year=102

==所謂的資料表切割，就是在twpop_yp目錄下，再產生一個目錄，名字就是欄位(year=102)，也就代表，以後只要打SQL指令，後面加where year=102，系統就會對到year=102的資料夾==

# -cp的指令，事實上是dtw系列的工作機在作業
# 不用 hdfs dfs -put 的原因是，因為如果檔案很大的話，只有 a1 一台電腦在奮鬥，可是用 -cp 的話，是全部的 work 虛擬主機在一起做事
$ hdfs dfs -cp /dataset/opendata/p/p102.csv mydataset/twpop_yp/year=102

$ hive -e 'select * from twpop_yp where year=102 limit 2' 2>/dev/null
102     NULL    556920.0        23      102
102     NULL    389813.0        16      102
# year=102，事實上對應的是mydataset/twpop_yp/year=102這個資料夾，
# 所以等等只會讀這個資料夾的內容，如果有其他年份的資料夾，
# 就不會再浪費時間和運算資源去讀取不需要的檔案

# 移除 第一個 year 欄位
$ hdfs dfs -cat /dataset/opendata/p/p102.csv | cut -d ',' -f2- > pp102.csv

# 重新上傳處理好的檔案到hdfs檔案系統，並且刪除舊的檔案
$ hdfs dfs -put pp102.csv mydataset/twpop_yp/year=102/; hdfs dfs -rm mydataset/twpop_yp/year=102/p102.csv
Deleted mydataset/twpop_yp/year=102/p102.csv


$ hive -e 'select * from twpop_yp where year=102' 2>/dev/null | tail -n 4
連江縣莒光鄉　  1438    4.7     306     102
連江縣東引鄉　  1171    3.8     308     102
東沙群島        NULL    2.38    NULL    102
南沙群島        NULL    0.4896  NULL    102

* 上面最後二筆資料的 第二個欄位資料 (people_total) 無法解析, 以至出現 NULL 值, 只要有一筆資料有欄位是 NULL 值, select count(*) from twpop_yp 這命令執行結果會出現 0

$ hive -e 'select count(*) from twpop_yp where year=102 and total IS NOT NULL' 2>/dev/null
368

自動產生 twpop_yp 資料表分割目錄 

bigred@dta1:~$ echo 'SET hive.exec.dynamic.partition.mode = nonstrict;
> INSERT OVERWRITE TABLE twpop_yp
> PARTITION (year)
> SELECT id,total,area,density,year
> FROM twpop; ' > pp_insert.hsql
## 先看select的欄位排序，year的欄位一定要放最後，
## FROM twpop ，從twpop資料夾抓取資料，用年切割
## INSERT OVERWRITE TABLE twpop_yp，把SELECT出來的資料放到twpop_yp 資料表中

bigred@dta1:~$ hive -f pp_insert.hsql  2>/dev/null

bigred@dta1:~$ hdfs dfs -ls mydataset/twpop_yp
Found 9 items
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=102
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=103
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=104
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=105
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=106
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=107
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=108
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=109
drwxr-xr-x   - bigred bigboss          0 2022-05-06 09:10 mydataset/twpop_yp/year=110

bigred@dta1:~$ hdfs dfs -ls mydataset/twpop_yp/year=102
Found 1 items
-rw-r--r--   3 bigred bigboss      14821 2022-05-06 09:10 
mydataset/twpop_yp/year=102/000000_0

bigred@dta1:~$ hdfs dfs -cat mydataset/twpop_yp/year=102/000000_0 | head -n 5
新北市板橋區　,556920,23.1373,24070
新北市三重區　,389813,16.317,23890
新北市中和區　,415742,20.144,20639
新北市永和區　,229062,5.7138,40089
新北市新莊區　,409760,19.7383,20760
cat: Unable to write to output stream.

分析人口密度資料集 

bigred@dta1:~$ for i in {1..3}; do (echo ""; time hive -e 'select sum(total)/10000 from twpop_yp where year=107' 2>/dev/null); done

2358.8932

real    0m29.999s
user    0m14.639s
sys     0m6.207s

2358.8932

real    0m30.437s
user    0m15.290s
sys     0m5.645s

2358.8932

real    0m29.259s
user    0m15.578s
sys     0m5.606s

雖然是用Hive的SQL指令去執行，但實際上在跑的仍然是Map和Reduce

bigred@dta1:~$ startjps
bigred@dta1:~$ hive -e 'select sum(total)/10000 from twpop_yp where year=107' 2>/dev/null
2358.8932
bigred@dta1:~$ stopjps
[dtw1]
09:43:07
17018 MRAppMaster 256m ## MRAppMaster會先上線


[dtw2]

[dtw3]
09:43:15
19458 YarnChild 256m  ## Map在抓資料

09:43:22
20083 YarnChild 384m  ## Reduce在分析


bigred@dta1:~$ startjps

bigred@dta1:~$ hive -e 'select * from twpop_yp limit 2' 2>/dev/null
新北市板橋區　  556920  23.1373 24070   102
新北市三重區　  389813  16.317  23890   102
## hive事實上有一個解析器叫做parser，他會分析指用者下的指令，且他知道twpop_yp的整個結構和內容，所以一看到這行指令
#就判斷只需要在HDFS上跑，不需要動到YARN和ＭapReduce的功能

bigred@dta1:~$ stopjps
[dtw1]
                       ## 空的代表沒有動用MapReduce
[dtw2]
                       ## 空的代表沒有動用MapReduce
[dtw3]
                       ## 空的代表沒有動用MapReduce

==如果多人同時協同作業，要確定YARN運算系統的總作戰力==

Apache Parquet

Parquet網站連結

Column Storage

Vertica stores data in a column format so it can be queried for best performance. Compared to row-based storage, column storage reduces disk I/O making it ideal for read-intensive workloads. VERTICA reads only the columns needed to answer the query. For example:

SELECT avg(price) FROM tickstore WHERE symbol = 'AAPL' and date = '5/31/13';

傳統的資料庫在執行上面那段SQL command時，會去讀取所有欄位的資料

==Parquet 可以只讀取目標、篩選欄位(price、symbol 和 date 三個)，要用到的才讀，其他的不讀==

Apache Parquet Format

  • 傳統資料庫儲存資料 »> 一筆一筆資料做儲存
  • Parquet
    • 相同欄位的資料存在一起，並且壓縮，因為是這種儲存模式，所以才能讀取一個目標
    • 對資料做垂直切割。
    • snappy 壓縮的演算法，特質:壓縮速度特快，犧牲壓縮比，但還是能到 1/10 的壓縮比
    • 企業備份資料，壓縮比最少 1/10

建立九宮格的人口密度資料表 

宣告hive的SQL命令檔
bigred@dta1:~$ echo $'drop table if exists twpop_pp;
> CREATE EXTERNAL TABLE twpop_pp (
> id string,
> total int,
> area float,
> density int
> )
> PARTITIONED BY (year string)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'
> STORED AS Parquet LOCATION \'/user/$USER/mydataset/twpop_pp\'; ' | envsubst > twpop_pp.hsql

## PARTITIONED BY (year string)，用年做切割（水平切割）
## STORED AS Parquet，用Parquet的方式儲存資料（垂直切割）
## 上面命令會自動建立 mydataset/twpop_pp 目錄 

執行hive的SQL命令檔
bigred@dta1:~$ hive -f twpop_pp.hsql  2>/dev/null

匯入人口密度資料 

bigred@dta1:~$ echo 'SET hive.exec.dynamic.partition.mode = nonstrict;
> INSERT OVERWRITE TABLE twpop_pp
> PARTITION (year)
> SELECT id,total,area,density,year
> FROM twpop; ' > ppp_insert.hsql

bigred@dta1:~$ hive -f ppp_insert.hsql 2>/dev/null

bigred@dta1:~$ hdfs dfs -ls mydataset/twpop_pp
Found 9 items
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=102
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=103
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=104
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=105
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=106
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=107
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=108
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=109
drwxr-xr-x   - bigred bigboss          0 2022-05-06 10:50 mydataset/twpop_pp/year=110

bigred@dta1:~$ hdfs dfs -cat mydataset/twpop_pp/year=102/000000_0 | head -c 36
PAR1     ,      6( 高雄cat: Unable to write to output stream.

parquet會將資料做壓縮 

檢查Partition + Parquet儲存完的資料大小
bigred@dta1:~$ hdfs dfs -ls -h mydataset/twpop_pp/year=102/000000_0
-rw-r--r--   3 bigred bigboss     14.0 K 2022-05-06 10:50 mydataset/twpop_pp/year=102/000000_0

檢查只用Partition儲存完的資料大小
bigred@dta1:~$ hdfs dfs -ls -h mydataset/twpop_yp/year=102/000000_0
-rw-r--r--   3 bigred bigboss     14.5 K 2022-05-06 09:10 mydataset/twpop_yp/year=102/000000_0

這裡只差0.5kb是因為目前的資料量比較小，所以差異不大

實名制口罩 1.0 - Hive (Extract-Load-Transform)

下載口罩 1.0 資料集 

## wget下載壓縮檔
bigred@dta1:~$ wget http://www.oc99.org/dt/dataset/twmask.zip
--2022-05-06 13:02:26--  http://www.oc99.org/dt/dataset/twmask.zip
Resolving www.oc99.org (www.oc99.org)... 104.131.160.22
Connecting to www.oc99.org (www.oc99.org)|104.131.160.22|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 860260408 (820M)
Saving to: 'twmask.zip'

twmask.zip                           100%[====================================================================>] 820.41M  5.21MB/s    in 2m 16s

2022-05-06 13:04:42 (6.05 MB/s) - 'twmask.zip' saved [860260408/860260408]

bigred@dta1:~$ ls -alh twmask.zip
-rw-r--r-- 1 bigred bigred 821M Jul 11  2021 twmask.zip

bigred@dta1:~$ unzip twmask.zip

bigred@dta1:~$ dir twmask | wc -l
5585

bigred@dta1:~$ hdfs dfs -mkdir -p /dataset/twmask
bigred@dta1:~$ time hdfs dfs -put -f twmask/* /dataset/twmask/

real    4m51.668s
user    0m10.768s
sys     0m33.188s

bigred@dta1:~$ hdfs dfs -ls /dataset/twmask | wc -l
5583
## 少兩筆是因為在hdfs檔案系統裡面沒有.和..的目錄

檢視口罩 1.0 資料集 

## 由檔名可以得知2020/03/12日開始每一小時會抓一次資料
bigred@dta1:~$ hdfs dfs -ls /dataset/twmask | grep '/12-03' | head -n 5
-rw-r--r--   3 bigred bigboss     733586 2022-05-06 13:25 /dataset/twmask/12-03-2020-00-29.csv
-rw-r--r--   3 bigred bigboss     733586 2022-05-06 13:25 /dataset/twmask/12-03-2020-01-29.csv
-rw-r--r--   3 bigred bigboss     733586 2022-05-06 13:25 /dataset/twmask/12-03-2020-02-29.csv
-rw-r--r--   3 bigred bigboss     733586 2022-05-06 13:25 /dataset/twmask/12-03-2020-03-29.csv
-rw-r--r--   3 bigred bigboss     733586 2022-05-06 13:25 /dataset/twmask/12-03-2020-04-29.csv

## 2020/03/28日開始每半小時會抓一次資料
bigred@dta1:~$ hdfs dfs -ls /dataset/twmask | grep '/28-03' | head -n 3
-rw-r--r--   3 bigred bigboss     574469 2022-05-06 13:28 /dataset/twmask/28-03-2020-00-09.csv
-rw-r--r--   3 bigred bigboss     574469 2022-05-06 13:28 /dataset/twmask/28-03-2020-00-39.csv
-rw-r--r--   3 bigred bigboss     574469 2022-05-06 13:28 /dataset/twmask/28-03-2020-01-09.csv

## 2020/04/18日開始每15分鐘會抓一次資料
bigred@dta1:~$ hdfs dfs -ls /dataset/twmask | grep '/18-04' | head -n 2
-rw-r--r--   3 bigred bigboss     622556 2022-05-06 13:26 /dataset/twmask/18-04-2020-00-02.csv
-rw-r--r--   3 bigred bigboss     633774 2022-05-06 13:26 /dataset/twmask/18-04-2020-00-17.csv

## 一直收集到2020/05/31日晚上11點57分
bigred@dta1:~$ dir twmask | tail -n 5
-rw-r--r--  1 bigred bigred  36K Jun  1  2020 31-05-2020-22-57.csv
-rw-r--r--  1 bigred bigred  36K Jun  1  2020 31-05-2020-23-12.csv
-rw-r--r--  1 bigred bigred  36K Jun  1  2020 31-05-2020-23-27.csv
-rw-r--r--  1 bigred bigred  36K Jun  1  2020 31-05-2020-23-42.csv
-rw-r--r--  1 bigred bigred  36K Jun  1  2020 31-05-2020-23-57.csv

## 看欄位名稱
bigred@dta1:~$ hdfs dfs -cat /dataset/twmask/12-03-2020-23-14.csv | head -n 3
醫事機構代碼,醫事機構名稱,醫事機構地址,醫事機構電話,成人口罩剩餘數,兒童口罩剩餘數,來源資料時間
0145080011,衛生福利部花蓮醫院豐濱原住民分院,花蓮縣豐濱鄉豐濱村光豐路４１號,(03)8358141,3652,2287,2020/03/12 23:13:58
0291010010,連江縣立醫院,連江縣南竿鄉復興村２１７號,(083)623995,0,274,2020/03/12 23:13:58
cat: Unable to write to output stream.


bigred@dta1:~$ hdfs dfs -cat /dataset/twmask/12-03-2020-23-14.csv | grep '金門縣'
2390010010,金門縣金城鎮衛生所,金門縣金城鎮民權路１４７號,(082)325059,0,1468,2020/03/12 23:13:58
2390020016,金門縣金沙鎮衛生所,金門縣金沙鎮三民路１６號,(082)333597,597,1678,2020/03/12 23:13:58
2390030030,金門縣金湖鎮衛生所,金門縣金湖鎮正義里成功７號之１,(082)336662,431,884,2020/03/12 23:13:58
2390040518,金門縣金寧鄉衛生所,金門縣金寧鄉盤山村仁愛新屯２之１號,(082)325735,2,66,2020/03/12 23:13:58
2390050032,金門縣烈嶼鄉衛生所,金門縣烈嶼鄉西口村西方６－５號,(082)362078,1305,1360,2020/03/12 23:13:58
5990010515,大賀藥局,金門縣金城鎮北門里民生路３號,(082)327678,1,8,2020/03/12 23:13:58
5990010542,大愛藥局,金門縣金城鎮東門里民族路１６號,(082)312266,1,0,2020/03/12 23:13:58
5990010631,大森藥局,金門縣金城鎮民生路２８、３０號１、２樓,(82)325100,6,0,2020/03/12 23:13:58
5990010668,百泰藥局,金門縣金城鎮西海路１段１號,(082)312832,303,453,2020/03/12 23:13:58
5990030062,大山藥局,金門縣金湖鎮新市里中正路２號、２－２號,(082)333290,1,0,2020/03/12 23:13:58

認識口罩 1.0 資料集 

2020/03/19 有發口罩的藥局總數
bigred@dta1:~$ hdfs dfs -cat  /dataset/twmask/19-03-2020-14-06.csv | grep -e "^5" | wc -l
5946

2020/03/19 有發口罩的區公所總數
bigred@dta1:~$ hdfs dfs -cat  /dataset/twmask/19-03-2020-14-06.csv | grep -e "^2" | wc -l
335

2020/04/19 有發口罩的藥局總數
bigred@dta1:~$ hdfs dfs -cat /dataset/twmask/19-04-2020-09-18.csv | grep -e "^5" | wc -l
387
## 因爲星期日藥局休息, 所以數字變少

產生 口罩 1.0 資料表 

bigred@dta1:~$ echo $'DROP TABLE IF EXISTS daily_mask;
> CREATE EXTERNAL TABLE daily_mask (
>   code string,
>   name string,
>   address string,
>   tel string,
>   adult INT,
>   child INT,
>   dt string
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'
> STORED AS TEXTFILE LOCATION \'/dataset/twmask\'; ' > daily_mask.hsql

bigred@dta1:~$ hive -S -f daily_mask.hsql

bigred@dta1:~$ hive -S -e 'show tables' 2>/dev/null
daily_mask
twpop
twpop_pp
twpop_yp

檢視 口罩 1.0 資料表

口罩 1.0 分析 - 單一健康服務中心

口罩 1.0 分析 - 單一藥局

提升分析效能

顯示單日每一個小時的總量 (5 千多個檔)

建立 twmask 資料集壓密目錄 

$ nano twmcompact.pig 
a = load '/dataset/twmask' using PigStorage(',');
b = filter a by $0!='醫事機構代碼';
rmf /dataset/twmcompact;
store b into '/dataset/twmcompact' using PigStorage(',');

$ pig twmcompact.pig
  • 在 pig 的語法中，$0,1,2..，代表第一個, 第二個，第三個欄位…依此類推
  • filter a by $0!='醫事機構代碼'，代表要擷取第一欄位不等於 醫事機構代碼，的所有資料
  • pig 會將資料壓密，意思就是將 5000多個檔案，壓扁成 20 幾個檔案，並不是將資料壓縮

建立 twmask 資料集壓密目錄 

$ hdfs dfs -ls -h /dataset/twmcompact
Found 27 items
-rw-r--r--   2 bigred bigboss          0 2023-02-19 01:53 /dataset/twmcompact/_SUCCESS
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00000
-rw-r--r--   2 bigred bigboss    126.8 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00001
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00002
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00003
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00004
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00005
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00006
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00007
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00008
-rw-r--r--   2 bigred bigboss    126.8 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00009
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00010
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00011
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:52 /dataset/twmcompact/part-m-00012
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00013
-rw-r--r--   2 bigred bigboss    126.8 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00014
-rw-r--r--   2 bigred bigboss    126.8 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00015
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00016
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00017
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00018
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00019
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00020
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00021
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00022
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00023
-rw-r--r--   2 bigred bigboss    126.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00024
-rw-r--r--   2 bigred bigboss     11.9 M 2023-02-19 01:53 /dataset/twmcompact/part-m-00025

建立 twmcompact 資料表 

$ echo $'DROP TABLE IF EXISTS twmcompact;
CREATE EXTERNAL TABLE twmcompact (
  code string,
  name string,
  address string,
  tel string,
  adult INT,
  child INT,
  dt string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'
STORED AS TEXTFILE LOCATION \'/dataset/twmcompact\'; ' > twmcompact.hsql

$ hive -S -f twmcompact.hsql

顯示單日每一個小時的總量 (25 個檔) 

$ time hive -S -e "select substring(dt,12,8) as t, sum(adult)/10000 from twmcompact where substring(dt,1,10)='2020/04/17' group by substring(dt,12,8) order by t" 2>/dev/null

建立 Parquet 資料集檔 

How to Set Reducers in Pig, Hive And MapReduce
https://hadoopjournal.wordpress.com/2015/05/30/set-reducers-in-pig-hive-and-mapreduce/

# 因 pig 內定不會處理 parquet 的資料，所以下載一個 *.jar 檔，讓 pig 可以處理 parquet 的資料。
$ wget http://www.oc99.org/dt/parquet-pig-bundle-1.6.0.jar
$ sudo mv parquet-pig-bundle-1.6.0.jar /opt/pig-0.17.0/lib/

# 第一行宣告 parquet 的資料用 snappy 壓縮法壓縮
# parquet.pig.ParquetStorer() ，請 pig 將資料用 parquet 的方式儲存資料
$ nano twmparquet.pig 
SET parquet.compression snappy;
a = load '/dataset/twmcompact' using PigStorage(',') as (code:chararray, name:chararray, address: chararray, tel:chararray, adult:int, child:int, dt:chararray);
rmf /dataset/twmparquet;
store a into '/dataset/twmparquet' using parquet.pig.ParquetStorer();

$ pig twmparquet.pig

$ hdfs dfs -ls -h /dataset/twmparquet
Found 29 items
-rw-r--r--   2 bigred bigboss          0 2023-02-19 02:20 /dataset/twmparquet/_SUCCESS
-rw-r--r--   2 bigred bigboss        318 2023-02-19 02:20 /dataset/twmparquet/_common_metadata
-rw-r--r--   2 bigred bigboss     19.9 K 2023-02-19 02:20 /dataset/twmparquet/_metadata
-rw-r--r--   2 bigred bigboss     10.0 M 2023-02-19 02:20 /dataset/twmparquet/part-m-00000.snappy.parquet
-rw-r--r--   2 bigred bigboss     11.1 M 2023-02-19 02:20 /dataset/twmparquet/part-m-00001.snappy.parquet
...
  • chararray | 同 SQL 的 varcher 、 hive 的 string

建立可讀取 Parquet 資料集的外部資料表 

$ echo $'DROP TABLE IF EXISTS twmparquet;
CREATE EXTERNAL TABLE twmparquet (
  code string,
  name string,
  address string,
  tel string,
  adult INT,
  child INT,
  dt string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'
STORED AS Parquet LOCATION \'/dataset/twmparquet\'
TBLPROPERTIES (\'parquet.compress\'=\'snappy\'); ' > twmparquet.hsql

$ hive -S -f twmparquet.hsql
  • STORED AS Parquet LOCATION \'/dataset/twmparquet\' | 用 pqrqet 儲存的資料來源
  • TBLPROPERTIES (\'parquet.compress\'=\'snappy\') | 告訴 hive ，parquet 的壓縮法用 snappy
  • zip 與 rar 的壓縮技術，pig 和 hive 不支援

顯示單日每一個小時的總量 

$ time hive -S -e "select substring(dt,12,8) as t, sum(adult)/10000 from twmparquet where substring(dt,1,10)='2020/04/17' group by substring(dt,12,8) order by t" 2>/dev/null

建立 “九宮格” 資料表 

$ echo $'DROP TABLE IF EXISTS twmpp;
CREATE TABLE twmpp (
  code string,
  name string,
  address string,
  tel string,
  adult INT,
  child INT,
  dt string
)
PARTITIONED BY (month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\'
STORED AS Parquet 
TBLPROPERTIES (\'parquet.compress\'=\'snappy\');' > twmpp.hsql

$ hive -S -f twmpp.hsql

[重要] twmpp 是內部資料表, 所以它的 資料 存儲目錄在 /user/$USER/hive/twmpp
  • 內部資料表，標準動作 —> 在 hdfs 產生資料夾
$ echo $'SET hive.exec.dynamic.partition.mode = nonstrict;
INSERT OVERWRITE TABLE twmpp
PARTITION (month)
SELECT code,name,address,tel,adult,child,dt,substring(dt,6,2)
FROM twmcompact;' > insertpp.hsql
  • SELECT code,name,address,tel,adult,child,dt,substring(dt,6,2) FROM twmcompact; ，從壓密資料表，選擇各個欄位的資料，多了一個月份的欄位 substring(dt,6,2)
  • INSERT OVERWRITE TABLE twmpp ，九宮格資料表
  • PARTITION (month)，Partition 會根據資料的最後一個欄位做切割，以本例子為例，用月做切割，對應在 hdfs 終究是一個資料夾的名字
  • hive.exec.dynamic.partition.mode = nonstrict ，動態產生 Partition 的資料夾

顯示單日每一個小時的總量

檢視 原始, 壓扁, Parquet 及 九宮格 目錄

tags: 資料科技平台
dt*_script 簡介Apache Spark