在hive中创建分区表,再关联到hdfs有关位置,而不需导入数据到hive表

超、凢脫俗 2022-06-07 12:11 550阅读 0赞

【需求】有时候我们不想导入数据到hive中，而是通过在hive中创建关联表的方式查询hdfs上的数据，之后就能通过hive客户端或者spark应用程序获取hive的数据了。

【原理】由于在hdfs中已存入了我们提前整理好的结构化数据(例如每条记录都是以逗号分隔)，那么在hive中建一个相同结构的表，再把此表关联到相应的hdfs目录就可以了。

下面一个小案例讲一下我的经验。步骤——

**步骤一：**在hive中创建外部表**；****步骤二：**修改hive表,使其关联HDFS指定目录，就能将二者的数据自动关联**；****步骤三：**验证是否关联成功

**步骤一：在hive中创建外部表**  
hive> CREATE EXTERNAL TABLE **email\_ralationship** (sender STRING, receiver STRING, copied STRING)  
> PARTITIONED BY (dt STRING)  
> ROW FORMAT DELIMITED  
> FIELDS TERMINATED BY '\\073'  
> STORED AS TEXTFILE;  
OK  
Time taken: 0.319 seconds  
\[注意点\] 这里的字段分隔符'\\073'是ascii码表里的分号表示，hive中不能用FIELDS TERMINATED BY ';' 否则报错

**步骤二：修改hive表：使其关联HDFS指定目录，就能将二者的数据自动关联**

\# 报错：访问被拒绝  
hive> ALTER TABLE **email\_ralationship** ADD PARTITION (dt="2017-01-09")  
> LOCATION "/apps/tony\_kidkid/email\_relationship/2017-01-09/2017-01-09";  
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:java.security.AccessControlException: Permission denied: user=hive, access=WRITE, inode="/apps/tony\_kidkid/email\_relationship/2017-01-09/2017-01-09":hdfs:hdfs:-rwxr-xr-x  
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.check(FSPermissionChecker.java:319)  
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:219)  
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:190)  
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkPermission(FSDirectory.java:1827)  
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkPermission(FSDirectory.java:1811)  
at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkPathAccess(FSDirectory.java:1785)  
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkAccess(FSNamesystem.java:8558)  
at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.checkAccess(NameNodeRpcServer.java:2064)  
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.checkAccess(ClientNamenodeProtocolServerSideTranslatorPB.java:1451)  
at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod(ClientNamenodeProtocolProtos.java)  
at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:640)  
at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:982)  
at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2313)  
at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2309)  
at java.security.AccessController.doPrivileged(Native Method)  
at javax.security.auth.Subject.doAs(Subject.java:422)  
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724)  
at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2307)  
)  
\# 排错：查看我要关联的hdfs上的文件访问权限，发现所有者是用户'hdfs'而不是'hive'  
\[hdfs@kmr-5b9c18fc-gn-7b3518df-master-1-001 root\]$ hdfs dfs -ls -h /apps/tony\_kidkid/email\_relationship/2017-01-09  
Found 46 items  
\-rwxr-xr-x 3 hdfs hdfs 81.0 K 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-01-09  
\-rwxr-xr-x 3 hdfs hdfs 652.2 K 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-09-04  
\-rwxr-xr-x 3 hdfs hdfs 2.0 M 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-09-05  
\-rwxr-xr-x 3 hdfs hdfs 2.1 M 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-09-06  
\-rwxr-xr-x 3 hdfs hdfs 1.1 M 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-09-07  
\-rwxr-xr-x 3 hdfs hdfs 1.3 M 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-09-08  
\-rwxr-xr-x 3 hdfs hdfs 718.8 K 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-09-09  
。。。 。。。 。。。 。。。。。。 。。。 。。。 。。。。。。。。。。。。 。。。 。。。 。。。。。。  
\-rwxr-xr-x 3 hdfs hdfs 1.9 M 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-10-17  
\-rwxr-xr-x 3 hdfs hdfs 1.6 M 2017-10-19 17:03 /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-10-18  
  
