HiveQL

数据类型长度例子备注TINYINT1byte有符号整数20SMALINT2byte有符号整数20INT4byte有符号整数20BIGINT8byte有符号整数20BOOLEAN布尔类型，true或falseTRUEFLOAT单精度浮点数3.14159实现的是Java中的floatDOUBLE双精度浮点数3.14159STRING字符序列。可以指定字符集。可以使用单引号或者双引号’now is the time’,”for all good men”实现的是Java中的StringTIMESTAMP(v0.0.0+)整数浮点数或者字符串1327882394（Unix新纪元秒），1327882394.123456789（Unix新纪元秒并跟随有纳秒数）和’2012-02-03 12:34:56.123456789′(JDBC所兼容的java.sql.Timestamp时间格式)可以是整数也就是距离Unix新纪元时间(1970年1月1日，午夜12点)的秒数；也可以是浮点数，即距离Unix新纪元时间的秒数精确到纳秒（小数点后保留9位数）；还可以是字符串，即JDBC所约定的时间字符串格式:YYYY-MM-DD hh:mm:ss:fffffffffBINARY(v0.0.0+)字节数组和很多关系型数据库中的VARBINARY数据类型类似，但和BLOB数据类型并不相同。因为BINARY的列是存储在记录中，而BLOB则不同。BINARY可以在记录中包含任意字节

数据类型描述字面语法示例STRUCT和C语言的struct或者”对象”类似，都可以通过”点”符号访问元素内容struct(‘John’,’Doe’)MAPMAP是一组键值对元组集合，使用数组表示法（例如[‘key’]）可以访问元素。map(‘first’,’JOIN’,’last’,’Doe’)ARRAY数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始Array(‘John’,’Doe’)

对于所谓的逗号分隔值CSV或TSV格式，有一个共同的缺点，用户需要对文本文件中那些不需要作为分隔符处理的逗号或者制表符须格外小心。也因此，Hive默认使用了几个控制字符，这些字符很少出现在字段值中。Hive使用术语field来表示替换默认分隔符的字符。

Hive中默认的记录和字段分隔符

分隔符描述\n对于文本文件来说，每行都是一条记录，因此换行符可以分割记录^A(Ctrl+A)用于分隔字段(列)。在CREATE TABLE 语句中可以使用八进制编码\001表示^B用于分隔ARRARY或者STRUCT中的元素，或用于MAP中键值对之间的分隔。在CREATE TABLE 语句中可以使用八进制编码\002表示^C用于MAP中键和值之间的分隔。在CREATE TABLE语句中可以使用八进制编码\003表示

当用户向传统数据库写入数据的时候，不管是采用装载外部数据的方式，还是采用将一个查询的输出结果写入的方式，或者使用UPDATE语句，等等，数据库对于存储都具有完全的控制力。传统数据库是写时模式(schema on write)，即数据在写入数据库时对模式进行检查。
Hive对底层存储并没有这样的控制。对于Hive要查询的数据，有很多种方式对其进行创建、修改，甚至损坏。因此Hive不会在数据加载时进行验证，而是查询时进行，也就是读时模式(schema on read)

HiveQL是Hive查询语言。和普通使用的所有SQL方言一样，它不完全遵守任一种ANSI SQL标准的修订版。
Hive不支持行级插入操作、更新操作和删除操作。Hive也不支持事务。Hive增加了在Hadoop背景下的可以提供更高性能的扩展，以及一些个性化的扩展，甚至还增加了一些外部程序。

Hive中数据库的概念本质仅仅是表的一个目录或者命名空间，然而，对于具有很多组和用户的大集群来说，这是非常有用的，因为这样可以避免表明名冲突。通常会使用数据库来将生产表组织成逻辑组。

使用如下语句可以创建一个数据库:

hive> CREATE DATABASE IF NOT EXISTS financials;

随时可以通过如下命令方式查看Hive中所包含的数据库:

