BYR Achieve · 镜像论坛

Hive on spark，2G 数据查询速度非常慢（已查明，问题出在 spar

2020/3/4镜像同步15 回复

update: 通过查看任务的日志，已查明是 hive 和 spark 的兼容性太差，hive on spark 跑不起来，把 execution engine 换成默认的就 ok 了。而且我正文中提到的查询超时，实际并不是 spark 任务跑的时间长导致查询超时，实际上 spark 任务在开始后不久就已经 failed 了。出错的具体原因是抛出了以下异常： ``` Exception in thread "main" java.lang.reflect.InvocationTargetException 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.spark.deploy.worker.DriverWrapper$.main(DriverWrapper.scala:65) at org.apache.spark.deploy.worker.DriverWrapper.main(DriverWrapper.scala) Caused by: java.lang.NoSuchFieldError: SPARK_RPC_SERVER_ADDRESS at org.apache.hive.spark.client.rpc.RpcConfiguration.<clinit>(RpcConfiguration.java:48) at org.apache.hive.spark.client.RemoteDriver.<init>(RemoteDriver.java:138) at org.apache.hive.spark.client.RemoteDriver.main(RemoteDriver.java:536) ... 6 more 2020-03-04 15:43:55,745 INFO util.ShutdownHookManager: Shutdown hook called 2020-03-04 15:43:55,745 INFO util.ShutdownHookManager: Deleting directory /tmp/spark-204d5332-e495-49f0-a75e-9cc0fa8efd5c ``` Reference: https://cwiki.apache.org/confluence/display/Hive/Hive+on+Spark%3A+Getting+Started ================================ 数据是通过 spark 写入到 hive 中的，有 5 个分区，总大小大约 2G。 hive 配置的执行引擎是 spark。然后执行查询 `SELECT srcip from waf_log where dstip = 10.0.0.1`，执行到查询超时也跑不出结果: ``` 0: jdbc:hive2://spark1:10 (waf)> select srcip from waf_log where dstip = '10.0.0.1'; DEBUG : Acquired the compile lock. INFO : Compiling command(queryId=20200304151659_e3601712-378b-4dd6-828d-f67efb6760d9): select srcip from waf_log where dstip = '10.0.0.1' INFO : Concurrency mode is disabled, not creating a lock manager INFO : No Stats for waf@waf_log, Columns: dstip INFO : Semantic Analysis Completed (retrial = false) INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:srcip, type:string, comment:null)], properties:null) INFO : Completed compiling command(queryId=20200304151659_e3601712-378b-4dd6-828d-f67efb6760d9); Time taken: 1.482 seconds INFO : Concurrency mode is disabled, not creating a lock manager INFO : Executing command(queryId=20200304151659_e3601712-378b-4dd6-828d-f67efb6760d9): select srcip from waf_log where dstip = '10.0.0.1' INFO : Query ID = 20200304151659_e3601712-378b-4dd6-828d-f67efb6760d9 INFO : Total jobs = 1 INFO : Launching Job 1 out of 1 INFO : Starting task [Stage-1:MAPRED] in parallel ERROR : FAILED: Execution Error, return code 30041 from org.apache.hadoop.hive.ql.exec.spark.SparkTask. Failed to create Spark client for Spark session 07219d88-14d8-4c13-8f79-06e1aa4f1911 DEBUG : Shutting down query select srcip from waf_log where dstip = '10.0.0.1' INFO : Completed executing command(queryId=20200304151659_e3601712-378b-4dd6-828d-f67efb6760d9); Time taken: 302.029 seconds INFO : Concurrency mode is disabled, not creating a lock manager DEBUG : Shutting down query select srcip from waf_log where dstip = '10.0.0.1' Error: Error while processing statement: FAILED: Execution Error, return code 30041 from org.apache.hadoop.hive.ql.exec.spark.SparkTask. Failed to create Spark client for Spark session 07219d88-14d8-4c13-8f79-06e1aa4f1911 (state=42000,code=30041) ``` hive-site.xml: ``` <configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://spark1:3306/hive?createDatabaseIfNotExist=true&useSSL=false</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.cj.jdbc.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>password</value> </property> <property> <name>hive.metastore.warehouse.dir</name> <value>/hive/warehouse</value> </property> <property> <name>hive.cli.print.current.db</name> <value>true</value> </property> <property> <name>hive.execution.engine</name> <value>spark</value> </property> <property> <name>hive.metastore.schema.verification</name> <value>false</value> </property> <property> <name>spark.home</name> <value>/data/spark-2.4.5-bin-hadoop2.7</value> </property> <property> <name>spark.master</name> <value>spark://spark1:7077</value> </property> <property> <name>spark.executor.instances</name> <value>8</value> </property> <property> <name>spark.executor.memory</name> <value>2048m</value> </property> <property> <name>spark.executor.cores</name> <value>4</value> </property> <property> <name>spark.driver.memory</name> <value>4096m</value> </property> <property> <name>hive.spark.client.connect.timeout</name> <value>30000ms</value> </property> <property> <name>hive.spark.client.server.connect.timeout</name> <value>300000ms</value> </property> <property> <name>spark.yarn.jars</name> <value>hdfs://spark1:9000/spark-jars</value> </property> <property> <name>hive.exec.parallel</name> <value>true</value> </property> <property> <name>hive.exec.parallel.thread.number</name> <value>16</value> </property> </configuration> ``` hive 的日志中最后是报了一个 `java.util.concurrent.ExecutionException: java.util.concurrent.TimeoutException: Timed out waiting for client connection.`，应该是查询时间超时导致客户端断开了连接。应该怎样优化呢？并且一般来说，hive 查询 2G 左右数据的话大概花费多长时间呢？

订阅后，新回复会通过你的通知中心匿名送达。