hive 写es外部表报错 hive外部表加载数据

转载

游侠小影 2023-07-12 11:08:20

文章标签 hive 写es外部表报错大数据 shell hadoop 数据 文章分类 Hive 大数据

hive 关联表和外表的区别：

1. 外部表需要external关键之，location是数据文件默认存放位置，不管是不管是select 还是load的数据都存放在这里。导入数据到外部表，数据并没有mv到数据仓库目录，而是在loacation目录。

2.内部表建表时也能加上location,作用和外部表一样，都是表数据的存放路径，不同的是drop table时内部表会将hdfs上的数据也删掉，外部表仅仅是删除表的元数据，原始数据不会删除。

Hive 外表的优点：

1.安全，外部表不用担心表删除带来的风险。在Hive当中，对于外部表来说，我们在删除表数据的时候，删除的仅仅是表的元数据信息，但是却不能删除掉表中的真实数据。
2.灵活，方便数据共享，使用更加灵活。

外部数据

hive> dfs -ls /dw/im/prod/sessionContent;
Found 7 items
drwxr-xr-x   - icosc  hadoop          0 2018-11-07 23:55 /dw/im/prod/sessionContent/2018-11-07
drwxr-x--x   - hadoop hadoop          0 2018-11-08 23:55 /dw/im/prod/sessionContent/2018-11-08
drwxr-x--x   - hadoop hadoop          0 2018-11-09 23:55 /dw/im/prod/sessionContent/2018-11-09
drwxr-x--x   - hadoop hadoop          0 2018-11-10 23:55 /dw/im/prod/sessionContent/2018-11-10
drwxr-x--x   - hadoop hadoop          0 2018-11-11 23:55 /dw/im/prod/sessionContent/2018-11-11
drwxr-x--x   - hadoop hadoop          0 2018-11-12 23:55 /dw/im/prod/sessionContent/2018-11-12
drwxr-x--x   - hadoop hadoop          0 2018-11-13 13:55 /dw/im/prod/sessionContent/2018-11-13
hive>

创建Hive外表

根据数据的存放位置，我在建表 sql 中指定 location

use temp;
create external table tmp_im_session_content(
  `eventType` string comment "会话类型",
  `channelId` int comment "渠道 id",
  `sourceType` int comment "",
  `sessionId` string COMMENT "会话session id",
  `cId` string COMMENT "会话 id",
  `userId` string COMMENT "用户id",
  `vccId` string COMMENT "企业 id",
  `total` int COMMENT "",
  `index` int COMMENT '标识本次是第几次发送会话内容，从1开始 (每次最多发送10条聊天内容)',
  `content` array<struct<`from`:int,`to`:int,content:string,msgtype:int,`sequence`:bigint,createtime:bigint,errorcode:int,errorstring:string,status:int>> COMMENT '会话内容'
  )
  PARTITIONED BY (`date` string)
  location "/dw/im/prod/sessionContent"
  TBLPROPERTIES ("comment"="IM 会话内容 by gz");

use temp;
create external table tmp_im_session_content(
  `eventType` string comment "会话类型",
  `channelId` int comment "渠道 id",
  `sourceType` int comment "",
  `sessionId` string COMMENT "会话session id",
  `cId` string COMMENT "会话 id",
  `userId` string COMMENT "用户id",
  `vccId` string COMMENT "企业 id",
  `total` int COMMENT "",
  `index` int COMMENT '标识本次是第几次发送会话内容，从1开始 (每次最多发送10条聊天内容)',
  `content` array<struct<`from`:int,`to`:int,content:string,msgtype:int,`sequence`:bigint,createtime:bigint,errorcode:int,errorstring:string,status:int>> COMMENT '会话内容'
  )
  PARTITIONED BY (`date` string)
  location "/dw/im/prod/sessionContent"
  TBLPROPERTIES ("comment"="IM 会话内容 by gz");

数据装载和验证

我们在建表的时候，已经指定了location，但是我们去查的时候，发现并没有数据。

hive> select
    > a.*
    > from temp.tmp_im_session_content a
    > where `date` = '2018-11-07'
    > limit 40;
OK
Time taken: 0.107 seconds
hive>

为什么？？？？？？？？？？？？？？？？？？？

这是因为虽然在HDFS当中数据已经存在了，但是在Hive的元数据仓库中并没有，所以我们查不到数据。

给Hive 外表数据仓库装载数据

-- 创建分区并指定 location
alter table temp.tmp_im_session_content add if not exists partition (`date`='2018-11-07') location '/dw/im/prod/sessionContent/2018-11-07';

-- 验证数据
select
eventType,
channelId,
sourceType,
sessionId,
cId,
userId,
vccId,
total,
index,
content
from temp.tmp_im_session_content a 
where `date` = '2018-11-07' 
limit 40;

