이전 SQL의 중첩 및 반복 필드 쿼리
이 문서에서는 이전 SQL 쿼리 구문에서 중첩된 데이터와 반복되는 데이터를 쿼리하는 방법을 자세히 설명합니다. BigQuery의 기본 쿼리 문법은 GoogleSQL입니다. GoogleSQL의 중첩 및 반복 데이터 처리에 대한 자세한 내용은 GoogleSQL 마이그레이션 가이드를 참조하세요.
BigQuery는 JSON 및 Avro 파일 형태의 중첩 및 반복 데이터의 로드와 내보내기를 지원합니다. legacy SQL 쿼리는 대부분 BigQuery에서 데이터를 자동으로 평면화할 수 있습니다. 예를 들어 여러 SELECT 문을 통해 데이터 구조를 유지하면서 중첩되거나 반복되는 필드를 검색할 수 있으며 WHERE 절로 구조를 유지하면서 데이터를 필터링할 수 있습니다. 반대로 ORDER BY 및 GROUP BY 절은 쿼리된 데이터를 암시적으로 평면화합니다. legacy SQL에서 여러 개의 반복 필드를 쿼리하는 등 데이터가 암시적으로 평면화되지 않는 경우 FLATTEN 및 WITHIN SQL 함수를 사용하면 데이터를 쿼리할 수 있습니다.
평면화
중첩 데이터를 쿼리하면 BigQuery에서 자동으로 테이블의 데이터를 평면화합니다. 예를 들어 개인 정보의 샘플 스키마를 살펴보겠습니다.
Last modified Schema Total Rows Total Bytes Expiration
----------------- ----------------------------------- ------------ ------------- ------------
27 Sep 10:01:06 |- kind: string 4 794
|- fullName: string (required)
|- age: integer
|- gender: string
+- phoneNumber: record
| |- areaCode: integer
| |- number: integer
+- children: record (repeated)
| |- name: string
| |- gender: string
| |- age: integer
+- citiesLived: record (repeated)
| |- place: string
| +- yearsLived: integer (repeated)보다시피 반복되고 중첩된 필드가 여러 개 있습니다. 개인 정보 테이블에 다음과 같은 이전 SQL 쿼리를 실행하는 경우:
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery에서 평면화된 출력으로 데이터를 반환합니다.
+---------------+-----+--------+-------------------+------------------------+ | name | age | gender | citiesLived_place | citiesLived_yearsLived | +---------------+-----+--------+-------------------+------------------------+ | John Doe | 22 | Male | Seattle | 1995 | | John Doe | 22 | Male | Stockholm | 2005 | | Mike Jones | 35 | Male | Los Angeles | 1989 | | Mike Jones | 35 | Male | Los Angeles | 1993 | | Mike Jones | 35 | Male | Los Angeles | 1998 | | Mike Jones | 35 | Male | Los Angeles | 2002 | | Mike Jones | 35 | Male | Washington DC | 1990 | | Mike Jones | 35 | Male | Washington DC | 1993 | | Mike Jones | 35 | Male | Washington DC | 1998 | | Mike Jones | 35 | Male | Washington DC | 2008 | | Mike Jones | 35 | Male | Portland | 1993 | | Mike Jones | 35 | Male | Portland | 1998 | | Mike Jones | 35 | Male | Portland | 2003 | | Mike Jones | 35 | Male | Portland | 2005 | | Mike Jones | 35 | Male | Austin | 1973 | | Mike Jones | 35 | Male | Austin | 1998 | | Mike Jones | 35 | Male | Austin | 2001 | | Mike Jones | 35 | Male | Austin | 2005 | | Anna Karenina | 45 | Female | Stockholm | 1992 | | Anna Karenina | 45 | Female | Stockholm | 1998 | | Anna Karenina | 45 | Female | Stockholm | 2000 | | Anna Karenina | 45 | Female | Stockholm | 2010 | | Anna Karenina | 45 | Female | Moscow | 1998 | | Anna Karenina | 45 | Female | Moscow | 2001 | | Anna Karenina | 45 | Female | Moscow | 2005 | | Anna Karenina | 45 | Female | Austin | 1995 | | Anna Karenina | 45 | Female | Austin | 1999 | +---------------+-----+--------+-------------------+------------------------+
이 예시에서 citiesLived.place는 현재 citiesLived_place이고 citiesLived.yearsLived는 현재 citiesLived_yearsLived입니다.
중첩 필드는 BigQuery에서 자동으로 평면화할 수 있지만 반복 필드를 두 개 이상 처리할 때는 FLATTEN을 명시적으로 호출해야 할 수도 있습니다. 예를 들어 다음과 같은 legacy SQL 쿼리를 실행하려는 경우:
SELECT fullName, age FROM [dataset.tableId] WHERE (citiesLived.yearsLived > 1995 ) AND (children.age > 3)
BigQuery에서 다음과 같은 오류를 반환합니다.
Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived
다수의 반복 필드를 쿼리하려면 필드 중 하나를 평면화해야 합니다.
SELECT fullName, age, gender, citiesLived.place FROM (FLATTEN([dataset.tableId], children)) WHERE (citiesLived.yearsLived > 1995) AND (children.age > 3) GROUP BY fullName, age, gender, citiesLived.place
다음과 같은 결과가 반환됩니다.
+------------+-----+--------+-------------------+ | fullName | age | gender | citiesLived_place | +------------+-----+--------+-------------------+ | John Doe | 22 | Male | Stockholm | | Mike Jones | 35 | Male | Los Angeles | | Mike Jones | 35 | Male | Washington DC | | Mike Jones | 35 | Male | Portland | | Mike Jones | 35 | Male | Austin | +------------+-----+--------+-------------------+
WITHIN 절
WITHIN 키워드는 특히 집계 함수와 함께 작동해 레코드 및 중첩 필드의 하위 항목과 반복 필드를 대상으로 집계합니다. WITHIN 키워드를 지정할 때는 집계할 범위를 지정해야 합니다.
WITHIN RECORD: 레코드 내에서 반복되는 값의 데이터를 집계합니다.WITHIN node_name: 집계 함수에서 필드의 상위 노드에 해당하는 특정 노드 내에서 반복되는 값의 데이터를 집계합니다.
이전 예시의 사람들이 몇 명의 자녀를 두었는지 알아본다고 가정합니다. 이를 위해서는 각 레코드의 children.name 수를 집계하면 됩니다.
SELECT fullName, COUNT(children.name) WITHIN RECORD AS numberOfChildren FROM [dataset.tableId];
다음과 같은 결과를 얻게 됩니다.
+---------------+------------------+ | fullName | numberOfChildren | +---------------+------------------+ | John Doe | 2 | | Jane Austen | 2 | | Mike Jones | 3 | | Anna Karenina | 0 | +---------------+------------------+
비교하려면 자녀 이름을 모두 나열합니다.
SELECT fullName, children.name FROM [dataset.tableId]
+---------------+---------------+ | fullName | children_name | +---------------+---------------+ | John Doe | Jane | | John Doe | John | | Jane Austen | Josh | | Jane Austen | Jim | | Mike Jones | Earl | | Mike Jones | Sam | | Mike Jones | Kit | | Anna Karenina | None | +---------------+---------------+
이는 WITHIN RECORD 쿼리 결과와 일치합니다. John Doe에게는 Jane과 John이라는 두 명의 자녀가 있고 Jane Austen에게는 Josh와 Jim이라는 두 명의 자녀가 있고 Mike Jones에게는 Earl, Sam, Kit라는 세 명의 자녀가 있으며 Anna Karenina에게는 자녀가 없습니다.
이번에는 이사 횟수를 알아본다고 가정하겠습니다.
WITHIN 절을 사용하면 특정 노드 하나를 대상으로 집계할 수 있습니다.
SELECT fullName, COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived, citiesLived.place, COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity, FROM [dataset.tableId];
+---------------+---------------------+-------------------+-------------------------+ | fullName | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity | +---------------+---------------------+-------------------+-------------------------+ | John Doe | 2 | Seattle | 1 | | John Doe | 2 | Stockholm | 1 | | Mike Jones | 4 | Los Angeles | 4 | | Mike Jones | 4 | Washington DC | 4 | | Mike Jones | 4 | Portland | 4 | | Mike Jones | 4 | Austin | 4 | | Anna Karenina | 3 | Stockholm | 4 | | Anna Karenina | 3 | Moscow | 3 | | Anna Karenina | 3 | Austin | 2 | +---------------+---------------------+-------------------+-------------------------+
이 쿼리는 다음과 같은 작업을 수행합니다.
citiesLived.place에서WITHIN RECORD를 수행해 각 인물이 거주한 장소의 수를 집계합니다.citiesLived.yearsLived에서WITHIN을 수행해 도시별로 각 인물이 거주한 횟수를 집계합니다(citiesLived에서 집계).
중첩 필드와 반복 필드를 대상으로 범위 지정 집계 사용은 BigQuery의 가장 강력한 기능 중 하나로서, 많은 비용이 드는 쿼리의 조인을 제거할 수 있습니다.