如何基于Hive on Spark通过JDBC读取PostgreSQL并在集群运行？

Got it, let's tackle this problem directly—since you're using Hive on Spark and want to stick to SQL without custom UDFs or MapReduce, there's a native, reliable way to pull data from PostgreSQL right in Hive. Here's how to make it work smoothly:

1. Make Sure the PostgreSQL JDBC Driver is Accessible #

First, you need the PostgreSQL JDBC driver (e.g., postgresql-42.6.0.jar) available to both Hive and Spark across your cluster. You have two solid options:

• Copy the driver jar to Hive's auxlib directory (typically $HIVE_HOME/auxlib) and Spark's jars directory (typically $SPARK_HOME/jars) on every cluster node.
• If you're running jobs via spark-submit, attach the driver dynamically with the --jars flag:

  spark-submit --jars /path/to/postgresql-42.6.0.jar your-hive-query-script.hql
  

2. Create an External Hive Table Linked to PostgreSQL #

Forget the UDF approach—use Hive's built-in JDBC storage handler to create an external table that maps directly to your PostgreSQL table. This lets you query it like any regular Hive table, with Spark handling the heavy lifting.

Here's a sample SQL template (replace placeholders with your actual PostgreSQL details):

CREATE EXTERNAL TABLE pg_customer_data (
  customer_id INT,
  full_name STRING,
  signup_date TIMESTAMP,
  email STRING
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
  "hive.sql.database.type" = "POSTGRESQL",
  "hive.sql.jdbc.driver" = "org.postgresql.Driver",
  "hive.sql.jdbc.url" = "jdbc:postgresql://your-pg-host:5432/your-database-name",
  "hive.sql.jdbc.username" = "your-pg-username",
  "hive.sql.jdbc.password" = "your-pg-password",
  "hive.sql.table" = "public.your_postgresql_table" -- include schema if needed
);

Pro tip: If your PostgreSQL table uses complex types (like arrays or JSON), make sure to map them to compatible Hive types (e.g., ARRAY<STRING> or STRING for JSON).

3. Query the Linked Table Like Any Hive Table #

Once the external table is set up, you can run standard SQL queries on it—Spark will handle the distributed computation automatically:

-- Fetch a sample of records
SELECT * FROM pg_customer_data LIMIT 20;

-- Join with a local Hive table for analysis
SELECT c.customer_id, c.full_name, o.order_total
FROM pg_customer_data c
JOIN hive_order_history o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01';

4. Optional: Boost Performance for Large Datasets #

If you're working with big data, these tweaks will help:

• Predicate Pushdown: Enable it by setting hive.optimize.ppd=true in your Hive config—this pushes filters (like WHERE clauses) directly to PostgreSQL, so you don't fetch unnecessary data.
• Partitioning: If your PostgreSQL table has a logical partition key (e.g., signup_date), add it as a partition column in the Hive table and set "hive.sql.partition.column" = "signup_date" in the TBLPROPERTIES. This lets Hive/Spark only scan relevant partitions.

Why Your UDF Approach Didn't Work #

Hive UDFs are designed for row-level transformations in the Hive execution context, but when using Hive on Spark, execution shifts to Spark's distributed framework. JDBC connections in UDFs cause issues because each executor would need its own connection, and UDFs aren't built to handle that distributed setup. The JDBC storage handler, on the other hand, is purpose-built to integrate Hive with external JDBC sources seamlessly in a Spark environment.

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