Hive随笔_hive insert into select 无结果

Hive

1、概述

hive是建立在Hadoop基础上的数据仓库基础架构。可以将结构化数据文件映射成一张表，提供类sql查询功能（将hql转成MapReduce任务），对其中的数据进行分析和管理

2、hive的工作方式

将存放在hive中的数据映射成一张表，提供类sql语句的操作方式，Hive SQL实际上是先被SQL解析器解析成抽象语法树AST Tree，然后被Hive框架解析成一个MapReduce可执行的计划交给hadoop集群处理，结果也会输出在hdfs之中。（必须是结构化的数据）。在存储的时候hive对数据不做校验，在读取的时候校验。

3、优点

①Hive极大的简化了分布式的计算程序的编程，允许程序员使用SQL命令来完成数据的分布式计算，计算构建在yarn之上

②支持自定义函数，可根据用户自定义逻辑处理数据

③海量数据的离线处理

4、缺点

不支持增删改

执行MapReduce任务，有比较高的延迟

Hive调优比较困难，粒度较粗

5、架构

1、接口层client

• CLI：shell终端命令行，采用交互式使用hive命令行与hive进行交互，最常用（学习、生成、调试）
• Jdbc/odbc：是hive的基于jdbc操作提供的客户端，用户（开发、运维）通过这个链接hive server服务
• Web UI：通过浏览器访问hive（基本不用）

2、元数据库metastore

• 存储在hive中的数据的信息：表名，表结构，属性，对应HDFS的目录

  • 1、mysql（推荐）

    • 1、支持多用户
    • 2、获取元数据的操作需要立即响应：show tables,desc table_name
    • 3、元数据一般不会很大

  • 2、derby（默认）

    • 不适合多用户操作，并且数据存储目录不固定。数据库跟着hive的进入目录走，极度不方便管理。

3、Driver

• 1、解析器：解释器的作用是将hiveSQL语句转换成抽象语法树AST
• 2、编译器：将语法树编译成为逻辑执行计划MapReduce
• 3、优化器：优化器是对逻辑执行计划进行优化
• 4、执行器：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark

4、hadoop

• hive底层架构，HDFS存储、MR计算、YARN资源调度

存储格式

TEXTFILE

• 默认格式，数据不做压缩，磁盘开销大，数据解析开销大。

SEQUENCEFILE

• 二进制文件,以<key,value>的形式序列化到文件中，SequenceFile是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点

RCFILE

• RCFile是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取

ORCFILE

• ORC是列式存储，有多种文件压缩方式，并且有着很高的压缩比

7、Hive和传统关系型数据库的比较

1、数据存储位置

• hive存储在hdfs上
• 数据库将数据保存在块设备或者本地文件系统中

2、数据更新

• hive不建议对数据进行改写
• 数据库中的数据通常需要进行修改

3、执行延迟

• 数据规模较小时：hive执行延迟较高，数据库执行延迟较低。
• 当数据库规模大到超过数据库的处理能力的时候。hive的并行计算体现巨大优势。

4、数据规模

• 由于 Hive 建立在集群上并可以利用 MapReduce 进行并行计算，因此可以支持很大规模的数据
• 数据库可以支持的数据规模很小

5、数据格式

• Hive 中没有定义专门的数据格式
• 数据库中所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时

6、索引

• Hive 在加载数据的过程中不会对数据进行任何处理。因此访问延迟较高，决定了不适合在线数据查询
• 数据库可以有很高的效率，较低的延迟

8、hive建表

1、使用默认建表语句

create table students
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’; // 必选，指定列分隔符

2、指定location

create table students2
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
LOCATION ‘/input1’;

• 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location

3、指定存储格式

create table students3
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
STORED AS rcfile; // 指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。

• 指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。

4、SQL语句建表

• create table students4 as select * from students2;

5、只建表不加载数据

• create table students5 like students;

9、hive加载数据

1、使用 hdfs dfs -put ‘本地数据’ ‘hive表对应的HDFS目录下’

2、load data语句

