Hive SQL基本使用

Hive介绍

Hive是一个数据仓库基础的应用工具，在Hadoop中用来处理结构化数据，它架构在Hadoop之上，通过SQL来对数据进行操作。

Hive 查询操作过程严格遵守Hadoop MapReduce 的作业执行模型，Hive 将用户的Hive SQL 语句通过解释器转换为MapReduce 作业提交到Hadoop 集群上，Hadoop 监控作业执行过程，然后返回作业执行结果给用户。Hive 并非为联机事务处理而设计，Hive 并不提供实时的查询和基于行级的数据更新操作。Hive 的最佳使用场合是大数据集的批处理作业。

数据库操作

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]		--一些对数据库的描述信息
[LOCATION hdfs_path]			--存储的路径，此目录就是这个数据库，这个数据库就是那个目录，两者是映射关系
[WITH DBPROPERTIES (property_name=property_value, ...)]; 	--数据库的一些属性

LOCATION 指定数据存储在HDFS的路径

WITH DBPROPERTIES 可设置KV格式的数据库属性，KEY可以自定义

CREATE DATABASE IF NOT EXISTS TEST 
    COMMENT 'THIS IS TEST DATABASE'
    LOCATION '/USER/ROOT/' 
    WITH DBPROPERTIES('CREATOR'='XXX','DATE'='2016-11-12');

查询数据库

-- 显示数据库
show databases;

-- 查询数据库具体信息
desc database default;

-- 查看数据库扩展信息
desc database extended default;

-- 切换当前数据库
use default

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

默认情况下Hive不允许删除表里有数据的数据库，使用CASCADE会在删除数据库时删除该库下的所有对象，RESTRICT即为默认情况设置

-- 如果数据库不是空的，不能删除
drop database test;

-- 强制删库
drop database test cascade;

修改数据库

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。只能修改数据库的属性

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

alter database test set dbproperties("111"="222","333"="444");

表操作

创建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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]
  [SKEWED BY (col_name, col_name, ...)
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
  [AS select_statement];

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（3）COMMENT：为表和列添加注释。

（4）PARTITIONED BY 创建分区表。

（5）CLUSTERED BY 创建分桶表。

（6）SORTED BY 不常用，对桶中的一个或多个列另外排序

（7）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确定表的具体的列的数据。

SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。

（8）STORED AS 指定存储文件类型

常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

（9）LOCATION ：指定表在HDFS上的存储位置。

（10）AS：后跟查询语句，根据查询结果创建表。

（11）LIKE允许用户复制现有的表结构，但是不复制数据。

示例：

create table test
(id int comment "ID",name string comment "Name")
comment "Test Table"
row format delimited fields terminated by '\t'
location "/test.db"
tblproperties("aaa"="bbb");

设置行、字段分隔符

行与字段分隔符可让Hive在从文件加载数据时明确数据是怎么分隔的，具体设置方法如下：

-- 设置字段分隔符
ROW FORMAT DELIMITED  FIELDS TERMINATED BY char

Hive 默认使用'\001'，'\001'代表控制符，用ctrl+v然后再ctrl+a可以输入这个控制符；分隔符可用八进制ASCII码表示，或者直接写字符，不过特殊字符需要转义，如空格'\t'，换行'\n'。

-- 设置转义符
ROW FORMAT DELIMITED  FIELDS ESCAPED BY  char

如果字段中有些符号需要保留，可以用ESCAPED BY设置转义符。

-- 设置行分隔符
LINES TERMINATED BY char

一般设置为 '\n'

分隔符的设定有：

ROW FORMAT DELIMITED 分隔符设置开始语句
FIELDS TERMINATED BY：设置字段与字段之间的分隔符
COLLECTION ITEMS TERMINATED BY：设置一个复杂类型（array,struct)字段的各个item之间的分隔符
MAP KEYS TERMINATED BY：设置一个复杂类型(Map)字段的key value之间的分隔符
LINES TERMINATED BY：设置行与行之间的分隔符，必须是所有分隔语句的最后，不然会报错。

例子：

ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'

内部表

表的元数据和数据都由Hive维护，创建表时会自动在HDFS的/user/hive/warehouse 目录下创建与表同名的文件夹，删除表时HDFS上的数据和文件夹会删除

CREATE TABLE IF NOT EXISTS person 
    (id int COMMENT '编号',name string COMMENT '姓名') 
    COMMENT '人员表' 
    STORED AS TEXTFILE;

外部表（EXTERNAL）

删除表时只删除元数据，HDFS上的数据和文件夹保留，创建表时如果指定了HDFS的路径，则数据文件会直接存在指定路径下；如果创建表时不指定HDFS路径，会默认在/user/hive/warehouse目录创建表文件夹。

CREATE EXTERNAL TABLE IF NOT EXISTS person 
    (id int COMMENT '编号',name string COMMENT '姓名') 
    COMMENT '人员表' 
    STORED AS TEXTFILE 
    LOCATION '/user/hdfs/';

临时表（TEMPORARY）

临时表的数据和元数据都由Hive维护，只对当前Session有效，Session退出时表自动删除；

CREATE TEMPORARY TABLE IF NOT EXISTS person_tmp 
    (id int COMMENT '编号',name string COMMENT '姓名') 
    COMMENT '人员临时表' 
    STORED AS TEXTFILE 
    LOCATION '/user/hdfs/';

外部表和内部表的转换

如果想创建外部表，但命令写错了，这种情况下不能直接删除表，因为其中的数据也会被删除

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

可以将其转换成外部表

alter table test set tblproperties("EXTERNAL"="TRUE");

将外部表转换成内部表

alter table test set tblproperties("EXTERNAL"="false");

Create Table As Select (CTAS)

Create Table 部分使用Select的查询结果创建目标表，且可以指定SerDe 和存储格式，不过也存在以下限制：

SerDe是Serializer / Deserializer的缩写，Hive将SerDe接口用于IO。该接口既处理序列化和反序列化，又将序列化的结果解释为要处理的单个字段。
SerDe允许Hive从表中读取数据，并将其以任何自定义格式写回HDFS

1）要创建的表不能是分区表；
2）要创建的表不能是外部表；
3）要创建的表不能是List Bucketing表。

CREATE TABLE new_person 
    ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"  
    STORED AS RCFile  
    AS 
select id,name from person;

Create Table Like

Create Table table1 Like table2 会创建与table2 结构完全相同的table1，通常用于复制表结构但不复制数据。

查看表

desc formatted t_test;

修改表

-- 重命名表
alter table test rename to testRename;

-- 修改列 把id列数据类型改为string类型
alter table testrename change id id string;

-- 增加列
alter table testrename add columns(class string);

-- 重写所有的列（可用于删除指定字段）
alter table testrename replace columns(id double,name string);

-- 很少使用，因为hive处理的一般是历史数据，比如订单历史数据，信息不改变，元信息也就不改变

-- 删除表
drop table testrename;

-- 用查询结果建表
create table stu_res as select * from testrename where id=1001;

删除表

DROP TABLE [IF EXISTS] table_name [PURGE];

如果被删除的表有视图引用，在删除时不会警告，需要手动检查或重建视图

如果指定了PURGE，删除表时数据会从HDFS上完全清除，而不会转入回收站

