HiveQL:数据定义
Hive中的数据库
如果用户没有显示指定数据库,那么将会使用默认的数据库default.
如何创建一个数据库?
create database finacials;
如果数据库已经存在了,那么将会抛出错误信息,使用一下语句可以避免在这种情况下抛出错误信息。
create database if not exists finacials;
如何查看Hive包含数据库?
show databases
如果数据库特别多可以使用正则表达式来筛选出需要的数据库名
show databases like 'd.*';
Hive会为每一个数据库创建一个目录,数据库中的表以这个数据库目录的子目录形式存储。
但是有一个例外,default数据库,本身没有目录。
default中的表就在/user/hive/warehouse
的目录下
数据库所有目录位于属性hive.metastore.warehouse.dir所指定的目录中,假设用户使用的是这个默认的配置,当创建出具库finacials
时,Hive就会对应的创建一个以数据库名.db结尾的目录。
当然用户也可以更改默认的位置
create database test_database
location '/my';
增加描述信息
用户也可以为这个数据库增加一些描述信息,这样通过desc database 数据库名就可以查看该信息
create database test_database
comment 'good good study';
create database test_database
with dbproperties('author'='wangxiaodng','date'='2019-4-17');
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'}
没有办法可以删除或者修改数据库属性
创建表
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")
;
然后提示我
然后我查阅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;
可查看表的详细信息
TBLPROPERTIES
TBLPROPERTIES的主要作用是按键值对的格式为表增加额外的文档说明
表的属性
hive还会自动生成两个表的属性,一个是last_modified_by,其保存着最后修改这个表的用户名,另一个是last_modified_time,其保存着最后一次修改的新纪元时间(这个貌似是很久的版本才会有,目前已经取消)
默认情况下,Hive总是将创建的表的目录放置在这个表所属的数据库目录下。不过default数据库是一个例外,其在/user/hive/warehouse下并没有对应一个数据库目录。因此default数据库中的表目录会直接位于/user/hive/warehouse目录之后。(除非用户为其制定其他的路径)
用户还可以拷贝一张已经存在的表的表模式
create table if not exists mydb.employees2
like mydb.employees;
location '/user/myemployees';
--这里可以指定数据库的路径
即使我们不在那个数据库下,我们也可以列举指定数据库下的表。
show tables in default;
如果有很多表还可以使用正则表达式来进行筛选
show tables 'empl.*';
查看表的信息
desc table
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';
因为表示外部的所以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);
查看表的结构显示
在这里分区字段为country与state
对数据分区的好处?
对数据进行分区,为为了更快的查询,如果不分区会全表扫描,如果分区就只扫描满足条件的一部分。
严格模式
如果表中的数据以及每个分区都很大的话,执行一个包含所有分区的查询可能引发一个巨大的mapreduce任务。其中一个安全措施就是设置严格模式
strict
,这样如果对分区没有进行过滤的话,将会禁止提交任务。
设置的语句为set hive.mapred.mode=strict
查看分区
可以使用show parttions table
查看表的分区情况
可以只查看特定分区下的分区(过滤功能)
show parttions table partition(country="us")
向分区中插入数据
在管理表中可以通过载入数据的方式创建分区
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 ':'
;
此时如果我们想查看某个分区存储在那个目录下需要使用
desc formatted out_employees partition(country='us',state='ca');
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;
这是普通表的
删除表
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;
修改前
修改后
关键字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'
)
修改表属性
用户可以增加附加的属性,或者修改已经存在的属性,但是无法删除属性。
alter table myemployees set tblproperties(
'notes' = 'The process id is no longer captured'
)
修改存储属性
下面这个语句讲一个分区的存储格式修改成了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官网
其他的修改语句
可以在脚本中写入
hive -e 'alter table log_messages touch partition(year = 2012)'