Day15_20180503_链家二手房信息案例分析

一、回顾
    -》Hive：为用户提供SQL接口来操作Hadoop的工具
        -》将hdfs上的文件映射成表
        -》将SQL语句转换为MapReduce程序提交给yarn
    -》hive的使用
        -》client：HQL
        -》-e：在Linux shell中执行一条命令
        -》-f：在Linux shell中执行一个sql文件
         注意：实际工作中
            -》使用脚本，将SQL语句封装到一个SQL文件
                在脚本中使用bin/hive -f xxxx.sql
            -》jdbc：cs
                java app -> hive server
            -》 beeline：统一jdbc客户端
                -》启动hiveserver2:10000
                    bin/hiveserver2
                -》启动beeline去连接hiveserver2 
                    jdbc:hive2://hostname:10000
                    !connect jdbc:hive2://hostname:10000
                    rdedu
                    rdedu
        -》web UI
            -》启动hiveserver2，默认web端口：9999
                在哪台机器启动hiveserver，你的web就在哪台机器
            -》需要自己编译hive，生成war包
                -》将war包放入lib目录下即可
    -》问题
        -》hive启动没有成功加载自定义的配置
            -》你的hive_conf_dir
                export HIVE_CONF_DIR=/opt/modules/hive-1.2.1-bin/conf
        -》mysql的连接配置
            mysql> select user,host from user;
                +------+------+
                | user | host |
                +------+------+
                | root | %    |
                +------+------+                
    -》hive元数据的三种存储方式
        -》derby
        -》本地元数据库（当前）：c -> s(元数据) 
            server访问元数据库
        -》远程元数据库（公司）：多个框架共同使用hive的元数据
            spark SQL、impala共享hive元数据库（表对应hdfs的位置）
            server访问元数据服务
            c -> s -> metastore(服务)
                 c ->  s       
二、表的类型

