hive 表创建及字段信息管理

 1.  分区表创建及数据导入

1.1  创建分区表

-- 以日期pt分区，字段用\t分隔，输入格式为txt,存储格式为orc
use db_name;
drop table if exists tablename;
CREATE TABLE IF NOT EXISTS tablename (
    aid string,
    gender int,                   --性别
    age string,                   --年龄
    num bigint,                
    value1 array<int>,      
    value2 array<string> 
) partitioned by (pt string comment "YYYY-MM-DD.HH_MM")
-- NULL DEFINED as 'null'
stored as orc  -- textfile
-- row format delimited fields terminated by '\t'
-- STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
-- OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;

-- 存储格式亦可指定为txt
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

注意：内部表与外部表的区别

• 外部表需要添加 external 关键字，删除外部表时，HDFS中的数据文件不会一起被删除；
• 内部表不需要 external 关键字，删除表时表数据及HDFS中的数据文件都会被删除。

1.2 向分区表导入数据

1.2.1  从其他表导入

-- 从其他表导入
-- (1) XX 表中没有pt字段
use db_name;
insert overwrite table tablename partition(pt='2020-08-12')
select a.*
from XX a;
-- (2) 以XX表中pt字段指定分区字段
use db_name;
insert overwrite table tablename partition(pt)
select a.*
from XX a;

1.2.2  从本地文件导入

-- filename 为txt格式
load data local inpath '/home/zz/filename' overwrite
into table tablename partition(pt='2020-08-12');

1.3  删除hive表的一个分区

alter table tablename drop if exists partition(pt='2020-08-12');

1.4  查看表相关信息

--1.查看hive建表语句，在hdfs中的位置
show create table tablename;

--2.查看hive表元数据信息（创建时间，字段，位置，数据条数，数据大小）
desc formatted tablename;

--3.查看分区表元数据信息：
desc formatted tablename partition(pt='2020-08-12');

--4.查看表分区：
show partitions tablename;

2. 创建hive 临时表

2.1   使用建表语句创建

可以指定分隔符，以orc格式存储数据

orc格式对数据进行了压缩，压缩比 1:20左右

-- 指定"\t"作为分隔符
use db_name;
drop table if exists tablename;
CREATE TABLE tablename (
    id string,
    name string,
    age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';

-- 从本地文件加载数据到hive
load data local inpath '/home/zz/filename' overwrite into table tablename;

快速建表

        创建一个新表，而新表的结构与查询的表（旧的表）的结构是一样的。

# 创建一个新表，而新表的结构与查询的表（旧的表）的结构是一样的。
create table newtable as 
select * 
from oldtable 
where 1=0;

        使用 where 1=0(这个条件始终为 false)，结果不会返回任何数据，只有表结构，可用于快速建表 。
 

2.2  直接从其他表导入数据

stored as 可以指定数据存储方式orc，textfile

create table XX stored as textfile   -- 以textfile格式保存表
as select * 
from tablename;  

2.3   建表时使用 loaction 指定数据位置

use db_name;
drop table if exists tablename;
CREATE TABLE tablename (
    id string,
    name string,
    age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
LOCATION 'hdfs://nswx/user/hive/warehouse/aa.bb';
-- LOCATION 's3://${s3_bucket}/db_name/table_name'
-- 亦可本地文件
LOCATION '/home/zz/filename'

3. hive表重命名

alter table table_name rename to new_table_name;

4. 表字段修改

hive表，能增加列，不可以删除列，但可以改列名;

对于分区表，要使用cascade才会对所有分区生效，否则历史分区不会生效。

--修改字段名
alter table tablename change value1 value1_new string [cascade];
--修改字段类型
alter table tablename change column value1 value1 int [cascade];
--增加新字段
alter table tablename add columns(value1 int) [cascade];
alter table tablename add columns(
    encrypt_phone string comment '手机加密'
    ,registered_version_name string comment '注册版本号名称'
) cascade;

在指定字段(value1)后添加新字段(value7 int)

alter table XX add columns(value7 int) [cascade];
alter table XX add column value7 int after value;  -- mysql
alter table XX change column value7 value7 int after value1 [cascade];