Hive[4] 数据定义 HiveQL

HiveQL 是 Hive 查询语言，它不完全遵守任一种 ANSI SQL 标准的修订版，但它与 MySQL 最接近，但还有显著的差异，Hive 不支持行级插入，更新和删除的操作，也不支持事务，但 Hive 增加了在 Hadoop 背景下的可以提供更高性能的扩展，以前个性化的扩展，还有一些外部程序；

 

4.1 Hive 中的数据库

Hive 数据库本质上就是表的一个目录或命名空间；如果用户没有显示式的指定库那么将会使用默认的数据库 default

hive> cretae database if not exists financials;    创建一个数据库(如果不存在的话)

注意：在所有的数据库相关命令中，都可以使用 SCHEMA 这个关键字来代替关键字 TABLE; 

hive>show databases;    查看所有数据库

hive>show databases linke 'h.*'   查看以h 开头其他字结尾的数据库名

 

Hive 会为每个数据库创建一个目录，数据库中的表会以这个目录的子目录的形式存储；

数据库所在的目录位于属性 hive.metasotre.warehouse.dir 所指定的顶层目录之后；

 

hive>create database 

>finacials comment '数据库描述信息'

>location '/my/preferred/directory' ;来修改默认存储的位置

>describe database financials;   显示数据库的信息

test2 hdfs://192.168.1.12:9000/user/hive/warehouse/test2.db root USER

下面会显示数据库所在的文件目录的位置路径,如果用户是伪分布式模式，那么主节点服务器的名称就应该是 localhost；对于本地模式这个应该是一个本地路径 ，例如： file://user/hive/warehouse/test2.db ; 如果这部分省略了，Hive 将全使用 Hadoop 配置文件中的配置项 fs.default.name 作为 master-server 所对应的服务器名和端口号；

注意：hdfs:///user/hive/warehouse/test.db 和 hdfs://master-server/user/hive/warehouse/test.db 是等价的；

 

create database test3 with dbproperties('creator'='Mark Moneybags','date'='2015-01-10') ;  建立数据库时给库添加其属性字段；

describe database extended test3;   查看数据库属性

 

hive> use test2;   切换到 test2 库；

hive> show tables;   显示当前库下所有表；

hive> show tables 'empl.*' ;  显示 empl 开头的其它字符结尾的表；

hive> show tables in mydb;   显示 mydb 数据库下的表(不在当前库下也可以显示其他库下的表)；

hive>drop database if exists test3;   删除数据库； if exists 可以避免因数据库不存在而抛出警告信息；

hive> drop database if exists test3 cascade ;  

注意： Hive 是不允许用户删除一个包含有表的数据库，用户要么先删除数据库中的表，然后再删除数据库，要么在删除命令的最后加上 cascade 这样就可以先删除数据库的表；如果使用 restrict 这个关键字，那么就和默认的情况一样，必须先删除表再删除数据库，如果删除了库那么对应的目录也会被删除；

 

4.2 修改数据库

hive>alter database databaseName set dbproperties ('edited-by'='Joe Dba') ; 修改数据库描述，数据库其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置，没有办法可以删除或“重置”数据库属性；

 

4.3 创建表

Hive 创建表可自定义数据文件存储的位置，使用什么存储格式等；

示例：

CREATE TABLE IF NOT EXISTS  test2.employees(

    name STRING COMMENT 'xing ming',

    salary FLOAT COMMENT 'gong zi',

    subordinates ARRAY<STRING>  COMMENT 'xia shu',

    deductions MAP<STRING,FLOAT>  COMMENT 'kou chu',

    address  STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>  COMMENT 'di zhi'

)COMMENT 'Desciption of the table'  

TBLPROPERTIES ('creator'='me','create_at'='2014-12-17 21:10:50')  --表描述【TBLPROPERTIES作用是按键值对的格式为表增加额外的说明，也可以表示数据库链接的必要的元数据信息】

LOCATION '/user/hive/warehouse/mydb.db/employees';  --为表中的数据指定一个存储路径（和元数据不同的是，元数据总是会保存这个路径）

