文档章节

hive 概览及dml语句

枯藤KT
 枯藤KT
发布于 2016/08/03 16:30
字数 1866
阅读 46
收藏 0

Hive Tutorial
数据单元:
分区:
    每一个表可以有一个或多个分区列,用来决定数据如何存储。分区不仅仅是存储单元,而且允许用户按照条件组织分类数据,分区键列中每一个不重复的值定义一个表的分区。分区可以极大的提高数据分析的速度。一个分区列就是一个伪列,所以分区列名可以自由设置,分区列的名称不可以和表中某一实际列的名称相同。


Buckets(Clusters):
    表中或每一个分区中的数据可以被分隔成多个Buckets,分隔方式是依据表中的一些列的hash值,这些列可以用clustered by指定,这些列中相同的值会被存储到同一个文件中,并且可以通过sorted by 设置排序列和排序方式,这样的文件就称为bucket。表级别可以有bucket,分区下面也可以有bucket,这个时候分区和表的概念可以认为相同。bucket的特点:一个bucket文件中的数据某一列的值相同,并且可以依据某一列有序,前提是buckets的数目要和clustered by指定的列可能的取值数相同(个人理解)。

数据类型:
    (包括各类型的长度,继承转化关系以及复杂类型构建)
    https://cwiki.apache.org/confluence/display/Hive/Tutorial

内置操作符和内置函数:
    在beeline命令行中查看:
        show functions; 显示函数列表
        describe function  function_name; 函数的简单描述
        describe function extended function_name; 函数的详细描述
    str rlike regexp: Returns true if str matches regexp and false otherwise
    常用函数:round floor  ceil rand concat substr upper  lower trim ltrim rtrim regexp_replace size from_unixtime to_date get_json_object 
    内置的聚合函数:
        count(*):返回记录行总数,包括NULL值
        count(expr): 返回expr不为null的行总数
        count(distinct expr) 返会expr不为NULL并且值唯一的行的总数
        avg(col)、avg(distinct col) 
        sum(col)、sum(distinct col)

Hive Sql的功能:
    where语句过滤结果
    select选择特定的列
    表间的连接
    对group by分组求值
    把查询结果存储到一个表中
    下载hive表中的内容到本地目录中
    把查询结果保存到hdfs目录中
    管理表和分区
    在自定义的MR任务中,插入自定义的脚本

加载数据:
    创建一个external表,指向hdfs中一个指定的位置,并提供数据行格式的信息,用户可以使用hdfs的put或copy命令,把文件放到指定的那个位置。
    举例:
        如果/tmp/pv_2008-06-08.txt包含逗号分隔的行,要加载到page_view表中,可以依次执行:
        CREATE EXTERNAL TABLE page_view_stg(...)
        ROW FORMAT DELIMITED FIELDS TERMINATED BY '44' LINES TERMINATED BY '12'
        STORED AS TEXTFILE
        LOCATION '/user/data/staging/page_view';

        hadoop dfs -put /tmp/pv_2008-06-08.txt /user/data/staging/page_view

        FROM page_view_stg pvs
        INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US')
        SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip
        WHERE pvs.country = 'US';
    这种方式适合hdfs中已经有的数据,需要使用hive来查询,因此需要向hive注册数据的元数据信息。

    除此之外,还支持直接从操作系统中加载数据到hive表中:
        LOAD DATA LOCAL INPATH /tmp/pv_2008-06-08_us.txt INTO TABLE page_view PARTITION(date='2008-06-08', country='US')
    inpath参数取值:一个目录(目录下的所有文件被加载,不会递归子目录)、一个文件、通配符(仅仅匹配文件名)
    
    从hdfs中的文嘉中加载数据到hive中:
        LOAD DATA INPATH '/user/data/pv_2008-06-08_us.txt' INTO TABLE page_view PARTITION(date='2008-06-08', country='US')

