第四章-HiveQL：数据定义

HiveQL：数据定义

Hive中的数据库

如果用户没有显示指定数据库，那么将会使用默认的数据库default.

第四章-HiveQL：数据定义

如何创建一个数据库？

create database finacials;
第四章-HiveQL：数据定义

如果数据库已经存在了，那么将会抛出错误信息，使用一下语句可以避免在这种情况下抛出错误信息。

create database if not exists finacials;
第四章-HiveQL：数据定义

如何查看Hive包含数据库？

show databases
第四章-HiveQL：数据定义

如果数据库特别多可以使用正则表达式来筛选出需要的数据库名

show databases like 'd.*';
第四章-HiveQL：数据定义

Hive会为每一个数据库创建一个目录，数据库中的表以这个数据库目录的子目录形式存储。
第四章-HiveQL：数据定义
但是有一个例外，default数据库，本身没有目录。
default中的表就在/user/hive/warehouse的目录下

数据库所有目录位于属性hive.metastore.warehouse.dir所指定的目录中，假设用户使用的是这个默认的配置，当创建出具库finacials时，Hive就会对应的创建一个以数据库名.db结尾的目录。
第四章-HiveQL：数据定义

当然用户也可以更改默认的位置

create database test_database
location '/my';

第四章-HiveQL：数据定义

增加描述信息

用户也可以为这个数据库增加一些描述信息，这样通过desc database 数据库名就可以查看该信息

create database test_database
comment 'good good study';

create database test_database
with dbproperties('author'='wangxiaodng','date'='2019-4-17');

第四章-HiveQL：数据定义

desc database 数据库名看不到
desc database extended 数据库名可以看到

use命令可以用于将某个数据库设置为用户当前的工作数据库。

use test_database
现在使用show tables就可以显示该数据库下所有的表

如何删除数据库？

drop database if exists test_database

默认情况下Hive不允许用户删除一个包含有表的数据库的。用户要么先删除数据库中的表，然后在删除数据库，要么在删除命令后加上关键字 CASCADE，这样可以使用Hive进行级联删除

drop database if exists test_database CASCADE

修改数据库

用户可以使用alter database命令为某个数据库的DBPROPERTIES设置键值对属性，来描述这个数据库的属性信息，数据库的其他元素的元数据信息(数据库名，数据库位置等)都是不可更改的。

alter database test_database set DBPROPERTIES{'edited-by'='wang'}

第四章-HiveQL：数据定义
没有办法可以删除或者修改数据库属性

创建表

create table new_employees(
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
)
COMMENT 'Description of the table'
row format delimited 
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
TBLproperties("creator"="me","created_at"="2019-5-1")
;

在这里遇到一个问题，之前sql是这样写的

create table new_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 '\t'
collection items terminated by ','
map keys terminated by ':'
COMMENT 'Description of the table'
TBLproperties("creator"="me","created_at"="2019-5-1")
;

然后提示我
第四章-HiveQL：数据定义

然后我查阅hiveDDL语法得知sql语法顺序很重要，对于表的comment描述必须要跟在）后边

官方文档的DDL规范

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [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]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
 
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

desc formatted new_employees;可查看表的详细信息
第四章-HiveQL：数据定义

TBLPROPERTIES

TBLPROPERTIES的主要作用是按键值对的格式为表增加额外的文档说明

表的属性

hive还会自动生成两个表的属性，一个是last_modified_by，其保存着最后修改这个表的用户名，另一个是last_modified_time，其保存着最后一次修改的新纪元时间（这个貌似是很久的版本才会有，目前已经取消）

第四章-HiveQL：数据定义

第四章-HiveQL：数据定义
默认情况下，Hive总是将创建的表的目录放置在这个表所属的数据库目录下。不过default数据库是一个例外，其在/user/hive/warehouse下并没有对应一个数据库目录。因此default数据库中的表目录会直接位于/user/hive/warehouse目录之后。(除非用户为其制定其他的路径)

用户还可以拷贝一张已经存在的表的表模式

create table if not exists mydb.employees2
like mydb.employees;
location '/user/myemployees';
--这里可以指定数据库的路径

第四章-HiveQL：数据定义

即使我们不在那个数据库下，我们也可以列举指定数据库下的表。

show tables in default;

如果有很多表还可以使用正则表达式来进行筛选

show tables 'empl.*';

查看表的信息

desc table
第四章-HiveQL：数据定义
desc extended table

desc formatted table

使用frmatted更加详细，而且可读性强

管理表

我们之前创建的表全是管理表，管理表也成为内部表，hive会控制管理表数据的生命周期。管理表不方便和其他工作共享数据。因此有时我们需要使用外部表。

外部表

create external table stocks1(
a string,
symbol string,
volume int)
row format delimited fields terminated by ','
location '/data/stocks';

第四章-HiveQL：数据定义
因为表示外部的所以hive并未完全拥有数据，因此删除该表不会删除其原有的数据，不过该表的描述信息会随之删除。

对于外部表也可以对一张已经存在的表进行复制

create external table employees3
like employees2
location '/path/to/data'

说明：

如果语句中省略external关键字的话，如果原表是外部表，那么新表也是外部表，如果原表是管理表那么新表也是内部表。但是如果加了external，不管原表是什么类型，新表都是外部表。location可选

分区表与管理表

数据分区的概念一般概念存在已久，通常使用分区来水平分散压力。

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

查看表的结构显示
第四章-HiveQL：数据定义
在这里分区字段为country与state

