有没有办法用PrestoDB优化下面的插入？

Question

我正在运行一个查询，使用PrestoDB和AWS S3 (以及元数据粘合)将数据从源表插入到目标表。

该查询如下所示(尽管它有更多的案例)：

INSERT INTO my_table
SELECT other_field AS other_field ,
       (CASE
            WHEN regexp_like(LOWER(user_agent) , LOWER('bot')) THEN '1'
            WHEN regexp_like(LOWER(user_agent) , LOWER('spider')) THEN '2'
            WHEN regexp_like(LOWER(user_agent) , LOWER('crawler')) THEN '3'
            WHEN regexp_like(LOWER(user_agent) , LOWER('Google-AdSense-Auto')) THEN '4'
            WHEN regexp_like(LOWER(user_agent) , LOWER('Google-Adwords-DisplayAds-WebRender')) THEN '5'
            ELSE NULL
        END) AS bot_type
FROM source_table;

有没有更有效的方法来做这件事？我找不到任何关于这方面的资源。

Answer 1

您可以使用不区分大小写的正则表达式(添加(?i))并去掉LOWER()

WHEN regexp_like(user_agent,'(?i)bot') THEN '1' 
WHEN regexp_like(user_agent,'(?i)spider') THEN '2'
...

虽然不区分大小写的regexp看起来更简单，但由于并行执行，它对提高性能没有多大帮助。

Answer 2

由@Vamsi Prabhala建议

WITH
user_agents AS
    (SELECT DISTINCT
            headers_user_agent
        FROM
            source_table
        WHERE
            YEAR = '2020')
bot_types AS
    (SELECT
      headers_user_agent,
      (CASE
        WHEN regexp_like(LOWER(headers_user_agent), LOWER('bot'))     THEN '2'
        WHEN regexp_like(LOWER(headers_user_agent), LOWER('spider'))  THEN '3'
        ELSE NULL)
        AS
          bot_type
        FROM
          user_agents)
SELECT
  *
FROM
  bot_types
WHERE
  bot_type IS NOT NULL

我将这段代码插入到一个表中，这样我就可以连接到查询中，并摆脱其他SQL中的CASE。由于我每天执行一次基于案例的SQL，而另一个每天执行100次，这是一个很大的改进。