Hive的學習，Hive的DDL和DML

Hive數據類型

之前文中講解了Hive的基本的配置，本篇講解Hive的其他的知識點。

基本數據類型

對於Hive的String類型相當於資料庫的varchar類型，該類型是一個可變的字符串，不過它不能聲明其中最多能存儲多少個字符，理論上它可以存儲2GB的字符數。

集合數據類型

Hive有三種複雜數據類型ARRAY、MAP 和 STRUCT。ARRAY和MAP與Java中的Array和Map類似，而STRUCT與C語言中的Struct類似，它封裝了一個命名欄位集合，複雜數據類型允許任意層次的嵌套。

案例實操

1） 假設某表有如下一行，我們用JSON格式來表示其數據結構。在Hive下訪問的格式為

{ "name": "songsong", "friends": ["bingbing" , "lili"] , //列表Array, "children": { //鍵值Map, "xiao song": 18 , "xiaoxiao song": 19 } "address": { //結構Struct, "street": "hui long guan" , "city": "beijing" }}

2）基於上述數據結構，我們在Hive裡創建對應的表，並導入數據。

創建本地測試文件test.txt

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijingyangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

注意：MAP，STRUCT和ARRAY裡的元素間關係都可以用同一個字符表示，這裡用「_」。

3）Hive上創建測試表test

3）Hive上創建測試表testcreate table test(name string,friends array<string>,children map<string, int>,address struct<street:string, city:string>)row format delimited fields terminated by ','collection items terminated by '_'map keys terminated by ':'lines terminated by '\n';

欄位解釋：

row format delimited fields terminated by ',' -- 列分隔符

collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(數據分割符號)

map keys terminated by ':' -- MAP中的key與value的分隔符

lines terminated by '\n'; -- 行分隔符

4）導入文本數據到測試表

hive (default)> load data local inpath 『/opt/module/datas/test.txt』into table test

5）訪問三種集合列裡的數據，以下分別是ARRAY，MAP，STRUCT的訪問方式

hive (default)> select friends[1],children['xiao song'],address.city from testwhere name="songsong";OK_c0 _c1 citylili 18 beijingTime taken: 0.076 seconds, Fetched: 1 row(s)

類型轉化

Hive的原子數據類型是可以進行隱式轉換的，類似於Java的類型轉換，例如某表達式使用INT類型，TINYINT會自動轉換為INT類型，但是Hive不會進行反向轉化，例如，某表達式使用TINYINT類型，INT不會自動轉換為TINYINT類型，它會返回錯誤，除非使用CAST操作。

1．隱式類型轉換規則如下

（1）任何整數類型都可以隱式地轉換為一個範圍更廣的類型，如TINYINT可以轉換成INT，INT可以轉換成BIGINT。

（2）所有整數類型、FLOAT和STRING類型都可以隱式地轉換成DOUBLE。

（3）TINYINT、SMALLINT、INT都可以轉換為FLOAT。

（4）BOOLEAN類型不可以轉換為任何其它的類型。

2．可以使用CAST操作顯示進行數據類型轉換

例如CAST('1' AS INT)將把字符串'1' 轉換成整數1；如果強制類型轉換失敗，如執行CAST('X' AS INT)，表達式返回空值 NULL。

DDL數據定義

創建資料庫

CREATE DATABASE [IF NOT EXISTS] database_name[COMMENT database_comment][LOCATION hdfs_path][WITH DBPROPERTIES (property_name=property_value, ...)];

1）創建一個資料庫，資料庫在HDFS上的默認存儲路徑是/user/hive/warehouse/*.db。

hive (default)> create database db_hive;

2）避免要創建的資料庫已經存在錯誤，增加if not exists判斷。（標準寫法）

hive (default)> create database db_hive;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists

hive (default)> create database if not exists db_hive;

3）創建一個資料庫，指定資料庫在HDFS上存放的位置

hive (default)> create database db_hive2 location '/db_hive2.db';

查詢資料庫

顯示資料庫

1．顯示資料庫

hive> show databases;

2．過濾顯示查詢的資料庫

hive> show databases like 'db_hive*';OKdb_hivedb_hive_1

查看資料庫詳情

1．顯示資料庫信息

hive> desc database db_hive;OKdb_hive hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db atguiguUSER

2．顯示資料庫詳細信息，extended

hive> desc database extended db_hive;OKdb_hive hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db atguiguUSER

切換當前資料庫

hive (default)> use db_hive;

修改資料庫

用戶可以使用ALTER DATABASE命令為某個資料庫的DBPROPERTIES設置鍵-值對屬性值，來描述這個資料庫的屬性信息。資料庫的其他元數據信息都是不可更改的，包括資料庫名和資料庫所在的目錄位置。

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');

在hive中查看修改結果

hive> desc database extended db_hive;db_name comment location owner_name owner_type parametersdb_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db atguigu USER {createtime=20170830}

刪除資料庫

1．刪除空資料庫

hive>drop database db_hive2;

2．如果刪除的資料庫不存在，最好採用 if exists判斷資料庫是否存在

hive> drop database db_hive;FAILED: SemanticException [Error 10072]: Database does not exist: db_hivehive> drop database if exists db_hive2;

3．如果資料庫不為空，可以採用cascade命令，強制刪除

hive> drop database db_hive;FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)hive> drop database db_hive cascade;

創建表

1．建表語法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name[(col_name data_type [COMMENT col_comment], ...)][COMMENT table_comment][PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)][CLUSTERED BY (col_name, col_name, ...)[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS][ROW FORMAT row_format][STORED AS file_format][LOCATION hdfs_path][TBLPROPERTIES (property_name=property_value, ...)][AS select_statement]

2．欄位解釋說明

（1）CREATE TABLE 創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常；用戶可以用 IF NOT EXISTS 選項來忽略這個異常。

（2）EXTERNAL關鍵字可以讓用戶創建一個外部表，在建表的同時可以指定一個指向實際數據的路徑（LOCATION），在刪除表的時候，內部表的元數據和數據會被一起刪除，而外部表只刪除元數據，不刪除數據。

（3）COMMENT：為表和列添加注釋。

（4）PARTITIONED BY創建分區表

（5）CLUSTERED BY創建分桶表

（6）SORTED BY不常用，對桶中的一個或多個列另外排序

（7）

ROW FORMAT  DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用戶在建表的時候可以自定義SerDe或者使用自帶的SerDe。如果沒有指定ROW FORMAT 或者ROW FORMAT DELIMITED，將會使用自帶的SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的SerDe，Hive通過SerDe確定表的具體的列的數據。

SerDe是Serialize/Deserilize的簡稱， hive使用Serde進行行對象的序列與反序列化。

（8）STORED AS指定存儲文件類型

常用的存儲文件類型：SEQUENCEFILE（二進位序列文件）、TEXTFILE（文本）、RCFILE（列式存儲格式文件）

如果文件數據是純文本，可以使用STORED AS TEXTFILE。如果數據需要壓縮，使用 STORED AS SEQUENCEFILE。

（9）LOCATION ：指定表在HDFS上的存儲位置。

（10）AS：後跟查詢語句，根據查詢結果創建表。

（11）LIKE允許用戶複製現有的表結構，但是不複製數據。

管理表

1．理論

默認創建的表都是所謂的管理表，有時也被稱為內部表。因為這種表，Hive會（或多或少地）控制著數據的生命周期。Hive默認情況下會將這些表的數據存儲在由配置項hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定義的目錄的子目錄下。 當我們刪除一個管理表時，Hive也會刪除這個表中數據。管理表不適合和其他工具共享數據。

2．案例實操

（1）普通創建表

create table if not exists student2(id int, name string)row format delimited fields terminated by '\t'stored as textfilelocation '/user/hive/warehouse/student2';

（2）根據查詢結果創建表（查詢的結果會添加到新創建的表中）

create table if not exists student3 as select id, name from student;

（3）根據已經存在的表結構創建表

create table if not exists student4 like student;

（4）查詢表的類型

hive (default)> desc formatted student2;Table Type:             MANAGED_TABLE  

外部表

1．理論

因為表是外部表，所以Hive並非認為其完全擁有這份數據。刪除該表並不會刪除掉這份數據，不過描述表的元數據信息會被刪除掉。

2．管理表和外部表的使用場景

每天將收集到的網站日誌定期流入HDFS文本文件。在外部表（原始日誌表）的基礎上做大量的統計分析，用到的中間表、結果表使用內部表存儲，數據通過SELECT+INSERT進入內部表。

3．案例實操

分別創建部門和員工外部表，並向表中導入數據。

（1） 上傳數據到HDFS

hive (default)> dfs -mkdir /student;

hive (default)> dfs -put /opt/module/datas/student.txt /student;

（2）建表語句

創建外部表

hive (default)> create external table stu_external(

id int,

name string)

row format delimited fields terminated by '\t'

location '/student';

（3）查看創建的表

hive (default)> select * from stu_external;OKstu_external.id stu_external.name1001    lisi1002    wangwu1003    zhaoliu

（4）查看表格式化數據

hive (default)> desc formatted dept;Table Type:             EXTERNAL_TABLE

（5）刪除外部表

hive (default)> drop table stu_external;

外部表刪除後，hdfs中的數據還在，但是metadata中stu_external的元數據已被刪除

管理表與外部表的互相轉換

（1）查詢表的類型

hive (default)> desc formatted student2;

Table Type: MANAGED_TABLE

（2）修改內部表student2為外部表

alter table student2 set tblproperties('EXTERNAL'='TRUE');

（3）查詢表的類型

hive (default)> desc formatted student2;Table Type: EXTERNAL_TABLE

（4）修改外部表student2為內部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

（5）查詢表的類型

hive (default)> desc formatted student2;Table Type:             MANAGED_TABLE

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')為固定寫法，區分大小寫！

分區表

分區表實際上就是對應一個HDFS文件系統上的獨立的文件夾，該文件夾下是該分區所有的數據文件。Hive中的分區就是分目錄，把一個大的數據集根據業務需要分割成小的數據集。在查詢時通過WHERE子句中的表達式選擇查詢所需要的指定的分區，這樣的查詢效率會提高很多。

分區表基本操作

1．引入分區表（需要根據日期對日誌進行管理）

/user/hive/warehouse/log_partition/20170702/20170702.log/user/hive/warehouse/log_partition/20170703/20170703.log/user/hive/warehouse/log_partition/20170704/20170704.log

2．創建分區表語法

hive (default)> create table dept_partition(deptno int, dname string, loc string)partitioned by (month string)row format delimited fields terminated by '\t';

注意：分區欄位不能是表中已經存在的數據，可以將分區欄位看作表的偽列。

3．加載數據到分區表中

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201708');hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201707』);

注意：分區表加載數據時，必須指定分區

4．查詢分區表中數據

單分區查詢

hive (default)> select * from dept_partition where month='201709';

多分區聯合查詢

hive (default)> select * from dept_partition where month='201709' union select * from dept_partition where month='201708' union select * from dept_partition where month='201707';_u3.deptno _u3.dname _u3.loc _u3.month10 ACCOUNTING NEW YORK 20170710 ACCOUNTING NEW YORK 20170810 ACCOUNTING NEW YORK 20170920 RESEARCH DALLAS 20170720 RESEARCH DALLAS 20170820 RESEARCH DALLAS 20170930 SALES CHICAGO 20170730 SALES CHICAGO 20170830 SALES CHICAGO 20170940 OPERATIONS BOSTON 20170740 OPERATIONS BOSTON 20170840 OPERATIONS BOSTON 201709

5．增加分區

創建單個分區

hive (default)> alter table dept_partition add partition(month='201706') ;

同時創建多個分區

hive (default)> alter table dept_partition add partition(month='201705') partition(month='201704');

6．刪除分區

刪除單個分區

hive (default)> alter table dept_partition drop partition (month='201704');

同時刪除多個分區

hive (default)> alter table dept_partition drop partition (month='201705'), partition (month='201706');

7．查看分區表有多少分區

hive> show partitions dept_partition;

8．查看分區表結構

hive> desc formatted dept_partition; # Partition Information          # col_name              data_type               comment             month                   string    

分區表注意事項

1．創建二級分區表

hive (default)> create table dept_partition2( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';

2．正常的加載數據

（1）加載數據到二級分區表中

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201709', day='13');

（2）查詢分區數據

hive (default)> select * from dept_partition2 where month='201709' and day='13';

3．把數據直接上傳到分區目錄上，讓分區表和數據產生關聯的三種方式

（1）方式一：上傳數據後修復

上傳數據

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;

查詢數據（查詢不到剛上傳的數據）

hive (default)> select * from dept_partition2 where month='201709' and day='12';

執行修復命令

hive> msck repair table dept_partition2;

再次查詢數據

hive (default)> select * from dept_partition2 where month='201709' and day='12';

（2）方式二：上傳數據後添加分區

上傳數據

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=11;hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=11;

執行添加分區

hive (default)> alter table dept_partition2 add partition(month='201709', day='11');

查詢數據

hive (default)> select * from dept_partition2 where month='201709' and day='11';

（3）方式三：創建文件夾後load數據到分區

創建目錄

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;

上傳數據

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');

查詢數據

hive (default)> select * from dept_partition2 where month='201709' and day='10';

修改表

重命名表

1．語法

ALTER TABLE table_name RENAME TO new_table_name

2．實操案例

hive (default)> alter table dept_partition2 rename to dept_partition3;

增加/修改/替換列信息

1．語法

更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替換列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

註：ADD是代表新增一欄位，欄位位置在所有列後面(partition列前)，REPLACE則是表示替換表中所有欄位。

2．實操案例

（1）查詢表結構

hive> desc dept_partition;

（2）添加列

hive (default)> alter table dept_partition add columns(deptdesc string);

（3）查詢表結構

hive> desc dept_partition;

（4）更新列

hive (default)> alter table dept_partition change column deptdesc desc int;

（5）查詢表結構

hive> desc dept_partition;

（6）替換列

hive (default)> alter table dept_partition replace columns(deptno string, dname string, loc string);

（7）查詢表結構

hive> desc dept_partition;

刪除表

hive (default)> drop table dept_partition;

DML數據操作

數據導入

向表中裝載數據（Load）

1．語法

hive> load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

（1）load data:表示加載數據

（2）local:表示從本地加載數據到hive表；否則從HDFS加載數據到hive表

（3）inpath:表示加載數據的路徑

（4）overwrite:表示覆蓋表中已有數據，否則表示追加

（5）into table:表示加載到哪張表

（6）student:表示具體的表

（7）partition:表示上傳到指定分區

2．實操案例

（0）創建一張表

hive (default)> create table student(id string, name string) row format delimited fields terminated by '\t';

（1）加載本地文件到hive

hive (default)> load data local inpath '/opt/module/datas/student.txt' into table default.student;

（2）加載HDFS文件到hive中

上傳文件到HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/hadoop/hive;

加載HDFS上數據

hive (default)> load data inpath '/user/atguigu/hive/student.txt' into table default.student;

（3）加載數據覆蓋表中已有的數據

上傳文件到HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/hadoop/hive;

加載數據覆蓋表中已有的數據

hive (default)> load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student;

通過查詢語句向表中插入數據（Insert）

1．創建一張分區表

hive (default)> create table student(id int, name string) partitioned by (month string) row format delimited fields terminated by '\t';

2．基本插入數據

hive (default)> insert into table  student partition(month='201709') values(1,'wangwu'),(2,』zhaoliu』);

3．基本模式插入（根據單張表查詢結果）

hive (default)> insert overwrite table student partition(month='201708')             select id, name from student where month='201709';insert into：以追加數據的方式插入到表或分區，原有數據不會刪除insert overwrite：會覆蓋表或分區中已存在的數據

注意：insert不支持插入部分欄位

4．多表（多分區）插入模式（根據多張表查詢結果）

hive (default)> from student              insert overwrite table student partition(month='201707')              select id, name where month='201709'              insert overwrite table student partition(month='201706')              select id, name where month='201709';

查詢語句中創建表並加載數據（As Select）

根據查詢結果創建表（查詢的結果會添加到新創建的表中）

create table if not exists student3as select id, name from student;

創建表時通過Location指定加載數據路徑

1．上傳數據到hdfs上

hive (default)> dfs -mkdir /student;hive (default)> dfs -put /opt/module/datas/student.txt /student;

2. 創建表，並指定在hdfs上的位置

hive (default)> create external table if not exists student5(              id int, name string              )              row format delimited fields terminated by '\t'              location '/student;

3．查詢數據

hive (default)> select * from student5;

Import數據到指定Hive表中

注意：先用export導出後，再將數據導入。

hive (default)> import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

數據導出

Insert導出

1．將查詢的結果導出到本地

hive (default)> insert overwrite local directory '/opt/module/datas/export/student'            select * from student;

2．將查詢的結果格式化導出到本地

hive(default)>insert overwrite local directory '/opt/module/datas/export/student1'           ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;

3．將查詢的結果導出到HDFS上(沒有local)

hive (default)> insert overwrite directory '/user/hadoop/student2'             ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;

Hadoop命令導出到本地

hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0/opt/module/datas/export/student3.txt;

Hive Shell 命令導出

基本語法：（hive -f/-e 執行語句或者腳本 > file）

bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;

Export導出到HDFS上

defahiveult)> export table default.student to '/user/hive/warehouse/export/student';

export和import主要用於兩個Hadoop平臺集群之間Hive表遷移。

清除表中數據（Truncate）

注意：Truncate只能刪除管理表，不能刪除外部表中數據

hive (default)> truncate table student;

下個篇將繼續講解hive的查詢和內置函數以及怎麼自定義函數。