언제 Google BigQuery를 사용하는 것이 합리적입니까?

경력을 쌓는 동안 다양한 종류의 보고서와 차트에 대해 복잡한 분석 쿼리를 작성해야 했습니다. 대부분 날짜, 주, 분기 등으로 집계된 데이터를 표시하는 차트였습니다. 일반적으로 이러한 보고서는 고객이 추세를 식별하고 비즈니스가 높은 수준에서 수행되는 방식을 설명하는 데 도움이 되도록 작성됩니다. 하지만 데이터 과학자와 엔지니어가 빅 데이터 세트를 기반으로 훨씬 더 광범위한 보고서를 생성해야 하는 경우에는 어떻게 될까요?

보고서가 작은 데이터 집합을 기반으로 하는 경우 관계형 데이터베이스 아래에 SQL 쿼리를 작성하여 작업을 해결할 수 있습니다. 이 단계에서는 쿼리 작성의 기본 사항과 쿼리를 더 빠르고 효율적으로 만드는 방법을 아는 것이 중요합니다. 그러나 때때로 보고서는 더 큰 데이터 집합(예: 테이블의 수백만 개 이상의 행)에 의존하지만 보고서는 입력 변수(매개변수)에 의존하지 않거나 값의 수가 매우 적다는 것을 알 수 있습니다. 이러한 시나리오에서는 SQL 쿼리가 느려질 수 있으므로 사용자가 쿼리가 실행될 때까지 기다리는 것은 최적이 아닙니다. 이러한 경우 가장 일반적인 방법은 클라이언트가 보고서를 요청하기 전에 미리 쿼리를 실행하는 것입니다.

또한 클라이언트가 실시간으로 쿼리를 실행하는 대신 캐시에서 데이터를 가져올 수 있도록 일부 캐싱 기능을 구현해야 합니다. 이 접근 방식은 실시간 데이터를 표시할 필요가 없다면 완벽하게 작동합니다. 한 시간 또는 하루 전에 계산된 데이터를 표시할 수 있습니다. 따라서 실제 보고서/차트는 실시간 데이터가 아닌 캐시된 데이터를 사용하여 표시됩니다.

Google BigQuery로 전환

제약 산업에서 분석 프로젝트를 진행하는 동안 우편번호와 약품 이름을 입력 매개변수로 사용하는 차트가 필요했습니다. 나는 또한 미국의 특정 지역에서 약물 간의 비교를 보여줘야 했습니다.

분석 쿼리는 매우 복잡했으며 Postgres 서버(16GB RAM의 쿼드 코어 CPU)에서 약 50분 동안 실행되었습니다. 쿼리가 우편번호와 약물을 입력 매개변수로 사용하기 때문에 미리 실행하고 결과를 캐시할 수 없었고 수천 개의 조합이 있었고 어느 ​​클라이언트가 선택할지 예측할 수 없었습니다.

모든 입력 매개변수 조합을 실행하려고 해도 데이터베이스가 충돌했을 가능성이 큽니다. 따라서 다른 접근 방식을 선택하고 사용하기 쉬운 솔루션을 선택할 때였습니다. 이 차트는 클라이언트에게 중요했지만 클라이언트는 아키텍처를 크게 변경하거나 다른 DB로 완전히 마이그레이션할 준비가 되어 있지 않았습니다.

그 특정 프로젝트에서 우리는 몇 가지 다른 접근 방식을 시도했습니다.

• 서버의 수직 확장(Postgres 서버에 RAM 및 CPU 추가)
• Amazon Redshift 및 기타와 같은 대체 DB 사용.
• 우리는 또한 NoSQL 솔루션을 연구했지만 대부분은 상당히 복잡하고 아키텍처에 많은 변경이 필요합니다.

마지막으로 Google BigQuery를 시도했습니다. 그것은 우리의 기대를 충족시켰고 클라이언트가 승인하기를 꺼릴 큰 변경 없이 작업을 완료할 수 있게 해주었습니다. 그러나 Google BigQuery는 무엇이며 어떻게 작동합니까?

