如何运行顺序临时表和最终选择查询

wkyowqbh 于 2021-06-24 发布在 Hive

关注(0)|答案(1)|浏览(286)

我习惯于bigquery，在那里我可以用'with'子句运行临时表，然后用一个最终查询连接这些临时表。但是，我现在通过datagrip使用hivedb，在这里我不能在一次查询执行中运行顺序temp表。相反，我必须突出显示每个临时表块（在一个脚本中），然后执行到下一个，然后执行到下一个。。。这很烦人。
带两个项目的帮助：
有人知道我如何运行连续的临时表，然后用最后的select语句将它们连接起来吗？
另外，我发现temp表存储在我的会话中，我需要用一行简单的代码来删除它们，这在bigquery中不是一件事（同样恼人）。有人能帮我处理这件事吗？因为有时临时表可以更改列名，我不想担心删除以前具有旧列名的临时表。
下面是一个代码示例：

-- audience temp table  
    CREATE TEMPORARY VIEW aud AS (
   1 SELECT
        exp_luid
    FROM audience_manager.segments5_luid
    WHERE segment_version_id IN (627, 629)
    )

-- KVJ table
    CREATE TEMPORARY VIEW prod AS (
    SELECT
        station_callsign,
        exp_luid,
        ds,
        ad_start_ts_utc as ad_time,
        COUNT(ds) AS impressions
    FROM vizio_production.kantar_vizio_v4_new
    WHERE  product_id = 36325675
        AND ds BETWEEN 20190101 AND 20190430
        AND exp_luid IS NOT NULL
    GROUP BY 1,2,3,4
    )

-- Join KVJ and audience data set
    CREATE TEMPORARY VIEW join_one AS (
    SELECT
        aud.exp_luid AS exp_luid,
        prod.station_callsign AS network,
        prod.ds AS ds,
        prod.ad_time AS ad_time,
        SUM(prod.impressions) AS impressions
    FROM aud
    INNER JOIN prod ON aud.exp_luid = prod.exp_luid
    GROUP BY 1,2,3,4
    )

SELECT * FROM join_one

select语句“join\u one”的最终联接，而不缓存临时表并在一次脚本执行中运行整个sql脚本。

sql Hive hiveql

来源：https://stackoverflow.com/questions/56297105/how-can-i-run-sequential-temp-tables-final-select-query

1条答案

按热度按时间

zrfyljdw1#

Hive文档让我相信这是可行的：

WITH aud AS (
SELECT
    exp_luid
FROM audience_manager.segments5_luid
WHERE segment_version_id IN (627, 629)
),

prod AS (
SELECT
    station_callsign,
    exp_luid,
    ds,
    ad_start_ts_utc as ad_time,
    COUNT(ds) AS impressions
FROM vizio_production.kantar_vizio_v4_new
WHERE  product_id = 36325675
    AND ds BETWEEN 20190101 AND 20190430
    AND exp_luid IS NOT NULL
GROUP BY 1,2,3,4
),

join_one AS (
SELECT
    aud.exp_luid AS exp_luid,
    prod.station_callsign AS network,
    prod.ds AS ds,
    prod.ad_time AS ad_time,
    SUM(prod.impressions) AS impressions
FROM aud
INNER JOIN prod ON aud.exp_luid = prod.exp_luid
GROUP BY 1,2,3,4
)

SELECT * FROM join_one

我不太清楚为什么需要将其扩展到CTE，因为单个查询比较紧凑：

SELECT
    aud.exp_luid AS exp_luid,
    prod.station_callsign AS network,
    prod.ds AS ds,
    prod.ad_time AS ad_time,
    SUM(prod.impressions) AS impressions
  FROM 
    audience_manager.segments5_luid aud
    INNER JOIN 
    (
      SELECT
        station_callsign,
        exp_luid,
        ds,
        ad_start_ts_utc as ad_time,
        COUNT(ds) AS impressions
      FROM vizio_production.kantar_vizio_v4_new
      WHERE  product_id = 36325675
        AND ds BETWEEN 20190101 AND 20190430
        AND exp_luid IS NOT NULL
      GROUP BY 1,2,3,4
    ) prod 
    ON aud.exp_luid = prod.exp_luid
  WHERE aud.segment_version_id IN (627, 629) 
  GROUP BY 1,2,3,4

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

我来回答

如何运行顺序临时表和最终选择查询

1条答案

相关问题

热门标签

最新问答