Hive實作離線計算（HQL）

????????‍????部落客介紹：大家好，我是芝士味的椒鹽，一名在校大學生，熱愛分享知識，很高興在這裡認識大家????

????擅長領域：Java、大資料、運維、電子

????????如果本文章各位小夥伴們有幫助的話，????關注+????????點贊+????評論+????收藏，相應的有空了我也會回訪，互助！！！

????另本人水準有限，旨在創作簡單易懂的文章，在文章描述時如有錯，懇請各位大佬指正，在此感謝！！！

大資料業務分為兩大：實時計算，離線計算

實時計算：實時對來到的資料進行及時計算

離線計算：對堆積已久的資料進行計算（MapReduce（效率低）、Hive）

Hive提供了一種類SQL的操作進行查詢統計

Hive的底層：是基于MapReduce的引擎，會将查詢語句轉換為Map階段和Reduce階段

MR、Hive：離線計算處理子產品的技術

Hive特點：

可擴充性———>Hive自由擴充叢集的規模，一般無需重新開機服務
延展性————>Hive支援使用者自定義函數，使用者可以根據自己的需求來實作自己的函數
容錯性————>節點當機，SQL任然可以完成執行

資料倉庫（DataWarehouse）：可以包含多個資料庫

資料庫：包含多個表

Hive與傳統資料庫的對比：

安裝Hive

Hive常用互動指令：

"-e" 不進入hive的互動視窗執行sql

hive -e "show databases;"
“-f” 執行腳本中的sql語句

編寫selectdata.sql腳本

use testdatas; select * from stu;
執行sql腳本

hive -f selectdata.sql
将sql執行的結果寫入檔案中

hive -f selectdata.sql >> ./restult1.txt

Hive其他指令操作：

退出hive視窗(老版本有差別，新版沒有差別)：

exit;#先隐形送出資料，再退出 quit;不送出資料，退出
在hive cli指令視窗中檢視hdfs檔案系統
在hive cli指令視窗中檢視本地檔案系統
檢視在hive中輸入的所有的曆史指令

cat /home/目前使用者/.hivehistory

Hive資料倉庫位置配置：

配置Default資料倉庫的位置，預設是在hdfs上的：/user/hive/warehouse/(預先存在的路徑)下，可以将下面的标簽片段編寫到hive-site.xml中就可以改變Default資料創庫的位置 <property> <name>hive.metastore.warehouse.dir</name> <value>/user/hive/warehouse</value> <description>location of default database for the warehouse</description> </property>

切記配置完Default資料創庫的位置，需要設定同組可讀的權限

hadoop fs -chmod g+w /user/hive/warehouse

背景資訊查詢配置hive-site.xml,就可以實作顯示目前資料庫，以及查詢表的頭資訊配置

<property> <name>hive.cli.print.header</name> <value>true</value> </property> <property> <name>hive.cli.print.current.db</name> <value>true</value> </property>

Hive運作日志資訊配置：

在/tmp/hadoop目錄下可以看到hive的日志檔案，進行排錯,hive.log、hive.log.2020-09-27....
拷貝并修改hive-log4j.properties. template——>hive-log4j.properties

#可以修改日志存儲的位置 hive.log.dir=/usr/local/src/hive/logs #可以修改日志列印日志時間的格式 log4j.appender.DRFA.DatePattern=.yyyy-MM-dd

參數配置方式：

檢視目前所有的配置資訊

hive>set ;
參數配置的三種方式

三種配置的優先級：配置檔案<指令行參數<參數聲明

Hive的基本概念：

使用者可以在Hive中執行Hadoop的fs指令，如“dfs -ls /;”查詢hdfs上存儲的資料
在hive中使用hadoop指令比直接在bash shell中執行hadoop dfs更加高效，在hive中是會在同一個程序中執行這些指令，而在bash shell執行則會每次啟動一個JVM的新執行個體。

Hive中的基本資料類型：

Hive v0.8.0版本中增加了時間戳資料類型和二進制數組資料類型

Hive實作離線計算（HQL）

所有的資料類型都是對Java中的資料類型接口的實作
新增資料類型TIMESTAMP的值可以是整數,也就是距離Unix新紀元時間( 1 970年1月1日, 午夜12點)的秒數;也可以是浮點數,即距離 Unix新紀元時間的秒數,精确到納秒(小數點後保留9位數) ; 還可以是字元串,即JDBC所約定的時間字元串格式,格式為YYYY-MM-DD hh:mm:s.ffffff。
TIMESTAMPS表示的是UTC時間。Hive本身提供了不同時區間互相轉換的内置函數,也就是to_utctimestamp函數和fromutc_ timestamp函數
BINARY資料類型和很多關系型資料庫中的VARBINARY資料類型是類似的,但其和BLOB資料類型并不相同。因為BINARY的列是存儲在記錄中的,而BLOB則不同。BINARY可以在記錄中包含任意位元組,這樣可以防止Hive嘗試将其作為數字,字待串等進行解析。
String相當于varchar，可以單個存儲2G的字元數

操作

建立stu表

#**row format delimited----》表示對行格式化 #fields terminated by '\\t'----》表示參數按什麼分割這裡，這為一個制表符** **create table stu(id bigint,name string ,score double,age int) row format delimited fields terminated by '\\t';**
在/home/hadoop/databasesdata/下建立stu.txt,并加入以下内容 20192023 Marry 99.9 18 20192022 Lucrh 78 22 20192021 Marry 89.0 99

tips:資料和資料之間要嚴格按照建立表時指定的資料分割格式，不然之後加載進表中會出現 NULL 值

