효과적인 쿼리문 작성법

한 번만 실행하거나 작은 데이터셋에 적용할 쿼리문은 효율적으로 작성할 필요가 없습니다.

하지만 이와 반대되는 경우, 웹사이트/기업데이터 유저들의 대기 시간을 줄이기 위해 효율적으로 작성해야 합니다.

 

 

대부분의 Database System은 Query Optimizer(쿼리 최적화)를 가지고 있습니다.

이는 당신의 쿼리문을 해석하고 실행할 때 가장 효율적은 방법으로 되도록 해줍니다.

몇몇의 전략은 여전히 비용을 많이 절감할 수 있습니다.

 

 

useful FUNTIONs

• show_amount_of_data_scanned() : 쿼리문에 사용된 데이터양을 보여줌
• show_time_to_run() : 쿼리문 실행에 걸린 시간을 출력

from google.cloud import bigquery
from time import time

client = bigquery.Client()

def show_amount_of_data_scanned(query):
    # dry_run lets us see how much data the query uses without running it
    dry_run_config = bigquery.QueryJobConfig(dry_run=True)
    query_job = client.query(query, job_config=dry_run_config)
    print('Data processed: {} GB'.format(round(query_job.total_bytes_processed / 10**9, 3)))
    
def show_time_to_run(query):
    time_config = bigquery.QueryJobConfig(use_query_cache=False)
    start = time()
    query_result = client.query(query, job_config=time_config).result()
    end = time()
    print('Time to run: {} seconds'.format(round(end-start, 3)))

 

 

전략

1. 필요한 column만 선택하기 - SELECT * FROM

아래 코드는 전체를 볼 경우 2523GB를 봐야했지만

필요한 column만 선택하게 되면 2.4GB만 보면 되어 효율성이 올라간다

 

star_query = "SELECT * FROM `bigquery-public-data.github_repos.contents`"
show_amount_of_data_scanned(star_query)

basic_query = "SELECT size, binary FROM `bigquery-public-data.github_repos.contents`"
show_amount_of_data_scanned(basic_query)

Data processed: 2523.552 GB

Data processed: 2.412 GB

 

2. 적은 양의 데이터를 스캔 

하단의 두 쿼리문은 샌프란시스코의 일방통행 자전거 여행의 평균 소요 시간을 초단위로 계산해준다.

station ID와 station 이름은 일대일 관계이기 때문에 'start_station_id'와 'end_station_id' column는 사용하지 않았다.

오직 station ID column만을 사용했기에 적은 양의 데이터를 스캔할 수 있었다.

 

즉 해당 데이터셋의 고유의 column 간 관계를 잘 살펴보자

 

SELECT MIN(start_station_name) AS start_station_name,
                      MIN(end_station_name) AS end_station_name,
                      AVG(duration_sec) AS avg_duration_sec
                  FROM `bigquery-public-data.san_francisco.bikeshare_trips`
                  WHERE start_station_id != end_station_id 
                  GROUP BY start_station_id, end_station_id
                  LIMIT 10

SELECT start_station_name,
                      end_station_name,
                      AVG(duration_sec) AS avg_duration_sec                  
                  FROM `bigquery-public-data.san_francisco.bikeshare_trips`
                  WHERE start_station_name != end_station_name
                  GROUP BY start_station_name, end_station_name
                  LIMIT 10

Data processed: 0.076 GB

Data processed: 0.06 GB

 

3. 다대다(N:N) JOINs를 피하라

1:1 - 각 테이블 속 각 행은 최소 하나 이상 다른 테이블과 겹친다.

N:1 JOIN - 하나의 테이블 속 각 행은 다른 테이블의 여러 행과 겹친다

N:N JOIN - 행이 너무 많이 생겨버렸다.

 

 

코드로 비교해보자

첫번째 쿼리문은 N:N JOIN이다. 

JOIN의 크기를 줄여 다시 쿼리문을 작성해보니 훨씬 빠른 속도로 돌아간다.

// big_join_query
SELECT repo,
                     COUNT(DISTINCT c.committer.name) as num_committers,
                     COUNT(DISTINCT f.id) AS num_files
                 FROM `bigquery-public-data.github_repos.commits` AS c,
                     UNNEST(c.repo_name) AS repo
                 INNER JOIN `bigquery-public-data.github_repos.files` AS f
                     ON f.repo_name = repo
                 WHERE f.repo_name IN ( 'tensorflow/tensorflow', 'facebook/react', 'twbs/bootstrap', 'apple/swift', 'Microsoft/vscode', 'torvalds/linux')
                 GROUP BY repo
                 ORDER BY repo

// small_join_query
WITH commits AS
                   (
                   SELECT COUNT(DISTINCT committer.name) AS num_committers, repo
                   FROM `bigquery-public-data.github_repos.commits`,
                       UNNEST(repo_name) as repo
                   WHERE repo IN ( 'tensorflow/tensorflow', 'facebook/react', 'twbs/bootstrap', 'apple/swift', 'Microsoft/vscode', 'torvalds/linux')
                   GROUP BY repo
                   ),
                   files AS 
                   (
                   SELECT COUNT(DISTINCT id) AS num_files, repo_name as repo
                   FROM `bigquery-public-data.github_repos.files`
                   WHERE repo_name IN ( 'tensorflow/tensorflow', 'facebook/react', 'twbs/bootstrap', 'apple/swift', 'Microsoft/vscode', 'torvalds/linux')
                   GROUP BY repo
                   )
                   SELECT commits.repo, commits.num_committers, files.num_files
                   FROM commits 
                   INNER JOIN files
                   		ON commits.repo = files.repo
                   ORDER BY repo