BigQuery는 대규모 데이터 집합에서 복잡한 분석 SQL 기반 쿼리를 실행할 수 있는 REST 기반 웹 서비스입니다. BigQuery에 데이터를 업로드하고 Postgres와 동일한 쿼리를 실행한 후(구문은 매우 유사함) 쿼리가 훨씬 더 빠르게 실행되었고 완료하는 데 약 1분이 걸렸습니다. 결국 다른 서비스를 사용하는 것만으로도 성능이 50배 향상되었습니다. 다른 DB가 동일한 성능 향상을 제공하지 않았다는 점은 주목할 가치가 있습니다. 관대하고 단순히 가깝지 않다고 말합시다. 솔직히 말해서 BigQuery가 제공하는 성능 향상에 깊은 인상을 받았습니다. 수치가 우리 모두가 기대했던 것보다 더 좋았기 때문입니다.

그럼에도 불구하고 나는 BigQuery를 세계 최고의 데이터베이스 솔루션으로 광고하지 않을 것입니다. 우리 프로젝트에서는 잘 작동했지만 하루에 테이블의 제한된 수의 업데이트, 요청당 데이터 크기 제한 등과 같은 많은 제한 사항이 있습니다. BigQuery는 관계형 데이터베이스를 대체하는 데 사용할 수 없으며 단순한 CRUD 작업 및 쿼리가 아닌 분석 쿼리 실행을 지향한다는 점을 이해해야 합니다.

이 기사에서는 실제 사용 사례 시나리오에서 Postgres(내가 가장 좋아하는 관계형 데이터베이스)와 BigQuery를 사용하여 비교하려고 합니다. 또한 그 과정에서 몇 가지 제안, 즉 BigQuery를 사용하는 것이 실제로 적절한 시점에 대한 제 의견을 제시하겠습니다.

샘플 데이터

Postgres와 Google BigQuery를 비교하기 위해 국가, 연령, 연도 및 성별로 그룹화된 각 국가의 공개 인구 통계 정보를 가져왔습니다(이 링크에서 동일한 데이터를 다운로드할 수 있음).

4개의 테이블에 데이터를 추가했습니다.

1. populations
2. locations
3. age_groups
4. populations_aggregated

마지막 테이블은 이전 세 테이블에서 집계된 데이터입니다. 다음은 DB 스키마입니다.

내가 만든 populations 테이블에는 690만 개 이상의 행이 있습니다. 너무 많지는 않지만 내 테스트에는 충분했습니다.

샘플 데이터를 기반으로 실제 분석 보고서 및 차트를 작성하는 데 사용할 수 있는 쿼리를 작성하려고 했습니다. 그래서 다음 보고서에 대한 쿼리를 준비했습니다.

1. 연도별로 집계한 미국 인구.
2. 가장 큰 국가에서 시작하여 모든 국가의 2019년 인구.
3. 매년 상위 5개 "가장 오래된" 국가. 가장 오래된 국가는 전체 인구 중 60세 이상 인구의 비율이 가장 높은 국가를 나타냅니다. 쿼리는 매년 5개의 결과를 제공해야 합니다.
4. 연도별로 집계한 상위 5개국으로, 남녀 인구의 차이가 가장 큽니다.
5. "가장 오래된" 국가부터 "가장 젊은" 국가까지 매년 국가별 중앙값(평균) 연령을 구합니다.
6. 매년 상위 5개 "죽어가는" 국가를 찾으십시오. "죽음"은 인구가 감소하고 있는 국가를 의미합니다(인구 감소가 가장 높음).

쿼리 #1, #2, #6은 매우 쉽고 간단하지만 쿼리 #3, #4, #5는 작성하기가 그리 쉽지 않았습니다. 적어도 저에게는 그렇습니다. 저는 백엔드 엔지니어이고 복잡한 SQL 쿼리를 작성하는 것은 제 전문 분야가 아니므로 SQL 경험이 더 많은 사람이 더 똑똑한 쿼리를 작성할 수 있습니다. 그러나 현재로서는 Postgres와 BigQuery가 동일한 데이터로 동일한 쿼리를 어떻게 처리하는지 확인해야 합니다.

