|
|
51CTO旗下网站
|
|
移动端

Hive SQL常用命令总结,大数据开发人员按需收藏

Hive是基于Hadoop生态的一个重要组件,是对数据仓库进行管理和分析数据的工具。她提供了SQL查询方式来分析存储在HDFS分布式文件系统中的数据,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功能。

作者:技术大咖秀来源:今日头条|2019-08-06 14:14

Hive是基于Hadoop生态的一个重要组件,是对数据仓库进行管理和分析数据的工具。她提供了SQL查询方式来分析存储在HDFS分布式文件系统中的数据,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功能。

这种SQL就是Hive SQL,她可以将SQL语句转换为Map Reduce任务运行,通过特殊的SQL去查询分析需要的内容,使不熟悉map reduce的用户很方便的利用SQL 语言查询,汇总,分析数据。

Hive SQL常用命令总结,大数据开发人员按需收藏

一、基础命令

1、数据库操作

  • show databases; # 查看某个数据库
  • use 数据库; # 进入某个数据库
  • show tables; # 展示所有表
  • desc 表名; # 显示表结构
  • show partitions 表名; # 显示表名分区
  • show create table_name; # 显示创建表的结构

2、表结构修改

  • use xxdb; create table xxx; # 内部表
  • create table xxx like xxx; # 创建一个表,结构与其他一样
  • use xxdb; create external table xxx; # 外部表
  • use xxdb; create external table xxx (l int) partitoned by (d string); # 分区表
  • alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 内部表转外部表
  • alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表转内部表

3、字段类型

  • 基本类型: tinyint, smallint, int, bigint, float, decimal, boolean, string
  • 复合类型:struct, array, map

二、常用函数

  • length() # 返回字符串长度
  • trim() # 去除两边空格
  • lower(), upper() # 大小写转换
  • reverse() # 反转字符串
  • cast(expr as type) # 类型转换
  • substring(string A, int start, int len) # 字符串截取
  • split(string str, string pat) # 按照pat字符串分割str,返回分割后的字符串数组
  • coalesce(v1, v2, v3, ...) # 返回列表中第一个非空元素,如果所有值都为空,则返回null
  • from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') # 返回当前时间
  • instr(string str, string search_str) # 返回第二个参数在待查找字符串中的位置(找不到返回0)
  • concat(string A, string B, string C, ...) # 字符串连接
  • concat_ws(string sep, string A, string B, string C, ...) # 自定义分隔符sep的字符串连接
  • str_to_map(string A, string item_pat, string dict_pat) # 将字符串转为map
  • map_keys(map m) # 提取出map的key, 返回key的array
  • datediff(date1, date2) # 日期比较函数,返回相差天数,datediff('${cur_date},d)
  • explode(colname) # explode就是将hive一行中复杂的array或者map结构拆分成多行

三、相关概念

1、hive

hive是基于hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库库表,并提供类SQL查询功能。

2、基本组成

用户接口:CLI,shell命令行;JDBC/ODBC是hive的java实现;webGUI是通过浏览器访问hive;元数据存储:通常是存储在关系数据库如mysql, derby中;hive的元数据包括表的名字,表的列和分区及其属性,表的属性(是否为外部表),表的数据所在目录等。

解释器,编译器,优化器完成HQL查询语句从词法分析,语法分析,编译,优化以及查询计划的生成。生成的查询存储在HDFS中,并随后有mapreduce调用执行。因此,hive与Hadoop的关系可以理解为用户发出SQL查询语句,hive将查询存储在HDFS中,然后由mapreduce调用执行。

3、table

Hive 中的 Table 和数据库中的 Table 在概念上是类似的,每一个 Table 在 Hive 中都有一个相应的目录存储数据。例如,一个表 pvs,它在 HDFS 中的路径为:/wh/pvs,其中,wh 是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的数据仓库的目录,所有的 Table 数据(不包括 External Table)都保存在这个目录中。

4、partition

Partition 对应于数据库中的 Partition 列的密集索引,但是 Hive 中 Partition 的组织方式和数据库中的很不相同。在 Hive 中,表中的一个 Partition 对应于表下的一个目录,所有的 Partition 的数据都存储在对应的目录中。

5、buckets

Buckets 对指定列计算 hash,根据 hash 值切分数据,目的是为了并行,每一个 Bucket 对应一个文件。将 user 列分散至 32 个 bucket,首先对 user 列的值计算 hash,对应 hash 值为 0 的 HDFS 目录为:/wh/pvs/ds=20090801/ctry=US/part-00000;hash 值为 20 的 HDFS 目录为:/wh/pvs/ds=20090801/ctry=US/part-00020

6、external table

External Table 指向已经在 HDFS 中存在的数据,可以创建 Partition。它和 Table 在元数据的组织上是相同的,而实际数据的存储则有较大的差异。

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

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

7、全量数据和增量数据

查看分区信息:如果分区的大小随时间增加而增加,则最新的分区为全量数据。如果分区的大小随时间增加而大小上下变化,则每个分区都是增量数据。

四、HQL和SQL的异同

1、HQL和SQL常见不同,

  • select distinct 后必须指定字段名
  • join 条件仅支持等值关联且不支持or条件
  • 子查询不能在select中使用;
  • HQL中没有UNION,可使用distinct+ union all 实现 UNION;
  • HQL以分号分隔,必须在每个语句结尾写上分号;
  • HQL中字符串的比较比较严格,区分大小写及空格,因此在比较时建议upper(trim(a))=upper(trim(b))
  • 日期判断,建议使用to_date(),如:to_date(orderdate)=‘2016-07-18’
  • 关键字必须在字段名上加``符号,如select `exchange` from xxdb.xxtb;
  • 数据库和表/视图之间仅有1个点,如xx_db.xx_tb。

2、HQL不支持update,采用union all + left join (is null)变相实现update。

  • 取出增量数据;
  • 使用昨日分区的全量数据通过主键左连接增量数据,并且只取增量表中主键为空的数据(即,取未发生变化的全量数据);
  • 合并1、2的数据覆盖至最新的分区,即实现了update。

3、HQL不支持delete,采用not exists/left join(is null)的方法变相实现。

  • 取出已删除的主键数据(表B);
  • 使用上一个分区的全量数据(表A)通过主键左连接A,并且只取A中主键为空的数据,然后直接insert overwrite至新的分区。

对于会SQL的人员,转入Hive SQL还是比较容易的,语法大部分是想通的,少部分函数不太一致。

【编辑推荐】

  1. 如何从MongoDB迁移到MySQL?这有现成经验!
  2. PostgreSQL之时间戳自动更新
  3. Adaptive Execution 让 Spark SQL 更智能更高效
【责任编辑:未丽燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

343人订阅学习

20个局域网建设改造案例

20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

666人订阅学习

WOT2019全球人工智能技术峰会

WOT2019全球人工智能技术峰会

通用技术、应用领域、企业赋能三大章节,13大技术专场,60+国内外一线人工智能精英大咖站台,分享人工智能的平台工具、算法模型、语音视觉等技术主题,助力人工智能落地。
共50章 | WOT峰会

0人订阅学习

读 书 +更多

Fedora Core 5 Linux系统安装与管理

以Linux为代表的自由软件及其稳定性,逐渐在全世界崭露头角且备受重视。由于可以支持多种网络环境,因此在采用Linux系统之前,必须熟悉各种...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO播客