Bigtable용 GoogleSQL 쿼리 예시

참고: 이 페이지에는 SQL용 Data Boost와 같이 Enterprise Plus 버전에서만 사용할 수 있는 기능이 포함되어 있습니다. 자세한 내용은 버전 개요를 참조하세요.

이 페이지의 예시는 일반 및 고급 Bigtable 쿼리의 SQL 쿼리 패턴을 보여줍니다. Bigtable Studio 쿼리 편집기에서 GoogleSQL 쿼리를 실행할 수 있습니다. 또한 Java용 Bigtable 클라이언트 라이브러리를 사용하여 쿼리를 실행할 수도 있습니다.

이 페이지를 읽기 전에 Bigtable용 GoogleSQL 개요를 읽어보세요.

참고: 버전은 지정된 열 및 행의 타임스탬프 값을 의미합니다. 각 버전은 셀에 저장됩니다. 자세한 내용은 Bigtable 스토리지 모델을 참조하세요.

이 페이지의 예시에서는 예시 데이터와 비슷한 ID와 값이 사용됩니다.

일반적인 Bigtable SQL 쿼리 패턴

다음은 Bigtable 데이터의 일반적인 쿼리 예시입니다. Bigtable 데이터 API를 호출하는 비슷한 쿼리 예시를 보려면 읽기 예시 및 필터 사용을 참조하세요. 구조화된 row key에 대한 쿼리 예시는 구조화된 row key 쿼리를 참조하세요.

지정된 row key에 대해 모든 열의 최신 버전을 검색합니다.

  SELECT * FROM myTable WHERE _key = 'r1'

지정된 row key에 대해 모든 열의 모든 버전을 검색합니다.

  SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'

지정된 row key에 대해 특정 column family에서 가져온 특정 열의 최신 버전을 검색합니다.

  SELECT stats_summary['os_build'] AS os
  FROM analytics
  WHERE _key = 'phone#4c410523#20190501'

지정된 row key 범위에 대해 여러 열의 row key 및 최신 버전을 검색합니다.

  SELECT
    _key,
    stats_summary['os_build'] AS os,
    stats_summary['user_agent'] AS agent
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

여러 row key 범위에 대해 최대 10개 행까지 모든 열의 모든 버전을 검색합니다.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE
    (_key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201')
    OR (_key >= 'phone#5c10102#20190501' AND _key < 'phone#5c10102#20190601')
  LIMIT 10

여러 row key에 대해 모든 열의 모든 버전을 검색합니다.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'

다른 접근 방법을 사용해서 여러 row key에 대해 모든 열의 모든 버전을 검색합니다.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')

row key 프리픽스에 대해 column family 내의 모든 열의 최신 버전을 검색합니다.

  SELECT stats_summary
  FROM analytics
  WHERE _key LIKE 'phone#%'

테이블에 있는 모든 행에 대해 column family 내에 있는 모든 열의 row key 및 3개의 최신 버전을 검색합니다. 이 쿼리는 전체 테이블 스캔이 필요하기 때문에 지연 시간이 짧고 처리량이 높은 액세스 패턴에는 권장되지 않습니다.

  SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)

지정된 정규 표현식과 일치하는 row key가 있는 모든 열의 최신 버전을 검색합니다. 이 쿼리는 전체 테이블 스캔이 필요하기 때문에 WHERE 절에 row key 프리픽스 또는 row key 범위 조건자도 제공하지 않는 한 지연 시간이 짧고 처리량이 높은 액세스 패턴에는 권장되지 않습니다.

  SELECT *
  FROM myTable(with_history => TRUE)
  WHERE REGEXP_CONTAINS(_key, '.*#20190501$')

일치하는 row key 프리픽스와 123보다 큰 카운터 값이 있는 모든 열의 최신 버전을 검색합니다. Bigtable 집계가 숫자로 표시되기 때문에 이 비교를 위해서는 변환이 필요하지 않습니다.

  SELECT *
  FROM myTable
  WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123

리퍼러가 특정 값과 일치할 경우 row key 프리픽스에 대한 모든 열의 최신 버전을 검색합니다.

  SELECT *
  FROM analytics
  WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'