查询和插入数据:
简单查询:
    INSERT OVERWRITE TABLE user_active 
    SELECT user.*
    FROM user
    WHERE user.active = 1;
    所有的查询结果都会插入一个指定的表中,select * from users;查询结果在内部会写入到一些临时文件中。
分区查询:
    查询时在where子句中,包含创建表时指定的分区字段作为条件。
连接join:
    (left outer、right outer、full outer)
    INSERT OVERWRITE TABLE pv_users
    SELECT pv.*, u.gender, u.age
    FROM user u JOIN page_view pv ON (pv.userid = u.id)
    WHERE pv.date = '2008-03-03';
    检查一个key在另外一个表中是否存在 LEFT SEMI JOIN:
        INSERT OVERWRITE TABLE pv_users
        SELECT u.*
        FROM user u LEFT SEMI JOIN page_view pv ON (pv.userid = u.id)
        WHERE pv.date = '2008-03-03';
    多表连接:
        INSERT OVERWRITE TABLE pv_friends
        SELECT pv.*, u.gender, u.age, f.friends
        FROM page_view pv JOIN user u ON (pv.userid = u.id) JOIN friend_list f ON (u.id = f.uid)
        WHERE pv.date = '2008-03-03';
    连接时把包含大数据量的表放到右边,可以提高性能,仅支持等同连接。
聚合查询:
    INSERT OVERWRITE TABLE pv_gender_sum
    SELECT pv_users.gender, count (DISTINCT pv_users.userid)
    FROM pv_users
    GROUP BY pv_users.gender;

    使用多个聚合函数,每一个聚合函数中,distinct后指定的列必须相同:
    INSERT OVERWRITE TABLE pv_gender_agg
    SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)
    FROM pv_users
    GROUP BY pv_users.gender;
多表多文件插入:

FROM pv_users
    INSERT OVERWRITE TABLE pv_gender_sum
    SELECT pv_users.gender, count_distinct(pv_users.userid)
    GROUP BY pv_users.gender    --查询结果插入到hive表中
 
    INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv_age_sum'
    SELECT pv_users.age, count_distinct(pv_users.userid)
    GROUP BY pv_users.age;  --查询结果放到hdfs文件系统中

    如果数据要存到多个不同的分区:
    multi-insert:
    FROM page_view_stg pvs
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US')
       SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'US'
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='CA')
       SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'CA'
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='UK')
       SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'UK';
    这种方式,每次增加一个新的国家,都要修改代码增加一个insert分支语句,而且因为每一个insert可能被当作一个Map/Reduce任务,效率低。

    下面这种方式会根据数据内容自动将数据插入到对应分区中,如果分区不存在会自动创建,而且只有一个MR任务,效率高。
    Dynamic-partition insert:
    FROM page_view_stg pvs
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
       SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.country

    两者的不同点:
        country出现在partition中,但是不指定具体值,这表明country是一个动态分区列,dt指定了值,是一个静态分区列。如果一个列是动态分区列,那么它的值取自数据。动态分区列要放到partition子句的最后,如PARTITION(dt='2008-06-08',country),因为这个顺序表明了分区的层次,这个例子说明country是dt分区下面的子分区。
        select子句中要把动态分区列指定为查询列。
总结:
    同一分区下,如果数据相同,会覆盖分区数据,因为 OVERWRITE TABLE。
    hive分区类似hdfs目录概念,所以分区名要规范,如有特殊字符会转换成%dd格式,如果不是string类型,会转换为string之后作为分区名,在hdfs上创建目录。
    如果输入列是null或者空字符串,数据会放到一个特殊的分区中,由参数hive.exec.default.partition.name指定,默认值为HIVE_DEFAULT_PARTITION{}。
    动态创建分区可能短时间创建大量分区:
        hive.exec.max.dynamic.partitions.pernode
        默认100,一个mapreduce任务中最多能创建的分区数目
        hive.exec.max.dynamic.partitions
        默认1000,一个dml语句能创建的最大动态分区数目。
        hive.exec.max.created.files
        默认100000,所有的MR任务可以创建的文件总数
    只指定动态分区列,未指定静态分区列:
        hive.exec.dynamic.partition.mode=strict
        该模式下,必须要指定一个静态分区列
        hive.exec.dynamic.partition=true/false
        启用或禁用动态分区列,默认false

