Hive（一）資料型別以及DDL資料定義

一年都在冬眠發表於2024-09-02
Hive資料型別

Hive資料型別

一、基本資料型別

Hive資料型別 Java資料型別
TINYINT byte
SMALINT short
INT int
BIGINT long
BOOLEAN boolean
FLOAT float
DOUBLE double
STRING string
TIMESTAMP
BINARY

對於Hive的String型別相當於資料庫的varchar型別，該型別是一個可變的字串。

二、集合資料型別

資料型別 描述 示例
STRUCT 相當於java語言當中沒有方法的物件，只有屬性。例如，如果某個列的資料型別是STRUCT{first STRING, last STRING},那麼第1個元素可以透過欄位.first來引用。 struct()
MAP MAP是一組鍵-值對元組集合，使用陣列表示法可以訪問資料。例如，如果某個列的資料型別是MAP，其中鍵->值對是'first'->'John'和'last'->'Doe'，那麼可以透過欄位名['last']獲取最後一個元素 map()
ARRAY 陣列是一組具有相同型別和名稱的變數的集合。這些變數稱為陣列的元素，每個陣列元素都有一個編號，編號從零開始。例如，陣列值為[‘John’, ‘Doe’]，那麼第2個元素可以透過陣列名[1]進行引用。 array()

三、型別轉化

  • 可以使用CAST操作顯示進行資料型別轉換
    CAST('1' AS INT)將把字串'1'轉換成整數1；如果強制型別轉換失敗，如執行CAST('X' AS INT)，表示式返回空值NULL。

DDL資料定義