\# 排错：把owner改成hive  
\[hdfs@kmr-5b9c18fc-gn-7b3518df-master-1-001 root\]$ hdfs dfs -chown -R hive:hdfs /apps/tony\_kidkid/email\_relationship/  
  
\# 排错：再到hive shell上尝试关联hdfs文件，报错：关联的HDFS路径不是一个目录  
hive> ALTER TABLE email\_ralationship ADD PARTITION (dt="2017-01-09")  
> LOCATION "/apps/tony\_kidkid/email\_relationship/2017-01-09/2017-01-09";  
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:MetaException(message:hdfs://hdfs-ha/apps/tony\_kidkid/email\_relationship/2017-01-09/2017-01-09 is not a directory or unable to create one))  
  
\# 解决：指定表email\_ralationship关联HDFS的目录，成功  
hive> ALTER TABLE email\_ralationship ADD PARTITION (dt="2017-01-09")  
> LOCATION "/apps/tony\_kidkid/email\_relationship/2017-01-09/";  
OK  
Time taken: 0.503 seconds

**步骤三：验证是否关联成功**

\# 先在hive表查询前五条记录；再到hdfs查询前五条记录，结果吻合  
hive> select \* from email\_ralationship where dt="2017-01-09" limit 5; \# 查询时必须要指定分区 dt="2017-01-09"  
OK  
w\_chenjunbin@hello.net ms\_all\_zh@hello.net NULL 2017-01-09  
yegoodboy@hello.net heminjian@hello.net,yangdada@hello.net,yueyue1@hello.net dududu@hello.net,fuzheng@hello.net,zhuyunfeng@hello.net 2017-01-09  
zhushusheng@hello.net w\_huangruihong@hello.net,w\_zzhh@hello.net,xuemuhong@hello.net nihaomei@hello.net,cendawei@hello.net,ddjj@hello.net,dududu@hello.net,fangping@hello.net,jiangheng@hello.net,ligang1@hello.net,liuqiang2@hello.net,qiaoliang@hello.net,w\_chenxiangbao@hello.net,binwongti@hello.net,wangnima@hello.net,wangtao@hello.net,ms\_dev\_pub@hello.net,wuyanxin@hello.net,yueyue1@hello.net,zhanxinkun@hello.net,zhushusheng@hello.net,zhuyunfeng@hello.net 2017-01-09  
lifuxiao@hello.net pc\_jr@hello.net,ms\_dev\_pub@hello.net,nimawang@hello.net binwongti@hello.net 2017-01-09  
w\_chenjunbin@hello.net ms\_all\_zh@hello.net NULL 2017-01-09  
Time taken: 1.389 seconds, Fetched: 5 row(s)  
  
\[hdfs@kmr-5b9c18fc-gn-7b3518df-master-1-001 root\]$ hdfs dfs -cat /apps/tony\_kidkid/email\_relationship/2017-01-09/2017-01-09 | head -n 5  
w\_chenjunbin@hello.net;ms\_all\_zh@hello.net  
yegoodboy@hello.net;heminjian@hello.net,yangdada@hello.net,yueyue1@hello.net;dududu@hello.net,fuzheng@hello.net,zhuyunfeng@hello.net  
zhushusheng@hello.net;w\_huangruihong@hello.net,w\_zzhh@hello.net,xuemuhong@hello.net;nihaomei@hello.net,cendawei@hello.net,ddjj@hello.net,dududu@hello.net,fangping@hello.net,jiangheng@hello.net,ligang1@hello.net,liuqiang2@hello.net,qiaoliang@hello.net,w\_chenxiangbao@hello.net,binwongti@hello.net,wangnima@hello.net,wangtao@hello.net,ms\_dev\_pub@hello.net,wuyanxin@hello.net,yueyue1@hello.net,zhanxinkun@hello.net,zhushusheng@hello.net,zhuyunfeng@hello.net  
lifuxiao@hello.net;pc\_jr@hello.net,ms\_dev\_pub@hello.net,nimawang@hello.net;binwongti@hello.net

w\_chenjunbin@hello.net;ms\_all\_zh@hello.net

**成功完成！**