平台搭建---Hive使用介绍

虽然数据存储的方式与传统数据库不一样，但使用的方式跟mysql是很像的，如何没有时间学习怎么用可以先按传统数据的语法规则来，出了问题再去查细节。
文章来源

Hive简介

Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具,可以用来进行数据提取转化加载(ETL),这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言,称为 HQL,它允许熟悉 SQL 的用戶查询数据。同时,这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。
首先,我来说说什么是hive(What is Hive?),请看下图:

Hive构建在Hadoop的HDFS和MapReduce之上,用于管理和查询结构化/非结构化数据的数据仓库。

• 使用HQL作为查询接口
• 使用HDFS作为底层存储
• 使用MapReduce作为执行层

Hive的应用，如下图所示

这里集群搭建Hive时用到了HA，最后用HAProxy来做代理。

结构描述

Hive 的结构可以分为以下几部分:

• 用戶接口:包括 CLI, Client, WU
• 元数据存储。通常是存储在关系数据库如 mysql, derby 中
• 解释器、编译器、优化器、执行器
• Hadoop:用 HDFS 进行存储,利用 MapReduce 进行计算

1、 用戶接口主要有三个:CLI,Client 和 WUI。其中最常用的是 CLI,Cli 启动的时候,会同时启动一个 Hive 副本。Client 是 Hive 的客戶端,用戶连接至 Hive Server。在启动 Client 模式的时候,需要指出 Hive Server 所在节点,并且在该节点启动 Hive Server。 WUI 是通过浏览器访问 Hive。
2、 Hive 将元数据存储在数据库中,如 mysql、derby。Hive 中的元数据包括表的名字,表的列和分区及其属性,表的属性(是否为外部表等),表的数据所在目录等。
3、 解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中,并在随后有 MapReduce 调用执行。

Hive和普通DB的异同

	Hive	RDBMS
查询语句	HQL	SQL
数据存储	HDFS	Raw Device or Local FS
索引	1.0.0版本支持	有
执行延迟	高	低
处理数据规模	大(或海量)	小
执行	MapReduce	Excutor

元数据

Hive 将元数据存储在 RDBMS 中,一般常用的有MYSQL和DERBY。由于DERBY只支持单客戶端登录,所以一般采用MySql来存储元数据。

数据存储

首先,Hive 没有专门的数据存储格式,也没有为数据建立索引,用戶可以非常自由的组织 Hive 中的表,只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符,Hive 就可以解析数据。
其次,Hive 中所有的数据都存储在 HDFS 中,Hive 中包含以下数据模型:Table,External Table,Partition,Bucket。

1. Hive 中的 Table 和数据库中的 Table 在概念上是类似的,每一个 Table 在 Hive 中都有一个相应的目录存储数据。例如,一个表 app,它在 HDFS 中的路径为:/ warehouse /app,其中,wh是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的数据仓库的目录,所有的 Table 数据(不包括 External Table)都保存在这个目录中。
   安装hive后，会在hdfs上创建如/user/hive/warehouse/这样的的属于hive的文件夹；如果我们在hive中创建数据库，则会在warehouse下产生一个子目录，形如/user/hive/warehouse/xxx.db;如果接着在该数据库中创建一个表，则会继续产生子目录，形如/user/hive/warehouse/xxx.db/yyyyyy；
2. Partition 对应于数据库中的 Partition 列的密集索引,但是 Hive 中 Partition 的组织方式和数据库中的很不相同。在 Hive 中,表中的一个 Partition 对应于表下的一个目录,所有的 Partition 的数据都存储在对应的目录中。例如:xiaojun 表中包含 dt 和 city 两个 Partition,则对应于 dt = 20100801, ctry = US 的 HDFS 子目录为:/ warehouse /app/dt=20100801/ctry=US;对应于 dt = 20100801, ctry = CA 的 HDFS 子目录为;/ warehouse /app/dt=20100801/ctry=CA
   这里对应了Hive将数据分块的方式，它是以某一个变量的取值来分枝的，一个值对应一个枝，即对应一个目录，，然后再用下一个变量进一步分枝，即进一步分出更多目录；
   如果创建表时有分区，则会在目录中产生分区标识来区分的文件，形如/user/hive/warehouse/xxx.db/yyyyyy/date=20180521,文件中即保存着相关的内容，以一定的分隔符区分字段;
3. Buckets 对指定列计算 hash,根据 hash 值切分数据,目的是为了并行,每一个 Bucket 对应一个文件。将 user 列分散至 32 个 bucket,首先对 user 列的值计算 hash,对应 hash 值为 0 的HDFS 目录为:/ warehouse /app/dt =20100801/ctry=US/part-00000;hash 值为 20 的 H
   DFS 目录为:/ warehouse /app/dt =20100801/ctry=US/part-00020
   如果指定Buckets，则date=20180521不是文件，而是文件名，然后再它的下级会产生以某一列值的hash 值为区分的文件，形如/user/hive/warehouse/xxx.db/yyyyyy/date=20180521/part-00000,文件中即保存着相关的内容
4. External Table 指向已经在 HDFS 中存在的数据,可以创建 Partition。它和 Table 在元数据的组
   织上是相同的,而实际数据的存储则有较大的差异。

Table (内部表)的创建过程和数据加载过程(这两个过程可以在同一个语句中完成),在加载数据的过程中,实际数据会被移动到数据仓库目录中;之后对数据对访问将会直接在数据仓库目录中完成。删除表时,表中的数据和元数据将会被同时删除。

External Table 只有一个过程,加载数据和创建表同时完成(CREATE EXTERNAL TABLE …LOCATION),实际数据是存储在 LOCATION 后面指定的 HDFS 路径中,并不会移动到数据仓库目录中。当删除一个 External Table 时,仅删除hive的元数据,不会删除hdfs上对应的文件。

Hive使用一段时间后Hadoop集群占用空间暴增的原因

Hive的基本操作

Create Table

介绍

• CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用戶可以用IF NOT EXIST 选项来忽略这个异常。

• EXTERNAL 关键字可以让用戶创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。

• LIKE 允许用戶复制现有的表结构,但是不复制数据。

• 用戶在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用戶还需要为表指定列,用戶在指定表的列的同时也会指定自定义的 SerDe,Hive 通过 SerDe 确定表的具体的列的数据。

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

• 有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区,每一个分区单独存在一个目录下。而且,表和分区都可以对某个列进行 CLUSTERED BY 操作,将若干个列放入一个桶(bucket)中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。

• 表名和列名不区分大小写,SerDe 和属性名区分大小写。表和列的注释是字符串

• SerDe是Serialize/Deserilize的简称，用于序列化和反序列化。

• STORED AS TEXTFILE：默认格式，数据不作压缩，磁盘开销大，数据解析开销大。可结合Gzip和Bzip使用（系统自动检查，执行查询时自动解压），但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

• STORED AS SEQUENCE:Hadoop API，提供一种二进制文件支持，其具有使用方便，可分割可压缩的特点。SequenceFile支持三种压缩选择：NONE、RECORD、BLOCK，RECORD压缩率低，一般建议使用BLOCK压缩。

语法

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]
| STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] [AS select_statement] CREATE [EXTERNAL] TABLE [IF NO
T EXISTS] table_name
LIKE existing_table_name
[LOCATION hdfs_path]

data_type
: primitive_type
| array_type
| map_type
| struct_type

primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| STRING

array_type
: ARRAY < data_type >

map_type
: MAP < primitive_type, data_type >

struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>

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,
...)]

file_format:
: SEQUENCEFILE
| TEXTFILE
| RCFILE (Note: only available starting with 0.6.0)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

Hive 数据类型

Hive 数据类型和存储格式、建表操作
基本数据类型

数据类型	所占字节	开始支持版本
TINYINT	1byte，-128 ~ 127 ，如10Y
SMALLINT	2byte，-32,768 ~ 32,767 ，如10S
INT	4byte,-2,147,483,648 ~ 2,147,483,647，如10
BIGINT	8byte,-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807，如10L
BOOLEAN
FLOAT	4byte单精度
DOUBLE	8byte双精度
STRING
BINARY		Hive0.8.0
TIMESTAMP	格式为yyyy-MM-dd HH:MM:SS.fffffffff，通过LOAD命令将文本加载到已创建的表中时，只有满足上述格式的内容才可以被正确解析，否则只能识别为NULL。	Hive0.8.0
DATE	YYYY-MM-DD	Hive0.12.0
DECIMAL	DECIMAL(precision, scale) ，如decimal(10,0)	Hive0.11.0
CHAR	长度255	Hive0.13.0
VARCHAR	长度1 to 65355	Hive0.12.0
Null	缺少值通过特殊值 - NULL表示

很多时候会用string类型来表示，即使像时间相关的数据，数据的解析交由程序本身来完成。
集合数据类型

数据类型	所占字节	开始支持版本
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<int, double, array, struct<a:int,b:string>>，{0:1}，{1:2.0}，{2:[“three”,“four”]} ，{3:{“a”:5,“b”:“five”}} ，{2:[“six”,“seven”]} ，{3:{“a”:8,“b”:“eight”}} ，{0:9} ，{1:10.0}	Hive 0.7.0开始支持

基本示例

1、如果一个表已经存在,可以使用if not exists
2、create table user(id int,cont string) row format delimited fields terminated by ‘\005’ stored as textfile; terminated by:关于来源的文本数据的字段间隔符
3、如果要将自定义间隔符的文件读入一个表,需要通过创建表的语句来指明输入文件间隔符,然后load data到这个表。
4、Shops数据库常用间隔符的读取 我们的常用间隔符一般是Ascii码5,Ascii码7等。在hive中Ascii码5用’\005’表示, Ascii码7用’\007’表示,依此类推。
5、装载数据查看一下:Hadoop fs -ls LOAD DATA INPATH ‘/user/admin/user/a.txt’ OVERWRITE IN TO TABLE user;
6、如果使用external建表和普通建表区别:前者存放元数据,删除后文件系统中的数据不会删除,后者会直接删除文件系统中的数据