截断表

TRUNCATE TABLE table_name [PARTITION partition_spec];
partition_spec:
(partition_column = partition_col_value, partition_column = partition_col_value, ...)

截断表会清空表里的数据，也可以指定清空特定分区

示例：

TRUNCATE TABLE table_name PARTITION (dt='20080808');

分区表、桶

hive有一个巨大的缺点：数据没有索引，在查找所有数据时，需要全表扫描，在数据量很大的时候，做全表扫描的时间复杂度太高，hive对此有两个解决办法：分区和分桶。

分区表

分区表：hive可以根据某一列值的不同，把数据分成几个区（文件夹），查询时在一起显示，但是存储在不同的文件中，查表时，选择分区，可以减小数据扫描量。

Hive中没有复杂的分区类型（List,Range,Hash）、各种复合分区，分区列不是表中的实际字段而是一个伪列，创建表时可以指定PARTITION BY 子句创建一个或多个分区，每个分区在HDFS中会自动创建一个独立的文件夹。
分区键不能和列名同名，不然会报 "FAILED: Error in semantic analysis: Column repeated in partitioning columns,"

Hive中分区表分两类：静态分区、动态分区；

静态分区

Hive默认是静态分区，静态分区在插入数据时需要指定分区键值，好让数据插入指定分区中

CREATE TABLE IF NOT EXISTS part_person 
(
	id string,
	name string
)
PARTITIONED BY (date string)
STORED AS TEXTFILE;

insert into part_person PARTITION(date='2016-11-11')  values('2','ss1');
insert into part_person PARTITION(date='2016-11-13')  values('3','ss2');

动态分区

如果数据量很大，每条插入语句都要指定键值很麻烦，于是就有了动态分区，让Hive自动根据数据插入到指定分区内。

-- 创建普通表，并插入测试数据
CREATE TABLE IF NOT EXISTS person (id int,name string,dt string,country string)  STORED AS TEXTFILE;
insert into person values(1,'dd','2016-11-11','jp');
insert into person values(2,'ee','2016-11-22','cn');
insert into person values(3,'gg','2016-11-14','jp');
insert into person values(4,'ff','2016-11-11','cn');
insert into person values(5,'tt','2016-11-22','jp');
insert into person values(6,'aa','2016-11-14','cn');
insert into person values(7,'bb','2016-11-11','cn');
insert into person values(8,'ss','2016-11-14','jp');
insert into person values(9,'gg','2016-11-11','cn');
insert into person values(10,'sr','2016-11-22','cn');
insert into person values(11,'4e','2016-11-11','jp');
insert into person values(12,'g5','2016-11-14','cn');
insert into person values(13,'1hg','2016-11-14','cn');
insert into person values(14,'haf','2016-11-22','jp');
insert into person values(15,'jhj','2016-11-14','cn');
insert into person values(16,'xc','2016-11-22','cn');
insert into person values(17,'nb','2016-11-11','jp');
insert into person values(18,'2ds','2016-11-22','jp');
insert into person values(19,'jse','2016-11-11','jp');
insert into person values(20,'ngh','2016-11-22','cn');
insert into person values(21,'aw4','2016-11-11','jp');
insert into person values(22,'4st','2016-11-14','cn');

-- 创建一个有两个分区的分区表
CREATE TABLE IF NOT EXISTS person_d_p 
(id int,name string) 
PARTITIONED BY (dt string,country string) 
STORED AS TEXTFILE;

向分区表插入数据：

-- 使用INSERT INTO SELECT 插入数据
hive> INSERT INTO person_d_p PARTITION(dt,country) SELECT * FROM person;

FAILED: SemanticException [Error 10096]: 
Dynamic partition strict mode requires at least one static partition column. 
To turn this off set hive.exec.dynamic.partition.mode=nonstrict

报错说明需要设置动态分区模式，使用动态分区需要设置以下参数：

hive.exec.dynamic.partition

默认值：false

使用动态分区必须设置为true

hive.exec.dynamic.partition.mode

默认值：strict

表示至少需要一个静态分区，一般使用nonstrict，即所有分区都是动态分区

hive.exec.max.dynamic.partitions.pernode

默认值：100

每个MR节点允许创建的最大分区数，如果实际的分区数超过设置的值会报错

Fatal error occurred when node tried to create too many dynamic partitions.

hive.exec.max.dynamic.partitions

默认值：1000

一个动态分区语句，在所有节点允许创建的最大分区数，同上

hive.exec.max.created.files

默认值：100000

所有节点可以创建的最大文件数，根据需要调整

根据需要设置参数：

SET hive.exec.dynamic.partition=true; 
SET hive.exec.dynamic.partition.mode=nonstrict; 
SET hive.exec.max.dynamic.partitions.pernode=1000;
SET hive.exec.max.dynamic.partitions=10000;

INSERT INTO person_d_p PARTITION(dt,country) SELECT * FROM person;

示例：

-- 显示所有分区
hive> show partitions person_d_p;
OK
dt=2016-11-11/country=cn
dt=2016-11-11/country=jp
dt=2016-11-14/country=cn
dt=2016-11-14/country=jp
dt=2016-11-22/country=cn
dt=2016-11-22/country=jp
Time taken: 0.034 seconds, Fetched: 6 row(s)

-- 显示指定分区
hive> show partitions person_d_p partition(dt='2016-11-14',country='cn');
OK
dt=2016-11-14/country=cn

动态分区和静态分区还有一点区别就是静态分区不管有没有数据插入一定会创建分区，动态分区只有在插入数据的时候才创建分区；

动态分区会为每个分区创建一个reduce任务，当分区数比较多时，需要设置合理的mapred.reduce.tasks参数，以避免直接把集群搞挂掉。

桶

Hive允许对表和分区以bucket的形式进一步划分数据，这在对表进行JOIN操作和数据采样（sampling）时能获得较高的性能，如果两个表的JOIN字段都分桶了，在JOIN时将大大减少读取的数据量；可以对一个以上的列分桶，分桶的方式采用对列值HASH除以桶的个数求余来决定数据落在哪个桶里。

CREATE TABLE IF NOT EXISTS person_bkt
(
    id int,
    name string,
    country string,
    dt string
) 
CLUSTERED BY(country) SORTED BY (id) INTO 4 BUCKETS
STORED AS TEXTFILE;

创建桶时以CLUSTERED BY 子句指定字段，多个字段以逗号分隔；SORTED BY子句指定桶里的数据以哪个字段排序，默认为升序；INTO 指定分多少个桶。

需要注意的是，SORTED BY并不会在插入数据时进行排序，需要显式指定排序

加载数据需要开启下面参数：

set hive.enforce.bucketing=true;

向桶里载入数据，需要指定order by 子句，且排序字段与建表SORTED BY 字段一致

INSERT INTO person_bkt SELECT id,name,country,dt FROM person order by id;

查看hdfs目录，发现有4个文件：

