4.HiveQL：数据定义

最新推荐文章于 2020-12-21 17:08:18 发布

洋气的网名

最新推荐文章于 2020-12-21 17:08:18 发布

阅读量218

点赞数

分类专栏： Hive 编程指南

本文链接：https://blog.csdn.net/qq_28066793/article/details/89214021

版权

Hive 编程指南专栏收录该内容

7 篇文章 0 订阅

订阅专栏

个人记录学习，大佬不喜勿喷。。。

4.1 Hive中的数据库

1.查看hive中所包含的数据库

show datebases;

2.创建数据库

 create datebase financials;

若financials存在则可能会抛出错误信息，使用如下避免错误

create datebase if not exit financials;

如果数据库非常多的话，可以使用正则表达式来匹配和筛选需要的数据库名
hive为每个数据库创建一个目录，数据库中的表将会以这个数据库目录的子目录形式存储

show databases like'h.*';

例外，default 数据库中的表，这个数据库本身没有自己的目录。

创建数据库默认创建一个目录/user/hive/warehouse/financials.db

3.修改数据库默认位置：

create datebase financials
location '/my/preferred/directory';

4.为数据库增加描述信息

create datebase financials;
comment 'holds all financial tables';

describe database 也会显示这个数据库所在的文件目录位置路径。
此例子中 URI 格式是 hdfs 如果安装 MapR，这里应该是maprts.

5.将某个数据库设置为用户啊当前的工作数据库。

    use financials;
    show tables #显示在当前数据库下所有的表

Hive中可以重复使用 USE 命令，Hive 中没有嵌套数据库的概念。

6.删除数据库

    drop database if exists financials;

默认情况下，Hive不允许用户啊删除一个包含有表的数据库。用户要么先删除数据库中的表，然后再删除数据库，要么在删除命令的最后面加上关键cascade

    drop database if exists financials cascade;

若使用的关键字变为 restrict 等同于默认

如果某个数据库被删除了，那么其对应的目录同时也会被删除

4.2 修改数据库

使用alter database命令为某个数据库的dbproperties设置键-值对属性值，来描述这个数据库的属性信息。

数据库中其他院数据信息都是不可更改的，包含数据库名以及所在目录。

 alter database financials set dbpoperties('edited-by'='Joe Dba');

!!! 咩有办法可以重置或者删除数据库属性。

4.3 创建表

create table if not exists mydb.employees(...)
comment 'description of the table'
talproperties(...)
location '/user/hive/warehouse/mydb.db/employees'
***
1.location 要跟完整目录
2.这里/user/hive/warehouse是默认的”数据仓库“路径地址，mydb.db是数据库目录，employees是表目录。
3.default并咩有对应的数据库目录，而是存在于/user/hive/warehouse 之后（除非用户明确制定其他目录）***

若当前所在数据库并非目标数据库，可在表明前加上一个数据库名，也就这里的mydb
加上 if not exits 之后，若表已经存在，hive会自动忽略后面的语句，且不会报错。

若指定的表模式已经和存在的饿这个表模式不同，Hive不会做出提示。
如用户的意图是使这个表具有重新制定新的模式化，应删除这个表，也就是丢弃之前的数据。然后重新建表。这里可以考虑用alter table 修改表结构

用户可以在字段类型后为每个字段增加一个注释

hive会自动增加两个表属性：last_modified_by,last_modified_time

show tblpoperties talbe_name
列举出某个表的tblproperties属性信息

拷贝一张已经存在的表

create table if not exists mydb.employees2
like mydb.employees;

可以接受更改location,但是其他属性，包括模式等都是从原始表直接获取不可重新定义。

hive 并非支持所有正则表达式

查看表的详细信息

describe extended mydb.employees
**使用formatted 代替extended可以提供更加可读和冗长的输出信息，实际使用formatted 多***

查看某一列的信息

describe mydb.employees.salary

4.3.1 管理表

目前所创建的表都是管理表，有时别称为内部表，这种表hive（在一定程度上）会控制数据的生命周期

