hive 基础(1)
$hive -help
Usage: hive [--config confdir] [-hiveconf x=y]* [-i <init-filename>]*[<-f filename>|<-e query-string>] [-S]
-u username
-p [password]
-i <filename> init Sql file
-e 'quoted query string' Sql from command line
-f <filename> Sql from files
-S Silent mode in interactive shell
-e and -f cannot be specified together. In the absence of these
options, interactive shell is started
-f 可以把命令写在文件里
-S 只输出最终结果
官方文档内容
建表
可以整表注释也可对单字段注释。partition为分区,并不属于数据部分,决定了存储目录结构。cluster为了优化性能,预先排列,方便快速查询。ASCII 001(ctrl-A) 默认字段分隔,换行为默认行分隔。
CREATE TABLE page_view(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, friends ARRAY<BIGINT>, properties MAP<STRING, STRING> ip STRING COMMENT 'IP Address of the User') COMMENT 'This is the page view table' PARTITIONED BY(dt STRING, country STRING) CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '1' COLLECTION ITEMS TERMINATED BY '2' MAP KEYS TERMINATED BY '3' STORED AS SEQUENCEFILE;
SHOW TABLES;SHOW TABLES 'page.*';SHOW PARTITIONS page_view;DESCRIBE page_view;DESCRIBE EXTENDED page_view;DESCRIBE EXTENDED page_view PARTITION (ds='2008-08-08');
CREATE EXTERNAL TABLE page_view_stg(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User', country STRING COMMENT 'country of origination') COMMENT 'This is the staging page view table' ROW FORMAT DELIMITED FIELDS TERMINATED BY '44' LINES TERMINATED BY '12' STORED AS TEXTFILE LOCATION '/user/data/staging/page_view'; hadoop dfs -put /tmp/pv_2008-06-08.txt /user/data/staging/page_view FROM page_view_stg pvs INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US') SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'US';
LOAD DATA LOCAL INPATH {{/tmp/pv_2008-06-08_us.txt}} INTO TABLE page_view PARTITION(date='2008-06-08', country='US')
LOAD DATA INPATH '/user/data/pv_2008-06-08_us.txt' INTO TABLE page_view PARTITION(date='2008-06-08', country='US')
select a.id,a.capital,b.province from tb1 a left outer join tb2 b on (a.id = b.id);
select a.id,a.capital,b.province from tb1 a right outer join tb2 b on (a.id = b.id);
select a.id,a.capital,b.province from tb1 a full outer join tb2 b on (a.id = b.id);
select a.id,a.capital,b.province from tb1 a join tb2 b on (a.id = b.id);
select a.id,a.capital from tb1 a left semi join tb2 b on (a.id = b.id);