지정된 열의 값을 기준으로 지정된 행을 분류합니다. 이 쿼리는 Bigtable 데이터 API에서 구성 조건부 필터를 사용하는 것과 비슷합니다.

  SELECT
    *,
    CASE cell_plan['data_plan']
      WHEN '10gb' THEN 'passed-filter'
      ELSE 'filtered-out'
      END
      AS label
  FROM analytics

지정된 row key 범위에 대해 특정 column family에 있는 row key 및 column qualifier를 검색합니다. SQL에서 column family는 맵 데이터 유형으로 표시되며, 여기서 각 column qualifier와 값은 키-값 쌍으로 매핑됩니다. 이 SQL 쿼리는 Bigtable 데이터 API에서 값 제거 필터를 사용하는 것과 비슷합니다.

  SELECT _key, MAP_KEYS(cell_plan) AS keys
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

UNPACK 함수를 사용하면 Bigtable 데이터를 테이블 형식의 시계열 형식으로 변환할 수 있으며, 이는 시계열 분석을 실행할 때 유용합니다. engagement column family에 clicks 열이 있는 예를 살펴보겠습니다. 다음 쿼리는 UNPACK을 사용하여 지난 1시간 동안의 클릭수를 집계하여 특정 캠페인의 실적을 확인합니다.

  SELECT
    FORMAT_TIMESTAMP('%M', _timestamp) AS minute,
    COUNT(clicks) AS total_clicks
  FROM
    UNPACK((
      SELECT engagement['clicks'] as clicks
      FROM metrics(with_history => true, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
      WHERE _key = @campaign_id
    ))
  GROUP BY
    minute;

고급 Bigtable SQL 쿼리 패턴

다음 샘플은 고급 패턴을 보여줍니다.

다음 쿼리를 사용하면 session column family에서 JSON 속성 abc의 row key 및 최신 값을 검색할 수 있습니다. 자세한 내용은 JSON 함수를 참조하세요.

  SELECT _key, JSON_VALUE(session['payload'], '$.abc') AS abc FROM analytics

다음 쿼리를 사용하면 테이블의 각 행에 대해 숫자로 표시되는 2개의 Bigtable 집계 셀을 사용해서 row key를 검색하고 평균 세션 길이를 계산할 수 있습니다.

  SELECT
    _key AS userid,
    session['total_minutes'] / session['count'] AS avg_session_length
  FROM analytics

다음 쿼리를 사용하면 session column family에 referrer, origin, server가 column qualifier로 포함된 경우 지정된 row key 프리픽스에 대해 모든 열의 최신 버전을 검색할 수 있습니다. 또는 이 쿼리를 session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL과 같이 일련의 개별 비교로 작성할 수도 있습니다. 하지만 많은 수의 비교가 포함된 쿼리의 경우에는 다음 접근 방법이 권장됩니다.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])

다음 쿼리를 사용하면 session column family에 referrer, origin, server가 column qualifier로 포함된 경우 지정된 row key 프리픽스에 대해 모든 열의 최신 버전을 검색할 수 있습니다. 또는 session['referrer'] IS NOT NULL AND session ['origin'] IS NOT NULL과 같이 이 쿼리를 일련의 개별 비교로 작성할 수도 있습니다.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ALL(MAP_KEYS(session), ['referrer', 'origin', 'server'])

다음 쿼리를 사용하면 session column family에 com.google.search, com.google.maps, com.google.shopping이 값으로 포함된 경우 지정된 row key 프리픽스에 대해 모든 열의 최신 버전을 검색할 수 있습니다.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(
      MAP_VALUES(session),
      ['com.google.search', 'com.google.maps', 'com.google.shopping'])

다음 쿼리를 사용하면 cell_plan column family의 키-값 쌍에 data_plan:unlimited 및 roaming:North America가 모두 포함된 경우 모든 열의 최신 버전을 검색할 수 있습니다.

  SELECT *
  FROM analytics
  WHERE
    ARRAY_INCLUDES_ALL(
      CAST(
        MAP_ENTRIES(cell_plan)
        AS ARRAY<STRUCT<key STRING, value STRING>>),
      [('data_plan', 'unlimited'), ('roaming', 'North America')])

