hive 基本操作，

hive 基本操作，

一、创建表


1、创建分区表：
CREATE TABLE page_view(viewTime INT,userid BIGINT,
page_url STRING, reffer_url STRING,
ip STRING COMMENT 'IP Address of user')
COMMENT 'this is table'
PARTITIONED BY(dt STRING,country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '001'
STORED AS SEQUENCEFILE;


2、聚类存储：
DROP TABLE page_view;
CREATE TABLE page_view(viewTime INT,userid BIGINT,
page_url STRING, reffer_url STRING,
ip STRING COMMENT 'IP Address of user')
COMMENT 'this is table'
PARTITIONED BY(dt STRING,country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '001'
COLLECTION ITEMS TERMINATED BY '002'
MAP KEYS TERMINATED BY '003'
STORED AS SEQUENCEFILE;


3、指定存储位置：
DROP TABLE page_view;
CREATE EXTERNAL TABLE page_external(viewTime INT,userid BIGINT,
page_url STRING, reffer_url STRING,
ip STRING COMMENT 'IP Address of user')
COMMENT 'this is table'
PARTITIONED BY(dt STRING,country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '054'
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/page_like';


4、LIKE创建已经存在的表结构：
CREATE TABLE page_like LIKE page;


二、修改表


1、重命名表：
ALTER TABLE page_view RENAME TO page;


2、修改列明，字段名，位置，注释：
CREATE TABLE test_chage(a INT,b INT,c INT);
ALTER TABLE test_chage CHANGE a t STRING;
ALTER TABLE test_chage CHANGE b m STRING AFTER t;
ALTER TABLE test_chage CHANGE m m1 BIGINT FIRST;


3、增加，更新新列：
ALTER TABLE page ADD COLUMNS (id STRING);
ALTER TABLE page REPLACE COLUMNS (id BIGINT);


4、增加表属性：
--ALTER TABLE page SET TBLPROPERTIES 


6、改变表文件组织格式：
ALTER TABLE page SET FILEFORMAT TEXTFILE;
ALTER TABLE page CLUSTERED BY(itemid) SORTED BY(pagetime) INTO 24 BUCKETS;


7、增加分区：
ALTER TABLE page ADD PARTITION (dt='2010-4-5',country='us');
ALTER TABLE page ADD PARTITION (dt='2013-6-5',country='cn');


8、删除分区：
ALTER TABLE page DROP PARTITION(dt='2010-4-5',country='us');


9、创建、删除视图：
LOAD DATA INPATH '/input/pig.txt' 
OVERWRITE INTO TABLE page 
PARTITION (dt='2014-6-7',country='cn');




CREATE VIEW IF NOT EXISTS page_view(itmid COMMENT '是itemid')
COMMENT 'page 的VIEW'
AS
SELECT DISTINCT itemid
FROM page
WHERE id='123';


10、创建、删除函数：
CREATE TEMPORARY FUNCTION page_func AS class_name;
DROP TEMPORARY FUNCTION page_func;


11、显示描述：
SHOW TABLES [...];
SHOW PARTITIONS page;


12、插入数据：
INSERT OVERWRITE TABLE page_external PARTITION(dt='2034-4-5',country='cn')
SELECT * FROM page;


13、查询：
INSERT OVERWRITE DIRECTORY '/tmp/page' 
SELECT * from page p WHERE p.dt='2014-6-7';