本文由 发布，转载请注明出处，如有问题请联系我们！ 发布时间: 2021-07-24关于hive核心

一、DDL数据信息界定

1、建立数据库

1）建立一个数据库查询，数据库查询在 HDFS 上的默认设置储存途径是/user/hive/warehouse/*.db。

hive (default)> create database db_hive;

2）防止要建立的数据库查询早已存有不正确，提升 if not exists 分辨。（规范书写）

hive (default)> create database db_hive;

FAILED:  Execution   Error,   return   code  1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists

hive (default)> create database if not exists db_hive;

3）建立一个数据库查询，特定数据库查询在 HDFS 上储放的部位

hive (default)> create database db_hive2 location '/db_hive2.db';

2、数据库查询

表明数据库查询

1．表明数据库查询

hive> show databases;

2．过虑表明查看的数据库查询

hive> show databases like 'db_hive*'; 

OK 
db_hive 
db_hive_1

查看数据库详细信息

1．表明数据库查询信息内容

hive> desc database db_hive; 
OK 
db_hive		hdfs://master:8020/usr/hive/warehouse/db_hive.db	root	USER

2．表明数据库查询详细资料，extended

hive> desc database extended db_hive; 
OK
db_hive		hdfs://master:8020/usr/hive/warehouse/db_hive.db	root	USER

转换当今数据库查询

hive (default)> use db_hive;

3、改动数据库查询

客户能够 应用 ALTER DATABASE 指令为某一数据库查询的 DBPROPERTIES 设定键-值对特性值，来叙述这一数据库查询的特性信息内容。

数据库查询的别的元数据信息全是不能变更的，包含数据库查询名和数据库查询所属的文件目录部位。

hive (default)>alter hive set database dbproperties('createtime'='20200830');

在 hive 中查询改动結果

hive> desc database extended db_hive;

db_name comment location owner_name owner_type parameters 
db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db chaosUSER {createtime=20200830}

4、删除数据库

1．删掉空数据库查询

hive>drop database db_hive2;

2．假如删掉的数据库查询不会有，最好是选用 if exists 分辨数据库查询是不是存有

hive> drop database db_hive;

FAILED: SemanticException [Error 10072]: Database does not exist: db_hive

hive> drop database if exists db_hive2;

3．假如数据库查询不以空，能够 选用 cascade 指令，强行删除

hive> drop database db_hive;

FAILED: Execution   Error,   return   code  1 from org.apache.hadoop.hive.ql.exec.DDLTask.

InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.) hive> drop database db_hive cascade;

5、创建表

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]

　　[STORED AS file_format]

　　[LOCATION hdfs_path]

2）字段名表述

•CREATE TABLE 建立一个特定名称的表。假如同样名称的表早已存有，则抛出异常；客户可以用 IF NOT EXIST 选择项来忽视这一出现异常
•EXTERNAL 关键词能够 让客户建立一个外界表，新建表的与此同时特定一个偏向具体数据信息的途径（LOCATION）,Hive 建立內部表时，会将数据信息挪动到hadfs偏向的途径；若建立外界表，仅纪录数据信息所属的途径，不对数据信息的部位做一切更改。在删除表的情况下，內部表的数据库和数据信息会被一起删掉，而外界表只删掉数据库，不删掉数据信息。
•LIKE 容许客户拷贝目前的表构造，可是不拷贝数据信息
•COMMENT能够 为表与字段名提升注解叙述
•PARTITIONED BY  建立分区表,特定系统分区
•ROW FORMAT 
　　DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] 
　　　　MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
　　　　| SERDE serde_name [WITH SERDEPROPERTIES 
　　　　(property_name=property_value, property_name=property_value, ...)] 
　　客户新建表的情况下能够 自定 SerDe 或是应用内置的 SerDe。要是没有特定 ROW FORMAT 或是 ROW FORMAT DELIMITED，可能应用内置的 SerDe。新建表的情况下，
客户还必须 为表特定列，客户在特定表的列的与此同时也会特定自定的 SerDe，Hive 根据 SerDe 明确表的实际的列的数据信息。•STORED AS 
　　SEQUENCEFILE //实例化文档
　　| TEXTFILE //一般的文本文档文件格式
　　| RCFILE　　//队伍储存紧密结合的文档
　　| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定格式文件
　　假如文档数据信息是纯文字，能够 应用 STORED AS TEXTFILE。假如数据信息必须 缩小，应用 STORED AS SEQUENCE 。
•LOCATION特定表在HDFS的储存途径

6、改动表

重新命名表

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 则是表明更换表格中全部字段名。

简易实例

（1）查看表结构

hive> desc dept_partition;

（2）加上列

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

（3）升级列

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

（4）更换列

hive (default)> alter table dept_partition replace columns(deptno string, dname string, loc string);

注：hive不兼容删除字段

7、删除表

留意：內部表和外界表删掉的差别

 drop table dept_name;

8、案例

1）有关內部表与外界表

建表语句

建立单位表

create external table if not exists default.dept( deptno int, dname string, loc int )

row format delimited fields terminated by '\t';

建立职工表

create external table if not exists default.emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int)
row format delimited fields terminated by '\t';

查询建立的表

hive (default)> show tables; 
OK 
tab_name 
dept 
emp

向外界表格中导进数据信息导进数据信息

hive (default)>load data inpath local '/opt/module/data/dept.txt' into table default.dept;
hive (default)>load data local inpath'/opt/module/data/emp.txt' into table default.emp;

查看結果

hive (default)> select * from emp;
hive (default)> select * from dept;

查询表恢复出厂设置数据信息

hive (default)> desc formatted dept;

Table Type: EXTERNAL_TABLE

管理方法表与外界表的相互之间变换

（1）查看表的种类

hive (default)> desc formatted student2;

 Table Type: MANAGED_TABLE

（2）改动內部表 student2 为外界表

alter table student2 set tblproperties('EXTERNAL'='TRUE');

（3）查看表的种类

hive (default)> desc formatted student2;

 Table Type: EXTERNAL_TABLE

（4）改动外界表 student2 为內部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

（5）查看表的种类

hive (default)> desc formatted student2;

 Table Type: MANAGED_TABLE

留意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定不动书写，区别英文大小写！

2）有关基本分区表

1．引进分区表（必须 依据日期对日志开展管理方法）

/user/hive/warehouse/log_partition/20200702/20200702.log

/user/hive/warehouse/log_partition/20200703/20200703.log

/user/hive/warehouse/log_partition/20200704/20200704.log

2．建立分区表英语的语法

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

3．载入数据信息到分区表中

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202009');

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202008');

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202007’);

4．查看分区表中数据

单系统分区查看

hive (default)> select * from dept_partition where month='202009';

多系统分区联合查询

hive (default)> select * from dept_partition where month='202009' union all select * from dept_partition where month='202008' union select * from dept_partition where month='202007';、
简易书写
hive (default)> select * from dept_partition where month>='202007'and month<='202009' 

5．提升系统分区

建立单独系统分区

hive (default)> alter table dept_partition add partition(month='202006') ;

与此同时建立好几个系统分区

hive (default)> alter table dept_partition add partition(month='202005') partition(month='202004');

6．删除分区

删掉单独系统分区

hive (default)> alter table dept_partition drop partition (month='202004');

与此同时删掉好几个系统分区

hive (default)> alter table dept_partition drop partition (month='202005'), partition (month='202006');
简易书写
hive (default)> alter table dept_partition drop partition (month>='202005',month<='202006');

7．查询分区表有多少系统分区

hive> show partitions dept_partition;

8．查询分区表构造

hive> desc formatted dept_partition;

3）有关多级别分区表

hive中的多级别分区表，能够 了解为多级别文件目录，依照系统分区字段名次序将本来数据信息区划为多级别文件目录

1．建立二级分区表

hive (default)> create table dept_partition2(deptno int, dname string, loc string)
partitioned by (month string, day string) 
row format delimited fields terminated by '\t';

2．一切正常的载入数据信息

（1）载入数据信息到二级分区表中

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='202009', day='13');

（2）查看系统分区数据信息

hive (default)> select * from dept_partition2 where month='202009' and day='13';

3．把数据信息立即上传入系统分区文件目录上，让分区表和数据信息造成关系的三种方法

（1）方法一：提交数据信息后修补

​ 提交数据信息

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=12; 

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=202009/day=12;

查看数据信息（查看不上刚提交的数据信息）

hive (default)> select * from dept_partition2 where month='202009' and day='12';

实行修补指令

hive> msck repair table dept_partition2;

再度查看数据信息

hive (default)> select * from dept_partition2 where month='202009' and day='12';

（2）方法二：提交数据信息后加上系统分区

​ 提交数据信息

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=11; 

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=202009/day=11;

实行加上系统分区

 hive (default)> alter table dept_partition2 add partition(month='202009',day='11');

查看数据信息

hive (default)> select * from dept_partition2 where month='202009' and day='11';

（3）方法三：创建文件夹后 load 数据信息到系统分区

建立文件目录

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=10;

提交数据信息

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='202009',day='10');

查看数据信息

hive (default)> select * from dept_partition2 where month='202009' and day='10';

4）有关动态性分区表

前边的系统分区建立方法为静态数据系统分区，假如必须 建立十分多的系统分区，或是依据特定字段名值系统分区，就必须 应用动态分区，hive的动态分区能够 根据查看主要参数推测必须 建立的系统分区名字。

eg：

insert overwrite table employees partition(country,state)

select ...,se.cnty,se.st

from staged_employees se;

如上边事例，hive能够 依据select句子中最终2列来明确系统分区字段名country和state的值。这也是为什么在上面事例应用了不一样的取名，便是为了更好地注重源表字段值和輸出系统分区值中间的关联是依据部位而不是依据取名来配对的。

有关混和应用动态性和静态数据系统分区。

eg：

insert overwrite table employees partition(country='China',state)

select ...,se.cnty,se.st

from staged_employees se

where se.cnty='China';

如上边事例，特定了country字段名的数值静态数据的我国，而系统分区字段名state是动态性值

留意：静态数据系统分区务必发生在动态分区键以前。

动态分区作用默认设置是关掉的，打开后，也会默认设置是严格模式实行，在这类方式下规定最少有一列系统分区字段名是静态数据的，这有利于因设计方案不正确造成查看造成很多的系统分区。

动态分区特性

5）有关歪斜表

根据特定一个或是好几个列经常会出现的值（比较严重倾斜），Hive 会全自动将牵涉到这种值的数据信息拆分成独立的文档。在查看时，假如牵涉到歪斜值，它就立即从单独文档中读取数据，而不是扫描仪全部文档，这促使特性获得提高。

create [exertnal] table 歪斜表名(列名 种类,列名 种类) skewed by (歪斜字段名) ON (相匹配普遍歪斜值,相匹配普遍歪斜值)

row format delimited fields terminated by 字段名分节符;

9、主视图

当查看越来越长或繁杂的情况下，根据应用主视图将这一查看句子切分成好几个小的、更可控性的精彩片段能够 减少这类复杂性。这一点与在计算机语言中应用涵数或是软件开发中的分层次设计概念是一致的。封裝繁杂的一部分能够 是终端用户根据器重反复的一部分来搭建繁杂的查看。

针对主视图而言一个普遍的应用情景便是根据一个或好几个列的值来限定輸出結果。有一些数据库查询容许将主视图做为一个安全性体制，也就是不给客户立即浏览具备隐秘数据的初始表，只是给予给客户一个根据WHERE子句限定了的主视图，以供浏览。Hive 现阶段并不兼容这一作用，由于客户务必具备可以浏览全部最底层初始表的管理权限，这时候主视图才可以工作中。殊不知，根据创建视图来限定数据信息浏览能够 用于维护信息内容不被随便查看。

Hive 会先分析主视图，随后应用分析結果再去分析全部查看句子。殊不知，做为Hive查看优化器的一部分，查看句子和主视图句子很有可能会合拼成-一个单-一的具体查看句子。这一定义主视图依然适用主视图和应用这一主视图的查看句子都包括了一个ORDER BY子句或-一个LIMIT子句的状况。这时候会在应用这一主视图的查看句子以前对该主视图开展分析。比如，假如主视图句子带有一个LIMIT 100 子句，而与此同时应用到这一主视图的查看带有一个LIMIT 200子句，那麼客户最后数最多只有获得100条結果纪录。由于界定一个主视图事实上并不会“细化”实际操作一切具体数据信息，因此主视图事实上是对其所应用到的表和列的一个查看句子干固全过程。因而，假如主视图所涉及到的表或是列不会再存有时，会造成主视图查看不成功。

一个主视图的名字要和这一主视图所属的数据库查询下的别的全部表和主视图的名字不一样。客户还能够为全部的新列或一部分新列提升一个COMMNET子句，开展写注解。这种注解并不是“ 承继”初始表格中的界定。一样地，假如AS SELECT子句中包括沒有取名别称的关系式得话，比如size(cols)(测算cols中原素的数量)，那麼Hive可能应用_ _CN 做为新的字段名，在其中N表明从0开始的一个整数金额。假如AS SELECT句子不合理合法得话，那麼创建视图全过程将不成功。

eg:

CREATE VIEW IF NOT EXISTS shipments (time, part)

COMMENT ' Time and parts for shipments. '

TBLPROPERTIES ( 'creator' = 'me' )

AS SELECT ...;

在AS SELECT子句以前，客户能够 根据界定TBLPROPERTIES来界定表特性信息内容，这一点和表同样。上例中，大家界定的特性为“creator”, 表明这一主视图的创始人名字。

CREATE [EXTERNAL] TABLE ... LIKE ..构造一样适用拷贝主视图，只必须 在LIKE关系式里边写主视图名就可以了:

eg:

CREATE TABLE shipments2 LIKE shipments;

主视图不能够做为INSERT句子或LOAD指令的总体目标表。主视图是写保护的。针对主视图只容许更改数据库中TBLPROPERTIES(表特性)特性信息内容:

ALTER VIEW shipments SET TBLPROPERTIES ('created_ at' = ' some_ timestamp') ;

1）Hive 的主视图和关联型数据库查询的主视图差别

和关联型数据库查询一样，Hive 也给予了主视图的作用，但是一定要注意，Hive 的主视图和关联型数据库查询的数据信息或是有非常大的差别：

　　（1）仅有逻辑性主视图，沒有物化视图；

　　（2）主视图只有查看，不可以做载入数据信息实际操作，如：Load/Insert/Update/Delete 数据信息；

　　（3）主视图在建立情况下，仅仅储存了一份数据库，当查看主视图的情况下，才逐渐实行主视图相匹配的这些子查询

　　（4）view界定中若包括了ORDER BY/LIMIT句子，当查看主视图时也开展ORDER BY/LIMIT句子实际操作，view之中界定的优先高些

​ • view: order by age asc;

​ • select order by age desc;

​ • select * from view order by age desc;

　　（5）view适用迭代更新主视图

​ • view1: select * from tb_user1;

​ • view2: select * from view1;

​ • view3: select * from view2;

2）Hive主视图的建立句子

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name 
  [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ... ;

3）Hive主视图的查询句子

show views;
desc view_test;-- 查询某一实际主视图的信息内容

4）Hive主视图的应用句子

select colums from view_test;
select * from view_test;

5）Hive主视图的删除语句

DROP VIEW [IF EXISTS] [db_name.]view_name;
drop view view_test;

10、数据库索引

Hive仅有比较有限的数据库索引作用。Hive中沒有一般关联型数据库查询中键的定义，可是或是能够 对一些字段名创建数据库索引来加快一些实际操作的。一张表的数据库索引数据储存在此外一张表格中。

数据库索引解决控制模块被设计方案变成能够 订制的Java编号的软件，因而，客户能够 依据必须 对其开展完成，以达到本身的要求。

当逻辑分区事实上过多过细而基本上没法应用时，创建数据库索引也就变成系统分区的另-一个挑选。创建数据库索引能够 协助剪裁掉一张表的一些数据信息块，那样可以降低MapReduce的输入信息量。并不是全部的查看都能够根据创建数据库索引得到 益处。根据EXPLAIN指令能够 查询某一查看句子是不是采用了数据库索引。

Hive中的数据库索引和这些关联型数据库查询中的一样， 必须 开展细心评定才可以应用。维护保养数据库索引也必须 附加的储存空间，与此同时创建索引也必须 耗费云计算服务器。客户必须 在创建数据库索引为查看产生的益处和因而而必须 投入的成本中间作出衡量。

1）创建索引

create index t1_index on table tb_user(name) 

as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild 

in table t1_index_table;

as：特定索引器；

in table：特定数据库索引表，若不特定默认设置转化成在default_tb_user_t1_index表格中

create index t2_index on table tb_user(name)

as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;

with deferred rebuild表明在实行alter index xxx_index on xxx rebuild时将启用generateIndexBuildTaskList获得Index的MapReduce，并实行为数据库索引添充数据信息。

2）表明数据库索引

show [formatted] index on tb_name;

关键词FORMATTED是可选择的。提升这一关键词能够 使輸出中包括有列名字。客户还能够更换INDEX为INDEXES,那样輸出中就可以例举出好几个数据库索引信息内容了。

3）复建数据库索引

创建新数据库索引以后务必复建数据库索引才可以起效

ALTER INDEX t2_index ON tb_user REBUILD;
ALTER INDEX employees_index ON TABLE employees
PARTITION (country = 'China')
REBUILD;

假如省去掉PARTITION,那麼可能对全部系统分区开展复建数据库索引。

都还没一个内嵌的体制可以在最底层的表或是某一特殊的系统分区发生改变时，全自动开启复建数据库索引。可是，假如客户具备一个工作流引擎来升级表系统分区中的数据信息得话，那麼客户很有可能早已在这其中某点应用到ALTER TABLE .. TOUCH PARTITION(..)作用，一样地，在这个工作流引擎中也能够 对相匹配的数据库索引实行复建数据库索引句子ALTER INDEX ... REBUILD。

假如复建数据库索引不成功，那麼在复建逐渐以前，数据库索引将滞留在以前的版本号情况。从这类实际意义上看，复建数据库索引实际操作是原子性的。

4）删除索引

如果有数据库索引表得话，删掉一个数据库索引可能删掉这一数据库索引表:

DROP INDEX IF EXISTS t1_index ON tb_user;
DROP INDEX IF EXISTS employees_index ON TABLE employees;

Hive不允许客户立即应用DROP TABLE句子以前删除索引表。而一般 状况下，IF EXISTS全是可选择的，其用以当数据库索引不会有时防止抛出去错误报告。假如被数据库索引的表被删除了，那麼其相匹配的数据库索引和数据库索引表也会被删掉。一样地，假如初始表的某一系统分区被删除了，那麼这一系统分区相匹配的系统分区数据库索引也与此同时会被删掉掉。

二、DML数据信息实际操作

1、数据信息导进

1） 向表格中运载数据信息（Load）

load data [local] inpath '/opt/module/datas/table_name.txt' [overwrite] | into table table_name
[partition (partcol1=val1,…)];

（1）load data:表明载入数据信息

（2）local:表明从当地载入数据信息到 hive 表；不然从 HDFS 载入数据信息到 hive 表

（3）inpath:表明载入数据信息的途径

（4）overwrite:表明遮盖表格中现有数据信息，不然表明增加

（5）into table:表明载入到哪张表

（6）student:表明实际的表

（7）partition:表明上传入特定系统分区

2）Load实例

（1）建立一张表

hive (default)> create table student(id string, name string) 
row format delimited fields terminated by '\t';

（2）载入本地文件到 hive

hive (default)>load data local inpath '/opt/module/datas/student.txt' 
into table default.student;

（3）载入 HDFS 文档到 hive 中

文件上传到 HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/chaos/hive;

载入 HDFS 上数据信息

hive (default)>load data inpath '/user/chaos/hive/student.txt' into table default.student;

（4）载入数据信息遮盖表格中现有的数据信息

文件上传到 HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/chaos/hive;

载入数据信息遮盖表格中现有的数据信息

hive (default)>load data inpath '/user/chaos/hive/student.txt' overwrite into table default.student;

3） 根据查看句子向表格中插进数据信息（Insert）

1．建立一张分区表

create table student(id int, name string) partitioned by (month string) 
row format delimited fields terminated by '\t';

2．基本上插进数据信息

hive (default)>insert into table student partition(month='202009') values(1,'wangwu');

3．基本原则插进（依据一张表查看結果）

hive (default)> insert overwrite table student partition(month='202008') 
select id, name from student where month='202009';

4．多插进方式（依据好几张表查看結果）

from(select * from student
)t 
insert overwrite table student partition(month='202007') 
select id,name where month='202009' 
insert overwrite table student partition(month='202006')
select id, name where month='202009';

4） 查看句子中创建表并载入数据信息（As Select）

依据查看結果创建表（查看的結果会加上到创好的表中）

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

5）创建表时根据 Location 特定载入数据信息途径

1．创建表，并特定在 hdfs 上的部位

hive (default)> create table if not exists student5(id int, name string)
row format delimited fields terminated by '\t' 
location '/user/hive/warehouse/student5';

2．提交数据信息到 hdfs 上

hive (default)> dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5;

3．查看数据信息

hive (default)> select * from student5;

6） Import 数据信息到特定 Hive 表格中

留意：先用 export 导出来后，再将数据信息导进。

hive (default)> import table student2 partition(month='202009') from '/user/hive/warehouse/export/student';

2、 数据信息导出来

1）Insert 导出来

（1）将查看的結果导出来到当地

hive (default)> insert overwrite local directory '/opt/module/datas/export/student' 
select * from student;

（2）将查看的結果恢复出厂设置导出来到当地

hive(default)>insert overwrite local directory '/opt/module/datas/export/student1' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

（3）将查看的結果导出来到 HDFS 上(与导出来到当地的差别是沒有 local)

hive (default)>insert overwrite directory '/user/chaos/student2' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

2 ）Hadoop指令导出来到当地

hive (default)>dfs -get /user/hive/warehouse/student/month=202009/000000_0 /opt/module/datas/export/student3.txt;

3） Hive Shell 指令导出来基本上英语的语法：

（hive -f/-e 实行句子或是脚本制作 > file）

[chaos@hadoop102 hive]$ bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;

4）Export 导出来到 HDFS 上

hive(default)> export table default.student to '/user/hive/warehouse/export/student';

3、 消除表中数据（Truncate）

留意：Truncate 只有删掉管理方法表，不可以删掉外界表中数据

hive (default)> truncate table student;

三、查看

查看句子英语的语法：

[WITH CommonTableExpression (, CommonTableExpression)*](Note: Only available starting with Hive 0.13.0)
  SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list] | [DISTRIBUTE BY col_list] 
  [SORT BY col_list]
  [LIMIT number]

1、 基本上查看（Select…From）

1） 全表和特殊列查看

全表查看

hive (default)> select * from emp;

挑选特殊列查看

hive (default)> select empno, ename from emp;

留意：

（1）SQL 语言表达英文大小写不比较敏感。

（2）SQL 能够 写在一行或是几行

（3）关键词不可以被简称也不可以支行

（4）各子句一般要支行写。

（5）应用缩近提升 句子的易读性。

2） 列别称

1．重新命名一个列

2．有利于测算

3．紧随字段名，还可以在字段名和别称中间添加关键词‘AS’

4．汉语别称必须 应用反引号 `

5．实例实际操作查看名字和单位，建立時间

select 
	ename AS name,
	deptno dn,
	fcd as `建立時间`
from emp;

3） 算术运算符

4）常用函数

1．求数量（count）

select count(*) cnt from emp;

2．求薪水的最高值（max）

select max(sal) max_sal from emp;

3．求单位薪水的极小值（min）

select deptno，min(sal) min_sal from emp group by deptno;

4．求薪水的总数（sum）

select sum(sal) sum_sal from emp; 

5．求薪水的均值（avg）

 select avg(sal) avg_sal from emp;

5 ）Limit 句子

典型性的查看会回到几行数据信息。LIMIT 子句用以限定回到的个数。

select * from emp limit 5;

2、Where 句子

1．应用 WHERE 子句，将不符合条件的行过虑掉

2．WHERE 子句紧随 FROM 子句

1） 较为操作符（Between/In/ Is Null）

下边表格中叙述了谓词运算符，这种运算符一样能够 用以 JOIN…ON 和 HAVING 句子中。

2） Like 和 RLike

1．应用 LIKE 计算挑选相近的值

2．挑选标准能够 包括标识符或数据:

% 意味着零个或好几个标识符(随意字符)。

_ 意味着一个字符。

3．RLIKE 子句是 Hive 中这一作用的一个拓展，其能够 根据 Java 的正则表达式这一更强劲的语言表达来特定配对标准。

eg:

（1）搜索以 2 开始工资的员工信息

select * from emp where sal LIKE '2%';

（2）搜索第二个标值为 2 的工资的员工信息

select * from emp where sal LIKE '_2%';

（3）搜索工资中带有 2 的员工信息

select * from emp where sal RLIKE '[2]';

3）逻辑运算符（And/Or/Not）

3、排序查看

1）Group By 句子

GROUP BY 句子一般 会和聚合函数一起应用，依照一个或是好几个整队結果开展排序，随后对每一个组实行汇聚实际操作。

eg：

（1）测算 emp 表每一个单位的平均收入

select t.deptno, avg(t.sal) avg_sal

from emp t

group by t.deptno;

（2）测算 emp 每一个单位中每一个职位的最大工资

select t.deptno, t.job, max(t.sal) max_sal

from emp t

group by t.deptno, t.job;

2） Having 句子

having 与 where 不同之处

（1）where 对于表格中的列充分发挥，查看数据信息；having 对于查看結果中的列充分发挥，挑选数据信息。

（2）where 后边不可以写聚合函数，而 having 后边能够 应用聚合函数。

（3）having 只用以 group by 排序统计分析句子。

eg：

求每一个单位的均值工资超过 2000 的单位求每一个单位的平均收入

select deptno, avg(sal)

from emp

group by deptno;

求每一个单位的均值工资超过 2000 的单位

select deptno, avg(sal) avg_sal

from emp

group by deptno

having avg_sal > 2000;

4、 Join 句子

1）等价 Join

Hive 适用一般 的 SQL JOIN 句子，可是只适用等值连接，不兼容非等值连接。

eg:

select

​ e.empno,

​ e.ename,

​ d.deptno,

​ d.dname

from emp e

join dept d on e.deptno != d.deptno;

会出错，'>'和'<' 这类也不兼容

2） 表的别称

1．益处

（1）应用别称能够 简单化查看。

（2）应用表名作为前缀能够 提升 实行高效率。<提升 的很少，但是也算能够 提升提升 的点，与此同时也提升sql的易读性>

3） 内连接

仅有开展联接的2个表上都存有与联接标准相符合的数据信息才会被保存出来。

4） 左外连接左外连接

JOIN 运算符左侧表格中合乎 WHERE 子句的全部纪录可能被回到。

5）右外连接右外连接

JOIN 运算符右侧表格中合乎 WHERE 子句的全部纪录可能被回到。

6） 满外连接

可能回到全部表格中合乎 WHERE 句子标准的全部纪录。假如任一表的特定字段名沒有满足条件的值得话，那麼就应用 NULL 值取代。

7） 多表联接

留意：联接 n 个表，最少必须 n-1 个联接标准。

比如：联接三个表，最少必须 2个联接标准。

eg:

1．建立部位表

create table if not exists default.location( loc int, loc_name string) row format delimited fields terminated by '\t';

2．导进数据信息

hive (default)>load data local inpath '/opt/module/data/location.txt' into table default.location;

3．多表连接查询

SELECT e.ename, d.deptno, l.loc_name

FROM emp e

JOIN dept d ON d.deptno = e.deptno

JOIN location l ON d.loc = l.loc;

大部分状况下，Hive 会对每对 JOIN 联接目标运行一个 MapReduce 每日任务。本例中会最先运行一个 MapReduce job 对表 e 和表 d 开展联接实际操作，随后会重新启动一个 MapReduce job 将第一个 MapReduce job 的輸出和表 l;开展联接实际操作。

留意：为何并不是表 d 和表 l 先开展联接实际操作呢？这是由于 Hive 一直依照从左往右的次序实行的。

8） 笛卡尔积

1．笛卡儿聚会在下面标准下造成

（1）省去联接标准

（2）联接标准失效

（3）全部表格中的全部行相互之间联接

留意：打开严格模式得话，笛卡尔积这类查看会出错

9） 联接谓词中不兼容 or

select
	e.empno,
	e.ename,
	d.deptno
from emp e 
join dept d on e.deptno=d.deptno or e.ename=d.dname;

FAILED: SemanticException [Error 10019]: Line 10:3 OR not supported in JOIN currently 'dname'

5、排列

1）全局性排列（Order By）

Order By：全局性排列，一个 Reducer

1．应用 ORDER BY 子句排列

ASC（ascend）: 降序（默认设置）

DESC（descend）: 降序

2．ORDER BY 子句在 SELECT 句子的末尾

2） 依照别称排列

eg:

依照职工工资的 2 倍排列

select ename, sal*2 twosal from emp order by twosal;

3） 好几个列排列

eg:

依照单位降序和薪水降序排列

select ename, deptno, sal from emp order by deptno asc, sal desc ;

4） 每一个 MapReduce 內部排列/区域内排列（Sort By）

Sort By：每一个 Reducer 內部开展排列，对全局性結果集而言并不是排列。

eg:

1．设定 reduce 数量

hive (default)> set mapreduce.job.reduces=3;

2．查询设定 reduce 数量

hive (default)> set mapreduce.job.reduces;

3．依据单位序号降序查询员工信息

hive (default)> select * from emp sort by empno desc;

4．将查看結果导到文档中（依照单位序号降序排列）

hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;

5） 系统分区排列（Distribute By）

Distribute By：相近 MR 中 partition，开展系统分区，融合 sort by 应用。

留意，Hive 规定 DISTRIBUTE BY 句子要写在 SORT BY 句子以前。

针对 distribute by 开展检测，一定要分派多 reduce 开展解决，不然没法见到 distribute by 的实际效果。

eg：

依据单位序号查询每一个单位，再依据职工序号降序查询每一个单位中职工

先依照单位序号系统分区，再依照职工序号降序排列。

hive (default)> set mapreduce.job.reduces=3;

hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

6） Cluster By

当 distribute by 和 sorts by 字段名同样时，能够 应用 cluster by 方法。

cluster by 除开具备 distribute by 的作用外还兼顾 sort by 的作用。可是排列只有是降序排列，不可以特定排列标准为 ASC 或是 DESC。

下列二种书写等额的

hive (default)> select * from emp cluster by deptno; 
hive (default)> select * from emp distribute by deptno sort by deptno;

留意：依照单位序号系统分区，不一定便是固定不动死的标值，能够 是 20 号和 30 号单位分到一个系统分区里边去。

6） 分桶及取样查看<非常少用>

系统分区对于的是数据信息的储存途径；分桶对于的是数据库文件。

系统分区给予一个防护数据信息和提升查看的便捷方法。但是，并不是全部的数据都可以产生有效的系统分区，尤其是以前所提及过的要明确适合的区划尺寸这一顾虑。

分桶是将数据转化成更非常容易管理方法的多个一部分的另一个技术性。

eg:

先建立分桶表，根据立即导进数据库文件的方法

（1）数据信息提前准备

student.txt

（2）建立分桶表,和一个一般表

create table stu_buck(id int, name string) clustered by(id) into 4 buckets row format delimited fields terminated by '\t';

create table stu(id int, name string) row format delimited fields terminated by '\t';

向一般的 stu 表格中导进数据信息

load data local inpath '/opt/module/datas/student.txt' into table stu;

（3）查询表结构

hive (default)> desc formatted stu_buck;

Num Buckets: 4

（4）设定特性,根据子查询的方法导进数据信息

hive (default)> set hive.enforce.bucketing=true;

hive (default)> set mapreduce.job.reduces=-1;

hive (default)> insert into table stu_buck select id, name from stu;

分桶取样查看

针对十分大的数据，有时候客户必须 应用的是一个具备象征性的查看結果而不是所有結果。Hive 能够 根据对表开展取样来达到这一要求。查看表 stu_buck 中的数据信息。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample 是取样句子，英语的语法：TABLESAMPLE(BUCKET x OUT OF y) 。

y 务必是 table 总 bucket 数的倍率或是因素。hive 依据 y 的尺寸，决策取样的占比。比如，table 一共分了 4 份，当 y=2 时，提取(4/2=)2 个 bucket 的数据信息，当 y=8 时，提取(4/8=)1/2 个 bucket 的数据信息。

x 表明从哪一个 bucket 逐渐提取，假如必须 取好几个系统分区，之后的系统分区号为当今系统分区号再加上 y。

比如，table 总 bucket 数为 4，tablesample(bucket 1 out of 2)，表明一共提取（4/2=）2 个 bucket的数据信息，提取第 1(x)个和第 3(x y)个 bucket 的数据信息。

留意：x 的值务必不大于 y 的值，不然

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

6、常见查看涵数

1）条件函数

2）数学函数

3）结合涵数

4）数据转换涵数

5）日期涵数

6）标识符涵数

7）聚合函数

8）表生成函数

9）其他涵数

collect

Hive中collect有关的涵数有collect_list和collect_set。
他们全是将排序中的某列变为一个二维数组回到，不一样的是collect_list不去重复而collect_set去重复。还能够运用collect来提升group by的限定，Hive中在group by查看的情况下规定发生在select后边的列都务必是发生在group by后边的，即select列务必是做为排序根据的列，可是有的情况下大家想依据A开展排序随后随意取下每一个排序中的一个B，例如依照客户开展排序，随后随意取出一个他看了的视頻名字：

select username, collect_list(video_name)[0] 
from user_visit_video 
group by username;

注：与之相匹配的是explode，行转列

Lateral View

lateral view是Hive中给予给表生成函数的融合，它能够 处理表生成函数不可以加上附加的select列的难题。
lateral view实际上 便是用于和相近explode这类表生成函数涵数联用的，lateral view会将表生成函数转化成的結果放进一个虚似表格中，随后这一虚似表会和键入行开展join来做到联接表生成函数外的select字段名的目地。

• 文件格式一

lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*

lateral view在UDTF前应用，表明联接UDTF所瓦解的字段名。

UDTF(expression)：应用的表生成函数，比如explode()。

tableAlias：表明表生成函数变换的虚似表的名字。

columnAlias：表明虚似表的虚似列名称，假如瓦解以后有一个列，则写一个就可以；假如瓦解以后有好几个列，依照列的次序在括弧中申明全部虚似字段名，以分号分隔。

eg:

统计人员表中国共产党有多少种喜好、多少个大城市?

select count(distinct(myCol1)), count(distinct(myCol2)) from psn

LATERAL VIEW explode(likes) myTable1 AS myCol1

LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;

• 文件格式二

from basetable (lateral view)*

在from子句中应用，一般和文件格式一配搭应用，这一文件格式仅仅表明了lateral view的应用部位。
from子句后边还可以跟好几个lateral view句子，应用空格符间距就可以了。

• 文件格式三

from basetable (lateral view outer)*

它比文件格式二仅仅多了一个outer，这一outer的功效是在表生成函数变换列的情况下将在其中的空也给展现出去，UDTF默认设置是忽视輸出空的，再加上outer以后，会将空也輸出，表明为NULL。这一作用是在Hive0.12是逐渐适用的。

7、窗口函数

一般的聚合函数汇聚的行集是组,窗口函数汇聚的行集是对话框。因而,一般的聚合函数每一组(Group by)只回到一个值，而窗口函数则能为对话框中的每排都回到一个值。简易了解便是对查看的結果空出一列，这一列能够 是汇聚值，还可以是排列值。 窗口函数一般分成两大类——汇聚窗口函数和排列窗口函数。

窗口函数的启用文件格式为：

涵数名(列) OVER(选择项)

OVER 关键词表明把涵数当做窗口函数而不是聚合函数。SQL规范容许将全部聚合函数用作窗口函数，应用OVER关键词来区别这二种使用方法。

OVER()

特定剖析涵数工作中的数据信息对话框尺寸，这一数据信息对话框尺寸很有可能会伴随着行的转变而转变；

在over()里边用的：

​ CURRENT ROW：当今行；

​ n PRECEDING：向前 n 行数据信息；

​ n FOLLOWING：往后面 n 行数据信息；

​ UNBOUNDED：起始点，

​ UNBOUNDED PRECEDING 表明从前边的起始点，

​ UNBOUNDED FOLLOWING 表明到后边的终点站；

在over()前要的:

​ LAG(col,n)：向前第 n 行数据信息；

​ LEAD(col,n)：往后面第 n 行数据信息；

​ NTILE(n)：把井然有序系统分区中的行派发到特定数据信息的组里，每个组是序号，序号从 1 逐渐，针对每一行，NTILE 回到此番隶属的组的序号。留意：n 务必为 int 种类。

汇聚窗口函数

SUM

1．数据信息提前准备：

cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4

2．建立当地 business.txt，导进数据信息

[chaos@hadoop102 datas]$ vi cookie1.txt

3．建立 hive 表并导进数据信息
create table cookie1(cookieid string, createtime string, pv int) row format delimited fields terminated by ',';
load data local inpath "/opt/module/datas/cookie1.txt" into table cookie1;

4．查看实例

select
cookieid,
createtime,
pv,
sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1,
sum(pv) over (partition by cookieid order by createtime) as pv2,
sum(pv) over (partition by cookieid) as pv3,
sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4,
sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5,
sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from cookie1;

表明：

pv1: 排序内从起始点到当今行的pv积累，如，11号的pv1=16号的pv 11号的pv, 12号=16号 11号 12号
pv2: 同pv1
pv3: 排序内(cookie1)全部的pv累积
pv4: 排序内当今行 向前3行，如，11号=16号 11号， 12号=16号 11号 12号， 13号=16号 11号 12号 13号， 14号=11号 12号 13号 14号
pv5: 排序内当今行 向前3行 往后面1行，如，14号=11号 12号 13号 14号 15号=5 7 3 2 4=21
pv6: 排序内当今行 往后面全部行，如，13号=13号 14号 15号 16号=3 2 4 4=13，14号=14号 15号 16号=2 4 4=10

如果不特定ROWS BETWEEN,默认设置为从起始点到当今行
如果不特定ORDER BY，则将排序内全部值累积
注：别的AVG，MIN，MAX，和SUM使用方法一样

AVG

select
cookieid,
createtime,
pv,
avg(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 默认设置为从起始点到当今行
avg(pv) over (partition by cookieid order by createtime) as pv2, --从起始点到当今行，結果同pv1
avg(pv) over (partition by cookieid) as pv3, --排序内全部行
avg(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --当今行 向前3行
avg(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --当今行 向前3行 往后面1行
avg(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --当今行 往后面全部行
from cookie1;

MIN

select
cookieid,
createtime,
pv,
min(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 默认设置为从起始点到当今行
min(pv) over (partition by cookieid order by createtime) as pv2, --从起始点到当今行，結果同pv1
min(pv) over (partition by cookieid) as pv3, --排序内全部行
min(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --当今行 向前3行
min(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --当今行 向前3行 往后面1行
min(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --当今行 往后面全部行
from cookie1;

MAX

select
cookieid,
createtime,
pv,
max(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 默认设置为从起始点到当今行
max(pv) over (partition by cookieid order by createtime) as pv2, --从起始点到当今行，結果同pv1
max(pv) over (partition by cookieid) as pv3, --排序内全部行
max(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --当今行 向前3行
max(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --当今行 向前3行 往后面1行
max(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --当今行 往后面全部行
from cookie1;

排列窗口函数（编码序列涵数）

表明依据COL一分组，在排序內部依据COL2排列， 而此函数计算的值就表明每一组內部排列后的次序序号 （该序号在组内是持续而且唯一的)。

留意： 编码序列涵数不兼容WINDOW子句。（ROWS BETWEEN）

NTILE

NTILE(n)，用以将排序数据信息依照次序切分为n片，回到当今切成片值
NTILE不兼容ROWS BETWEEN，例如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
假如切成片不匀称，默认设置提升第一个切成片的遍布

eg:

select
cookieid,
createtime,
pv,
ntile(2) over (partition by cookieid order by createtime) as rn1, --排序内将数据信息分为2片
ntile(3) over (partition by cookieid order by createtime) as rn2, --排序内将数据信息分为2片
ntile(4) over (order by createtime) as rn3 --将全部数据信息分为4片
from cookie.cookie2
order by cookieid,createtime;

统计分析一个cookie，pv最多的前1/3的天

select
cookieid,
createtime,
pv,
ntile(3) over (partition by cookieid order by pv desc ) as rn
from cookie.cookie2;

--rn = 1 的纪录，便是大家要想的結果

ROW_NUMBER

ROW_NUMBER() –从1逐渐，依照次序，转化成排序内纪录的编码序列
ROW_NUMBER() 的应用领域十分多，再例如，获得排序内排列第一的纪录;获得一个session中的第一条refer等。

eg:

-- 依照pv降序排序，转化成排序内每日的pv成绩

select
cookieid,
createtime,
pv,
row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2;

-- 因此假如必须 取每一组的前3名，只必须 rn<=3就可以，合适TopN

RANK/DENSE_RANK

—RANK() 转化成数值数据在排序中的排行，排行相同会在成绩中留有位置
—DENSE_RANK() 转化成数值数据在排序中的排行，排行相同会在成绩中不容易留有位置

eg:

select
cookieid,
createtime,
pv,
rank() over (partition by cookieid order by pv desc) as rn1,
dense_rank() over (partition by cookieid order by pv desc) as rn2,
row_number() over (partition by cookieid order by pv desc) as rn3
from cookie.cookie2
where cookieid='cookie1';

結果：

rn1 rn2 rn3
1 1 1
2 2 2
3 3 3
3 3 4
5 4 5
6 5 6
7 6 7

ROW_NUMBER、RANK和DENSE_RANK的差别

row_number： 按序序号，没留位置
rank： 按序序号，同样的值编同样号，留位置
dense_rank： 按序序号，同样的值编同样的号，没留位置

案例：

1．数据信息提前准备：

name，orderdate，cost

jack,2021-01-01,10

tony,2021-01-02,15

jack,2021-02-03,23

tony,2021-01-04,29

jack,2021-01-05,46

jack,2021-04-06,42

tony,2021-01-07,50

jack,2021-01-08,55

mart,2021-04-08,62

mart,2021-04-09,68

neil,2021-05-10,12

mart,2021-04-11,75

neil,2021-06-12,80

mart,2021-04-13,94

2．建立当地 business.txt，导进数据信息

[chaos@hadoop102 datas]$ vi business.txt

3．建立 hive 表并导进数据信息

create table business(name string, orderdate string, cost int)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/datas/business.txt" into table business;

4．按要求查看数据信息

（1）查看在 2020年 4 月份选购过的消费者及总人数

（2）查看消费者的选购清单及月选购总金额

（3）以上的情景,要将 cost 依照日期开展累积

（4）查询消费者之前的选购時间

（5）查看前 20%時间的订单信息