创建分区

HIVE的分区通过在创建表时启用partition by实现,如下面的示例所示；用来partition的维度并不是实际数据的某一列,具体分区的标志是由插入内容时给定的，如insert into test partition(dt='2018-08-15') select * from df_Temp。分区标志在查询结果中也是一列，在hdfs上则对应一个文件夹；当要查询某一分区的内容时可以采用where语句,形似where tablename.partition_key > a来实现。 创建含分区的表。 命令原型:

CREATE TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
STORED AS SEQUENCEFILE;

如建表：

CREATE TABLE c02_clickstat_fatdt1
(yyyymmdd string,
id INT,
ip string,
country string,
cookie_id string,
page_id string,
clickstat_url_id string,
query_string string,
refer string
)PARTITIONED BY(dt STRING)
row format delimited fields terminated by '\005' stored as textfile;

create table test(
fullcode string,
fullname string,`date` string
) partitioned by (dt string) stored as textfile

装载数据：

LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20131101/19/clickstat_gp_fatdt0/0' OVERWRITE INTO TA
BLE c02_clickstat_fatdt1
PARTITION(dt='20131101');

访问某个分区：

SELECT count(*) FROM c02_clickstat_fatdt1 a
WHERE a.dt >= '20131101' AND a.dt < '20131102';

指定Location位置

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User',
country STRING COMMENT 'country of origination')
COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
STORED AS TEXTFILE
LOCATION '<hdfs_location>';

复制一个空表：

CREATE TABLE empty_key_value_store
LIKE key_value_store;

hive分桶管理

hive分桶的原理

hive分桶的作用

hive分桶和分区的区别

hive分桶的操作

hive 中的二级分区表和动态分区表

create table test_bucket_table(
id int,
name string,
addr string
)
clustered by (id) into 4 buckets
row format delimited fields terminated by '\|';
`//强制开启分桶
set hive.enforce.bucketing=true;
insert overwrite table test_bucket_table select * from dept;
`//若没有使用hive.enforce.bucketing属性, 则需要设置和分桶个数相匹配的reducer个数, 同时SELECT后添加CLUSTER BY
set mapred.reduce.tasks=4;
insert into table test_bucket_table select * from dept cluster by deptno;

HIVE-分桶表的详解和创建实例
使用hive脚本一键动态分区、分区又分桶以及脚本步骤详细详解（亲测成功）
hive分桶 与保存数据的方式
使用sort by 可以指定排序，使用cluster by 不能，默认是升序。

解决spark hive插入数据异常Spark currently does NOT populate bucketed output

set hive.enforce.bucketing=false;
set hive.enforce.sorting=false;

Enable creating hive bucketed tables

分区分桶性能对比

3.6亿条数据、66个字段，其中的deviceid用9万多个
原来按日期分区，按询整个所有数据用时3分37秒；
后来变成分区分桶表，按日期分区，按deviceid分桶，30个桶（在spark中操作，数据成功插入，但是不确定是否真的分桶成功，从文件来看，似乎没真正按设计分桶）；查询用时2分6秒；加速不到一半
第三种方式，双分区，先按日期分区，然后再用deviceid的后两位来分区，内部就形成100个分区，再乘以外层的天；从查看hdfs上文件大小看，按deviceid后两位分区，基本上数据大小是均匀的，没有太明显的偏科。用时5.15s，目前看改观较大，但对分区数会有担心。

行分隔符

ASCII码值表
Char列就是我们平时看到的分隔符比如“|”，比如“，”等，Hex就是对应的ASCII码对应的十六进制形式，也就是我们平时建hive表时"FIELDS TERMINATED BY ‘\054’"时by后面的。

Alter Table

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息；也可以使用该命令修改数据库的用户或权限，但是数据库的其他元数据信息都是不可以更改的，包括数据库名和数据库所在的目录位置。

添加分区

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'l
ocation2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

例子：

ALTER TABLE c02_clickstat_fatdt1 ADD
PARTITION (dt='20131202') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20131202'
PARTITION (dt='20131203') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20131203';

删除分区

ALTER TABLE table_name DROP partition_spec, partition_spec,...

例子：

ALTER TABLE c02_clickstat_fatdt1 DROP PARTITION (dt='20101202');

删除符合条件的数据:

insert overwrite table t_table1 select * from t_table1 where XXXX;

其中xxx是你需要保留的数据的查询条件。

重命名表

Hive修改表名，列名，列注释，表注释，增加列，调整列顺序，属性名等操作

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 tcdc_recmd_hotelsales_predict change column sales_predict sales_predict_new decimal(30,11);

添加/替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

ADD是代表新增一字段,字段位置在所有列后面(partition列前);REPLACE则是表示替换表中所有字段。
例子:

hive> desc xi;
OK
id
int
cont string
dw_ins_date string
Time taken: 0.061 seconds
hive> create table xibak like xi;
OK
Time taken: 0.157 seconds
hive> alter table xibak replace columns (ins_date string);
OK
Time taken: 0.109 seconds
hive> desc xibak;
OK
ins_date
string

删除列
用replace变相实现

ALTER TABLE test REPLACE COLUMNS (

creatingTs BIGINT,
a STRING,
b BIGINT,
c STRING,
d STRING,
e BIGINT
);

hive改表结构的两个坑

创建视图

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

注：视图关键字，视图是只读的，不能用LOAD/INSERT/ALTER

显示表

查看表名:

SHOW TABLES;

查看表名,部分匹配:

SHOW TABLES 'page.*';
SHOW TABLES '.*view';

查看某表的所有Partition,如果没有就报错:

SHOW PARTITIONS page_view;

查看某表结构:

DESCRIBE invites;

查看分区内容:
SELECT a.foo FROM invites a WHERE a.ds=‘2012-08-15’;
查看有限行内容,同Greenplum,用limit关键词:

SELECT a.foo FROM invites a limit 3;

查看表分区定义:

DESCRIBE EXTENDED page_view PARTITION (ds='2013-08-08');

Hive中数据导入导出

hive的数据导入与数据导出：（本地，云hdfs，hbase），列分隔符的设置，以及hdfs上传给pig如何处理
向hive中加载或添加数据的三种方式
导入数据到hive表中的6种方式

加载

HIVE装载数据没有做任何转换加载到表中的数据只是进入相应的配置单元表的位置移动数据文件。纯加载操作复制/移动操作。

语法

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=v
al2 ...)]

Load 操作只是单纯的复制/移动操作,将数据文件移动到 Hive 表对应的位置。如:从本地导入数据到表格并追加原表

LOAD DATA LOCAL INPATH `/tmp/pv_2013-06-08_us.txt` INTO TABLE c02 PARTITION(date='2013-06-08', country
='US')

从本地导入数据到表格并追加记录:

LOAD DATA LOCAL INPATH './examples/files/kv1.txt' INTO TABLE pokes;

从hdfs导入数据到表格并覆盖原表:

LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20131101/18/clickstat_gp_fatdt0/0' INTO table c02_click
stat_fatdt1 OVERWRITE PARTITION (dt='20131201');

关于来源的文本数据的字段间隔符 如果要将自定义间隔符的文件读入一个表,需要通过创建表的语句来指明输入文件间隔符,然后load data到这个表就ok了。

Hive使用HDFS目录数据创建Hive表分区

来源

yesterday=`date -d -1days +%Y-%m-%d`  
hive -e "load data inpath '/user/pms/workspace/ouyangyewei/testUsertrack/job1Output/crossSale' into table pms.cross_sale_path partition(ds='$yesterday');" 

插入

INSERT语法

Standard syntax:
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

Hive extension (dynamic partition inserts):
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

spark中插入数据到hive表

将dataframe数据写入Hive分区表
http://spark.apache.org/docs/latest/api/python/pyspark.sql.html

#-*-coding:utf-8-*-

from pyspark import SparkContext
from pyspark.sql import SparkSession
import pandas as pd

if __name__=="__main__":
    sc=SparkContext(appName='myApp')
    spark=SparkSession.builder.enableHiveSupport().getOrCreate()
    # create table test(fullcode string,fullname string,`date` string) partitioned by (dt string) stored as textfile
    df=spark.createDataFrame([("002","地下","2018-08-15T13:05:00")],("fullcode","fullname","date"))
    df.show()
    """
    +--------+--------+-------------------+                                         
    |fullcode|fullname|               date|
    +--------+--------+-------------------+
    |     002|      地下|2018-08-15T13:05:00|
    +--------+--------+-------------------+
    """
    df.registerTempTable("df_Temp")
    sql = "use app"
    spark.sql(sql)
    sql = "insert into test partition(dt='2018-08-15') select * from df_Temp"
    spark.sql(sql)
    sql = "select * from app.test"
    df2=spark.sql(sql)
    df2.show()
    """
    +--------+--------+-------------------+----------+                              
    |fullcode|fullname|               date|        dt|
    +--------+--------+-------------------+----------+
    |     002|      地下|2018-08-15T13:05:00|2018-08-15|
    |     001|      天上|2018-08-15T23:04:00|2018-08-15|
    +--------+--------+-------------------+----------+
    """

spark中写入数据到hive表

(df.write.format('parquet')  
	.bucketBy(100, 'year', 'month')
	.sortBy('day')
	.mode("overwrite")
	.saveAsTable('sorted_bucketed_table'))

其他可参考:Spark 增量操作 insertInto() 与 mode(SaveMode.Append).saveAsTable() 的区别

hive表格按分区存储的静态分区和动态分区之分

静态分区就是人为指定数据存储到哪个分区，此时dataframe中不包含分区字段，而是人为指定。如下

df = hiveContext.createDataFrame([("002","地下","2018-08-15T13:05:00"),("003","地下","2018-08-16T13:05:00"),("004","地下","2018-08-16T13:05:00")],("fullcode","fullname","date"))
df.registerTempTable("df_Temp")
sql ="use test"#需要先指定数据库，好像不能在后面用test.test表示，可能是版本问题；
hiveContext.sql(sql)
sql = "insert overwrite table test partition(dt='2018-08-15') select * from df_Temp"
hiveContext.sql(sql)

