sparksql--spark整合hive

1. 安装MySQL并创建一个普通用户，并且授权

	CREATE USER 'hive'@'%' IDENTIFIED BY '123456'; 
	GRANT ALL PRIVILEGES ON hivedb.* TO 'hive'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
	FLUSH PRIVILEGES;

2. 在${SPARK_HOME}/conf/下添加一个hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!--
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
-->
<configuration>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://linux01:3306/hivedb?createDatabaseIfNotExist=true</value>
        <description>JDBC connect string for a JDBC metastore</description>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
        <description>Driver class name for a JDBC metastore</description>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>hive</value>
        <description>username to use against metastore database</description>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>root</value>
        <description>password to use against metastore database</description>
    </property>

    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>
    
    <property>
        <name>datanucleus.schema.autoCreateAll</name>
        <value>true</value>
    </property>

    <property>
      <name>hive.metastore.warehouse.dir</name>
      <value>hdfs://linux01:8020/user/hive/warehouse</value>
      <description>location of default database for the warehouse</description>
    </property>
</configuration>

3. 上传一个mysql连接驱动

bin/spark-sql --master spark://node-4:7077,node-5:7077 --driver-class-path /root/mysql-connector-java-5.1.47.jar

5. 重新启动SparkSQL的命令行

• spark-sql on yarn
  ${HIVE_HOME}

bin/spark-sql \
  --master yarn \
  --deploy-mode client \
  --driver-memory 1g \
  --executor-memory 512m \
  --num-executors 3 \
  --executor-cores 1 \
  --driver-class-path /root/mysql-connector-java-5.1.49.jar

• spark-sql 启动HiveServer2(不能使用cluster模式)
  ${HIVE_HOME}

sbin/start-thriftserver.sh \
  --master yarn \
  --deploy-mode client \
  --driver-memory 1g \
  --executor-memory 512m \
  --num-executors 3 \
  --executor-cores 1 \
  --driver-class-path /root/mysql-connector-java-5.1.49.jar

• 使用beeline链接HiveServer2
  ${HIVE_HOME}

/bin/beeline -u jdbc:hive2://linux01:10000 -n root

[补充] hive跟mysql的区别:

hive是一个sql解析框架,数仓开发工具,计算框架,不是数据库,计算需要依赖于mr和spark.可以用来提交任务的客户端,将sql提交到集群中运行.

mysql中并不存真正要计算的数据,而是存放表的描述信息