hive外表

内表和外表的不同：

创建外部表需要指定external关键字，内表不需要；

外表的表结构和表数据是解绑的，表相当于定义了去解析相应的文件的规范，删除外表时，hdfs中数据文件不会一起删除；删除内表时，表数据及hdfs中数据文件会一起删除。

1、外表

1.1、hive 处理json数据总体来说有两个方向的路走

1、将json以字符串的方式整个入Hive表，然后通过使用UDF函数解析已经导入到hive中的数据，比如使用LATERAL VIEW json_tuple的方法，获取所需要的列名。

例子：https://blog.csdn.net/weixin_37139561/article/details/90415619

get_json_object(group_info,'$.advertiser_id')

lateral view json_tuple(strategy_facts

,'firstLogin'

,'multiScore'

) b as

first_login_flag

,multi_score

2、在导入之前将json拆成各个字段，导入Hive表的数据是已经解析过得。这将需要使用第三方的SerDe（“序列化器和反序列化器”的缩写）,该方式只能解析一层的json。

1.2、文件格式存储

1.Stored as textfile -- 默认格式，数据不做压缩，磁盘开销大，数据解析开销大。

实际上等于：

STORED AS INPUTFORMAT

'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

2.stored as sequencefile -- SequenceFile是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。

实际上等于：

STORED AS INPUTFORMAT

'org.apache.hadoop.mapred.SequenceFileInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.mapred.SequenceFileOutputFormat'

3.stored as rcfile -- 全称Record Columnar File，列式记录文件，是一种类似于SequenceFile的键值对（Key/Value Pairs）数据文件。

实际上等于：

STORED AS INPUTFORMAT

'org.apache.hadoop.hive.ql.io.RCFileInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'

4.stored as orcfile -- 优化rcfile

实际上等于：

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'

5.Parquet文件格式

实际上等于：

ROW FORMAT SERDE

'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'

STORED AS INPUTFORMAT

'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'

 

create external table if not exists dbank.dbank_f_reconciliation_sdictktrust_loan(

outOrderNo string comment '货款编号',

paymentAmount string comment '货款金额（元）',

realPayDate string comment '交易时间(YYYY-MM-DD)')

comment "国投泰康信托放款对账文件" -- 表中文名

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' -- 比使用get_json_object或json_tuple解析方便（官方）

STORED AS TEXTFILE -- 文件存储格式

location 'oss://lattebank-bankgateway-prod/Reconciliation_SDICTKTRUST/loan'; -- 绝对路径数据导入新建的表 桶名：lattebank-bankgateway-prod

 

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'

tblproperties ("skip.header.line.count"="1", "skip.footer.line.count"="2") -- 跳过首行，跳过末尾两行

field.delim是表的两个列字段之间的文件中的字段分隔符.

其中serialization.format是文件序列化时表中两个列字段之间的文件中的字段分隔符.

ROW FORMAT delimited

fields terminated by '|' -- 文件格式以“｜”分隔符分隔

 

DP建外表

LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/lattebank-bigdata-dp-ads-app-marketing/dp_ads_app_marketing.db/ads_app_marketing_rta_original_new_device_data_di'; -- oss-cn-beijing-internal.aliyuncs.com OSS存储空间对应的域名，内网访问

 

1.3、清理外表的三个策略

1.将外部表文件所在目录指定为一个空的目录。

2.外部表改为内部表，利用truncate清空内部表数据后，再把内部表改回外部表。

alter table test_external set TBLPROPERTIES('EXTERNAL'='false');

truncate table test_external;

alter table test_external set TBLPROPERTIES('EXTERNAL'='true');

3.建一张空的表结构完全一致的外表，使用insert overwrite table 也可以达到清空外表的功能。

1.4、msck repair table tb_name

Msck repair table 命令主要是解决通过hdfs dfs -put或者hdfs api写入hive分区表的数据在hive无法被查询到的问题

hive的metastore服务主要是存储一些元数据信息，比如数据库名，表名或者表的分区等等信息。

如果不是通过hive的insert等插入语句，很多分区信息在metastore中是没有的，如果插入分区数据量很多的话，

用 ALTER TABLE table_name ADD PARTITION 一个个分区添加十分麻烦。只需要运行MSCK REPAIR TABLE命令，

Hive通过检测表在hdf上的文件，把没有写入metastore的分区信息写入。