hadoop - HIVE - 使用 WITH 子句插入覆盖

Question

我有一个生成的查询以 WITH 子句开头，当我在控制台中运行它时，当我尝试使用 INSERT OVERWRITE 运行查询以将输出加载到单独的配置单元表中时，它工作正常

INSERT OVERWRITE TABLE $proc_db.$master_table PARTITION(created_dt, country) $master_query

它抛出以下错误

cannot recognize input near 'WITH' 't' 'as' in statement

查询如下:

master_query="
WITH t
AS (
SELECT subscription_id
    ,country
    ,email_type
    ,email_priority
    ,created_dt
FROM crm_arrow.birthday
WHERE created_dt = '2016-07-07'
    AND (COUNTRY = 'SG')
GROUP BY subscription_id
    ,country
    ,email_type
    ,email_priority
    ,created_dt

UNION ALL

SELECT subscription_id
    ,country
    ,email_type
    ,email_priority
    ,created_dt
FROM crm_arrow.wishlist
WHERE created_dt = '2016-07-07'
    AND (COUNTRY = 'SG')
GROUP BY subscription_id
    ,country
    ,email_type
    ,email_priority
    ,created_dt

UNION ALL
.....
)
SELECT q.subscription_id
,q.country
,q.email_type
FROM (
SELECT t1.subscription_id
    ,t1.country
    ,DENSE_RANK() OVER (
        PARTITION BY t1.subscription_id
        ,t1.country ORDER BY t1.email_priority
        ) global_rank
    ,CASE 
        WHEN t1.email_type = t2.email_type
            THEN t1.email_type
        END email_type
FROM t t1
LEFT JOIN t t2 ON t1.country = t2.country
    AND t1.subscription_id = t2.subscription_id
) q
WHERE q.email_type IS NOT NULL
AND (
    q.global_rank <= 2
    AND country = 'SG'
    )
"

我怎样才能用一个巨大的内部查询进行高效的自连接？我还尝试在 master_query 中包含 select 语句，但它仍然无法正常工作。

Answer 1

问题就出在你插入 INSERT 语句的地方。有关如何将 INSERT 与 WITH 子句结合使用的示例，请参见此处

CREATE TABLE ramesh_test
(key          BIGINT,
 text_value   STRING,
 roman_value  STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

WITH v_text
AS
(SELECT 1 AS key, 'One' AS value),
v_roman
AS
(SELECT 1 AS key, 'I' AS value)
INSERT OVERWRITE TABLE ramesh_test
SELECT v_text.key, v_text.value, v_roman.value
  FROM v_text JOIN v_roman
                ON (v_text.key = v_roman.key);

将 INSERT 置于主 SELECT 之上。

希望这对您有所帮助!