上一章介紹了如何安裝hive以及hive的基礎介紹，這里開始使用hive，使用之前先介紹hive的基礎陳述句的學習，還有什么是內部表、外部表，

hive基礎陳述句

我們來看看最基本的格式，因為格式有很多種，我們先來看一個總的，然后一點點決議，

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] 
   // 指定Hive儲存格式：textFile、rcFile、SequenceFile 默認為：textFile
   [STORED AS file_format]
   
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  // 指定儲存位置
  [LOCATION hdfs_path]
  // 跟外部表配合使用，比如：映射HBase表，然后可以使用HQL對hbase資料進行查詢，當然速度比較慢
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

我們來分開一個一個細說：

建表1：全部使用默認建表方式

這里的是最基本的默認的建表陳述句，最后一句也可以沒有，但是意味著你只能有一列資料，

create table students
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必選，指定列分隔符

建表2：指定location （這種方式也比較常用）

一般在資料已經上傳到HDFS，想要直接使用，會指定Location，通常Locaion會跟外部表一起使用，內部表一般使用默認的location
指定資料的存盤位置不一定是外部表，后面會說

create table students2
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input1'; // 指定Hive表的資料的存盤位置

建表3：指定存盤格式

這個是指定表的存盤格式，對于這個存盤格式，在上一章節介紹過資料存盤的幾種格式，

create table students3
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; // 指定儲存格式為rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默認為textfile，注意：除textfile以外，其他的存盤格式的資料都不能直接加載，需要使用從表加載的方式，

建表4

create table xxxx as select_statement(SQL陳述句) (這種方式比較常用)

這種建表方式是不是很熟悉？在mysql中我們也接觸過，

create table students4 as select * from students2;

建表5

create table xxxx like table_name  只想建表，不需要加載資料

create table students5 like students;

hive加載資料

上傳檔案

使用 hdfs dfs -put ‘本地資料’ 'hive表對應的HDFS目錄下
使用 load data inpath

第一種方式應該就不用說了，這里來看第二中方式
// 將HDFS上的/input1目錄下面的資料 移動至 students表對應的HDFS目錄下，注意是 移動、移動、移動
load data inpath '/input1/students.txt' into table students;
這里為什么要強調移動呢，因為這種方式傳檔案，原來目錄下的檔案就不存在了，但是通過第一種的方式進行上傳檔案，原路徑的問價還是存在的，這里簡單區分一下，

這里注意一下：
  我們如果上傳資料到hdfs的目錄和hive表沒有關系，
  上傳到hive表和hive表有關系（需要進行資料的轉換），

清空檔案

清空表內容

truncate table students;

這里注意一下：加上 local 關鍵字可以將Linux本地目錄下的檔案上傳到 hive表對應HDFS 目錄下原檔案不會被洗掉

load data local inpath '/usr/local/soft/data/students.txt' into table students;

overwrite 覆寫加載
load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;

加載資料

基礎格式

hive> dfs -put /usr/local/soft/data/students.txt /input2/;
hive> dfs -put /usr/local/soft/data/students.txt /input3/;

洗掉表

格式

drop table <table_name>
Moved: 'hdfs://master:9000/input2' to trash at: hdfs://master:9000/user/root/.Trash/Current

OK
Time taken: 0.474 seconds
hive> drop table students_external;
OK
Time taken: 0.09 seconds
hive>

注意：

可以看出，洗掉內部表的時候，表中的資料（HDFS上的檔案）會被同表的元資料一起洗掉，
洗掉外部表的時候，只會洗掉的元資料，不會洗掉表中的資料（HDFS上的檔案），
一般在公司中，使用外部表多一點，因為資料可以需要多個程式使用，避免誤刪，通常外部表結合location一起使用，
外部表還可以將其他資料資料源中的資料，映射到hive中，比如說：hbase，ElasticSearch…
設計外部表的初衷就是讓表的元資料與資料解耦，

Hive 磁區

概念

磁區表實際上就是在表的目錄下在以磁區命名，建子目錄

作用

進行磁區裁剪，避免全表掃描，減少MapReduce處理的資料量，提高效率

注意事項：

一般在公司中，幾乎所有的表都是磁區表，通常按日期磁區、地域磁區，
磁區表在使用的時候記得加上磁區欄位
磁區也不是越多越好，一般不超過三級，按實際業務衡量，

