Hive终端中使用WITH临时表创建永久表报ParseException的解决方法咨询

Why You're Seeing the Error #

Hive's syntax doesn't allow a WITH clause (Common Table Expression, CTE) to be followed directly by CREATE TABLE. The parser expects the WITH clause to be immediately tied to a SELECT statement, not a DDL CREATE command. Your original code tries to chain the CTEs and then jump straight to CREATE TABLE, which breaks Hive's parsing rules.

The Fix: Restructure the Query #

You need to move the WITH clause inside the CREATE TABLE ... AS SELECT statement—right before the final SELECT that populates the new table. Here's the corrected, formatted version of your query:

CREATE TABLE cm_demographics AS
WITH temp1 AS (
    SELECT 
        cust_id, 
        CASE 
            WHEN cm_since_dt BETWEEN '2017-01-01' AND '2017-12-31' THEN '2017'
            WHEN cm_since_dt BETWEEN '2018-01-01' AND '2018-12-31' THEN '2018'
            WHEN cm_since_dt BETWEEN '2019-01-01' AND '2019-12-31' THEN '2019'
            WHEN cm_since_dt BETWEEN '2020-01-01' AND '2020-12-31' THEN '2020'
            WHEN cm_since_dt BETWEEN '2021-01-01' AND '2021-12-31' THEN '2021'
            WHEN cm_since_dt BETWEEN '2022-01-01' AND '2022-12-31' THEN '2022'
            ELSE 'old cm' 
        END AS cm_since 
    FROM customer_demographics_table
), temp2 AS (
    SELECT cust_id, MIN(cm_since) AS min_cm_since
    FROM temp1 
    GROUP BY cust_id
)
SELECT * FROM temp2;

Key Improvements: #

• I added an alias (min_cm_since) to the aggregated column in temp2—this avoids potential errors from unnamed columns in the final table.
• The WITH clause now directly precedes the SELECT that populates the new table, aligning perfectly with Hive's supported syntax.

Alternative Approach (For Older Hive Versions) #

If you're working with a very old Hive version that still has issues with CTEs in CTAS operations, split the process into two steps:

1. Create the empty table structure first
2. Insert data using the CTE:

-- Step 1: Create empty table (adjust data types to match your source table)
CREATE TABLE cm_demographics (
    cust_id STRING,
    min_cm_since STRING
);

-- Step 2: Insert data with the CTE
INSERT INTO TABLE cm_demographics
WITH temp1 AS (
    SELECT 
        cust_id, 
        CASE 
            WHEN cm_since_dt BETWEEN '2017-01-01' AND '2017-12-31' THEN '2017'
            WHEN cm_since_dt BETWEEN '2018-01-01' AND '2018-12-31' THEN '2018'
            WHEN cm_since_dt BETWEEN '2019-01-01' AND '2019-12-31' THEN '2019'
            WHEN cm_since_dt BETWEEN '2020-01-01' AND '2020-12-31' THEN '2020'
            WHEN cm_since_dt BETWEEN '2021-01-01' AND '2021-12-31' THEN '2021'
            WHEN cm_since_dt BETWEEN '2022-01-01' AND '2022-12-31' THEN '2022'
            ELSE 'old cm' 
        END AS cm_since 
    FROM customer_demographics_table
), temp2 AS (
    SELECT cust_id, MIN(cm_since) AS min_cm_since
    FROM temp1 
    GROUP BY cust_id
)
SELECT * FROM temp2;

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