解答：
1、默认的存储位置:内存数据库derby，安装小，但是数据存在内存，不稳定 2、mysql数据库，数据存储模式可以自己设置，持久化好，查看方便。 解答： 主要体现在删除时内部表：加载数据到hive所在的hdfs目录
元数据中表的信息会被删除，HDFS中表的目录以及数据也会被删除 适用于中间表、结果表（数据恢复起来比较快的） 外部表(external关键字)：不加载数据到hive所在的hdfs目录
元数据中表的信息会被删除，但是HDFS中表的目录以及数据依旧存在 只是删除了HDFS与Hive的表的关联 适合应用于多张表共用一份数据的情况下，即共享源数据的时候 解答：
1、因为外部表不会加载数据到hive，减少数据传输、数据还能共享。 2、hive不会修改数据，所以无需担心数据的损坏 3、删除表时，只删除表结构、不删除数据。 4、ods层的埋点日志和业务抽取数据可以使用外部表，因为被误删除恢复会比较麻烦（实时采集） 5、抽取过来处理后的数据恢复起来很快，建议使用内部表（中间表&结果表） 解答：
在导入hive的时候，如果数据库中有blob或者text字段，会报错。有个参数limit 解答： 解释:此列在表中并未真正存在 Hive提供了三个虚拟列：
INPUT__FILE__NAME
BLOCK__OFFSET__INSIDE__FILE
当前map任务处理的数据所对应的偏移量,文件中的块内偏移量 ROW__OFFSET__INSIDE__BLOCK
但ROW__OFFSET__INSIDE__BLOCK默认是不可用的，需要设置hive.exec.rowoffset为true才可以。- 可以用来排查有问题的输入数据。
INPUT__FILE__NAME, mapper任务的输出文件名。 BLOCK__OFFSET__INSIDE__FILE, 当前全局文件的偏移量。对于块压缩文件，就是当前块的文件偏移量，即当前块的第一个字节在文件中的偏移量。 hive> SELECT INPUT__FILE__NAME , BLOCK__OFFSET__INSIDE__FILE , line
> FROM hive_text WHERE line LIKE '%hive%' LIMIT 2 ;
har: / / file/ user/ hive/ warehouse/ hive_text/ folder= docs/
data. har/ user/ hive/ warehouse/ hive_text/ folder= docs/ README . txt 2243
har: / / file/ user/ hive/ warehouse/ hive_text/ folder= docs/
data. har/ user/ hive/ warehouse/ hive_text/ folder= docs/ README . txt 3646
文件的行偏移量平时作用不大,但这三个参数在找错误方面很有用 已知clickcube_mid表中有一个字段 regioncode , regioncode 描述了 一个ip对应的region信息，这个regioncode 目前使用的是原始值，为日志中直接获取。 某一天，由于regioncode 异常，导致spark 进程中断，查找得知是 regioncode 不合理导致，此时我们需要找到错误的regioncode, 可以进行如下的查询： SELECT
INPUT__FILE__NAME,
BLOCK__OFFSET__INSIDE__FILE,
ROW__OFFSET__INSIDE__BLOCK,
substr( regioncode, 0 , 20 )
FROM clickcube_mid
WHERE length( regioncode) > 100 ;
功能：将数据按照数据本身分区的规则来存储 本质:实现了底层程序的输入的优化
通过分区，先对分区过滤，然后再作为输入 比先读取所有数据，再过滤，性能要高的多 不做分区表：表的最后一级目录就是表的目录 做了分区表：表的最后一级目录是分区的目录 分区的方式：
如果数据文件是已经按照分区分好的每个文件，就用手动分区
create table tb_emp_part1(
empno string,
ename string,
job string,
managerno string,
hiredate string,
salary double ,
jiangjin double ,
deptno string
)
partitioned by ( department int )
row format delimited fields terminated by '\t' ;
load data local inpath '/export/datas/emp10.txt' into table tb_emp_part1 partition ( department = 10 ) ;
load data local inpath '/export/datas/emp20.txt' into table tb_emp_part1 partition ( department = 20 ) ;
load data local inpath '/export/datas/emp30.txt' into table tb_emp_part1 partition ( department = 30 ) ;
step1:先创建一个张原始数据表，放所有数据：tb_emp step2:将所有数据通过程序来实现分区，写入新的分区表 step3:开启自动分区
set hive.exec.dynamic.partition.mode=nonstrict; step4:创建分区表 create table tb_emp_part2 (
empno string,
ename string,
job string,
managerno string,
hiredate string,
salary double ,
jiangjin double
)
partitioned by ( dept string)
row format delimited fields terminated by '\t' ;
insert into table tb_emp_part2 partition ( dept)
select * from tb_emp;
实现：判断tb_emp中的部门编号：deptno有哪几种值，第一种值作为第一个分区，第二种值作为第二个分区，将对应的数据放入对应的分区
如何知道我是按照tb_emp中的deptno做的分区？ 按照查询语句的最后一个字段做分区，写入新表 如果分区的字段不是最后一个字段怎么办？ 例如：我想按照job进行分区，那么select的时候将其放到最后 create table tb_emp_part2(
empno string,
ename string,
managerno string,
hiredate string,
salary double ,
jiangjin double ，
deptno string
)
partitioned by ( job string)
row format delimited fields terminated by '\t' ;
insert into table tb_emp_part2 partition ( job)
select
empno, ename, managerno, hiredate
, salary, jiangjin, deptno
, job
from tb_emp;
show partitions 表名;
本质:就是底层MapReduce的分区，分桶的规则按照分桶字段的HASH取余 应用场景: SMB Join：适合于大表 join 大表的应用场景，是一种优化以后 join
sort merge bulket join 如果两张表要进行SMB Join，要求两张表必须都为桶表 Reduce join：适合于`大表join大表，但是比较慢
A表：1亿条 B表：1亿条 reducejoin时：A表的每一条都需要与B表的每一条进行比较，然后关联
注意:分桶的数据是不能直接load进去的，必须通过MapReduce进行分区处理才能分桶 step1:开启分桶 set hive. enforce. bucketing= true ;
create table tb_emp_bucket (
empno string,
ename string,
job string,
managerno string,
hiredate string,
salary double ,
jiangjin double ,
deptno string
)
clustered by ( deptno) into 3 BUCKETS
row format delimited fields terminated by '\t' ;
insert overwrite table tb_emp_bucket
select * from tb_emp cluster by ( deptno) ;
共同点：大数据的处理思想：大而化小，分而治之 字段：分区的字段是假的，分桶的字段是真的 目录：分区是目录级别，分桶是文件级别 目的：
分区是为了加快输入的性能，会构建元数据，表的最后一级目录是分区的目录分桶是为了SMB Join而构建的，表到最后一级目录还是表的目录 本质减少mr的工作量，比如我们要查询某一字段的全部数据，可以只对目录下的分区文件进行一个mr作业就可以了 对数据进行水平切分，每个分区即为一个物理文件夹。 按照某列或某些列分为多个分区,容易数据倾斜 物理表现为：目录到分区,对应不同文件夹 分区字段为假 应用场景：比如国家，时间 本质减少shuffle花费的时间，表文件会被物理分桶为不同的文件，被分桶的数据被统一放到了一个或几个切片，这样可以让相同分桶的字段join，只需要把这两个分桶shuffer到一个reducer中就可以了 对数据进行垂直切分，每个分桶即为一个文件。 按照某列hash值%桶个数分割 物理表现为：目录到表,对应不同文件,细粒度 分区字段为真 应用场景：比如班级，某些有固定规定的字段 解答：
insert into：
insert overwrite：
alter table ptable drop partition ( daytime= '20140911 ',city=' bj') ;
元数据，数据文件都删除，但目录daytime= 20140911还在 map 输出数据按 key Hash 的分配到 reduce 中，由于 key 分布不均匀、业务数据本身的特、 建表时考虑不周、等原因造成的 reduce 上的数据量差异过⼤。
（1）key 分布不均匀; （2）业务数据本身的特性; （3）建表时考虑不周; （4）某些 SQL 语句本身就有数据倾斜; 如何避免：对于 key 为空产⽣的数据倾斜，可以对其赋予⼀个随机值 （1）参数调节：
hive.map.aggr = true hive.groupby.skewindata=true 有数据倾斜的时候进⾏负载均衡，当选项设定位 true,⽣成的查询计划会有两个 MR Job。 第⼀个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合 操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从⽽达到负载均衡的⽬的；第⼆个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同⼀个 Reduce 中）， 最后完成最终的聚合操作。 （2）SQL 语句调节：
① 选⽤ join key 分布最均匀的表作为驱动表。做好列裁剪和 filter 操作，以达到两表做 join 的时候，数据量相对变⼩的效果。 ② ⼤⼩表 Join： 使⽤ map join 让⼩的维度表（1000 条以下的记录条数）先进内存。在 map 端完成 reduce. ③⼤表 Join ⼤表： 把空值的 key 变成⼀个字符串加上随机数，把倾斜的数据分到不同的 reduce 上，由于 null 值关联不上，处理后并不影响最终结果。 ④ count distinct ⼤量相同特殊值: count distinct 时，将值为空的情况单独处理，如果是计算 count distinct，可以不⽤处理， 直接过滤，在最后结果中加 1。如果还有其他计算，需要进⾏ group by，可以先将值为空的 记录单独处理，再和其他计算结果进⾏ union。 hive 是基于 Hadoop 的⼀个数据仓库⼯具，可以将结构化的数据⽂件映射为⼀张数据库 表，并提供完整的 sql 查询功能，可以将 sql 语句转换为 MapReduce 任务进⾏运⾏。
其优点 是学习成本低，可以通过类 SQL 语句快速实现简单的 MapReduce 统计，不必开发专⻔的 MapReduce 应⽤，⼗分适合数据仓库的统计分析，但是 Hive 不⽀持实时查询。 Hive 与关系型数据库的区别： order by：会对输⼊做全局排序，因此只有⼀个 reduce（r 多个 reducer ⽆法保证全局有序）。 只有⼀个 reducer，会导致当输⼊规模较⼤时，需要较⻓的计算时间。 sort by：分区内排序，不是全局排序，其在数据进⼊ reducer 前完成排序，每个reduce内有序。 distribute by：按照指定的字段对数据进⾏划分输出到不同的 reduce 中，一般结合sort by，类似MR中Partition，按照指定的key进行分区。 cluster by：除了具有 distribute by 的功能外还兼具 sort by 的功能，指定的列只能是降序，不能指定asc和desc。 null 与任何值运算的结果都是 null, 可以使⽤ is null、is not null 函数指定在其值为 null 情况下的取值。null 在 hive 底 层 默 认 是 ⽤'\N'来 存 储 的 ， 可 以 通 过 alter table test SET SERDEPROPERTIES(‘serialization.null.format’ = ‘a’);来修改。 查询出 ID在t1表存在在t2不存在的数据split 将字符串转化为数组，即：split(‘a,b,c,d’ , ‘,’) ==> [“a”,“b”,“c”,“d”]。 coalesce(T v1, T v2, …) 返回参数中的第⼀个⾮空值；如果所有值都为 NULL，那么返 回 NULL。 collect_list 列出该字段所有的值，不去重 select collect_list(id) from table。Hive ⽀持三种不同的元存储服务器，分别为：内嵌式元存储服务器、本地元存储服务器、 远程元存储服务器，每种存储⽅式使⽤不同的配置参数。 内嵌式元存储主要⽤于单元测试，在该模式下每次只有⼀个进程可以连接到元存储， Derby 是内嵌式元存储的默认数据库。 在本地模式下，每个 Hive 客户端都会打开到数据存储的连接并在该连接上请求 SQL 查 询。 在远程模式下，所有的 Hive 客户端都将打开⼀个到元数据服务器的连接，该服务器依 次查询元数据，元数据服务器和客户端之间使⽤ Thrift 协议通信。 创建表时：
创建内部表时，会将数据移动到数据仓库指向的路径； 若创建外部表，仅记 录数据所在的路径， 不对数据的位置做任何改变。 删除表时：
在删除表的时候，内部表的元数据和数据会被⼀起删除， ⽽外部表只删除元 数据，不删除数据。 这样外部表相对来说更加安全些，数据组织也更加灵活，⽅便共享源数 据。 HiveSQL ->AST(抽象语法树) -> QB(查询块) ->OperatorTree（操作树）->优化后的操作 树->mapreduce 任务树->优化后的 mapreduce 任务树过程描述如下：
SQL Parser：Antlr 定义 SQL 的语法规则，完成 SQL 词法，语法解析，将 SQL 转化为抽 象 语法树 AST Tree； Semantic Analyzer：遍历 AST Tree，抽象出查询的基本组成单元 QueryBlock； Logical plan：遍历 QueryBlock，翻译为执⾏操作树 OperatorTree； Logical plan optimizer: 逻 辑 层 优 化 器 进 ⾏ OperatorTree 变 换 ，合 并 不 必 要 的 ReduceSinkOperator，减少 shuffle 数据量； Physical plan：遍历 OperatorTree，翻译为 MapReduce 任务； Logical plan optimizer：物理层优化器进⾏ MapReduce 任务的变换，⽣成最终的执⾏计 划； 由于 Hive 的元数据可能要⾯临不断地更新、修改和读取操作，所以它显然不适合使⽤ Hadoop ⽂件系统进⾏存储。⽬前 Hive 将元数据存储在 RDBMS 中，⽐如存储在 MySQL、 Derby 中。元数据信息包括：存在的表、表的列、权限和更多的其他信息。
select a. key , a. value
from a where a. key not exists ( select b. key from b)
LOAD DATA LOCAL INPATH '/your/path/test.txt'
OVERWRITE INTO TABLE test PARTITION ( l_date= '2016-10-10' )
⽬前 hive ⽀持简单的权限管理，默认情况下是不开启，这样所有的⽤户都具有相同的权 限，同时也是超级管理员，也就对 hive 中的所有表都有查看和改动的权利，这样是不符合 ⼀般数据仓库的安全原则的。Hive 可以是基于元数据的权限管理，也可以基于⽂件存储级 别的权限管理。 为了使⽤ Hive 的授权机制，有两个参数必须在 hive-site.xml 中设置： < property> < name> </ name> < value> </ value> < description> </ description> </ property> < property> < name> </ name> < value> </ value> < description> </ description> </ property> Hive ⽀持以下权限： Hive 授权的核⼼就是⽤户（user）、组（group）、⻆⾊（role）。 Hive 中的⻆⾊和平常我们认知的⻆⾊是有区别的，Hive 中的⻆⾊可以理解为⼀部分有 ⼀些相同“属性”的⽤户或组或⻆⾊的集合。这⾥有个递归的概念，就是⼀个⻆⾊可以是⼀些⻆⾊的集合。 下⾯举例进⾏说明： ⽤户 组
张三 G_db1
李四 G_db2
王五 G_bothdb
如上有三个⽤户分别属于 G_db1、G_db2、G_alldb。G_db1、G_db2、G_ bothdb 分别表示该组⽤户可以访问数据库 1、数据库 2和可以访问 1、2两个数据库。现在可以创建 role_db1和 role_db2，分别并授予访问数据库 1和数据库 2的权限。这样只要将 role_eb1赋给 G_db1（或者该组的所有⽤户），将 role_eb2赋给 G_db2，就可以是实现指定⽤户访问指定数据库。最后创建role_bothdb 指向 role_db1、role_db2（role_bothdb 不需要指定访问那个数据库），然后 role_bothdb 授予 G_bothdb，则 G_bothdb 中的⽤户可以访问两个数据库。 Hive 的⽤户和组使⽤的是 Linux 机器上的⽤户和组，⽽⻆⾊必须⾃⼰创建。 ⻆⾊管理：
create role role_name;
drop role role_name;
show roles
grant select on database db_name to role role_name;
grant select on [ table ] t_name to role role_name;
show grant role role_name on database db_name;
show grant role role_name on [ table ] t_name;
grant role role_name to user user_name
revoke select on database db_name from role role_name;
revoke select on [ table ] t_name from role role_name;
show role grant user user_name;
1-开发udf程序：继承UDF类，实现一个或者多个evaluate方法 2-打成jar包 3-上传jar包到集群中，并添加到hive的环境变量中，在hive中执行 add jar / export/ datas/ udf. jar;
create temporary function transDate as 'bigdata.hanjiaxiaozhi.cn.hive.udf.UserUDF' ;
select transDate( "18/Aug/2019:12:30:05" ) ;
DROP TEMPORARY FUNCTION transDate;
TextFile
默认格式，存储⽅式为⾏存储，数据不做压缩，磁盘开销⼤，数据解析开销⼤。 可结合Gzip、Bzip2使⽤(系统⾃动检查，执⾏查询时⾃动解压)，但使⽤这种⽅式，压缩后的⽂件不⽀持 split，Hive 不会对数据进⾏切分，从⽽⽆法对数据进⾏并⾏操作。并且在反序列化过程中，必须逐个字符判断是不是分隔符和⾏结束符，因此反序列化开销会⽐ SequenceFile ⾼⼏⼗倍。 SequenceFile
SequenceFile 是 Hadoop API 提供的⼀种⼆进制⽂件⽀持，，存储⽅式为⾏存储，其具有使⽤⽅便、可分割、可压缩的特点。 SequenceFile ⽀持三种压缩选择：NONE，RECORD，BLOCK。Record 压缩率低，⼀般建议使⽤ BLOCK 压缩。 优势是⽂件和 hadoop api 中的 MapFile 是相互兼容的 RCFile
存储⽅式：数据按⾏分块，每块按列存储。结合了⾏存储和列存储的优点：
⾸先，RCFile 保证同⼀⾏的数据位于同⼀节点，因此元组重构的开销很低； 其次，像列存储⼀样，RCFile 能够利⽤列维度的数据压缩，并且能跳过不必要的列读取； RCFile 的⼀个⾏组包括三个部分：
第⼀部分是⾏组头部的【同步标识】，主要⽤于分隔 hdfs 块中的两个连续⾏组 第⼆部分是⾏组的【元数据头部】，⽤于存储⾏组单元的信息，包括⾏组中的记录数、每个列的字节数、列中每个域的字节数 第三部分是【表格数据段】，即实际的列存储数据。在该部分中，同⼀列的所有域顺序存储。 从图可以看出，⾸先存储了列 A 的所有域，然后存储列 B 的所有域等。 数据追加：RCFile 不⽀持任意⽅式的数据写操作，仅提供⼀种追加接⼝，这是因为底层的 HDFS 当前仅仅⽀持数据追加写⽂件尾部。 ⾏组⼤⼩：⾏组变⼤有助于提⾼数据压缩的效率，但是可能会损害数据的读取性能，因为这样增加了 Lazy 解压性能的消耗。⽽且⾏组变⼤会占⽤更多的内存，这会影响并发执⾏的其他 MR 作业。考虑到存储空间和查询效率两个⽅⾯，Facebook 选择 4MB 作为默认的⾏组⼤⼩，当然也允许⽤户⾃⾏选择参数进⾏配置。 ORCFile
存储⽅式：数据按⾏分块 每块按照列存储。 压缩快 快速列存取。 效率⽐ rcfile ⾼,是 rcfile 的改良版本。 以下为 RCFile、TextFile、SequenceFile 三种⽂件的存储情况： [ hadoop@master ~ ] $ hadoop dfs - dus / user/ Hive / warehouse
- rw- r-- r-- 2 hadoop supergroup 51328177 2014 - 03 - 20 00 : 42
/ user/ Hive / warehouse/ rcfile_table/ 000000_0
- rw- r-- r-- 2 hadoop supergroup 51309896 2014 - 03 - 20 00 : 43
/ user/ Hive / warehouse/ rcfile_table/ 000001_0
- rw- r-- r-- 2 hadoop supergroup 56263711 2014 - 03 - 20 01 : 20
/ user/ Hive / warehouse/ seqfile_table/ 000000_0
- rw- r-- r-- 2 hadoop supergroup 56233984 2014 - 03 - 20 01 : 21
/ user/ Hive / warehouse/ seqfile_table/ 000001_0
- rw- r-- r-- 2 hadoop supergroup 536799616 2014 - 03 - 19 23 : 15
/ user/ Hive / warehouse/ testfile_table/ weibo. txt
- rw- r-- r-- 2 hadoop supergroup 53659758 2014 - 03 - 19 23 : 24
/ user/ Hive / warehouse/ textfile_table/ 000000_0. gz
- rw- r-- r-- 2 hadoop supergroup 53648309 2014 - 03 - 19 23 : 26
/ user/ Hive / warehouse/ textfile_table/ 000001_1. gz
总结:(重要)
相⽐ TEXTFILE 和 SEQUENCEFILE，RCFILE 由于列式存储⽅式，数据加载时性能消耗较⼤，但是具有较好的压缩⽐和查询响应。 数据仓库的特点是⼀次写⼊、多次读取，因此，整体来看，RCFILE 相⽐其余两种格式具有较明显的优势。 解答:
个人感觉这个解释不太满意，不过小表放前，养成习惯也好 1.将最⼤的表放置在 JOIN 语句的最右边，或者直接使⽤/*+ streamtable(table_name) */指出。 2.在编写带有 join 操作的代码语句时，应该将条⽬少的表/⼦查询放在 Join 操作符的左边。因为在 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，载⼊条⽬较少的表可以有效减少 OOM（out of memory）即内存溢出。所以对于同⼀个 key 来说，对应的 value 值⼩的放前，⼤的放后，这便是“⼩表放前”原则。若⼀条语句中有多个 Join，依据Join 的条件相同与否，有不同的处理⽅法。 解答:
如果其中有⼀张表为⼩表，直接使⽤ map 端 join 的⽅式（map 端加载⼩表）进⾏聚合。 如果两张都是⼤表，那么采⽤联合 key，联合 key 的第⼀个组成部分是 join on 中的公共字段，第⼆部分是⼀个 flag，0 代表表 A，1 代表表 B，由此让 Reduce 区分客户信息和订单信息；在 Mapper 中同时处理两张表的信息，将 join on 公共字段相同的数据划分到同⼀个分区中，进⽽传递到⼀个 Reduce 中，然后在 Reduce 中实现聚合。 解答:
在 Hive 0.13 版本之前，通过 left outer join 实现 SQL 中的 in 查询 0.13 版本之后，Hive已经⽀持 in 查询。 解答: 不是，从 Hive0.10.0 版本开始 对于简单的不需要聚合的类似 SELECT <col> from <table>LIMIT n语句，不需要起 MapReduce job，直接通过 Fetch task 获取数据。 解答: UDF: 单⾏进⼊，单⾏输出 →一对一UDAF: 多⾏进⼊，单⾏输出 →多对一UDTF: 单⾏输⼊，多⾏输出 →一对多解答:
桶表是对数据进⾏哈希取值，然后放到不同⽂件中存储。 数据加载到桶表时，会对字段取 hash 值，然后与桶的数量取模 。把数据放到对应的⽂件中。物理上，每个桶就是表(或分区⽬录⾥的⼀个⽂件，⼀个作业产⽣的桶(输出⽂件)和reduce 任务个数相同。 桶表专⻔⽤于抽样查询，是很专业性的，不是⽇常⽤来存储数据的表，需要抽样查询时，才创建和使⽤桶表。 解答:
1）写⼀个类继承（org.apache.hadoop.hive.ql.）UDF 类； 2）覆盖⽅法 evaluate()； 3）打 JAR 包； 4）通过 hive 命令将 JAR 添加到 Hive 的类路径：
hive> add jar /home/ubuntu/ToDate.jar; 5）注册函数：
hive> create temporary function xxx as ‘XXX’; 6）使⽤函数； 7）[可选] drop 临时函数； Hive 中对某些情况的查询可以不必使⽤ MapReduce 计算。
hive-default.xml.template ⽂件中 hive.fetch.task.conversion 默认是 more,老版本hive是minimal（修改为more） more时全局查找、字段查找、limit 查找等都不⾛ mapreduce。 单台机器上处理所有的任务,适合于小数据集,执行时间会大大缩短 Hive 的输⼊数据量⾮常⼩的情况下 set hive.exec.mode.local.auto=true; （开启本地模式） set hive.exec.mode.local.auto.inputbytes.max=134217728;（小于128mb采用本地模式，默认134217728） set hive.exec.mode.local.auto.input.files.max=10;（输入文件个数小于10个采用本地模式，默认为 4） 场景:
我想要验证一个函数的执行结果
我先建一张表xxx,
往表里插入一行数据
select ( 1 + 2 ) from xxx
结果需要好久???
name我可以开启本地模式set hive. exec . mode . local . auto= true ; ( 默认为false )
但是你会发现job确实是以本地模式运行了（看job名字就能看出来, 中间有local 字样）, 但是还是会报错，各种找不到jar包。
这里还要运行一个语句：set fs. defaultFS= file :
在执行sql 发现速度大大提高了!
当一个job满足下面条件的时候才能真正使用本地模式
输入数据小于参数：hive. exec . mode . local . auto. inputbytes. max ( 默认128 MB)
map数小于参数：hive. exec . mode . local . auto. tasks. max ( 默认4 )
reduce为0 或1
表的优化1.⼩表、⼤表 Join（新版的 hive 已经对⼩表 JOIN ⼤表和⼤表 JOIN ⼩表进⾏了优化。⼩表 放在左边和右边已经没有明显区别。）
将 key 相对分散，并且数据量⼩的表放在 join 的左边 优点：
1.有效减少内存溢出错误发⽣的⼏率 2.可以使⽤ Group 让⼩的维度表（1000条以下的记录条数）先进内存 3.在 map 端完成 reduce。 表的优化2.⼤表 Join ⼤表
空 KEY 过滤（is not null）
有时 join 超时是因为某些 key 对应的数据太多，⽽相同 key 对应的数据都会发送到相同的 reducer 上，从⽽导致内存不够。此时我们应该仔细分析这些异常的 key，很多情况下，这些 key 对应的数据是异常数据，我们需要在 SQL 语句中进⾏过滤。例如 key 对应的字段为空 空 key 转换（concat（‘含空列’,rand()））
有时虽然某个 key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join 的结果中，此时我们可以表 a 中 key 为空的字段赋⼀个随机的值，使得数据随机均匀地分不到不同的 reducer 上。 表的优化3.MapJoin 表的优化4.Group By 表的优化5.Count(Distinct) 去重统计 表的优化6.避免笛卡尔积
尽量避免笛卡尔积（join 的时候不加 on 条件，或者⽆效的 on 条件） 表的优化7.⾏列过滤
列处理：在 SELECT 中，只拿需要的列，如果有，尽量使⽤分区过滤，少⽤ SELECT *。 ⾏处理：在分区剪裁中，当使⽤外关联时，如果将副表的过滤条件写在 Where 后⾯，那么就会先全表关联，之后再过滤 表的优化8.动态分区调整
关系型数据库中，对分区表 Insert 数据时候，数据库⾃动会根据分区字段的值，将数据插⼊到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使⽤ Hive 的动态分区，需要进⾏相应的配置。 表的优化9.分桶 表的优化10.分区 Map 数 ⼩⽂件进⾏合并 复杂⽂件增加 Map 数 Reduce 数 并⾏执⾏严格模式JVM 重⽤推测执⾏压缩场景举例.北京市学⽣成绩分析. 成绩的数据格式:时间,学校,年纪,姓名,科⽬,成绩 样例数据如下: 2013 , 北⼤, 1 , 裘容絮, 语⽂, 97
2013 , 北⼤, 1 , 庆眠拔, 语⽂, 52
2013 , 北⼤, 1 , 乌洒筹, 语⽂, 85
2012 , 清华, 0 , 钦尧, 英语, 61
2015 , 北理⼯, 3 , 冼殿, 物理, 81
2016 , 北科, 4 , 况飘索, 化学, 92
2014 , 北航, 2 , 孔须, 数学, 70
2012 , 清华, 0 , 王脊, 英语, 59
2014 , 北航, 2 , ⽅部盾, 数学, 49
2014 , 北航, 2 , 东⻔雹, 数学, 77
hive - e"set mapreduce.job.queuename=low;
select t.*
from(select
school,
class,
subjects,
score,
row_number() over (partition by school,class,subjects order by score desc) rank_code
from spark_test_wx
where partition_id = " 2017 "
) t
where t.rank_code <= 3;"
select school, class, subjects, score,
sum ( score) over ( order by score range between 2 preceding and 2 following ) sscore
from spark_test_wx
where partition_id = "2017" and school= "北航"
提问, 上述 sql 有没有可优化的点.
hive - e "
set mapreduce.job.queuename=low;
select school,class,name,sum(score) as total_score,
count(1) over (partition by school,class) nct
from spark_test_wx
where partition_id = " 2017 " and school=" 清华" and class = 1
group by school,class,name
having total_score>200;
"
having 是分组（group by ）后的筛选条件，分组后的数据组内再筛选，也就是说 HAVING ⼦句可以让我们筛选成组后的各组数据。
where 则是在分组, 聚合前先筛选记录。也就是说作⽤在GROUP BY ⼦句和HAVING ⼦句前。
今年加⼊进来了 10 个学校, 学校数据差异很⼤计算每个学校的平均分。
该题主要是考察数据倾斜的处理⽅式。
Group by ⽅式很容易产⽣数据倾斜。需要注意⼀下⼏点
1 ）Map 端部分聚合
hive. map. aggr= true （⽤于设定是否在 map 端进⾏聚合，默认值为真，相当于combine）
hive. groupby. mapaggr. checkinterval= 100000 （⽤于设定 map 端进⾏聚合操作的条数）
2 ）有数据倾斜时进⾏负载均衡
设定 hive. groupby. skewindata，当选项设定为 true 是，⽣成的查询计划有两个MapReduce 任务。
在第⼀个MapReduce 中，map 的输出结果集合会随机分布到reduce 中，每个reduce做部分聚合操作，并输出结果。这样处理的结果是，相同的 Group By Key 有可能分发到不同的 reduce 中，从⽽达到负载均衡的⽬的；
第⼆个 MapReduce 任务再根据预处理的数据结果按照 Group By Key 分布到 reduce中（这个过程可以保证相同的 Group By Key 分布到同⼀个 reduce 中），最后完成最终的聚合操作。
假设我创建了⼀张表，其中包含了 2016 年客户完成的所有交易的详细信息：CREATE TABLE transaction_details ( cust_id INT , amount FLOAT , month STRING, country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘, ’;
现在我插⼊了 100 万条数据，我想知道每个⽉的总收⼊。
问：如何⾼效的统计出结果。写出步骤即可。
解答:
1 ）⾸先分析这个需求，其实并不难，但是由于题⽬说了，要⾼效。⽽且数据量也不⼩, 直接写 sql 查询估计肯定会挂。
2 ）分析：
（1 ）我们可以通过根据每个⽉对表进⾏分区来解决查询慢的问题。 因此，对于每个⽉我们将只扫描分区的数据，⽽不是整个数据集。
（2 ）但是我们不能直接对现有的⾮分区表进⾏分区。 所以我们会采取以下步骤来解决这个问题：
（3 ）创建⼀个分区表，partitioned_transaction：
create table partitioned_transaction ( cust_id int , amount float , country string) partitioned by ( month string) row format delimited fields terminated by ‘, ’;
（4 ）在 Hive 中启⽤动态分区：
SET hive. exec . dynamic. partition = true ;
SET hive. exec . dynamic. partition . mode = nonstrict;
（5 ）将数据从⾮分区表导⼊到新创建的分区表中：
insert overwrite table partitioned_transaction partition ( month ) select cust_id, amount, country, month from transaction_details;
（6 ）使⽤新建的分区表实现需求。
本地文件导入到Hive表–load data local inpath '路径' into/overwrite table 表名HDFS文件导入到Hive表–load data inpath 'hdfs路径' into/overwrite table 表名Hive表导入到Hive表(select 或者insert )
创建表的过程中从其他表导入–create table ... as select xxx from 其他表 从其他表导入–insert into/overwrite table 表名 select xxx from 其他表 指定数据库目录在HDFS上的地址–location 'hdfs路径' 导出到本地文件系统；
insert overwrite local directory ‘/home/wyp/wyp’ select * from wyp； 和导入数据到Hive不一样，不能用insert into来将数据导出 还可以用hive的-e和-f参数来导出数据。其中-e 表示后面直接接带双引号的sql语句；而-f是接一个文件，文件的内容为一个sql语句
hive -e “select * from wyp” >> local/wyp.txt 导出到HDFS中；
insert overwrite directory ‘/home/wyp/wyp’ select * from wyp； 导出到Hive的另一个表中。
insert into/overwrite table 表名 select xxx from 其他表 TextFile
行式存储:查询一整行的时候,查询快默认格式 数据不做压缩,磁盘开销大,数据解析开销大 可使用Gzip、Bzip2，但压缩后的文件不支持split 在反序列化的时候，必须逐个字段判断是不是分隔符和行结束符，因此反序列化开销高 SequenceFile
ORC
列式存储:查询只有少数几个字段的时候,大大减少读取的数据量每个ORC文件由3部分组成：Index Data（1w行做一个索引）、Row Data（具体数据）、Stripe Footer（类型，长度） Parquent
列式存储二进制存储会按照Block大小设置行组的大小 Bzip2
可切分 压缩率高,高于Gzip,压缩速度很慢 适合:对速度要求不高,但需要较大压缩率 Gzip
LZO
不可切分 压缩率低于Gzip 需要建索引,并指定输入格式 LZ4
Snappy
不可切分高效的压缩速度和合理的压缩率压缩率低于Gzip 动态分区插入reduce数量过多数据源本身包含大量小文件会开很多map,一个map开一个jvm执行,这些任务的初始化和启动和回收会占用大量资源 会占用大量元数据内存(每个小文件对象150byte左右)不使用textfile 减少reduce数量 少用动态分区,用的话加上distribute by分区 对于已存在的数据
hadoop archive命令把小文件进行归档 重建表,建表时减少reduce数量 参数进行调节,设置map/reduce相关参数
map端/reduce端输出进行合并 设置合并文件大小 当输出文件的平均大小小于某值时,启动一个独立的MR任务进行文件merge
set mapred. max. split. size= 256000000 ;
set mapred. min. split. size. per. node= 100000000 ;
set mapred. min. split. size. per. rack= 100000000 ;
set hive. input. format= org. apache. hadoop. hive. ql. io. CombineHiveInputFormat;
我们可以通过一些配置项来使Hive在执行结束后对结果文件进行合并：
set hive. merge . mapfiles = true
set hive. merge . mapredfiles = true
set hive. merge . size. per. task = 256 * 1000 * 1000
set hive. merge . smallfiles. avgsize= 16000000
注意：对于输出结果为压缩文件形式存储的情况，要解决小文件问题，如果在Map输入前合并，对输出的文件存储格式并没有限制。但是如果使用输出合并，则必须配合SequenceFile来存储，否则无法进行合并。 内关联（JOIN）
左外关联（LEFT [OUTER] JOIN）
以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。 是否指定OUTER关键字，貌似对查询结果无影响。 右外关联（RIGHT [OUTER] JOIN）
和左外关联相反，以RIGTH [OUTER] JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。 是否指定OUTER关键字，貌似对查询结果无影响。 全外关联（FULL [OUTER] JOIN）
以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。 是否指定OUTER关键字，貌似对查询结果无影响。 注意：FULL JOIN时候，Hive不会使用MapJoin来优化。 LEFT SEMI JOIN
以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY 也在副表中的记录。 笛卡尔积关联（CROSS JOIN）
LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。 LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方都不行。 因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。 left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。因为右表只有 join key 参与关联计算了，而left join on 默认是整个关系模型都参与计算了 Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持LEFT SEMI JOIN和CROSS JOIN，但这两种JOIN类型也可以用前面的代替。 注意：Hive中Join的关联键必须在ON ()中指定，不能在Where中指定，否则就会先做笛卡尔积，再过滤。 数据准备 hive> desc lxw1234_a;
OK
id string
name string
Time taken: 0.094 seconds, Fetched: 2 row ( s)
hive> select * from lxw1234_a;
OK
1 zhangsan
2 lisi
3 wangwu
Time taken: 0.116 seconds, Fetched: 3 row ( s)
hive> desc lxw1234_b;
OK
id string
age int
Time taken: 0.159 seconds, Fetched: 2 row ( s)
hive> select * from lxw1234_b;
OK
1 30
2 29
4 21
Time taken: 0.09 seconds, Fetched: 3 row ( s)
SELECT a. id,
a. name,
b. age
FROM lxw1234_a a
join lxw1234_b b
ON ( a. id = b. id) ;
1 zhangsan 30
2 lisi 29
左外关联（LEFT [OUTER] JOIN）
以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。 是否指定OUTER关键字，貌似对查询结果无影响。 SELECT a. id,
a. name,
b. age
FROM lxw1234_a a
left join lxw1234_b b
ON ( a. id = b. id) ;
1 zhangsan 30
2 lisi 29
3 wangwu NULL
右外关联（RIGHT [OUTER] JOIN）
和左外关联相反，以RIGTH [OUTER] JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。 是否指定OUTER关键字，貌似对查询结果无影响。 SELECT a. id,
a. name,
b. age
FROM lxw1234_a a
RIGHT OUTER JOIN lxw1234_b b
ON ( a. id = b. id) ;
1 zhangsan 30
2 lisi 29
NULL NULL 21
全外关联（FULL [OUTER] JOIN）
以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。 注意：FULL JOIN时候，Hive不会使用MapJoin来优化。 SELECT a. id,
a. name,
b. age
FROM lxw1234_a a
FULL OUTER JOIN lxw1234_b b
ON ( a. id = b. id) ;
1 zhangsan 30
2 lisi 29
3 wangwu NULL
NULL NULL 21
LEFT SEMI JOIN
以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录。 SELECT a. id,
a. name
FROM lxw1234_a a
LEFT SEMI JOIN lxw1234_b b
ON ( a. id = b. id) ;
1 zhangsan
2 lisi
SELECT a. id,
a. name
FROM lxw1234_a a
WHERE a. id IN ( SELECT id FROM lxw1234_b) ;
SELECT a. id,
a. name
FROM lxw1234_a a
join lxw1234_b b
ON ( a. id = b. id) ;
SELECT a. id,
a. name
FROM lxw1234_a a
WHERE EXISTS ( SELECT 1 FROM lxw1234_b b WHERE a. id = b. id) ;
SELECT a. id,
a. name,
b. age
FROM lxw1234_a a
CROSS JOIN lxw1234_b b;
1 zhangsan 30
1 zhangsan 29
1 zhangsan 21
2 lisi 30
2 lisi 29
2 lisi 21
3 wangwu 30
3 wangwu 29
3 wangwu 21
除非特殊需求，并且数据量不是特别大的情况下，才可以慎用CROSS JOIN，否则，很难跑出正确的结果，或者JOB压根不能执行完。 Hive中只要是涉及到两个表关联，首先得了解一下数据，看是否存在多对多的关联。 开启后禁止3种类型查询
分区表没有指定分区字段为过滤条件的查询 order by不加limit的查询 笛卡尔积的查询 Hive严格模式
Hive提供了一个严格模式，可以防止用户执行那些可能产生意向不到的不好的效果的查询。说通俗一点就是这种模式可以阻止某些查询的执行。通过如下语句设置严格模式： hive> set hive. mapred. mode = strict;
设置为严格模式后，可以禁止3种类型的查询： (1)：带有分区的表的查询
如果在一个分区表执行hive，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行。换句话说就是在严格模式下不允许用户扫描所有的分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。如果不进行分区限制的查询会消耗巨大的资源来处理，如下不带分区的查询语句： hive> SELECT DISTINCT ( planner_id) FROM fracture_ins WHERE planner_id= 5 ;
FAILED: Error in semantic analysis: No Partition Predicate Found for Alias "fracture_ins" Table "fracture_ins
hive> SELECT DISTINCT ( planner_id) FROM fracture_ins
> WHERE planner_id= 5 AND hit_date= 20120101 ;
(2)：带有order by的查询 对于使用了order by的查询，要求必须有limit语句。因为order by为了执行排序过程会将所有的结果分发到同一个reduce中进行处理，强制要求用户增加这个limit语句可以防止reduce额外消耗资源，如下是不带limit关键字的查询语句： hive> SELECT * FROM fracture_ins WHERE hit_date> 2012 ORDER BY planner_id;
FAILED: Error in semantic analysis: line 1 :56 In strict mode ,
limit must be specified if ORDER BY is present planner_id
hive> SELECT * FROM fracture_ins WHERE hit_date> 2012 ORDER BY planner_id
> LIMIT 100000 ;
(3)：限制笛卡尔积的查询 对关系型数据库非常了解的用户可能期望在执行join查询的时候不使用on语句而是使用where语句，这样关系型数据库的执行优化器就可以高效的将where语句转换成那个on语句了。不幸的是，Hive并不支持这样的优化，因为如果表非常大的话，就会出现不可控的情况，如下是不带on的语句： hive> SELECT * FROM fracture_act JOIN fracture_ads
> WHERE fracture_act. planner_id = fracture_ads. planner_id;
FAILED: Error in semantic analysis: In strict mode , cartesian product
is not allowed. If you really want to perform the operation,
+ set hive. mapred. mode = nonstrict+
hive> SELECT * FROM fracture_act JOIN fracture_ads
> ON ( fracture_act. planner_id = fracture_ads. planner_id) ;
中间阶段不落盘
Hadoop 每次 shuffle 操作后，必须写到磁盘，而 Spark 在 shuffle 后不一定落盘，可以 cache 到内存中，以便迭代时使用。如果操作复杂，很多的 shufle 操作，那么 Hadoop 的读写 IO 时间会大大增加，也是 Hive 更慢的主要原因了。消除了冗余的 MapReduce 阶段
Hadoop 的 shuffle 操作一定连着完整的 MapReduce 操作，冗余繁琐。而 Spark 基于 RDD 提供了丰富的算子操作，且 reduce 操作产生 shuffle 数据，可以缓存在内存中。 JVM 的优化
Hadoop 每次 MapReduce 操作，启动一个 Task 便会启动一次 JVM，基于进程的操作。而 Spark 每次 MapReduce 操作是基于线程的，只在启动 Executor 是启动一次 JVM，内存的 Task 操作是在线程复用的。每次启动 JVM 的时间可能就需要几秒甚至十几秒，那么当 Task 多了，这个时间 Hadoop 不知道比 Spark 慢了多少。 极端查询 这个查询只有一次 shuffle 操作，此时，也许 Hive HQL 的运行时间也许比 Spark 还快，反正 shuffle 完了都会落一次盘，或者都不落盘。 Select month_id
, sum ( sales)
from T
group by month_id;
Hive是一个基于Hadoop的数据仓库工具，用于处理和分析大规模的结构化数据。虽然Hive提供了很多SQL-like的功能，但是在设计上它与传统的关系型数据库有很大的不同。 Hive的主要目标是高效地读取和分析数据，而不是数据的实时修改。因此，Hive在数据更新和删除方面的功能有限。在Hive中，表的结构信息存储在元数据中，而数据实际存储在HDFS（Hadoop Distributed File System）或其他分布式文件系统中。 Hive表不支持删除字段的原因如下：
1.数据存储不连续：Hive中的数据以文件形式存储，这些文件中的记录并不一定按照某种特定顺序排列。如果需要删除一个字段，就需要对整个数据集进行重写，这在大规模数据场景下会导致巨大的计算和I/O成本。 2.数据不可变性：Hive基于Hadoop，而Hadoop的文件系统（HDFS）是基于数据不可变性原则设计的。这意味着一旦数据写入HDFS，就不能进行修改。删除字段意味着需要修改已经存储的数据，这与HDFS的设计原则相悖。 3.兼容性问题：如果支持删除字段，那么需要处理与现有数据的兼容性问题。删除字段后，已经存在的数据中的这个字段应该如何处理？这会导致数据不一致，增加处理复杂性。 尽管Hive不支持删除字段，但你可以通过以下方法变更表结构：
1.创建一个新表，包含原表中除了要删除的字段之外的所有字段。 2.使用INSERT INTO或INSERT OVERWRITE从原表中选择所需的字段并将其插入新表。 3.在确认新表数据正确无误后，删除原表，并将新表重命名为原表名。 这种方法虽然存在一定的数据迁移成本，但可以确保数据的完整性和一致性。 Hive适用于离线批处理数据仓库场景。 Presto适用于实时或交互式查询大数据集的场景。 Spark适用于多种大数据工作负载，包括批处理、实时流处理、机器学习和图形计算等，具有更广泛的应用领域。 以下是详细对比： 1、Hive：
适用场景：Hive是一个数据仓库工具，专注于批量数据处理。它通常用于处理大规模的离线数据，如数据仓库、数据分析和报表生成等场景。 查询语言：Hive使用HiveQL，这是类似SQL的查询语言，使非技术人员能够轻松查询和分析数据。 存储：Hive通常将数据存储在Hadoop分布式文件系统（HDFS）中，支持多种数据格式，如Parquet、ORC等。 执行引擎：Hive使用MapReduce或Tez等底层执行引擎来处理查询。 2、Presto：
适用场景：Presto是一个分布式SQL查询引擎，旨在实时或交互式地查询大数据集。它适用于需要快速查询和分析数据的场景，如数据探索、仪表板、实时分析等。 查询语言：Presto使用标准SQL，支持复杂的查询和连接操作，具有较低的查询延迟。 存储：Presto支持多种数据存储系统，包括Hive、HDFS、Amazon S3、MySQL等，因此可以查询不同数据源中的数据。 执行引擎：Presto使用自己的执行引擎，它将查询分解为任务，并并行执行这些任务。 3、Spark：
适用场景：Spark是一个通用的大数据计算引擎，适用于多种场景，包括批处理、实时流处理、机器学习和图形计算等。它可以在多种工作负载下运行。 编程模型：Spark提供了丰富的API，包括Spark SQL、Spark Streaming、MLlib（机器学习库）和GraphX（图形计算库），使开发人员可以编写复杂的数据处理应用程序。 存储：Spark可以与多种数据存储系统集成，包括HDFS、Amazon S3、Cassandra等。 执行引擎：Spark使用内存计算来加速数据处理，它的执行引擎可以在内存中保留中间数据，减少了磁盘IO，从而提高了性能。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
