基本介紹

教學目標

初步了解如何在 Cloudera 的 CDH 環境使用 Impala 和 Hive 建立資料庫和查詢資料表中統計資訊。

重點概念

在實務應用中我們將會需要將 HDFS 儲存空間中的 CSV 檔案匯入至 Hive/Impala 中的資料表,以利查詢統計資訊等應用。

建立資料表

開啟 Hive 工具。

1
$ hive

首先查看目前資料庫的資訊,是否存在 hadoopexamdb 資料庫,若不存在請先建立 hadoopexamdb 資料庫。

1
> SHOW DATABASES;

使用 hadoopexamdb 資料庫。

1
> USE hadoopexamdb;

建立 region 資料表。

1
> CREATE TABLE IF NOT EXISTS region (r_regionkey smallint, r_name string, r_comment string, r_nations array<struct<n_nationkey:smallint,n_name:string,n_comment:string>>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' COLLECTION ITEMS TERMINATED BY ',' STORED AS PARQUET;

查看 region 資料表是否成功建立成功。

1
> SHOW TABLE;
建立資料表
建立資料表

在 HDFS 中建立檔案

透過 Hue 網站服務在 HDFS 儲存空間的 /user/cloudera 目錄中建立 hadoopexam_101 目錄,接著新增 region.csv 檔案。

1
/user/cloudera/hadoopexam_101/region.csv
在 HDFS 中建立檔案
在 HDFS 中建立檔案
在 HDFS 中建立檔案
在 HDFS 中建立檔案
在 HDFS 中建立檔案
在 HDFS 中建立檔案

再來編輯 region.csv 檔案。

region.csv 檔案內容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
r_regionkey|r_name|r_comment|r_nations<n_nationkey,n_name,n_comment>
1|AFRICA|Good Business Region for company.com|0,Cameroon,Reference site http://www.company.com
1|AFRICA|Good Business Region for company.com|5,Egypt,Reference site http://www.company.com
1|AFRICA|Good Business Region for company.com|14,Namibia,Reference site http://www.company.com
1|AFRICA|Good Business Region for company.com|15,Zimbabwe,Reference site http://www.company.com
1|AFRICA|Good Business Region for company.com|16,Uganda,Reference site http://www.company.com
2|AMERICA|Average Business Region for company.com|1,United States,Reference site http://www.company.com
2|AMERICA|Average Business Region for company.com|2,Canada,Reference site http://www.company.com
2|AMERICA|Average Business Region for company.com|3,Cuba,Reference site http://www.company.com
2|AMERICA|Average Business Region for company.com|17,Costa Rica,Reference site http://www.company.com
2|AMERICA|Average Business Region for company.com|24,Panama,Reference site http://www.company.com
3|ASIA|Best Business Region for company.com|8,India,Reference site http://www.company.com
3|ASIA|Best Business Region for company.com|9,China,Reference site http://www.company.com
3|ASIA|Best Business Region for company.com|12,Japan,Reference site http://www.company.com
3|ASIA|Best Business Region for company.com|18,Russia,Reference site http://www.company.com
3|ASIA|Best Business Region for company.com|21,Israel,Reference site http://www.company.com
4|EUROPE|Low sale Business Region for company.com|6,Austria,Reference site http://www.company.com
4|EUROPE|Low sale Business Region for company.com|7,Bulgaria,Reference site http://www.company.com
4|EUROPE|Low sale Business Region for company.com|19,Belgium,Reference site http://www.company.com
4|EUROPE|Low sale Business Region for company.com|22,Croatia,Reference site http://www.company.com
4|EUROPE|Low sale Business Region for company.com|23,Denmark,Reference site http://www.company.com
5|MIDDLE EAST|Ok Ok sale Business Region for company.com|4,Saudi Arabia,Reference site http://www.company.com
5|MIDDLE EAST|Ok Ok sale Business Region for company.com|10,Yemen,Reference site http://www.company.com
5|MIDDLE EAST|Ok Ok sale Business Region for company.com|11,Oman,Reference site http://www.company.com
5|MIDDLE EAST|Ok Ok sale Business Region for company.com|13,Kuwait,Reference site http://www.company.com
5|MIDDLE EAST|Ok Ok sale Business Region for company.com|20,Qatar,Reference site http://www.company.com
在 HDFS 中建立檔案
在 HDFS 中建立檔案

將 HDFS 中的檔案匯入至資料表中

建立 tempregion 資料表,主要用於暫存資料。

1
> CREATE TABLE tempregion(data string);

將 HDFS 儲存空間中的 /user/cloudera/hadoopexam_101/region.csv 檔案匯入至 tempregion 資料表中。

1
> LOAD DATA INPATH '/user/cloudera/hadoopexam_101/region.csv' INTO TABLE tempregion;
將 HDFS 中的檔案匯入至資料表中
將 HDFS 中的檔案匯入至資料表中

查詢 tempregion 資料表的資料。

1
> SELECT * FROM tempregion LIMIT 10;
將 HDFS 中的檔案匯入至資料表中
將 HDFS 中的檔案匯入至資料表中

查詢 tempregion 資料表的資料,主要根據 region 資料表的欄位進行對應,以利接下來進行匯入之前的確認。

1
> SELECT SPLIT(data,'|')[0] r_regionkey, SPLIT(data,'|')[1] r_name, SPLIT(data,'|')[2] r_comment, SPLIT(SPLIT(data,'|')[3],",")[0] n_nationkey, SPLIT(SPLIT(data,'|')[3],",")[1] n_name, SPLIT(SPLIT(data,'|')[3],",")[2] n_comment FROM tempregion;
將 HDFS 中的檔案匯入至資料表中
將 HDFS 中的檔案匯入至資料表中

將 tempregion 資料表的資料匯入至 region 資料表中。

1
> INSERT OVERWRITE TABLE region SELECT SPLIT(data,'|')[0] r_regionkey, SPLIT(data,'|')[1] r_name, SPLIT(data,'|')[2] r_comment, ARRAY(named_struct("n_nationkey", CAST(SPLIT(SPLIT(data, '|')[3],",")[0] AS SMALLINT), "n_name", SPLIT(SPLIT(data, '|')[3],",")[1] , "n_comment", SPLIT(SPLIT(data, '|')[3],",")[2])) FROM tempregion;
將 HDFS 中的檔案匯入至資料表中
將 HDFS 中的檔案匯入至資料表中

查詢 region 資料表的資料。

1
> SELECT * FROM region LIMIT 10;
將 HDFS 中的檔案匯入至資料表中
將 HDFS 中的檔案匯入至資料表中

查詢資料表中統計資訊

透過 invalidate metadata 指令將 Hive 中的資料同步至 Impala 中,然而在 Impala 中新增的資料會自動同步至 Hive 中。

開啟 Impala 工具。

1
$ impala-shell

同步更新 Hive 中的資料。

1
> invalidate metadata;
查詢資料表中統計資訊
查詢資料表中統計資訊

查詢 region 資料表中的統計資訊。

1
2
> USE hadoopexamdb;
> SELECT r_name, COUNT(r_nations.item.n_nationkey) AS count, SUM(r_nations.item.n_nationkey) AS sum, AVG(r_nations.item.n_nationkey) AS minimum, MIN(r_nations.item.n_name) AS maximum, NDV(r_nations.item.n_nationkey) AS distinct_vals FROM region, region.r_nations as r_nations GROUP BY r_name ORDER BY r_name;
查詢資料表中統計資訊
查詢資料表中統計資訊

重新更新資料表中的資料

開啟 Hive 工具。

1
$ hive

刪除 region 資料表。

1
> DROP TABLE IF EXISTS hadoopexamdb.region;

建立 region 資料表。

1
2
> USE hadoopexamdb;
> CREATE TABLE IF NOT EXISTS region (r_regionkey smallint, r_name string, r_comment string, r_nations array<struct<n_nationkey:smallint,n_name:string,n_comment:string>>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' COLLECTION ITEMS TERMINATED BY ',' STORED AS PARQUET;

將 tempregion 資料表的資料匯入至 region 資料表中。

1
> INSERT OVERWRITE TABLE hadoopexamdb.region SELECT SPLIT(data,'|')[0] r_regionkey, SPLIT(data,'|')[1] r_name, SPLIT(data,'|')[2] r_comment, ARRAY(named_struct("n_nationkey", CAST(SPLIT(SPLIT(data, '|')[3],",")[0] AS SMALLINT), "n_name", SPLIT(SPLIT(data, '|')[3],",")[1] , "n_comment", SPLIT(SPLIT(data, '|')[3],",")[2])) FROM hadoopexamdb.tempregion WHERE NOT (SPLIT(data,'|')[1] IN ('r_name'));
重新更新資料表中的資料
重新更新資料表中的資料

開啟 Impala 工具。

1
$ impala-shell

同步更新 Hive 中的資料。

1
> invalidate metadata;

查詢資料表中統計資訊。

1
2
> USE hadoopexamdb;
> SELECT r_name, COUNT(r_nations.item.n_nationkey) AS count, SUM(r_nations.item.n_nationkey) AS sum, AVG(r_nations.item.n_nationkey) AS minimum, MIN(r_nations.item.n_name) AS maximum, NDV(r_nations.item.n_nationkey) AS distinct_vals FROM region, region.r_nations as r_nations GROUP BY r_name ORDER BY r_name;
重新更新資料表中的資料
重新更新資料表中的資料

總結在此篇的練習中主要是以 Hive 進行資料表處理,主要原因是有些 SQL 語法 Impala 目前不支援,但是 Hive 支援,此時我們就可以先在 Hive 處理之後,再開啟 Impala 同步資料。此外我們更能夠透過 Hue 網站服務透過操作介面新建目錄和檔案至 HDFS 儲存空間中,接著再透過 Hive 將 HDFS 儲存空間中的 CSV 檔案匯入成資料表,以利查詢統計資訊等應用。

相關資源