hive> SHOW DATABASES;

用户可以通过如下命令修改默认的位置：

hive> CREATE DATABASE financials
    > LOCATION 'my/preferred/directory';

用户也可以为这个数据库增加一个描述信息，这样通过 DESCRIBE DATABASE 数据库名 命令就可以看到该信息:

hive> CREATE DATABASE financials
    > COMMENT 'Holds all financial tables';

hive> DESCRIBE DATABASE financials;

此外，用户还可以为数据库增加一些和其相关的键值对属性信息，尽管目前仅有功能就是提供了一种可以通过 DESCRIBE DATABASE EXTENDED 数据库名语句显示出这些信息的方式:

hive> CREATE DATABASE financials
    > WITH DBPROPERTIES('creator'='Mark Moneybags','date'='2012-01-02');

hive> DESCRIBE DATABASE financials;
hive> DESCRIBE DATABASE EXTENDED financials;

在默认情况下，Hive是不允许用户删除一个包含有表的数据库的。用户要么先删除数据库中的表，再删除数据库，要么在删除命令的最后面加上关键字CASCADE：

hive> DROP DATABASE IF EXISTS financials CASCADE;

用户可以对某个数据库的DBPROPERTIES设置键值对属性

hive> ALTER DATABASE financials SET DBPROPERTIES('edited-by'='Joe Dba');

CREATE TABLE语句遵从SQL语法惯例，但是Hive的这个语句中具有显著的功能扩展，使其可以具有更广泛的灵活性。
例:

CREATE TABLE IF NOT EXISTS mydb.employees(
    name    STRING COMMENT 'Employee name',
    salary  FLOAT COMMENT 'Employee salary',
    subordinates    ARRAY<string> COMMENT 'Name of subordinates',
    deductions  MAP<string,float> COMMENT 'Keys are deductions name,values are percentages',
    address STRUCT<street:string,city:string,state:string,zip:int> COMMENT 'Home address')
COMMENT 'Description of the table'
TBLPROPERTIES('creator'='me','created_at'='2012-01-02 10:00:00',...)
LOCATION '/user/hive/warehouse/mydb.db/employees';
</street:string,city:string,state:string,zip:int></string,float></string>

• Hive会自动增加两个表属性:一个是last_modified_by,其保存着最后修改这个表的用户的用户名；另一个是last_modified_time，其保存着最后一次修改的新纪元时间秒

目前所创建的表都是所谓的管理表，也可称为内部表。因为这种表，Hive会控制着数据的生命周期。默认情况下，会将这些表的数据存储在由配置项hive.metastore.warehouse.dir所定义的目录的子目录下

下面语句将创建一个外部表，其可以读取所有位于/data/stocks目录下的以逗号分隔的数据：

CREATE EXTERNAL TABLE IF NOT EXISTS stocks(
    exchange    STRING,
    symbol      STRING,
    ymd         STRING,
    price_open  FLOAT,
    price_high  FLOAT,
    price_close FLOAT,
    volume      INT,
    price_adj_close FLOAT)
ROW FORMAT DELIMITED FILEDS TERMINATED BY ','
LOCATION '/data/stocks';

关键字EXTENAL告诉Hive这个表是外部的，LOCATION子句告诉Hive数据位于哪个路径下

因为表是外部的，所以Hive并非认为其完全拥有这份数据。因此，删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

用户可以通过 DESCRIBE EXTENDED 表名来输出看到表是否是管理表或外部表。在详细信息的末尾
管理表为: ...tableType:MANAGED_TABLE外部表为: ...tableType:EXTERNAL_TABLE

用户可以对一张存在的表进行表结构复制(不会复制数据):

CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3
LIKE mydb.employee
LOCATION '/path/to/data';

数据分区的一般概念存在已久。其可以有多种形式，但是通常使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更近的地方，以及实现其他目的。

Hive中有分区表的概念，分区表可以将数据以一种符合逻辑的方式进行组织，比如分层存储。
例：

