hive hql命令摘自programming hive

雨点打透心脏的1/2处 2022-09-20 15:07 119阅读 0赞

**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');