hive shell命令行
// 清空表
truncate table students;
// 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除
load data local inpath ‘/usr/local/soft/data/students.txt’ into table students;
// overwrite 覆盖加载
load data local inpath ‘/usr/local/soft/data/students.txt’ overwrite into table students;

// 将HDFS上的/input1目录下面的数据 移动至 students表对应的HDFS目录下，注意是 移动、移动、移动
load data inpath ‘/input1/students.txt’ into table students;

3、create table xxx as SQL语句

4、insert into table xxxx SQL语句 （没有as）

• insert into table students2 select * from students; 将 students表的数据插入到students2 这是复制 不是移动 students表中的表中的数据不会丢失
• insert overwrite table students2 select * from students;覆盖操作

10、sql

1、HQL 执行优先级：

• from、where、 group by 、having、order by、join、select 、limit

2、where 条件里不支持子查询，实际上是支持 in、not in、exists、not exists

3、判断null

• is null
• nvl()函数

4、使用explain查看SQL执行计划

10、内外部表

外部表

• 删除表的元数据，不会删除表中的数据（HDFS上的文件）建表语句加上external
• 外部表还可以将其他数据源中的数据 映射到 hive中，比如说：hbase，ElasticSearch…

内部表

• 删除内部表的时候，表中的数据（HDFS上的文件）会被同表的元数据一起删除

11、分区

作用

• 进行分区裁剪，避免全表扫描，减少MapReduce处理的数据量，提高效率

静态分区

• 建表语句增加
  PARTITIONED BY(dt string)

动态分区

• 根据数据中某几列的不同的取值 划分 不同的分区
• // 分区字段需要放在 select 的最后，如果有多个分区字段 同理，它是按位置匹配，不是按名字匹配
  insert into table students_dt_p partition(dt) select id,name,age,gender,clazz,dt from students_dt;

// 比如下面这条语句会使用age作为分区字段，而不会使用student_dt中的dt作为分区字段
insert into table students_dt_p partition(dt) select id,name,age,gender,dt,age from students_dt;

• 多级分区

  • 多个分区字段。插入数据时，按位置选择最后的几个分区字段建立分区

12、分桶

作用

• 1、进行抽样

• 2、解决数据倾斜

• 3、map-side join

  • 在join是如果对俩表join字段进行了分桶，会将相同列值的桶进行join，大大提高效率

分桶实际上是对文件（数据）的进一步切分，分桶字段需要根据业务进行设定，可以解决数据倾斜问题

（1）获得更高的查询处理效率。分桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

13、五个by

1、order by

• 为了保证全局有序，必须使用一个reduce任务对全局数据进行排序，效率非常低，尽量避免使用

2、group by

• 通常结合聚合函数一起使用

3、sort by

• 会对每一个reduce输出的结果进行排序，只能保证在相同的reduce任务中有序

4、distribute by

• 只是控制map端的输出结果如何分布到Reduce中，并不会对reduce中的数据进行排序 。类似MR中Partition，进行分区，结合sort by使用。

5、cluster by

• Cluster by=distribute by+sort by。当distribute by和sort by字段相同时，可以直接使用clueter by，排序只能是升序排序

14、函数

1、行列转换

• 行转列 lateral view explode

  select name,col1,col2 from testarray2 lateral view explode(map(‘key1’,1,‘key2’,2,‘key3’,3)) t1 as col1,col2;
  在最后加上 lateral view explode

  map：一个一个处理
  explode：展开
  lateral view：侧视图，临时表的感觉
  t1 ：给侧视图取别名
  col1,col2 ：展开的俩个列名，在select中使用

• 列转行 collect_list

  collect_set(col) --将某字段的值进行去重汇总

2、自定义函数

• 1、UDF:一进一出，用UDF函数解析公共字段
• 2、UDTF：一进多出，用UDTF函数解析事件字段
• 3、UDAF：多近一出，聚合函数

3、开窗函数

4、系统函数

• 条件函数

  • if ：if（Boolean，‘true’，‘false’）
  • COALESCE ：从左往右 一次匹配 直到非空为止
  • case when than end