총 24개의 쿼리를 만들었습니다.

• 6은 집계되지 않은 테이블을 사용하는 Postgres DB의 경우( populations , locations , age_groups )
• 6, populations_aggregated 테이블을 사용하는 Postgres DB용
• 집계 및 비 집계 테이블을 사용하는 BigQuery에 대한 6+6 쿼리

단순(#1) 쿼리와 복잡한 #5 쿼리의 복잡성을 이해할 수 있도록 집계된 데이터에 대한 BigQuery 쿼리 #1과 #5를 공유하겠습니다.

연도별로 집계된 미국 인구 쿼리:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

가장 오래된 것부터 가장 어린 것 순으로 정렬된 매년 국가별 중간 연령 쿼리:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

참고: 내 bitbucket 저장소에서 모든 쿼리를 찾을 수 있습니다(링크는 기사 끝에 있음).

시험 결과

쿼리를 실행하기 위해 두 개의 다른 Postgres 서버를 사용했습니다. 첫 번째는 1개의 CPU 코어와 4GB RAM이 있으며 SSD 드라이브가 지원됩니다. 두 번째 서버에는 16개의 CPU 코어, 64GB RAM이 있으며 SSD 드라이브도 사용하고 있었습니다(두 번째 서버는 CPU 및 RAM 잠재력의 16배).

또한 테스트를 실행하는 동안 데이터베이스에 로드가 없었음을 유의하십시오. 쿼리를 실행하기 위해서만 만들었습니다. 실제 상황에서는 다른 쿼리가 동시에 실행될 수 있고 병렬로 실행되는 쿼리가 테이블을 잠글 수 있기 때문에 쿼리가 더 오래 걸립니다. 쿼리 속도를 확인하기 위해 pgAdmin3과 BigQuery 웹 인터페이스를 사용했습니다.

내 테스트에서 다음과 같은 결과를 얻었습니다.

쿼리 #1 및 쿼리 #5에 대한 막대 차트에서 해당 결과를 보여드리겠습니다.

참고: Postgres 데이터베이스는 미국 기반 서버에 있었고 저는 유럽 기반이어서 Postgres에서 데이터를 전송하는 데 추가 지연이 있었습니다.

BigQuery 성능 및 결론

얻은 결과를 바탕으로 다음과 같은 결론을 내렸습니다.

• Postgres를 수직으로 확장하는 경우 16배라도 단일 쿼리를 실행할 때 성능의 10-25%만 제공합니다. 즉, CPU 코어가 1개이고 RAM이 4GB인 Postgres 서버는 CPU 코어가 16개이고 RAM이 64GB인 서버에 필요한 시간과 매우 유사한 시간으로 쿼리를 실행하고 있었습니다. 물론 더 큰 서버는 훨씬 더 큰 데이터 세트를 처리할 수 있지만 쿼리 실행 시간이 크게 향상되지는 않습니다.
• 작은 테이블이 있는 Postgres 조인의 경우( locations 테이블에는 약 400개의 행이 있고 age_groups 에는 100개의 행이 있음) 한 테이블에 있는 집계된 데이터에서 쿼리를 실행하는 것과 비교할 때 큰 차이가 나지 않습니다. 또한 1~2초 동안 실행되는 쿼리의 경우 내부 조인이 있는 쿼리가 더 빠르지만 장기 실행 쿼리의 경우 상황이 다릅니다.
• BigQuery에서는 조인이 있는 상황이 완전히 다릅니다. BigQuery는 조인을 좋아하지 않습니다. 집계된 데이터와 집계되지 않은 데이터를 사용하는 쿼리 간의 시간 차이는 상당히 큽니다(쿼리 #3 및 $5의 경우 약 2초). 즉, BigQuery의 경우 원하는 만큼 하위 쿼리를 수행할 수 있지만 좋은 성능을 위해서는 쿼리가 하나의 테이블을 사용해야 합니다.
• Postgres는 단순 집계 또는 필터링을 사용하거나 작은 데이터 세트를 사용하는 쿼리에 더 빠릅니다. Postgres에서 5초 미만이 소요되는 쿼리가 BigQuery에서 더 느리게 작동한다는 것을 발견했습니다.
• BigQuery는 장기 실행 쿼리에 대해 훨씬 더 나은 성능을 제공합니다. 데이터 세트 크기의 차이가 증가함에 따라 이러한 쿼리를 완료하는 데 걸리는 시간의 차이도 증가합니다.

BigQuery를 사용하는 것이 합리적인 경우

이제 이 기사에서 논의된 핵심 문제로 돌아가 보겠습니다. 실제로 Google BigQuery를 언제 사용해야 할까요? 내 결론에 따르면 다음 조건이 충족될 때 BigQuery를 사용하는 것이 좋습니다.

• 관계형 데이터베이스에서 5초 이상 실행되는 쿼리가 있을 때 사용합니다. BigQuery의 아이디어는 복잡한 분석 쿼리를 실행하는 것입니다. 즉, 단순한 집계 또는 필터링을 수행하는 쿼리를 실행하는 것은 의미가 없습니다. BigQuery는 많은 양의 데이터를 사용하여 작동하는 "무거운" 쿼리에 적합합니다. 데이터세트가 클수록 BigQuery를 사용하여 성능을 얻을 가능성이 높아집니다. 내가 사용한 데이터 세트는 330MB(기가바이트도 아닌 메가바이트)에 불과했습니다.
• BigQuery는 조인을 좋아하지 않으므로 더 나은 실행 시간을 얻으려면 데이터를 하나의 테이블로 병합해야 합니다. BigQuery를 사용하면 쿼리 결과를 새 테이블에 저장할 수 있으므로 새 집계 테이블을 만들려면 모든 데이터를 BigQuery에 업로드하고 모든 데이터를 통합하는 쿼리를 실행하고 새 테이블에 저장하기만 하면 됩니다.
• BigQuery는 캐시가 내장되어 있어 데이터가 자주 변경되지 않고 캐시를 사용하려는 시나리오에 적합합니다. 이것은 무엇을 의미 하는가? 동일한 쿼리를 실행하고 테이블의 데이터가 변경(업데이트)되지 않은 경우 BigQuery는 캐시된 결과만 사용하고 쿼리를 다시 실행하지 않습니다. 또한 BigQuery는 캐시된 쿼리에 대해 비용을 청구하지 않습니다. 참고: 캐시된 쿼리도 결과를 반환하는 데 1-1.2초가 걸립니다.
• 관계형 데이터베이스의 부하를 줄이려는 경우에도 BigQuery를 사용할 수 있습니다. 분석 쿼리는 "무거우며" 관계형 데이터베이스에서 과도하게 사용하면 성능 문제가 발생할 수 있습니다. 따라서 결국에는 서버 확장에 대해 생각해야 할 수 있습니다. 그러나 BigQuery를 사용하면 이러한 실행 중인 쿼리를 타사 서비스로 이동할 수 있으므로 기본 관계형 데이터베이스에 영향을 미치지 않습니다.

마지막으로 실생활에서 BigQuery를 사용하는 방법에 대해 몇 마디 더 하겠습니다. 실제 프로젝트에서 보고서의 데이터는 매주 또는 매월 변경되므로 BigQuery에 수동으로 데이터를 업로드할 수 있었습니다. 그러나 데이터가 자주 변경되는 경우 관계형 데이터베이스와 BigQuery 간의 데이터 동기화가 그렇게 간단하지 않을 수 있으며 이는 명심할 가치가 있는 주의 사항입니다.

연결

여기에서 이 문서에 사용된 샘플 데이터를 찾을 수 있으며 CSV 형식의 쿼리 및 데이터는 여기에서 액세스할 수 있습니다.