一：简介

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

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

Hive SQL 与 SQL的区别：

1. HQL不支持行级别的增(insert into table values)、改、删，所有数据在加载时就已经确定，不可更改。
2. 不支持事务
3. 支持分区存储

二：Hive数据类型

• 基础数据类型：boolean、tinyint、smallint、int、bigint、float、double、deicimal、String(字符串，不需要指定字符串长度)、varchar(需要指定字符串长度)、char、binary(字节数组)、timestamp(时间戳，纳秒精度)、date(日期YYYY-MM-DD)
• 复合数据类型：array：可通过下标获取指定索引对应的值map：可通过key来获取valuestruct：可以通过点.语法获取union

三：常用Hive SQL

<code>-- 创建数据库
create database ;

-- 切换数据库
use ;

-- 复制一个表的表结构(不包含数据)
create table  like 
-- 创建表包含数据
create table  as select * from 

-- 列出所有数据库
show databases;

-- 显示所有的表
show tables;

-- 显示所有分区
show partitions;
show partitions ;

-- 显示所有函数
show functions;

-- 更改表明
ALTER TABLE  RENAME TO ;

-- 增加列
alter table  ADD COLUMNS (  COMMENT [列注释]);

-- 查看表结构
describe extended ;

-- 清空表数据
truncate table ;

-- 删除表
drop table ;

-- 删除视图
drop view ;
/<code> 

四：示例

struct和array都属于collection, map中也使用collection分隔符分隔每个entry，常用的字段分隔符有"," 、"\\t"。

<code>>hive create database test;
>hive use test;

>hive create table if not exists employee(
    id bigint,
    username string,
    age tinyint,
    weight decimal(10, 2),
    create_time timestamp,
    is_test boolean,
    tags array<string>,
    ext map<string> comment '扩展字段',
    address struct<string>
)
comment '员工表'
row format delimited 
fields terminated by ',' 
collection items terminated by '#' 
map keys terminated by ':' 
lines terminated by '\\n';
/<string>/<string>/<string>/<code>

/data/employee.txt

<code>1,zhangsan,28,60.66,2020-02-01 10:00:00,true,eat#drink,k1:v1#k2:20,s1#c1#s1#1
2,lisi,29,60.66,2020-02-01 11:00:00,false,play#drink,k3:v3#k4:30,s2#c2#s1#2
/<code>

<code># 加载数据到hive
hive > load data local inpath '/data/employee.txt' into table employee;

# 查询数据
hive > seleict * from employee;
hive > select username, tags[0], address.city, ext['k1'] from employee;

# stack(记录总行数，记录数据)
hive > insert into table employee
select stack(
    1,
    3, "wangwu", 30, 70.00, current_timestamp, true, array('eat','play','happy'), map("k5", 5, "key6", "value6"), named_struct("street", "street3", "city", "city3", "state", "state3", "zip", 3) 

) from employee
limit 1;
/<code>

执行insert into select的时候可能会报错，在hive-site.xml中配置以下两个参数。有的时候还会报错不知道为什么，这里先load data两条数据，然后insert into select 一条数据就插入成功了。hive3.1.2中自己使用insert into values这种语法没有插入成功。

<code><property>
     <name>hive.enforce.bucketing/<name>
     <value>true/<value>
/<property>
<property>
     <name>hive.exec.dynamic.partition.mode/<name>
     <value>nonstrict/<value>
/<property>
/<code>

五：Hive中文注释乱码

创建数据库时我们一般使用utf8编码，安装数据库一般也选择utf8编码，数据库连接url通常也会使用characterEncoding=UTF-8参数，Hive中文注释乱码大部分原因都是因为hive在初始化数据库时使用的SQL脚本中设定的编码是latin1编码。因此只需要修改数据库表的字段的编码即可。

初始化数据库时是通过命令./schematool -dbType mysql -initSchema来初始化的，此命令会执行 /libexec/scripts/metastore/upgrade/mysql/hive-schema-3.0.0.mysql.sql这个SQL脚本，我们只需要修改这个脚本对应的表对应的字段的编码即可(注意：不可将所有字段及表的编码都改成utf8, 只需要修该某几个字段对应的编码即可，如果修改表的编码可能会报错)。

方式一：修改sql脚本，删除数据库，重新初始化

修改hive-schema-3.0.0.mysql.sql脚本中的COLUMNSV2. COMMENT、TABLEPARAMS. PARAMVALUE、PARTITIONKEYS. PKEY_COMMENT字段对应的编码为utf8, 其它字段不需要修改，表的编码也不需要修改，只需要修改这3个字段的编码即可。SQL脚本修改后把数据库删掉再重新初始化(./schematool -dbType mysql -initSchema)。如果数据库里的数据不重要，可以删掉数据库重新初始化。

• 列的注释是存放到COLUMNS_V2表COMMENT字段上，需要将COMMENT字段字符集修改为utf-8
• 表的注释是存放到TABLEPARAMS表PARAMVALUE字段上，需要将PARAM_VALUE字段字符集修改为utf-8
• 分区注释是存放到PARTITIONKEYS表PKEYCOMMENT字段上，需要将PKEY_COMMENT字段字符集修改为utf-8

修改COLUMNS_V2表COMMENT字段的编码为utf8

<code>CREATE TABLE IF NOT EXISTS `COLUMNS_V2` (
  `CD_ID` bigint(20) NOT NULL,

  `COMMENT` varchar(256) CHARACTER SET utf8 DEFAULT NULL,

  `COLUMN_NAME` varchar(767) CHARACTER SET SET latin1 COLLATE latin1_bin NOT NULL,
  `TYPE_NAME` MEDIUMTEXT DEFAULT NULL,
  `INTEGER_IDX` int(11) NOT NULL,
  PRIMARY KEY (`CD_ID`,`COLUMN_NAME`),
  KEY `COLUMNS_V2_N49` (`CD_ID`),
  CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/<code>

修改TABLEPARAMS表PARAMVALUE字段的编码为utf8

<code>CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` (
  `TBL_ID` bigint(20) NOT NULL,
  `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,

  `PARAM_VALUE` MEDIUMTEXT CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`TBL_ID`,`PARAM_KEY`),
  KEY `TABLE_PARAMS_N49` (`TBL_ID`),
  CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/<code>

修改PARTITIONKEYS表PKEYCOMMENT字段的编码为utf8

<code>CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` (
  `TBL_ID` bigint(20) NOT NULL,

  `PKEY_COMMENT` varchar(4000) CHARACTER SET utf8 DEFAULT NULL,

  `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 

  `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `INTEGER_IDX` int(11) NOT NULL,
  PRIMARY KEY (`TBL_ID`,`PKEY_NAME`),
  KEY `PARTITION_KEYS_N49` (`TBL_ID`),
  CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/<code>

方式二：直接修改现有数据库表的字段的编码

如果数据库中的数据重要不能删除，则直接修改字段的编码即可。

<code>mysql> alter table COLUMNS_V2 modify column `COMMENT` varchar(256) character set utf8;
mysql> alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
mysql> alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
/<code>

<code>hive> create table tbl_test (id bigint, username string comment "用户名")
comment "测试表"
partitioned by (city string comment "城市")
row format delimited
fields terminated by ","
lines terminated by "\\n"
stored as textfile;

-- 查看表结构
hive> describe extended tbl_test;
/<code>

閱讀更多 Java實用技術 的文章

關鍵字: 详解 严格遵守 SQL