hive使用join进行表连接报错

qq_39579845 2017-08-03 10:05:31
环境：
hadoop-2.6.0-cdh5.12.0
hive-1.1.0-cdh5.12.0
做了高可靠！
hive使用各种函数等等一切正常，但是就是无法做表连接！！！
看图：

看图：

开启debug模式：
[hadoop@master01 hive-1.1.0-cdh5.12.0]$ hive -hiveconf hive.root.logger=DEBUG,console
which: no hbase in (/data/hadoop-2.6.0-cdh5.12.0/bin:/usr/java/jdk1.8.0_131/bin:/data/hadoop-2.6.0-cdh5.12.0/bin:/usr/java/jdk1.8.0_131/bin:/usr/java/jdk1.8.0_131/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/bin:/home/hadoop/.local/bin:/home/hadoop/bin:/bin:/data/hive-1.1.0-cdh5.12.0/bin:/data/hive-1.1.0-cdh5.12.0/conf:/bin:/home/hadoop/.local/bin:/home/hadoop/bin:/data/hadoop-2.6.0-cdh5.12.0/bin:/data/hive-1.1.0-cdh5.12.0/bin:/data/hive-1.1.0-cdh5.12.0/conf:/data/sqoop-1.4.6-cdh5.12.0/bin)
17/08/03 22:02:58 WARN conf.HiveConf: HiveConf of name hive.metastore.local does not exist
17/08/03 22:02:58 [main]: DEBUG common.LogUtils: Using hive-site.xml found on CLASSPATH at /data/hive-1.1.0-cdh5.12.0/conf/hive-site.xml
17/08/03 22:02:58 [main]: WARN conf.HiveConf: HiveConf of name hive.metastore.local does not exist

