1.查看mysql中metastore數(shù)據(jù)存儲結(jié)構(gòu)

Metastore中只保存了表的描述信息（名字，列，類型，對應目錄）

使用SQLYog連接itcast05 的mysql數(shù)據(jù)庫

這里寫圖片描述

查看hive數(shù)據(jù)庫的表結(jié)構(gòu)：

這里寫圖片描述

2.建表(默認是內(nèi)部表（先建表忿项，后有數(shù)據(jù)）)

（建表時必須指定列的分隔符）

create table trade_detail(
id bigint, 
account string, 
income double, 
expenses double, 
time string) 
row format delimited fields terminated by '\t';

3.Hive狀態(tài)下執(zhí)行Hadoop hdfs命令

在使用hive shell 的時候，我們有時候需要操作hdfs
Hive為我們提供了在hive命令行下hdfs的shell：
城舞，例如：

dfs  -ls  /; 
dfs -mkdir  /data;
dfs  -put  /root/student.txt;

用法和hdfs下是一樣的轩触，只是細微的差別
和Hadoop命令稍微有些差別,前面是dfs開頭，后面以“;”結(jié)尾

4.創(chuàng)建–外部表（先有數(shù)據(jù)家夺，后建表）

先上傳數(shù)據(jù)文件　a.txt b.txt 到hdfs:/data目錄下脱柱，
a.txt 和 b.txt 中的內(nèi)容都是：

這里寫圖片描述

后執(zhí)行創(chuàng)建表的命令：

create external table ext_student (
id int,
name string) 
row format delimited fields terminated by '\t' 
location '/data';

創(chuàng)建完成后使用命令：select * from ext_student;　查看表中內(nèi)容：

這里寫圖片描述

再次上傳數(shù)據(jù)文件　pep.avi　

這里寫圖片描述

到　hdfs:/data　目錄下，后執(zhí)行全表掃描：select * from ext_student;

這里寫圖片描述

說明：只要將這個數(shù)據(jù)放到　hdfs:/data　表所指定的目錄下拉馋，hive就能將這個表中的數(shù)據(jù)讀取出來（內(nèi)部表和外部表都支持榨为，但也存在特殊情況讀不出）

為什么把文件丟到對應目錄下就能把數(shù)據(jù)讀出來？

答：因為metastore記錄了這張表和數(shù)據(jù)的映射關系

SDS表中的內(nèi)容：

這里寫圖片描述

5.創(chuàng)建–分區(qū)表

建分區(qū)表是為了提高數(shù)據(jù)的查詢效率煌茴，按照省份随闺、年份、月份等分區(qū)

創(chuàng)建一個外部分區(qū)表(External Table )：
（表名：beauties 指向文件：beauty）

create external table beauties (
id bigint, 
name string, 
size double) 
partitioned by (nation string) 
row format delimited fields terminated by ‘\t’ 
location ‘/beauty’ ;

show create table beauties;  

執(zhí)行完成之后發(fā)現(xiàn)hdfs根目錄下有beauty文件夾蔓腐。

準備好3個數(shù)據(jù)文件： b.c b.j b.a

這里寫圖片描述

這里寫圖片描述

這里寫圖片描述

載入數(shù)據(jù)文件矩乐，同時指定分區(qū)：

load data local inpath '/root/b.c' into table beauties partition (nation='China');

查看表中是否成功load數(shù)據(jù)：

這里寫圖片描述

突發(fā)奇想：能否像平常使用外部表一樣，在 hdfs:/beauty 目錄下創(chuàng)建一個文件夾 nation=Japan ,然后將b.j 文件上傳到這個目錄下,數(shù)據(jù)就可以查出來了回论？

答：不行散罕！ 因為在載入數(shù)據(jù)的時候，metastore是不知道你將這個文件放到 /beauty/nation=Japan/ 目錄下的傀蓉。

拯救方法：通知hive在元數(shù)據(jù)庫中添加一個beauties表的分區(qū)記錄

alter table beauties add partition (nation=’Japan’) location “/beauty/nation=Japan/”

添加分區(qū)后笨使，metastore中SDS表多了一條 記錄：

這里寫圖片描述

再次查詢beauties表，發(fā)現(xiàn)b.j中的數(shù)據(jù)也能查詢出來了：

分區(qū)表的使用優(yōu)勢：

select * from beauties where nation=’China’;

在數(shù)據(jù)量很大的時候僚害，建分區(qū)表可以提高查詢效率硫椰，就不需要將整張表數(shù)據(jù)篩選對比之后再輸出，因為數(shù)據(jù)在hdfs中直接是以分區(qū)存儲的萨蚕，所以使用類似”nation”等分區(qū)字段是可以直接把數(shù)據(jù)取出的

