Spark 2.2无Hive环境下ODBC访问Spark表问题咨询

Great question! The short answer is no, you don't have to install and run a full Hive deployment to make Spark tables accessible via ODBC through the Thrift Server. But there's a key catch around how Spark manages metadata that's causing your tables to be invisible—let's walk through why this happens and how to fix it.

Why You Can't See Your Spark Tables #

Spark Thrift Server relies on a metadata store to track tables, columns, and their locations. By default:

• If you don't configure a Hive Metastore, Spark uses an embedded Derby database for metadata.
• Derby runs in single-user mode by default, and stores its data in the current working directory of the Spark process.
• When you create tables in a Spark Shell/Spark SQL session, that session writes metadata to its local Derby instance. The Thrift Server, running in a separate process (and likely a different working directory), uses its own separate Derby instance—so it can't see the tables you created.
• Also, if you created temporary tables (CREATE TEMPORARY TABLE or CREATE VIEW), those only exist in the Spark session they were created in, and won't be visible to the Thrift Server's separate session.

Step-by-Step Fixes to Make Tables Visible #

Follow these steps to ensure your Spark tables are shared with the Thrift Server:

1. Configure a Shared Metadata & Warehouse Directory
   Edit your spark-defaults.conf file (usually in $SPARK_HOME/conf) to set these parameters:

   # Use Hive-style metadata (no full Hive deployment needed)
   spark.sql.catalogImplementation=hive
   # Define a fixed warehouse directory for all Spark processes
   spark.sql.warehouse.dir=/path/to/your/shared/spark-warehouse
   # Configure Derby to use a shared metadata directory (instead of current working dir)
   spark.driver.extraJavaOptions=-Dderby.system.home=/path/to/your/shared/metastore_db
   

   Replace /path/to/your/shared/... with actual directories on your server (make sure the Spark user has read/write permissions).

2. Create Persistent Tables (Not Temporary Ones)
   When creating tables, use the standard CREATE TABLE syntax instead of temporary variants. For example:

   -- This table will be persisted to the warehouse directory and tracked in metadata
   CREATE TABLE my_permanent_table (id INT, name STRING)
   USING parquet
   LOCATION '/path/to/your/shared/spark-warehouse/my_permanent_table';
   

   Avoid CREATE TEMPORARY TABLE or CREATE VIEW if you want the table to be accessible across sessions.

3. Restart the Thrift Server with the Same Configuration
   When starting the Thrift Server, ensure it picks up the updated configuration. You can either use the edited spark-defaults.conf, or pass the parameters directly:

   $SPARK_HOME/sbin/start-thriftserver.sh \
     --conf spark.sql.warehouse.dir=/path/to/your/shared/spark-warehouse \
     --conf spark.driver.extraJavaOptions=-Dderby.system.home=/path/to/your/shared/metastore_db
   

4. Verify Metadata Sharing
   After creating a table in your Spark session, check if the Thrift Server can see it by connecting via ODBC and running:

   SHOW TABLES;
   

   You should see your persistent tables listed now.

When Would You Need a Full Hive Deployment? #

A dedicated Hive Metastore Service (running on MySQL/PostgreSQL instead of Derby) is useful if you:

• Have multiple Spark clusters or processes that need to share metadata consistently
• Want better scalability and concurrency (Derby isn't designed for multi-user/process access)
• Need integration with other tools that rely on Hive Metastore (like Presto, Hive Server2)

But for your use case—exposing Spark tables via ODBC without a full Hive stack—the above configuration will work perfectly.

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