이 페이지에서는 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
절에 추가하여 평면화
쿼리의 결과를 새 테이블로 작성하려면 쿼리 결과에서 테이블 만들기를 참조하세요.