如何连接Hive与Spark SQL？Spark SQL连Hive元数据失败需Hive哪些配置修改？

Hey there, let's tackle your two questions about Hive and Spark SQL connections one by one:

Connecting Hive and Spark SQL boils down to letting Spark share Hive's metadata. Here's a step-by-step breakdown:

• Copy Hive configuration files: Grab the hive-site.xml from your Hive installation's conf folder and paste it into Spark's conf directory. This lets Spark read Hive's metadata storage settings and connection details.
• Ensure Spark has Hive support:
  • If you're using a pre-built Spark package, pick the version with Hive support (usually named like spark-<version>-bin-hive.tgz).
  • If compiling Spark from source, enable Hive support by adding the -Phive -Phive-thriftserver flags during compilation.
• Initialize the Hive connection context:
  • For Spark 1.x: In the Spark Shell, create a HiveContext directly:

    val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
    

  • For Spark 2.x and later: SparkSession comes with built-in Hive support—just enable it:

    val spark = SparkSession.builder()
      .appName("Hive-Spark Link")
      .enableHiveSupport()
      .getOrCreate()
    

• Verify the connection: Run a simple Hive SQL command to check if it works, like listing databases:

  // Spark 1.x
  hiveContext.sql("show databases").show()
  // Spark 2.x+
  spark.sql("show tables").show()
  

If you see your Hive databases/tables in the output, the connection is successful.

This issue usually stems from misconfigured or unstarted Hive metadata services. Here's what you need to adjust on the Hive side:

• Switch metadata storage to an external database (replace default Derby):
  Derby is a single-user embedded database that can't handle external clients like Spark. Switch to MySQL, PostgreSQL, etc., by updating hive-site.xml with these settings:

  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost:3306/hive_metadata?createDatabaseIfNotExist=true&amp;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>your_mysql_username</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>your_mysql_password</value>
  </property>
  

  Replace the database URL, username, and password with your actual credentials.
• Initialize the metadata database schema:
  After switching to an external database, initialize Hive's metadata schema with this command:

  schematool -dbType mysql -initSchema
  

  Adjust dbType to match your database (e.g., postgresql for PostgreSQL).
• Start the Hive Metastore Service:
  Spark connects to Hive metadata via the Thrift protocol, so you need to start the metastore service:

  # Start in foreground
  hive --service metastore
  # Start in background (recommended for persistence)
  nohup hive --service metastore > metastore.log 2>&1 &
  

  Also, add the metastore address to hive-site.xml so Spark can find it:

  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://localhost:9083</value>
  </property>
  

  Replace localhost with the remote host IP if the metastore is on another machine.
• Check permission settings (optional):
  If Hive has authorization enabled, it might block Spark's access. Temporarily disable it to troubleshoot:

  <property>
    <name>hive.security.authorization.enabled</name>
    <value>false</value>
  </property>
  

  Once the connection works, you can reconfigure permissions as needed.

内容的提问来源于stack exchange，提问作者Biswajit Sarkar