SR 2.0.1 创建hive 外部表失败

U_1645168806528_8959 · 2022年02月18日 07:32

如题，创建hive外部表错误。

CREATE EXTERNAL TABLE hive_t1 ( 
request_id varchar(100),
cityid int,
carrier int,
score int,
dt varchar(100) 
)
engine = hive 
properties ( 
"resource" = "hive0",
"database" = "ndata",
"table" = "earth_model",
"kerberos_principal" = "starrocks@TC.XXX.CN",
"kerberos_keytab" = "/etc/security/keytab/starrocks.keytab"
);

MySQL [test]> show resources;
+-------+--------------+---------------------+---------------------------------------+
| Name  | ResourceType | Key                 | Value                                 |
+-------+--------------+---------------------+---------------------------------------+
 | hive0 | hive         | hive.metastore.uris | thrift://hive-1.cvm.tc.xxxx.cn:9083 |
+-------+--------------+---------------------+---------------------------------------+
1 row in set (0.00 sec)


ERROR 1064 (HY000): get hive table from meta store failed: Socket is closed by peer.

FE日志报错。

2022-02-18 15:10:07,843 WARN (starrocks-mysql-nio-pool-18|2198) [HiveMetaStoreThriftClient.open():522] set_ugi() not successful, Likely cause: new client talking to old server. Continuing without it.
org.apache.thrift.transport.TTransportException: Socket is closed by peer.
	at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:130) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.protocol.TBinaryProtocol.readStringBody(TBinaryProtocol.java:411) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:254) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_set_ugi(ThriftHiveMetastore.java:4753) ~[hive-apache-3.0.0-5.jar:3.0.0-4-4-g7dde337]
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.set_ugi(ThriftHiveMetastore.java:4739) ~[hive-apache-3.0.0-5.jar:3.0.0-4-4-g7dde337]
	at com.starrocks.external.hive.HiveMetaStoreThriftClient.open(HiveMetaStoreThriftClient.java:514) ~[starrocks-fe.jar:?]
	at com.starrocks.external.hive.HiveMetaStoreThriftClient.reconnect(HiveMetaStoreThriftClient.java:400) ~[starrocks-fe.jar:?]
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient$1.run(RetryingMetaStoreClient.java:187) ~[hive-apache-3.0.0-5.jar:3.0.0-4-4-g7dde337]
	at java.security.AccessController.doPrivileged(Native Method) ~[?:1.8.0_251]
	at javax.security.auth.Subject.doAs(Subject.java:422) ~[?:1.8.0_251]
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1845) ~[hadoop-common-3.3.0.jar:?]
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:183) ~[hive-apache-3.0.0-5.jar:3.0.0-4-4-g7dde337]
	at com.sun.proxy.$Proxy41.getTable(Unknown Source) ~[?:?]
	at com.starrocks.external.hive.HiveMetaClient.getTable(HiveMetaClient.java:133) ~[starrocks-fe.jar:?]
	at com.starrocks.external.hive.HiveRepository.getTable(HiveRepository.java:112) ~[starrocks-fe.jar:?]
	at com.starrocks.catalog.HiveTable.validate(HiveTable.java:355) ~[starrocks-fe.jar:?]
	at com.starrocks.catalog.HiveTable.<init>(HiveTable.java:108) ~[starrocks-fe.jar:?]
	at com.starrocks.catalog.Catalog.createHiveTable(Catalog.java:4193) ~[starrocks-fe.jar:?]
	at com.starrocks.catalog.Catalog.createTable(Catalog.java:3044) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DdlExecutor.execute(DdlExecutor.java:115) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.StmtExecutor.handleDdlStmt(StmtExecutor.java:1219) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:434) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:248) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:395) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:631) ~[starrocks-fe.jar:?]
	at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:55) ~[starrocks-fe.jar:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_251]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_251]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_251]
