Hive命令学习记录

常用命令

1、查看数据库 show databases;

2、使用某个数据库默认是default

      use mydatabase;

3、查看数据表

     show tables;

4、查询数据

    select * from tmp limit 1;

    内链接查询：SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

    左连接SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);

    右连接SELECT sales.*, things.* FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);

    全连接SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id);

5、创建数据表
CREATE TABLE `demo` (
    `id` BIGINT,
    `title` STRING,
    `adddate` TIMESTAMP
)
PARTITIONED BY (date STRING)//以日期作为分区名字
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' //以\001为分隔符
STORED AS TEXTFILE;//以文本的形式存储

6、把本地文件的数据导入到demo表中
//注意：分隔符要保持一致，不然导入后全部都是null值
LOAD DATA LOCAL INPATH '/home/tmp/demo.hql' [OVERWRITE] INTO TABLE `demo`;

7、把查询结果写入新表里面

INSERT OVERWRITE TABLE ..SELECT：新表预先存在
FROM records2
    INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year
    INSERT OVERWRITE TABLE records_by_year SELECT year, COUNT(1) GROUP BY year
    INSERT OVERWRITE TABLE good_records_by_year SELECT year, COUNT(1) WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9) GROUP BY year;  

CREATE TABLE ... AS SELECT：新表表预先不存在
CREATE TABLE target AS SELECT col1,col2 FROM source;

insert overwrite table rpt_chnl_visits_daily_dtl_1 partition(date='{$date}')
select
    utm.partner_id partner_id,
    utm.group_id group_id,
    t1.utmid utm_id,
    t1.site_id site_id,
    nvl(t1.uv,0) uv_num,
    nvl(t1.visit_num,0) visits_num,
    nvl(t1.pv,0) pv_num,
    nvl(t1.next_click_sids,0) next_click_sids,
    nvl(t1.avg_page_num,0) avg_pv_num,
    nvl(t1.avg_stay_time,0) avg_stay_time,
    nvl(t3.reg_num,0) aegister_users,
    nvl(t2.jump_person,0) jump_uv,
    nvl(t2.jump_num,0) jump_pv
from tmp_pc_rpt_chnl_visits_daily_dtl_1_1 t1
    left outer join tmp_pc_rpt_chnl_visits_daily_dtl_1_2 t2
        on t1.utmid = t2.utmid and   t1.site_id =t2.site_id
    left outer join tmp_pc_rpt_chnl_visits_daily_dtl_1_3 t3
        on t1.utmid = t3.utmid and   t1.site_id =t3.site_id
    left outer join default.js_partner_utm utm
        on utm.id = t1.utmid;