hive笔记

1、HIve是什么？
HIve是基于Hadoop的数仓解决方案。由于Hadoop本身在数据存储和计算方面有很好的可扩展性和高容错性，因此使用Hive构建的数仓也秉承了这些特性。
其实Hive就是在Hadoop上添加了一层sql接口，可以将sql翻译成MapReduce在Hadoop上执行，使得数据开发和数据分析人员很方便的使用SQL来完成海量数据的统计和分析。
2、HIve擅长什么？
Hive可以使用HQL很方便的完成对海量数据的统计汇总，即时查询和分析，除了内置的函数，还支持开发人员使用其他编程人员和脚本语言来自定义函数。
由于Hadoop本事是一个批处理，高延迟的框架，HIve使用Hadoop作为执行引擎，也具有批处理。高延迟的特点。
Hive对事物的支持不够好，原因为HDFS本身就设置为一次写入，多次读取的分布式存储系统，因此不能用HIve来完成增删改等在线事务处理的需求。
HIVe擅长的是非实时的、离线的、对相应的及时性要求不高的海量数据批量计算，即席查询和统计分析。
3、Hive的数据单元
Database、tabales、Partitions（分区）、Buckets（分桶）
4、Hive的数据类型
 原始数据类型
    整形：
        TINYINT 微整形，占用一个字节，存储0-255的整数
        SMLALLINT 小整形 占2个字节，存储范围-32768-32767
        INT 整形，占用4个字节，同java
        BIGINT 长整型，占用8个字节，存储范围-2^63-2^63-1
    布尔型：
        boolean，同java
    浮点类型：
        FLOAT：同Java
        DOUBLE：同JAVA
    字符串类型：
        STRING：不设定长度
 复合数据类型
    STRUCTS：任意数据类型组成的结构
    MAP：同javaMAP
    ARRAYS：数组

    
    

Hive函数（参考网上相关资料）

Hive的数据库和表
hive的数据是存储在HDFS上的，配置文件为hive-site中，由参数hive.metastore.warehouse指定。默认值为/user/hive/warehouse指定。默认值为/usr/hive/warehous
Hive建表语句详解
创建表的三种方法
Hive创建表的方式（默认路径为/user/hive/warehouse,也可以location指定，主要针对external表）
1、使用create命令创建一个新表，带分区
#########################################
create table '####' (
    `###` int,
    `###` string,
)partitioned by(date string)
ROW format deltmited fields terminated by ',';   //设置hive表切割字符为 “,”
#########################################

2、把一张表的某些字段抽取出来，创建为一张新表，使用as
#########################################
create table `###` as select * from `###` where statis_date="20180229";
#########################################

3、复制表结构，使用like创建表
create table `###` like `###`
不会复制表中属性值，只会复制表结构（包括表的分区以及存储格式之类的，区别于as）

特别说明：
a) create table
 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常。
 可使用关键字if not exists。
b) external
 创建一个外部表，在建表的同时指定一个指向实际数据的路径(LOCATION).hive创建内部表时，会将数据移动到数仓指向的路径；如果创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
 3、like
 允许用户复制现有的表结构，但是不复制数据
 4、row format delimited
 是用来设置创建的表在加载数据的时候，支持的列分隔符。
 hive默认的分隔符是\001,属于不可见字符，这个字符在vi里面是^A.
 5、stored as(数据压缩)
