HiveQL

参考链接:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

1.数据定义语言(DDL)

  • 创建、修改、删除数据库

创建数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

[WITH DBPROPERTIES (property_name=property_value, …)];

语法解释:

  • 数据库注释:COMMENT
  • 在hdfs上的路径: LOCATION
  • 使用Key-value的方式为数据库注释

使用实例:

create database lesson1test comment ‘lesson1test’ with dbproperties(‘user’=’banana’);

修改数据库的key-value注释

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …);

— (Note: SCHEMA added in Hive 0.14.0)

修改数据库的用户

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

— (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

使用实例

alter database lesson1test set dbproperties(‘user’=’lighter’);

alter database lesson1test set owner user hdfs;

drop database if exists lesson1test;

  • 创建、截断、删除表(带分区)

创建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

[(col_name data_type [COMMENT col_comment], …)]

[COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)]

[

[ROW FORMAT row_format]

[STORED AS file_format]

| STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)]  — (Note: Available in Hive 0.6.0 and later)

]

[LOCATION hdfs_path]

语法解释:

  • 是否是外部表: EXTERNAL
  • 表格注释: COMMENT
  • 定义分区信息:PARTITIONED BY
  • 行格式化方式(1.决定了是否使用hive自带的序列化函数2.可用于指定属性分隔符): ROW FORMAT

创建表使用实例:

CREATE TABLE goods_price(

id INT,

name STRING,

price FLOAT

)

COMMENT ‘Goods table’

PARTITIONED BY(supplier STRING, class_name STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’

STORED AS TEXTFILE;

复制表结构:

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

LIKE existing_table_or_view_name

[LOCATION hdfs_path];

使用实例:

create table if not exists copy_table like lesson1.goods_price;

截断表(保留表结构,删除数据):

TRUNCATE TABLE table_name [PARTITION partition_spec];

partition_spec:

(partition_column = partition_col_value, partition_column = partition_col_value, …)

使用实例(可指定一个partition_column,也可都指定):

truncate table copy_table partition (supplier=’yihaodian’,class_name=’shupian’);

truncate table copy_table partition (supplier=’yihaodian’);

删除表(任何删除动作都会删除相关的元数据):

DROP TABLE [IF EXISTS] table_name [PURGE];     — (Note: PURGE available in Hive 0.14.0 and later)

语法解释:

不指定PURGE,则被删除数据被移到.Trash/Current。

使用PURGE则直接删除(不可恢复)。

使用实例:

drop table if exists copy_table;

  • 修改表、分区、列

修改表名(保留表结构,删除数据):

ALTER TABLE table_name RENAME TO new_table_name;

使用实例:

alter table goods_price rename to new_goods_price;

增加表分区(未修改表结构,只是多了一个分区值):

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec

[LOCATION ‘location1’] partition_spec [LOCATION ‘location2′] …;

partition_spec:

: (partition_column = partition_col_value, partition_column = partition_col_value, …)

使用实例:

alter table new_goods_price add if not exists partition (supplier=’taobao’,class_name=’shupian’);

alter table new_goods_price add if not exists partition (supplier=’jingdong’,class_name=’shupian’) location ‘/user/hive/warehouse/lesson1.db/goods_price/supplier=jingdong/class_name=shupian’;

表知识扩展——内部表和外部表

表类型 语句 是否将数据拷贝到warehouse 删除表是否删除数据
内部表 create table;
外部表 create external table;

Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径。

内部表不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除, 而外部表只删除元数据,不删除数据。这样外部表相对来说更加安全些,数据组织也更加灵活,方便共享源数据

表知识扩展——表分区

意义:避免全表扫描。Where语句采用分区限定的话,表查询会限制在分区内进行,提高效率和准确率。

  • 数据通过存储目录划分分区。
  • 指定多个分区时,hdfs上存储目录按照分区创建顺序形成树形结构。
  • 分区字段的选择:时间段、不同服务的数据、IP段等等。

两个分区时的二级树形存储目录(先supplier后class_name),由下图可见supplier目录是class_name的上级目录。

QQ截图20150330151934

2.数据操作语言(DML)

  • 文件数据导入表

命令:

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

语法解释:

  • 指定数据文件目录: INPATH
  • 覆盖原有数据: OVERWRITE
  • 指定分区(顺序无先后,但需要明确指定每一个分区)

partition(supplier=’yihaodian’,class_name=’niunai’);

使用实例:

load data local inpath ‘/tmp/20150313/niunai’ into table goods_price partition(supplier=’yihaodian’,class_name=’niunai’);

  • 查询结果数据插入表

命令(增加数据):

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement;

使用实例:

insert into table copy_table partition(class_name=’niunai’,supplier=’yihaodian’) select id,name,price from goods_price;

命令(覆盖原有数据):

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …) [IF NOT EXISTS]] select_statement1 FROM from_statement;

使用实例:

insert overwrite table copy_table partition(class_name=’niunai’,supplier=’yihaodian’) select id,name,price from overwrite_table;

  • UPDATE&DELETE

命令:

UPDATE tablename SET column = value [, column = value …] [WHERE expression]

DELETE FROM tablename [WHERE expression]

使用实例:

Hive 0.14支持,目前最新发行版为Hive 0.13

 

Leave a Comment

电子邮件地址不会被公开。 必填项已用*标注