image.png

hive on spark原理

是把hive查询从mapreduce 的mr (Hadoop计算引擎)操作替换为spark rdd（spark 执行引擎）
HiveOnSpark原理.jpg

hive与spark的安装问题

HIVE写SparkJob也需要 new SparkContext，也就是说hive也需要spark依赖
所以hive和spark需要安装在同一台机器上，获取spark的jar包
这时hive需要读取一个SPARK_HOME的环境变量来找到spark的jar以及相关配置文件

executor的加载问题

同时YARN模式的Spark是分布式的，需要获得相关jar包来加载不同节点上的Executor
所以需要配置一个Spark.yarn.jars变量，来上传加载excutor所需的jar包
如果不指明则会自动将找到SPARK_HOME,将SPARK_HOME上所有jar包上传至hdfs上
这时候因为spark自带了hive，就会上传hive2.3.x的jar包，而我们现在使用的是hive3.x的版本，有可能会产生冲突
所以我们需要安装一个纯净不带hive的spark，将其jar上传到hdfs 上面去

配置

总配置hive-site.xml

  1. <?xml version="1.0"?>
  2. <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
  3. <!--用utf8方式连接mysql -->
  4. <configuration>
  5.     <property>
  6.         <name>javax.jdo.option.ConnectionURL</name>
  7. <value>jdbc:mysql://hadoop162:3306/metastore?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
  8.     </property>
  10.     <property>
  11.         <name>javax.jdo.option.ConnectionDriverName</name>
  12.         <value>com.mysql.jdbc.Driver</value>
  13.     </property>
  15.     <property>
  16.         <name>javax.jdo.option.ConnectionUserName</name>
  17.         <value>root</value>
  18.     </property>
  20.     <property>
  21.         <name>javax.jdo.option.ConnectionPassword</name>
  22.         <value>aaaaaa</value>
  23.     </property>
  26.     <property>
  27.         <name>hive.metastore.schema.verification</name>
  28.         <value>false</value>
  29.     </property>
  31.   <!--Spark依赖位置-->
  32.   <property>
  33.       <name>spark.yarn.jars</name>
  34.       <value>hdfs://hadoop162:9820/sparkjars/*</value>
  35.   </property>
  37.   <!--Hive执行引擎（默认是mr）-->
  38.   <property>
  39.       <name>hive.execution.engine</name>
  40.       <value>spark</value>
  41.   </property>
  43. <property>
  44.     <name>mapreduce.job.queuename</name>
  45.     <value>hive</value>
  46. </property>
  47.   <!--配置hive专门运行的队列-->
  48.   <property>
  49.     <name>mapreduce.job.queuename</name>
  50.     <value>hive</value>
  51. </property>
  52. </configuration>

配置hive连接mysql

客户端读写需要支持中文：
由以下配置确定，以下配置已经在上面

  1. <configuration>
  2.     <property>
  3.         <name>javax.jdo.option.ConnectionURL</name>
  4. <value>jdbc:mysql://hadoop102:3306/metastore?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
  5.     </property>

在元数据表中的收到中文影响的表：
表的注释：
TABLE_PARAMS表可以看到mysql的字符集
image.png
列的注释：
COLUMNS_V2表可以看到列的编码情况
image.png

通过语句改变相关表编码：
在Hive元数据存储的Mysql数据库中，执行以下SQL：

  1. #修改字段注释字符集
  2. alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
  3. #修改表注释字符集
  4. alter table TABLE_PARAMS modify column PARAM_VALUE varchar(20000) character set utf8;
  5. #修改分区参数，支持分区建用中文表示
  6. alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(20000) character set utf8;
  7. alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(20000) character set utf8;
  8. #修改索引名注释，支持中文表示
  9. alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
  10. #修改视图，支持视图中文
  11. ALTER TABLE TBLS modify COLUMN VIEW_EXPANDED_TEXT mediumtext CHARACTER SET utf8;
  12. ALTER TABLE TBLS modify COLUMN VIEW_ORIGINAL_TEXT mediumtext CHARACTER SET utf8;

配置容量调度器为多队列

