Hive命令



一.            显示地展示当前使用的数据库hive> set hive.cli.print.current.db=true;  

̶         切换当前的数据库hive(default)> USE hduser4801;

̶         使Hive显示列头  set hive.cli.print.header=true;

̶         创建数据库命令hive> CREATE DATABASE financials;

̶         删除数据库的时候，不允许删除有数据的数据库，如果数据库里面有数据则会报错。如果要忽略这些内容，则在后面增加CASCADE关键字，则忽略报错，删除数据库。

hive> DROP DATABASE DbName CASCADE(可选);

hive> DROP DATABASE IF EXISTS DbName CASCADE;

̶         查看当前有DB有啥表。hive> SHOW TABLES IN DbName;

也可以使用正则表达式 hive> SHOW TABLES LIKE 'h*' ;

hive> SHOW TABLES 'h*' ;

̶         查看所有的数据库。

hive> show databases ;

hive> describe databases DbName; --查看数据库信息

̶         通过Alter关键字修改DB相关信息

hive> ALTER DATABASE financials SET DBPROPERTIES ('edited-by' = 'Joe Dba');

truncate table table_a ;  清空一个表的数据(hive 0.11.0支持)

 

Hive 0.8 以后可以使用not in，但是不支持子查询。 可以支持：

Select * from test where column_a not in (‘a’,’b’,’c’);

 

Hive 0.10.0:

命令: show create table XXX_TBL;

可以获得表的建表语句。

 

̶         Hive 0.10.0支持 CUBE ROLLUP分析函数。

如：

SELECT key,val, count(1) FROM T1 GROUP BY key, val with cube  ;

SELECT key,count(distinct val) FROM T1 GROUP BY key with rollup ; 

 

̶         Hive 0.11.0支持窗口函数

SELECT AVG(col) OVER(PARTITION BY col_b ORDER BY col_c)

FROM TBL_A

 

̶         Hive 0.11.0支持聚合函数：LEAD/LAG/FIRST/LAST

 

̶         展示授权：

show grant user hadoop on database eshop;

show grant user hadoop on table ahhs_product_info;

̶         获取当前日期：

hive> select from_unixtime(unix_timestamp()) from ahhs_product_info limit 1;

Result:

2013-07-18 09:29:44

Hive Table

CREATE TABLE IF NOT EXISTS mydb.employees (

name           STRING COMMENT 'Employee name',

salary         FLOAT  COMMENT 'Employee salary',

subordinates    ARRAY COMMENT 'Names of subordinates',

deductions     MAP

COMMENT 'Keys are deductions names, values are percentages',

address         STRUCT COMMENT 'Home address‘

)

COMMENT 'Description of the table'

TBLPROPERTIES ('creator'='me', 'created_at'='2012-01-02 10:00:00', )

LOCATION ‘/user/hive/warehouse/mydb.db/employees‘;

首先Hive有内部表，和External Table外部表两种表的概念。内部表就是通过Hive自身创建的表，并由Hive本身来管理数据。

而外部表，则是通过Hive建立表的Metadata提供一些数据查询服务，比如一些HDFS文件，Pig，Hbase的文件都可以通过Hive进行批处理和查询。

使用外部表就使用CREATE EXTERNAL TABLE，增加External关键字，并且通过LOCATION关键字指定外部表的数据存放位置。

内部表使用LOCATION关键字时指定该表的存放在HDFS中位置。

 

在使用DELETE TABLE命令的时候，内部表的数据会与表结构一起删除，而外部表则只会删除表结构。

与RDBMS一样，可以使用COMMENT关键字来给字段和表添加注释。

与RDBMS不一样，Hive支持各种数据类型，常用的是string，bigint，double

日期类型也是使用string来存储。

此外，Hive支持各种结构，如Map，Array，Struct，这些数据类型非常强大。

 

最后，Hive表还有一个TBLPROPERTIES用来给表添加一些描述信息，比如最后一次修改信息，最后一个修改人。

create table choice(userid int,classname string)

row format delimited fields terminated by '\t'

STORED AS TEXT FILE

LOCATION '/data/test01/daxingyu930’;

 

STORED AS TEXTFILE 关键字指代数据的存储格式为文本格式，如果对数据进行压缩，可以使用RCFILE或者是SEQUENCE FILE。

 

ROW FORMAT DELIMITED FIELDS TERMINATED BY指定读取的文本的数据分隔符，还有可以逗号，CTRL-A等等。Hive默认的分隔符是：\u0001，所以创建表格的时候需要根据实际情况修改一下。(\001)

 

通过：DESC TB_NAME ;

或者：DESC EXTENDED TB_NAME ;

这个也很好用：DESC FORMATTED TABLE_NAME;

 

可以查看表的基本信息（包括字段类型，字段名称等等），

和表的详细信息 （包括表的属性，存储的位置等）。

 

1.将select的结果放到一个的的表格中（首先要用create table创建新的表格）

 insert overwrite table test

 select uid,name from test2;

2.将select的结果放到本地文件系统中

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3'

SELECT a.* FROM events a;

3.将select的结果放到hdfs文件系统中

INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out'

SELECT a.* FROM invites a WHERE a.ds='';

创建表：
hive> CREATE TABLE pokes (foo INT, bar STRING);
        Creates a table called pokes with two columns, the first being an integer and the other a string

创建一个新表，结构与其他一样
hive> create table new_table like records;

创建分区表：
hive> create table logs(ts bigint,line string) partitioned by (dt String,country String);

加载分区表数据：
hive> load data local inpath '/home/hadoop/input/hive/partitions/file1' into table logs partition (dt='2001-01-01',country='GB');

展示表中有多少分区：
hive> show partitions logs;

展示所有表：
hive> SHOW TABLES;
        lists all the tables
hive> SHOW TABLES '.*s';

lists all the table that end with 's'. The pattern matching follows Java regular
expressions. Check out this link for documentation http://java.sun.com/javase/6/docs/api/java/util/regex/Pattern.html

显示表的结构信息
hive> DESCRIBE invites;
        shows the list of columns

更新表的名称：
hive> ALTER TABLE source RENAME TO target;

添加新一列
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
 
删除表：
hive> DROP TABLE records;
删除表中数据，但要保持表的结构定义
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;