Bigtable용 GoogleSQL 쿼리 예시
이 페이지의 예시는 일반 및 고급 Bigtable 쿼리의 SQL 쿼리 패턴을 보여줍니다. Bigtable Studio 쿼리 편집기에서 GoogleSQL 쿼리를 실행할 수 있습니다. 또한 Java용 Bigtable 클라이언트 라이브러리를 사용하여 쿼리를 실행할 수도 있습니다.
이 페이지를 읽기 전에 Bigtable용 GoogleSQL 개요를 읽어보세요.
이 페이지의 예시에서는 예시 데이터와 비슷한 ID와 값이 사용됩니다.
일반적인 Bigtable SQL 쿼리 패턴
다음은 Bigtable 데이터의 일반적인 쿼리 예시입니다. Bigtable 데이터 API를 호출하는 비슷한 쿼리 예시를 보려면 읽기 예시 및 필터 사용을 참조하세요.
지정된 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'
고급 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-04 23:51:00+00'))
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