Logging initialized using configuration in jar:file:/data/hive-1.1.0-cdh5.12.0/lib/hive-common-1.1.0-cdh5.12.0.jar!/hive-log4j.properties
17/08/03 22:02:58 [main]: INFO SessionState:
Logging initialized using configuration in jar:file:/data/hive-1.1.0-cdh5.12.0/lib/hive-common-1.1.0-cdh5.12.0.jar!/hive-log4j.properties
17/08/03 22:02:58 [main]: DEBUG conf.VariableSubstitution: Substitution is on: hive
17/08/03 22:02:58 [main]: DEBUG hdfs.BlockReaderLocal: dfs.client.use.legacy.blockreader.local = false
17/08/03 22:02:58 [main]: DEBUG hdfs.BlockReaderLocal: dfs.client.read.shortcircuit = false
17/08/03 22:02:58 [main]: DEBUG hdfs.BlockReaderLocal: dfs.client.domain.socket.data.traffic = false
17/08/03 22:02:58 [main]: DEBUG hdfs.BlockReaderLocal: dfs.domain.socket.path =
17/08/03 22:02:58 [main]: DEBUG hdfs.HAUtil: No HA service delegation token found for logical URI hdfs://ns
17/08/03 22:02:58 [main]: DEBUG hdfs.BlockReaderLocal: dfs.client.use.legacy.blockreader.local = false
17/08/03 22:02:58 [main]: DEBUG hdfs.BlockReaderLocal: dfs.client.read.shortcircuit = false
17/08/03 22:02:58 [main]: DEBUG hdfs.BlockReaderLocal: dfs.client.domain.socket.data.traffic = false
17/08/03 22:02:58 [main]: DEBUG hdfs.BlockReaderLocal: dfs.domain.socket.path =
17/08/03 22:02:58 [main]: DEBUG retry.RetryUtils: multipleLinearRandomRetry = null
17/08/03 22:02:58 [main]: DEBUG ipc.Server: rpcKind=RPC_PROTOCOL_BUFFER, rpcRequestWrapperClass=class org.apache.hadoop.ipc.ProtobufRpcEngine$RpcRequestWrapper, rpcInvoker=org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker@5adb0db3
17/08/03 22:02:58 [main]: DEBUG ipc.Client: getting client out of cache: org.apache.hadoop.ipc.Client@a22cb6a
17/08/03 22:02:58 [Thread-4]: DEBUG unix.DomainSocketWatcher: org.apache.hadoop.net.unix.DomainSocketWatcher$2@73e226bf: starting with interruptCheckPeriodMs = 60000
17/08/03 22:02:58 [main]: DEBUG util.PerformanceAdvisory: Both short-circuit local reads and UNIX domain socket are disabled.
17/08/03 22:02:58 [main]: DEBUG sasl.DataTransferSaslUtil: DataTransferProtocol not using SaslPropertiesResolver, no QOP found in configuration for dfs.data.transfer.protection
17/08/03 22:02:58 [main]: DEBUG ipc.Client: The ping interval is 60000 ms.
17/08/03 22:02:58 [main]: DEBUG ipc.Client: Connecting to master01/10.38.64.68:9000
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop: starting, having connections 1
17/08/03 22:02:58 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop sending #0
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop got value #0
17/08/03 22:02:58 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getFileInfo took 38ms
17/08/03 22:02:58 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop sending #1
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop got value #1
17/08/03 22:02:58 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getFileInfo took 1ms
17/08/03 22:02:58 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop sending #2
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop got value #2
17/08/03 22:02:58 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getFileInfo took 1ms
17/08/03 22:02:58 [main]: DEBUG nativeio.NativeIO: Initialized cache for IDs to User/Group mapping with a cache timeout of 14400 seconds.
17/08/03 22:02:58 [main]: INFO session.SessionState: Created local directory: /tmp/7ffd4187-62e3-4254-8b2a-c577c0d2eadc_resources
17/08/03 22:02:58 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop sending #3
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop got value #3
17/08/03 22:02:58 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getFileInfo took 1ms
17/08/03 22:02:58 [main]: DEBUG hdfs.DFSClient: /user/hive/tmp/hadoop/7ffd4187-62e3-4254-8b2a-c577c0d2eadc: masked={ masked: rwx------, unmasked: rwx------ }
17/08/03 22:02:58 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop sending #4
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop got value #4
17/08/03 22:02:58 [main]: DEBUG ipc.ProtobufRpcEngine: Call: mkdirs took 4ms
17/08/03 22:02:58 [main]: INFO session.SessionState: Created HDFS directory: /user/hive/tmp/hadoop/7ffd4187-62e3-4254-8b2a-c577c0d2eadc
17/08/03 22:02:58 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop sending #5
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop got value #5
17/08/03 22:02:58 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getFileInfo took 1ms
17/08/03 22:02:58 [main]: INFO session.SessionState: Created local directory: /tmp/hadoop/7ffd4187-62e3-4254-8b2a-c577c0d2eadc
17/08/03 22:02:58 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop sending #6
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop got value #6
17/08/03 22:02:58 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getFileInfo took 0ms
17/08/03 22:02:58 [main]: DEBUG hdfs.DFSClient: /user/hive/tmp/hadoop/7ffd4187-62e3-4254-8b2a-c577c0d2eadc/_tmp_space.db: masked={ masked: rwx------, unmasked: rwx------ }
17/08/03 22:02:58 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop sending #7
17/08/03 22:02:58 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop got value #7
17/08/03 22:02:58 [main]: DEBUG ipc.ProtobufRpcEngine: Call: mkdirs took 3ms
17/08/03 22:02:58 [main]: INFO session.SessionState: Created HDFS directory: /user/hive/tmp/hadoop/7ffd4187-62e3-4254-8b2a-c577c0d2eadc/_tmp_space.db
17/08/03 22:02:58 [main]: INFO session.SessionState: No Tez session required at this point. hive.execution.engine=mr.
WARNING: Hive CLI is deprecated and migration to Beeline is recommended.
hive> 17/08/03 22:03:08 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop: closed
17/08/03 22:03:08 [IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop]: DEBUG ipc.Client: IPC Client (966966167) connection to master01/10.38.64.68:9000 from hadoop: stopped, remaining connections 0
...全文
749 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
woodslee_lz 2017-11-03
  • 打赏
  • 举报
 回复
我也是这个问题，这两天准备迁移集群，新集群是CDH 5.12, 老集群是CDH5.11，只要是left join 就出错。除非关掉本地模式，CDH 5.11没问题
Macro的一亩三分地 2017-09-21
  • 打赏
  • 举报
 回复