利用静态分区的形式，插入数据正常

动态分区是hive自动根据dataframe中分区字段的值将数据写到对应的分区中，此时dataframe中需要人为的多加一个分区字段，如下

df = hiveContext.createDataFrame([("002","地下","2018-08-15T13:05:00","2018-08-10"),("003","地下","2018-08-16T13:05:00","2018-08-11"),("004","地下","2018-08-16T13:05:00","2018-08-12")],("fullcode","fullname","date","dt"))
df.registerTempTable("df_Temp")
sql ="use test"
hiveContext.sql(sql)
sql = "set hive.exec.dynamic.partition.mode=nonstrict"#要不程序中设置，好坑，应该可以在什么地方配置。
hiveContext.sql(sql)
sql = "insert overwrite table test partition(`dt`) select * from df_Temp"
hiveContext.sql(sql)

对比insert overwrite 和insert into 的差别：
3天的数据共847500000条，在原表无数据的情况下，用insert overwrite table test插入hive表，用时23分中
在3天的数据已经存在hive表的情况下，改成insert into table test,将同样的3天数据插入到hive中，用时25 minute，总数据条数变成1695000000。在这种情况下原数据不会被删除，但其实用时并没有大量增加。

直接利用dataframe保存数据,dataframe中也需要人为的多加一个分区字段

df = hiveContext.createDataFrame([("002","地下","2018-08-15T13:05:00","2018-08-10"),("003","地下","2018-08-16T13:05:00","2018-08-11"),("004","地下","2018-08-16T13:05:00","2018-08-12")],("fullcode","fullname","date","dt"))
df.write.saveAsTable("test.test",mode='append',partionBy='dt')

但是在实践中发现在spark1.6.3版本中，用上面的方式将dataframe中的数据写入hive分区表中，hive中的建表语句（create table test(fullcode string,fullname string,date string) partitioned by (dt string) stored as textfile;），数据可以写成功hive表，但是分区没了。这个好生奇怪。
后来发现是spark版本的问题，至少在spark2.2.1中是可以按照上面的方式直接保存的。

#-*-coding:utf-8-*-

#import sys
#reload(sys)
#sys.setdefaultencoding('utf8')

from pyspark import SparkContext
from pyspark.sql import SparkSession

if __name__=="__main__":
        sc=SparkContext(appName='myApp')
        spark=SparkSession.builder.enableHiveSupport().getOrCreate()
        # create table testDataFrame(fullcode string,fullname string,`date` string) partitioned by (dt string) stored as textfile
        df=spark.createDataFrame([("002","地下","2018-08-15T13:05:00","2018-08-10"),("003","地下","2018-08-16T13:05:00","2018-08-11"),("004","地下","2018-08-16T13:05:00","2018-08-12")],("fullcode","fullname","date","dt"))
        df.show()
        sql = "use test"
        spark.sql(sql)
        df.write.saveAsTable("testDataFrame",mode='append',partionBy='dt')

更详细的信息可参考：
对现有Hive的大表进行动态分区
hive静态与动态分区理解
Hive静态分区表&动态分区表
Exception in thread “main” org.apache.spark.sql.AnalysisException: The format of the existing table dkl.test_partition is HiveFileFormat. It doesn’t match the specified format ParquetFileFormat.;
上文中是通过指定数据格式的方式来解决问题，但好像自己还是没解决

hive使用动态分区时如果动态分区的字段存在空值的问题
hive使用动态分区问题3

hive:默认允许动态分区个数为100，超出抛出异常：
可参考集群使用杂，用spark的配置设置（已产生效果）
在执行插入数据到分区时，添加参数设置：

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=10000;
set hive.exec.max.created.files=10000;

SparkException——Dynamic partition strict mode 问题解决

org.apache.spark.SparkException: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

用类似sqlContext.setConf("hive.exec.dynamic.partition.mode","nonstrict");
sparksql 设置动态分区数报错
hive.execution.engine=mr;

Hive中的小文件

Hive小文件合并
hive优化之自己主动合并输出的小文件
Hive小文件合并迁移
hive控制文件生成个数

从hdfs上导入hive同格式的数据到hive
load data inpath '/tmp/test' into table test.tbltbltbltbl partition(partition_date='2018-06-18',did='99');

当导入的是文件夹时，在hive对就目录下did='99'也会产生一个文件夹，但名字不再是test; 而是类似delta_0000001_0000001_0000，该文件夹下又会产生多个文件类似000066_0，文件数目与源端test目录下的数目相同；
同时源端test下的数据不存在。load只是在搬运数据，不会有过多的操作，


load data inpath '/tmp/test/*.*' into table test.tbltbltbltbl partition(partition_date='2018-06-18',did='99');
这个的语句似乎不行,应该是不能识别*


先将hive中分区文件合并到中间目录，然后再用hive load 数据到对应分区（应该不能用hdfs命令，应该要经过hive才能管理元数据这些）。
原理上应该是可以，但实际操作发现hive表不能查询，需要再仔细核对一下


