当前位置 博文首页 > 文章内容

    Hive

    作者: 栏目:未分类 时间:2020-07-10 18:09:29

    本站于2023年9月4日。收到“大连君*****咨询有限公司”通知
    说我们IIS7站长博客,有一篇博文用了他们的图片。
    要求我们给他们一张图片6000元。要不然法院告我们

    为避免不必要的麻烦,IIS7站长博客,全站内容图片下架、并积极应诉
    博文内容全部不再显示,请需要相关资讯的站长朋友到必应搜索。谢谢!

    另祝:版权碰瓷诈骗团伙,早日弃暗投明。

    相关新闻:借版权之名、行诈骗之实,周某因犯诈骗罪被判处有期徒刑十一年六个月

    叹!百花齐放的时代,渐行渐远!



    Hive数据仓库

    Facebook由hive和Hadoop组建

    hive由facebook开发的

    存储HDFS,查询MapReduce

    优势

    • 解决了传统关系型数据库在大数据处理上的瓶颈。适合大数据批量处理

    • 充分利用集群的CPU计算资源,存储资源,实现并行计算

    • Hive支持标准的SQL语法,免去了编写MR程序的过程,减少了开发成本

    • 良好的扩展性,扩展功能方便

    劣势

    • HQL表达能力有限:有些复杂运算用HQL不易表达

    • 效率低:Hive自动生成MR作业,通常不够智能

    不适于机器学习

    MySQL服务
     service mysql atart & chkconfig mysqld on

    启动MySQL服务并且设置每次开机都启动MySQL服务

    启动hive服务
     hive --service metastore &

    HQL

    执行方式

    hive 命令行

    hive -e hive语句

    hive -f hive脚本

    案例

    泰坦尼克号乘客储存与分析

    1594106585173

    1594106859455

    !开头;结束

    查看当前数据库位置

    1594127650557

    表的数据类型

     类型描述举例
    基础数据类型 TINYINT 1byte有符号整型 20
    SMALLINT 2byte有符号整型 20  
    INT 4byte有符号整型 20  
    BIGINT 8byte有符号整型 20  
    BOOLEAN 布尔类型 True  
    FLOAT 单精度浮点型 3.14159  
    DOUBLE 双精度浮点型 3.14159  
    STRING(CHAR、VARCHAR) 字符串 ‘hello world’  
    TIMESTAMP(DATE) 时间戳 1327882394  
    BINARY 字节数组 01  
     类型描述举例
    复杂数据类型 ARRAY 一组有序字段,字段的类型必须相同 user[1]
      MAP 一组无序的键/值对,键的类型必须是原子的,值可以是任何类型。同一个映射的键类型必须相同,值类型也必须相同 user[‘name’]
      STRUCT 一组命名的字段,字段类型可以不同 user.age

    创建内表语句

    create table person( > id int, > name string, > age int) > row format delimited fields terminated by ','; 以 ‘,’分割

     

    外部表

    创建外表指定路径/user/root/external/person

    create external table external(

    id int,

    name string,

    age int)

    row format delimited fields terminated by ','

    location '/user/root/external/external_person';

    案例实现

    泰坦尼克号,建表

    create table tidanic( > passengerid int, > survived int, > pclass string, > name string, > sex int, > age int, > sibsp int, > parch int, > ticket string, > fare double, > cabin string, > embarked string) > row format delimited fields terminated by ',';

    查看十条数据

    select * from tidanic limit 10;

    静态分区表

    建表语句

     hive> create table tidanic_part(
        > passengerid int,
        > survived int,
        > pclass string,
        > name string)
        > partitioned by(gender string) #指定分段为gender
        > row format delimited fields terminated by ',';

    放入数据

     insert overwrite table tidanic_part partition(gender='female') select passengerid,survived,pclass,name from tidanic where sex='female';

    动态分区表

    建表

     hive> create table tidanic_dynamic_part(
        > passengerid int,
        > survived int,
        > name string)
        > partitioned by(passenderclass string) #指定分段为gender
        > row format delimited fields terminated by ',';

    设置

     hive> set hive.exec.dynamic.partition=true;
     hive> set hive.exec.dynamic.partition.mode-nostrict;

    设置字段

     set hive.exec.dynamic.partition.mode=nostrict;
     insert overwrite table tidanic_dynamic_part partition(passenderclass) select passengerid,survived,name,pclass from tidanic;

    创建带数据的表

     create table tidanic_10 as select * from tidanic limit 10;

    复制表结构

      create table tidanic_like like tidanic;

    桶表,按照年龄将数据分到四个桶,抽取两个桶的数据创建一个新的表

     hive> create table tidanic_bucket(
        > passengerid int,
        > name string,
        > age int)
        > clustered by (age) into 4 buckets
        > row format delimited fields terminated by ',';

    set hive.enforce.bucketing=true;

    导入导出数据

     LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
     LOCAL:导入语句带有LOCAL,说明是导入Linux本地的数据,若是从HDFS上导入数据,则导入语句不加LOCAL;
     filepath:数据的路径
     OVERWRITE:指定覆盖表之前的数据,如果是追加,则去掉OVERWRITE关键字即可。
     PARTITON:如果是分区表,指定分区字段的名称。
     LOAD DATA LOCAL INPATH '/opt/GoodsTypes.csv' OVERWRITE INTO TABLE goodtypes;

    吧HDFS数据整到表中

    1594185476143

    2.单表查询导入数据

     INSERT [OVERWRITE|INTO] table 表1 
     [PARTITION(part1=val1,part2=val2)] SELECT字段1, 字段2, 字段3 FROM 表2 ;

    3.多表查询导入数据

     FROM 表1 
     INSERT INTO TABLE 表2 SELECT 字段 LIMIT N
     INSERT INTO TABLE 表3 SELECT 字段 WHERE … ;
     
     查询tidanic表中的存活乘客数据到tidanic_save
     查询tidanic表中的死亡乘客数据到tidanic_died

    搞出10行数据放到tidanic_10中

     hive> insert into table tidanic_10 select * from tidanic_10;
     

    多表查询

    1.先建立两个表

    hive> insert into table tidanic_10 select * from tidanic_10;

    hive> insert into table tidanic_10 select * from tidanic_10;

     from tidanic
     insert overwrite table tidanic_save select * where survived=1
     insert overwrite table tidanic_died select * where survived=0;
     

    overwrite表示覆盖原先内容

    把不同的内容放在不同的表中,导入死亡名单

    Hive导出数据

     INSERT OVERWRITE [LOCAL] DIRECTORY '路径’ 
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ',’
     SELECT 字段1, 字段2, 字段3 FROM 表名 ;
     如果添加LOCAL关键字,那么导出的是本地目录,如果没有该关键字,那么导出的是HDFS目录;
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ','在这里指定导出数据的分隔符为‘,’。
     OVERWRITE LOCAL DIRECTORY 查询结果将覆盖本地目录

    1594188285931

    hive查询

    select语法

     SELECT  [ALL|DISTINCT]  字段列表(字段1 别名,....) 
     FROM 表1 别名, 表2 别名, ....
     WHERE 条件 ….
     GROUP BY 分组字段 HAVING(组约束条件)
     ORDER BY 排序字段1 Asc | Desc, 字段2 Asc|Desc, .....
     [CLUSTER BY 字段 | [DISTRIBUTE BY字段] [SORT BY字段]]
     LIMIT M,N;

    distinct去重

    order by 查询

    1594203198973

    1594203206404

    1594203421150

    分组

    查询死亡和存活的情况

    select count(*) as p_sout,survived from tidanic group by survived;

    关联查询
     select count(*) as p_sout,survived from tidanic group by survived;

    left outer join:左边表的所有记录连接结果会被返回,右边表没有符合on条件的,连接的右边的列为Null

    right outer join:右边表的所有记录连接结果会被返回,左边表没有符合on条件的,连接的左边的列为Null

    full outer join:返回两个表所有的记录连接结果

    left semi join:左半开边连接,只返回满足连接条件的左边记录,类似MySQL in

    1594204853290

    查询emp表中的员工姓名,薪水,如果薪水小于2000标记为low,如果薪水在2000和5000之间标记为middle,如果薪水大于5000标记为high

    1594205175693

    任务实现

    Ø统计性别与生存率的关系

    Ø统计客舱等级与生存率的关系

    Ø统计登船港口与生存率的关系

    select sex,count(*) as s_count from tidanic where survived=1 group by sex;

    1594213993830

    1594214150195

    1594214425805

    执行顺序

    目标:查询emp表平均薪水大于2000的部门编号、平均薪水并按照部门编号排序,不包括10部门

    Ø查询顺序

    SELECT deptno,avg(sal) as avg_sal FROM emp WHERE deptno<>10 GROUP BY deptno HAVING avg(sal)>2000 ORDER BY deptno;

    ØSQL执行顺序

    FROM ... WHERE ... GROUP BY ... AVG SUM 等聚合函数 ... HAVING ... SELECT... ORDER BY ...

    ØHive执行顺序

    FROM ... WHERE ... SELECT ... GROUP BY ... HAVING ... ORDER BY ...

    hive开发

    配置Java环境

    首先打开

     nohup hive --service hiveserver2 &

    查看1000端口是否运行

     netstat -nplt | grep 10000

    Java开发

         public static void main(String[] agrs) throws ClassNotFoundException, SQLException {
      String driverName ="org.apache.hive.jdbc.HiveDriver";
      String url="jdbc:hive2://192.168.128.130:10000/default";
      String username="root";
      String password="root";
      Class.forName(driverName);
      Connection connection = DriverManager.getConnection(url, username, password);
      Statement stmt = connection.createStatement();
      stmt.execute("CREATE DATABASE shopbucket");
      connection.close();
     
      }

    1594276985636

    1594339086896

     select t.types,t.goods,t.t_g_count from (select c.types,c.goods,count(1) t_g_count,row_number() over(partition by c.types order by count(1) desc) rank from(select a.*,b.types from goodsorder a left outer join goodstypes b on a.goods=b.goods) c group by c.types,c.goods) t where rank<=10;
    9.将同一个客户的订单整合为一列并写入HDFS

    1594343463203

    自定义函数

    UDF:用户定义函数

    UDF操作作用于单个数据行,并且产生一个数据行作为输出。大多数函数都属于这一类(比如数学函数和字符串函数)

    ØUDAF:用户定义聚集函数

    UDAF 接受多个输入数据行,并产生一个输出数据行。像COUNT和MAX这样的函数就是聚集函数。

    UDTF:用户定义表生成函数

    UDTF 操作作用于单个数据行,并且产生多个数据行。比如explode。

    创建临时函数

    1.上传自定义udf的jar到Linux

    2.在Hive CLI执行:add jar /opt/dateudf.jar;

    3.在Hive CLI执行:create temporary function datetotime as 'demo.DateUDF';

    创建永久函数

    1.把自定义函数的jar上传到hdfs中

    2.创建永久函数:

    create function datetotime as 'demo.DateUDF' using jar 'hdfs://master:8020/user/root/dateudf.jar’;

    3.删除函数:drop [temporary] function [if exists] [dbname.]函数名;

    优化

    索引

    CREATE INDEX index_name --索引名称

    ON TABLE base_table_name (col_name, ...) --建立索引的列

    AS index_type --索引类型

    [WITH DEFERRED REBUILD] --重建索引

    [IDXPROPERTIES (property_name=property_value, ...)] --索引额外属性

    [IN TABLE index_table_name] --索引表的名字

    [ [ ROW FORMAT ...] STORED AS ...

    | STORED BY ... ] --索引表行分隔符 、 存储格式

    [LOCATION hdfs_path] --索引表存储位置

    [TBLPROPERTIES (...)] --索引表表属性

    [COMMENT "index comment"]; --索引注释

    Ø显示表上所有列的索引

    SHOW FORMATTED INDEX ON table_name;

    Ø删除索引

    DROP INDEX [IF EXISTS] index_name ON table_name;

    自动使用索引

    SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

    SET hive.optimize.index.filter=true;

    SET hive.optimize.index.filter.compact.minsize=0;

    案例分析

    •公司收入的80%来自顶端的20%的客户。

    •20%的客户其利润率100%。

    •90%以上的收入来自现有客户。

    •大部分的营销预算经常被用在非现有客户上。

    •5%至30%的客户在客户金字塔中具有升级潜力。

    •客户金字塔中客户升级2%,意味着销售收入增加10%,利润增加50%。

    这些经验也许并不完全准确,但是它揭示了新时代客户分化的趋势,也说明了对客户价值分析的迫切性和必要性。

    数据预处理

    航空公司客户原始数据存在少量的缺失值和异常值,需要清洗后才能用于分析。

    通过对数据观察发现原始数据中存在票价为空值,票价最小值为0,折扣率最小值为0,总飞行公里数大于0的记录。

    Ø票价为空值的数据可能是客户不存在乘机记录造成。

    处理方法:丢弃票价为空的记录。

    Ø其他的数据可能是客户乘坐0折机票或者积分兑换造成。由于原始数据量大,这类数据所占比例较小,对于问题影响不大,因此对其进行丢弃处理。

    处理方法:丢弃票价为0,平均折扣率不为0,总飞行公里数大于0的记录。

     creat table sum_0_seg_avg_not_0 as select * from 表 where !(sum_yr_1=0 and avg_discount<>0 and seg_km_sum>0);