hive> dfs -ls /user/hive/warehouse/person_bkt;
Found 4 items
-rwxrwxrwt   3 root hive          0 2016-11-16 17:41 /user/hive/warehouse/person_bkt/000000_0
-rwxrwxrwt   3 root hive          0 2016-11-16 17:41 /user/hive/warehouse/person_bkt/000001_0
-rwxrwxrwt   3 root hive        200 2016-11-16 17:41 /user/hive/warehouse/person_bkt/000002_0
-rwxrwxrwt   3 root hive        239 2016-11-16 17:41 /user/hive/warehouse/person_bkt/000003_0

但是实际只有两个文件有数据，因为country字段只有两组数据‘cn’和‘jp’。

查询数据，数据是以country分组，id升序排列

hive> select * from person_bkt;
OK
1   dd  jp  2016-11-11
3   gg  jp  2016-11-14
5   tt  jp  2016-11-22
8   ss  jp  2016-11-14
11  4e  jp  2016-11-11
14  haf jp  2016-11-22
17  nb  jp  2016-11-11
18  2ds jp  2016-11-22
19  jse jp  2016-11-11
21  aw4 jp  2016-11-11
2   ee  cn  2016-11-22
4   ff  cn  2016-11-11
6   aa  cn  2016-11-14
7   bb  cn  2016-11-11
9   gg  cn  2016-11-11
10  sr  cn  2016-11-22
12  g5  cn  2016-11-14
13  1hg cn  2016-11-14
15  jhj cn  2016-11-14
16  xc  cn  2016-11-22
20  ngh cn  2016-11-22
22  4st cn  2016-11-14
Time taken: 0.03 seconds, Fetched: 22 row(s)

桶数据抽样

分桶之后怎么查？Hive提供了tablesample关键字进行数据抽样，可以对桶里的数据进行抽样查询

select * from person_bkt tablesample (bucket 2 out of 4 on id);

2代表从第几个桶开始，4代表取多少个桶，必须是桶总数的倍数，可以使用desc formatted tablename 查看表的桶数量。

Hive 基本操作

插入数据

向表中装载数据（Load）

load data [local] inpath '路径' [overwrite] into table 表名 [partition (分区字段=值,…)];

overwrite：表示覆盖表中已有数据，否则表示追加

如：从本地文件系统加载数据到hive表

load data local inpath '/home/hdfs/data/test.txt' into table test;

insert into语句，见下一小节

insert values语句

Hive 0.14版本开始，支持INSERT…VALUES语法，具体语法如下：

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]
 
values_row:
(value [, value ...])
value可以为null或者任意SQL语法

示例：

insert OVERWRITE table score
values('悟空', '语文', 87),
('悟空', '数学', 95),
('悟空', '英语', 68),
('八戒', '语文', 94),
('八戒', '数学', 56),
('八戒', '英语', 84),
('张飞', '语文', 64),
('张飞', '数学', 86),
('张飞', '英语', 84),
('小乔', '语文', 65),
('小乔', '数学', 85),
('小乔', '英语', 78)

创建表时加载数据

create table if not exists 表名 as select_statement1 FROM from_statement;

import数据到指定Hive表中

先用export导出后，再将数据导入(export和import主要用于两个Hadoop平台集群之间Hive表迁移)

import table 表名 from '路径';

如：

从a集群中导出hive表数据：

export table default.student to '/wcinput/export';

向b集群中导入数据到hive表：

import table student from '/wcinput/export' ;

删除数据

hive 表删除部分数据不支持使用 Delete From table_name where ...语句

hive表删除数据要分为不同的粒度：table、partition、partition内

有 Partition 分区表

有分区字段的数据表，删除数据时要注意分两种情况：

1、根据分区删除数据，可以删除满足条件的分区，具体代码格式如下：

--删除一个分区的数据
alter table table_name drop partition(partiton_name='value')
--删除多个分区的数据
alter table table_name drop partition(partiton_name<'value')
alter table table_name drop partition(partiton_name<='value')
alter table table_name drop partition(partiton_name>'value')
alter table table_name drop partition(partiton_name>='value')

2、删除分区内部的部分数据，这时使用重写方式对满足条件的分区进行 overwrite 操作，并通过 where 来限定需要的信息，未过滤的的信息将被删除，具体代码格式如下：

insert overwrite table table_name partition(partition_name='value') 
select column1,column2，column2 FROM table_name
where partition_name='value' and column2 is not null

没有 Partition 分区表

1、直接清空数据，再插入需要的数据，具体代码格式如下：

truncate table database.tablename

2、通过 overwrite 对所有数据重写，具体代码格式如下：

insert overwrite table table_name 
select * from table_name WHERE column is not null  --限制条件可以自行修改

Hive insert into语句用法

在Hive0.8开始支持Insert into语句，它的作用是在一个表格里面追加数据。

标准语法语法如下：

-- 用法一：
INSERT OVERWRITE TABLE tablename1 [PARTITION 
(partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] 
select_statement1 FROM from_statement;
 
-- 用法二：
INSERT INTO TABLE tablename1 [PARTITION 
(partcol1=val1, partcol2=val2 ...)] 
select_statement1 FROM from_statement;

举例：

hive> insert into table cite
　　> select * from tt;

这样就会将tt表格里面的数据追加到cite表格里面。并且在cite数据存放目录生成了一个新的数据文件，这个新文件是经过处理的，列之间的分割是cite表格的列分割符，而不是tt表格列的分隔符。

(1)、如果两个表格的维度不一样，将会插入错误：

hive> insert into table cite
    > select * from cite_standby;
 
FAILED: SemanticException [Error 10044]: Line 1:18 Cannot insert into
target table because column number/types are different 'cite': 
Table insclause-0 has 2 columns, but query has 1 columns.

从上面错误提示看出，查询的表格cite_standby只有一列，而目标表格（也就是需要插入数据的表格）有2列，由于列的数目不一样，导致了上面的语句不能成功运行，我们需要保证查询结果列的数目和需要插入数据表格的列数目一致，这样才行。

(2)、在用extended关键字创建的表格上插入数据将会影响到其它的表格的数据，因为他们共享一份数据文件。

(3)、如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，比如如果查询出来的数据类型为int，插入表格对应的列类型为string，可以通过转换将int类型转换为string类型；但是如果查询出来的数据类型为string，插入表格对应的列类型为int，转换过程可能出现错误，因为字母就不可以转换为int，转换失败的数据将会为NULL。

(4)、可以将一个表查询出来的数据插入到原表中：

hive> insert into table cite     
　　> select * from cite;

结果就是相当于复制了一份cite表格中的数据。

(5)、和insert overwrite的区别：

hive> insert overwrite table cite                       
　　> select * from tt;

上面的语句将会用tt表格查询到的数据覆盖cite表格已经存在的数据。

Hive 查询语句用法

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference
[WHERE where_condition] 
[GROUP BY col_list [HAVING condition]] 
[CLUSTER BY col_list 
  | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] 
] 
[LIMIT [offset,] rows]

常用关键词解释：

SELECT select_expr ：需要提取的信息（字段、表达式、函数）如类型、人数等；
FROM table_name：从数据表table_name中查询数据（如从订单表里查看人数）；
JOIN table_name2：通过关联查询table_name2中的数据，一般通过on来设置关联条件（否则会导致关联表产生的叉积，即表1中每条记录匹配表2中的每条记录；
WHERE where_condition：设置过滤的条件对表记录(或join产生的记录)进行过滤；
GROUP BY col_list：按字段的值分组进行统计，一般在select中配合聚合函数(如max/sum/count等)进行使用，也可以用来去重；
ORDER BY col_list：对上面语法产生的计算结果，通过col_list进行ASC/DESC（递增/递减）排序；
LIMIT rows：只限制前rows行的数据进行输出；（从文档发现2.0.0版本已支持分页输出）