hadoop fs -cat /warehouse/tablespace/managed/hive/test.db/tbltbltbltbl/partition_date=2018-06-18/did=99/delta_0000002_0000002_0000/* | hadoop fs -appendToFile - /tmp/test/temp.c000

hive -e "alter table test.tbltbltbltbl drop partition (partition_date='2018-06-18', did='99')"

hive -e "LOAD DATA INPATH '/tmp/test/temp.c000' INTO TABLE test.tbltbltbltbl PARTITION(partition_date='2018-06-18', did='99')"

用类似下面的语句可以控制在hive表中产生文件的数量,distribute by 对应reduce的数量，一个reduce对应一个文件。这个已经验证没问题。
从程序端来控制，如果是用insert into …… 就用 select * distribute by；如果是saveAsTable,则可以在之前用repartition
参考hive的高级查询（group by、 order by、 join 、 distribute by、sort by、 clusrer by、 union all等） ;这篇文章讲得相当清楚

sql = "insert into table sdp_electric_vehicles_data_shbus partition(partition_date,did) select * from df_Temp distribute by partition_date,did"

插入到本地文件

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/pv_gender_sum'
SELECT pv_gender_sum.*
FROM pv_gender_sum;

Insert时,from子句既可以放在select子句后,也可以放在insert子句前,下面两句是等价的

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;
hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

需要注意的是,hive没有直接插入一条数据的sql,不过可以通过其他方法实现: 假设有一张表B至少有一条数据,我们想向表A(int,string)中插入一条数据,可以用下面的方法实现:

from B insert table A select 1,‘abc’ limit 1;

我觉得Hive好像不能够插入一个记录,因为每次你写INSERT语句的时候都是要将整个表的值OVERWRITE。我想这个应该是与Hive的storage layer是有关系的,因为它的存储层是HDFS,插入一个数据要全表扫描,还不如用整个表的替换来的快些。
注：Hive不支持一条一条的用sert语句进行插入操作，也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入则不会修改。要么drop掉整个表，要么建立新表，导入新的数据。
但可以利用动态分区和insert into插入

关于hive中数据的导出，也可以参考hue的使用部分：hue中如何将spark的计算结果写出

WRITE语法

Standard syntax:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

导出文件到本地:

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

也可以利用linux系统功能实现如

hive> insert overwrite local directory '/home/wyp/wyp'
       > select * from wyp;

导出文件到HDFS:

INSERT OVERWRITE DIRECTORY '/user/admin/SqlldrDat/CnClickstat/20131101/19/clickstat_gp_fatdt0/0' SELECT
a.* FROM c02_clickstat_fatdt1 a WHERE dt=’20131201’;

一个源可以同时插入到多个目标表或目标文件,多目标insert可以用一句话来完成:

FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2013-04-08', hr='12') SELECT src.key WHERE src.key >= 200 a
nd src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

例子:

from tbl1
insert overwrite table test2 select '1,2,3' limit 1
insert overwrite table d select '4,5,6' limit 1;

关于hive数据导出方式更详细的内容可参考

删除

方式一：仅删除表中数据，保留表结构

truncate table 表名;

（truncate用于删除所有的行，这个行为在hive元存储删除数据是不可逆的）
或 delete from 表名 where 1 = 1 ;

（delete用于删除特定条件下的行，使用where 1=1 删除所有行 SQL中where 1 = 1 的使用）

truncate 不能删除外部表！因为外部表里的数据并不是存放在Hive Meta store中

方式二：删除整个表

删除一个内部表的同时会同时删除表的元数据和数据。删除一个外部表,只删除元数据而保留数据。
语法:

DROP TABLE tbl_name

利用上面的命令删除数据后，数据在hdfs上并不会马上消失，而是会进入回收站，用哪个用户来删除表的，就会在/user/XXX用户/.trash下
如果要立马释放空间，在hdfs上去这里删除相应的文件夹。（然后磁盘会在一段时间内渐渐释放空间）

如果要永久性删除，不准备再恢复：
drop table 表名 purge;

删除分区

如前所述

Hive实现update和delete

Hive实现update和delete
让Hive支持行级insert、update、delete

设置字段自增

Hive应用：设置字段自增
Hive row_number() 等用法
hive学习之六：row_number()排序函数的使用

hive数据表去重方法

hive group by distinct区别以及性能比较

select count(*) from (select DISTINCT *  from sdp_vehicle_hive.sdp_electric_vehicles_data_bjev) t

Limit/Top/REGEX

Limit 可以限制查询的记录数。查询的结果是随机选择的。下面的查询语句从 t1 表中随机查询5条记录:

SELECT * FROM t1 LIMIT 5

下面的查询语句查询销售记录最大的 5 个销售代表。

SET mapred.reduce.tasks = 1
SELECT * FROM sales SORT BY amount DESC LIMIT 5

SELECT 语句可以使用正则表达式做列选择,下面的语句查询除了 ds 和 hr 之外的所有列:

SELECT `(ds|hr)?+.+` FROM sales

查询

语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

hive中使用not in的方法

a=['01','02','03']
a=str(tuple(a))
print(a)
sql="select * from user where user.id not in %s" %(a)
print(sql)
a=['01','02','03']
a=str(tuple(a))
print(a)
sql="select * from user where user.id not in %s" %(a)
print(sql)

可以先用上面的语句将一些变量变成字符串，然后再拼接到sql中，如果如下

('01', '02', '03')
select * from user where user.id not in ('01', '02', '03')

GROUP BY

groupByClause: GROUP BY groupByExpression (, groupByExpression)*
groupByExpression: expression
groupByQuery: SELECT expression (, expression)* FROM src groupByClause?

Order/Sort By

Order by 语法:

colOrder: ( ASC | DESC )
orderBy: ORDER BY colName colOrder? (',' colName colOrder?)*
query: SELECT expression (',' expression)* FROM src orderBy

Sort By 语法: Sort顺序将根据列类型而定。如果数字类型的列,则排序顺序也以数字顺序。如果
字符串类型的列,则排序顺序将字典顺序。

colOrder: ( ASC | DESC )
sortBy: SORT BY colName colOrder? (',' colName colOrder?)*
query: SELECT expression (',' expression)* FROM src sortBy

Hive Join

语法:

join_table:
table_reference JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition

table_reference:
table_factor
| join_table

table_factor:
tbl_name [alias]
| table_subquery alias
| ( table_references )

join_condition:
ON equality_expression ( AND equality_expression )*

equality_expression:
expression = expression

Hive 只支持等值连接(equality joins)、外连接(outer joins)和(left/right joins)。Hive 不支持所有非等值的连接,因为非等值连接非常难转化到 map/reduce 任务。另外,Hive 支持多于 2 个表的连接。

注意事项

• 只支持等值join.

例如:

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b
ON (a.id = b.id AND a.department = b.department)

是正确的,然而:

SELECT a.* FROM a JOIN b ON (a.id b.id)

是错误的。

• 可以join多于2个表

例如:

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

如果join中多个表的 join key 是同一个,则 join 会被转化为单个 map/reduce 任务,例如:

SELECT a.val, b.val, c.val FROM a JOIN b
ON (a.key = b.key1) JOIN c
ON (c.key = b.key1)

被转化为单个 map/reduce 任务,因为 join 中只使用了 b.key1 作为 join key。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key2)

而这一 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件,而 b.key2 用于第二次 join。

• join时，map/reduce的逻辑

reducer 会缓存 join 序列中除了最后一个表的所有表的记录,再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中,应该把最大的那个表写在最后(否则会因为缓存浪费大量内存)。例如:

SELECT a.val, b.val, c.val FROM a
JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

所有表都使用同一个 join key(使用 1 次 map/reduce 任务计算)。Reduce 端会缓存 a 表和 b 表的记录,然后每次取得一个 c 表的记录就计算一次 join 结果,类似的还有:

SELECT a.val, b.val, c.val FROM a
JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

这里用了 2 次 map/reduce 任务。第一次缓存 a 表,用 b 表序列化;第二次缓存第一次 map/reduce 任务的结果,然后用 c 表序列化。

HIVE内置函数

非常详尽的资料
Hive2.0函数大全(中文版)
例如：Hive中yyyymmdd和yyyy-mm-dd日期之间的切换，应该是可以直接使用date_format()函数，而不需要像上面那样转两次

substr

hive中常用substr函数截取字符串匹配演示

UDF

关于UDF、UDAF、UDTF的更多信息可参考：Hive的UDF是什么？、学习Hadoop第三十三课（Hive自定义UDF）、hive 的udf 函数使用

基本函数:

SHOW FUNCTIONS;
DESCRIBE FUNCTION <function_name>;

UDTF

UDTF即Built-in Table-Generating Functions 使用这些UDTF函数有一些限制:
1、SELECT里面不能有其它字段,如:

SELECT pageid, explode(adid_list) AS myCol...

2、不能嵌套,如:

SELECT explode(explode(adid_list)) AS myCol... # 不支持

3、不支持GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY ,如:

SELECT explode(adid_list) AS myCol ... GROUP BY myCol

EXPLODE

下面是一个示例:
场景:将数据进行转置,如:

create table test2(mycol array<int>);
insert OVERWRITE table test2 select * from (select array(1,2,3) from a union all select array(7,8,9) from d)c;
hive> select * from test2;
OK
[1,2,3]
[7,8,9]
hive> SELECT explode(myCol) AS myNewCol FROM test2;
OK
1
2
3
7
8
9

抽样

抽样语句允许用戶抽取样品数据而不是整个表的数据来进行查询, 抽样语句只适用于在表创建时使用bucketed on 语句进行分桶的表, 例如:

INSERT OVERWRITE TABLE pv_gender_sum_sample
SELECT pv_gender_sum.*
FROM pv_gender_sum TABLESAMPLE(BUCKET 3 OUT OF 32);

抽取总共32桶中的第三桶
抽样语句的语法如下:
TABLESAMPLE(BUCKET x OUT OF y)
其中, x必须比y小, y必须是在创建表的时候bucket on的数量的因子或者倍数, hive会根据y的大小来决定抽样多少, 比如原本分了32分, 当y=16时, 抽取32/16=2分, 这时TABLESAMPLE(BUCKET 3 OUT OF 16) 就意味着要抽取第3和第16+3=19分的样品.如果y=64, 这要抽取 32/64=1/2份数据, 这时TABLESAMPLE(BUCKET 3 OUT OF 64) 意味着抽取第3份数据的一半来进行.

数组操作

一个字段的数据类型是数组时, 可以通过数组的索引来访问该字段的某个索引值

SELECT pv.friends[2]
FROM page_views pv;

另外还提供了一个函数 size, 可以求出数组的大小

SELECT pv.userid, size (pv.friends)
FROM page_view pv;

Map(关联性数组)操作
map的访问类似于 php中对数组的访问, 直接用key作为索引来访问数组即可.

INSERT OVERWRITE page_views_map
SELECT pv.userid, pv.properties['page type']
FROM page_views pv;

与数组类似, 也提供了一个求大小的函数 size

SELECT size (pv.properties)(/Column/Column?Channel=cloud&Type=hot)

Hive性能优化

首先,我们来看看Hadoop的计算框架特性,在此特性下会衍生哪些问题?

• 数据量大不是问题,数据倾斜是个问题。
• jobs数比较多的作业运行效率相对比较低,比如即使有几百行的表,如果多次关联多次汇总,产生十几个jobs,耗时很⻓。原因是map reduce作业初始化的时间是比较⻓的。
• sum,count,max,min等UDAF,不怕数据倾斜问题,hadoop在map端的汇总合并优化,使数据倾斜不成问题。
• count(distinct ),在数据量大的情况下,效率较低,如果是多count(distinct )效率更低,因为count(distinct)是按group by 字段分组,按distinct字段排序,一般这种分布方式是很倾斜的。举个例子:比如男uv,女uv,像淘宝一天30亿的pv,如果按性别分组,分配2个reduce,每个reduce处理15亿数据。

面对这些问题,我们能有哪些有效的优化手段呢?下面列出一些在工作有效可行的优化手段:

• 好的模型设计事半功倍。
• 解决数据倾斜问题。
• 减少job数。
• 置合理的map reduce的task数,能有效提升性能。(比如,10w+级别的计算,用160个reduce,那是相当的浪费,1个足够)。
• 了解数据分布,自己动手解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化,但算法优化有时不能适应特定业务背景,开发人员了解业务,了解数据,可以通过业务逻辑精确有效的解决数据倾斜问题。
• 数据量较大的情况下,慎用count(distinct),count(distinct)容易产生倾斜问题。
• 对小文件进行合并,是行至有效的提高调度效率的方法,假如所有的作业设置合理的文件数,对云梯的整体调度效率也会产生积极的正向影响。
• 优化时把握整体,单个作业最优不如整体最优。

性能低下的根源

hive性能优化时,把HiveQL当做M/R程序来读,即从M/R的运行⻆度来考虑优化性能,从更底层思考如何优化运算性能,而不仅仅局限于逻辑代码的替换层面。
RAC(Real Application Cluster)真正应用集群就像一辆机动灵活的小货车,响应快;Hadoop就像吞吐量巨大的轮船,启动开销大,如果每次只做小数量的输入输出,利用率将会很低。所以用好Hadoop的首要任务是增大每次任务所搭载的数据量。
Hadoop的核心能力是parition和sort,因而这也是优化的根本。
观察Hadoop处理数据的过程,有几个显著的特征:

• 数据的大规模并不是负载重点,造成运行压力过大是因为运行数据的倾斜。
• jobs数比较多的作业运行效率相对比较低,比如即使有几百行的表,如果多次关联对此汇总,产生几十个jobs,将会需要30分钟以上的时间且大部分时间被用于作业分配,初始化和数据输出。M/R作业初始化的时间是比较耗时间资源的一个部分。
• 在使用SUM,COUNT,MAX,MIN等UDAF函数时,不怕数据倾斜问题,Hadoop在Map端的汇总合并优化过,使数据倾斜不成问题。
• COUNT(DISTINCT)在数据量大的情况下,效率较低,如果多COUNT(DISTINCT)效率更低,因为COUNT(DISTINCT)是按GROUP BY字段分组,按DISTINCT字段排序,一般这种分布式方式是很倾斜的;比如:男UV,女UV,淘宝一天30亿的PV,如果按性别分组,分配2个reduce,每个reduce处理15亿数据。
• 数据倾斜是导致效率大幅降低的主要原因,可以采用多一次 Map/Reduce 的方法, 避免倾斜。

最后得出的结论是:避实就虚,用 job 数的增加,输入量的增加,占用更多存储空间,充分利用空
闲 CPU 等各种方法,分解数据倾斜造成的负担。

配置角度优化

我们知道了性能低下的根源,同样,我们也可以从Hive的配置角度去优化。Hive系统内部已针对不同的查询预设定了优化方法,用戶可以通过调整配置进行控制, 以下举例介绍部分优化的策略以及优化控制选项。

列裁剪

Hive 在读数据的时候,可以只读取查询中所需要用到的列,而忽略其它列。 例如,若有以下查询:

SELECT a,b FROM q WHERE e<10;

在实施此项查询中,Q 表有 5 列(a,b,c,d,e),Hive 只读取查询逻辑中真实需要 的 3 列 a、b、e,而忽略列 c,d;这样做节省了读取开销,中间表存储开销和数据整合开销。
裁剪所对应的参数项为:hive.optimize.cp=true(默认值为真)

分区裁剪

可以在查询的过程中减少不必要的分区。 例如,若有以下查询:

SELECT * FROM (SELECTT a1,COUNT(1) FROM T GROUP BY a1) subq WHERE subq.prtn=100; #(多余分区)
SELECT * FROM T1 JOIN (SELECT * FROM T2) subq ON (T1.a1=subq.a2) WHERE subq.prtn=100;

查询语句若将“subq.prtn=100”条件放入子查询中更为高效,可以减少读入的分区 数目，传统数据库也是一样的。 Hive 自动执行这种裁剪优化。
分区参数为:hive.optimize.pruner=true(默认值为真)

join操作

在编写带有 join 操作的代码语句时,应该将条目少的表/子查询放在 Join 操作符的左边。 因为在 Reduce 阶段,位于 Join 操作符左边的表的内容会被加载进内存,载入条目较少的表 可以有效减少 OOM(out of memory)即内存溢出。所以对于同一个 key 来说,对应的 value 值小的放前,大的放后,这便是“小表放前”原则。 若一条语句中有多个 Join,依据 Join 的条件相同与否,有不同的处理方法。
join原则
在使用写有 Join 操作的查询语句时有一条原则:应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段,位于 Join 操作符左边的表的内容会被加载进内存,将条目少的表放在左边,可以有效减少发生 OOM 错误的几率。对于一条语句中有多个 Join 的情况,如果 Join 的条
件相同,比如查询:

INSERT OVERWRITE TABLE pv_users
SELECT pv.pageid, u.age FROM page_view p
JOIN user u ON (pv.userid = u.userid)
JOIN newuser x ON (u.userid = x.userid);

• 如果 Join 的 key 相同,不管有多少个表,都会则会合并为一个 Map-Reduce
• 一个 Map-Reduce 任务,而不是 ‘n’ 个
• 在做 OUTER JOIN 的时候也是一样

如果 Join 的条件不相同,比如:

INSERT OVERWRITE TABLE pv_users
SELECT pv.pageid, u.age FROM page_view p
JOIN user u ON (pv.userid = u.userid)
JOIN newuser x on (u.age = x.age);

Map-Reduce 的任务数目和 Join 操作的数目是对应的,上述查询和以下查询是等价的:

INSERT OVERWRITE TABLE tmptable
SELECT * FROM page_view p JOIN user u
ON (pv.userid = u.userid);
INSERT OVERWRITE TABLE pv_users
SELECT x.pageid, x.age FROM tmptable x
JOIN newuser y ON (x.age = y.age);

MAP JOIN操作

Join 操作在 Map 阶段完成,不再需要Reduce,前提条件是需要的数据在 Map 的过程中可以访问到。比如查询:

INSERT OVERWRITE TABLE pv_users
SELECT /*+ MAPJOIN(pv) */ pv.pageid, u.age
FROM page_view pv
JOIN user u ON (pv.userid = u.userid);