将資料加載進stu表

load data local inpath '/home/hadoop/databasesdata/stu.txt' into table stu;

集合資料類型

Hive中的列支援使用struct、map和array集合資料類型

Hive實作離線計算（HQL）

操作

建立star表 create table star(name string, friends array<string >, childern map<string, int>, address struct<street:string, city:string>) row format delimited fields terminated by ',' collection items terminated by '_' map keys terminated by ':' line terminated by '\\n';

row format delimited fields terminated by ',':每行每條語句的分隔符
collection items terminated by '_' :array的資料分隔符
line terminated by '\n'：行分隔符

類型轉化

Hive的原子資料類型可以進行隐式轉化，可以正向轉化，不會進行反向轉化，如果傳入的值不是低于屬性的資料類型，而是高于其，則會直接報錯，可以使用CAST進行操作，将不會進行報錯。

隐式類型轉換

任何類型都可以準換成一個更廣的類型

TINYINT—>INT—>BIGINT
所有整數類型、FLOAT和STRING都可以隐式轉換成DOUBLE
TINYINT、SMALLINT、INT都可以轉換成FLOAT
BOOLEAN不允許轉換成其他類型

強制轉換

例子：

CAST('1' AS INT)是将字元串型‘1’轉換成整數1,
如果強制類型轉換失敗，執行了CAST('X' AS INT) 表達式将傳回空值NULL

DDL資料定義

建立資料庫

建立的資料庫預設是在/user/hive/warehouse/下

hive (default)> create database star;
使用not exitis判斷是否預先已有資料庫

hive (default)> create database if not exitis star;
建立資料庫，并制定資料庫在HDFS上存儲的位置

create database if not exists kos location '/koss.db';

查詢資料庫

顯示資料庫

顯示資料庫

show databases;
過濾顯示查詢的資料庫,*

show databases like 'ko*';

查詢資料庫詳情

顯示資料庫資訊

desc database star;
顯示資料庫詳細資訊，extended

desc database extended star;

切換目前資料庫

use kos;

修改資料庫

alter database kos set dbproperties('createtime'='20180818');

tips:通過設定鍵值對的形式修改資料庫的屬性資訊， 資料庫其他的中繼資料都是不可以更改的，包括資料庫名和資料庫所在的目錄位置 ，可以通過extend檢視修改之後的資訊

删除資料庫

drop database kos;

tips：可以采取if exists進行判斷資料庫是否存在，資料庫不為空的情況下采取cascade指令将其删除

drop database kos 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也會删除這個表中資料。管理表不适合和其他工具共享資料。

普通表建立

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';
根據查詢結果創表

create table if not exists restl as select * from stu;
根據已經存在的表結建構立表

create table if not exists re2 like stu;
查詢表的類型

desc formatted stu;

外部表

因為表是外部表，是以Hive并非認為其完全擁有這份資料。删除該表并不會删除掉這份資料，不過描述表的中繼資料資訊會被删除掉

案例:

dept.txt

10 ACCOUNTING 1700 20 RESEARCH 1800 30 SALES 1900 40 OPERATIONS 1700
emp.txt

7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
建表語句（dept、emp）

create external table if not exists testdatas.dept(deptno int, deptname string, money int) row format delimited fields terminated by '\\t'; create external table if not exists testdatas.emp(empno int, empname string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) row format delimited fields terminated by '\\t';
加載資料進表中

load data local inpath '/home/hadoop/databasesdata/dept.txt' into table dept; load data local inpath '/home/hadoop/databasesdata/emp.txt' into table emp;

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

首先查詢表的類型使用

desc formatted stu;
修改内部表stu為外部表

alter table stu set tblproperties('EXTERNAL'='TRUE');
修改外部表stu為内部表

alter table stu set tblproperties('EXTERNAL'='FLASE');

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

建立分區表

create table depts_partition (dept int, dname string, loc string) partitioned by (month string) row format delimited fields terminated by '\\t';
加載資料到分區表中

load data local inpath '/home/hadoop/databasesdata/dept.txt' into table depts_partition partition(month='2020-9-29');
查詢分區表中資料

select * from depts_partition where month='2020-9-23';
增加分區

#添加單個分區 alter table depts_partition add partition(month='2020-9-30'); #添加多個分區 alter table depts_partition add partition(month='2020-10-2') partition(month='2020-10-1');
删除分區

#删除單個分區 alter table depts_partition drop partition(month='2020-10-2'); #删除多個分區 alter table depts_partition drop partition(month='2020-10-2') partition(month='2020-10-1');
檢視分區表中有分區

show partitions depts_partition;
檢視分區結構

desc formatted depts_partition;

建立二級分區

create table kos_deptss_partition(dept int, dname string, loc string) partitioned by (month string, day string) row format delimited fields terminated by '\\t';
正常加載資料到分區

load data local inpath '/home/hadoop/databasesdata/dept.txt' into table kos_deptss_partition partition(month='1999-9',day='9');
把資料直接上傳到分區目錄，讓分區和資料表産生關聯的三種方式
修改表

修改表名

alter table table_name rename to new_table_name
增加/修改/替換列資訊

文法更新列 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則是表示替換表中所有字段。

例子：

查詢結構

hive> desc dept_partition;
添加列

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

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

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

Hive實作離線計算（HQL）

安裝Hive

集合資料類型

類型轉化

DDL資料定義

建立資料庫

查詢資料庫

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

User Defined Hadoop DataType

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Ambari介紹和架構原理

詳解STM32單片機的堆棧

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述