HIVE--DDL语句

最新推荐文章于 2024-04-24 09:00:13 发布

铜锣湾扛把子-数据

最新推荐文章于 2024-04-24 09:00:13 发布

阅读量6k

点赞数 1

分类专栏： Hive 文章标签： hive

本文链接：https://blog.csdn.net/qq_37296285/article/details/97056297

版权

Hive 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

文章目录

DDL(对表的结构进行修改的语句)

DDL(对表的结构进行修改的语句)

删除操作
删除库表
drop database if exists 数据库名；    
drop database if exists 数据库名 cascade;   //强制删除库

drop table if exists 表名；
外部表不能按一般步骤drop，否则你还得手动去hdfs rm -r xxx文件，并且还可能遇到因为文件太大而不能删除等问题；
ALTER TABLE xxx SET TBLPROPERTIES('EXTERNAL'='False');

drop table xxx;
删除数据但表结构还在
truncate table 表名；       //把表中数据删掉了，但表结构还在

注意:不可以删除外部表，因为外部表的元数据并不是存放在Hive Meta store中
分区：
alter table 表名 drop if exists partition(分区字段=""); 

alter table 表名 drop partition(分区字段>="",分区字段<="");   --  这里只能是一个分区，类似于双分区的dt和hh，不可以使用dt='..',hh>'..'这样写的话，知会删除dt='...'，后面的hh不会生效
建表语句【3种】
create table if not exists qttdw.dwd_qinnovate_new_retain_user_di(
devcice_uuid string comment '设备唯一标识',
two_flag string comment '次留标识',
three_flag string comment '三留标识',     -- 一定要注意数字类型等的表示范围，否则会变成null
seven_flag string comment '七留标识',
forteen_flag string comment '十四留标识',
thrity_flag string comment '三十留标识'
)
comment '留存用户明细表'
partitioned by(
dt string comment '日期分区'
)
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
row format delimited               -- 在spark2.4.3中，只能搭配textfile的文件存储格式
fields termianted by '\t'          -- 在spark2.4.3中，只能搭配textfile的文件存储格式
lines terminated by '\n'           -- 在spark2.4.3中，只能搭配textfile的文件存储格式
stored as orc
tblproperties('orc.compression'='SNAPPY')
易错点:

partitioned by（要有ed）
都用单引号

方式二：
# 使用create as select 语句
create table 表名 as select ...          # 此处的新的列名称会使用它原有的列名称
或者
create table 表名(列名1，列名2，...) as select ...  # 此处多做了一个重新命名列的操作
注意点：
目标表不允许分区操作的，原表的分区字段即使select过来，也会变成单纯的字段

目标表不允许外部表 create external table as select…
目标表会默认将存储格式设置为textfile格式，但是可以在sql中自己指定
create table 表名 
row format delimited
fields terminated by '\t'
lines terminated by '\n'       
stored as orc
tblproperties('orc.compression'='SNAPPY') 
as select 
这种方式生成的新表字段注释会消失
方式三：【可以直接有分区；可以有外部表；但必须和旧表一样的表结构】
# 或者通过hdfs复制数据并修复新表的分区相关元数据
create 【external】table 新表名 like 旧表名；
hdfs dfs -cp /old_table/ /path/new_table/
msck repair table newtable 


#  这种方式的速度会慢于上面的方式，但是如果对数据有操作，就只可以使用这种方式了
create [external] table partition_test like old_table;
insert into table partition_test
partition(dt)
select 
trim(userid) userid,
trim(cardno) cardno,
if(lower(trim(flag)) in ("true","1","01"),"1","0") flag,
substr(cardtype,1,1) cardtype,
trim(times) times,
substr(times,0,10) dt
from old_table
order by rand()
limit 100;
内部表和外部表
create external table t2(
id int,
name string
)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as orc
location '自定义存储目录'；
未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）

区别：

外部表数据存储位置由自己决定；如果不指定，则和内部表一样的位置

内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse）存储路径不是只能写hdfs集群路径，本地路径也是可以的，如　LOCATION ‘file:home/hjl/sunwg/’

删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除

对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）

使用场景

每天采集的ng日志和埋点日志,在存储的时候建议使用外部表，因为日志数据是采集程序实时采集进来的，一旦被误删，恢复起来非常麻烦。而且外部表方便数据的共享。（实时采集的数据用外部）
抽取过来的业务数据，其实用外部表或者内部表问题都不大，就算被误删，恢复起来也是很快的，如果需要对数据内容和元数据进行紧凑的管理, 那还是建议使用内部表。否则不但每次都要指定路径，而且也有可能指定的乱七八糟 (内部表方便数据管理)
在做统计分析时候用到的中间表，结果表可以使用内部表，因为这些数据不需要共享，使用内部表更为合适。并且很多时候结果分区表我们只需要保留最近3天的数据，用外部表的时候删除分区时无法删除数据。(内部表删除操作方便，可以避免数据仍然占据空间)
修改表字段，类型等相关
-- 修改字段数据类型，列注释，列位置
-- 修改字段类型，比较容易报错，因为不同的类型在例如parquet等的存储格式中，会有不同的处理，一旦更改，较容易报错。见《spark--异常9》
alter table 表名 change column 原字段名称 现字段名称 数据类型
[comment 列注释] [first或者after 列名]


-- 新增字段表,新增字段位置只能在最后，但肯定在分区字段以前.新加的字段在以前的日期中都为null
alter table 表名 add columns(字段名 数据类型 comment 列注释)
-- 表示用指定字段替换表中所有字段，可以用于删除某个特定字段，replace后不写要删除的那个字段即可
alter table 表名 replace columns(字段名 字段类型)  

-- 重命名表
alter table 表名 rename to 新表名

-- 修改表文件格式
alter table 表名 set serdeproperties('field.delim'='\t')  -- 当然还可以写其他的

-- 修改表的注释
ALTER TABLE table_name SET TBLPROPERTIES('comment' = '这是表注释!');