默认的情况下 Hive 总是将创建的表的目录放置在这个表所属的数据库目录之后，但 default 库例外，这个数据库表的目录会直接位一 /user/hive/warehouse 目录之后（除了用户明确指定为其他路径）

 

last_modified_by  保存着最后修改这个表的用户的用户名；hive 自动添加的表属性

last_modified_time 最后一次修改的新纪元时间秒；hive 自动添加的表属性

hive>show tblproperties  tableName ;  显示某表的 tblproperties  信息

hive>create table if not exists mydb.abc like mydb.aaa;  直接拷贝表结构，不带数据；

hive>describe extended mydb.abc ;   查盾这个表的详细表结构信息；

hive>describe mydb.abc.salary;  显示表里的某一列的信息；

 

4.3 管理表

以上我们建立的表都是管理表，也叫内部表；当然也有外部表；

创建一个外部表来读取位于 /data/stocks 目录下的以逗号分隔的数据：

create external table if not exista 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';

external 告诉这个表是外部的，location 告诉Hive 数据位于哪个路径下；

注意：因为表是外部，所以 Hive并非认为基完全拥有这份数据，因此，删除此表时并不会删除掉这份数据，不过描述表的元数据信息会被删除掉；

describe extended tablename 可以查看表是否是管理表或外部表；[如果输出中有 tableType:MANAGED_TALBE表明是托管表，tableType:EXTERNAL_TALBE 外部表] 

 

create external table if not exists mydb.employees3   like mydb.employees 

location '/path/to/data';   --对一张存在的表进行表结构复制（不会复制数据）

注意：如果上面例子中省掉 external 关键字而且源表是外部表搞乱 ，那么生成的表也是外部表，如果语句中省略掉external关键字而且源表是管理表的话，那么生成的表也是管理表。

 

4.4 分区表，管理表

通常使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更近的地方，以及实现其他目的；

对数据分区最重要的原因就是为了更快的查询，寻找个表周的人员时只需要扫描一个目录下的内容即可；

 

创建分区表：

CREATE TABLE employeesPP(

name STRING, salary  FLOAT, subordinates ARRAY<STRING>,  

deductions MAP<STRING,FLOAT>,

address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>

) PARTITIONED BY(contry STRING,  state STRING);

分区表改变了 Hive 对数据存储的组织方式； 具体不同 P80页

/employeesP/country=CA/state=AB

/employeesP/country=CA/state=BC

/employeesP/country=US/state=AL

/employeesP/country=US/state=AK

 

SELECT * FROM employeesP WHERE country = 'US' NAD state = 'IL';   查询分区字段

set hive.mapred.mode=strict;  [nostrict]  将 Hive 设置为 strict 严格模式；

show partitions employeesPP；  查盾表中存在的所有分区；

show partitions employeesPP partition(country='US',state='AK');    想查看是否存储某个特定分区键的分区；

describe extended employeesPP 显示分区键；

 

通过载入数据的方式创建分区表：

LOAD DATA LOCAL INPUTH '${env:HOME}/california-employees' INTO TABLE employees  ---注意 HiveQL中是如何引用  HOME 环境变量的

partition (country='US', state='CA');

 

4.4 外部分区表

外部表上同样可以使用分区；

日志分析实例：时间戳，严重程度（error、warting、info）,服务器名称和进程ID等；

create external table if not exists log_messages(

hms    INT,    serverity    STRING,    server    STRING,    process_id    INT,    message    STRING)    

partitioned by(year INT, month INT,  day INT)

row format delimited fields terminated by '\t';

上面语句是创建一个外部年月日的分区表

 

alter table log_messages ADD PARTITION(year=2012,month=1,day=2) location 'hdfs://master_server/data/log_messages/2012/01/02';  --可以单独进行增加分区；

hadoop distcp /data/log_message/2011/12/01/ s3n://ourbucket/logs/2011/12/02 将分区下的数据拷贝到 S3中；

alter table log_messages partition(year=2012,month=12,day=2) set location 's3n://ourbucket/logs/2012/12/1=02/'; 修改表将分区路径指向到 S3路径；