可以在 Map 阶段完成 Join,如图所示:

相关的参数为:

• hive.join.emit.interval = 1000
• hive.mapjoin.size.key = 10000
• hive.mapjoin.cache.numrows = 10000

GROUP BY操作

进行GROUP BY操作时需要注意一下几点:

• Map端部分聚合

事实上并不是所有的聚合操作都需要在reduce部分进行,很多聚合操作都可以先在Map端进行部分聚合,然后reduce端得出最终结果。
这里需要修改的参数为:
hive.map.aggr=true(用于设定是否在 map 端进行聚合,默认值为真) hive.groupby.mapaggr.checkinterval=100000(用于设定 map 端进行聚合操作的条目数)

• 有数据倾斜时进行负载均衡

此处需要设定 hive.groupby.skewindata,当选项设定为 true 是,生成的查询计划有两 个 MapReduce 任务。在第一个 MapReduce 中,map 的输出结果集合会随机分布到 reduce 中, 每个 reduce 做部分聚合操作,并输出结果。这样处理的结果是,相同的 Group By Key 有可 能分发到不同的 reduce中,从而达到负载均衡的目的;第二个 MapReduce 任务再根据预处 理的数据结果按照 Group By Key分布到 reduce 中(这个过程可以保证相同的 Group By Key 分布到同一个 reduce 中),最后完成最终的聚合操作。

合并小文件

Hive小文件合并汇总

我们知道文件数目小,容易在文件存储端造成瓶颈,给 HDFS 带来压力,影响处理效率。对此,可以通过合并Map和Reduce的结果文件来消除这样的影响。
用于设置合并属性的参数有:

• 是否合并Map输出文件:hive.merge.mapfiles=true(默认值为真)
• 是否合并Reduce 端输出文件:hive.merge.mapredfiles=false(默认值为假)
• 合并文件的大小:hive.merge.size.per.task=25610001000(默认值为 256000000)

程序角度优化

熟练使用SQL提高查询

熟练地使用 SQL,能写出高效率的查询语句。
场景:有一张 user 表,为卖家每天收到表,user_id,ds(日期)为 key,属性有主营类目,指标有交易金额,交易笔数。每天要取前10天的总收入,总笔数,和最近一天的主营类目。
解决方法1：
如下所示:常用方法

INSERT OVERWRITE TABLE t1
SELECT user_id,substr(MAX(CONCAT(ds,cat),9) AS main_cat) FROM users
WHERE ds=20120329 // 20120329 为日期列的值,实际代码中可以用函数表示出当天日期 GROUP BY user_id;

INSERT OVERWRITE TABLE t2
SELECT user_id,sum(qty) AS qty,SUM(amt) AS amt FROM users
WHERE ds BETWEEN 20120301 AND 20120329
GROUP BY user_id

SELECT t1.user_id,t1.main_cat,t2.qty,t2.amt FROM t1
JOIN t2 ON t1.user_id=t2.user_id

下面给出方法1的思路,实现步骤如下:
第一步:利用分析函数,取每个 user_id 最近一天的主营类目,存入临时表 t1。
第二步:汇总 10 天的总交易金额,交易笔数,存入临时表 t2。
第三步:关联 t1,t2,得到最终的结果。
解决方法2：
如下所示:优化方法

SELECT user_id,substr(MAX(CONCAT(ds,cat)),9) AS main_cat,SUM(qty),SUM(amt) FROM users
WHERE ds BETWEEN 20120301 AND 20120329
GROUP BY user_id

在工作中我们总结出:方案 2 的开销等于方案 1 的第二步的开销,性能提升,由原有的 25 分钟完成,缩短为 10 分钟以内完成。节省了两个临时表的读写是一个关键原因,这种方式也适用于 Oracle 中的数据查找工作。(mysql中这样很多天的数据，按人员group by的时候，像sum()这类函数得到的是所以天的相关数据和，但如果里面按天的字段，则只会取出其中某一天数据，而且的排在最上面的那一天的数据，似乎hive中也是这样的结果。所以对于取的是哪一天一定要注意，避免取到不是自己想要的数据)
SQL 具有普适性,很多 SQL 通用的优化方案在 Hadoop 分布式计算方式中也可以达到效果。

无效ID在关联时的数据倾斜问题

问题:日志中常会出现信息丢失,比如每日约为 20 亿的全网日志,其中的 user_id 为主 键,在日志收集过程中会丢失,出现主键为 null 的情况,如果取其中的 user_id 和 bmw_users 关联,就会碰到数据倾斜的问题。原因是 Hive 中,主键为 null 值的项会被当做相同的 Key 而分配进同一个计算 Map。
解决方法 1:user_id 为空的不参与关联,子查询过滤 null

SELECT * FROM log a
JOIN bmw_users b ON a.user_id IS NOT NULL AND a.user_id=b.user_id
UNION All SELECT * FROM log a WHERE a.user_id IS NULL

解决方法 2:如下所示:函数过滤 null

SELECT * FROM log a LEFT OUTER
JOIN bmw_users b ON
CASE WHEN a.user_id IS NULL THEN CONCAT(‘dp_hive’,RAND()) ELSE a.user_id END =b.user_id;

调优结果:原先由于数据倾斜导致运行时⻓超过 1 小时,解决方法 1 运行每日平均时⻓ 25 分钟,解决方法 2 运行的每日平均时⻓在 20 分钟左右。优化效果很明显。
我们在工作中总结出:解决方法2比解决方法1效果更好,不但IO少了,而且作业数也少了。解决方法1中log读取两次,job 数为2。解决方法2中 job 数是1。这个优化适合无效 id(比如-99、 ‘’,null 等)产生的倾斜问题。把空值的 key 变成一个字符串加上随机数,就能把倾斜的数据分到不同的Reduce上,从而解决数据倾斜问题。因为空值不参与关联,即使分到不同 的 Reduce 上,也不会影响最终的结果。附上 Hadoop 通用关联的实现方法是:关联通过二次排序实现的,关联的列为 partion key,关联的列和表的 tag 组成排序的 group key,根据 pariton key分配Reduce。同一Reduce内根据group key排序。

不同数据类型关联产生的数据倾斜问题

问题:不同数据类型 id 的关联会产生数据倾斜问题。
一张表 s8 的日志,每个商品一条记录,要和商品表关联。但关联却碰到倾斜的问题。 s8 的日志中有 32 为字符串商品 id,也有数值商品 id,日志中类型是 string 的,但商品中的 数值 id 是 bigint 的。
猜想问题的原因是把 s8 的商品 id 转成数值 id 做 hash 来分配 Reduce, 所以字符串 id 的 s8 日志,都到一个 Reduce 上了,解决的方法验证了这个猜测。
解决方法:把数据类型转换成字符串类型

