Hive 基本操作

2016-10-20 大数据 hive 0 评论字数统计: 4.9k(字) 阅读时长: 20(分)

Hive 交互命令

刚刚安装好hive后，先来看一看基本的交互

(base) [cpeixin@CpeixindeMBP:] ~ $ hive -help
Hive Session ID = 626af458-fe89-406e-9a63-5967e2962486
usage: hive
 -d,--define <key=value>          Variable substitution to apply to Hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable substitution to apply to Hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

在不进入客户端的情况下，我们可以上面这些交互命令来操作hive

-e 在linux命令行窗口执行sql语句
[cpeixin@CpeixindeMBP]$ hive -e “hive -e “select * from test.car””
注：记得加库名
-f 执行脚本中sql语句，并将结果写到指定文件
[cpeixin@CpeixindeMBP]$ hive -f /opt/sql/test.sql > /opt/data/test_result.txt
查看hive的执行历史
[cpeixin@CpeixindeMBP:] ~ $ cat .hivehistory

在进入hive客户端中，我们还可以使用命令查看本地和HDFS文件系统的文件，这个还是蛮实用的

屏幕快照 2020-03-24 下午11.01.03.png

Hive 数据类型

基本的交互了解后呢，我们来看一下Hive中的数据类型

基本数据类型：

数据类型	字节	范围	示例
TINYINT	1byte	-128 ~ 127	100Y
SMALLINT	2byte	-32,768 ~ 32,767	100S
INT/INTEGER	4byte	-2,147,483,648 ~ 2,147,483,647	100
BIGINT	8byte	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	100L
FLOAT	4byte	单精度浮点数	0.2
DOUBLE	8byte	双精度浮点数	0.2
DECIMAL		高精度浮点数	DECIMAL(9,8)
BOOLEAN		TRUE/FALSE	true
BINARY		二进制类型

TIMESTAMP		时间戳
DATE		日期	2016-08-08

STRING
VARCHAR		长度 1～65535
CHAR		最大长度255

关于整型：
默认情况下，整型数据默认为INT，除非数字超出INT的范围，在这种情况下它被表示为 BIGINT，或者直接指定100Y，100S, 100L 才会对应转换成TINYINT、SMALLINT、BIGINT。

关于浮点型：
浮点数假定为 DOUBLE，Decimal 字为 DOUBLE 类型提供精确值和浮点数的更大范围。 Decimal 数据类型存储数值的精确表示，而 DOUBLE 数据类型存储非常接近数值的近似值。DECIMAL不指定精度时默认为DECIMAL(10,0)；DOUBLE 的(非常接近)近似值不足以满足要求是，需要使用Decimal 类型，例如财务应用程序，相等和不等式检查以及舍入操作。对于处理 DOUBLE 范围(大约-10308 到 10308)或非常接近零(-10-308 到 10-308)之外的数的用例，也需要它们。另外，Decimal为专门为财务相关问题设计的数据类型。

关于字符型：
Strings 字符串数据可以用单引号(‘)或双引号(“)表示.Hive 在 strings 中使用 C-style 转义。
Varchar 使用长度说明符(介于 1 和 65535 之间)创建 Varchar 类型，该长度说明符定义字符 string 中允许的最大字符数。如果varchar value超过了长度说明符，则会以静默方式截断 string。字符长度由字符串中包含的字符数决定。与 string 一样，尾随空格在 varchar 中很重要，会影响比较结果。
Char 类型与 Varchar 类似，但它们是固定长度意味着短于指定长度 value 的值用空格填充，但尾部空格在比较期间不影响比较。最大长度固定为 255。

关于时间类型：
Timestamps
timestamp表示UTC时间，可以是以秒为单位的整数；带精度的浮点数，最大精确到小数点后9位，纳秒级；java.sql.Timestamp格式的字符串 YYYY-MM-DD hh:mm:ss.fffffffff
Date
Hive中的Date只支持YYYY-MM-DD格式的日期，其余写法都是错误的，如需带上时分秒，请使用timestamp

复杂数据类型：

数据类型	释义
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<data_type, data_type, …>

Hive DDL操作

关于库：

创建库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]　　　　　　//关于数据块的描述
[LOCATION hdfs_path]　　　　　　　　　　//指定数据库在HDFS上的存储位置
[WITH DBPROPERTIES (property_name=property_value, …)];　　　　//指定数据块属性

eg: create database t1;
** create database if not exists t1;** create database if not exists t1 comment ‘comment dor t1’;
**create database if not exists t3 with dbproperties(‘creator’=’cpeixin’,’date’=’2016-04-05’);