有时删除管理表是，hive会删除这个表中的数据。

管理表不方便和其他工作共享数据，创建一个外部表只想着分数据，而不需要对其有所有权

4.3.2 外部表

create external table if not exits stock(
exchange  string,
symbol string,
price_adj_close float)
row format delimited fields terminated by ','
location '/data/stocks';

extenal 告诉hive这个表示外部表，location 子句用于告诉hive 数据位于哪个路径下

因为是外部表，所以hive并非认为其完全拥有过着分数据，因此删除该表不会删掉这部分数据，但是描述表的元数据信息会被删除。

通过 describe extended tablename 查看表是否是管理表或外部表

结构复制

对于存在的表进行表结构复制（不复制数据）：

create external table if not exists mydb.employees3
like mydb.employees
location '/path/to/data'

4.4 分区表、管理表

分区水平分散压力，将数据从物理上转移到和使用最频繁的用户更接近的地方，用于实习其他目的。

分区表会将数据以一种复合逻辑方式进行存储，例如分层存储。

分区表改变数据存储的组织方式

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

如果在mydb数据库创建该分区表，那么对于这个表只会有一个employees目录。
hdfs://master_servr/user/hive/warehouse/mydb.db/employees

hive将会创建好可以反应分区结构的字字母：例如

where 字句中增加谓词来按照分区值进行过滤，这些为此被称为分区过滤器。

查看表中存在的所有分区

show partitions employees;

查看某个特定分区

show partitions employees partition(country= 'US');

显示分区键

describeextendedemployees;

可以像给普通字段增加注释一样给分区字段增加注释。

在管理表中可通过载入数据的方式创建分区

从本地目录（$HOME/california-employees）载入数据到表中会创建一个分区 US和CA，用户需要为每个分区字段指定一个值。

load data local inpath '{env:HOME}/california-employees'
into table employees
partition(country = 'US',state='CA')
;

hive 会创建这个分区对应的目录
…/employees/country=US/state=CA而且$HOME/california-employees路径下的文件都会被拷贝到上述分区目录下。

4.4.1 外部分区表

单独增加分区

alter table log_messages add partition(year = 2012,month =1,day = 2)
location 'hdfs://master_server/data/log_messages/2012/01/02'；

该语句需要为每一个分区指定一个值

之前创建非分区外部表，需要用到location字句。对于外部分区表没有这样的要求

hive不关心一个分区对应的分区目录是否存在或者分区母驴下是否有文件
顶多是没有文件在过滤查询时没有返回结果
所以用户在另外一个进程开始往分区中鞋数据之前创建好分区，这样做很方便，数据一旦存在就会有查询结果。

并非所有的表数据都是放在通常的hive 'warehouse’目录下，同时当删除管理表时，这些数据不会连带被删除。

4.4.2 自定义表的存储格式

hive 默认的存储格式是文本文件格式
也可通过可选字句 storted as textfile 显式指定，用户可以在创建表时，指定各种各样的分隔符

create table employees(
name string,
salary float,
subordinates ARRAY<string>
deductions MAP<string,float>
address STRUCT<street:string,city:string,state:string,zip:int>)
row format delimited
fields terminated by '\001'
collection items terminated by '\002'
map keys terminated by '\003'
lines terminated by '\n'
stored as textfile;

textfile 意味着所有字段都适用字母、数字、字符编码，使用该字段每一行被认为是一个单独记录。

可以将textfile 替换为sequencefile和rfile 这两种文件格式都使用二进制编码和压缩用于优化磁盘空间。

4.5 删除表

drop table if exists employees;

4.6 修改表

alter table 仅仅会修改表元数据，表数据本身不会有任何修改，需要用户自己确认所有的修改都和真实数据一致。

4.6.1 表重命名

alter table log_messages rename to lomsgs;

4.6.2 增加、修改和删除表分区

增加分区

alter table log_messages add if not exists
partition(year = 2011,month =1,day =1) location '/log/2011/01/01';

修改分区