SELECT * FROM s8_log a LEFT OUTER
JOIN r_auction_auctions b ON a.auction_id=CASE(b.auction_id AS STRING)

调优结果显示:数据表处理由 1 小时 30 分钟经代码调整后可以在 20 分钟内完成。

利用Hive对Union ALL 优化的特性

多表 union all 会优化成一个 job。
问题:比如推广效果表要和商品表关联,效果表中的 auction_id 列既有 32 位字符串商 品 id,也有数字 id,和商品表关联得到商品的信息。
解决方法:Hive SQL 性能会比较好

SELECT * FROM effect a
JOIN
(SELECT auction_id AS auction_id FROM auctions
UNION All
SELECT auction_string_id AS auction_id FROM auctions) b
ON a.auction_id=b.auction_id

比分别过滤数字 id,字符串 id 然后分别和商品表关联性能要好。
这样写的好处:1 个 MapReduce 作业,商品表只读一次,推广效果表只读取一次。把 这个 SQL 换成 Map/Reduce 代码的话,Map 的时候,把 a 表的记录打上标签 a,商品表记录 每读取一条,打上标签 b,变成两个<key,value>对,<(b,数字 id),value>,<(b,字符串 id),value>。所以商品表的HDFS 读取只会是一次。

解决Hive对UNION ALL优化的短板

Hive 对 union all 的优化的特性:对 union all 优化只局限于非嵌套查询。

• 消灭子查询内的 group by

示例 1:子查询内有 group by

SELECT * FROM
(SELECT * FROM t1 GROUP BY c1,c2,c3 UNION ALL SELECT * FROM t2 GROUP BY c1,c2,c3)t3
GROUP BY c1,c2,c3

从业务逻辑上说,子查询内的 GROUP BY 怎么都看显得多余(功能上的多余,除非有 COUNT(DISTINCT)),如果不是因为 Hive Bug 或者性能上的考量(曾经出现如果不执行子查询 GROUP BY,数据得不到正确的结果的 Hive Bug)。所以这个 Hive 按经验转换成如下所示:

SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)t3 GROUP BY c1,c2,c3

调优结果:经过测试,并未出现 union all 的 Hive Bug,数据是一致的。MapReduce 的 作业数由3 减少到 1。
t1 相当于一个目录,t2 相当于一个目录,对 Map/Reduce 程序来说,t1,t2 可以作为 Map/Reduce 作业的 mutli inputs。这可以通过一个Map/Reduce 来解决这个问题。Hadoop 的 计算框架,不怕数据多,就怕作业数多。
但如果换成是其他计算平台如 Oracle,那就不一定了,因为把大的输入拆成两个输入, 分别排序汇总后 merge(假如两个子排序是并行的话),是有可能性能更优的(比如希尔排 序比冒泡排序的性能更优)。

• 消灭子查询内的 COUNT(DISTINCT),MAX,MIN。

SELECT * FROM
(SELECT * FROM t1
UNION ALL SELECT c1,c2,c3 COUNT(DISTINCT c4) FROM t2 GROUP BY c1,c2,c3) t3
GROUP BY c1,c2,c3;

由于子查询里头有 COUNT(DISTINCT)操作,直接去 GROUP BY 将达不到业务目标。这时采用 临时表消灭 COUNT(DISTINCT)作业不但能解决倾斜问题,还能有效减少 jobs。

INSERT t4 SELECT c1,c2,c3,c4 FROM t2 GROUP BY c1,c2,c3;
SELECT c1,c2,c3,SUM(income),SUM(uv) FROM
(SELECT c1,c2,c3,income,0 AS uv FROM t1
UNION ALL
SELECT c1,c2,c3,0 AS income,1 AS uv FROM t2) t3
GROUP BY c1,c2,c3;

job 数是 2,减少一半,而且两次 Map/Reduce 比 COUNT(DISTINCT)效率更高。
调优结果:千万级别的类目表,member 表,与 10 亿级得商品表关联。原先 1963s 的任务经过调整,1152s 即完成。

• 消灭子查询内的 JOIN

SELECT * FROM
(SELECT * FROM t1 UNION ALL SELECT * FROM t4 UNION ALL SELECT * FROM t2 JOIN t3 ON t2.id=t3.id) x
GROUP BY c1,c2;

上面代码运行会有 5 个 jobs。加入先 JOIN 生存临时表的话 t5,然后 UNION ALL,会变成 2 个 jobs。

INSERT OVERWRITE TABLE t5
SELECT * FROM t2 JOIN t3 ON t2.id=t3.id;
SELECT * FROM (t1 UNION ALL t4 UNION ALL t5);

调优结果显示:针对千万级别的广告位表,由原先 5 个 Job 共 15 分钟,分解为 2 个 job 一个 8-10 分钟,一个3分钟。

GROUP BY替代COUNT(DISTINCT)达到优化效果

计算 uv 的时候,经常会用到 COUNT(DISTINCT),但在数据比较倾斜的时候 COUNT(DISTINCT) 会比较慢。这时可以尝试用 GROUP BY 改写代码计算 uv。
原有代码

INSERT OVERWRITE TABLE s_dw_tanx_adzone_uv PARTITION (ds=20120329)
SELECT 20120329 AS thedate,adzoneid,COUNT(DISTINCT acookie) AS uv FROM s_ods_log_tanx_pv t WHERE t.ds=2
0120329 GROUP BY adzoneid

关于COUNT(DISTINCT)的数据倾斜问题不能一概而论,要依情况而定,下面是我测试的一组数据:
测试数据:169857条

#统计每日IP
CREATE TABLE ip_2014_12_29 AS SELECT COUNT(DISTINCT ip) AS IP FROM logdfs WHERE logdate='2014_12_29';
耗时:24.805 seconds
#统计每日IP(改造)
CREATE TABLE ip_2014_12_29 AS SELECT COUNT(1) AS IP FROM (SELECT DISTINCT ip from logdfs WHERE logdate
='2014_12_29') tmp;
耗时:46.833 seconds

测试结果表名:明显改造后的语句比之前耗时,这是因为改造后的语句有2个SELECT,多了一个job,这样在数据量小的时候,数据不会存在倾斜问题。

优化总结

优化时,把hive sql当做mapreduce程序来读,会有意想不到的惊喜。理解hadoop的核心能力,是hive优化的根本。这是这一年来,项目组所有成员宝贵的经验总结。

• ⻓期观察hadoop处理数据的过程,有几个显著的特征:

1. 不怕数据多,就怕数据倾斜。
2. 对jobs数比较多的作业运行效率相对比较低,比如即使有几百行的表,如果多次关联多次汇总,产生十几个jobs,没半小时是跑不完的。map reduce作业初始化的时间是比较⻓的。
3. 对sum,count来说,不存在数据倾斜问题。
4. 对count(distinct ),效率较低,数据量一多,准出问题,如果是多count(distinct )效率更低。

• 优化可以从几个方面着手:

1. 好的模型设计事半功倍。
2. 解决数据倾斜问题。
3. 减少job数。
4. 设置合理的map reduce的task数,能有效提升性能。(比如,10w+级别的计算,用160个reduce,那是相当的浪费,1个足够)。
5. 自己动手写sql解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化,但算法优化总是漠视业务,习惯性提供通用的解决方法。 Etl开发人员更了解业务,更了解数据,所以通过业务逻辑解决倾斜的方法往往更精确,更有效。
6. 对count(distinct)采取漠视的方法,尤其数据大的时候很容易产生倾斜问题,不抱侥幸心理。自己动手,丰衣足⻝。
7. 对小文件进行合并,是行至有效的提高调度效率的方法,假如我们的作业设置合理的文件数,对云梯的整体调度效率也会产生积极的影响。

优化时把握整体,单个作业最优不如整体最优。

常用优化手段

主要由三个属性来决定：

• hive.exec.reducers.bytes.per.reducer #这个参数控制一个job会有多少个reducer来处理,依据的是输入文件的总大小。默认1GB。
• hive.exec.reducers.max #这个参数控制最大的reducer的数量, 如果 input / bytes per reduce> max 则会启动这个参数所指定的reduce个数。 这个并不会影响mapre.reduce.tasks参数的设置。默认的max是999。
• mapred.reduce.tasks #这个参数如果指定了,hive就不会用它的estimation函数来自动计算reduce的个数,而是用这个参数来启动reducer。默认是-1。

参数设置的影响

如果reduce太少:如果数据量很大,会导致这个reduce异常的慢,从而导致这个任务不能结束,也有可能会OOM 2、如果reduce太多: 产生的小文件太多,合并起来代价太高,namenode的内存占用也会增大。如果我们不指定mapred.reduce.tasks, hive会自动计算需要多少个reducer。

HIVE 中实现增量更新数据

根据如何每日增量加载数据到Hive分区表和Hive中实现增量更新和hive中的基本操作吞整理。
增量更新主要是考虑到一旦源数据更新了，与之关联的所有下游表和下游表的下游表也要增量更新：
比如为了数量入库的方便和统一，我们从kafka接收数据不会做什么处理，而是直接用接收数据的日期来在hive表里做一个简单的分区，假如这个表为table1；但这种分区与表中各条记录的业务时间是不一致的，table1中的分区时间没有太大意义；这个时候我们可能会基于table1来进一步整理，从table1中提取相应业务时间来进一步分区，比如按天分区，甚至进一步再按小时分区，创建table2；我们也可能会对table1进行一定的ETL操作，比如按天统计某一指标，并创建table3；我们可以利用定时任务来每天维护table2和table3的更新，由于table1中的数据量一般很大，我们不可能每天去全量扫描table1，更经济的做法是对table2 和table3进行增量更新；
但是增量更新有一些要注意的细节，比如我们在5/22日凌晨更新table2 和table3中5/21日的数据，table1中分区标识为5/20日的数据我们都已经处理过了，这个时候我们只要读取table1中分区标识为5/21日的数据，但是table1中分区标识为5/21日的数据中可能夹着实际业务时间为5/21日之前的数据（始终要记住table1中的分区时间是数据入库时间），这个时候我们就要从table1中分区标识为5/21日的数据中最早的业务时间开始，来更新table2 和table3中的数据。
为了达到这个目的，