Time to run: 1272.855 seconds

Time to run: 4.494 seconds

 

 

 

---

Excercise

 

1) 국제 반려동물 가게에서 근무 중인 당신

아래의 세 쿼리문 중에서 가장 최적하기에 가치 있는 쿼리문은 무엇일까요?

Hint: 

- Do the queries run on big or small datasets?

- Do they need to be run many times, or just once?

- Is the data spread over multiple tables, or contained in just one?

 

1. 배송 부서를 위해 한 소프트웨어 엔지니어가 앱을 만들었습니다. 이 앱은 어떤 아이템들이 배송되어야 하고 어떤 창고(warehouse)의 복도로 가야 그 아이템을 찾을 수 있는지 알려줍니다. 이를 위해 order table, shipments table, warehouseLocation table을 사용해야합니다. 배송 부서 직원들은 이 앱을 통해 어떤 아이템(costume)을 어디로 보내야 하는지 알려고 합니다.
2. 회장이 모든 고객의 리뷰와 불만의 목록을 원합니다. 이는 review table에 저장되어있습니다. 어떤 리뷰는 굉장히 길다는 점에 주의하세요.
3. 강아지 주인은 어느때 보다도 자신의 반려동물을 지키려 합니다. 그래서 엔지니어 부서는 GPS 추적장치와 무선 소통 장치를 탑재한 옷을 만드려 합니다. 이 옷은 이 옷의 좌표를 당신의 데이터베이스로 1초만에 전송합니다. 그럼 사용자들은 그들의 개의 위치(또는 적어도 옷의 위치)를 웹사이트에서 확인하죠. 이를 위해 모든 옷들의 최신 위치를 보여주는 쿼리문을 작성해야 합니다. 이는 CostumeLocations table과 CostumeOwners table에 있습니다.

답은 3번입니다.

그 이유는 초단위로 실행되야 하고 대부분의 데이터를 포함하기 때문입니다. 그리고 반복적으로 실행되죠.

1번은 두번째로 최적화해야하는 쿼리입니다. 반복적으로 실행되면 여러 테이블들의 병합이 포함되기에 효율적으로 작성가능합니다.

2번은 오로지 한 번만 실행하면 되는 쿼리이기에 추가적으로 몇 초 더 걸리거나 몇 원 더 쓴다고 해도 괜찮습니다. 하물며 JOINs를 사용하지 않습니다.

 

1) 쿼리문을 작성해보자

CostumeLocation table : 모든 반려동물 옷의 GPS data를 시간과 함께 보유

CostumeID  table : 각 옷을 구별할 수 있는 식별자(unique identifier)

CostumeOwners table: 각 옷을 소유한 주인의 ID를 OwnerID column에 보유. 이 또한 unique identifier. 한 주인은 여러 종류의 옷을 소유할 수 있으며 한 의상을 여러 명이 소유할 수도 있기에(가족) N:N JOIN.

 

예로 Miezie라는 강아지의 위치를 찾아봅시다.

Miezie 주인 중 한 명(주인의 ID는 MitzieOwnerID)은 자신이 가진 모든 옷들의 최신 위치를 확인합니다. 이는 아래 쿼리문을 통해 얻을 수 있습니다.

 

WITH LocationsAndOwners AS 
(
SELECT * 
FROM CostumeOwners co INNER JOIN CostumeLocations cl
   ON co.CostumeID = cl.CostumeID
),
LastSeen AS
(
SELECT CostumeID, MAX(Timestamp)
FROM LocationsAndOwners
GROUP BY CostumeID
)
SELECT lo.CostumeID, Location 
FROM LocationsAndOwners lo INNER JOIN LastSeen ls 
    ON lo.Timestamp = ls.Timestamp AND lo.CostumeID = ls.CostumeID
WHERE OwnerID = MitzieOwnerID

 

 

이 쿼리문은 많은 양을 병합하려는 단점이 있습니다.

이를 최적화하면 아래와 같이 쿼리문을 새롭게 작성할 수 있습니다.

LocationsAndOwner table이 비효율적이기에 다른 table을 이용해봤습니다.

 

WITH CurrentOwnersCostumes AS
(
SELECT CostumeID 
FROM CostumeOwners 
WHERE OwnerID = MitzieOwnerID
),
OwnersCostumesLocations AS
(
SELECT cc.CostumeID, Timestamp, Location 
FROM CurrentOwnersCostumes cc INNER JOIN CostumeLocations cl
    ON cc.CostumeID = cl.CostumeID
),
LastSeen AS
(
SELECT CostumeID, MAX(Timestamp)
FROM OwnersCostumesLocations
GROUP BY CostumeID
)
SELECT ocl.CostumeID, Location 
FROM OwnersCostumesLocations ocl INNER JOIN LastSeen ls 
    ON ocl.timestamp = ls.timestamp AND ocl.CostumeID = ls.costumeID

 

모든 옷에 대해 JOIN을 하고 계산하는 것보다는

제일 먼저 다른 사용자의 행을 삭제합니다.

이후에 가장 최신의 타임 스탬프를 계산하는 쿼리를 하면,

원래 쿼리보다 99.999% 적은 행으로 작업할 수 있습니다.