hive小结 - 校招准备

2020-07-01 00:00:00 数据 用户 文件 指定 压缩

行转列:explode


列转行:

使用函数：concat_ws(',',collect_set(column))

说明：collect_list 不去重，collect_set 去重。 column的数据类型要求是stringhive 基础知识的总结回顾 内含我收集的几个sql题目, 用来回顾练手的

包括一些优化相关的总结

是什么

  • 为Hadoop上的任务提供一个sql的接口
  • hive适用于数据仓库应用程序 OLAP 任务
    • 静态数据分析
    • 对实时性要求不高


  • 不是一个完整的数据库
    • 只有表的元数据
    • 具体数据存储在HDFS中

数据模型

  • db
    • 在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹


  • table
    • 在hdfs中表现所属db目录下一个文件夹
  • external table
    • 与table类似，不过其数据存放位置可以在任意指定路径(外部表)


  • partition
    • 在hdfs中表现为table目录下的子目录
  • bucket
    • 在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

基本组成

  • 用户接口
    • CLI
    • JDBC/ODBC
    • WebGUI


  • 元数据存储
  • 解释器、编译器、优化器、执行器
    • 完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成

hive服务

  • cli
    • 命令行接口
    • hive
      • -- 注释
      • -d 定义kv值
      • -f filename
      • -h help
      • -h hostname
      • -e 一次性的命令
        • hive -e "" > file


  • hiveserver2
    • 让hive以thrift服务运行
  • beeline
    • 嵌入式工作的hive命令行接口


  • hwi
    • hive的web接口
  • hue
    • 更加全面的web接口


  • metastore
    • 默认metostore和hive在同一个服务里
    • 可以让其成为单独的元数据服务

特点

  • 内部表 外部表
  • 存储格式
    • 文件
    • 二进制格式


  • 元数据
    • 单机
    • 元数据服务
      • 若配置了,每次都要启动
      • hive --service metastore &
  • 视图
    • 类似关系型数据库视图


  • 索引
    • Hive的索引目的是提高Hive表指定列的查询速度。
  • 执行引擎
    • mapreduce
    • tez


  • 读时模式 写时模式
    • hive是读时模式
    • 在加载的时候验证

分区与分桶

分区

  • 每个分区建立一个文件夹
  • 外部分区表
  • 自己增加分区
  • 自己挂载进来

分桶

  • 桶是通过对指定列进行哈希计算来实现的，通过哈希值将一个列名下的数据切分为一组桶，并使每个桶对应于该列名下的一个存储文件。
  • 随机分数据

hql语法

基础

  • 数据类型
    • 基本数据类型
    • String


  • 集合数据类型
  • hdfs支持的文件压缩格式

数据库操作

  • 与mysql类似
  • 可以用like
    • show databases like "h.*"


  • if not exists

表操作

  • describe table
    • 查勘表的情况


  • show partitions XXX
    • 显示XXX的分区情况
  • create teble XXX like XXX

建表

建表语法 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] [LOCATION hdfs_path]

说明： 1、CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。 2、EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。 3、LIKE 允许用户复制现有的表结构，但是不复制数据。 4、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, ...)] 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。 5、STORED AS SEQUENCEFILE|TEXTFILE|RCFILE 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

6、CLUSTERED BY 对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

  • create table
  • (name: type, ..)
    • 属性


  • ROW FORMAT DELIMITED
    • 指定下面的分隔符
    • 要先有这个
  • FIELDS TERMINATED By '\001'
    • 每列之间分割


  • COLLECTION ITEMS TERMINATED by '\002'
    • list之间
  • MAP Keys TERMINATED by '\003'
    • map之间


  • Lines TERMINATED by '\n'
    • 不需要粉色的 声明开头
    • 每行之间
  • PARTITIONED by (year int,day int)
    • 划分分区


  • CLUSTERED BY(userid) SORTED BY(viewTime) INTO n BUCKETS
  • STORED as
    • 存储的文件格式
    • 默认 TEXTFILE
    • SEQUENCEFILE
    • RECFILE
    • 还可以 自己制定 InputFormat 和OutputFormat
  • LOCATION ''"
    • 存储在哪里
    • 默认是 /user/hive/warehouse

查询

  • select中 可以使用 array下标和 map[key]
  • case when then
    • if语句
    • CASE when then when then


  • group having
  • join
    • inner
    • left
    • outer
    • join优化
      • mapside join
  • sort by order by
  • distribute by
    • 控制reducer如何划分
    • 可以和sort by一起用


  • limit

函数类型

  • 数学函数
    • round
      • 近似值


    • abs
  • 聚合函数
    • count
    • max min avg sum
    • hive.map.aggr


  • 生成函数
    • explode
      • 返回多个元素
    • stack
      • m列转为n行


  • 其他
    • cast( as )
      • 类型装换
    • length
    • split
    • ltrim
    • lower
    • repeat
      • 重复n次

数据操作

  • load data [ local] inpath "XXX" [overwrite] into table XXX [Partition (...)]

2.2.1Load 语法结构 LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明： 1、Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。 2、filepath： 相对路径，例如：project/data1 路径，例如：/user/hive/project/data1 包含模式的完整 URI，列如： hdfs://namenode:9000/user/hive/project/data1 3、LOCAL关键字 如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。 如果没有指定 LOCAL 关键字，则根据inpath中的uri[如果指定了 LOCAL，那么： load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。 load 命令会将 filepath中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。

如果没有指定 LOCAL 关键字，如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。 否则：如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。 如果路径不是的，Hive 相对于/user/进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中。]查找文件

4、OVERWRITE 关键字 如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。 如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

- 没有local的话 就是在hdfs中, 会将其移动过去

相关文章