select
a.*
from temp.tmp_im_session_content a 
where `date` = '2018-11-07' 
limit 40;
-- 验证 ok

修复分区

Hive提供了一个"Recover Partition"的功能。原理相当简单，执行后，Hive会检测如果HDFS目录下存在，但表的metastore中不存在的partition元信息，更新到metastore中。如下：

msck repair table temp.tmp_im_session_content;

问题又来了，查询：

hive> select
    > a.*
    > from temp.tmp_im_session_content a
    > where `date` = '2018-11-08'
    > limit 40;
OK
Time taken: 0.107 seconds
hive>

还是没数据。这里就不得不说源数据存放的格式了。我创建的外表分区字段是 date，但是我们的源文件路径是/dw/im/prod/sessionContent/2018-11-08，按照分区目录的格式应该是/dw/im/prod/sessionContent/date=2018-11-08。因此，用修复命令没起作用。这就要求在写 HDFS 的时候做好格式约定，避免后续各种意想不到的惊喜 ☹。

这个问题，可以通过下面 laod data inpath 一节了解更详细的内容

laod data inpath 装载数据

命令如下：

laod data inpath '/dw/im/prod/sessionContent/2018-11-08' overwrite into table temp.tmp_im_session_content PARTITION (`date`='2018-11-08');

看下源文件状态：

hive> dfs -ls /dw/im/prod/sessionContent/
    > ;
Found 7 items
drwxr-xr-x   - icosc  hadoop          0 2018-11-07 23:55 /dw/im/prod/sessionContent/2018-11-07
drwxr-x--x   - hadoop hadoop          0 2018-11-09 23:55 /dw/im/prod/sessionContent/2018-11-09
drwxr-x--x   - hadoop hadoop          0 2018-11-10 23:55 /dw/im/prod/sessionContent/2018-11-10
drwxr-x--x   - hadoop hadoop          0 2018-11-11 23:55 /dw/im/prod/sessionContent/2018-11-11
drwxr-x--x   - hadoop hadoop          0 2018-11-12 23:55 /dw/im/prod/sessionContent/2018-11-12
drwxr-x--x   - hadoop hadoop          0 2018-11-13 14:35 /dw/im/prod/sessionContent/2018-11-13
drwxr-x--x   - hadoop hadoop          0 2018-11-08 23:55 /dw/im/prod/sessionContent/date=2018-11-08

发现什么没？原来的目录/dw/im/prod/sessionContent/2018-11-09的目录不见了，但是多了一个/dw/im/prod/sessionContent/date=2018-11-08的目录。

前面我们提到，如果分区很多的时候，可以通过msck repair table tableName 的方式来修复，但是如果格式不是 Hive 标准的分区文件存储格式，这个命令就没什么卵用。从刚刚的laod data命令我们也可以看出，此命令会移动数据文件，如果不是标准的问价存储路径，也会根据命令进行相应的调整。这一点很重要，需要记住。

如果需要传递日期变量，可以：

-- 针对标准的分区文件，即显示的按照分区字段存放的数据文件
laod data inpath '/dw/im/prod/sessionContent/date=${date}' overwrite into table temp.tmp_im_session_content PARTITION (`date`='${date}');

OR

-- 针对非标准的，未显示按照分区字段存放的数据文件
laod data inpath '/dw/im/prod/sessionContent/${date}' overwrite into table temp.tmp_im_session_content PARTITION (`date`='${date}');

这里有个小插曲，前面我通过alter table装载了2018-11-07的数据，目录没有变化，因此有用load data的方式处理：

laod data inpath '/dw/im/prod/sessionContent/2018-11-07' overwrite into table temp.tmp_im_session_content PARTITION (`date`='2018-11-07');

因为使用了overwrite INTO,导致/dw/im/prod/sessionContent/date=2018-11-07的数据被删除了☹️

只好手动将数据又移动回来：

hadoop fs -mv /user/dev/.Trash/181113150000/dw/im/prod/sessionContent/2018-11-07 /dw/im/prod/sessionContent/date=2018-11-07

然后

laod data inpath '/dw/im/prod/sessionContent/date=2018-11-07' into table temp.tmp_im_session_content PARTITION (`date`='2018-11-07');

分区管理

从最前面 HDFS 文件可以看到，还有还6个日期目录，我可以创建5个 alterr table 的语句来装载数据，但是如果有365天的数据需要加载呢？如果写多条365条alter 语句，岂不是很傻？

shell 循环创建

这种方式已经写过，不再赘述，故而此处省略。

load data 方式加载数据

只要load data，数据都会被移动到表的location下面。

删除外表分区管理

-- 删除分区
alter table temp.tmp_im_session_content drop partition(`date`=2018-11-07)

-- 删除分区
alter table temp.tmp_im_session_content drop partition(`date`=2018-11-07)