regexp_like를 사용하여 배열에서 키워드 찾기 - Amazon Athena

regexp_like를 사용하여 배열에서 키워드 찾기

다음의 예는 regexp_like 함수를 사용하여 배열 내부 요소 내에서 키워드에 대한 데이터 집합을 검색하는 방법을 보여줍니다. 이 함수는 평가할 정규 표현식 패턴 또는 파이프(|)로 구분된 용어 목록을 입력으로 사용하며 패턴을 평가하고 지정된 문자열이 해당 패턴을 포함하고 있는지 판단합니다.

정규 표현식 패턴은 문자열 내에 포함되어 있어야 하고, 문자열과 반드시 일치할 필요는 없습니다. 전체 문자열을 일치시키려면 패턴의 시작 부분을 ^로, 끝부분을 $로 묶으세요(예: '^pattern$').

호스트 이름과 flaggedActivity 요소가 포함된 사이트 배열을 고려하세요. 이 요소에는 여러 개의 MAP 요소가 포함된 ARRAY가 포함되며, 각기 다른 인기 키워드와 인기도가 나열됩니다. 이 배열에서 MAP 내의 특정 키워드를 찾고자 한다고 가정해 보겠습니다.

이 데이터 세트에서 특정 키워드를 갖는 사이트를 검색하려면 유사한 SQL LIKE 연산자 대신 regexp_like을(를) 사용하는데, 다수의 키워드를 검색할 때는 regexp_like이(가) 더욱 효율적이기 때문입니다.

예제 1: regexp_like 사용하기

이 예제의 쿼리는 regexp_like 함수를 사용하여 배열 내부의 값에 있는 'politics|bigdata'(이)라는 용어를 검색합니다.

WITH dataset AS (
  SELECT ARRAY[
    CAST(
      ROW('aws.amazon.com', ROW(ARRAY[
          MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']),
          MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']),
          MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']),
          MAP(ARRAY['term', 'count'], ARRAY['iot', '74'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   ),
   CAST(
     ROW('news.cnn.com', ROW(ARRAY[
       MAP(ARRAY['term', 'count'], ARRAY['politics', '241']),
       MAP(ARRAY['term', 'count'], ARRAY['technology', '211']),
       MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']),
       MAP(ARRAY['term', 'count'], ARRAY['iot', '170'])
     ])
     ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   ),
   CAST(
     ROW('netflix.com', ROW(ARRAY[
       MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']),
       MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']),
       MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']),
       MAP(ARRAY['term', 'count'], ARRAY['iot', '4'])
     ])
     ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   )
 ] AS items
),
sites AS (
  SELECT sites.hostname, sites.flaggedactivity
  FROM dataset, UNNEST(items) t(sites)
)
SELECT hostname
FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags)
WHERE regexp_like(flags['term'], 'politics|bigdata')
GROUP BY (hostname)

이 쿼리는 다음 두 사이트를 반환합니다.

+----------------+
| hostname       |
+----------------+
| aws.amazon.com |
+----------------+
| news.cnn.com   |
+----------------+
예제 2: regexp_like 사용하기

다음 예의 쿼리는 검색어와 일치하는 사이트의 총 인기 점수를 regexp_like 함수로 합산한 다음, 가장 높은 순서에서 가장 낮은 순서로 정렬합니다.

WITH dataset AS (
  SELECT ARRAY[
    CAST(
      ROW('aws.amazon.com', ROW(ARRAY[
          MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']),
          MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']),
          MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']),
          MAP(ARRAY['term', 'count'], ARRAY['iot', '74'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    ),
    CAST(
      ROW('news.cnn.com', ROW(ARRAY[
        MAP(ARRAY['term', 'count'], ARRAY['politics', '241']),
        MAP(ARRAY['term', 'count'], ARRAY['technology', '211']),
        MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']),
        MAP(ARRAY['term', 'count'], ARRAY['iot', '170'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    ),
    CAST(
      ROW('netflix.com', ROW(ARRAY[
        MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']),
        MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']),
        MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']),
        MAP(ARRAY['term', 'count'], ARRAY['iot', '4'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    )
  ] AS items
),
sites AS (
  SELECT sites.hostname, sites.flaggedactivity
  FROM dataset, UNNEST(items) t(sites)
)
SELECT hostname, array_agg(flags['term']) AS terms, SUM(CAST(flags['count'] AS INTEGER)) AS total
FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags)
WHERE regexp_like(flags['term'], 'politics|bigdata')
GROUP BY (hostname)
ORDER BY total DESC

이 쿼리는 다음 두 사이트를 반환합니다.

+------------------------------------+
| hostname       | terms    | total  |
+----------------+-------------------+
| news.cnn.com   | politics |  241   |
+----------------+-------------------+
| aws.amazon.com | bigdata |  10    |
+----------------+-------------------+