Presto插入操作失败：hive连接器无page sink provider问题求助

Let's break down your issue and walk through the root causes and actionable fixes step by step.

Your Problem Context #

You can successfully create, drop, and query tables in Presto, but INSERT operations consistently fail. Here's the exact scenario and error you encountered:

Executed Commands #

presto:default> create table test.lll (a int);
CREATE TABLE
presto:default> insert into test.lll select 1;

Error Output #

Query 20180104_091933_00007_k8e78, FAILED, 5 nodes
Splits: 84 total, 30 done (35.71%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]
Query 20180104_091933_00007_k8e78 failed: No page sink provider for connector 'hive'

• Error Type: INTERNAL_ERROR
• Error Code: GENERIC_INTERNAL_ERROR (65536)

Full Stack Trace #

java.lang.IllegalArgumentException: No page sink provider for connector 'hive'
    at com.google.common.base.Preconditions.checkArgument(Preconditions.java:191)
    at com.facebook.presto.split.PageSinkManager.providerFor(PageSinkManager.java:67)
    at com.facebook.presto.split.PageSinkManager.createPageSink(PageSinkManager.java:61)
    at com.facebook.presto.operator.TableWriterOperator$TableWriterOperatorFactory.createPageSink(TableWriterOperator.java:97)
    at com.facebook.presto.operator.TableWriterOperator$TableWriterOperatorFactory.createOperator(TableWriterOperator.java:88)
    at com.facebook.presto.operator.DriverFactory.createDriver(DriverFactory.java:92)
    at com.facebook.presto.execution.SqlTaskExecution$DriverSplitRunnerFactory.createDriver(SqlTaskExecution.java:515)
    at com.facebook.presto.execution.SqlTaskExecution$DriverSplitRunnerFactory.access$1400(SqlTaskExecution.java:490)
    at com.facebook.presto.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:616)
    at com.facebook.presto.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
    at com.facebook.presto.execution.executor.LegacyPrioritizedSplitRunner.process(LegacyPrioritizedSplitRunner.java:23)
    at com.facebook.presto.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:492)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

Root Causes #

This error boils down to your Hive connector not being configured to support write operations. The most common triggers are:

• Using a read-only Hive connector configuration (e.g., incorrect connector.name value)
• Missing write-related dependencies in your Presto Hive plugin
• Misconfigured Hive connector settings that disable write functionality
• HDFS permission issues preventing Presto from writing table data

Step-by-Step Fixes #

1. Validate Hive Connector Configuration #

First, check your etc/catalog/hive.properties file (in your Presto installation directory) to ensure it's set up for writes:

# Use a write-enabled connector name (hive-hadoop2 is standard for Hadoop 2.x environments)
connector.name=hive-hadoop2
# Replace with your actual metastore URI
hive.metastore.uri=thrift://your-metastore-host:9083
# Link to your Hadoop config files to enable HDFS access
hive.config.resources=/etc/hadoop/conf/core-site.xml,/etc/hadoop/conf/hdfs-site.xml
# Explicitly set write target (use S3 instead if you're storing data there)
hive.write-target=HDFS

Note: Avoid using connector.name=hive if your Presto version treats it as a read-only connector. Stick to version-specific names like hive-hadoop2 or hive-hadoop3.

2. Check Hive Plugin Completeness #

Ensure your Presto plugin/hive directory has all required JARs for write operations. If you installed Presto via a package manager, reinstall the Hive plugin to fetch missing dependencies. For manual deployments, download the full Hive plugin package from the official Presto release page.

3. Confirm Table Storage Format Supports Writes #

Some storage formats are read-only by default. Check your table's format with:

DESC FORMATTED test.lll;

If the format is problematic, recreate the table with a write-friendly format like ORC or Parquet:

CREATE TABLE test.lll (a int) WITH (format = 'ORC');

4. Fix HDFS Permissions #

Presto needs write access to the HDFS directory where your Hive table is stored. Run these Hadoop commands to set the correct permissions (adjust paths and user/group as needed):

# Create the table directory if it doesn't exist
hdfs dfs -mkdir -p /user/hive/warehouse/test.db/lll
# Grant write access to the Presto user
hdfs dfs -chown presto:presto /user/hive/warehouse/test.db/lll

5. Restart Presto Cluster #

After making any configuration changes or fixing permissions, restart all Presto Coordinator and Worker nodes to apply the updates.

Verify the Fix #

Once your cluster is back up, re-run your INSERT query:

INSERT INTO test.lll SELECT 1;

If it succeeds, confirm the data was written with:

SELECT * FROM test.lll;

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