• 首先我们要记录table2 和table3上一次更新时的游标，即更新到了table1中分区标识的哪个时间点，假如 为time1；
• 然后我们可以记录table2 和table3最新的分区时间点假如为time23（当然也可以不记录要用时去hive里查），但直接记录下来，更新时会更快更方便；
• 读取table1中time1之后的数据，假如为data1，并解析这些数据，获得这些记录中最早的业务时间，假如为timePre
• 假如timePre大于time23，则table2 和table3直接产生新的分区，并将data1的数据直接填入；如果timePre小于等于time23，则需要读取table2 和table3中timePre之后的数据，假如为data23；
  *删除table2 和table3中timePre之后的分区; 重新增加新的分区至最新时间点； 对data23和data1进行整合，将整合后的数据按照时间，填入各新增分区中。

下面是在mysql数据库记录的与增量更新相关的hive表上下游关系和更新的时间记录点，表中utime可以看作是源表中的最近时间，position 可以看作是下游表中的时间

+--------------------------+--------------------------+----------+---------------------+
| downstream               | upstream                 | position | utime               |
+--------------------------+--------------------------+----------+---------------------+
| databaseA.tableB| databaseA.tableA| 20180521 | 2018-05-22 00:32:21 |
| databaseA.tableC| databaseA.tableB| 20180521 | 2018-05-22 00:33:35 |
+--------------------------+--------------------------+----------+---------------------+

HIVE 数据类型转换

hive中的数据类型：基本数据类型，集合数据类型
HIVE 数据类型转换

hive 中时间戳与时间字符串的相互转换

unix_timestamp()
unix_timestamp(string date)
unix_timestamp(string date, string format)
from_unixtime(int/bigint timestamp)
from_unixtime(int/bigint timestamp, string format)
有时候，存放的时间戳不是秒数，而是毫秒数，因此转换前需要除以1000。

Hive的实操举例

• 进入HIVE之前要把HADOOP给启动起来，因为HIVE是基于HADOOP的。所有的MR计算都是在HADOOP上面进行的；

• 在命令行中输入：hive。这个时候就可以顺利的进入HIVE了。当然了，如果你想直接执行HQL脚本文件可以这样：hive -f xxxxx.hql；

• 进入hive之后一一般默认的数据库都是default。如果你切换数据库的话所建的表都会是在default数据库里面；

• 创建数据库的语法是：create database database_name;非常简单的，其实hive跟mysql的语法还是比较相似的；

• 查看所有数据库的时候可以输入：show databases，或者更详细一点SHOW (DATABASES|SCHEMAS) [LIKE identifier_with_wildcards];
  切换数据库的时候可以输入：use database_name；
  查看所有表的时候可以输入：show tables;

• 看表结构的时候可以输入：describe tab_name;
  describe formatted tab_name;则可以查看详细信息，包括表对应在hdfs上存储的位置

• 查询数据：如select * from bit_water_meter limit 10;

group by 比较费时
group by 似乎前面只能select 这个group by 的变量，不能加入其他变量；

将查询数据写入文件

insert overwrite local directory '/tmp/downloads/aaa' select * from abc;#没有local则写入hdfs对应的文件中

写完之后，可用exit即出hive;
如果是写入hdfs可以用如下的命令来查看文件

hadoop fs -ls /tmp/downloads/aaa#对就当时存储的位置

查看全部文件内容，即查看查询出来的表格内容