在hive会话中先执行：set hive.auto.convert.join=false;
qq_39579845 2017-08-04
  • 打赏
  • 举报
 回复
@OwenZeng_DBA 2017-08-04 09:31:16,122 ERROR [main]: mr.MapredLocalTask (MapredLocalTask.java:executeInChildVM(351)) - Exception: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask.executeInChildVM(MapredLocalTask.java:326) at org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask.execute(MapredLocalTask.java:153) at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:214) at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:99) at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:2041) at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1748) at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1501) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1285) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1275) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:220) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:172) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:383) at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:775) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:693) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:628) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) 2017-08-04 09:31:16,124 ERROR [main]: ql.Driver (SessionState.java:printError(1103)) - FAILED: Execution Error, return code 1 from org.apache. hadoop.hive.ql.exec.mr.MapredLocalTask
qq_39579845 2017-08-04
  • 打赏
  • 举报
 回复
@OwenZeng_DBA 2017-08-04 09:12:36,388 ERROR [main]: mr.MapredLocalTask (MapredLocalTask.java:executeInChildVM(351)) - Exception: 2017-08-04 09:12:36,390 ERROR [main]: ql.Driver (SessionState.java:printError(1103)) - FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask 就这些
OwenZeng_DBA 2017-08-03
  • 打赏
  • 举报
 回复
看看错误日志呢
qq_39579845 2017-08-03
  • 打赏
  • 举报
 回复
@OwenZeng_DBA debug就显示那么多.... 那个链接我看过，进入shell后： set hive.exec.mode.local.auto=false 然后依然不行
OwenZeng_DBA 2017-08-03
  • 打赏
  • 举报
 回复
@qq_39579845 怀疑更本地模式有关。你这个DEBUG模式没有看到有用的信息啊。你参考下，下面的链接吧 http://vase.iteye.com/blog/2090320
大数据之Hive详解
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。是hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。本质是将HQL转化成MapReduce程序.本课程从基本概念,hive和mysql软件的安装入手,讲解hive的数据类型,DDL和DML操作,以及常用的查询和函数的使用,让你快速入门hive,即刻成为大数据程序员.
hive 执行sql出现报错 引申出来的mapjoin相关
hive使用版本： 在Hive v0.7之前，需要给出MapJoin的指示，Hive才会提供MapJoin的优化。Hive v0.7之后的版本已经不需要给出MapJoin的指示就进行优化 MapJoin ：在Map阶段进行表之间的连接，节省Shuffle阶段时要进行的大量数据传输 MapJoin的实现方法： 1）在Map-Reduce的驱动程序中使用静态方法DistributedCache.addCacheFile()增加要拷贝的小表文件。 JobTracker在作业启动之前会获取这个UR...
Hive中的各种join关系和使用
hive编程是整个数据仓库操作的核心，而各种业务之间的join是hive的核心，所以熟练明白滴掌握hive中的各种join是数据仓库开发工程师必备的技能。 hive中的join只支持等值join,也就是说join on中的on里面表之间连接条件只能是=，不能是<,>等符号。此外，on中的等值连接之间只能是and,不能是or. （如果在on 里添加非表之间的条件可以是非等号，下面有演示...
hive的left join 陷阱
hive中，左连接操作不能在on的右边加不等式条件。比如在hive中不能进行如下操作： select * from tabel_1 a left join table_2 b on a.id=b.id and a.salary>b.salary 会出现报错。有人给出的答案是把上面语句改成： select * from tabel_1 a left join table_2 b on a.id...
hive sql数据倾斜——大表join小表如何使用map join
Map Join是Hive的一种优化操作，其适用于小表JOIN大表的场景，由于表的JOIN操作是在Map端且在内存进行的，所以其并不需要启动Reduce任务也就不需要经过shuffle阶段，从而能在一定程度上节省资源提高JOIN效率 一、mapjoin的使用场景： 关联操作中有一张表非常小 不等值的链接操作 二、具体使用： 方法一： 在Hive0.11前，必须使用MAPJOIN来标记显示地启动该优化操作，由于其需要将小表加载进内存所以要注意小表的大小 SELECT/*+MAPJOIN(smallta.
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章