CREATE TABLE employees(
    name STRING,
    salary  FLOAT,
    subordinates    ARRAY<string>,
    deductions  MAP<string,float>,
    address     STRUCT<street:string,city:string,state:string,zip:int>
)
PARTITIONED BY (country STRING,state STRING);
</street:string,city:string,state:string,zip:int></string,float></string>

分区表改变了Hive对数据存储的组织方式。如果在mydb数据库中创建这个表，那么对于这个表只会有一个employees目录与之对应:

hdfs://master_server/user/hive/warehouse/mydb.db/employees

Hive会创建好可以反映分区结构的子目录

.../employees/country=CA/state=AB
.../employees/country=CA/state=BC
...

.../employees/country=US/state=AL
.../employees/country=US/state=AK

对数据进行分区，最重要的原因就是为了更快的查询。在WHERE子句中增加谓词按照分区值进行过滤时，这些谓词被称为分区过滤器。

如果表中数据以及分区个数都非常大的话，执行一个包含所有分区的查询会触发一个巨大的MapReduce任务，一个高度建议的安全措施就是将Hive设置为strict(严格)模式，这样如果对分区表进行查询而WHERE子句没有加分区过滤的话，将会禁止提交这个任务。
将Hive设置为严格模式：

hive> set hive.mapred.mode=strict;

将Hive设置为非严格模式：

hive> set hive.mapred.mode=nonstrict;

可以通过 SHOW PARTITIONS命令查看表中存在的所有分区：

hive> SHOW PARTITIONS 表名;

如果表中存在很多分区，而用户只想查看某个特定分区时，可以以如下命令进行查询：

hive> SHOW PARTITIONS 表名 PARTITION(字段名='值');

DESCRIBE EXTENDED 表名也可以显示出分区键

外部表同样可以使用分区。
例:
创建一个外部分区表log_massages并以年月日分区

CREATE EXTERNAL TABLE IF NOT EXISTS log_massages(
    hms     INT,
    severity    STRING,
    server      STRING,
    process_id  INT,
    message     STRING)
PARTITIONED BY (year INT,month INT,day INT)
ROW FORMAT DELIMITED FIELEDS TERMINATED BY '\t';

增加一个2012年1月2日的分区:

ALTER TABLE log_messages ADD PARTITION(year=2012,month=1,day=2)
LOCATION 'hdfs://master_server/data/log_messages/2012/01/02';

和非分区外部表一样，Hive并不控制这些数据，即使表被删除，数据也不会被删除。

DROP TABLE IF EXISTS 表名;

ALTER TABLE 表名 RENAME TO 新表名;

以增加时间属性分区为例:

ALTER TABLE 表名 ADD IF NOT EXISTS
PARTITION (year=2022,month=1,day=1) LOCATION '/logs/2022/01/01'
PARTITION (year=2022,month=1,day=2) LOCATION '/logs/2022/01/02'
PARTITION (year=2022,month=1,day=3) LOCATION '/logs/2022/01/03'

删除分区：

ALTER TABLE 表名 DROP IF EXISTS PARTITION(year=2022,month=1,day=2);

ALTER TABLE 表名
CHANGE COLUMN 字段名 新字段名 数据类型
COMMENT '注释内容'
AFTER 某字段名;

如果置顶可将 AFTER换为 FIRST

ALTER TABLE 表名 ADD COLUMNS(
    字段名1     数据类型    COMMENT '注释1',
    字段名2     数据类型    COMMENT '注释2',
    ...

);

如果新增的字段中有某个或多个字段位置需要调整则使用 ALTER COULME 表名 CHANGE COLUMN逐一将字段调整到正确的位置

用户可以增加附加的表属性或者修改已经存在的属性，但是无法删除属性：

ALTER TABLE 表名 SET TBLPROPERTIES(
    'notes'='The process id is no longer captured;this column is always NULL'
);

