이 페이지에서는 BigQuery를 사용하여 변이를 분석하는 고급 방법을 설명합니다.
이 튜토리얼의 데이터는 Illumina Platinum Genomes project에서 가져온 것입니다.
이 데이터는 BigQuery 변이 스키마를 사용한 BigQuery 테이블에 로드되었습니다.
테이블 이름은 platinum_genomes_deepvariant_variants_20180823입니다.
변이 데이터가 BigQuery 변이 스키마를 사용하는 BigQuery 테이블에 있는 경우 이 튜토리얼에 있는 쿼리를 데이터에 쉽게 적용할 수 있습니다. 변이 데이터를 BigQuery에 로드하는 방법은 transform pipeline 사용에 대한 문서를 참조하세요.
목표
이 튜토리얼에서는 다음 작업을 수행하는 방법을 보여줍니다.
- Genomics 데이터 개요 가져오기
- 비변이 세그먼트를 표현하는 방법 찾기
- 변이 추출을 표현하는 방법 찾기
- 변이 추출 품질 필터를 표현하는 방법 찾기
- 계층 구조 열 집계
- 쿼리 단축
- 고유한 행 수 계산
- 행 그룹화
- 맞춤 설정 함수 작성
이 튜토리얼에서는 다음 정보를 찾는 방법도 보여줍니다.
- 테이블의 행 수입니다.
- 변이 추출 수
- 각 샘플에서 추출된 변이
- 샘플 수
- 염색체당 변이 수
- 샘플당 고품질 변이 수
비용
이 문서에서는 비용이 청구될 수 있는 다음과 같은 Google Cloud 구성요소를 사용합니다.
- BigQuery
프로젝트 사용량을 기준으로 예상 비용을 산출하려면 가격 계산기를 사용하세요.
시작하기 전에
- Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
- BigQuery 변이 스키마를 숙지해야 합니다.
테이블 스키마 및 데이터 보기
테이블 액세스 및 스키마 보기
Illumina Platinum Genomes platinum_genomes_deepvariant_variants_20180823 테이블은 공개적으로 사용 가능합니다.
테이블의 변이 및 비변이
Illumina Platinum Genomes 데이터는 gVCF 형식을 사용합니다. 즉, 테이블에 비변이를 포함하는 행이 있습니다. 이 비변이를 '참조 추출'이라고 합니다.
테이블에는 일반적으로 비변이 세그먼트가 다음 방식으로 표현되어 있습니다.
- 길이가 0인
alternate_bases값 alternate_bases.alt값으로 표시된 텍스트 문자열<NON_REF>alternate_bases.alt값으로 표시된 텍스트 문자열<*>
비변이 세그먼트가 표현되는 방식은 일반적으로 소스 데이터를 생성한 변이 추출기에 따라 달라집니다. platinum_genomes_deepvariant_variants_20180823 테이블의 변이는 <*> 표기법을 사용하는 DeepVariant를 사용하여 추출되었습니다.
다음 테이블은 비변이 세그먼트를 나타내는 값이 포함된 일부 행을 보여줍니다. 세그먼트에는 다음 정보가 표시됩니다.
- 염색체
1에 염기10개의 참조 블록 - 참조 블록은
1000위치에서 시작됩니다. 1000위치의 참조 기준은A입니다.- 블록의 다른 위치에 있는 참조 기준은 표시되지 않습니다.
다음 테이블에서 alternate_bases REPEATED RECORD 열에는 값이 없습니다. 즉, 길이가 0인 ARRAY입니다.
| reference_name | start_position | end_position | reference_bases | alternate_bases.alt |
|---|---|---|---|---|
| 1 | 1000 | 1010 | A |
다음 테이블에서 alternate_bases REPEATED RECORD 열은 길이가 1이고, 리터럴 텍스트 문자열 <*>를 포함합니다.
| reference_name | start_position | end_position | reference_bases | alternate_bases.alt |
|---|---|---|---|---|
| 1 | 1000 | 1010 | A | <*> |
이 가이드에서 사용된 쿼리는 이전 테이블의 표현을 사용합니다.
게놈에서 비변이 위치를 표현하는 방법에 대한 자세한 내용은 VCF 사양을 참조하세요.
테이블 데이터 보기
platinum_genomes_deepvariant_variants_20180823 테이블의 데이터를 보려면 다음 단계를 완료하세요.
Google Cloud 콘솔의 BigQuery 페이지에서 테이블을 열람합니다.
테이블 정보가 나타납니다. 이 테이블에는 19.6GB의 데이터가 포함되어 있고 105,000,000개가 넘는 행이 있습니다.
미리보기를 클릭하여 테이블의 일부 행을 확인합니다.
테이블 쿼리
테이블 스키마와 일부 행을 확인한 후 쿼리를 실행하고 데이터를 분석합니다. 계속하기 전에 BigQuery가 사용하는 표준 SQL 쿼리 구문을 숙지해야 합니다.
테이블의 총 행 수 계산
테이블의 행 수를 확인하려면 다음 작업을 수행합니다.
Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.
쿼리 작성을 클릭합니다.
다음 쿼리를 복사하여 새 쿼리 텍스트 영역에 붙여넣습니다.
#standardSQL SELECT COUNT(1) AS number_of_rows FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`
쿼리 실행을 클릭합니다. 쿼리는 다음 결과를 반환합니다.
행 number_of_rows 1 105923159
테이블에서 변이 추출 수 계산
테이블의 각 행에는 변이나 비변이 세그먼트인 게놈 위치가 있습니다.
또한 각 행에는 변이 추출 ARRAY인 call 열이 포함되어 있습니다. 각 call열에는 유전자형, 품질 열, 판독 깊이, 일반적으로 VCF 파일에 있는 여러 항목 등 name과 여러 값이 포함되어 있습니다.
변이 추출 수를 계산하려면 ARRAY 열 내부에 있는 요소 개수를 쿼리합니다. 이 작업은 다음에 나오는 여러 방식으로 수행할 수 있습니다.
각 쿼리는 값 182,104,652를 반환하고, 이것은 데이터 세트에 평균 행당 1.7개의 변이 추출이 있음을 의미합니다.
call 배열의 길이 합산
각 call 배열 길이를 합하여 모든 샘플에서 총 변이 추출 수를 계산합니다.
#standardSQL
SELECT
SUM(ARRAY_LENGTH(call)) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`
쿼리는 다음 결과를 반환합니다.
| 행 | number_of_calls |
|---|---|
| 1 | 182104652 |
각 행에 JOIN 수행
call 열이 있는 각 행에서 JOIN을 사용하여 모든 샘플에서 총 변이 추출 수를 계산합니다. 쿼리는 JOIN에 사용된 축약 표기법인 쉼표(,) 연산자를 사용합니다.
call 열을 조인하면 call 열에서 암시적 UNNEST 작업이 수행됩니다.
#standardSQL
SELECT
COUNT(call) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
쿼리는 다음 결과를 반환합니다.
| 행 | number_of_calls |
|---|---|
| 1 | 182104652 |
call 열에서 name 계산
모든 샘플에서 총 변이 추출 수를 계산하는 세 번째 방법은 call 열의 name 값을 계산하는 것입니다. 각 call 열에 단일 name 값이 포함되어야 하므로, 다음 쿼리를 실행할 수 있습니다.
#standardSQL
SELECT
COUNT(call.name) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
쿼리는 다음 결과를 반환합니다.
| 행 | number_of_calls |
|---|---|
| 1 | 182104652 |
변이 및 비변이 세그먼트 수 계산
테이블에서 변이 및 비변이 세그먼트의 수를 계산하려면 먼저 다음과 같이 쿼리를 실행하여 비변이 세그먼트를 필터링합니다.
#standardSQL
SELECT
COUNT(1) AS number_of_real_variants
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.alternate_bases) AS alt
WHERE
alt.alt NOT IN ("<NON_REF>", "<*>"))
쿼리는 다음 결과를 반환합니다.
| 행 | number_of_real_variants |
|---|---|
| 1 | 38549388 |
변이 추출 수 계산에서 설명했듯이 이 테이블에서 총 변이 추출 수는 182,104,652개이므로, 이것은 테이블의 대다수 행이 비변이 세그먼트임을 나타냅니다.
테이블의 변이 및 비변이 섹션에서 보았듯이 변이 행을 비변이 세그먼트로 분류하는 방법에는 세 가지 이상이 있습니다. 위 쿼리에서 WHERE 절에는 실제 변이인 값이 alternate_bases 열에 포함된 행이 있습니다(즉, <*> 또는 <NON_REF>와 같은 특수 마커 값이 아님).
테이블의 각 행에서 해당 행의 alternate_bases 열에 대해 서브 쿼리가 실행되어, <NON_REF> 또는 <*>가 아닌 alternate_bases의 각 값에 대해 1 값을 반환합니다. 서브 쿼리가 반환하는 행 수는 변이 세그먼트 수입니다.
다음 쿼리는 비변이 세그먼트 수를 계산하는 방법을 보여줍니다.
#standardSQL
SELECT
COUNT(1) AS number_of_non_variants
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
NOT EXISTS (SELECT 1
FROM UNNEST(v.alternate_bases) AS alt
WHERE
alt.alt NOT IN ("<NON_REF>", "<*>"))
쿼리는 다음 결과를 반환합니다.
| 행 | number_of_non_variants |
|---|---|
| 1 | 143555264 |
비변이 세그먼트 수 (143,555,264)에 실제 변이 수 (38,549,388)를 추가하면 총 변이 추출 수와 같습니다.
각 샘플에서 추출된 변이 수 계산
테이블의 최상위 행을 검토한 후 하위 행에 대한 쿼리를 시작할 수 있습니다. 이 행에는 변이에 대해 추출된 개별 샘플과 같은 데이터가 포함되어 있습니다.
테이블의 각 변이에는 call.name에 대해 0개 이상의 값이 포함됩니다. 특정 call.name 값은 여러 행에 표시될 수 있습니다.
각 추출 세트에서 나타나는 행 수를 계산하려면 다음 쿼리를 실행합니다.
#standardSQL
SELECT
call.name AS call_name,
COUNT(call.name) AS call_count_for_call_set
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
GROUP BY
call_name
ORDER BY
call_name
쿼리를 실행하면 6개 행이 반환됩니다. 각 call_name은 시퀀싱된 개별 인간에 해당합니다.
| 행 | call_name | call_count_for_call_set |
|---|---|---|
| 1 | NA12877 | 31592135 |
| 2 | NA12878 | 28012646 |
| 3 | NA12889 | 31028550 |
| 4 | NA12890 | 30636087 |
| 5 | NA12891 | 33487348 |
| 6 | NA12892 | 27347886 |
인간은 일반적으로 call_count_for_call_set의 값에 표시된 3,000만 개의 변이를 갖지 않습니다. 비변이 세그먼트를 필터링하여 변이 행만을 계산합니다.
#standardSQL
SELECT
call.name AS call_name,
COUNT(call.name) AS call_count_for_call_set
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.alternate_bases) AS alt
WHERE
alt.alt NOT IN ("<NON_REF>", "<*>"))
GROUP BY
call_name
ORDER BY
call_name
쿼리는 다음 결과를 반환합니다.
| 행 | call_name | call_count_for_call_set |
|---|---|---|
| 1 | NA12877 | 6284275 |
| 2 | NA12878 | 6397315 |
| 3 | NA12889 | 6407532 |
| 4 | NA12890 | 6448600 |
| 5 | NA12891 | 6516669 |
| 6 | NA12892 | 6494997 |
변이 수는 이제 6백만 개에 가까워졌으며, 이는 인간 1명에서 보다 일반적입니다. 계속해서 다음 섹션으로 넘어가서 유전자형별로 실제 변이를 필터링합니다.
유전자형별 실제 변이 필터링
이 테이블의 변이에는 genotype 값이 -1로 표현되는 추출 없음이 포함되어 있습니다. 이 변이는 개인의 실제 변이로 간주되지 않으므로 필터링해야 합니다. 실제 변이는 0보다 큰 유전자형을 통한 추출만을 포함할 수 있습니다. 추출에 추출 없음(1) 또는 참조(0)인 유전형만이 포함되어 있는 경우에는 실제 변이가 아닙니다.
유전자형별로 변이를 필터링하려면 다음 쿼리를 실행합니다.
#standardSQL
SELECT
call.name AS call_name,
COUNT(call.name) AS call_count_for_call_set
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
EXISTS (SELECT 1 FROM UNNEST(call.genotype) AS gt WHERE gt > 0)
AND NOT EXISTS (SELECT 1 FROM UNNEST(call.genotype) AS gt WHERE gt < 0)
GROUP BY
call_name
ORDER BY
call_name
쿼리는 다음 결과를 반환합니다.
| 행 | call_name | call_count_for_call_set |
|---|---|---|
| 1 | NA12877 | 4486610 |
| 2 | NA12878 | 4502017 |
| 3 | NA12889 | 4422706 |
| 4 | NA12890 | 4528725 |
| 5 | NA12891 | 4424094 |
| 6 | NA12892 | 4495753 |
테이블에서 샘플 수 계산
각 샘플에서 추출된 변이 수 계산에서 각 쿼리는 call_name의 값이 있는 6개 행을 반환했습니다. 행 수를 쿼리하고 값을 가져오려면 다음 쿼리를 실행합니다.
#standardSQL
SELECT
COUNT(DISTINCT call.name) AS number_of_callsets
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
쿼리는 다음 결과를 반환합니다.
| 행 | number_of_callsets |
|---|---|
| 1 | 6 |
염색체당 변이 수 계산
염색체당 변이 수를 계산하려면 다음 쿼리를 실행합니다. 쿼리는 다음을 수행합니다.
- 적어도 하나 이상의 유전자형이 0보다 큰 변이 추출이 하나 이상 있는 모든 행 수를 계산합니다.
- 염색체별로 변이 행을 그룹화하고 각 그룹 수를 계산합니다.
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END
쿼리를 실행하면 염색체(reference_name) 이름과 각 염색체의 변이 행 수가 반환됩니다.
| 행 | reference_name | number_of_variant_rows |
|---|---|---|
| 1 | chr1 | 615000 |
| 2 | chr2 | 646401 |
| 3 | chr3 | 542315 |
| 4 | chr4 | 578600 |
| 5 | chr5 | 496202 |
| ... | ... | ... |
샘플당 고품질 변이 수 계산
여러 FILTER 값으로 추출 쿼리
VCF 사양에는 각기 다른 품질의 변이 추출에 라벨을 지정하는 데 사용될 수 있는 FILTER 열에 대한 설명이 있습니다.
다음 쿼리는 데이터 세트에서 per-variant-call FILTER 값을 확인하는 방법을 보여줍니다.
#standardSQL
SELECT
call_filter,
COUNT(call_filter) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,
v.call,
UNNEST(call.FILTER) AS call_filter
GROUP BY
call_filter
ORDER BY
number_of_calls
쿼리는 다음 결과를 반환합니다.
| 행 | call_filter | number_of_calls |
|---|---|---|
| 1 | RefCall | 11681534 |
| 2 | PASS | 26867854 |
PASS 값은 변이 추출이 고품질임을 의미합니다.
고품질 변이 추출을 위한 FILTER
변이를 분석할 때에는 품질이 낮은 변이를 필터링해야 합니다.
FILTER 열에 PASS 값이 포함되어 있으면 이 열에 다른 값이 없을 가능성이 높습니다. 다음을 실행하여 이를 확인할 수 있습니다. 또한 쿼리는 FILTER에 PASS 값이 포함되지 않은 모든 추출을 제외합니다.
#standardSQL
SELECT
reference_name,
start_position,
end_position,
reference_bases,
call.name AS call_name,
(SELECT STRING_AGG(call_filter) FROM UNNEST(call.FILTER) AS call_filter) AS filters,
ARRAY_LENGTH(call.FILTER) AS filter_count
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter = 'PASS')
AND ARRAY_LENGTH(call.FILTER) > 1
ORDER BY
filter_count DESC, reference_name, start_position, end_position, reference_bases, call_name
LIMIT
10
예상대로 쿼리를 실행하면 결과가 0으로 반환됩니다.
각 샘플의 모든 고품질 추출 수 계산
다음 쿼리는 각 추출 세트에서 모든 추출(변이 및 비변이) 수를 계산하는 방법을 보여주며 PASS가 아닌 필터가 있는 추출은 제외합니다.
#standardSQL
SELECT
call.name AS call_name,
COUNT(1) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
GROUP BY
call_name
ORDER BY
call_name
쿼리는 다음 결과를 반환합니다.
| 행 | call_name | number_of_calls |
|---|---|---|
| 1 | NA12877 | 29795946 |
| 2 | NA12878 | 26118774 |
| 3 | NA12889 | 29044992 |
| 4 | NA12890 | 28717437 |
| 5 | NA12891 | 31395995 |
| 6 | NA12892 | 25349974 |
각 샘플의 모든 고품질 실제 변이 추출 수 계산
다음 쿼리는 각 샘플에서 모든 추출(변이 및 비변이) 수를 계산하는 방법을 보여줍니다. 이는 PASS가 아닌 필터가 있는 모든 추출을 제외하고 실제 변이 1개 이상이 있는 추출만을 포함합니다. 즉, genotype > 0입니다.
#standardSQL
SELECT
call.name AS call_name,
COUNT(1) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
AND EXISTS (SELECT 1 FROM UNNEST(call.genotype) as gt WHERE gt > 0)
GROUP BY
call_name
ORDER BY
call_name
쿼리는 다음 결과를 반환합니다.
| 행 | call_name | number_of_calls |
|---|---|---|
| 1 | NA12877 | 4486610 |
| 2 | NA12878 | 4502017 |
| 3 | NA12889 | 4422706 |
| 4 | NA12890 | 4528725 |
| 5 | NA12891 | 4424094 |
| 6 | NA12892 | 4495753 |
권장사항
쿼리 단축
쿼리가 복잡해지면 논리가 정확하면서도 이해하기 쉽도록 간결하게 유지해야 합니다.
다음 예는 염색체당 변이 수를 계산하는 쿼리에서 시작하여 단계별로 SQL 구문과 맞춤 설정 함수를 사용하여 압축하는 방법을 보여줍니다.
염색체당 변이 수 계산 섹션에서 설명했듯이 쿼리에는 다음 요구사항이 있습니다.
- 적어도 하나 이상의 유전자형이 0보다 큰 변이 추출이 하나 이상 있는 모든 행 수를 계산합니다.
- 염색체별로 변이 행을 그룹화하고 각 그룹 수를 계산합니다.
첫 번째 작업을 완료하려면 쿼리 실행 컨텍스트를 행 수준으로 유지하면서 ARRAY(call) 내에서 ARRAY(genotype)를 조사해야 하기 때문에 이 쿼리는 작성하기에 복잡할 수 있습니다. 쿼리의 실행 컨텍스트는 call별 또는 genotype별 결과 대신 변이별 결과를 생성하려 하므로 행 수준에서 유지합니다.
UNNEST 함수를 사용하면 열이 테이블인 것처럼 ARRAY 열에서 쿼리할 수 있습니다. 이 함수는 ARRAY의 각 요소에 대해 하나의 행을 반환합니다. 또한 쿼리 컨텍스트를 변경하지 않습니다. WHERE 절의 EXISTS 서브 쿼리에서 UNNEST 함수를 사용합니다.
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call
WHERE EXISTS (SELECT 1
FROM UNNEST(call.genotype) AS gt
WHERE gt > 0))
GROUP BY
reference_name
ORDER BY
reference_name
이 쿼리는 다음과 같이 염색체당 변이 수 계산의 예와 동일한 결과를 반환합니다.
| 행 | reference_name | number_of_variant_rows |
|---|---|---|
| 1 | chr1 | 615000 |
| 2 | chr10 | 396773 |
| 3 | chr11 | 391260 |
| 4 | chr12 | 382841 |
| 5 | chr13 | 298044 |
| ... | ... | ... |
EXISTS 절을 call.genotype 열이 있는 call 열의 JOIN으로 변경하면 쿼리를 더 간결하게 만들 수 있습니다. 쉼표 연산자는 JOIN에 사용되는 축약 표기법입니다.
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY
reference_name
쿼리가 작동하고 간결하지만 염색체(reference_name)의 오름차순으로 출력을 정렬할 수 없습니다. reference_name의 값이 문자열 유형이고 각 값에 'chr' 프리픽스가 포함되기 때문입니다.
출력을 숫자로 정렬하려면 먼저 reference_name 열에서 'chr' 프리픽스를 삭제하고 별칭 chromosome을 지정합니다.
#standardSQL
SELECT
REGEXP_REPLACE(reference_name, '^chr', '') AS chromosome,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
chromosome
ORDER BY
chromosome
이 쿼리는 REGEXP_REPLACE 함수를 사용하여 'chr' 프리픽스 문자열을 빈 문자열로 바꿉니다. 그런 다음 쿼리는 계산된 chromosome 별칭을 사용하도록 GROUP BY 및 ORDER BY 함수를 변경합니다. 출력은 계속해서 문자열별로 정렬됩니다.
| 행 | 염색체 | number_of_variant_rows |
|---|---|---|
| 1 | 1 | 615000 |
| 2 | 10 | 396773 |
| 3 | 11 | 391260 |
| 4 | 12 | 382841 |
| 5 | 13 | 298044 |
| ... | ... | ... |
대신 숫자로 출력을 정렬하려면 chromosome 열을 문자열에서 정수로 변환합니다.
#standardSQL
SELECT
CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) AS chromosome,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
chromosome
ORDER BY
chromosome
'쿼리는 'X', 'Y'및 'M'과 같은 모든 염색체 이름이 숫자가 아니므로 오류를 반환합니다. CASE 함수를 사용하여 염색체 1부터 9까지 앞에 '0'을 추가하고 'chr' 프리픽스를 삭제합니다.
#standardSQL
SELECT
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END AS chromosome,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
chromosome
ORDER BY
chromosome
쿼리는 올바른 결과를 반환합니다.
| 행 | 염색체 | number_of_variant_rows |
|---|---|---|
| 1 | 01 | 615000 |
| 2 | 02 | 646401 |
| 3 | 03 | 542315 |
| 4 | 04 | 578600 |
| 5 | 05 | 496202 |
| ... | ... | ... |
이 쿼리는 오류를 반환하는 대신 염색체 X, Y, M에 NULL을 반환하는 SAFE_CAST 함수를 사용합니다.
출력을 마지막으로 개선하기 위해 이를 chromosome 별칭으로 설정하는 대신 reference_name 열을 다시 표시합니다. 이렇게 하려면 CASE 절을 ORDER BY 함수로 이동합니다.
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END
마지막 쿼리는 염색체당 변이 수 계산에 나오는 쿼리와 동일합니다.
사용자 정의 함수 작성
BigQuery는 사용자 정의 함수를 지원합니다. 사용자 정의 함수를 사용하면 다른 SQL 표현식 또는 자바스크립트와 같은 다른 프로그래밍 언어를 사용하여 함수를 만들 수 있습니다.
쿼리 단축의 예시는 복잡한 쿼리를 작성하는 방법을 보여주지만 쿼리가 지나치게 복잡합니다.
다음 쿼리는 CASE 논리를 함수로 이동하여 쿼리를 보다 간결하게 만드는 방법을 보여줍니다.
#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
RETURNS STRING AS (
-- Remove the leading "chr" (if any) in the reference_name
-- If the chromosome is 1 - 9, prepend a "0" since
-- "2" sorts after "10", but "02" sorts before "10".
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END
);
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY SortableChromosome(reference_name)
다음 쿼리는 쿼리를 보다 간결하게 만드는 방법을 보여 주지만 자바스크립트에서 정의 된 함수를 사용합니다.
#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
RETURNS STRING LANGUAGE js AS """
// Remove the leading "chr" (if any) in the reference_name
var chr = reference_name.replace(/^chr/, '');
// If the chromosome is 1 - 9, prepend a "0" since
// "2" sorts after "10", but "02" sorts before "10".
if (chr.length == 1 && '123456789'.indexOf(chr) >= 0) {
return '0' + chr;
}
return chr;
""";
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY SortableChromosome(reference_name)
두 쿼리 모두 올바른 결과를 반환하고 논리가 보다 간결해집니다.
쿼리 성능 향상 및 비용 절감
BigQuery 가격은 쿼리 하나에서 처리된 바이트 수를 기준으로 합니다. 처리되는 데이터의 양이 줄어들면 쿼리 성능이 향상됩니다. BigQuery는 쿼리가 시작된 이후 경과한 시간과 쿼리가 처리한 바이트 수에 대한 데이터를 제공합니다. 쿼리 최적화에 대해서는 BigQuery 쿼리 계획 설명을 참조하세요.
테이블의 변이 추출 수 계산과 같이 이 페이지에 나오는 일부 예는 쿼리를 작성하는 다양한 방법을 보여줍니다. 어떤 쿼리 방법이 가장 적합한지 확인하려면 여러 쿼리의 기간을 검토하고 처리하는 데이터의 바이트 수를 확인합니다.
삭제
튜토리얼을 완료한 후에는 만든 리소스를 삭제하여 할당량 사용을 중지하고 요금이 청구되지 않도록 할 수 있습니다. 다음 섹션은 이러한 리소스를 삭제하거나 사용 중지하는 방법을 설명합니다.
비용이 청구되지 않도록 하는 가장 쉬운 방법은 튜토리얼에서 만든 프로젝트를 삭제하는 것입니다.
프로젝트를 삭제하는 방법은 다음과 같습니다.
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.