查看库
show databases;
** desc database extended t1;
**show create database t1;
删除库

** **drop database dbname;

切换库
```
use dbname;
```

关于表：

创建表
下面为官网给出的建表参数：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [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, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [<font></font>
   [ROW FORMAT row_format]
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]

内部表和外部表

默认情况下，Hive创建内部表，其中文件，元数据和统计信息由内部Hive进程管理
这里我们需要知道最根本的区别就是，在删除内部表的时候，数据也会被删除，而外部表不会。

STORED as 存储格式

屏幕快照 2020-03-25 下午9.44.34.png
是指定文件的类型，保存在hive中的文件的类型有多种，一般简单就保存为文本格式，即TEXTFILE，但是企业中一般不使用这种格式来保存数据，主要是因为文本格式占的空间比较大，不利于大数据分析。企业中一般使用ORC和PARQUET，AVRO三种文件类型来保存，具体的会在后面讲解。

ROW FORMAT DELIMITED 行分隔符

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘ ‘

这句的意思是以空格来分隔行数据，那么这一行中的数据只要遇到一个空格就可以划分为一个数据。这里的分隔符可以是其他字符，比如”,”,”#”,”|”等，一般只要用数据文件中可以区分每一行中的不同数据即可。

列与列直接的分隔符通常是以换行符来区分，可以用如下的语句来指定：
`COLLECTION ITEMS TERMINATED BY ‘\n’，通常列与列直接的分隔符是不需要写的。

LOCATION hdfs_path **

可以在创建表的时候指定该表映射到到hdfs的文件路径，默认是映射到/user/hive/warehouse目录下。

PARTITIONED BY 分区

为了对表进行合理的管理以及提高查询效率，Hive可以将表组织成“分区”。一个分区实际上就是表下的一个目录，一个表可以在多个维度上进行分区，分区之间的关系就是目录树的关系。

通过PARTITIONED BY子句指定，分区的顺序决定了谁是父目录，谁是子目录。在这里分区又分为静态分区和动态分区。简单的来说，静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断

下面进行建表，建表的示例也会尽可量的使用各种数据类型进行解释说明：

建表实例：

1.创建普通表，不添加任何参数**

create table t_user_details(
user_name string,
age tinyint,
phone_number string,
birth_date string,
deposit_amount float,
promotion_amount double,
register_date date,
last_login_time timestamp,
user_level int,
vip_flag boolean
);

hive (test)> show create table t_user_details;
OK
createtab_stmt
CREATE TABLE `t_user_details`(
  `user_name` string, 
  `age` tinyint, 
  `phone_number` string, 
  `birth_date` string, 
  `deposit_amount` float, 
  `promotion_amount` double, 
  `register_date` date, 
  `last_login_time` timestamp, 
  `user_level` int, 
  `vip_flag` boolean)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://localhost:8020/user/hive/warehouse/test.db/t_user_details'
TBLPROPERTIES (
  'bucketing_version'='2', 
  'transient_lastDdlTime'='1586087852')

insert into t_user_details values ('brent',27,'13611111111','1993-03-14',100.91,4000.56,2016-04-05,'2016-04-07 14:20:36.345',6,True),('haylee',25,'13211111111','1994-03-14',130.91,40000000.56,'2016-04-06','2016-04-08 14:20:36.345',6,False);

屏幕快照 2020-04-05 下午8.53.05.png

观察以上内容，是针对没有输入任何建表参数的情况下，所生成的结果，在show create table xx的结果中，体现出了完整的建表默认参数。

关于ROW FORMAT SERDE，用于指定序列化和反序列化的规则，默认值：LazySimpleSerDe
简单来说，就是它希望对于Deserialization，反序列化，可以lazy一点。对于Serialization，序列化，可以simple一点

在没有指定字段之间的分隔符时，默认是用\001 不可见字符进行分割的，我们也可以在建表的时候使用FIELDS TERMINATED BY ‘,’ 参数来指定字段之间使用逗号分割。

除此之外，在使用hive的时候，存储格式的选择非常重要，不同存储格式直接在最底层影响着你的执行效率，所以这部分在之后用单独的一篇文章来说。这里大家先知道企业里面常用的ORC，Parquet，Avro等格式就可以了

2.创建分区表
**
分区表在显示工作中非常常用，例如针对网站数据的存储，网站每天产生数据量过大的话，我们不能始终在表末尾进行数据的追加，而是应该利用动态分区或者静态分区，按月，按天的粒度进行分区存储

CREATE TABLE `t_user_detail_partition`(
    user_name string,
    age tinyint,
    phone_number string,
    birth_date string,
    deposit_amount float,
    promotion_amount double,
    register_date date,
    last_login_time timestamp,
    user_level int,
    vip_flag boolean)
PARTITIONED BY ( 
  `snapshot_date` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'='\u0001', 
  'line.delim'='\n', 
  'serialization.format'='\u0001') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
;

insert into t_user_detail_partition values ('brent',27,'13611111111','1993-03-14',100.91,4000.56,2016-04-05,'2016-04-07 14:20:36.345',6,True,'2016-04-04'),('haylee',25,'13211111111','1994-03-14',130.91,40000000.56,'2016-04-06','2016-04-08 14:20:36.345',6,False,'2016-04-05');

上面的建表语句使用 PARTITIONED BY 指定了 snapshot_date 为分区字段，当然你还可以再添加分区字段，hive支持多分区，理论上最多支持8级分区

在插入数据的语句中，将分区字段的值顺序的写在表中字段值的后面，则可以按照分区进行插入数据。

屏幕快照 2020-04-05 下午9.48.19.png

在hdfs中，t_user_detail_partition表则按照分区字段进行划分，将数据存储到不同的分区目录下。

Hive中的分区是使用的表外字段，MySQL使用的是表内字段

静态分区和动态分区在创建表时，语句是一样的。只是在赋值的时候有区别

动态分区和静态分区的区别

加载数据的方式：静态分区可以通过load命令，向不同的分区加载数据，加载数据时要指定分区的值；静态分区只能通过select加载数据，并且不需要指定分区的名字，而是根据伪列的值，动态的确定分区值
确定分区值的方式：两者在创建表的时候命令完全一致，只是在确定分区值的时候不同，静态分区需要手动指定分区值，而动态分区会自动识别伪列的属性，动态生成分区值

_动态分区插入_（或多分区插入）旨在通过动态确定在扫描输入表时应创建和填充哪些分区来解决此问题。这是新增功能，仅从0.6.0版本开始可用。在动态分区插入中，将评估输入列的值，以确定应将此行插入哪个分区。如果尚未创建该分区，它将自动创建该分区。使用此功能，您只需一个插入语句即可创建并填充所有必要的分区。

另外，由于只有一个insert语句，因此只有一个对应的MapReduce作业。与多次插入的情况相比，这显着提高了性能并减少了Hadoop集群的工作量。

以下是使用一个插入语句将数据加载到所有国家/地区分区的示例：

1
2
3

FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
       SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.country

创建Parquet格式，snappy压缩Hive表

CREATE TABLE t_user_behavior (
    user_id bigint,
    good_id bigint,
    good_category_id bigint,
    behavior_type string,
    timestamps timestamp)
partitioned by (snapshot_date string)
STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');

不要忘记加参数

val spark: SparkSession = SparkSession
  .builder()
  .master("local")
  .appName("login_data_2_hive")
  .config("spark.sql.session.timeZone", "UTC")
  .config("spark.serializer","org.apache.spark.serializer.KryoSerializer")
  .config("hive.exec.dynamici.partition",true)
  .config("hive.exec.dynamic.partition.mode","nonstrict")
  .enableHiveSupport()
  .getOrCreate()

user_behavior_dataframe
  .write
  .format("Hive")
  .mode(SaveMode.Overwrite)
  .partitionBy("snapshot_date")
  .saveAsTable("test.t_user_behavior")

#### 浏览表和分区

SHOW TABLES;

列出仓库中的现有表；其中有很多，可能比您想要浏览的更多。
SHOW TABLES 'page.*';

列出前缀为“ page”的表。该模式遵循Java正则表达式语法（因此句点是通配符）。
SHOW PARTITIONS page_view;

列出表分区。如果该表不是分区表，则会引发错误。
DESCRIBE page_view;

列出表的列和列类型。
DESCRIBE EXTENDED page_view;

列出表的列和所有其他属性。这会打印很多信息，但也不会以漂亮的格式显示。通常用于调试。
DESCRIBE EXTENDED page_view PARTITION (ds='2008-08-08');

列出分区的列和所有其他属性。这还会打印很多通常用于调试的信息。

修改表

将现有表重命名为新名称。如果具有新名称的表已经存在，则返回错误：
ALTER TABLE old_table_name RENAME TO new_table_name;

重命名现有表的列。确保使用相同的列类型，并为每个现有列包括一个条目：
ALTER TABLE old_table_name REPLACE COLUMNS (col1 TYPE, ...);

要将列添加到现有表：
ALTER TABLE tab1 ADD COLUMNS (c1 INT COMMENT 'a new int column', c2 STRING DEFAULT 'def val');

请注意，架构更改（例如添加列）会保留表的旧分区的架构，以防它是分区表。所有访问这些列并在旧分区上运行的查询都隐式返回这些列的空值或指定的默认值。
在以后的版本中，如果在特定分区中未找到该列，则可以使某些行为具有假设值，而不是抛出错误。

删除表和分区

删除表相当简单。将该表放下将隐式删除该表上已建立的所有索引（这是将来的功能）。关联的命令是：
DROP TABLE pv_users;

删除分区。更改表以删除分区。
ALTER TABLE pv_users DROP PARTITION (ds='2008-08-08')
    
请注意，此表或分区的任何数据都将被删除并且可能无法恢复。

### Hive DML操作

加载文件数据到表

1
2
3

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] 
INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
复制代码

LOCAL 关键字代表从本地文件系统加载文件，省略则代表从 HDFS 上加载文件：
从本地文件系统加载文件时， filepath 可以是绝对路径也可以是相对路径 (建议使用绝对路径)；
从 HDFS 加载文件时候，filepath 为文件完整的 URL 地址：如 hdfs://namenode:port/user/hive/project/ data1
filepath 可以是文件路径 (在这种情况下 Hive 会将文件移动到表中)，也可以目录路径 (在这种情况下，Hive 会将该目录中的所有文件移动到表中)；
如果使用 OVERWRITE 关键字，则将删除目标表（或分区）的内容，使用新的数据填充；不使用此关键字，则数据以追加的方式加入；
加载的目标可以是表或分区。如果是分区表，则必须指定加载数据的分区；
加载文件的格式必须与建表时使用 STORED AS 指定的存储格式相同。

查询结果插入到表

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 0.13.0 开始，建表时可以通过使用 TBLPROPERTIES（“immutable”=“true”）来创建不可变表 (immutable table) ，如果不可以变表中存在数据，则 INSERT INTO 失败。（注：INSERT OVERWRITE 的语句不受 immutable 属性的影响）;
可以对表或分区执行插入操作。如果表已分区，则必须通过指定所有分区列的值来指定表的特定分区；
从 Hive 1.1.0 开始，TABLE 关键字是可选的；
从 Hive 1.2.0 开始，可以采用 INSERT INTO tablename(z，x，c1) 指明插入列；
可以将 SELECT 语句的查询结果插入多个表（或分区），称为多表插入。语法如下：

FROM from_statement
INSERT OVERWRITE TABLE tablename1 
[PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;

**动态插入分区**
**

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) 
select_statement FROM from_statement;
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) 
select_statement FROM from_statement;
复制代码

在向分区表插入数据时候，分区列名是必须的，但是列值是可选的。如果给出了分区列值，我们将其称为静态分区，否则它是动态分区。动态分区列必须在 SELECT 语句的列中最后指定，并且与它们在 PARTITION() 子句中出现的顺序相同。

注意：Hive 0.9.0 之前的版本动态分区插入是默认禁用的，而 0.9.0 之后的版本则默认启用。

使用SQL语句插入值

1
2
3

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] 
VALUES ( value [, value ...] )
复制代码

使用时必须为表中的每个列都提供值。不支持只向部分列插入值（可以为缺省值的列提供空值来消除这个弊端）；
如果目标表表支持 ACID 及其事务管理器，则插入后自动提交；
不支持支持复杂类型 (array, map, struct, union) 的插入。

更新和删除数据

更新和删除的语法比较简单，和关系型数据库一致。需要注意的是这两个操作都只能在支持 ACID 的表，也就是事务表上才能执行。

-- 更新
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
--删除
DELETE FROM tablename [WHERE expression]

查询结果写出到文件系统

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] 
  SELECT ... FROM ...
复制代码

OVERWRITE 关键字表示输出文件存在时，先删除后再重新写入；
和 Load 语句一样，建议无论是本地路径还是 URL 地址都使用完整的；

写入文件系统的数据被序列化为文本，其中列默认由^A 分隔，行由换行符分隔。如果列不是基本类型，则将其序列化为 JSON 格式。其中行分隔符不允许自定义，但列分隔符可以自定义，如下：

-- 定义列分隔符为'\t' 
insert overwrite local directory './test-04' 
row format delimited 
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
select * from src;

本文链接： cpeixin.cn/2016/10/20/Hive-%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/
版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！

Brent大数据工程师 & 机器学习

一心九用的工程师