目录
一、目的
主要为了阐述在现场的开发环境中怎么定位查看hive表的一些信息:
- 是否为内部表、外部表
- 存储位置
- 字段分隔符
- 分区字段
- 建表语句
二、操作
1、建表
--创建内部表
create table inner_stu(id string,name string) row format delimited fields terminated by ',';
--创建外部表
create external table external_stu(id string,name string) row format delimited fields terminated by ',';
2、desc formatted 表名
hive> desc formatted inner_stu;
OK
# col_name data_type comment
id string
name string
# Detailed Table Information
Database: default
Owner: hive
CreateTime: Fri Jul 30 09:08:07 CST 2021
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://ambari1.master.com:8020/apps/hive/warehouse/inner_stu
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE {\"BASIC_STATS\":\"true\"}
numFiles 0
numRows 0
rawDataSize 0
totalSize 0
transient_lastDdlTime 1627607287
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,
Time taken: 0.389 seconds, Fetched: 33 row(s)
# 主要信息
Database: 所属库
Owner: hive
CreateTime: 表创建时间
Location: 表的存储位置
Table Type: 表类型:MANAGED_TABLE(内部表),EXTERNAL_TABLE(外部表)
3、desc 表名
hive> desc inner_stu;
OK
id string
name string
Time taken: 0.389 seconds, Fetched: 2 row(s)
可以单独查看表字段信息及分区情况;
4、show create table 表名
hive> show create table external_stu;
OK
CREATE EXTERNAL TABLE `external_stu`(
`id` string,
`name` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://ambari1.master.com:8020/apps/hive/warehouse/external_stu'
TBLPROPERTIES (
'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}',
'numFiles'='0',
'numRows'='0',
'rawDataSize'='0',
'totalSize'='0',
'transient_lastDdlTime'='1627607473')
Time taken: 0.144 seconds, Fetched: 18 row(s)
- ROW FORMAT DELIMITED FIELDS TERMINATED BY ','可以看出表字段为逗号分隔;
- 上面也是建表语句;
注意:如果遇到不认识的分隔符怎么导入数据:可以先建个确定字段分隔符的临时表,先把文件数据导入此临时表,然后再把此临时表的数据导入到目的表即可;