建立磁區表

create external table students_pt1
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
PARTITIONED BY(pt string)
ROW F

增加一個磁區

alter table students_pt1 add partition(pt='20210904');

洗掉一個磁區

alter table students_pt drop partition(pt='20210904');

查看磁區

查看某個表中的所有磁區

show partitions students_pt; // 推薦這種方式（直接從元資料中獲取磁區資訊）

select distinct pt from students_pt; // 不推薦

插入資料

往磁區中添加資料

insert into table students_pt partition(pt='20210902') select * from students;

load data local inpath '/usr/local/soft/data/students.txt' into table students_pt partition(pt='20210902');

查詢磁區資料

查詢某個磁區的資料

// 全表掃描，不推薦，效率低
select count(*) from students_pt;

// 使用where條件進行磁區裁剪，避免了全表掃描，效率高
select count(*) from students_pt where pt='20210101';

// 也可以在where條件中使用非等值判斷
select count(*) from students_pt where pt<='20210112' and pt>='20210110';

Hive動態磁區

有時候我們原始表中的的資料包含了''日期欄位 dt''，我們需要根據dt
中不同的日期，分為不同的磁區，將原始表改成磁區表，
hive默認不開啟動態磁區

概念

動態磁區：根據資料中某幾列的不同的取值 劃分 不同的資料，

開啟Hive的動態磁區支持

# 表示開啟動態磁區
hive> set hive.exec.dynamic.partition=true;
# 表示動態磁區模式：strict（需要配合靜態磁區一起使用）、nostrict
# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nostrict;
# 表示支持的最大的磁區數量為1000，可以根據業務自己調整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;

練習

建立原始表并加載資料

create table students_dt
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string,
    dt string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

建立磁區表并加載資料

create table students_dt_p
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
PARTITIONED BY(dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

使用動態磁區插入資料

// 磁區欄位需要放在 select 的最后，如果有多個磁區欄位 同理，它是按位置匹配，不是按名字匹配
insert into table students_dt_p partition(dt) select id,name,age,gender,clazz,dt from students_dt;


// 比如下面這條陳述句會使用age作為磁區欄位，而不會使用student_dt中的dt作為磁區欄位
insert into table students_dt_p partition(dt) select id,name,age,gender,dt,age from students_dt;

多級磁區

create table students_year_month
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string,
    year string,
    month string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

create table students_year_month_pt
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
PARTITIONED BY(year string,month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

insert into table students_year_month_pt partition(year,month) select id,name,age,gender,clazz,year,month from students_year_month;

Hive分桶

概念

hive的分桶世紀上就是對檔案（資料）的進一步切分
hive默認是關閉分桶

作用

在往分桶表中插入資料的時候，會根據clustered by 指定的欄位，進
行hash磁區，對指定的buckets個數，進行取余，進而可以將資料分割
成buckets個數個檔案，進以達到資料均勻分布，可以解決Map的“資料
傾斜”問題，方便我們取抽樣資料，提高Map join效率，

分桶欄位需要根據業務進行設定

開啟分桶

由于分桶默認是關閉的，

hive> set hive.enforce.bucketing=true;

建立分桶表

create table students_buks
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
CLUSTERED BY (clazz) into 12 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

添加資料

// 直接使用load data 并不能將資料打散
load data local inpath '/usr/local/soft/data/students.txt' into table students_buks;

// 需要使用下面這種方式插入資料，才能使分桶表真正發揮作用
insert into students_buks select * from students;

https://zhuanlan.zhihu.com/p/93728864 Hive分桶表的使用場景以及優缺點分析

Hive JDBC

啟動hive server2

hive --service hiveserver2 &

或者
hiveserver2 &

新建maven專案并添加兩個依賴

    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.7.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.2.1</version>
    </dependency>

撰寫JDBC代碼

import java.sql.*;

public class HiveJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.hive.jdbc.HiveDriver");
        Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/test3");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from students limit 10");
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            String gender = rs.getString(4);
            String clazz = rs.getString(5);
            System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
        }
        rs.close();
        stat.close();
        conn.close();
    }
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/395067.html

標籤：其他

上一篇：Spark統計每天新增用戶

下一篇：HDFS常用檔案操作命令匯總

hive學習-----基礎陳述句