sequencefile|textfile|extfile|rcfIle
若文本数据是纯文本，可以使用stored as textfile
若数据需要压缩，使用stored as sequencefile
 6、clustered by 
 对于每一个表或者分区，hive可以进一步组织成桶。hive采用对列值hash，然后以桶的个数求余的方式决定该条记录存放在哪个桶当中。
 7、location
 指定加载数据路径（指定hdfs上的位置）。针对extetnal外部表，要指定存储路径或采用默认路径。
 
 内部表和外部表
  默认情况下，hive会创建内部表，其中文件，元数据和统计信息由内部hive进程管理。
  内部表存储在hive.metastore.warehouse.dir路径属性下，location在表创建期间，可以覆盖默认位置。如果删除内部表或分区，则删除与该表或分区关联的数据和元数据，如果未指定PURGE选项，则数据将移动到回收站文件件一段时间。
  
  当hive应该管理表的生命周期或生成临时表时使用内部表。
  外部表描述外部文件的元数据/模式。外部表文件可以由hive外部的进程访问和管理。外部表可以访问存储在Azure Storage Volumes(ASV) 或远程HDFS位置等源中的数据。如果更改外部表的结构或分区，则可以使用MSCK REPAIR TABLE table_name语句来刷新元数据信息。
  当文件已存在或位于远程位置时使用外部表，即时删除表，文件也应保留。
  EXTERNAL关键字允许您创建并提供LOCATION，以便Hive不使用此表的默认位置。删除EXTERNAL表时，表中的数据不会从文件系统中删除。
  EXTERNAL表指向其存储的任何HDFS位置，而不是存储在配置属性指定的文件夹中.
  
  TEXTFILE
    默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理。源文件可以直接通过hadoop fs -cat 查看.
    存储方式：行存储
    磁盘开销大,数据解析开销大.
    压缩的text文件 hive无法进行合并和拆分.
  SEQUENCEFILE
    一种Hadoop API提供的二进制文件，使用方便、可分割、可压缩等特点。 
    SEQUENCEFILE将数据以< key,value>的形式序列化到文件中。序列化和反序列化使用Hadoop 的标准的Writable 接口实现。key为空，用value 存放实际的值， 这样可以避免map 阶段的排序过程。
    三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。文件和Hadoop api中的mapfile是相互兼容的。使用时设置参数: 
    set hive.exec.compress.output=true; 
    set io.seqfile.compression.type=BLOCK;         – NONE/RECORD/BLOCK 
    create table test2(str STRING) STORED AS SEQUENCEFILE;     
  RCFILE
    一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。 
    理论上具有高查询效率（但hive官方说效果不明显，只有存储上能省10%的空间，所以不好用，可以不用）。 
    RCFile结合行存储查询的快速和列存储节省空间的特点 
    1）同一行的数据位于同一节点，因此元组重构的开销很低； 
    2) 块内列存储，可以进行列维度的数据压缩，跳过不必要的列读取。
    查询过程中，在IO上跳过不关心的列。实际过程是，在map阶段从远端拷贝仍然拷贝整个数据块到本地目录，也并不是真正直接跳过列，而是通过扫描每一个row group的头部定义来实现的。 
    但是在整个HDFS Block 级别的头部并没有定义每个列从哪个row group起始到哪个row group结束。所以在读取所有列的情况下，RCFile的性能反而没有SequenceFile高。
  ORC
    hive给出的新格式，属于RCFILE的升级版。
    ORC（OptimizedRC File）存储源自于RC（RecordColumnar File）这种存储格式，RC是一种列式存储引擎，对schema演化（修改schema需要重新生成数据）支持较差，而ORC是对RC改进，但它仍对schema演化支持较差，主要是在压缩编码，查询性能方面做了优化。RC/ORC最初是在Hive中得到使用，最后发展势头不错，独立成一个单独的项目。Hive 1.x版本对事务和update操作的支持，便是基于ORC实现的（其他存储格式暂不支持）。ORC发展到今天，已经具备一些非常高级的feature，比如支持update操作，支持ACID，支持struct，array复杂类型。你可以使用复杂类型构建一个类似于parquet的嵌套式数据架构，但当层数非常多时，写起来非常麻烦和复杂，而parquet提供的schema表达方式更容易表示出多级嵌套的数据类型。 
    ORC是RCfile的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩比和Lzo压缩差不多，比text文件压缩比可以达到70%的空间。而且读性能非常高，可以实现高效查询。 
    具体介绍https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC

   注意：       只有TEXTFILE表能直接加载数据，必须本地load数据和external外部表直接加载运路径数据，都只能用TEXTFILE表。更深一步，hive默认支持的压缩文件（hadoop默认支持的压缩格式）也只能用TEXTFILE表直接读取。其他格式不行。可以通过TEXTFILE表加载后insert到其他表中。
    换句话说，SequenceFile、RCFile表不能直接加载数据，数据要先导入到textfile表，再从textfile表通过insert select from 导入到SequenceFile,RCFile表。
    SequenceFile、RCFile表的源文件不能直接查看，在hive中用select看。
    RCFile源文件可以用 hive –service rcfilecat /xxx/000000_0查看，但是格式不同，很乱。
    ORCFile源文件可以用 hive –orcfiledump来进行分析ORC存储文件，就可以看到这些信息： hive –orcfiledump < path_to_file>
    
    
    
