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);

巴特西

HIve的基本使用

最新文章

热门文章