Access Hive Tables with Spark

You can configure Spark 3.5.5 and Spark 4.1.1 to access and query Hive tables through the Hive Metastore.

Hive Metastore compatibility

The Hive Metastore in ODP can use Hive 4.x. Spark 3.5.5 and Spark 4.1.1 require different Hive Metastore configurations.

For Spark 3.5.5, using the default Hive client with a Hive 4 Metastore can cause errors such as Invalid method name: 'get_table' and NoSuchFieldError: out, or prevent Hive tables from being displayed.

Prerequisites

Before you access Hive tables:

1. Set SPARK_HOME for the Spark version that you want to use.
   For Spark 3.5.5:
   

   export SPARK_HOME=/usr/odp/current/spark3-client

   
   For Spark 4.1.1:
   

   export SPARK_HOME=/usr/odp/current/spark4-client

2. Verify the Hive Metastore URI. The URI is typically configured in hive-site.xml.
   For example:
   

   thrift://<hms-host>:9083

3. Use the version-specific Spark client instead of running spark-sql directly from the system PATH.

Access Hive tables with Spark 3.5.5

Spark 3.5.5 requires a Hive 3 standalone metastore JAR to communicate with the Hive Metastore.

Configure the metastore JAR

Set SPARK_HOME:

export SPARK_HOME=/usr/odp/current/spark3-client
 

Set the metastore JAR:

export MS=file:///usr/odp/current/spark2-client/standalone-metastore/standalone-metastore-1.2.1.spark24.0.14.1-hive3.jar
 

Verify that the JAR exists:

ls -l /usr/odp/current/spark2-client/standalone-metastore/*.jar
 

Access Hive tables using Spark SQL

Run:

$SPARK_HOME/bin/spark-sql --master yarn \
  --name hive-access-spark355 \
  --database default \
  --conf spark.sql.hive.metastore.jars=path \
  --conf spark.sql.hive.metastore.jars.path="$MS" \
  --conf spark.sql.hive.metastore.version=3.0 \
  -e "SHOW DATABASES; SHOW TABLES;"
 

Replace default with the required Hive database.

For example:

tpcds_sf10_parquet
 

Access Hive tables using spark-submit

Use the same Hive Metastore configurations when you submit an application:

$SPARK_HOME/bin/spark-submit \
  --master yarn \
  --deploy-mode cluster \
  --name hive-access-spark355 \
  --conf spark.sql.hive.metastore.jars=path \
  --conf spark.sql.hive.metastore.jars.path="$MS" \
  --conf spark.sql.hive.metastore.version=3.0 \
  --class com.example.MyApp \
  /path/to/my-app.jar
 

In your application, enable Hive support when you create the Spark session:

val spark = SparkSession.builder()
  .appName("hive-access-spark355")
  .enableHiveSupport()
  .getOrCreate()
 
spark.sql("SHOW DATABASES").show(false)
spark.sql("SELECT * FROM my_db.my_table LIMIT 10").show()
 

Use the same metastore configurations when you use spark-shell.

Access Hive tables with Spark 4.1.1

Spark 4.1.1 can communicate with the Hive 4 Metastore by using the Hive client libraries.

Spark 4.1.1 also requires JDK 17 for the YARN ApplicationMaster and executors.

Configure JDK 17

Set the JDK 17 installation directory:

export JDK17=/usr/lib/jvm/java-17-openjdk
 

If multiple JDK versions are installed, specify the complete JDK 17 installation path.

For example:

export JDK17=/usr/lib/jvm/java-17-openjdk-17.0.20.1.1-1.1.el8_10.x86_64
 

Without JDK 17, Spark 4.1.1 might report errors such as:

Module jdk.incubator.vector not found
 

Configure the Hive client libraries

Set SPARK_HOME:

export SPARK_HOME=/usr/odp/current/spark4-client
 

Create the Hive Metastore JAR path:

export MS_PATH=$(ls /usr/odp/current/hive-client/lib/*.jar | sed 's|^|file://|' | paste -sd, -)
 

To verify the generated paths, run:

echo "$MS_PATH" | tr ',' ' ' | head
 

Access Hive tables using Spark SQL

Run:

$SPARK_HOME/bin/spark-sql --master yarn \
  --name hive-access-spark411 \
  --database default \
  --conf spark.yarn.appMasterEnv.JAVA_HOME="$JDK17" \
  --conf spark.executorEnv.JAVA_HOME="$JDK17" \
  --conf spark.sql.hive.metastore.jars=path \
  --conf spark.sql.hive.metastore.jars.path="$MS_PATH" \
  --conf spark.sql.hive.metastore.version=4.1.0 \
  -e "SHOW DATABASES; SHOW TABLES;"
 

Replace default with the required Hive database.

Access Hive tables using spark-submit

Run:

$SPARK_HOME/bin/spark-submit \
  --master yarn \
  --deploy-mode cluster \
  --name hive-access-spark411 \
  --conf spark.yarn.appMasterEnv.JAVA_HOME="$JDK17" \
  --conf spark.executorEnv.JAVA_HOME="$JDK17" \
  --conf spark.sql.hive.metastore.jars=path \
  --conf spark.sql.hive.metastore.jars.path="$MS_PATH" \
  --conf spark.sql.hive.metastore.version=4.1.0 \
  --class com.example.MyApp \
  /path/to/my-app.jar
 

Hive Metastore configuration by Spark version

Verify Hive table access

After you configure the Hive Metastore, run the following queries:

SHOW DATABASES;
 
USE my_hive_db;
 
SHOW TABLES;
 
SELECT *
FROM my_table
LIMIT 5;
 

Verify that Spark displays the expected Hive databases and tables.

If SHOW DATABASES displays only the default database when additional Hive databases exist, verify the configured metastore JARs and metastore version.

Troubleshoot Hive table access

Access Hive managed ACID tables

The procedures on this page are intended for non-ACID Hive tables accessed through Spark SQL and the Hive Metastore.

For Hive managed ACID tables, use Hive Warehouse Connector (HWC).

For Spark 3, see:

• Access Hive Managed ACID Tables with Spark 3
• Use Hive Warehouse Connector with Spark

For Spark 4, see:

• Use Hive Warehouse Connector with Spark 4