下例将一个分区的存储格式改成了SEQUENCE FILE

ALTER TABLE 表名
PARTITION(year=2022,month=1,day=1)
SET FILEFORMAT SEQUENCEFILE;

ALTER TABLE ... ARCHIVE PARTITION语句会将分区内的文件打成一个Hadoop(HAR)压缩包文件。但这样仅仅可以降低文件系统中的文件数以减轻NameNode压力，而不会减少任何存储空间。

既然Hive没有行级别的数据插入、数据更新和删除操作，那么往表中装载数据的途径就是一种数据装载操作。或者通过其他方式仅仅将文件写入到正确的目录下。

LOAD DATA LOCAL INPATH '${env:HOME}/读取路径'
OVERWRITE INTO TABLE 表名
PARTITION(字段名1='值1',字段名2='值2',...);

如果分区目录不存在的话，这个命令会先创建分区目录，然后再将数据拷贝到该目录下。
如果目标表是非分区表，那么省略 PARTITION子句。

通常情况下指定的路径应该是一个目录，而不是单独的文件。Hive会将所有文件都拷贝到这个目录中。使得用户将更方便地组织数据到多文件中，同时，在不修改Hive脚本地前提下修改文件命名规则。

如果使用了LOCAL这个关键字，那么这个路径应该为本地文件系统路径，数据会被拷贝到目标位置。如果省略掉LOCAL关键字，那么这个路径应该是分布式文件系统中的路径。

INSERT语句允许用户通过查询语句向目标表中插入数据。

INSERT OVERWRITE TABLE 表名
PARTITION(分区字段1='值1',分区字段2='值2')
SELECT * FROM 来源表
WHERE 来源表.对应字段1='值1' AND 来源表.对应字段2='值2';

这里如果没有使用 OVERWRITE关键字或者使用 INTO关键字则不会覆盖掉之前的内容，追加记录

如果来源表非常大，Hive提供了另一种INSERT语法:

FROM 来源表
INSERT OVERWRITE TABLE 表名
    PARTITION (分区字段1='值1',分区字段2='值2',...)
    SELECT * WHERE 来源表.对应字段1='值1' AND 来源表.对应字段2='值2'
INSERT OVERWRITE TABLE 表名
    PARTITION (分区字段3='值3',分区字段4='值4',...)
    SELECT * WHERE 来源表.对应字段3='值3' AND 来源表.对应字段4='值4'
INSERT OVERWRITE TABLE 表名
    PARTITION (分区字段5='值5',分区字段6='值6',...)
    SELECT * WHERE 来源表.对应字段5='值5' AND 来源表.对应字段6='值6'

INSERT OVERWRITE TABLE 表名
PARTITION(分区字段1,分区字段2)
SELECT ...,来源表.对应字段1,来源表.对应字段2
FROM 来源表;

当静态与动态插入混用时，静态分区键必须在动态分区键之前

动态分区默认情况没有开启，开启后默认是以”严格”模式执行。

属性名称缺省值描述hive.exec.dynamic.partitionfalse设置成true，表示开启动态分区功能hive.exec.dynamic.partition.modestrict设置成nonstrict，表示允许所有分区都是动态的hive.exec.max.dynamic.partitions.pernode100每个mapper或reducer可以创建的最大动态分区个数。如果某个mapper或reducerr尝试创建大于这个值的分区的话则会抛出一个错误hive.exec.max.dynamic.partitions+1000一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个错误hive.exec.max.created.files100000全局可以创建的最大文件个数。有一个Hadoop计数器会跟踪记录创建了多少个文件，如果超过这个值会抛出一个错误

用户同样可以在一个语句中完成创建表并将查询结果载入到这个表的操作：

CREATE TABLE 表名
AS SELECT 字段名1,字段名2,...

FROM 原表名
WHERE 条件;

这个功能不能应用于外部表

hadoop fs -cp source_path target_path