数据保存到本地:
    INSERT OVERWRITE LOCAL DIRECTORY '/tmp/pv_gender_sum'
    SELECT pv_gender_sum.*
    FROM pv_gender_sum;

union all、array、map的操作,见官网。
 

© 著作权归作者所有

上一篇: 浏览器跨域
枯藤KT

枯藤KT

粉丝 4
博文 104
码字总数 62438
作品 0
昌平
程序员
私信 提问
Impala SQL 语言参考

Impala SQL 语言参考 Cloudera Impala 的查询语言是基于 SQL 的。为了保护用户在技能和查询设计方面的已有投资,Impala 提供与 Hive 查询语言(HiveQL)的高度兼容: 因为使用与 Hive 记录表结...

weiqingbin
2013/12/20
11.1K
1
Hadoop Hive sql 语法详解

Hive 是基于Hadoop 构建的一套数据仓库分析系统,它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功...

luanpeng825485697
2018/05/30
0
0
hive视频教程|Hive入门及实战演练(2017版本)

Hive入门及实战演练(2017版本) 网盘地址:https://pan.baidu.com/s/10RZX4Eqrrn8F1MfXrkFM5g 密码: ecbv 备用地址(腾讯微云):https://share.weiyun.com/5rDivzP 密码:xsdfyq hive是基于...

人气王子333
2018/04/18
0
0
hive(05)、使用JAVA对数据仓库HIVE进行操作

本文代码码云地址:https://gitee.com/MaxBill/HSDP 在前文中我们实践了基于hadoop的数据仓库hive的安装、配置、应用、扩展等,那么我们在实际中该如何通过程序调用(用户接口)开发呢,hiv...

MaxBill
2018/01/16
760
0
Hive学习系列一--安装部署

Hive的认识 Hive 是基于Hadoop 构建的一套数据仓库分析系统,它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据,可以将结构化的数据文件映射为一张数据库表,并提供完整...

乔康007
2016/06/14
21
0

没有更多内容

加载失败,请刷新页面

加载更多

java通过ServerSocket与Socket实现通信

首先说一下ServerSocket与Socket. 1.ServerSocket ServerSocket是用来监听客户端Socket连接的类,如果没有连接会一直处于等待状态. ServetSocket有三个构造方法: (1) ServerSocket(int port);...

Blueeeeeee
今天
6
0
用 Sphinx 搭建博客时,如何自定义插件?

之前有不少同学看过我的个人博客(http://python-online.cn),也根据我写的教程完成了自己个人站点的搭建。 点此:使用 Python 30分钟 教你快速搭建一个博客 为防有的同学不清楚 Sphinx ,这...

王炳明
昨天
5
0
黑客之道-40本书籍助你快速入门黑客技术免费下载

场景 黑客是一个中文词语,皆源自英文hacker,随着灰鸽子的出现,灰鸽子成为了很多假借黑客名义控制他人电脑的黑客技术,于是出现了“骇客”与"黑客"分家。2012年电影频道节目中心出品的电影...

badaoliumang
昨天
14
0
很遗憾,没有一篇文章能讲清楚线程的生命周期!

(手机横屏看源码更方便) 注:java源码分析部分如无特殊说明均基于 java8 版本。 简介 大家都知道线程是有生命周期,但是彤哥可以认真负责地告诉你网上几乎没有一篇文章讲得是完全正确的。 ...

彤哥读源码
昨天
15
0
jquery--DOM操作基础

本文转载于:专业的前端网站➭jquery--DOM操作基础 元素的访问 元素属性操作 获取:attr(name);$("#my").attr("src"); 设置:attr(name,value);$("#myImg").attr("src","images/1.jpg"); ......

前端老手
昨天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部