hadoop fs -cat /tmp/downloads/aaa/*

创建一个新表，然后再储存在本地文件中的表格加载到hive表里

create table cd.abc like cd.bit;#照船cd.bit表的数据结构，省得设置
load data inpath '/tmp/downloads/aaa' into tabels cd.abc;

要删除这个表格可用

drop b

hive 查询 字符串处理
从字符串 url 的 某个下标开始截取 len 的长度

select substr(url,63,19)  from table_name; 

使用正则表达式提取字符串中的特定字段

select regexp_extract(url,"news_(.*?)\\/http", 1)  from table_name; 

字符串长度函数：length

    语法: length(string A)  
    返回值: int  
    说明：返回字符串A的长度  
    举例：  
    hive> select length(‘abcedfg’) from dual;  
    7  

字符串反转函数：reverse

    语法: reverse(string A)  
    返回值: string  
    说明：返回字符串A的反转结果  
    举例：  
    hive> select reverse(‘abcedfg’) from dual;  
    gfdecba

更详细的hive常用字符串函数可查看

hive 日期处理处理
详见网页
日期转年函数: year()、日期转月函数: month()、日期转天函数: day()、日期转小时函数: hour()、日期转分钟函数: minute()等

hive中计算均值、方差、标准差、四分位数等
1.方差公式：
m为x1,x2…xn数列的期望值（平均数）
s^2 = [(x1-m)^2 + (x2-m)^2 + … (xn-m)^2]/n
s即为标准差
s^2为方差。

Hive> select * from dim_row_num limit 10;

结果如下：

OK
0
1
2
3
4
5
6
7
8
9

hive> select stddev(row_num) from dim_row_num where row_num<10; 

结果如下：

 2.8722813232690143 

另外：
stddev_pop = stddev

stddev_samp :
s^2 = [(x1-m)^2 + (x2-m)^2 + … (xn-m)^2]/n-1
标准差：
stddev=2.8722813232690143
stddev_samp=3.0276503540974917
stddev_pop=2.8722813232690143

方差：
var_samp=stddev_samp^2=9.166666666666666
var_pop=stddev_pop^2=8.25

更多Hive内置函数，网址；

hive 保存文件
HIVE-执行hive的几种方式，和把HIVE保存到本地的几种方式：
第一种，在bash中直接通过hive -e命令，并用 > 输出流把执行结果输出到制定文件

hive -e "select * from student where sex = '男'" > /tmp/output.txt

第二种，在bash中直接通过hive -f命令，执行文件中一条或者多条sql语句。并用 > 输出流把执行结果输出到制定文件

hive -f exer.sql  > /tmp/output.txt

文件内容

select * from student where sex = '男';
select count(*) from student;

Hive命令行及参数配置
可以配置hive运行时使用的资源
第三种，在hive中输入hive-sql语句，通过使用INSERT OVERWRITE LOCAL DIRECTORY结果到本地系统和HDFS文件系统。
语法一致，只是路径不同

insert overwrite local directory "/tmp/out"
 > select cno,avg(grade) from sc group by(cno);

insert overwrite directory 'hdfs://server71:9000/user/hive/warehouse/mystudent'
select * from student1;

以上是三种，包含了3执行hive-sql的方法。结果保存到本地的方法前两种都属于linxu BASH自带的方法。第三种才是HIVE本身的导出数据的方法。
第四种，就是基本的SQL语法，从一个表格中抽取数据，直接插入另外一个表格。参考SQL语法即可。

insert overwrite table student3 
select sno,sname,sex,sage,sdept from student3 where year='1996';

hive性能分析：
这是一条hql查询过程

Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 56
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1505994645742_1112, Tracking URL = http://master:8088/proxy/application_1505994645742_1112/
Kill Command = /data/..../hadoop job  -kill job_1505994645742_1112
Hadoop job information for Stage-1: number of mappers: 49; number of reducers: 56
2018-01-22 10:58:05,600 Stage-1 map = 0%,  reduce = 0%
2018-01-22 10:58:24,783 Stage-1 map = 1%,  reduce = 0%, Cumulative CPU 50.39 sec
2018-01-22 10:58:25,861 Stage-1 map = 2%,  reduce = 0%, Cumulative CPU 66.1 sec
2018-01-22 10:58:27,004 Stage-1 map = 3%,  reduce = 0%, Cumulative CPU 97.56 sec
2018-01-22 10:58:29,970 Stage-1 map = 8%,  reduce = 0%, Cumulative CPU 177.95 sec
2018-01-22 10:58:31,061 Stage-1 map = 12%,  reduce = 0%, Cumulative CPU 247.16 sec
2018-01-22 10:58:32,113 Stage-1 map = 17%,  reduce = 0%, Cumulative CPU 259.95 sec
2018-01-22 10:58:33,163 Stage-1 map = 21%,  reduce = 0%, Cumulative CPU 307.56 sec
2018-01-22 10:58:34,259 Stage-1 map = 24%,  reduce = 0%, Cumulative CPU 320.73 sec
2018-01-22 10:58:36,479 Stage-1 map = 27%,  reduce = 0%, Cumulative CPU 393.29 sec
2018-01-22 10:58:37,531 Stage-1 map = 29%,  reduce = 0%, Cumulative CPU 401.42 sec
2018-01-22 10:58:39,696 Stage-1 map = 34%,  reduce = 0%, Cumulative CPU 413.57 sec
2018-01-22 10:58:40,793 Stage-1 map = 36%,  reduce = 0%, Cumulative CPU 421.85 sec
2018-01-22 10:58:41,905 Stage-1 map = 38%,  reduce = 0%, Cumulative CPU 428.51 sec
2018-01-22 10:58:43,015 Stage-1 map = 41%,  reduce = 0%, Cumulative CPU 443.39 sec
2018-01-22 10:58:44,135 Stage-1 map = 43%,  reduce = 0%, Cumulative CPU 452.78 sec
2018-01-22 10:58:45,235 Stage-1 map = 46%,  reduce = 0%, Cumulative CPU 457.05 sec
2018-01-22 10:58:46,356 Stage-1 map = 49%,  reduce = 1%, Cumulative CPU 472.06 sec
2018-01-22 10:58:47,517 Stage-1 map = 53%,  reduce = 1%, Cumulative CPU 480.97 sec
2018-01-22 10:58:48,588 Stage-1 map = 58%,  reduce = 1%, Cumulative CPU 491.03 sec
2018-01-22 10:58:49,632 Stage-1 map = 62%,  reduce = 2%, Cumulative CPU 501.73 sec
2018-01-22 10:58:50,721 Stage-1 map = 67%,  reduce = 3%, Cumulative CPU 512.19 sec
2018-01-22 10:58:51,799 Stage-1 map = 70%,  reduce = 4%, Cumulative CPU 517.64 sec
2018-01-22 10:58:52,840 Stage-1 map = 78%,  reduce = 5%, Cumulative CPU 531.52 sec
2018-01-22 10:58:53,893 Stage-1 map = 80%,  reduce = 7%, Cumulative CPU 535.47 sec
2018-01-22 10:58:54,943 Stage-1 map = 84%,  reduce = 8%, Cumulative CPU 542.45 sec
2018-01-22 10:58:55,986 Stage-1 map = 86%,  reduce = 10%, Cumulative CPU 544.13 sec
2018-01-22 10:58:57,027 Stage-1 map = 86%,  reduce = 12%, Cumulative CPU 546.06 sec
2018-01-22 10:58:58,068 Stage-1 map = 86%,  reduce = 13%, Cumulative CPU 547.29 sec
2018-01-22 10:59:00,164 Stage-1 map = 86%,  reduce = 15%, Cumulative CPU 552.94 sec
2018-01-22 10:59:01,204 Stage-1 map = 86%,  reduce = 17%, Cumulative CPU 555.59 sec
2018-01-22 10:59:04,327 Stage-1 map = 86%,  reduce = 18%, Cumulative CPU 562.6 sec
2018-01-22 10:59:08,514 Stage-1 map = 86%,  reduce = 19%, Cumulative CPU 571.11 sec
2018-01-22 10:59:47,953 Stage-1 map = 88%,  reduce = 19%, Cumulative CPU 610.87 sec
2018-01-22 10:59:48,988 Stage-1 map = 88%,  reduce = 20%, Cumulative CPU 612.09 sec
2018-01-22 11:00:05,596 Stage-1 map = 89%,  reduce = 20%, Cumulative CPU 646.92 sec
2018-01-22 11:00:12,847 Stage-1 map = 92%,  reduce = 20%, Cumulative CPU 656.95 sec
2018-01-22 11:00:14,915 Stage-1 map = 92%,  reduce = 21%, Cumulative CPU 658.65 sec
2018-01-22 11:00:21,138 Stage-1 map = 93%,  reduce = 21%, Cumulative CPU 671.41 sec
2018-01-22 11:00:22,205 Stage-1 map = 94%,  reduce = 21%, Cumulative CPU 673.87 sec
2018-01-22 11:00:49,137 Stage-1 map = 95%,  reduce = 21%, Cumulative CPU 698.59 sec
2018-01-22 11:00:51,212 Stage-1 map = 96%,  reduce = 21%, Cumulative CPU 701.22 sec
2018-01-22 11:00:53,270 Stage-1 map = 96%,  reduce = 22%, Cumulative CPU 702.69 sec
2018-01-22 11:01:54,232 Stage-1 map = 96%,  reduce = 22%, Cumulative CPU 732.84 sec
2018-01-22 11:02:55,039 Stage-1 map = 96%,  reduce = 22%, Cumulative CPU 755.55 sec
2018-01-22 11:03:55,864 Stage-1 map = 96%,  reduce = 22%, Cumulative CPU 777.63 sec
2018-01-22 11:04:56,656 Stage-1 map = 96%,  reduce = 22%, Cumulative CPU 801.06 sec
2018-01-22 11:05:57,438 Stage-1 map = 96%,  reduce = 22%, Cumulative CPU 823.85 sec
2018-01-22 11:06:58,204 Stage-1 map = 96%,  reduce = 22%, Cumulative CPU 855.53 sec
2018-01-22 11:07:58,974 Stage-1 map = 96%,  reduce = 22%, Cumulative CPU 882.34 sec
2018-01-22 11:08:34,999 Stage-1 map = 100%,  reduce = 23%, Cumulative CPU 895.88 sec
2018-01-22 11:09:04,811 Stage-1 map = 100%,  reduce = 54%, Cumulative CPU 905.99 sec
2018-01-22 11:09:34,657 Stage-1 map = 98%,  reduce = 54%, Cumulative CPU 916.4 sec
2018-01-22 11:10:04,485 Stage-1 map = 98%,  reduce = 43%, Cumulative CPU 926.87 sec
2018-01-22 11:10:11,676 Stage-1 map = 98%,  reduce = 40%, Cumulative CPU 929.43 sec
2018-01-22 11:10:34,299 Stage-1 map = 100%,  reduce = 40%, Cumulative CPU 937.37 sec
2018-01-22 11:10:35,336 Stage-1 map = 100%,  reduce = 38%, Cumulative CPU 937.73 sec
2018-01-22 11:11:05,203 Stage-1 map = 100%,  reduce = 29%, Cumulative CPU 948.47 sec
2018-01-22 11:11:06,232 Stage-1 map = 100%,  reduce = 27%, Cumulative CPU 948.86 sec
2018-01-22 11:11:12,533 Stage-1 map = 100%,  reduce = 22%, Cumulative CPU 951.19 sec
2018-01-22 11:11:15,662 Stage-1 map = 100%,  reduce = 23%, Cumulative CPU 953.79 sec
2018-01-22 11:11:17,723 Stage-1 map = 100%,  reduce = 24%, Cumulative CPU 956.08 sec
2018-01-22 11:11:19,787 Stage-1 map = 100%,  reduce = 25%, Cumulative CPU 960.61 sec
2018-01-22 11:11:20,817 Stage-1 map = 100%,  reduce = 27%, Cumulative CPU 963.26 sec
2018-01-22 11:11:21,847 Stage-1 map = 100%,  reduce = 28%, Cumulative CPU 966.12 sec
2018-01-22 11:11:23,906 Stage-1 map = 100%,  reduce = 30%, Cumulative CPU 971.06 sec
2018-01-22 11:11:25,961 Stage-1 map = 100%,  reduce = 31%, Cumulative CPU 976.82 sec
2018-01-22 11:11:29,058 Stage-1 map = 100%,  reduce = 32%, Cumulative CPU 990.27 sec
2018-01-22 11:11:30,087 Stage-1 map = 100%,  reduce = 33%, Cumulative CPU 992.0 sec
2018-01-22 11:11:35,231 Stage-1 map = 98%,  reduce = 33%, Cumulative CPU 995.82 sec
2018-01-22 11:12:35,422 Stage-1 map = 98%,  reduce = 33%, Cumulative CPU 1055.77 sec
2018-01-22 11:12:48,813 Stage-1 map = 100%,  reduce = 36%, Cumulative CPU 1071.46 sec
2018-01-22 11:12:49,869 Stage-1 map = 100%,  reduce = 47%, Cumulative CPU 1086.72 sec
2018-01-22 11:12:50,996 Stage-1 map = 100%,  reduce = 74%, Cumulative CPU 1129.75 sec
2018-01-22 11:12:52,031 Stage-1 map = 100%,  reduce = 93%, Cumulative CPU 1166.76 sec
2018-01-22 11:12:53,062 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1183.59 sec
MapReduce Total cumulative CPU time: 19 minutes 43 seconds 590 msec
Ended Job = job_1505994645742_1112
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 49  Reduce: 56   Cumulative CPU: 1190.72 sec   HDFS Read: 14127859647 HDFS Write: 5760 SUCCESS
Total MapReduce CPU Time Spent: 19 minutes 50 seconds 720 msec

从上可以看到job数目，reducer数目，设置reducer数目的方法。实现的maper和reducer数目。各阶段的时间，整个查询的时间。可以看到主要是reducer比较费时。

hive问题集锦：
1、hive select * 没问题，select count(*) 就报错。
为“select ”时没有使用到mapreduce，只是直接将hdfs的内容进行反馈，当count()时开始使用mapreduce就报错了，说明hive里hadoop的配置有问题，或者hadoop客户端本身的配置有问题。

利用dbeaver连接hive并查询

通过数据库客户端界面工具DBeaver连接Hive
亲测有用
填入主机，端口等信息，用户名和密码可以和ranger中的用户一致。
然后是编辑驱动：将原来连网的驱动删掉，将自己集群上的hive库的驱动添加进去，注意查看自己hive的版本（系统可能有两个hive版本）。
将对应路径的驱动复制下来，比如
/usr/hdp/2.6.5.0-292/hive/jdbc/hive-jdbc-1.2.1000.2.6.5.0-292-standalone/jar
其实还有一种spark hive的连接模式，目前还没试

Hive Hadoop 解析 orc 文件

解析 orc 格式 为 json 格式：

./hive --orcfiledump -d  <hdfs-location-of-orc-file>
 把解析的 json 写入 到文件

./hive --orcfiledump -d  <hdfs-location-of-orc-file> > myfile.txt
 注意

<hdfs-location-of-orc-file> 最好精确到 具体的 orc 文件，比如 /data/recsys/negative-feedback/negative_feedback/part-r-00000

hive的orc格式详解

pyton 连接hive库

目前还未真正连通（可能是网络原因）
将数据导入Hive数据库中，使用python链接Hive读取数据库，转化成pandas的dataframe
安装pyhs2报错，装不上，添加装sasl依赖也不行

pip install sasl
pip install thrift
pip install thrift-sasl
pip install pyhive

很有可能sasl安装不上，可到

如果sasl安装不上，可参考评论中https://www.lfd.uci.edu/~gohlke/pythonlibs/#sasl

下载window版本的库

#未验证
from pyhive import hive

conn = hive.connect(host='111.222.333.444',
                    port=10000,
                    username='test',
                    database='test',
                    password='**',
                    auth='CUSTOM')

cursor = conn.cursor()
sql_str = "SELECT * from AAA.BBB WHERE partition_date='2018-11-12' "
cursor.execute(sql_str)
print(cursor.fetchone())