INSERT OVERWRITE LOCAL DIRECTORY '路径'
SELECT 字段名1,字段名2,字段名3,..

FROM 表名
WHERE 条件;

FROM 表名
INSERT OVERWRITE DIRECTORY '路径1'
    SELECT * WHERE 条件1
INSERT OVERWRITE DIRECTORY '路径2'
    SELECT * WHERE 条件2
    ...;

CREATE TABLE IF NOT EXISTS employees(
    name        STRING,
    salary      FLOAT,
    subordinates    ARRAY<string>,
    deductions      MAP<string,float>,
    address     STRUCT<street:string,city:string,state:string,zip:int>
)
PARTITIONED BY (country STRING,state STRING);
</street:string,city:string,state:string,zip:int></string,float></string>

下面两个查询是等价的：

hive> SELECT    name,   salary  FROM    employees;
hive> SELECT    e.name, e.salary    FROM    employees e;

用户选择的列为集合数据类型时，Hive会使用JSON语法用于输出。

hive> SELECT name,subordinates FROM employees;
其输出结果格式为：
John    Doe     ["Mary Smith","Todd Jones"]
Marry   Smith   ["Bill King"]
Todd    Jones   []
Bill    King    []

MAP类型也是用JSON表达：

hive> SELECT name,deductions FROM employees;
其输出结果格式为：
John    Doe     {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}
Marry   Smith   {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}
Todd    Jones   {"Federal Taxes":0.15,"State Taxes":0.03,"Insurance":0.1}
Bill    King    {"Federal Taxes":0.15,"State Taxes":0.03,"Insurance":0.1}

STRUCT也是用JSON表达：

hive> SELECT name,address FROM employees;
其输出结果格式为：
John    Doe     {"street":"1 Michigan Ave.","city":"Chicago","state":"IL","zip":60600}
Marry   Smith   {"street":"100 Ontario St.","city":"Chicago","state":"IL","zip":60601}
Todd    Jones   {"street":"200 Chicago Ave.","city":"Oak Park","state":"IL","zip":60700}
Bill    King    {"street":"300 Obscure Dr.","city":"Obscuria","state":"IL","zip":60100}

查询数组的第一个元素:

hive> SELECT name,subordinates[0] FROM employees;
其输出结果格式为：
John    Doe     Mary Smith
Marry   Smith   Bill King
Todd    Jones   NULL
Bill    King    NULL

查询MAP中的一个元素:

hive> SELECT name,deductions["State Taxes"] FROM employees;
其输出结果格式为：
John    Doe     0.05
Marry   Smith   0.05
Todd    Jones   0.03
Bill    King    0.03

查询STRUCT中的一个元素：

hive> SELECT name,address.city FROM employees;
其输出结果格式为：
John    Doe     Chicago
Marry   Smith   Chicago
Todd    Jones   Oak Park
Bill    King    Obscuria

例：选择symbol列和所有列名以price作为前缀的列：

hive> SELECT symbol,price.* FROM stocks;

hive> SELECT upper(name),salary,deductions["Federal Taxes"],round(salary*(1-deductions["Federal Taxes"])) FROM employees;

运算符类型描述A+B数值A与B相加A-B数值A减去BA*B数值A与B相乘A/B数值A除以B，如果能整除那么返回商（商是一个整数，表示在不考虑有余数的情况下，除数可以被除数的次数）A%B数值A除以B的余数A&B数值A和B按位取与A|B数值A和B按位取或A^B数值A和B按位取异或~A数值A按位取反

返回值类型样式描述BIGINTround(DOUBLE d)返回DOUBLE型d的BIGINT类型的近似值DOUBLEround(DOUBLE d,INT n)返回DOUBLE型d的保留n位小数的DOUBLE型的近似值BIGINTfloor(DOUBLE d)d是DOUBLE类型的，返回

Original: https://blog.csdn.net/weixin_41489136/article/details/127556802
Author: 盛者无名
Title: HiveQL