hadoop fs -rmr /data/log_message/2012/01/02/;   删除 HDFS 中的这个分区数据；

 

Hive 不关心一个分区对应的分区目录是否存在或者分区目录下是否有文件，如果分区目录不存在或者分区目录下没有文件，则对于这个过滤分区的查询将没有返回结果，当用户想在另外一个进程开始往分区中写数据之前创建好分区时，这样做很方便的，数据一旦存在，对于这份灵气的查询就会有返回结果；这个功能所具有的另一个好处是：可以将新数据写入到一个专用的目录中，并与位于他目录中的数据存在明显的区别，同时，不管用户是将旧数据转移到一个“存档”位置还是直接删除掉，新数据被篡改的风险都被降低了，因为新数据的数据子集位于不同的目录；

和非分区外部表一样，Hive 并不控制这些数据，即使表被删除，数据也不会被删除；

 

show partitions log_messages;  查看外部分区表的分区；

describe extended log_messages;   会将分区键作为表的模式的一部分和 partitonKeys 列表的内容同时进行显示[输出会缺少分区数据的实际路径]；

describe extended log_messages partiton(year=2012,month=1,day=2);  会显示出来分区数据的实际存储路径；

我们通常会使用分区外表，因为它具有非常多的优点，如逻辑数据管理，高性能的查询等；

 

alter table ... add partition 语句并非只有对外部表才能够使用，对于管理表，当有分区数据不是由 LOAD 和 INSERT 语句产生时，用户同样可以使用这个命令指定分区路径；

 

自定义表的存储格式

CREATE TABLE IF NOT EXISTS  test2.employees(

    name STRING COMMENT 'xing ming',

    salary FLOAT COMMENT 'gong zi',

    subordinates ARRAY<STRING>  COMMENT 'xia shu',

    deductions MAP<STRING,FLOAT>  COMMENT 'kou chu',

    address  STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>  COMMENT 'di zhi'

)COMMENT 'biao shuo ming'  

TBLPROPERTIES ('creator'='me','create_at'='2014-12-17 21:10:50')   表说明标签

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'                        --列与列之间的分隔符；

COLLECTION ITEMS TERMINATED BY '\002'  --数组分隔符设定；

MAP KEYS TERMAINATED '\003'                      -- MAP 分隔符

LINES TERMINATED '\N'                --行与行之间的分隔符；

STROED AS TEXTFILE;         --文本格式存储表数据，意味着每一行被认为是一个单独的记录；

LOCATION ‘/user/hive/warehouse/mydb.db/employees';  数据存放地址 

 

用户可以将 textfile 替换为其他 Hive 所支持的内置文件格式，包括 sequencefile 和 rcfile 这两种文件格式都是使用二进制编码和压缩（可选），来优化磁盘空间使用以及 I/O带宽性能的；

 

用户还可以指定第三方的输入和输出格式以及 SerDe 这个功能允许用户自定义 Hive 本身不支持的其他广泛的文件格式；

以下例子使用了自定义 SerDe，输入格式也输出格式，可以通过 Avro 协议访问这些文件：

create table kst

partitioned by (ds STRING)

row format serde 'com.linkedin.haivvreo.AvroSerDe'   --指定了SerDe

with serdeproperties('schema.url'='http://schema_provider/kst.avsc')   --with serdeproperties 允许用户传递配置信息给 SerDe【每个属性都需要用引号】

stored as

inputfomat 'com.linkedin.haivvreo.AvroContainerInputFormat'   --指定用于输入格式的Java类

outputformat 'com.linkedin.haivvreo.AvroContainerOutputFormat';   --指定用于输出格式的Java类

注意：如果用户要指定，就两个都必须指定；

 

describe extended table；会在 detailed table informationg 部分列举出输入和输出格式以有 SerDe 和 SerDe 所自带的属性信息。

drop table if exists employees;  删除表，对于管理表，表的元数据信息和表内的数据都会被删除；对于外部表，表的元数据信息会被删除，但是表中的数据不会被删除；

 

