BigQuery 변이 테이블 평면화

이 페이지에서는 BigQuery 변이 스키마를 사용하는 BigQuery 테이블을 평면화하는 방법을 보여줍니다.

BigQuery 테이블을 평면화하면 반복 행이 제거됩니다. 평면화는 다음과 같은 상황에서 유용할 수 있습니다.

  • 데이터 시각화 등에 사용되는 타사 도구에 평면화된 구조의 테이블이 필요한 경우
  • 독립적으로 반복된 필드를 여러 개 쿼리하거나 이러한 필드의 크로스 프로덕트를 계산할 경우 오류 메시지 Cannot query the cross product of repeated fields ...가 표시됩니다.

테이블 평면화

BigQuery에 다음 행을 포함하는 테이블이 있다고 가정해 보겠습니다. 행에는 2개의 대체 염기 CT(alternate_bases.alt 열)와 2개의 추출 NA12890NA12878(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 절에 추가하여 평면화

쿼리의 결과를 새 테이블로 작성하려면 쿼리 결과에서 테이블 만들기를 참조하세요.