第一种：
CREATE  TABLE IF NOT EXISTS tb_name(
col1 type,
col2 type,
col3 type,
……
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION 'HDFS_PATH';

第二种：as
CREATE TABLE tb2 as SELECT …… FROM tb1;

第三种：like
CREATE TABLE tb2 like tb1; 只复制表结构

强制删除非空数据库：drop database empmanager cascade;

外部表和管理表：

  -》区别：在删除时
        -》外部表：只删除元数据，没有删除表目录
        -》管理表：既删除元数据也删除表目录
    -》在工作中：一般都使用外部表
        -》对于同一份数据文件，我们会创建多个表，供于多个业务分析
        -》在对同一个数据文件目录创建多个外部表时，使用location来指定

创建外部表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [COMMENT col_comment],
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [ROW FORMAT row_format] 
  [STORED AS file_format]
  [LOCATION hdfs_path]

类型：
EXTERNAL：外部表
hive (empmanager)>    

create external table dept_ext(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t';
load data local inpath '/opt/datas/dept.txt' into table dept_ext;

MANAGED_TABLE ：管理表（默认）
hive (empmanager)>  

create table dept_mgr(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t';
load data local inpath '/opt/datas/dept.txt' into table dept_mgr;

验证： 

[root@bigdata-training01 hadoop-2.6.0-cdh5.7.6]#bin/hdfs  dfs -put /opt/datas/dept.txt /user/hive/warehouse/empmanager.db/dept_ext/dept2.txt;

Permission    Owner    Group    Size    Last Modified    Replication    Block Size    Name
-rwxr-xr-x    rdedu    supergroup    79 B    2019/9/7 下午4:25:47    1    128 MB    dept.txt
-rw-r--r--      rdedu    supergroup    79 B    2019/9/7 下午4:32:16    1    128 MB    dept2.txt

hive (empmanager)>

select * from  dept_ext;

dept_ext.deptno dept_ext.dname  dept_ext.loc
10               ACCOUNTING      NEW YORK
20               RESEARCH        DALLAS
30               SALES           CHICAGO
40               OPERATIONS      BOSTON
10               ACCOUNTING      NEW YORK
20               RESEARCH        DALLAS
30               SALES           CHICAGO
40               OPERATIONS      BOSTON 
 

#外部表通过location来指定导入的文件
create external table dept_ext1(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/test/dept';       
        
create external table dept_ext2(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/test/dept';  

  使用hive等SQL on hadoop 工具
  SQL：子查询，join
        rdbms：相对来说较难
        大数据：所有的业务都可以进行拆分
            -》先将内部查询的结果报错
            -》再对结果进行外部查询操作
https://tech.meituan.com/user-funnel-analysis-design-build.html

select 
    count (distinct t1.id1), 
    count (distinct t2.id2), 
    count (distinct t3.id3) 
from (
    select 
        uuid id1, timestamp ts1 
    from 
        data 
    where 
        timestamp >= 1510329600 and timestamp < 1510416000 and page = '首页'
        ) t1
    left join
    (select 
        uuid id2, timestamp ts2 
    from 
        data 
    where 
        timestamp >= 1510329600 and timestamp < 1510416000 and page = '搜索' and keyword = '中餐'
    ) t2
    on 
        t1.id1 = t2.id2 and t1.ts1 < t2.ts2 and t2.ts2 - t1.ts1 < 3600
    left join
    (select 
        uuid id3, timestamp ts3 
    from 
        data 
    where 
        timestamp >= 1510329600 and timestamp < 1510416000 and page = '菜品'
    ) t3
    on t1.id1 = t3.id3 and t2.ts2 < t3.ts3 and t1.ts1 < t3.ts3 and t3.ts3 - t1.ts1 < 3600

kylin/impala
链家上海二手房分析
https://zhuanlan.zhihu.com/p/24802969
数据获取及加载

DROP DATABASE IF EXISTS second_house;
CREATE DATABASE second_house;
USE second_house;

数据源表
小区  房型  面积  地区          楼层  朝向  总价  单价  建造年份
梅园六街坊,2室0厅,47.72,浦东,低区/6层,朝南,500,104777,1992年建

CREATE TABLE 2nd_sh_house(
village_name string,
house_type string,
house_area double,
region string,
house_floor string,
direction string,
total_price double,
single_price double,
build_date string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

load data local inpath '/opt/datas/2nd_house_price.csv' into table 2nd_sh_house;

数据的清洗：ETL：数据业务表
    -》字段过滤
    -》字段补全:
    -》字段格式化

数据分析统计
 1、户型分布
房型
   一室  二室  三室  四室  五室  五室以上  

##设置本地模式会快一点
set hive.exec.mode.local.auto;
set hive.exec.mode.local.auto=true;

select a.house_type2 ,count(1) as number from (
SELECT 
    village_name,house_type,CASE
    WHEN substring(house_type,0,1) = 1 THEN '一室'
    WHEN substring(house_type,0,1) = 2 THEN '两室'
    WHEN substring(house_type,0,1) = 3 THEN '三室'
    WHEN substring(house_type,0,1) = 4 THEN '四室'
    WHEN substring(house_type,0,1) = 5 THEN '五室'
    WHEN substring(house_type,0,1) > 5 THEN '五室以上'
    ELSE '不合法'
    END AS house_type2
FROM
    2nd_sh_house
) as a 
GROUP BY 
    a.house_type2
order by 
    number desc;

a.house_type2   number
两室                   13534
三室                    7456
一室                    5210
四室                    1414
五室                    444
五室以上             143
Time taken: 8.518 seconds, Fetched: 6 row(s)

2、二手房的面积和房价的分布
面积  50平以下   50-70平  70-90平  90-110平 110-130平    130-150平 150平以上

SELECT 
    village_name,house_area,CASE
    WHEN house_area  > 150 THEN '150平以上'
    WHEN house_area  > 130 THEN '130-150平'
    WHEN house_area  > 110 THEN '110-130平'
    WHEN house_area  > 90 THEN '90-130平'
    WHEN house_area  > 70 THEN '70-90平'
    WHEN house_area  > 50 THEN '50-70平'
    ELSE '50平以下'
    END AS area_type
FROM
    2nd_sh_house limit 10;

village_name                                house_area      area_type
梅园六街坊                                     47.72              50平以下
碧云新天地（一期）                       108.93            90-130平
博山小区                                         43.79               50平以下
金桥新村四街坊（博兴路986弄）   41.66              50平以下
博山小区                                         39.77               50平以下
潍坊三村                                         34.84               50平以下
伟莱家园                                         100.15             90-130平
世茂滨江花园                                  260.39            150平以上
羽北小区                                         69.88               50-70平
证大家园（公寓）                          122.75             110-130平
Time taken: 0.165 seconds, Fetched: 10 row(s)