提示：如果用户开启了 Hadoop 回收站功能（默认是关闭的）那么数据将会被转移到用户在分布式文件系统中的用户根目录下的.Trash 目录下，也就是 HDFS 中的 /$USER/.Trash 目录，如果要开启这个功能，需要修改配置 fs.trash.interval 的值设置为一个合理的正整数即可；这个值是 “回收站检查点”间的时间间隔，单位是分钟。

 

修改表可以通过 alter table 语句来进行修改，这操作会修改元数据，但不会修改数据本身。

alter table log_messages rename to logmsgs;     修改表名

 

alter table log_messages add if not exists   --在同一个查询中可以同时增加多个分区（0.8以后的版本可以支持此查询）

partiton(year=2015,month=1,day=10) location '/logs/2015/01/10'

partiton(year=2015,month=1,day=9) location '/logs/2015/01/09'

partiton(year=2015,month=1,day=9) location '/logs/2015/01/08'

 

alter table log_messages partition(year=2012,month=12,day=2) set location 's3n://ourbucket/logs/2011/01/02' ; 用户可以通过高效地移动位置来修改某个分区的路径；但这个命令不会将数据从旧的路径转移走，也不会删除旧的数据；如果用户此句增加分区，分区内的数据也是会同时和元数据信息一起被删除的（外部表则不会）

 

alter table log_messages drop if exists partition(year=2012,month=12,day=2) ;    删除某个分区；

 

alter table log_messages change column hms hours_minutes_seconds int  --给字段进行重命名  （把hms字段改成 hours_minutes_seconds 并）

comment 'The houses, minutes, and seconds part of the timesamp' after severity;   --修改位置，类型或者注释 （位置在 severity 字段之后 ）

注意：即使字段名或者字段类型都没有改变，用户也需要完全指定旧的字段名，并给出新的字段名及新的字段类型，关键字 column 和 comment 子句都是可选的。如果把改后的字段改放到第一个位置只需要把 after 改成 first 即可 ；这个命令只会修改元数据信息，如果移动字段，那么数据也应当和新的模式匹配或者通过其他些方法修改数据以使其能够和模式匹配；

 

alter table log_message ADD COLUMNS (app name STRING COMMENT 'application name',session_id LONG COMMENT 'The current session id');  增加列

 

alter table log_messages replace columns(hours_mins_secs INT COMMON '', severity STRING COMMON '', message STRING COMMON '');   

重命名了之前的 hms 字段并且从之前的表定义的模式中移除了字段 server 和 process_id 只改变表的元数据信息；

 

alter table log_messages SET tblproperties('notes'='The process id is no longer captured');    修改表属性，但无法删除属性；

 

alter table log_msg partition(year=2015,month=1,day=10) SET fileformat sequencefile;   修改表的存储格式为 sequence file 存储格式

 

alter table talbe_using_JSON_storage SET SERDE 'com.example.JSONSerDE'  

WITH serdeproperties('prop1'='value1','prop2'='value2');   serdeproperties 中的属性会被传递给 SerDe 模块；

 

alter talbe table_useing_JSON_storage SET SERDEPROPERTIES('prop3'='value3','prop4'='value4');  为已经存在着的 SerDe 增加新的 SERDEPROPERTIES 属性；

 

alter table stocks clustered by (exchange,symbol) sorted by(symbol) into 48 BICLETS;  修改数据分桶表；

 

alter table log_msg TOUCH partition(year=2015,month=1,day=1);  当表中存储的文件在 Hive 之外被修改了，就会触发钩子的执行；

 

alter table log_msg archive partiton (year=2015,month=1,day=1);  会将这个分区内的文件打成一个 Hadoop（HAR） 压缩包，这样仅仅可以降低文件系统中的文件数据以及减轻 NameNode 的压力，而不会减少任何的存储空间；使用 UNARCHIVE 可以反向操作（只能用于分区表中独立的分区）；

 

alter table log_msg archive partiton (year=2015,month=1,day=1) ENABLE NO_DROP;  --防止被删除 DISENABLE 反向操作 只能用于分区表

alter table log_msg archive partiton (year=2015,month=1,day=1) ENABLE NO_OFFLINE;  --防止被查询   DISENABLE 反向操作 只能用于分区表