【HQL】HiveQL 库和表的创建、修改、删除_和风与影_hql删除表

irpas 02-07 4179

??今天继续学习 HiveQL。HiveQL 和 MySQL 接近，但也有差异。它不支持行级插入操作、更新操作、删除操作，不支持事务。关注公众号回复 803 获取《Hive 编程指南》pdf。【HQL】HiveQL 库和表的创建、修改、删除

1.Hive 中的数据库

Hive 中的数据库本质上是表的目录或命名空间。常用操作：

# 创建数据库 create database financials; # 为了避免已经存在该数据库，可以使用 create database if not exists financials; # 查看数据库 show databases; # 正在表达式匹配数据库，例如查看 f 开头的数据库 show databases like 'f.*'; # 修改默认位置 create database financials location '/my/preferred/directory'; # 增加描述信息 create database financials comment 'Holds all financial tables'; # 查看信息 describe database finacials; # 增加键值对信息 create database financials with dbproperties('creator'='Mark','date'='2021-01-02'); # 查看键值对信息 describe database extended finacials; # 设置当前工作数据库 use financials; # 显示当前的数据库 set hive.cli.print.current.db=true; # 删除数据库(不能删除有表的数据库) drop database if exists financials; # 删除有表的数据库 drop database if exists financials cascade; 2.修改数据库

数据库名、所在位置目录不能修改，可以修改的是 dbproperties。

alter database financials set dbproperties('edited-by'=Joe); 3.创建表

遵从 SQL 语法惯例，但有扩展，例如可以指定存储位置、存储格式。

# 创建表 create table if not exists mydb.employees ( # mydb 指定了要创建表的数据库是 mydb name string comment 'Employee name', salary float comment 'Employee salary', subordinates array<string> comment 'name of subordinates', deductions map<string, float> comment 'key-deductions names, values-percentages', address struct<street:string, city:string, state:string, zip:int> comment 'Home address') comment 'Description of the table' tblproperties ('creator'='me','created_at'='2012-01-02 10:00:00', ...) location '/user/hive/warehouse/mydb.db/employees'; # 创建表时拷贝一张已经存在的表的模式(不会拷贝数据)，可接受 location 语句 create table if not exists mydb.employees2 like mydb.employees; # 显示表 show tables; # 显示指定数据库中的表 show tables in mydb; # 显示正则匹配的表 show tables 'empl.*'; # 查看表信息 describe extended mydb.employees; # 查看更多信息 describe formatted mydb.employees; # 查看某一列的信息 describe mydb.employees.salary; 3.1 内部表

刚刚创建的都是内部表。删除表时会同时删除数据。

3.1 外部表

创建外部表的方法：

# 创建外部表 create external table if not exists stocks ( # mydb 指定了要创建表的数据库是 mydb exchange string, symbol string, ymd string, price_open string, price_high string, price_low string, price_closse string, volume int, price_ad_close float) row format delimited fields terminated by ',' # 读取以逗号分隔的数据 location '/data/stocks'; 4.分区表、管理表

用分区表来分散压力。查询时使用 where 带上分区，查询更快。创建方法：

# 创建分区表 create table employees ( # mydb 指定了要创建表的数据库是 mydb 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);

上面的语句改变了数据的存储方式：

原本是：

hdfs://master_server/user/hive/warehouse/mydb.db/employees

现在变成：

.../employees/country=CA/state=AB .../employees/country=CA/state=BC ... .../employees/country=US/state=AL .../employees/country=US/state=AK

关于分区的其他命令：

# 设置严格模式(查询时where必须带上分区) set hive.mapred.mode=strict; # 查看分区 show partitions employees; # 查看某些分区 show partitions employees partition(country='US'); show partitions employees partition(country='US',state='AK'); # describe extended 也会显示分区 describe extended employees;

从本地目录（%HOME/california-employees）载入数据到表中：

load data loval inpath '${env:HOME}/california-employees' into table employees partition (country='US', state='CA'); 4.1 外部分区表

外部分区表是最常用的情况，比如日志文件分析。

# 创建外部分区表 create external table if not exists log_messages ( hms int, severity 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) lovation 'hdfs://master_server/date/log_messages/2012/01/02'; # 查看分区 show partitions log_messages; # describe extended 也会显示分区 describe extended log_messages; # 查看分区的路径 describe extended log_messages partition(year=2012,month=1,day=2); 4.2 自定义表的存储格式 # 自定义格式 create table employees ( # mydb 指定了要创建表的数据库是 mydb 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' # array 或 struct 中元素的分割 map keys terminated by '\003' # map 中键和值的分割 lines terminated by '\n' # 换行分割记录 stored as textfile; # textfile 表明所有字段都使用字母、数字、字符编码 5.删除表 drop table if exists employees; 6.修改表

alter table 会修改元数据，不会修改数据本身。

6.1 表重命名 alter table log_messages to logmsgs; 6.2 增加、修改和删除分区 # 增加分区 alter table log_messages add if not exists partition(year=2011,month=1,day=1) location '/logs/2011/01/01' partition(year=2011,month=1,day=2) location '/logs/2011/01/01' partition(year=2011,month=1,day=3) location '/logs/2011/01/01'; # 修改分区路径(不会从旧的路径移走或删除数据) alter table log_messages partition(year=2011,month=12,day=2) set location 's3n://ourbucket/logs/2011/01/02'; # 删除分区 alter table log_messages drop if exists partition(year=2011,month=12,day=2) 6.3 修改列信息 # 对字段重命名，修改位置、类型或注释 alter table log_messages change column hms hours_minites_seconds int comment 'The hours, minites, and seconds part of the timestamp' after severity; 6.4 增加列 alter table log_messages add columns( app_name string comment 'Application name' session_id long comment 'The current session id') 6.5 删除或替换列 alter table log_messages replace columns( hours_mins_secs int comment 'hour, minite, and seconds from timestamp', severity string comment'The message severity', message string comment 'The rest of the message'); 6.6 修改表的属性

可以增加、修改属性，但不能删除属性。

alter table log_messages set tblproperties( 'notes'='The process id is no longer captured; this column is always NULL'); 6.7 修改存储属性

修改存储格式和 SerDe 属性。

# 修改存储格式 alter table log_messages partition(year=2012,month=1,day=1) set fileformat sequencefile; # 使用一个 Java 类来处理 JSON 文件 alter table table_using_JSON_storage set serde 'com.example.JSONSerDe' with serdeproperties( 'prop1'='value1', 'prop2'='value2'); 6.8 分区的保护

enable 替换成 disable 可以方向操作。

# 防止分区被删除 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;

欢迎关注。每天分析大数据面经和技术。