数据类型

Hive支持的数据类型分为两类，即基本数据类型和复杂数据类型。

基本数据类型包括数值型、布尔型和字符串类型，

复杂数据类型包括数据组（ARRAY）、映射（MAP）、结构体（STRUCT）和共同体（UNION）。

基本数据类型

基础数据类型包括：

TINYINT：
SMALLINT
INT
BIGINT
BOOLEAN
FLOAT
DOUBLE
STRING
BINARY
TIMESTAMP
DECIMAL
CHAR
VARCHAR
DATE

复杂数据类型

复杂类型包括ARRAY,MAP,STRUCT,UNION，这些复杂类型是由基础类型组成的。

ARRAY：ARRAY类型是由一系列相同数据类型的元素组成，这些元素可以通过下标来访问。比如有一个ARRAY类型的变量fruits，它是由['apple','orange','mango']组成，那么我们可以通过fruits[1]来访问元素orange，因为ARRAY类型的下标是从0开始的；
MAP：MAP包含key->value键值对，可以通过key来访问元素。比如”userlist”是一个map类型，其中username是key，password是value；那么我们可以通过userlist['username']来得到这个用户对应的password；
STRUCT：STRUCT可以包含不同数据类型的元素。这些元素可以通过”点语法”的方式来得到所需要的元素，比如user是一个STRUCT类型，那么可以通过user.address得到这个用户的地址。
UNION: UNIONTYPE，他是从Hive 0.7.0开始支持的。

类型转换

同Java语言一样，Hive也包括隐式转换（implicit conversions）和显式转换（explicitly conversions）。

Hive在需要的时候将会对numeric类型的数据进行隐式转换。比如我们对两个不同数据类型的数字进行比较，假如一个数据类型是INT型，另一个是SMALLINT类型，那么SMALLINT类型的数据将会被隐式转换地转换为INT类型，这个和Java中的一样；但是我们不能隐式地将一个INT类型的数据转换成SMALLINT或TINYINT类型的数据，这将会返回错误，除非你使用了CAST操作。

任何整数类型都可以隐式地转换成一个范围更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT和STRING都可以隐式地转换成DOUBLE；是的你没看出，STRING也可以隐式地转换成DOUBLE！但是你要记住，BOOLEAN类型不能转换为其他任何数据类型！

下标列出了Hive内置的数据类型之间是否可以进行隐式的转换操作：

	bl	tinyint	si	int	bigint	float	double	dm	string	vc	ts	date	ba
boolean	true	false	false	false	false	false	false	false	false	false	false	false	false
tinyint	false	true	true	true	true	true	true	true	true	true	false	false	false
smallint	false	false	true	true	true	true	true	true	true	true	false	false	false
int	false	false	false	true	true	true	true	true	true	true	false	false	false
bigint	false	false	false	false	true	true	true	true	true	true	false	false	false
float	false	false	false	false	false	true	true	true	true	true	false	false	false
double	false	false	false	false	false	false	true	true	true	true	false	false	false
decimal	false	false	false	false	false	false	false	true	true	true	false	false	false
string	false	false	false	false	false	false	true	true	true	true	false	false	false
varchar	false	false	false	false	false	false	true	true	true	true	false	false	false
ts	false	false	false	false	false	false	false	false	true	true	true	false	false
date	false	false	false	false	false	false	false	false	true	true	false	true	false
binary	false	false	false	false	false	false	false	false	false	false	false	false	true

注：由于表格比较大，这里对一些比较长的字符串进行缩写，ts是timestamp的缩写,bl是boolean的缩写,sl是smallint的缩写,dm是decimal的缩写,vc是varchar的缩写,ba是binary的缩写。

我们可以用CAST来显式的将一个类型的数据转换成另一个数据类型。如何使用？CAST的语法为cast(value AS TYPE)。举个例子：假如我们一个员工表employees，其中有name、salary等字段；salary是字符串类型的。有如下的查询：

SELECT name, salary FROM employees
WHERE cast(salary AS FLOAT) < 100000.0;

这样salary将会显示的转换成float。如果salary是不能转换成float，这时候cast将会返回NULL！

对cast有一下几点需要说明的：

如果将浮点型的数据转换成int类型的，内部操作是通过round()或者floor()函数来实现的，而不是通过cast实现！
对于BINARY类型的数据，只能将BINARY类型的数据转换成STRING类型。如果你确信BINARY类型数据是一个数字类型(a number)，这时候你可以利用嵌套的cast操作，比如a是一个BINARY，且它是一个数字类型，那么你可以用下面的查询：

SELECT (cast(cast(a as string) as double)) from src;

我们也可以将一个String类型的数据转换成BINARY类型。

对于Date类型的数据，只能在Date、Timestamp以及String之间进行转换。下表将进行详细的说明：

有效的转换	结果
cast(date as date)	返回date类型
cast(timestamp as date)	timestamp中的年/月/日的值是依赖与当地的时区，结果返回date类型
cast(string as date)	如果string是YYYY-MM-DD格式的，则相应的年/月/日的date类型的数据将会返回；但如果string不是YYYY-MM-DD格式的，结果则会返回NULL。
cast(date as timestamp)	基于当地的时区，生成一个对应date的年/月/日的时间戳值
cast(date as string)	date所代表的年/月/日时间将会转换成YYYY-MM-DD的字符串。

Hive常用函数

Hive SQL官方文档说明很详细，通过这个文档查看需要用到的函数的声明：Apache Software Foundation

关系运算

/**
 * 等值比较: =
 * 不等值比较: <>
 * 小于比较: <
 * 小于等于比较: <=
 * 大于等于比较: >=
 * 空值判断: IS NULL
 * 非空判断: IS NOT NULL
 * LIKE比较: A LIKE B
 * JAVA的LIKE操作: A RLIKE B
 * REGEXP操作: A REGEXP B
 */

/**
 * LIKE比较: A LIKE B
 * 如果字符串A或者字符串B为NULL，则返回NULL；
 * 如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。
 * B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。
 */

select 1 from t_test where 'football' like 'foot%';
select 1 from t_test where 'football' like 'foot____';
-- 注意：否定比较时候用NOT A LIKE B
select 1 from t_test where NOT 'football' like 'fff%';

/**
 * JAVA的LIKE操作: A RLIKE B
 * 如果字符串A或者字符串B为NULL，则返回NULL；
 * 如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。
 */
select 1 from t_test where 'footbar' rlike '^f.*r$';
-- 注意：判断一个字符串是否全为数字：
select 1 from t_test where '123456' rlike '^\\d+$';
select 1 from t_test where '123456aa' rlike '^\\d+$';

/**
 * REGEXP操作: A REGEXP B
 * 功能与RLIKE相同
 */
select 1 from t_test where 'footbar' REGEXP '^f.*r$';

数学运算