alter table log_messages partition(year = 2011,month =1,day =1) 
set location 's3n://ourbucket/logs/2011/01/02';

该命令不会将数据从旧路径转移，也不会删除旧的数据

删除分区

alter table log_messages drop if exists partition(year = 2011,month =1,day =1) ;

分区内的数据会同时和元数据信息一起被删除，外部表分区内数据不会被删除

4.6.3 修改列信息

对某个字段进行重命名，修改位置、类型
、注释

alter table log_messages
change column hms hours_minutes_seconds int#前面是旧名，后面是新名
comment 'the hours,minutes,and seconds part of the timestamp'（）
after severity;#将字段转移到 serverity字段之后
或者
first #将字段移动到第一列

4.6.4 增加列

在分区字段之前增加新的字段到已有的字段之后

alter table log_messages add columns(
app_name string comment 'application name',
session_id long comment 'the current session id');

若新增字段中有某个或多个字段错误，使用

  alter coulme 表名 change column 逐一将字段调整到正确位置

4.6.5 删除或者替换列

alter table log_messages replace columns(
hours_mins_secs int comment 'hour,minute,seconds from timestamp',
severity string comment 'the message severity');

重新命名了之前的hms字段并且从之前的表定义的模式中移除了字段server 和 process_id
因为是alter 所以只有元数据信息改变了

4.6.6 修改表属性

alter table log_messages set tablproperties(
'note'='the process id is no longer captured;this column is always NULL');

可以增加附加表的属性或者修改已经存在的属性，但是无法删除实行

4.6.7 修改存储属性

ALTER TABLE 可以用来修改存储格式和SerDe属性。

	ALTER TABLE
	PARTITION（year= 2012，month=1，day=1）
	SET FILEFORMAT  SEQUENCEFILE

用户可以指定一个新的SerDe，并为其制定SerDe属性。或者修改已经存在的SerDe的属性。

以下例子是使用了一个名为com.example.JSONSerDe的java类来处理记录和使用JSON编码文件。

	ALTER TABLE
	PARTITION（year= 2012，month=1，day=1）
	SET FILEFORMAT  'com.example.JSONSerDe'
	WITH SERDEPROPERTIES (
	'prop1'='values1'
	'prop2'='values2');

serdeproperties 重的属性会被传递给SerDe模块，属性名（prop1）和属性值（values1）应当是带引号的字符串。

像一个已经存在着SerDe增加新的SERDEPROPERTIES属性：

		ALTER TABLE table_using_JSON_storage
		SET SERDEPROPERTIES(
		'prop3'='values3',
		'prop4'='values4');

4.6.8 众多的修改表语句

触发钩子

			`ALTER TABLE  log_messages TOUCH
			PARTITION（year = 2012, month = 1, day = 1 ）`

当表中存储的文件在Hive之外被修改了，就回触发钩子执行。

例如，某个脚本向分区2012/01/01中写入新的日志信息文件，可以在
hive CLI 中进行下面的调用：

			hive -e 'ALTER  TABLE log_messages TOUCH PPARTITION（year = 2012，month=1,day = 1;'

若此分区表不存在，则不会创建表或分区。

ALTER TABLE … ARCHIVE PARTITION 语句会讲分区内文件达成一个hadoop压缩包（HAR）文件。可以降低文件系统中的文件数，以及减轻 NameNode 压力，但不会减少任何存储空间。

	ALTER  TABLE log_messages ARCHIVE 
	PARTITION(year =2012,month=1,day =1);
	UNARCHIVE 替换ARCHIVE 可以反向操作，勇于区分表中独立的分区。

下面语句可以分别防止分区被删除和被查询：

	ALTER TABLE log_messages
	PARTITION(year =2012,month=1,day =1)  ENABLE  NO_DROP;

	ALTER TABLE log_messages
	PARTITION(year =2012,month=1,day =1)  ENABLE  OFFLINE;

ENABLE 替换DISABLE 可以达到反向操作的目的，这些操作不可用于非分区表。