Hive 库表操作: 创建，查看，修改，删除

Hive 库表操作: 创建，查看，修改，删除

参考链接:

Hive Tutorial

Hive LanguageManual

库操作

• 创建库 create database if not exist test;

• 创建库带注释 create database test comment "learning hive" location hdfs_path

• 查看数据库 show databases;

• 查看满足条件的数据库 show databases like 'price*'

• 显示数据库的详细信息 desc database extended tmp;

• 查看正在使用的库 select current_database();

• 查看创建库时的详细语句 show create database tmp;

• 删除库 drop database if exits dbname [restrict | cascade];

• 展示库中的表 show tables in tmp

• 切换库 use dev;

表操作

Hive 数据类型(data_type)

• boolean - true/false - TRUE
• tinyint - 1字节(2^8)的有符号整数 -128~127 - 1
• smallint - 2字节(2^16)的有符号整数 -32768~32767 - 2
• int - 4字节的有符号整数 - 1
• bigint - 8字节的有符号整数 - 1
• float - 4字节单精度浮点数 - 1.0
• double - 8字节双精度浮点数 - 1.0
• deicimal - 任意精度的带符号小数 - 1.0
• String - 字符串 - ‘a’,“b”
• varchar - 变长字符串 - “A”,‘b’
• char - 固定长度字符串 - “a”,‘b’
• binary - 字节数组
• timestamp - 时间戳，纳秒精度 - 122327493795
• date - 日期 - “2020-04-07”
• array - 有序的同类型的集合 - array(1,2)
• map - key-value - map(‘a’,1)
• struct - 字段集合 - struct(‘1’,1,1,0)
• union_type - uniontype <date_type, data_type, …>

存储格式 (file_format)

• textfile - 默认格式，行存储，数据不压缩，磁盘开销大
• SequenceFile - 二级制文件支持，使用方便，可分割，可压缩(NONE, RECORD(压缩率低), BLOCK)
• RCFile - 行列存储相结合的存储方式
• ORCFile - 数据按块存储，每块列存储，每个块都存储一个索引
• Parquet - 行式存储，压缩较好
• jsonfile

小知识

• 内部表和外部表的区别

  • 内部表由Hive 自身管理，外部表由HDFS管理
  • 删除内部表会直接删除元数据及存储数据，删除外部表仅会删除元数据，HDFS上的文件不会被删除
  • 创建外部表时，仅记录数据所在路径，不对数据位置做任何改变

• 临时表 temporary

  • 表只对当前任务有效，关闭任务表自动删除
  • 表空间位于/tmp/hive/
  • 如果创建的临时表表名已存在，实际上使用的是临时表

创建表

直接创建

create [temporary] [external] table [if not exists] [db.name] table 
    [(col_name data_type [column_constraint_specification] [comment col_comment], ... [constraint specification] )]
    [comment table_comment]
    [partitioned by (col_name data_type [comment col_comment], ...)] -- 分区字段
    [clustered by (col_name data_type [comment col_comment] ) [sorted by (col_name [ase|desc], ...)] into num_buckets buckets]
    [row format fow_format]
    [stored as file_format]
    [location hdfs_path]
    [tblproperties (property_name = property_value, ...)];

-- 样例

create external table if not exists tmp.create_table_test (
sku_id string comment "商品编号" ,
cid3 int comment "三级品类id",
price float comment "价格",
sale_qtty int comment "销量"
)
comment "测试使用"
partitioned by (dt string comment "日期")
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
location
  'hdfs://ns7/user/mart_scr/tmp.db/create_table_test'
  tblproperties (
    "author" = 'xueyuan24',
    "mart_name" = "mart_rmb"
);

复制表结构，使用like建表

create [temporary] [external] table [if not exists] [db.name]table_name like existing_table_or_view_name
[location hdfs_path]

-- 样例
create external table if not exists tmp.create_table_test_like like tmp.create_table_test;

使用 as select 创建表 CTAS

create table if not exists tmp.create_table_test_select as select * from tmp.create_table_test;

-- 不能创建partition, external, bucket table

查看表

• 查看当前库的所有表 show tables
• 查看某库中的所有表 show tables in db_name
• 查看库中已price开头的表 show tables like 'price*'
• 查看表的信息 desc table_name
• 查看表的详细信息 desc extended table_name / desc fomatted table_name
• 查看分区信息 show partitions table _name
• 查看建表详细语句 show create table table_name

修改表

• 修改表名 alter table table_name rename to new_table_name;

• 增加一个字段 alter table table_name add columns (col_name, string) [CASCADE|RESTRICT] ;

• 修改一个字段 alter table table_name change old_name new_name string;

• 添加表注释 alter table table_name set tblproperties ('author' = 'my_name','commet':'表注释')

修改分区

• 添加分区 alter table table_name add partition(col_partition_name = 'new1');
• 添加多个分区 alter table table_name add partiton (col_partition_name = 'new1') partition(col_partition_name = 'new2');
• 修改分区：修改分区存储目录 alter table table_name if not exists partition (col_partition_name = 'new1') location *;
• 删除分区 alter table table_name drop partition (col_partition_name = 'new1');

删除表

• 删除表 drop table table_name;
• 清空表 truncate table table_name;

写入写出

• 插入数据到表

insert overwrite table new_table 
select * from old_table;

• 动态分区插入

FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US')
       SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'US';

• Hive 写入数据到本地

insert overwrite local  directory '/home/username/*'
select * from table_name;

• 本地数据写入Hive

load data local inpath '/home/username/*.txt' into table table_name partition(...)