最新消息:博客的意义不在于数量,在于原创。

Hive的常用命令

Hive 辰 羽 4943浏览 0评论

创建表:
hive> CREATE TABLE tb1 (foo INT, bar STRING);

创建一个新表,结构like其他表
hive> CREATE TABLE tb2 LIKE tb1;

创建分区表:
hive> CREATE TABLE tb1(ts BIGINT, line STRING) PARTITIONED BY(dt STRING, country STRING);

加载分区表数据:
hive> LOAD DATA LOCAL INPATH '/home/hadoop/input/hive/partitions/file1.log' INTO TABLE tb1 PARTITION (dt='20080808', country='GB');

展示表中有多少分区:
hive> SHOW PARTITIONS tb1;

展示所有表:
hive> SHOW TABLES;
hive> SHOW TABLES '.*s';

显示表的结构信息
hive> DESCRIBE tb1;
DESC tb1;

更新表的名称:
hive> ALTER TABLE tb1 RENAME TO tb2;

添加新一列
hive> ALTER TABLE tb1 ADD COLUMNS (new_col2 INT COMMENT 'a comment');

删除表:
hive> DROP TABLE tb1;
删除表中数据,但要保持表的结构定义
hive> dfs -rmr /user/hive/warehouse/records;

从本地文件加载数据:
hive> LOAD DATA LOCAL INPATH '/home/hadoop/input/ncdc/micro-tab/sample.txt' OVERWRITE INTO TABLE records;

显示所有函数:
hive> SHOW FUNCTIONS;

查看函数用法:
hive> DESCRIBE FUNCTION substr;

查看数组、map、结构
hive> select col1[0],col2['b'],col3.c from complex;

内连接:
hive> SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

查看hive为某个查询使用多少个MapReduce作业
hive> Explain SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

外连接:
hive> SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
hive> SELECT sales.*, things.* FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
hive> SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id);

in查询:Hive不支持,但可以使用LEFT SEMI JOIN
hive> SELECT * FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);
Map连接:Hive可以把较小的表放入每个Mapper的内存来执行连接操作
hive> SELECT /*+ MAPJOIN(things) */ sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

INSERT OVERWRITE TABLE ..SELECT:新表预先存在
hive> 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:新表表预先不存在
hive>CREATE TABLE target AS SELECT col1,col2 FROM source;

创建视图:
hive> CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999;

查看视图详细信息:
hive> DESCRIBE EXTENDED valid_records;

 

转载请注明:辰羽的视界 » Hive的常用命令

与本文相关的文章

  • 暂无相关文章!
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址