2022-02-18 15:10:07,843 INFO (starrocks-mysql-nio-pool-18|2198) [HiveMetaStoreThriftClient.open():551] Connected to metastore.
2022-02-18 15:10:07,844 WARN (starrocks-mysql-nio-pool-18|2198) [HiveMetaClient.getTable():135] get hive table failed
org.apache.thrift.transport.TTransportException: Socket is closed by peer.
	at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:130) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:455) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:354) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:243) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77) ~[libthrift-0.13.0.jar:0.13.0]
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_get_table(ThriftHiveMetastore.java:1993) ~[hive-apache-3.0.0-5.jar:3.0.0-4-4-g7dde337]
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.get_table(ThriftHiveMetastore.java:1979) ~[hive-apache-3.0.0-5.jar:3.0.0-4-4-g7dde337]
	at com.starrocks.external.hive.HiveMetaStoreThriftClient.getTable(HiveMetaStoreThriftClient.java:587) ~[starrocks-fe.jar:?]
	at com.starrocks.external.hive.HiveMetaStoreThriftClient.getTable(HiveMetaStoreThriftClient.java:582) ~[starrocks-fe.jar:?]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_251]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_251]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_251]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_251]
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:208) ~[hive-apache-3.0.0-5.jar:3.0.0-4-4-g7dde337]
	at com.sun.proxy.$Proxy41.getTable(Unknown Source) ~[?:?]
	at com.starrocks.external.hive.HiveMetaClient.getTable(HiveMetaClient.java:133) ~[starrocks-fe.jar:?]
	at com.starrocks.external.hive.HiveRepository.getTable(HiveRepository.java:112) ~[starrocks-fe.jar:?]
	at com.starrocks.catalog.HiveTable.validate(HiveTable.java:355) ~[starrocks-fe.jar:?]
	at com.starrocks.catalog.HiveTable.<init>(HiveTable.java:108) ~[starrocks-fe.jar:?]
	at com.starrocks.catalog.Catalog.createHiveTable(Catalog.java:4193) ~[starrocks-fe.jar:?]
	at com.starrocks.catalog.Catalog.createTable(Catalog.java:3044) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DdlExecutor.execute(DdlExecutor.java:115) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.StmtExecutor.handleDdlStmt(StmtExecutor.java:1219) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:434) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:248) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:395) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:631) ~[starrocks-fe.jar:?]
	at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:55) ~[starrocks-fe.jar:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_251]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_251]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_251]

所有RS机器都能正常解析 hive-1.cvm.tc.xxxx.cn 并能telnet 通9083端口。

hive 版本是3.1.0

jingdan · 2022年03月8日 07:30

请问下每台机器有做kerberos认证吗？可以看下fe.out或者be.out日志

U_魔咒 · 2022年04月1日 05:57

我们好像也碰到这个问题， hive是3.1.2，前面还好好地，后面重启了下hive就出现这个问题，resource删除重建也还是一样 fe重启 hive重启还是不行

U_魔咒 · 2022年04月2日 01:01

hive中的异常信息：

U_魔咒 · 2022年04月2日 01:02

@jingdan SR版本 2.1.1

jingdan · 2022年04月2日 04:47

这个可能跟防火墙有关，确认下sr到hive可以telent到端口吗

U_魔咒 · 2022年04月2日 05:37

@jingdan 端口可以telnet通，并且本地用fe调用hive的源代码执行也没问题，presto访问hive也是正常的，重新安装一个新hive也是一样的问题，服务重启、服务器重启都试过没用，后面把SR重装才恢复。很诡异！

jingdan · 2022年04月2日 05:40

方便发个完整的fe.info和be.info日志

U_魔咒 · 2022年04月2日 05:49

fe.log (2.4 MB) 这是fe的日志，可以看下17:40:45左右的那一次，但是be的日志都被清理掉了，之前看了be好像是没看到错误信息

U_1636011922679_8543 · 2022年04月2日 09:23

啥时候能够像presto一样，不用创建表就好了

zhaochun · 2022年04月21日 06:05

这个功能正在支持，新版本就不用直接建表可以查询了。

Harbor · 2022年04月29日 07:10

很棒的消息，请问下这个功能在Q2能release发布吗

zhaochun · 2022年04月29日 07:22

5月末会在main branch上有支持。到时候可以试一试

Harbor · 2022年04月29日 07:25

好的好的，我这里继续关注，感谢

U_1636011922679_8543 · 2022年05月23日 10:05

现在超级需要这个功能，一张一张建外部表太繁琐了。这个功能出来之后，我们presto都可以弃用了

U_魔咒 · 2022年07月29日 06:51

终于找到原因了，因为我们这边将启动服务注册到sysctl中，而sysctl没有去获取系统环境，导致/conf/hadoop_env.sh 中HADOOP_USER_NAME=${USER}里面${USER}变量没有获取到用户导致的，解决办法: 将${USER}写死用户名如HADOOP_USER_NAME=root等

dongquan · 2022年08月1日 06:58

StarRocks version 2.3 @ release-2.3 @ StarRocks Docs * 【Preview】发布全新数据源 Connector 框架，支持创建外部数据目录（External Catalog），从而无需创建外部表，即可直接查询 Apache Hive™。相关文档，请参见使用 Catalog 管理内部和外部数据。

dongquan · 2022年08月1日 06:58

2.3版本

【Preview】发布全新数据源 Connector 框架，支持创建外部数据目录（External Catalog），从而无需创建外部表，即可直接查询 Apache Hive™。相关文档，请参见使用 Catalog 管理内部和外部数据。

matthew · 2024年03月13日 02:02

我的也是这样，改了user为具体的名字就好了