hive02_SQL操作

Stitches發表於2024-07-26
HiveSQL

Hive DDL 操作

操作前需要保證 hive 成功啟動：

# 啟動 HiveServer2
hive --service hiveserver2 &
# 啟動 MetaStore
hive --service metastore &

# 進入 hive 命令列介面
beeline -u jdbc:hive2://node03:10000 -n root

資料庫操作

操作類似於 MySQL 資料庫操作。

表基本操作

hive 中表資料涉及兩部分，一部分是儲存在 HDFS 上的真實資料；一部分是儲存在 MetaStore 中的後設資料（包含表結構、許可權、屬性等資訊）。
hive 包括內表和外表，其中內表主要使用的表形式，外表一般作為資料匯入時的臨時表使用。內表對 HDFS 上的資料有所有權，而外表對 HDFS 上儲存的資料沒有所有權，僅有讀許可權。

為什麼需要外表？

  • 一般結構化資料到達 Hadoop 平臺時，可能存在編碼、換行符、髒資料的問題，不能直接匯入到 Hive 中儲存為內表。於是再資料接入之前先建立外表，指定表的後設資料資訊（create table t1(col1 type, col2 type ..)），因為有讀的許可權，可以從外表中查詢資料，以確保後設資料資訊是否正確，編碼是否合理。
  • 確保資料合理性前提下，再建立內表，將外表資料查詢出來，匯入到內表中。

外表建立語法：

CREATE EXTERNAL TABLE <table_name>　
    (<col_name> <data_type> [, <col_name> <data_type> ...])
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY 'xxx'
    COLLECTION ITEMS TERMINATED BY 'xxx'
    MAP KEYS TERMINATED BY 'xxx'
    LINES TERMINATED BY 'xxx'
    LOCATION '<file_path>';
  • ROW FORMAT DELIMITED：標誌開始設定分隔符；
  • FIELDS TERMINATED BY 'xxx'：資料的欄位透過 'xxx' 分割開；
  • COLLECTION ITEMS TERMINATED BY 'xxx'：複雜型別資料透過 'xxx' 分割；
  • MAP KEYS TERMINATED BY 'xxx'：複雜元素 MAP 透過 xxx 分割；
  • LINES TERMINATED BY 'xxx'：定義行分隔符；
  • LOCATION '<file_path>'：指定資料在 HDFS 上的儲存路徑；

資料建立及匯入示例：
源資料：

1,tom,18,music-game-driver,std_addr
2,jolin,21,music-movie,std_addr:beijing-work_addr:shanghai-addr:tokyo
3,tony,33,book-game-food,std_addr:beijing-work_addr:xian
4,lilei,12,scl_addr:xizhimen-home_addr:null
5,hanmeimei,12,scl_addr:xizhimen
6,baby,3,food,addr:tianjing

hadoop 上傳源資料檔案到 HDFS：

hadoop fs -mkdir -p /tmp/hive_data/psn
hadoop fs -put psn.txt /tmp/hive_data/psn/

建立 hive 外表：

use default;
CREATE EXTERNAL TABLE ext_psn(
    id INT comment 'ID',
    name STRING comment '姓名',
    age INT comment '年齡',
    likes ARRAY<STRING> comment '愛好',
    address MAP<STRING,STRING> comment '地址'
)comment '人員資訊表'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':' 
LINES TERMINATED BY '\n'
LOCATION '/tmp/hive_data/psn/';

img
建立 hive 內表：

CREATE TABLE inner_psn(
    id INT comment 'ID',
    name STRING comment '姓名',
    age INT comment '年齡',
    likes ARRAY<STRING> comment '愛好',
    address MAP<STRING<STRING> comment '地址'
) comment '人員資訊表';
  • 內表的資料預設存放到 /user/hive/warehouse/${db_name}.db/${table_name} 目錄下，若使用 default 資料庫，預設存放到 /user/hive/warehouse/${table_name}；
  • 內表建立時也可以指定 LOCATION 資料存放的位置，用於資料的讀取；不同的是外表 LOCATION 是必須定義的選項，內表 LOCATION 用於更改資料檔案的預設存放位置。

複製外表建立內表
如果外表建立之後均沒有問題，建立內表時可以直接複製外表內容。

  • 只複製後設資料表，不復制資料：CREATE TABLE <table_name> LIKE <existing_table_or_view_name>；
  • 透過查詢建立內表都複製：CREATE TABLE <table_name> AS SELECT <select_statement>;

切換外表為內表
ALTER TABLE <table_name> SET tblproperties('EXTERNAL' = 'FALSE')

清空表資料
TRUNCATE TABLE <table_name>

相關文章