이 페이지에서는 BigQuery 변이 스키마를 사용하는 BigQuery 테이블을 평면화하는 방법을 보여줍니다.
BigQuery 테이블을 평면화하면 반복 행이 제거됩니다. 평면화는 다음과 같은 상황에서 유용할 수 있습니다.
- 데이터 시각화 등에 사용되는 타사 도구에 평면화된 구조의 테이블이 필요한 경우
- 독립적으로 반복된 필드를 여러 개 쿼리하거나 이러한 필드의 크로스 프로덕트를 계산할 경우 오류 메시지
Cannot query the cross product of repeated fields ...가 표시됩니다.
테이블 평면화
BigQuery에 다음 행을 포함하는 테이블이 있다고 가정해 보겠습니다. 행에는 2개의 대체 염기 C 및 T(alternate_bases.alt 열)와 2개의 추출 NA12890 및 NA12878(call.name 열)이 있습니다.
| 행 | reference_name | start_position | end_position | reference_bases | alternate_bases.alt | alternate_bases.AC | quality | filter | call.name | call.genotype |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | C | 1 | 577.59 | LowMQ | NA12890 | 1 |
| T | 1 | 2 | ||||||||
| NA12878 | 1 | |||||||||
| 2 |
다음 쿼리를 실행하여 반복 추출 레코드로 테이블을 평면화하고 추출마다 한 행을 반환합니다.
여기에 사용된 쉼표(,) 연산자는 JOIN의 축약 표기법입니다.
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call
위 쿼리는 t.call에 정규화된 경로를 사용하지만 다음과 같이 t.call 대신 UNNEST 연산자를 사용할 수도 있습니다.
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE`, UNNEST(call) AS call
두 경우 모두 쿼리는 추출마다 한 행을 반환합니다.
| 행 | reference_name | start_position | end_position | reference_bases | call_name |
|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 |
| 2 | chrY | 9909316 | 9909317 | A | NA12878 |
추가 필드 평면화
SELECT 절에 필드를 추가하면 각 추출에 추가 정보를 포함할 수 있습니다. 예를 들어 다음 쿼리는 추출 유전자형을 정수 배열로 추가합니다.
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, call.genotype FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call
쿼리를 실행하면 다음이 반환됩니다.
| 행 | reference_name | start_position | end_position | reference_bases | call_name | genotype |
|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 |
| 2 | ||||||
| 2 | chrY | 9909316 | 9909317 | A | NA12878 | 1 |
| 2 |
BigQuery 테이블을 더 평면화하여 행마다 하나의 유전자형을 반환하려면 call.genotype 필드를 사용한 JOIN을 하나 더 추가합니다.
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, genotype FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype
쿼리를 실행하면 다음이 반환됩니다.
| 행 | reference_name | start_position | end_position | reference_bases | call_name | genotype |
|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 |
| 2 | chrY | 9909316 | 9909317 | A | NA12890 | 2 |
| 3 | chrY | 9909316 | 9909317 | A | NA12878 | 1 |
| 4 | chrY | 9909316 | 9909317 | A | NA12878 | 2 |
각 호출에 genotype 값이 두 개 포함되어 있으므로 출력에서 각 call_name이 중복되었습니다.
다음 쿼리는 독립적으로 반복되는 레코드(alternate_bases)를 SELECT 절에 추가한 결과를 보여줍니다.
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, genotype, alternate_bases FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype
쿼리를 실행하면 다음이 반환됩니다.
| 행 | reference_name | start_position | end_position | reference_bases | call_name | genotype | alternate_bases.alt | alternate_bases.AC |
|---|---|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 | C | 1 |
| T | 1 | |||||||
| 2 | chrY | 9909316 | 9909317 | A | NA12890 | 2 | C | 1 |
| T | 1 | |||||||
| 3 | chrY | 9909316 | 9909317 | A | NA12878 | 1 | C | 1 |
| T | 1 | |||||||
| 4 | chrY | 9909316 | 9909317 | A | NA12878 | 2 | C | 1 |
| T | 1 |
genotype 열이 평면화되었지만 출력에 alternate_bases 열이 모두 포함됩니다. genotype 열의 색인과 일치하는 특정 대체 염기를 반환하려면 다음 쿼리와 같이 ORDINAL 함수를 사용합니다.
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, genotype, IF(genotype > 0, alternate_bases[ORDINAL(genotype)], NULL) AS alternate_bases FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype
쿼리를 실행하면 다음이 반환됩니다.
| 행 | reference_name | start_position | end_position | reference_bases | call_name | genotype | alternate_bases.alt | alternate_bases.AC |
|---|---|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 | C | 1 |
| 2 | chrY | 9909316 | 9909317 | A | NA12890 | 2 | T | 1 |
| 3 | chrY | 9909316 | 9909317 | A | NA12878 | 1 | C | 1 |
| 4 | chrY | 9909316 | 9909317 | A | NA12878 | 2 | T | 1 |
각 행에는 대체 대립유전자가 하나만 들어 있으므로 SELECT 절에서 genotype 열의 시맨틱스가 변경되었습니다.
다음 쿼리는 IF(genotype > 0, 1, genotype) AS alt_genotype을 사용하여 genotype 열의 형식을 바꾸는 방법을 보여줍니다. 이렇게 하면 유전자형 값이 다음과 같이 설정됩니다.
- 대체 염기와 일치하면
1 - 기준 염기와 일치하면
0 - 추출되지 않았으면
-1. Variant Transforms 도구는-1을 사용하여 VCF 파일의.과 같이 추출되지 않은 유전자형을 나타냅니다.
다음 쿼리는 alternate_bases 레코드를 평면화하고 색인을 필터링 기준으로 사용해서 alternate_bases.alt 열만 포함하는 방법을 보여줍니다.
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, IF(genotype > 0, 1, genotype) AS alt_genotype, IF(genotype > 0, alts.alt, NULL) AS alt FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype LEFT JOIN t.alternate_bases AS alts WITH OFFSET AS a_index WHERE genotype IN (a_index + 1, 0, -1)
쿼리를 실행하면 다음이 반환됩니다.
| 행 | reference_name | start_position | end_position | reference_bases | call_name | alt_genotype | alt |
|---|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 | C |
| 2 | chrY | 9909316 | 9909317 | A | NA12890 | 1 | T |
| 3 | chrY | 9909316 | 9909317 | A | NA12878 | 1 | C |
| 4 | chrY | 9909316 | 9909317 | A | NA12878 | 1 | T |
이 쿼리는 LEFT JOIN을 사용하여 대체 염기가 없는 행을 포함합니다. 대체 염기가 하나 이상 있는 레코드만 포함하려면 다음 중 하나를 수행합니다.
INNER JOIN을 사용합니다.FROM절에t.alternate_bases AS alts WITH OFFSET as a_index를 포함합니다.
또한 쿼리는 다중 대립유전자 구간이 있으면(대체 염기가 여러 개인 경우) 유전자형 값 0에 대한 추가 행을 반환합니다. 이러한 상황은 LEFT JOIN이 각 유전자형과 대체 염기 사이에 CROSS JOIN을 수행하기 때문에 발생합니다.
그런 후 a_index로 필터링합니다. 따라서 0이 아닌 유전자형은 a_index + 1로 필터링되지만, 0 값 유전자형은 alternate_base와 값이 0인 유전자형이 있는 모든 조합에 대해 반환됩니다.
쿼리 예시
다음 쿼리는 반복 레코드가 포함되지 않도록 BigQuery 변이 테이블을 평면화하는 방법의 전체 예입니다. 유전자형 값은 다음과 같이 설정됩니다.
- 대체 염기와 일치하면
1 - 기준 염기와 일치하면
0 - 설정되지 않았으면
-1
다음 테이블에 대해 쿼리를 수행합니다.
| 행 | reference_name | start_position | end_position | reference_bases | alternate_bases.alt | alternate_bases.AC | quality | filter | call.name | call.genotype |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | chr9 | 139825375 | 139825376 | T | 120.24 | LowGQX | NA12878 | 0 | ||
| PASS | NA12877 | -1 | ||||||||
| NA12890 | -1 | |||||||||
| NA12889 | -1 | |||||||||
| NA12891 | 0 | |||||||||
| 0 | ||||||||||
| 2 | chr4 | 4070556 | 4070558 | A | null | LowGQX | NA12878 | 0 | ||
| LowMQ | 0 | |||||||||
| MaxDepth | ||||||||||
| 3 | chrX | 61702219 | 61702220 | T | C | 1 | 5.72 | LowGQX | NA12877 | 0 |
| LowMQ | 1 | |||||||||
| LowQD | ||||||||||
| TruthSensitivityTranche99.90to100.00 | ||||||||||
| 4 | chrY | 9909316 | 9909317 | A | C | 1 | 577.59 | LowMQ | NA12890 | 1 |
| T | 1 | 2 | ||||||||
| NA1878 | 1 | |||||||||
| 2 |
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, IF(genotype > 0, alts.alt, NULL) AS alt, t.quality, ARRAY_TO_STRING(t.filter, ' ') AS filter, call.name AS call_name, IF(genotype > 0, 1, genotype) AS alt_genotype, call.phaseset FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype LEFT JOIN t.alternate_bases AS alts WITH OFFSET AS a_index WHERE genotype IN (a_index + 1, 0, -1)
쿼리를 실행하면 다음이 반환됩니다.
| 행 | reference_name | start_position | end_position | reference_bases | alt | quality | filter | call_name | alt_genotype | phaseset |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | chr9 | 139825375 | 139825376 | T | null | 120.24 | LowGQX PASS | NA12878 | 0 | null |
| 2 | chr9 | 139825375 | 139825376 | T | null | 120.24 | LowGQX PASS | NA12877 | -1 | null |
| 3 | chr9 | 139825375 | 139825376 | T | null | 120.24 | LowGQX PASS | NA12890 | -1 | null |
| 4 | chr9 | 139825375 | 139825376 | T | null | 120.24 | LowGQX PASS | NA12889 | -1 | null |
| 5 | chr9 | 139825375 | 139825376 | T | null | 120.24 | LowGQX PASS | NA12891 | 0 | null |
| 6 | chr9 | 139825375 | 139825376 | T | null | 120.24 | LowGQX PASS | NA12891 | 0 | null |
| 7 | chr4 | 4070556 | 4070558 | A | null | null | LowGQX LowMQ MaxDepth | NA12878 | 0 | null |
| 8 | chr4 | 4070556 | 4070558 | A | null | null | LowGQX LowMQ MaxDepth | NA12878 | 0 | null |
| 9 | chrX | 61702219 | 61702220 | T | null | 5.72 | LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 | NA12877 | 0 | null |
| 10 | chrX | 61702219 | 61702220 | T | C | 5.72 | LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 | NA12877 | 1 | null |
| 11 | chrY | 9909316 | 9909317 | A | C | 577.59 | LowMQ | NA12890 | 1 | null |
| 12 | chrY | 9909316 | 9909317 | A | T | 577.59 | LowMQ | NA12890 | 1 | null |
| 13 | chrY | 9909316 | 9909317 | A | C | 577.59 | LowMQ | NA12878 | 1 | null |
| 14 | chrY | 9909316 | 9909317 | A | T | 577.59 | LowMQ | NA12878 | 1 | null |
다른 반복 필드에 대해서는 다음 중 하나를 수행할 수 있습니다.
- 단일 필드로 연결(
ARRAY_TO_STRING등의 함수 사용) FROM또는LEFT JOIN절에 추가하여 평면화
쿼리의 결과를 새 테이블로 작성하려면 쿼리 결과에서 테이블 만들기를 참조하세요.