다음 쿼리를 사용하면 마지막 7개 측정 중 온도가 70도를 초과한 사례에 대해 날씨 센서의 row key 및 temperature 수치를 검색할 수 있습니다.

  SELECT
    _key AS sensorid,
    ARRAY_FILTER(
      CAST(
        sensor['temperature']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> CAST(e.value AS FLOAT32) > 70) AS high_temperature
  FROM weather(with_history => TRUE, latest_n => 7)

시간 필터링 순서에서 latest_n이 마지막에 표시되므로, after => X, before => y, latest_n => 3과 같은 쿼리는 이후 및 이전 조건을 충족하는 최신 3개 값을 반환합니다. 사용 사례에서 latest_n을 우선해야 할 경우 latest_n을 유일한 시간 필터로 제공하고 예시에 표시된 것처럼 SELECT 문에 쿼리 연산자를 사용해서 나머지 시간 필터를 적용할 수 있습니다. 자세한 내용은 시간 필터를 참조하세요.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> e.timestamp > TIMESTAMP('2021-01-04T23:51:00.000Z'))
      AS street_address
  FROM locations(with_history => TRUE, latest_n => 3)

이전 예시와 비슷하게, 쿼리의 각 column family에 다른 시간 필터를 적용할 수 있습니다. 예를 들어 다음 쿼리는 street 열의 최신 버전 3개를와 state 열의 최신 버전 2개를 반환합니다.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      (e, i) -> i <= 2)
      AS street_address,
    ARRAY_FILTER(
      ARRAY_REVERSE(
        CAST(
          address['state']
          AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>)),
      (e, i) -> i <= 1)
      AS state
  FROM locations(with_history => TRUE)

다음 쿼리를 사용하면 주소 column family의 키-값 쌍에 특정 시점의 city:Savannah 또는 city:Nashville이 모두 포함된 경우 모든 열의 모든 버전을 검색할 수 있습니다.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        CAST(
          MAP_ENTRIES(address)
          AS ARRAY<
            STRUCT<
              key STRING,
              value ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>>>),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

이 특정 예시에서는 변환이 필요하지 않으므로, 다음과 같은 축약된 형식으로 이를 작성할 수도 있습니다.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        MAP_ENTRIES(address),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

Data Boost를 사용한 처리량이 높은 SQL 분석

Enterprise Plus 버전에서는 처리량이 높은 분석 워크로드의 경우 Data Boost를 사용하여 프로덕션 클러스터의 성능에 영향을 주지 않고 대량의 데이터를 스캔하는 쿼리를 실행할 수 있습니다. 이러한 쿼리는 실시간 분석 및 이전 또는 시계열 데이터에서 통계를 생성하는 데 최적입니다. 이러한 쿼리를 실행하려면 Data Boost용으로 구성된 애플리케이션 프로필을 사용해야 합니다.

다음 예시는 서버리스 컴퓨팅을 사용하는 처리량이 높은 분석의 SQL 패턴을 보여줍니다.

이전 시계열 데이터 분석

Data Boost를 사용하면 지난 24시간 동안 테이블에 있는 모든 행의 측정항목 row key 및 모든 버전을 검색할 수 있습니다. 다음 쿼리는 격리된 서버리스 컴퓨팅에서 처리되는 전체 테이블 스캔을 실행합니다.

SELECT _key, metrics
FROM myTable(with_history => TRUE, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR))

대규모 데이터 세트 집계

다음 쿼리는 전체 테이블에서 특정 기간 동안 각 기기의 총 이벤트 수를 계산하는 방법을 보여줍니다.

SELECT
  _key AS device_id,
  COUNT(events['event_type']) AS total_events
FROM
  UNPACK((
    SELECT events['event_type']
    FROM sensor_data(with_history => TRUE, after => TIMESTAMP('2026-01-01T00:00:00Z'))
  ))
GROUP BY
  Device_id;

대규모 범위 스캔

다음 쿼리는 대규모 범위의 row key에 대한 모든 열을 검색하는 방법을 보여줍니다. Data Boost를 사용하면 이 처리량이 높은 요청이 클러스터 노드의 CPU 리소스를 사용하지 않습니다.

SELECT *
FROM analytics
WHERE _key >= 'user#000000' AND _key < 'user#999999'

다음 단계

• Bigtable용 GoogleSQL 참고 문서 살펴보기
• Bigtable Spark 커넥터 사용
• row key 스키마 관리