/**
 * 加法操作: +
 * 减法操作: -
 * 乘法操作: *
 * 除法操作: /
 * 取余操作: %
 * 位与操作: &
 * 位或操作: |
 * 位异或操作: ^
 * 位取反操作: ~
 */


/**
 * 加法操作: +
 * 操作类型：所有数值类型
 * 返回A与B相加的结果。结果的数值类型等于A的类型和B的类型的最小父类型。
 * 比如，int + int 一般结果为int类型，而 int + double 一般结果为double类型
 */
select 1+9 from t_test;
select 1+1.2 from t_test;

/**
 * 减法操作: -
 * 操作类型：所有数值类型
 * 返回A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型。
 * 比如，int – int 一般结果为int类型，而 int – double 一般结果为double类型
 */
select 10-5 from t_test;


/**
 * 乘法操作: *
 * 操作类型：所有数值类型
 * 返回A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型。
 * 注意，如果A乘以B的结果超过默认结果类型的数值范围，则需要通过cast将结果转换成范围更大的数值类型
 */
 select 40*5 from t_test;

/**
 * 除法操作: /
 * 操作类型：所有数值类型
 * 返回A除以B的结果。结果的数值类型为double
 * hive中最高精度的数据类型是double,只精确到小数点后16位，在做除法运算的时候要特别注意
 */
select 40/5 from t_test;
-- 输出 4 
select ceil(28.0/6.999999999999999999999) from t_test limit 1;
-- 输出 5
select ceil(28.0/6.99999999999999) from t_test limit 1;

/**
 * 取余操作: %
 * 操作类型：所有数值类型
 * 返回A除以B的余数。结果的数值类型等于A的类型和B的类型的最小父类型
 */
-- 输出 1
select 41%5 from t_test;
-- 输出 0.4
select 8.4%4 from t_test;
-- 注意：精度在hive中是个很大的问题，类似这样的操作最好通过round指定精度
select round(8.4%4 , 2) from t_test;

/**
 * 位与操作: &
 * 操作类型：所有数值类型‘
 * 返回A和B按位进行与操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型
 * 位与：两位同时为1，结果才为1，否则为0
 */
-- 输出 0
select 4 & 8 from t_test;
-- 输出 4
select 6 & 4 from t_test;

/**
 * 位或操作: |
 * 操作类型：所有数值类型
 * 返回A和B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型。
 * 位或：参加运算的两个对象只要有一个为1，其值为1。
 */
-- 输出 12
select 4 | 8 from t_test;
-- 输出 14
select 6 | 8 from t_test;

/**
 * 位异或操作: ^
 * 操作类型：所有数值类型
 * 返回A和B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型
 * 位异或：参加运算的两个对象，如果两个相应位为"异"（值不同），则该位结果为1，否则为0。
 */
-- 输出 12
select 4 ^ 8 from t_test;
-- 输出 2
select 6 ^ 4 from t_test;

/**
 * 位取反操作: ~
 * 操作类型：所有数值类型
 * 返回A按位取反操作的结果。结果的数值类型等于A的类型
 * 位取反：对一个二进制数按位取反，即将0变1，1变0。
 */
-- 输出 -7
select ~6 from t_test;
-- 输出 -5
select ~4 from t_test;

逻辑运算

/**
 * 逻辑与操作: AND
 * 逻辑或操作: OR
 * 逻辑非操作: NOT
 */

/**
 * 逻辑与操作: AND
 * 如果A和B均为TRUE，则为TRUE；否则为FALSE。如果A为NULL或B为NULL，则为NULL
 */
select 1 from t_test where 1=1 and 2=2;

/**
 * 逻辑或操作: OR
 * 如果A为TRUE，或者B为TRUE，或者A和B均为TRUE，则为TRUE；否则为FALSE
 */
select 1 from t_test where 1=2 or 2=2;

/**
 * 逻辑非操作: NOT
 * 如果A为FALSE，或者A为NULL，则为TRUE；否则为FALSE
 */
select 1 from t_test where not 1=2;

数值计算

/**
 * 取整函数: round(double a)
 * 指定精度取整函数: round(double a, int d)
 * 向下取整函数: floor(double a)
 * 向上取整函数: ceil(double a)
 * 向上取整函数: ceiling(double a)
 * 取随机数函数： rand(),rand(int seed)
 * 自然指数函数: exp(double a)
 * 以10为底对数函数: log10(double a)
 * 以2为底对数函数: log2(double a)
 * 对数函数: log(double base, double a)
 * 幂运算函数: pow(double a, double p)
 * 幂运算函数: power(double a, double p)
 * 开平方函数: sqrt(double a)
 * 二进制函数: bin(BIGINT a)
 * 绝对值函数: abs(double a), abs(int a)
 * negative函数: negative(int a), negative(double a)
 */

/**
 * 取整函数: round(double a)
 * 返回double类型的整数值部分 （遵循四舍五入）
 * 返回值：BIGINT
 */
-- 输出 3
select round(3.1415926) from t_test;
-- 输出 4
select round(3.5) from t_test;

/**
 * 指定精度取整函数: round(double a, int d)
 * 返回指定精度d的double类型
 * 返回值：DOUBLE
 */
-- 输出 3.1416
select round(3.1415926,4) from t_test;

/**
 * 向下取整函数: floor(double a)
 * 返回等于或者小于该double变量的最大的整数
 * 返回值：BIGINT
 */
-- 输出 3
select floor(3.1415926) from t_test;
-- 输出 25
select floor(25) from t_test;

/**
 * 向上取整函数: ceil(double a)
 * 返回等于或者大于该double变量的最小的整数
 * 返回值：BIGINT
 */
-- 输出 4
select ceil(3.1415926) from t_test;
-- 输出 46
select ceil(46) from t_test;

/**
 * 向上取整函数: ceiling(double a)
 * 与ceil功能相同
 */
-- 输出 4
select ceiling(3.1415926) from t_test;
-- 输出 46
select ceiling(46) from t_test;

/**
 * 取随机数函数： rand(),rand(int seed)
 * 返回一个0到1范围内的随机数。如果指定种子seed，则会得到一个稳定的随机数序列
 * 返回值：double
 */
select rand() from t_test;
-- 多次执行，得到的数是一样的
select rand(100) from t_test;

/**
 * 自然指数函数: exp(double a)
 * 返回自然对数e的a次方
 * 返回值：double
 */
select exp(2) from t_test;
-- 输出 2
select ln(7.38905609893065) from t_test;

/**
 * 以10为底对数函数: log10(double a)
 * 返回以10为底的a的对数
 * 返回值：double
 */
-- 输出 2
select log10(100) from t_test;

/**
 * 以2为底对数函数: log2(double a)
 * 返回以2为底的a的对数
 * 返回值：double
 */
-- 输出 3
select log2(8) from t_test;

/**
 * 对数函数: log(double base, double a)
 * 返回以base为底的a的对数
 * 返回值：double
 */
-- 输出 4
select log(4,256) from t_test;


/**
 * 幂运算函数: pow(double a, double p)
 * 返回a的p次幂
 * 返回值：double
 */
select pow(2,4) from t_test;

/**
 * 幂运算函数: power(double a, double p)
 * 返回a的p次幂,与pow功能相同
 */
