impala-在with子句后创建表

pn9klfpd 于 2021-06-26 发布在 Impala

关注(0)|答案(2)|浏览(452)

我有一个带有几个with子句的查询，然后是一个 CREATE TABLE :

WITH TABLE_1 AS (
        SELECT * FROM SOMEWHERE_1
    ), TABLE_2 AS (
        SELECT * FROM SOMEWHERE_2

    (
        CREATE TABLE TABLE_3 AS
        (
            SELECT TABLE_1.*, TABLE_2.*
            FROM TABLE_1 
            INNER JOIN TABLE_2 ON TABLE_2.key = TABLE_1.key
        )
    )

但是，我有以下错误：
遇到：create应为：select，values，由：exception:语法错误引起
所以我试着把create语句放在第一位：

CREATE TABLE_3 AS
    (
        WITH TABLE_1 AS (
            SELECT * FROM SOMEWHERE_1
        ), TABLE_2 AS (
            SELECT * FROM SOMEWHERE_2

        (
            SELECT TABLE_1.*, TABLE_2.*
            FROM TABLE_1 
            INNER JOIN TABLE_2 ON TABLE_2.key = TABLE_1.key
        )
    )

但现在我有以下错误：
analysisexception:无法解析表引用：“表\u 1”
请注意：
上面的查询在没有“create”语句的情况下工作
我目前的情况比这个简单的例子更复杂，为了清楚起见，我想保留下面的陈述。

sql impala create-table with-statement

来源：https://stackoverflow.com/questions/54709229/impala-create-table-after-a-with-clause

2条答案

按热度按时间

nc1teljy1#

或者，你也可以。。。

WITH TABLE_1 AS (
      SELECT * FROM SOMEWHERE_1
     ),
     TABLE_2 AS (
      SELECT * FROM SOMEWHERE_2
     )
SELECT TABLE_1.*, TABLE_2.* INTO TABLE_3
FROM TABLE_1 INNER JOIN
     TABLE_2
     ON TABLE_2.key = TABLE_1.key

建议您随时准备ddl并运行 INSERT INTO TABLE SELECT * FROM CTE

赞(0）回复(0）举报 2021-06-26

cqoc49vn2#

嗯。我想这样行得通：

CREATE TABLE TABLE_3 AS
    WITH TABLE_1 AS (
          SELECT * FROM SOMEWHERE_1
         ),
         TABLE_2 AS (
          SELECT * FROM SOMEWHERE_2
         )
    SELECT TABLE_1.*, TABLE_2.*
    FROM TABLE_1 INNER JOIN
         TABLE_2
         ON TABLE_2.key = TABLE_1.key;

当然，你会有其他的问题，比如 key 列在结果中被复制——这将产生另一个错误。实际上，您应该准确地选择所需的列。

赞(0）回复(0）举报 2021-06-26

我来回答

impala-在with子句后创建表

2条答案

相关问题

热门标签

最新问答