• 日期函数

  • from_unixtime(1610611142,‘YYYY/MM/dd HH:mm:ss’)
  • from_unixtime(unix_timestamp(),‘YYYY/MM/dd HH:mm:ss’)

• 字符串函数

  • concat

  • concat_ws

    • select concat_ws(’#’,‘a’,‘b’,‘c’,NULL); // a#b#c 可以指定分隔符，并且会自动忽略NULL

  • substring

    • HQL中涉及到位置的时候 是从1开始计数

  • split

  • explode

    • 展开

15、优化

1、建表调优

• 设置分区分桶

• 拆分表

  当你需要对一个很大的表做分析的时候，但不是每个字段都需要用到，可以考虑拆分表，生成子表，减少输入的数据量。并且过滤掉无效的数据，或者合并数据，进一步减少分析的数据量

• 选择适当的文件压缩格式

2、查询优化

• 分区裁剪，先过滤再join
• map join 避免因join产生数据倾斜
• 预聚合：开启map端combiner

3、数据倾斜优化

• 见 16、解决数据倾斜的办法

4、作业优化

• 调整mapper和reducer数量

  如果小文件过多，map执行前合并小文件
  Reduce个数并不是越多越好
  （1）过多的启动和初始化Reduce也会消耗时间和资源；
  （2）另外，有多少个Reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
  在设置Reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的Reduce数；使单个Reduce任务处理数据量大小要合适；

• 并行执行

  Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。
  通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。

• Fetch抓取

  Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。

• 本地模式

  大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。
  用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。

• 严格模式

  对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
  对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。
  限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。

• JVM重用

  JVM重用是Hadoop调优参数的内容，其对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短。
  Hadoop的默认配置通常是使用派生JVM来执行map和Reduce任务的。这时JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含有成百上千task任务的情况。JVM重用可以使得JVM实例在同一个job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中进行配置。通常在10-20之间，具体多少需要根据具体业务场景测试得出。
  这个功能的缺点是，开启JVM重用将一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。如果某个“不平衡的”job中有某几个reduce task执行的时间要比其他Reduce task消耗的时间多的多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放。

• 推测执行

  在分布式集群环境下，因为程序Bug（包括Hadoop本身的bug），负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。为了避免这种情况发生，Hadoop采用了推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果

• 压缩

  设置map端输出，中间过程压缩。（减少IO流和网络传输，提高效率）

16、解决数据倾斜方法

1） 过滤掉脏数据：

如果大key是无意义的脏数据，直接过滤掉。如null值等

2）数据预处理：数据做一下预处理，尽量保证join的时候，同一个key对应的记录不要有太多。

3） 增加reduce个数：如果数据中出现了多个大key，增加reduce个数，可以让这些大key落到同一个reduce的概率小很多。

4） 转换为mapjoin：如果两个表join的时候，一个表为小表，可以用mapjoin做。

5） 大key单独处理：将大key和其他key分开处理

17、tez引擎

优点

• Tez可以将多个有依赖的作业转换为一个作业，这样只需要写一次HDFS，且中间节点较少，从而大大提升作业的计算性能。

Mr/tez/spark区别

• Mr引擎：多job串联，基于磁盘，落盘的地方比较多。虽然慢，但一定能跑出结果。一般处理，周、月、年指标。
• Spark引擎：虽然在Shuffle过程中也落盘，但是并不是所有算子都需要Shuffle，尤其是多算子过程，中间过程不落盘 DAG有向无环图。 兼顾了可靠性和效率。一般处理天指标。
• Tez引擎：完全基于内存。 注意：如果数据量特别大，慎重使用。容易OOM(内存溢出)。一般用于快速出结果，数据量比较小的场景。

jdbc

需要启动service hiveserver2

执行流程：hive SQL，通过命令或者客户端提交，经过compiler编译器，运用metastore中的元数据进行类型检测和语法分析，生成一个逻辑方案，然后通过的优化处理，产生一个maptask程序。