刪除分區(qū)：

alter table beauties drop if exists partition (nation ='Japan') ;

注：這里的 if exists 字段呢靶草，是一個檢查分區(qū)是否存在的字段，存在則刪除岳遥，不存在也不會報錯說分區(qū)不存在啦

建內(nèi)部分區(qū)表(Managed Table)

create table td_part(
id bigint, 
account string, 
income double, 
expenses double, 
time string) 
partitioned by (logdate string) 
row format delimited fields terminated by '\t';

普通表和分區(qū)表區(qū)別：有大量數(shù)據(jù)增加的需要建分區(qū)表

create table book (
id bigint, 
name string) 
partitioned by (pubdate string) 
row format delimited fields terminated by '\t'; 

分區(qū)表加載數(shù)據(jù)
（hive自己的語法）

load data local inpath './book.txt' 
overwrite into table book 
partition (pubdate='2010-08-22');

local inpath –>從本地磁盤加載奕翔，不是hdfs

overwrite –>以覆蓋的方式將數(shù)據(jù)寫入book表中

以下創(chuàng)建表的方式少了“overwrite”，則是以追加方式將數(shù)據(jù)加載到hive表中：

load data local inpath '/root/data.am' 
into table beauty 
partition (nation="USA");

使用分區(qū)字段查詢表中的數(shù)據(jù)

select nation, avg(size) from beauties group by nation order by avg(size);

6. 表關聯(lián)查詢

查詢舉例：
需求：
　　對 trade_detail 按照賬戶進行分組浩蓉，求出每個賬戶的總支出總結(jié)余派继，然后和 user_info 進行表關聯(lián)宾袜，取出名稱。

在mysql中一條查詢語句就能完成關聯(lián)查詢：

select t.account,u.name,t.income, t.expenses, t.surplus 
from user_info u join (
    select account,sum(income) as income,sum(expenses) as expenses,sum(income-expenses) as surplus 
    from trade_detail group by account 
) t 
on u.account = t.account

但是數(shù)據(jù)量一大驾窟，這個查詢過程將變得極其漫長

所以我們使用hive來完成：

a) 首先要將2張表中的數(shù)據(jù)導入hdfs中庆猫，同樣，我們也可以將mysql中的數(shù)據(jù)直接導入到hive表里面：

Mysql中的表：
trade_detail表：

這里寫圖片描述

user_info表：

這里寫圖片描述

b) 在hive中創(chuàng)建表

trade_detail表：

create table trade_detail (
id bigint,
account string,
income string,
expenses string ,
times string) 
row format delimited fields terminated by ‘\t’;

user_info表：

create table user_info (
id int,
account string,
name string,
age int) 
row format delimited fields terminated by ‘\t’;

c) 使用Sqoop 將mysql中trade_detail的數(shù)據(jù)導入hive中

./sqoop import 
--connect jdbc:mysql://192.168.1.102:3306/itcast 
--username root 
--password 123 
--table trade_detail 
--hive-import 
--hive-overwrite 
--hive-table trade_detail 
--fields-terminated-by '\t';

可能會出現(xiàn)如下的錯誤：

這里寫圖片描述

原因是沒有將hive添加到環(huán)境變量：
解決：
１）編輯　/etc/profile　文件绅络，添加HIVE_HOME：

這里寫圖片描述

２）source /etc/profile　刷新配置

３）使用 which 命令查看是否添加成功：

這里寫圖片描述

ok
４）再次執(zhí)行sqoop命令月培，發(fā)現(xiàn)sqoop導入正在執(zhí)行，可以看到map-reduce工作正在執(zhí)行恩急，在web瀏覽器上查看執(zhí)行完成之后的結(jié)果文件：

這里寫圖片描述

Sqoop導入執(zhí)行成功杉畜！

d) 使用Sqoop 將mysql中user_info的數(shù)據(jù)導入hive的user_info中

./sqoop import 
--connect jdbc:mysql://192.168.1\. 102:3306/itcast 
--username root 
--password 123 
--table user_info 
--hive-import 
--hive-overwrite 
--hive-table user_info 
--fields-terminated-by '\t';

e) hive執(zhí)行關聯(lián)查詢語句之后的結(jié)果：

select t.account,u.name,t.income, t.expenses, t.surplus 
from user_info u join (
    select account,sum(income) as income,sum(expenses) as expenses,sum(income-expenses) as surplus 
    from trade_detail group by account 
) t 
on u.account = t.account;

這里寫圖片描述