SQL 중급: 7일차

구글 BigQuery 작성하여 추출해 둔 데이터를
구글 스프레드시트로 연결하는 방법은 무엇일까?

 

쿼리 결과가 나온 화면에서 우측 상단에 <데이터 탐색> 선택

Sheets로 탐색을 누르면 구글 스프레드시트로 추출해 둔 데이터가 넘어간다.

 

그렇다면 왜 구글 BigQuery로 데이터를 정제한 후
구글 스프레드시트에 연결해서 시각화를 할까?

 

 

크게 2가지 이유가 있는 것 같다.

 

1. 구글 스프레드시트도 엑셀처럼 10만행까지만 확인 가능하기 때문에,
수백만 개의 데이터행 중에서 원하는 기간/조건을 가진 데이터만 BigQuery로 정제해서 추출하고
구글 스프레드시트로 넘어가는 것이다.

 

2. 구글 스프레드시트가 엑셀과 다름없이 때문에, 추가적으로 피벗테이블을 만들고 시각화하기 쉽다.

 

---

 

쿼리를 반복 수행할 수 있도록 설정하는 방법은 무엇인가?

 

 

구글 BigQuery와 연결된 시트에서 왼쪽 하단의 동그란 아이콘에 마우스 오버를 하면

새로고침 아이콘과 함께 바 같은 형태의 영역이 생긴다.

 

오른쪽 끝의 점 세개 토글을 선택 > 새로고침 옵션을 선택하면

오른쪽 영역에 새로운 탭이 생기고, 하단에 예약된 새로고침에서 지금 설정을 선택한다.

 

 

원하는 반복 주기를 설정하고 저장하면

내가 원하는 일자마다, 시간마다 쿼리가 반복 수행 되면서 쿼리 결과를 업데이트해서 구글 스프레드시트로 가져온다.

 

 

쿼리 반복 수행 기능은 왜 필요한가?

 

 

예를 들어 주간 보고를 해야 한다고 가정해 보자.

 

매일 확인하는 데이터도 똑같고 보고해야하는 양식도 정해져 있다면,

데이터를 정리할 때마다 쿼리 안에 있는 날짜를 바꿔서 수행하면 매우 귀찮은 반복 작업이 된다.

 

위와 같이 설정함으로 인해서

이미 내가 출근하였을 때 추출 완료된 데이터를 기반으로 주간 보고 데이터 입력을 자동화할 수 있는 것이다.

 

---

 

BigQuery와 연결된 구글 스프레드시트에서 매개변수를 설정하는 방법은 무엇인가?

 

 

똑같이 BigQuery와 연결된 시트 왼쪽 하단의 동그란 아이콘에 마우스 오버하면 보이는 바에서

점 세개 토글 선택, 연결 설정 선택한다.

 

 

 

그러면 아래와 같은 팝업이 뜨고, 작성한 쿼리 오른쪽에 매개변수 탭이 보인다.

추가를 눌러서 원하는 만큼 여러 개의 매개변수를 추가할 수 있다.

 

새로운 시트에 적어둔 날짜를 선택한다.

 

 

 

그리고 배개변수 이름을 REPORT_DATE라고 지은 후 저장하면

쿼리 편집기 가장 아래쪽에 @REPORT_DATE가 뜨는데, 이건 지운다.

 

그리고 쿼리문 자체에 날짜를 설정하는 문구를 아래와 같이 작성한다.

WHERE
  eventdate = @REPORT_DATE

 

 

결과적으로 아래와 같이 작성하면 된다.

SELECT * EXCEPT (name, screen),

  CASE

  WHEN name_screen = "login-home" THEN 1

  WHEN name_screen = "play_01-menu" THEN 2

  WHEN name_screen = "play_02-bag" THEN 3

  WHEN name_screen = "logout-home" THEN 4

  ELSE NULL

  END AS screen_number

FROM (

  SELECT *,

    CONCAT(name, "-", screen) AS name_screen

  FROM test.concat

-- WHERE screen_number IS NOT NULL

) AS subquery

-- WHERE screen_number IS NOT NULL

WHERE

  eventdate = @REPORT_DATE

ORDER BY eventdate, user_id, screen_number

 

 

참고로 매개변수는 여러개를 추가할 수 있다.

시작일 / 종료일을 각각 적어두고, 매개변수를 각각 추가하면 된다.

 

 

 

 

왜 매개변수를 설정하는가?

 

 

앞서 설정했던 쿼리 반복 수행 스케쥴링과 동일하다.

 

매번 BigQuery 편집기를 열어서 날짜를 수정하는 것이 아니라,

매개변수 시트에 작성된 날짜만 입력을 바꿔서 동적으로 데이터를 갱신할 수 있도록 편리하게 구성해 두는 것이다.

 

---

 

이후 원하는대로 추출된 데이터를 기반으로 피벗 테이블을 만들거나 시각화 차트를 만드는 것은

엑셀의 기능과 다르지 않으므로 생략한다.

 

---

 

쿼리를 계속 반복적으로 실행하면 비용이 많이 나올 텐데, 이것에 대한 규칙이나 처리 방식이 있을까?

 

 

현업에서는 Airflow를 사용한다고 하고

자주 사용할 것 같은 데이터는 BigQuery 테이블로 저장한 후, 저장한 데이터를 불러오는 방식으로 사용한다고 한다.

 

이 부분에 대해서는 내가 정확하게 이해하지는 못했지만,

예를 들어 내가 주간 보고를 할 때, 현재까지 누적된 매출을 알아야 한다고 가정해 보자.

 

그럼 1년은 52주이므로, 일자별(행) 저장된 데이터를 SELECT 해서

12월에는 거의 40주차의 매출을 모두 읽어와야 하는데, 이 부분에서 비용이 많이 발생할 수 있다.

 

그러므로 "연간 누적 데이터"라는 테이블을 만들고

매주 주간 보고를 할 때마다 매출을 포함해서 필요한 데이터를 쭉 쌓은 뒤에, 집계하는 방식이라고 이해를 했다.

 

정확하지 않을 수 있으니까, 사내 스터디에 공유하고 논의해 보아야겠다.