HIve的基本使用

• WHERE从表中筛选行；
• SELECT从表中查询指定的列；
• group by在列上做聚合。

-- 假设数据文件的内容，字段之间以ASCII 001（ctrl-A)分隔，行之间以换行分隔。
CREATE TABLE page_view
(viewTime INT, 
userid BIGINT,
page_url STRING, 
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User' -- 列级别的Comment
)
COMMENT 'This is the page view table' -- 表级别的Comment
PARTITIONED BY(dt STRING, country STRING) -- 分区
STORED AS SEQUENCEFILE;

-- 指定分隔符
CREATE TABLE page_view(viewTime INT, userid BIGINT,
                page_url STRING, referrer_url STRING,
                ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
        FIELDS TERMINATED BY '1'
STORED AS SEQUENCEFILE;

对已有表增加列：

ALTER TABLE tab1 ADD COLUMNS (c1 INT COMMENT 'a new int column', c2 STRING DEFAULT 'def val');

聚合:

• 两个聚合函数不能同时用DISTINCT作用于不同的列

-- （DISTINCT作用于不同的列）是不允许的
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)
FROM pv_users
GROUP BY pv_users.gender;

union all

SELECT u.id, actions.date
FROM (
    SELECT av.uid AS uid
    FROM action_video av
    WHERE av.date = '2008-06-03'

    UNION ALL

    SELECT ac.uid AS uid
    FROM action_comment ac
    WHERE ac.date = '2008-06-03'
    ) actions JOIN users u ON(u.id = actions.uid);