对数据分区的好处？

对数据进行分区，为为了更快的查询，如果不分区会全表扫描，如果分区就只扫描满足条件的一部分。

严格模式

如果表中的数据以及每个分区都很大的话，执行一个包含所有分区的查询可能引发一个巨大的mapreduce任务。其中一个安全措施就是设置严格模式
strict,这样如果对分区没有进行过滤的话，将会禁止提交任务。

设置的语句为set hive.mapred.mode=strict
第四章-HiveQL：数据定义

查看分区

可以使用show parttions table查看表的分区情况

第四章-HiveQL：数据定义

可以只查看特定分区下的分区（过滤功能）
show parttions table partition(country="us")
第四章-HiveQL：数据定义

向分区中插入数据

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

load data local inpath '/opt/test.txt'
into table employees
partition (country="us",state = "CA");

外部分区表

对于外部分区表不需要指定location,有一个alter table可以单独进行增加分区。这个语句需要为每一个分区指定一个值。例如

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

创建外部分区表

create external table out_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)
row format delimited 
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
;

第四章-HiveQL：数据定义

此时如果我们想查看某个分区存储在那个目录下需要使用

desc formatted out_employees partition(country='us',state='ca');

第四章-HiveQL：数据定义

alter table add parttiton并非只对外部表有用，对于管理表也同样适用。

适用其他的存储格式

默认情况（不指定）适用Textfile格式

也可以适用其他的格式（参考官方DDl撰写规范stored）

用户可以将TEXTFILE替换为其他hive所支持的内置文件格式，包括sequencefile 和RCFILE ，这两种文件都是使用二进制编码和压缩来优化磁盘空间以及IO带宽性能的。

hive使用一个inputformat对象将输入流分割成记录，然后使用一个outputformat对象来将记录格式化为输出流（例如查询的输出结果）再使用一个serde在读数据时，将记录解析成列，在写数据时将列编码成记录。

建立一个parquet格式的表

create table pa_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 '\t'
collection items terminated by ','
map keys terminated by ':'
STORED AS PARQUET;

insert into table pa_employees  select * from myemployees;

第四章-HiveQL：数据定义

这是普通表的
第四章-HiveQL：数据定义

删除表

drop table if exists tablename

对于管理表，表的元数据信息和表的数据都会被删除。

对于外部表，表的元数据信息会被删除，但是表中的数据不会被删除。

修改表

大多数表的属性可以通过alter table语句来进行修改。这种操作会修改元数据但不会修改数据本身。

表重命名

alter table tablename rename new_tablename

增加，修改和删除表的分区

alter table table_name add partition() location''
partition() location''

在同一个查询中可以同时增加多个分区

用户也可以修改分区的路径

alter table table_name partition()
set location ''

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

也可以删除某个分区

alter table table_name partition();

对于管理表，即使是使用add partition 增加的分区，分区时也是会同时和元数据一起删除的。但是外部表，分区内的数据不会删除。

修改列的信息

用户可以对某个字段进行重命名，并修改其位置，类型或者注释。

alter table table_name
change column old_name new_name type
comment ``
after column_name;

修改前
第四章-HiveQL：数据定义
修改后

关键字column和comment都是可选的，如果用户想要字段到首位需要使用first替换after column_name

增加列

alter table table_name add columns(
app_name string comment 'hour',
session_is string comment 'hour'
)

如果增加的字段位置时错误的可以使用alter table 表名 change column进行调整

删除或者替换列

alter table myemployees replace columns(
hour_mins_secs int comment'hour,minute,seconds'
)

第四章-HiveQL：数据定义

修改表属性

用户可以增加附加的属性，或者修改已经存在的属性，但是无法删除属性。

alter table myemployees set tblproperties(
'notes' = 'The process id is no longer captured'
)

第四章-HiveQL：数据定义

修改存储属性

下面这个语句讲一个分区的存储格式修改成了SEQUENCE

alter table myemployees 
partition(year = 2012,month = 1,day = 1)
set fileformat sequencefile;

如果表是分区表，那么需要使用partition子句。
用户也可以指定新的Serde，并为其指定Serde属性，或者修改已经存在的Serde属性。下面的例子演示的表使用一个名为com.example.JSONSerDe的Java类老处理记录中的JSON编码的文件。

alter table table_using_json_storage
set serde 'com.example.JSONSerDe'
with Serperporties(
'prop1'='value1'
)

下面这个例子显示如何添加新的属性

alter table table_using_json_storage
set Serperporties(
"prop1" = "value3"
)

也可以修改在创建表时的存储属性

alter table stocks
clustered by (exchange,symbol)
stored by (symbol)
into 48 buckets;

设置位置等，详情请查询Hive官网
第四章-HiveQL：数据定义

其他的修改语句

可以在脚本中写入

hive -e 'alter table log_messages touch partition(year = 2012)'

第四章-HiveQL：数据定义

HiveQL：数据定义

Hive中的数据库

如何创建一个数据库？

如何查看Hive包含数据库？

增加描述信息

如何删除数据库？

修改数据库

创建表

TBLPROPERTIES

表的属性

查看表的信息

管理表

外部表

分区表与管理表

对数据分区的好处？

严格模式

查看分区

向分区中插入数据

外部分区表

适用其他的存储格式

删除表

修改表

表重命名

增加，修改和删除表的分区

修改列的信息

增加列

删除或者替换列

修改表属性

修改存储属性

其他的修改语句

相关推荐