hive hql命令 摘自programming hive

1. CREATE DATABASE、SHOW DATABASE命令

(1) 创建数据库

hive> CREATE DATABASE financials;

(2) 创建数据库之前判断是否已经存在同名数据库，否则hive命令行会抛出异常

hive> CREATE DATABASE IF NOT EXISTS financials;

(3) 列出所有数据库

hive> SHOW DATABASES;
default
financials

(4) 新创建一个数据库，再列出所有的数据库，

hive> CREATE DATABASE human_resources;
hive> SHOW DATABASES;
default
financials
human_resources

(5)根据数据库名的前缀列出数据库

hive> SHOW DATABASES LIKE 'h.*';
human_resources

(6) 创建数据库，并设置其存放的位置，注意该路径在hdfs下

hive> CREATE DATABASE financials
       > LOCATION '/my/preferred/directory';

(7)创建数据库的同时，记录注释

hive> CREATE DATABASE financials
       > COMMENT 'Holds all financial tables';

查看数据库的详细信息，就可以看到注释

hive> DESCRIBE DATABASE financials;
financials  Holds all financial tables
hdfs://master-server/user/hive/warehouse/financials.db

(8)创建数据库的同时，增加扩展属性

hive> CREATE DATABASE financials
        > WITH DBPROPERTIES ('creator' = 'Mark Moneybags', 'date' = '2012-01-02');

通过DESCRIBE命令看不到扩展属性，只有加入EXTENDED关键字才能看到

hive> DESCRIBE DATABASE financials;
financials  hdfs://master-server/user/hive/warehouse/financials.db
hive> DESCRIBE DATABASE EXTENDED financials;
financials  hdfs://master-server/user/hive/warehouse/financials.db
{date=2012-01-02, creator=Mark Moneybags);

可以用FORMATTED代替EXTENDED关键字，这样排版的效果更清楚

2. USE命令，设置当前使用的数据库

hive> USE financials;

在hive中，没有命令能够打印当前工作数据库的名称，但是可以通过设置hive.cli.print.current.db实现命令行打印当前数据库名

hive> set hive.cli.print.current.db=true;

hive (financials)> USE default;

hive (default)> set hive.cli.print.current.db=false;

3. DROP命令，删除一个数据库

删除一个数据库，默认情况下，hive不允许删除含有表的数据库，要先将数据库中的表清空才能drop，否则会报错Database financials is not empty

hive> DROP DATABASE IF EXISTS financials;

加入CASCADE关键字，可以强制删除一个数据库

hive> DROP DATABASE IF EXISTS financials CASCADE;

4. CREATE TABLE、SHOW TABLE命令

(1)创建一个表employees，mydb为数据库名，在执行这条命令之前先要运行create database mydb; 否则会提示FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidObjectException(message:There is no database named mydb)

hive> CREATE TABLE IF NOT EXISTS mydb.employees (
    > name  STRING COMMENT 'Employee name',
    > salary  FLOAT  COMMENT 'Employee salary',
    > subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
    > deductions  MAP<STRING, FLOAT>
    > COMMENT 'Keys are deductions names, values are percentages',
    > address  STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
    > COMMENT 'Home address')
    > COMMENT 'Description of the table'
    > LOCATION '/user/hive/warehouse/mydb.db/employees';

其中，IF NOT EXISTS关键字会检查库里是否已经有同名表，如果有同名表，就不再创建，也就不会报错。但是这种操作模式蕴含了潜在的问题，当新创建的表与之前的表同名，但是表结构并不相当，使用IF NOT EXISTS以后，没有提示错误，就会让使用者以为新的表创建成功，当对该表进行操作时就有很大的可能发生错误。

(2)根据employees的表结构创建一个新表

CREATE TABLE IF NOT EXISTS mydb.employees2
LIKE mydb.employees;

(3)显示一个数据库中的表

hive> USE mydb;
hive> SHOW TABLES;
employees
employees2

还可以使用正则表达式，比如SHOW TABLES "*.2"

其他数据库作为当前工作数据库时，可以用IN database_name关键字，但此时不再支持正则表达式

hive> USE default;
hive> SHOW TABLES IN mydb;
employees
employees2

(4)使用EXTERNAL关键字创建外部表

hive只管理外部表元数据，而外部表的数据并不属于hive，当hive删除外部表时，只删除了元数据。使用外部表是为了方便hive与其他分析工具共享数据。

因为外部表并不保存在hive内部，建表的时候需要明确表保存的路径，所以EXTERNAL关键字一定要与LOCATION关键字配合使用。

CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
exchange  STRING,
symbol  STRING,
ymd  STRING,
price_open  FLOAT,
price_high  FLOAT,
price_low  FLOAT,
price_close  FLOAT,
volume  INT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';

(5)判断一个表是外部表还是内部表

hive>DESCRIBE EXTENDED tablename

在Detailed Table Information一项，如果是以tableType:MANAGED_TABLE结尾，说明该表是一个内部表。如果是以tableType:EXTERNAL_TABLE结尾，说明这是一个外部表。

(6)通过复制表结构创建外部表

hive>CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3
        >LIKE mydb.employees
        >LOCATION '/path/to/data';

如果原始表是外部表，即使复制的时候不使用EXTERNAL关键字，生成的也是外部表。

(7)PARTITIONED BY 关键字创建分区表

分区表的好处是根据选出的字段名，将表的内容存放在不同的目录下，可以通过限定范围来加快检索的速度

CREATE TABLE employees (
name  STRING,
salary  FLOAT,
subordinates ARRAY<STRING>,
deductions  MAP<STRING, FLOAT>,
address  STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

比如这个表，将会把表的内容分散到如下路径中：

...
.../employees/country=CA/state=AB
.../employees/country=CA/state=BC
...
.../employees/country=US/state=AL
.../employees/country=US/state=AK
...

(8)hive.mapred.mode限定分区表的检索范围

使用分区表以后，where关键字就会触发分区表的filter，如果检索时没有使用where，就会导致分区表功能大打折扣。所以可以通过hive.mapred.mode选项设置，是否强制使用where。strict方式，强制使用where如果没有where会导致报错。

hive> set hive.mapred.mode=strict;
hive> SELECT e.name, e.salary FROM employees e LIMIT 100;
FAILED: Error in semantic analysis: No partition predicate found for
Alias "e" Table "employees"

hive> set hive.mapred.mode=nonstrict;
hive> SELECT e.name, e.salary FROM employees e LIMIT 100;
Partitioned, Managed Tables | 59
John Doe  100000.0
...

(9)显示一个表的分区信息
hive> SHOW PARTITIONS employees;
...
Country=CA/state=AB
country=CA/state=BC
...
country=US/state=AL
country=US/state=AK
...

根据字段名，显示分区信息

hive> SHOW PARTITIONS employees PARTITION(country='US');
country=US/state=AL
country=US/state=AK
...
hive> SHOW PARTITIONS employees PARTITION(country='US', state='AK');
country=US/state=AK

显示分区的关键字段名

hive> DESCRIBE EXTENDED employees;
name  string,
salary  float,
...
address  struct<...>,
country  string,
state  string
Detailed Table Information...
partitionKeys:[FieldSchema(name:country, type:string, comment:null),
FieldSchema(name:state, type:string, comment:null)],
...

(10)将文件中的内容导入到分区表中

LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
INTO TABLE employees
PARTITION (country = 'US', state = 'CA');