一、建立資料庫

  • 建立一個資料庫，資料庫在HDFS上的預設儲存路徑是/opt/hive/warehouse/*.db
    create database if not exists db_name;
  • 建立一個資料庫，指定資料庫在HDFS上存放的位置
    create database if not exists db_name location 'hdfs路徑';

二、查詢資料庫

  • 顯示資料庫
    show databases;
  • 過濾顯示查詢的資料庫
    show databases like 'hivetest*';
  • 檢視資料庫詳情
    desc database hivetest;
  • 切換當前資料庫
    use 目標資料庫名稱;

三、刪除資料庫

  • 刪除空資料庫
    drop database 資料庫名稱;
  • 如果刪除的資料庫不存在，最好採用if exists判斷資料庫是否存在
    drop database if exists 資料庫名稱;
  • 如果資料庫不為空，可以採用cascade命令強制刪除
    drop database 資料庫名稱 cascade;

四、建立表

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]

補充
在hive中建表時，需要決定如何儲存和讀取表中的資料。資料在儲存時需要被轉換成一種格式（序列化），讀取時需要將這種格式再轉換成我們能理解的資料（反序列化）。為了完成這個轉化，hive使用了一個叫做SerDe的工具。

  • CREATE TABLE建立一個指定名字的表。如果相同名字的表已經存在，則丟擲異常；使用者可以用IF NOT EXISTS選項來忽略這個異常。
  • EXTERNAL關鍵字可以讓使用者建立一個外部表，在建表的同時指定一個指向實際資料的路徑（LOCATION），Hive建立內部表時，會將資料移動到資料倉儲指向的路徑；若建立外部表，僅記錄資料所在的路徑，不對資料的位置做任何改變。在刪除表的時候，內部表的後設資料和資料會被一起刪除，而外部表只刪除後設資料，不刪除資料。
  • COMMENT：為表和列新增註釋。
  • PARTITIONED BY建立分割槽表
  • CLUSTERED BY建立分桶表
  • SORTED BY不常用
  • ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]：透過定義ROW FORMAT來告訴hive我們想要自定義資料的儲存格式或者用特定的方式儲存複雜的資料結構，例如指定了欄位（列）之間的分隔符、集合中各個元素之間的分隔符、記錄（行）之間的分隔符
  • STORED AS指定儲存檔案型別
    常用的儲存檔案型別：SEQUENCEFILE（二進位制序列檔案）、TEXTFILE（文字）、RCFILE（列式儲存格式檔案）
    如果檔案資料是純文字，可以使用STORED AS TEXTFILE。
    如果資料需要壓縮，使用STORED AS SEQUENCEFILE。
  • LOCATION：指定表在HDFS上的儲存位置
  • LIKE允許使用者複製現有的表結構，但是不復制資料

1、內部表
預設建立的表都是所謂的管理表，有時也被稱為內部表。Hive預設情況下會將這些表的資料儲存在由配置項hive.metastore.warehouse.dir(例如，/opt/hive/warehouse)所定義的目錄的子目錄下。刪除一個管理表時，Hive也會刪除這個表中資料，管理表不適合和其他工具共享資料。

  • 普通建表
create table if not exists student2(
id int, name string
)
row format delimited fields terminated by '\t';
  • 根據查詢結果建立表（查詢的結果會新增到新建立的表中）
create table if not exists student3 as select id, name from student;
  • 根據已存在的表結構建立表
create table if not exists student4 like student;
  • 查詢表的型別
desc formatted student2;

2、外部表
刪除外部表並不會刪除掉這份資料，不過表述表的後設資料資訊會被刪除掉。
（1）管理表和外部表的使用場景
假設一家電商公司的資料分析工程師，需要處理和分析大量的使用者訂單資料，這些資料每天都會生成，並且需要進行負責的分析和報告。建立管理表來儲存最近一個月的使用者訂單資料，這些資料需要定期更新，舊資料將會被新資料替換。建立外部表來儲存所有的歷史訂單資料，這些資料由另一個團隊管理，並且儲存在一個共享的HFDS中，該工程師需要對這些資料進行查詢和分析，不需要管理資料的儲存。
（2）管理表和外部表的相互轉換

  • 修改內部表student為外部表
alter table student set tblproperties('EXTERNAL'='TRUE');
  • 修改外部表student為內部表
alter table student set tblproperties('EXTERNAL'='FALSE');

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

五、分割槽表

對應一個HDFS檔案系統上的獨立的資料夾，該資料夾下是該分割槽所有的資料檔案。Hive中的分割槽就是分目錄，把一個大的資料集根據業務需要分割成小的資料集。在查詢時透過WHERE子句中的表示式選擇查詢所需要的指定的分割槽，這樣的查詢效率會提高很多。
1、建立分割槽表語法

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

建立了一個分割槽表dept_partition，其中包含三個欄位：部門編號（deptno）、部門名稱（dname）和部門位置（loc）。此外，表透過month欄位進行分割槽。
2、載入資料到分割槽表

load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201707’);
load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201708’);
load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201709’);

3、查詢分割槽表中的資料

  • 單分割槽查詢
select * from dept_partition where month='201709';
  • 多分割槽聯合查詢
select * from dept_partition where month='201709'
union
select * from dept_partition where month='201708'
union
select * from dept_partition where month='201707';

4、增加分割槽

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

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

5、刪除分割槽

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

alter table dept_partition drop partition (month='201705'), partition (month='201706')

6、檢視分割槽表有多少分割槽

show partitions dept_partition;

7、檢視分割槽表結構

desc formatted dept_partition;

六、修改表

1、重新命名錶

ALTER TABLE table_name RENAME TO new_table_name

2、增加/修改/替換列資訊
（1）更新列

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

（2）增加和替換列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  • ADD是代表新增一欄位，欄位位置在所有列後面(partition列前)，REPLACE則是表示替換表中所有欄位。

3、刪除表

drop table dept_partition;
  • 外部表不能簡單的透過這個命令刪除，這個命令只能刪除外部表的後設資料，沒有辦法刪除HDFS上面的資料
  • 徹底刪除外部表的方法：

（1）轉換為內部表再刪除

ALTER TABLE xxx SET TBLPROPERTIES('EXTERNAL'='False');
drop table xxx;

（2）刪除後設資料，使用HDFS刪除資料

相關文章