大數(shù)據(jù)培訓(xùn)_HIve常用的基本語句總結(jié)

發(fā)布時間： 2019-06-27 14:54:58

　　大數(shù)據(jù)培訓(xùn)_HIve常用的基本語句總結(jié)

　　本次記錄hive當(dāng)中最常用的一些語句，助你在hive操作能快速入手。通過常用的SQL語句能對Hive更加理解。本次實例都是一些針對表的管理。包括外部表，內(nèi)部表，分區(qū)表，分桶表。

? 大數(shù)據(jù)培訓(xùn)

簡單的hive sql語句

　　#查詢數(shù)據(jù)庫
　　show databases;
　　#查詢表
　　show tables;
　　#使用數(shù)據(jù)庫
　　use database_name;
　　#查看表結(jié)構(gòu)
　　desc table_name;
　　#刪除表
　　drop table table_name;

創(chuàng)建表

格式：

　　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]

　　?# 字段解釋：
　　?[external] # 申明是否為外部表

　　?[if not exists] # 如果表存在，則不創(chuàng)建了

　　?[(col_name data_type [COMMENT col_comment], ...)] # 分別是字段名，字段類型，字段注釋

　　?[COMMENT table_comment] # 放在()后面，是表注釋

　　?[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] # 建立分區(qū)，()里是字段名，字段類型，字段注釋，分區(qū)里的字段不能包含在字段聲明區(qū)。

　　?[CLUSTERED BY (col_name, col_name, ...)
　　? [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] # 在clustered by 里的字段上建立 num_buckets個桶，記錄是由 sorted by里的字段排序的。

　　?[ROW FORMAT row_format] # 指定分隔符，可以是以下幾個：
: DELIMITED [FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]

　　?[STORED AS file_format] # 指定load文件的類型，分為：
textfile 是純文本文件
sequence 是壓縮文件

　　?[LOCATION hdfs_path] # 向表里加載數(shù)據(jù)，hdfs_path是一個hdfs上的目錄，不能是文件，hive會依據(jù)默認配置的hdfs路徑，自動將整個目錄下的文件都加載到表中。

?案例實操

（1）普通創(chuàng)建表

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

（2）根據(jù)查詢結(jié)果創(chuàng)建表（查詢的結(jié)果會添加到新創(chuàng)建的表中）??

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

（3）根據(jù)已經(jīng)存在的表結(jié)構(gòu)創(chuàng)建表??

　　create table if not exists student4 like student;?

（4）查詢表的類型
　　hive (default)> desc formatted student2;
　　Table Type: MANAGED_TABLE

?外部表
1）理論
因為表是外部表，所有 Hive 并非認為其完全擁有這份數(shù)據(jù)。刪除該表并不會刪除掉這份數(shù)據(jù)，不過描述表的元數(shù)據(jù)信息會被刪除掉。
2）管理表和外部表的使用場景：
每天將收集到的網(wǎng)站日志定期流入 HDFS 文本文件。在外部表（原始日志表）的基礎(chǔ)上做大量的統(tǒng)計分析，用到的中間表、結(jié)果表使用內(nèi)部表存儲，數(shù)據(jù)通過 SELECT+INSERT進入內(nèi)部表

使用復(fù)雜類型創(chuàng)建表?

創(chuàng)建一個帶分區(qū)的內(nèi)部表?

　　create table if not exists T3(
　　? id int,
　　? name string
　　?)
　　?partitioned by (classid int)
　　?row format
　　?delimited fields terminated by ','
　　?stored as textfile;

?創(chuàng)建一個帶桶的內(nèi)部表?

　　create table T4(
　　? id int ,
　　? name string,
　　? sex string,
　　? age int
　　?)
　　?partitioned by (city string)
　　?clustered by(age) sorted by(name) into 5 buckets
　　?row format
　　?delimited fields terminated by ','
　　?stored as textfile;