select power(2,4) from t_test;

/**
 * 开平方函数: sqrt(double a)
 * 返回a的平方根
 * 返回值：double
 */
select sqrt(16) from t_test;

/**
 * 二进制函数: bin(BIGINT a)
 * 返回a的二进制代码表示
 * 返回值：string
 */
-- 输出 111
select bin(7) from t_test;

/**
 * 十六进制函数: hex(BIGINT a)
 * 如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示
 * 返回值：string
 */ 
-- 输出  11
select hex(17) from t_test;
-- 输出  616263
select hex('abc') from t_test;

/**
 * 反转十六进制函数: unhex(string a)
 * 返回该十六进制字符串所代码的字符串
 * 返回值：string
 */
-- 输出：abc
select unhex('616263') from t_test;
-- 输出：17
select unhex('11') from t_test;
-- 输出：abc
select unhex(616263) from t_test;

/**
 * 进制转换函数: conv(BIGINT num, int from_base, int to_base)
 * 将数值num从from_base进制转化到to_base进制
 * 返回值：string
 */
-- 输出 11
select conv(17,10,16) from t_test;
-- 输出 10001
select conv(17,10,2) from t_test;

/**
 * 绝对值函数: abs(double a), abs(int a)
 */
select abs(-3.9) from t_test;
select abs(10.9) from t_test;

/**
 * 正取余函数: pmod(int a, int b), pmod(double a, double b)
 * 返回正的a除以b的余数
 */
-- 输出 1
select pmod(9,4) from t_test;
-- 输出 3 
select pmod(-9,4) from t_test;

/**
 * positive函数: positive(int a), positive(double a)
 * 返回值：返回a
 */
-- 输出 -10
select positive(-10) from iteblog;
-- 输出 12
select positive(12) from iteblog;

/**
 * negative函数: negative(int a), negative(double a)
 * 返回值：返回-a
 */
-- 输出 5
select negative(-5) from iteblog;
-- 输出 -8
select negative(8) from iteblog;

日期函数

/**
 * UNIX时间戳转日期函数: from_unixtime(bigint unixtime[, string format])
 * 获取当前UNIX时间戳函数: unix_timestamp()
 * 日期转UNIX时间戳函数: unix_timestamp(string date)
 * 指定格式日期转UNIX时间戳函数: unix_timestamp(string date, string pattern)
 * 日期时间转日期函数: to_date(string timestamp)
 * 日期转年函数: year(string date)
 * 日期转月函数: month(string date)
 * 日期转天函数: day(string date)
 * 日期转小时函数: hour (string date)
 * 日期转分钟函数: minute(string date)
 * 日期转秒函数: second(string date)
 * 日期转周函数: weekofyear(string date)
 * 日期比较函数: datediff(string enddate, string startdate)
 * 日期增加函数: date_add(string startdate, int days)
 * 日期减少函数: date_sub(string startdate, int days)
 * 返回当前日期：current_date()
 * 返回当前时间：current_timestamp()
 * 取date当月的最后一天：last_day(date)
 */

/**
 * UNIX时间戳转日期函数: from_unixtime(bigint unixtime[, string format])
 * 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
 * 返回值：string
 */
-- 输出 20111208
select from_unixtime(1323308943,'yyyyMMdd') from t_test;

/**
 * 获取当前UNIX时间戳函数: unix_timestamp()
 * 获得当前时区的UNIX时间戳
 * 返回值：bigint
 */
-- 输出：1664264484
select unix_timestamp() from t_test;

/**
 * 日期转UNIX时间戳函数: unix_timestamp(string date)
 * 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
 * 返回值：bigint
 */
-- 输出 1323262863
select unix_timestamp('2011-12-07 13:01:03') from t_test;

/**
 * 指定格式日期转UNIX时间戳函数: unix_timestamp(string date, string pattern)
 * 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
 */
-- 输出 1323262863
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from t_test;

/**
 * 日期时间转日期函数: to_date(string timestamp)
 * 返回日期时间字段中的日期部分。
 * 返回值：string
 */
-- 输出 2011-12-08
select to_date('2011-12-08 10:03:01') from t_test;

/**
 * 日期转年函数: year(string date)
 * 返回日期中的年。
 */
select year('2011-12-08 10:03:01') from t_test;
select year('2012-12-08') from t_test;

/**
 * 日期转月函数: month(string date)
 * 返回日期中的月份（1~9月份不包含前面的0）。
 */
select month('2011-12-08 10:03:01') from t_test;
select month('2012-01-08') from t_test;

/**
 * 日期转天函数: day(string date)
 * 返回日期中的天。
 */
select day('2011-12-08 10:03:01') from t_test;
select day('2012-01-08') from t_test;

/**
 * 日期转小时函数: hour (string date)
 * 返回日期中的小时。
 */
select hour('2011-12-08 10:03:01') from t_test;

/**
 * 日期转分钟函数: minute(string date)
 * 返回日期中的分钟。
 */
select minute('2011-12-08 10:03:01') from t_test;

/**
 * 日期转秒函数: second(string date)
 * 返回日期中的秒。
 */
select second('2011-12-08 10:03:01') from t_test;

/**
 * 日期转周函数: weekofyear(string date)
 * 返回日期在当前的周数
 */
select weekofyear('2011-12-08 10:03:01') from t_test;

/**
 * 日期比较函数: datediff(string enddate, string startdate)
 * 返回结束日期减去开始日期的天数。
 */
select datediff('2012-12-08','2012-05-09') from t_test;

/**
 * 日期增加函数: date_add(string startdate, int days)
 * 返回开始日期startdate增加days天后的日期。
 */
-- 输出 2012-12-18
select date_add('2012-12-08',10) from t_test;

/**
 * 日期减少函数: date_sub(string startdate, int days)
 * 返回开始日期startdate减少days天后的日期。
 */
select date_sub('2012-12-08',10) from t_test;

/**
 * 返回当前日期：current_date()
 * 格式：yyyy-MM-dd
 */
select current_date() from t_test;

/**
 * 返回当前时间：current_timestamp()
 * 包括当前日期和时、分、秒、毫秒
 */
-- 输出：2022-09-27 15:57:48.132
select current_timestamp() from t_test;

/**
 * last_day(date)
 * 取date当月的最后一天，截取到天，
 */
-- 输出 2022-09-30
select last_day('2022-09-27') from t_test;
-- 输出 2022-08-31
select last_day('2022-08-27 10:00:02') from t_test;

条件函数

/**
 * if函数: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
 * 非空查找函数: COALESCE(T v1, T v2, …)
 * 条件判断函数：CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
 * 条件判断函数：CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
 */

/**
 * if函数: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
 * 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
 */
select if(1=2,100,200) from t_test;
select if(1=1,100,200) from t_test;

/**
 * 非空查找函数: COALESCE(T v1, T v2, …)
 * 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
 */
-- 输出 100
select COALESCE(null,'100','50') from t_test;

/**
 * 条件判断函数：CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
 * 如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
 */
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from t_test;
select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from t_test;

/**
 * 条件判断函数：CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
 * 如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e
 */
select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from t_test;
select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from t_test;

聚合统计函数

