Bigtable용 GoogleSQL 개요

GoogleSQL 문을 사용하여 Bigtable 데이터를 쿼리할 수 있습니다. GoogleSQL은 BigQuery 및 Spanner와 같은 다른 Google Cloud 서비스를 위해서도 구현되는 ANSI 호환 구조화된 쿼리 언어(SQL)입니다.

이 문서에서는 Bigtable용 GoogleSQL에 대해 간략하게 설명합니다. Bigtable에 사용할 수 있는 SQL 쿼리 예시를 보여주고 Bigtable 테이블 스키마와의 관계를 설명합니다. 이 문서를 읽기 전에 Bigtable 스토리지 모델 및 스키마 설계 개념을 숙지해야 합니다.

Google Cloud 콘솔을 이용하여 Bigtable Studio에서 쿼리를 만들고 실행하거나 Java용 Bigtable 클라이언트 라이브러리를 사용하여 프로그래매틱 방식으로 실행할 수도 있습니다. 자세한 내용은 Bigtable 클라이언트 라이브러리에 SQL 사용을 참고하세요.

사용 사례

Bigtable용 GoogleSQL은 지연 시간이 짧은 애플리케이션 개발에 가장 적합합니다. 또한 테이블 스키마의 시각적 표현을 빠르게 확인하여 특정 데이터의 기록 상태를 확인하거나 가능한 데이터 문제를 디버깅하려는 경우에는 Google Cloud 콘솔에서 SQL 쿼리를 실행하는 것이 유용할 수 있습니다.

Bigtable용 GoogleSQL의 현재 출시 버전은 다음과 같은 몇 가지 일반적인 SQL 구성을 지원하지 않습니다.

SELECT 이외의 데이터 조작 언어(DML) 문, 예: INSERT, UPDATE, DELETE
데이터 정의 언어(DDL) 문, 예: CREATE, ALTER, DROP
데이터 액세스 제어 문
서브 쿼리의 쿼리 구문, 예: JOIN, UNION, GROUP BY, UNNEST, CTEs

지원되는 함수, 연산자, 데이터 유형, 쿼리 구문을 포함한 자세한 내용은 Bigtable용 GoogleSQL 참조 문서를 참조하세요.

주요 개념

이 섹션에서는 GoogleSQL을 사용하여 Bigtable 데이터를 쿼리할 때 주의가 필요한 주요 개념에 대해 설명합니다.

SQL 응답의 열 column family

Bigtable에서 테이블에는 열을 그룹화하는 데 사용되는 하나 이상의 column family가 포함됩니다. GoogleSQL로 Bigtable 테이블을 쿼리할 때 테이블의 스키마는 다음과 같이 구성됩니다.

쿼리된 테이블의 row key에 해당하는 _key라는 특수 열
해당 행에 있는 column family 데이터를 포함하는 테이블의 각 Bigtable column family에 대한 단일 열

맵 데이터 유형

Bigtable용 GoogleSQL에는 column family를 수용하도록 특별히 설계된 MAP<key, value> 데이터 유형이 포함되어 있습니다.

기본적으로 맵 열의 각 행에는 키-값 쌍이 포함되며, 여기서 키는 쿼리된 테이블의 Bigtable column qualifier이고 값은 이 열의 최신 값입니다.

다음은 columnFamily라는 맵에서 한정자의 최신 값과 row key 값이 있는 테이블을 반환하는 SQL 쿼리의 예시입니다.

  SELECT _key, columnFamily['qualifier'] FROM myTable

Bigtable 스키마가 열에 여러 셀 또는 데이터 버전을 저장하는 경우 SQL 문에 with_history와 같은 시간 필터를 추가할 수 있습니다.

이 경우 column family를 표현하는 맵이 중첩되고 배열로 반환됩니다. 배열에서 각 키는 그 자체로 키로 사용되는 타임스탬프와 값으로 사용되는 셀 데이터로 구성된 맵입니다. 형식은 MAP<key, ARRAY<STRUCT<timestamp, value>>>입니다.

다음 예시는 단일 행에 대해 'info' column family의 모든 셀을 반환합니다.

  SELECT _key, info FROM users(with_history => TRUE) WHERE _key = 'user_123';

