갑시다.
나는 상담을 위해 연락한 고객을 위한 데이터 샘플을 준비하기 위한 스크립트를 개발 중이었습니다. 샘플에는 각 파일에 100개의 행이 있으며 55개의 로케일로 분할됩니다. 내 쿼리는 다음과 같습니다
SELECT * FROM `project.dataset.table` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "US" LIMIT 100;
데이터는 “europe-west-4”에 저장되었으며 쿼리 가격은 TB당 6달러입니다 . 그래서 스크립트를 실행하여 다음을 처리했습니다.
매우 비싸다.
스크립트는 JavaScript 모듈 로 작성되었습니다.
// bq-samples.mjs import { BigQuery } from "@google-cloud/bigquery"; import { Parser } from "@json2csv/plainjs"; import makeDir from "make-dir"; import { write } from "./write.mjs"; import { locales } from "./locales.mjs"; import { perf } from "./performance.mjs"; const q = (locale, start, end, limit) => `SELECT * FROM \`project.dataset.table\` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "${locale}" LIMIT ${limit}` async function main() { const timer = perf() const dir = await makeDir('samples') const bigquery = new BigQuery() const csvParser = new Parser({}) try { const jobs = locales.map((locale) => async () => { // get query result from BigQuery const [job] = await bigquery.createQueryJob({ query: q(locale, "2022-12-01", "2023-02-28", 100), }) const [rows] = await job.getQueryResults() // parse rows into csv format const csv = parse(csvParser, rows) // write data into csv files and store in the file system await write(csv, dir, locale, "2022-12-01", "2023-02-28", 100) }) await Promise.all(jobs.map((job) => job())) console.log(`✨ Done in ${timer.stop()} seconds.`) } catch (error) { console.error('❌ Failed to create sample file', error) } } await main()
로케일별로 CSV 형식으로 하나의 샘플 파일을 생성합니다. 프로세스는 간단합니다.
내 쿼리에서 몇 가지 잘못된 작업을 수행한 것으로 나타났습니다. 가격 책정 모델을 다시 살펴보면 비용은 처리하는 데이터의 양에만 관련되어 있음을 알 수 있습니다. 따라서 내 쿼리가 100개의 행을 생성하기에는 너무 많은 데이터를 조회했다는 것이 분명합니다.
이 통찰력을 바탕으로 쿼리를 단계별로 최적화해 보겠습니다.
조금 반직관적이네요. 내 선택 문이 처리하는 데이터 양과 관련이 있는 이유는 무엇입니까? 내가 선택한 열에 관계없이 동일한 리소스와 데이터에서 읽어야 합니다. 그렇죠?
행 기반 데이터베이스에만 적용됩니다.
BigQuery는 실제로 열 형식 데이터베이스 입니다. 이는 열 지향입니다. 즉, 데이터가 열로 구성되어 있음을 의미합니다. BigQuery는 Dremel을 기본 컴퓨팅 엔진으로 사용합니다. Dremel의 콜드 스토리지에서 액티브 스토리지로 데이터가 이동되면 데이터가 트리 구조로 저장됩니다.
각 리프 노드는 Protobuf 형식의 열 중심 "레코드"입니다.
BigQuery에서 각 노드는 VM입니다. 쿼리 실행은 루트 서버(노드)에서 중간 서버를 거쳐 리프 서버로 전파되어 선택한 열을 검색합니다.
개별 열을 선택하도록 쿼리를 수정할 수 있습니다.
SELECT session_info_1, session_info_2, session_info_3, user_info_1, user_info_2, user_info_3, query_info_1, query_info_2, query_info_3, impression_info_1, impression_info_2, impression_info_3, ts FROM `project.dataset.table` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "US" LIMIT 100;
모든 열을 명시적으로 선택하는 것만으로도 처리되는 데이터를 3.08TB에서 2.94TB로 줄일 수 있었습니다. 이는 100GB 감소 입니다.
Google Cloud에서는 날짜별로 테이블을 분할할 것을 권장합니다. 이를 통해 데이터의 하위 집합만 쿼리할 수 있습니다.
쿼리를 더욱 최적화하려면 테이블이 "ts" 열로 분할되어 있으므로 where 문의 날짜 범위를 좁힐 수 있습니다.
SELECT session_info_1, session_info_2, session_info_3, user_info_1, user_info_2, user_info_3, query_info_1, query_info_2, query_info_3, impression_info_1, impression_info_2, impression_info_3, ts FROM `project.dataset.table` WHERE ts = TIMESTAMP("2022-12-01") AND locale = "US" LIMIT 100;
기간을 3개월이 아닌 하루로 좁혔습니다. 처리된 데이터를 37.43GB 로 줄일 수 있었습니다. 이는 원래 쿼리의 일부일 뿐입니다.
비용을 줄이는 또 다른 방법은 쿼리하는 데이터세트를 줄이는 것입니다. BigQuery는 쿼리 결과를 더 작은 데이터 세트로 저장할 수 있는 대상 테이블을 제공합니다. 대상 테이블은 임시 테이블과 영구 테이블의 두 가지 형태로 제공됩니다.
임시 테이블은 수명이 있고 공유 및 쿼리되도록 설계되지 않았기 때문에 쿼리 결과를 구체화하기 위해 영구 대상 테이블을 만들었습니다.
// bq-samples.mjs const dataset = bigquery.dataset('materialized_dataset') const materialzedTable = dataset.table('materialized_table') // ... const [job] = await bigquery.createQueryJob({ query: q(locale, '2022-12-01', '2023-02-28', 100), destination: materialzedTable, })
쿼리 결과는 대상 테이블에 저장됩니다. 향후 쿼리에 대한 참조 역할을 합니다. 대상 테이블에서 쿼리가 가능할 때마다 BigQuery는 테이블의 데이터를 처리합니다. 우리가 조회하는 데이터 크기가 크게 줄어들 것입니다.
BigQuery에서 비용을 절감하는 것은 매우 흥미로운 연구입니다. 세 가지 간단한 단계만 거치면 됩니다:
처리되는 데이터 크기를 3TB에서 37.5GB로 줄일 수 있었습니다. 총 비용이 $3,000에서 $30로 크게 절감됩니다.
BigQuery 아키텍처에 대해 자세히 알아보고 싶다면 다음 참고 자료를 참조하세요.
Google Cloud 문서에서 BigQuery 비용 최적화 에 대해 자세히 알아볼 수 있습니다.
사례 연구에 협력하고 BigQuery 아키텍처를 이해하는 데 도움이 되는 귀중한 통찰력을 제공한 Abu Nashir 에게 특별히 감사드립니다.
연결하고 싶으신가요?
이 기사는 원래 Daw-Chih 웹사이트 에 게시되었습니다.