/**
 * 个数统计函数：count(*), count(expr), count(DISTINCT expr[, expr_.])
 * 总和统计函数: sum(col), sum(DISTINCT col)
 * 平均值统计函数: avg(col), avg(DISTINCT col)
 * 最小值统计函数: min(col)
 * 最大值统计函数: max(col)
 */

/**
 * 个数统计函数：count(*), count(expr), count(DISTINCT expr[, expr_.])
 * count(*)统计检索出的行的个数，包括NULL值的行；
 * count(expr)返回指定字段的非空值的个数；
 * count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数
 */
select count(*) from t_test;
select count(DISTINCT id) from t_test;

/**
 * 总和统计函数: sum(col), sum(DISTINCT col)
 * sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果
 */
select sum(id) from t_test ;
select sum(distinct id) from t_test;

/**
 * 平均值统计函数: avg(col), avg(DISTINCT col)
 * avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值
 */
select avg(id) from t_test;

select avg(distinct id) from t_test;

/**
 * 最小值统计函数: min(col)
 * 统计结果集中col字段的最小值
 */
select min(id) from t_test;

/**
 * 最大值统计函数: max(col)
 * 统计结果集中col字段的最大值
 */
select max(id) from t_test;

复合类型构建操作

Map类型构建：map

同struct，map的用法基本相似，以下为创建一个map类型字段：

create table test3(field2 map<string,string>) row format delimited fields terminated by ',' collection items terminated by "|" map keys terminated by ":";

map keys terminated by ":"表示键值对之间用":"来分割

同样的，先使用sql方式插入

insert into test3(field2)values(str_to_map("name:zhangsan,age:25")),(str_to_map("name:lisi,age:23"));

再导入文件，文件内容如下：

load data local inpath  '/Users/zhangsheng/hive/note/hive/test.txt' into table test3;

map的访问通过map[key]的形式进行，测试一下：

select * from test3;
select field2["name"] as name,field2["age"] as age from test3;
select * from test3 where field2["age"] > 25;

Struct类型构建：struct

与C语言、golang中的struct类似,可以通过.语法来访问

定义一个包含struct字段的表：

create table test2(field1 struct<name:string,age:int> comment "test field") row format delimited fields terminated by "," collection items terminated by ":";

其中row format delimited fields terminated by ","表示字段之间用","分割

collection items terminated by ":"; 字段中各个元素项之间用":"分割

然后导入数据，可以使用sql语句和文件的方式，首先是sql语句：

insert into test2(field1)values(named_struct("name",'zhangsan',"age",25));
insert into test2(field1)values(named_struct("name","lisi","age",23));

从文件导入，新建一个test.txt，内容如下：

name:wangwu|age:27
name:zhaoliu|age:28

使用load data语句从本地导入

load data local inpath '/Users/zhangsheng/hive/note/hive/test.txt' into table test2;

使用.语法查询数据

-- 查询所有数据
select * from test2;
-- 查询name字段
select field1.name from test2;
-- 查询name为zhangsan的记录
select field1 from test2 where field1.name = "zhangsan";

再来：

create table iteblog as select struct('tom','mary','tim') as t from it_test;
-- struct<col1:string ,col2:string,col3:string>
describe iteblog;
-- {"col1":"tom","col2":"mary","col3":"tim"}
select t from iteblog;

Array类型构建：array

与所有支持数组的编程语言一致，使用array[下标]的方式访问，创建一个array类型字段

create table test4(field4 array<string>);

插入数据：

insert into test4(field4)values(array("zhangsan","lisi","wangwu"));
insert into test4(field4)values(`array`("lily","bob","alice"));
insert into test4(field4)values(`array`("A","B","C"));

查询测试：

select * from test4;
select field4[0] from test4;
select * from test4 where field4[0]="zhangsan";

复杂类型访问操作

array类型访问: A[n]

返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar'

hive> create table iteblog as select array("tom","mary","tim") as t from iteblog;
hive> select t[0],t[1],t[2] from iteblog;
tom     mary    tim

map类型访问: M[key]

返回map类型M中，key值为指定值的value值。比如，M是值为{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar'

hive> Create table iteblog as select map('100','tom','200','mary') as t from iteblog;
hive> select t['200'],t['100'] from iteblog;
mary    tom

struct类型访问: S.x

返回结构体S中的x字段。比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段。

hive> create table iteblog as select struct('tom','mary','tim') as t from iteblog;
hive> describe iteblog;
struct<col1:string ,col2:string,col3:string>
hive> select t.col1,t.col3 from iteblog;
tom     tim

复杂类型长度统计函数

Map类型长度函数: size(Map<k .V>)

返回map类型的长度

select size(map('100','tom','101','mary')) from iteblog;

array类型长度函数: size(Array<T>)

返回array类型的长度

select size(array('100','101','102','103')) from iteblog;

cast(expr as <type>)

返回转换后的数据类型

select cast(1 as bigint) from iteblog;

Hive常用字符串函数

Hive内部提供了很多操作字符串的相关函数。

/**
 * 返回字符串的长度函数：length(string A)
 * 输出：7
 */
select length('iteblog') from t_test;

/**
 * 字符串的反转函数：reverse(string A)
 * 输出：golbeti
 */
select reverse('iteblog') from t_test;

/**
 * 字符串连接函数：concat(string A, string B…)
 * 输出：www.iteblog.com
 */
select concat('www','.iteblog','.com') from t_test;

/**
 * 带分隔符字符串连接函数：concat_ws(string SEP, string A, string B…)
 * 第一个参数表示各个字符串间的分隔符
 * 输出：www.iteblog.com
 */
select concat_ws('.','www','iteblog','com') from t_test;

/**
 * 字符串截取函数：
 * 	substr(string A, int start),substring(string A, int start)
 * 	substr(string A, int start, int len),substring(string A, intstart, int len)
 * 正序从1开始
 * 倒序从-1开始
 */
-- 输出：eblog
select substr('iteblog',3) from t_test;
-- 输出：g
select substr('iteblog',-1) from t_test; 
-- 最后一个参数为长度, 输出：cd
select substr('abcde',3,2) from t_test;
-- 输出：cd
select substring('abcde',3,2) from t_test;
-- 输出：de
select substring('abcde',-2,2) from t_test;

/**
 * 字符串转大写函数：upper(string A) ucase(string A)
 */
-- 输出：ABSED
select upper('abSEd') from t_test;
-- 输出：ABSED
select ucase('abSEd') from t_test;

/**
 * 字符串转小写函数：lower(string A) lcase(string A)
 */
-- 输出：absed
select lower('abSEd') from t_test;
-- 输出：absed
select lcase('abSEd') from t_test;

/**
 * 去除字符串两边的空格：trim(string A)
 */
-- 输出：abc
select trim(' abc ') from t_test;

/**
 * 左边去空格函数：ltrim(string A)
 */
-- 输出：abc
select ltrim(' abc ') from t_test;

/**
 * 右边去空格函数：rtrim(string A)
 */
-- 输出：abc
select rtrim(' abc ') from t_test;

/**
 * 正则表达式替换函数：regexp_replace(string A, string B, string C)
 * 将字符串A中的符合java正则表达式B的部分替换为C。
 * 注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。
 */
-- 输出fb
select regexp_replace('foobar', 'oo|ar', '') from t_test;

/**
 * 正则表达式解析函数：regexp_extract(string subject, string pattern, int index)
 * 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
 * 注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。
 */
-- 输出：the
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) t_test;
-- 输出：bar
select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) t_test;
-- 输出：foothebar
select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) t_test;