반환되는 맵은 다음과 같습니다. 쿼리된 테이블에서 info는 column family이고, user_123은 row key이고 city와 state는 column qualifier입니다. 배열에서 각 타임스탬프-값 쌍(STRUCT)은 해당 행에 있는 열의 셀을 나타내며, 타임스탬프의 내림차순으로 정렬됩니다.

/*----------+------------------------------------------------------------------+
 |   _key   |                              info                                |
 +----------+------------------------------------------------------------------+
 | user_123 | {"city":{<t5>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

희소 테이블

Bigtable의 주요 기능은 유연한 데이터 모델입니다. Bigtable 테이블에서 한 행의 열이 사용되지 않을 경우 해당 열에 대한 데이터가 저장되지 않습니다. 어떤 행은 열이 하나 있고 다음 행은 열이 100개 있을 수 있습니다. 반대로 관계형 데이터베이스 테이블에서는 모든 행에 모든 열이 포함되고, 일반적으로 NULL 값이 해당 열에 대해 데이터가 없는 행의 열에 저장됩니다.

하지만 GoogleSQL로 Bigtable 테이블을 쿼리할 때는 사용되지 않은 열이 비어 있는 맵으로 표시되고 NULL 값으로 반환됩니다. 이러한 NULL 값은 쿼리 조건자로 사용할 수 있습니다. 예를 들어 WHERE family['column1'] IS NOT NULL과 같은 조건자를 사용해서 column1이 행에 사용된 경우에만 행을 반환할 수 있습니다.

바이트

문자열을 제공할 경우 GoogleSQL은 기본적으로 STRING 값에서 BYTES 값으로의 암시적 변환을 수행합니다. 예를 들어, b'qualifier' 바이트 시퀀스 대신 'qualifier' 문자열을 제공할 수 있습니다.

Bigtable은 기본적으로 모든 데이터를 바이트로 취급하기 때문에 대부분의 Bigtable 열은 유형 정보를 포함하지 않습니다. 하지만 GoogleSQL에서는 CAST 함수를 사용해서 읽기 시에 스키마를 정의할 수 있습니다. 변환에 대한 자세한 내용은 변환 함수을 참조하세요.

시간 필터

다음 표에서는 테이블의 시간적 요소를 액세스할 때 사용할 수 있는 인수를 보여줍니다. 인수는 필터링되는 순서로 나열됩니다. 예를 들어 with_history는 latest_n보다 먼저 적용됩니다. 유효한 타임스탬프를 제공해야 합니다.

인수	설명
`as_of`	타임스탬프. 제공된 타임스탬프보다 작거나 같은 타임스탬프를 포함하는 최신 값을 반환합니다.
`with_history`	부울. 최신 값을 스칼라로 반환하거나 타임스탬프 값을 `STRUCT`로 반환할지 여부를 제어합니다.
`after_or_equal`	타임스탬프. 입력 이후의 타임스탬프가 있는 값입니다(포함). `with_history => TRUE`가 필요합니다.
`before`	타임스탬프. 입력 이전의 타임스탬프가 있는 값입니다(제외). `with_history => TRUE`가 필요합니다.
`latest_n`	정수. column qualifier당 반환할 타임스탬프 값의 수입니다(맵 키). 1 이상이어야 합니다. `with_history => TRUE`가 필요합니다.

더 많은 예시는 고급 쿼리 패턴을 참조하세요.

기본 쿼리

이 섹션에서는 기본 Bigtable SQL 쿼리의 예시와 작동 방법을 보여줍니다. 추가 샘플 쿼리는 Bigtable용 GoogleSQL 쿼리 패턴 예시를 참조하세요.

		address
_key	street	city	state
user1	2023/01/10-14:10:01.000: '113 Xyz Street' 2021/12/20-09:44:31.010: '76 Xyz Street' 2005/03/01-11:12:15.112: '123 Abc Street'	2021/12/20-09:44:31.010: 'Brooklyn' 2005/03/01-11:12:15.112: 'Queens'	2005/03/01-11:12:15.112: 'NY'

_key	address
user1	{street:'113 Xyz Street', city:'Brooklyn', state: :'NY'}

모든 버전 검색

이전 버전(셀)의 데이터를 검색하려면 with_history 플래그를 사용합니다. 또한 다음 예시와 같이 별칭 열과 표현식을 지정할 수 있습니다.

  SELECT _key, columnFamily['qualifier'] AS col1
  FROM myTable(with_history => TRUE)

현재 행 상태로 이어지는 이벤트를 효과적으로 파악하기 위해서는 전체 기록을 검색하여 각 값의 타임스탬프를 검색할 수 있습니다. 예를 들어 user1이 현재 주소로 이동한 시간과 이동 전의 장소를 알고 싶으면 다음 쿼리를 실행하면 됩니다.

  SELECT
    address['street'][0].value AS moved_to,
    address['street'][1].value AS moved_from,
    FORMAT_TIMESTAMP('%Y-%m-%d', address['street'][0].timestamp) AS moved_on,
  FROM myTable(with_history => TRUE)
  WHERE _key = 'user1'

SQL 쿼리에서 with_history 플래그를 사용할 때 응답은 MAP<key, ARRAY<STRUCT<timestamp, value>>>로 반환됩니다. 배열의 각 항목은 지정된 행, column family, 열에 대한 타임스탬프 값입니다. 타임스탬프는 시간의 역순으로 정렬되므로 항상 최신 데이터가 첫 번째 반환 항목입니다.

쿼리 응답은 다음과 같습니다.

moved_to			moved_from	moved_on
113 Xyz Street			76 Xyz Street	2023/01/10

또한 다음 쿼리에 표시된 것처럼 배열 함수를 사용하여 각 행의 버전 수를 검색할 수 있습니다.

  SELECT _key, ARRAY_LENGTH(MAP_ENTRIES(address)) AS version_count
  FROM myTable(with_history => TRUE)

지정된 시간으로부터 데이터 검색

as_of 필터를 사용하면 특정 시점을 기준으로 행 상태를 검색할 수 있습니다. 예를 들어 2022년 1월 10일 오후 1시 14분을 기준으로 user의 주소를 알고 싶으면 다음 쿼리를 실행하면 됩니다.

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022/01/10-13:14:00'))
  WHERE _key = 'user1'

결과에는 2022년 1월 10일 오후 1시 14분에 마지막으로 알려진 주소에 해당하는 항목이 2021/12/20-09:44:31.010에 업데이트된 street와 city 그리고 2005/03/01-11:12:15.112의 state가 조합된 형태로 표시됩니다.

address
{street:'76 Xyz Street', city:'Brooklyn', state: :'NY'}

Unix 타임스탬프를 사용해도 동일한 결과를 얻을 수 있습니다.

  SELECT address
  FROM myTable(as_of => TIMESTAMP_FROM_UNIX_MILLIS(1641820440000))
  WHERE _key = 'user1'

다음 데이터 세트는 연기와 일산화탄소의 유무 상태에 대한 알람을 보여줍니다. column family는 alarmType이고 column qualifier는 smoke 및 carbonMonoxide입니다. 각 열의 셀은 비어 있는 줄로 구분됩니다.

	alarmType
_key	smoke	carbonMonoxide
building1#section1	2023/04/01-09:10:15.000: 'off' 2023/04/01-08:41:40.000: 'on' 2020/07/03-06:25:31.000: 'off' 2020/07/03-06:02:04.000: 'on'	2023/04/01-09:22:08.000: 'off' 2023/04/01-08:53:12.000: 'on'


building1#section2	2021/03/11-07:15:04.000: 'off' 2021/03/11-07:00:25.000: 'on'

다음 쿼리를 사용하면 2023년 4월 1일 오전 9시에 연기 알람이 켜진 위치에 해당하는 building1 부분과 당시 일산화탄소 알람의 상태를 찾을 수 있습니다.

  SELECT _key AS location, sensorType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023/04/01-09:00:00.000'))
  WHERE _key LIKE 'building1%' and sensorType['smoke'] = 'on'

결과는 다음과 같습니다.

location	CO_sensor
building1#section1	'on'

시계열 데이터 쿼리

Bigtable의 일반적인 사용 사례는 시계열 데이터를 저장하는 경우입니다. 다음 샘플 데이터 세트는 날씨 센서에 대한 온도 및 습도 수치를 보여줍니다. column family ID는 metrics이고 column qualifier는 temperature 및 humidity입니다. 열의 셀은 비어 있는 줄로 구분되며 각 셀은 타임스탬프가 포함된 센서 수치를 나타냅니다.

	metrics
_key	temperature	humidity
sensorA#20230105	2023/01/05-02:00:00.000: 54 2023/01/05-01:00:00.000: 56 2023/01/05-00:00:00.000: 55	2023/01/05-02:00:00.000: 0.89 2023/01/05-01:00:00.000: 0.9 2023/01/05-00:00:00.000: 0.91


sensorA#20230104	2023/01/04-23:00:00.000: 56 2023/01/04-22:00:00.000: 57	2023/01/04-23:00:00.000: 0.9 2023/01/04-22:00:00.000: 0.91

after, before, after_or_equal 시간 필터를 사용해서 타임스탬프 값의 특정 범위를 검색할 수 있습니다. 다음 예시는 after를 사용합니다.

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(after => TIMESTAMP('2023/01/04-23:00:00'),
         before => TIMESTAMP('2023/01/05-01:00:00'))
   WHERE _key LIKE 'sensorA%'

이 쿼리는 다음 형식으로 데이터를 반환합니다.

temp_versioned
{timestamp: '2023/01/05-01:00:00.000', value:56} {timestamp: '2023/01/05-00:00:00.000', value: 55}


{timestamp: '2023/01/04-23:00:00.000', value:56}

쿼리 JSON

JSON 함수를 사용하면 운영 워크로드를 위해 Bigtable 값으로 저장된 JSON을 조작할 수 있습니다.

예를 들어 다음 쿼리를 사용하여 row key와 함께 session column family에 있는 최신 셀로부터 JSON 요소 abc의 값을 검색할 수 있습니다.

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

특수문자 및 예약어 이스케이프 처리

Bigtable은 테이블 및 열 이름 지정에 있어서 높은 유연성을 제공합니다. 따라서 SQL 쿼리에서 특수문자 또는 예약어로 인해 테이블 이름을 이스케이프 처리해야 할 수 있습니다.

예를 들어 다음 쿼리는 테이블 이름에 있는 마침표 때문에 유효한 SQL이 아닙니다.

  -- ERROR: Table name format not supported

  SELECT * FROM my.table WHERE _key = 'r1'

하지만 백틱(`) 문자를 사용해서 항목을 묶으면 이 문제를 해결할 수 있습니다.

  SELECT * FROM `my.table` WHERE _key = 'r1'

SQL 예약 키워드가 식별자로 사용된 경우에는 이것도 비슷하게 이스케이프 처리할 수 있습니다.

  SELECT * FROM `select` WHERE _key = 'r1'

Bigtable 클라이언트 라이브러리에 SQL 사용

Java 및 Python용 Bigtable 클라이언트 라이브러리는 executeQuery API를 사용하는 SQL로 데이터를 쿼리할 수 있습니다. 다음 예시에서는 쿼리를 실행하고 데이터에 액세스하는 방법을 보여줍니다.

자바

이 기능을 사용하려면 java-bigtable 버전 2.41.0 이상을 사용해야 합니다. 사용에 대한 자세한 내용은 Javadoc에서 executeQuery, Statement, ResultSet를 참조하세요.

  static void query(BigtableDataClient client) {
    try (ResultSet resultSet =
        client.executeQuery(
            Statement.of(
                "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key='mykey'"))) {
      while (resultSet.next()) {
        ByteString byteValue = resultSet.getBytes("bytesCol");
        String stringValue = resultSet.getString("stringCol");
        Map<ByteString, ByteString> cf3Value =
            resultSet.getMap("cf3", SqlType.mapOf(SqlType.bytes(), SqlType.bytes()));
        // Do something with the data
      }
    }
  }

Python 비동기

이 기능을 사용하려면 python-bigtable 버전 2.26.0 이상을 사용해야 합니다.

  from google.cloud.bigtable.data import BigtableDataClientAsync

  async def execute_query(project_id, instance_id, table_id):
      async with BigtableDataClientAsync(project=project_id) as client:
          query = (
            "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol,"
            " cf3 FROM {table_id} WHERE _key='mykey'"
          )
          async for row in await client.execute_query(query, instance_id):
            print(row["_key"], row["bytesCol"], row["stringCol"], row["cf3"])