编辑$HADOOP_HOME/etc/hadoop/capacity-schdualer.xml
此参数可配置：
多个队列
每个队列所占初始比例（容量调度器队列容量能相互借用）
每个AM最多能调用的资源比例
当前队列中启动的MR AppMaster进程，所占用的资源可以达到队列总资源的多少，
通过这个参数可以限制队列中提交的Job数量

  1. <!--
  2.   Licensed under the Apache License, Version 2.0 (the "License");
  3.   you may not use this file except in compliance with the License.
  4.   You may obtain a copy of the License at
  6.     http://www.apache.org/licenses/LICENSE-2.0
  8.   Unless required by applicable law or agreed to in writing, software
  9.   distributed under the License is distributed on an "AS IS" BASIS,
  10.   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  11.   See the License for the specific language governing permissions and
  12.   limitations under the License. See accompanying LICENSE file.
  13. -->
  14. <configuration>
  16.    <!-- 容量调度器最多可以容纳多少个job-->
  17.   <property>
  18.     <name>yarn.scheduler.capacity.maximum-applications</name>
  19.     <value>10000</value>
  20.     <description>
  21.       Maximum number of applications that can be pending and running.
  22.     </description>
  23.   </property>
  25.   <!-- 当前队列中启动的MR AppMaster进程，所占用的资源可以达到队列总资源的多少
  26.         通过这个参数可以限制队列中提交的Job数量
  27.   -->
  28.   <property>
  29.     <name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
  30.     <value>0.5</value>
  31.     <description>
  32.       Maximum percent of resources in the cluster which can be used to run 
  33.       application masters i.e. controls number of concurrent running
  34.       applications.
  35.     </description>
  36.   </property>
  38.   <!-- 为Job分配资源时，使用什么策略进行计算
  39.   -->
  40.   <property>
  41.     <name>yarn.scheduler.capacity.resource-calculator</name>
  42.     <value>org.apache.hadoop.yarn.util.resource.DefaultResourceCalculator</value>
  43.     <description>
  44.       The ResourceCalculator implementation to be used to compare 
  45.       Resources in the scheduler.
  46.       The default i.e. DefaultResourceCalculator only uses Memory while
  47.       DominantResourceCalculator uses dominant-resource to compare 
  48.       multi-dimensional resources such as Memory, CPU etc.
  49.     </description>
  50.   </property>
  52.    <!-- root队列中有哪些子队列-->
  53.   <property>
  54.     <name>yarn.scheduler.capacity.root.queues</name>
  55.     <value>default,hive</value>
  56.     <description>
  57.       The queues at the this level (root is the root queue).
  58.     </description>
  59.   </property>
  61.   <!-- root队列中default队列占用的容量百分比30%
  62.         所有子队列的容量相加必须等于100
  63.   -->
  64.   <property>
  65.     <name>yarn.scheduler.capacity.root.default.capacity</name>
  66.     <value>30</value>
  67.     <description>Default queue target capacity.</description>
  68.   </property>
  69.    <!-- root队列中hive队列占用的容量百分比70%
  70.         所有子队列的容量相加必须等于100
  71.   -->
  72.   <property>
  73.     <name>yarn.scheduler.capacity.root.hive.capacity</name>
  74.     <value>70</value>
  75.     <description>Default queue target capacity.</description>
  76.   </property>
  80.     <!-- 队列中用户能使用此队列资源的极限百分比
  81.   -->
  82.   <property>
  83.     <name>yarn.scheduler.capacity.root.default.user-limit-factor</name>
  84.     <value>1</value>
  85.     <description>
  86.       Default queue user limit a percentage from 0.0 to 1.0.
  87.     </description>
  88.   </property>
  90.    <property>
  91.     <name>yarn.scheduler.capacity.root.hive.user-limit-factor</name>
  92.     <value>1</value>
  93.     <description>
  94.       Default queue user limit a percentage from 0.0 to 1.0.
  95.     </description>
  96.   </property>
  99.   <!-- root队列中default队列占用的容量百分比的最大值
  100.   -->
  101.   <property>
  102.     <name>yarn.scheduler.capacity.root.default.maximum-capacity</name>
  103.     <value>100</value>
  104.     <description>
  105.       The maximum capacity of the default queue. 
  106.     </description>
  107.   </property>
  109.    <property>
  110.     <name>yarn.scheduler.capacity.root.hive.maximum-capacity</name>
  111.     <value>100</value>
  112.     <description>
  113.       The maximum capacity of the default queue. 
  114.     </description>
  115.   </property>
  119.     <!-- root队列中每个队列的状态
  120.   -->
  121.   <property>
  122.     <name>yarn.scheduler.capacity.root.default.state</name>
  123.     <value>RUNNING</value>
  124.     <description>
  125.       The state of the default queue. State can be one of RUNNING or STOPPED.
  126.     </description>
  127.   </property>
  129.     <property>
  130.     <name>yarn.scheduler.capacity.root.hive.state</name>
  131.     <value>RUNNING</value>
  132.     <description>
  133.       The state of the default queue. State can be one of RUNNING or STOPPED.
  134.     </description>
  135.   </property>
  138.   <!-- 限制向default队列提交的用户-->
  139.   <property>
  140.     <name>yarn.scheduler.capacity.root.default.acl_submit_applications</name>
  141.     <value>*</value>
  142.     <description>
  143.       The ACL of who can submit jobs to the default queue.
  144.     </description>
  145.   </property>
  147.   <property>
  148.     <name>yarn.scheduler.capacity.root.hive.acl_submit_applications</name>
  149.     <value>*</value>
  150.     <description>
  151.       The ACL of who can submit jobs to the default queue.
  152.     </description>
  153.   </property>
  156.   <property>
  157.     <name>yarn.scheduler.capacity.root.default.acl_administer_queue</name>
  158.     <value>*</value>
  159.     <description>
  160.       The ACL of who can administer jobs on the default queue.
  161.     </description>
  162.   </property>
  164.   <property>
  165.     <name>yarn.scheduler.capacity.root.hive.acl_administer_queue</name>
  166.     <value>*</value>
  167.     <description>
  168.       The ACL of who can administer jobs on the default queue.
  169.     </description>
  170.   </property>
  174.   <property>
  175.     <name>yarn.scheduler.capacity.node-locality-delay</name>
  176.     <value>40</value>
  177.     <description>
  178.       Number of missed scheduling opportunities after which the CapacityScheduler 
  179.       attempts to schedule rack-local containers. 
  180.       Typically this should be set to number of nodes in the cluster, By default is setting 
  181.       approximately number of nodes in one rack which is 40.
  182.     </description>
  183.   </property>
  185.   <property>
  186.     <name>yarn.scheduler.capacity.queue-mappings</name>
  187.     <value></value>
  188.     <description>
  189.       A list of mappings that will be used to assign jobs to queues
  190.       The syntax for this list is [u|g]:[name]:[queue_name][,next mapping]*
  191.       Typically this list will be used to map users to queues,
  192.       for example, u:%user:%user maps all users to queues with the same name
  193.       as the user.
  194.     </description>
  195.   </property>
  197.   <property>
  198.     <name>yarn.scheduler.capacity.queue-mappings-override.enable</name>
  199.     <value>false</value>
  200.     <description>
  201.       If a queue mapping is present, will it override the value specified
  202.       by the user? This can be used by administrators to place jobs in queues
  203.       that are different than the one specified by the user.
  204.       The default is false.
  205.     </description>
  206.   </property>
  208. </configuration>

分发到集群，重启YARN！因为每个nodemanage都需要知能启动多少资源
在hive-site.xml中添加：

  1. <property>
  2.     <name>mapreduce.job.queuename</name>
  3.     <value>hive</value>
  4. </property>

重启Hive!

官方保证不冲突的hive与spark版本

Version Compatibility

Hive on Spark is only tested with a specific version of Spark, so a given version of Hive is only guaranteed to work with a specific version of Spark. Other versions of Spark may work with a given version of Hive, but that is not guaranteed. Below is a list of Hive versions and their corresponding compatible Spark versions.

Hive Version Spark Version
master 2.3.0
3.0.x 2.3.0
2.3.x 2.0.0
2.2.x 1.6.0
2.1.x 1.6.0
2.0.x 1.5.0
1.2.x 1.3.1
1.1.x 1.2.0