/**
 * URL解析函数：parse_url(string urlString, string partToExtract [, stringkeyToExtract])
 * 返回URL中指定的部分。
 * partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
 */
-- 输出：iteblog.com
select parse_url('http://iteblog.com?weixin=iteblog_hadoop', 'HOST') from t_test;
-- 输出：iteblog_hadoop
select parse_url('http://iteblog.com?weixin=iteblog_hadoop', 'QUERY','weixin') from t_test;

/**
 * json解析函数：get_json_object(string json_string, string path)
 * 解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
 */
-- 输出：amy
select get_json_object('{"store":
{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}
','$.owner') from t_test;

/**
 * 空格字符串函数：space(int n)
 * 返回长度为n的字符串
 */
select space(10) from t_test;
select length(space(10)) from t_test;

/**
 * 重复字符串函数：repeat(string str, int n)
 * 返回重复n次后的str字符串
 */
-- 输出：abcabcabcabcabc
select repeat('abc',5) from t_test;

/**
 * 首字符ascii函数：ascii(string str)
 * 返回字符串str第一个字符的ascii码
 */
-- 返回：97
select ascii('abcde') from t_test;

/**
 * 左补足函数： lpad(string str, int len, string pad)
 * 将str进行用pad进行左补足到len位
 * 注意：与GP，ORACLE不同，pad 不能默认
 */
-- 输出：tdtdtdtabc
select lpad('abc',10,'td') from t_test;

/**
 * 右补足函数：rpad(string str, int len, string pad)
 * 将str进行用pad进行右补足到len位
 */
-- 输出：abctdtdtdt
select rpad('abc',10,'td') from t_test;

/**
 * 分割字符串函数: split(string str, stringpat)
 * 按照pat字符串分割str，会返回分割后的字符串数组
 */
-- 输出：["ab","cd","ef"]
select split('abtcdtef','t') from t_test;

/**
 * 集合查找函数：find_in_set(string str, string strList)
 * 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
 */
-- 输出：2
select find_in_set('ab','ef,ab,de') from t_test;
-- 输出：0
select find_in_set('at','ef,ab,de') from t_test;

/**
 * 返回子字符串在字符串中出现的位置：instr(string, substring)
 * 找不到返回0
 */
-- 输出 4
select instr('hello', 'lo') from t_test;
-- 输出 0
select instr('hello', 'eo') from t_test;

窗口函数

注意：MySQL从8.0版本才开始支持窗口函数

窗口函数结构

分析函数（max()/sum()/row_number()）+ 窗口子句（over函数）
例：row_number() over(partition by uid order by create_time asc)

窗口函数应用场景

Top N
分区排序 —— row_number()
动态group by
累计计算
层次查询

常用的窗口函数

RANK() ：在分组中排名，相同排名时会留下空位；
DENSE_RANK() ：在分组中排名，相同排名时不会留下空位；
FIRST_VALUE() ：分组内排序取第一个值；
LAST_VALUE() ：分组内排序取最后一个值；
NTH_VALUE() ：返回有序行的第n小的值（oracle函数）；
NTILE(n) ：将分组数据按顺序切分成n份，返回当前所在切片，只能用作分析函数；
ROW_NUMGBER() ：在分组中从1开始按序记录序列；
CUME_DIST() ：小于等于当前值的行数 / 分组总行数（百分比）；
PERCENT_RANK() ：（分组内的RANK值-1） / （分组内总数-1）；
LAG(col, n, DEFAULT) ：在统计窗口内从下往上取第n行的值；
LEAD(col, n, DEFAULT)：在统计窗口内从上往下取第n行的值；

窗口函数示例

-- 创建表
create table score(name string, subject string, score int)
row format delimited
fields terminated by '\t';

-- 插入数据
insert OVERWRITE table score
values('悟空', '语文', 87),
('悟空', '数学', 95),
('悟空', '英语', 68),
('八戒', '语文', 94),
('八戒', '数学', 56),
('八戒', '英语', 84),
('张飞', '语文', 64),
('张飞', '数学', 86),
('张飞', '英语', 84),
('小乔', '语文', 65),
('小乔', '数学', 85),
('小乔', '英语', 78)

例子1：rank()

在分组中排名，相同排名时会留下空位

select *, rank() over(partition by subject order by score desc) rank from score;

例子2：DENSE_RANK()

在分组中排名，相同排名时不会留下空位；

select *,dense_rank() over(partition by subject order by score desc) dense_rank from score;

例子3：ROW_NUMGBER()

在分组中从1开始按序记录序列；

select *,row_number() over(partition by subject order by score desc) row_number from score;

例子4：FIRST_VALUE()

分组内排序取第一个值；

SELECT *, FIRST_VALUE(name) OVER (PARTITION BY subject ORDER BY score desc) as first_value from score;

例子5：LAST_VALUE()

分组内排序取最后一个值；

SELECT *, LAST_VALUE(name) OVER (PARTITION BY subject ORDER BY score desc) as last_value from score;

例子6：NTH_VALUE()

NTH_VALUE 返回 analytic_clause 定义的窗口中第 n 行的 measure_expr 值。返回的值具有 measure_expr 的数据类型。

格式：

NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)

参数说明：

select *,
nth_value(name,3) over(PARTITION BY subject ORDER BY score desc) as A from score;

例子7：NTILE(n)

将分组数据按顺序切分成n份，返回当前所在切片；

select *,
ntile(1) over (order by score desc)  as A,
ntile(2) over (order by score desc)  as B,
ntile(3) over (order by score desc)  as C,
ntile(4) over (order by score desc)  as D,
ntile(5) over (order by score desc)  as E,
ntile(3) over (PARTITION BY subject ORDER BY score desc)  as AA
 from score;

例子8：CUME_DIST()

小于等于当前值的行数 / 分组总行数（百分比）；

SELECT *, cume_dist() OVER (PARTITION BY subject ORDER BY score desc) as nth_value from score;

例子9：PERCENT_RANK()

（分组内的RANK值-1） / （分组内总数-1）；

SELECT *, PERCENT_RANK() OVER (PARTITION BY subject ORDER BY score desc) as percent_rank from score;

例子10：LAG(col, n, DEFAULT)

在统计窗口内从下往上取第n行的值

第一个参数为列名，第二个参数为偏移量，往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

-- 序号默认是1，可以设置为0
SELECT *, lag(name, 1, 'none')  OVER (PARTITION BY subject ORDER BY score desc) as lag_value from score;

例子11：LEAD(col, n, DEFAULT)

在统计窗口内从上往下取第n行的值

-- 序号默认是1，可以设置为0
SELECT *, LEAD(name, 1, 'none')  OVER (PARTITION BY subject ORDER BY score desc) as lead_value from score;

posted @ 2022-09-20 11:13 残城碎梦阅读(1027) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

残城碎梦