hive的安装配置：
    1、Hadoop Client + mysql
    2、配置环境变量
    3、创建hive元数据库和表，并赋权。
    4、配置hive-site.xml
    hive.metastore.warehouse.dir(Hive在HDFS上的根目录)
    javax.jdo.option.ConnectionURL hive元数据库的连接串。
    javax.jdo.option.ConnectionName-----
    datanucleus.autoCreateTables 数据库表不存在时自动创建hive元数据表。
    datanucleus.autoCreateCloumns 不存在时自动创建hive元数据列。    5、Hive的日志一般存放在操作系统本地的/tmp/${user.name}hive.log中，该文件由$HIVE_HOME/conf/hive-log4j.properties中指定。
    
HIve的视图和分区
    hive的视图只有逻辑视图，没有物化视图；
    视图只能查询，不能操作；
    视图在创建的时候，只是保存了一份元数据，当查询视图的时候，才开始执行视图对应的那些子查询。
    create/drop/alter(增删改) view ###(describe) comment '' as ..............

    Hive中的表分区，hive中的表分区就是把统一组数据放到同一个HDFS目录下，当查询中过滤条件制定了某一个分区值，只把这个分区对应的目录作为input，减少mapreduce的输入数据，挺高查询效率。
    创建分区表
    create external ###()comment '' partitioned by (month String,day STRING)
    创建表的时候，使用partitioned by关键字来指定该表作为分区表，括号中指定了分区的字段和类型，分区字段可以有多个，在HDFS中对应多级目录。
    添加分区
    使用insert添加分区，往分区中追加数据
    insert into table ### partition(month = '',day='') select *from dual;
    覆盖分区数据
    insert overwrite table ### partition(month = '',day='') select *from dual;
    使用alter table添加分区
    alter table ### add partition() location 'hdfs://namenode/tmp/###/month=###/day=###';

    查看分区对用的hdfs路径
    使用命令 show partitions ###;查看所有表分区。
    使用desc formatted ### partition (month = '',day='');查看该分区的详细信息，包括该分区的详细信息和该分区在hdfs上的路径。
    删除分区
    使用alter table ### drop partition(month='',day='');
    删除一个分区；同内部表和外部表，如果该分区为外部表，则分区对应的hdfs目录数据不会被删除。

向hive表中加载数据
    1、建表时指定
    create external table ### () row format delimited fields terminated nu stored as textfile location '';
    2、从本地文件系统或者hdfs的一个目录中加载
    若数据在本地，或者hdfs的某一个目录下，需要加载到目标或分区中，那么使用load data命令即可加载数据
        加载本地文件 load data local inpath '##' into table ### partition (day='');
        加载hdfs文件 load data local inpath '##' into table ### partition (day='');
    从业一个子查询中加载数据
    insert overwrite table ### partition(day='')
    select ,,,,from ###;
    
    导出hive数据到文件系统中。
    insert overwrite [local] directory '' [row format ''] [stored as ''] select ... from...
    若制定了local关键字，则为导出到本地文件系统，否则，导出到hdfs。使用row format 关键字可以指定导出的文件分隔符。
    
    
hive的查询语句select
    hive中的select基础语法和标准sql基本语法一致，支持where、distinct、group by、order by，having 、limit、子查询等。
    1、hive中的order by 和sort by 
    order by 用于全局排序，就是对指定的所有排序键进行全局排序，最后会用一个reduce task来完成全局排序。
    sort by 用于分区内排序,也就是每个redyce任务内排序。
    2、distribute by 和 cluster by
    distribute by是按照指定的字段或表达式对数据进行划分，输出到对用的reduce或者文件中。
    cluster by :除了兼具distribute by 的功能，还具有sort by的排序功能。
    3、子查询    子查询和标准sql中的子查询语法和用法基本一致，需要注意的是，hive中如果是从一个子查询进行select查询，那么子查询必须起一个别名。另外，0.13版本的hive中，在where子句中也支持子查询。
    4、把子查询作为一个表的语法。
    5、虚拟列
    hive中有两个虚拟列：input_file_name:数据对应的hdfs文件名；
    Block_OFFSET_INSIDE_FILE:该行记录在文件中的偏移量。
    
    
    hive的分析窗口函数 grouping sets,grouping_id,cube,rollup
    这几个分析函数通常用于olap中，不能累加,而且需要根据不同维度上钻和下钻的指标统计，比如分、小时、天、月、的UV数。
    1、grouping sets
    在一个group by查询中，根据不同维度组合进行聚合，等价于把不同维度的group by结果集进行union all
    其中的grouping_ID,表示结果属于哪一个分组集合。
    2、CUBE
    根据GROUP BY的维度的所有组合进行聚合
    3、ROLLUP
    是cube的子集，以最左侧的为主，从该维度进行层级聚合。