CODING.빅쿼리와 앱스크립트 연동하여 스케줄 걸기

안녕하세요, 쌍심지입니다.

주말마다 기존에 사용하던 블로그에서 이사작업을 하고있습니다.

빨리 새 블로그 조회수가 더 많아지는 날이 왔으면 좋겠네요.

그때까지 파이팅...

오늘은 Google Apps Script를 사용해서 BigQuery에 스케쥴 거는 방법을 공유하려 합니다.

제가 처음 빅쿼리와 앱스크립트를 연동했던 것이 18년 6월 경이었는데요.

기억하기로 구글에서는 18년 9월경 부로 스케쥴(쿼리 예약) 기능을 제공하였습니다.

(빅쿼리 쿼리예약 기능: https://cloud.google.com/bigquery/docs/scheduling-queries?authuser=2&hl=ko)

헌데 권한 문제로 (ADMIN 권한 필요) 저는 해당 기능을 사용하지 못했고

현재까지도 앱스크립트를 연동하는 펴법을 사용해서 스케쥴을 걸고 있습니다. (개꿀ㅎ)

본격적으로 상세한 방법을 소개해보도록 하겠습니다.

0. 준비

빅쿼리 사용하는 분이시라면 상단에 Schedule Query 버튼 자주 보셨을텐데요.

ADMIN 권한이 있으신 분이시라면 N가지 정보 입력 후에 스케쥴을 등록하셨겠지만,

권한이 없는 분들은 저와 동일한 에러메시지를 발견하셨을겁니다.

그렇다면 우리는 좌절하지 말고 권한 문제를 우회하여 쿼리를 등록하는 방법을 알아봅시다.

1. 구글 앱스크립트 파일 생성

구글 앱스크립트 파일을 생성합니다. (경로를 모르시는 분들은 이 링크로: https://script.google.com/home/start)

2. Google BigQuery API 추가

Resource > Advanced Google Services > BigQuery API

상기의 경로에 들어가셔서 not used를 use로 설정 변경하시면 됩니다.

3. 코드 작성

그리고는 코드를 작성하시면 됩니다.

하기 코드를 그대로 사용하실 것이시라면 "YOUR-"으로 시작하는 부분 수정을 꼭 해주셔야 합니다.

함수에 대해 간단히 설명을 드릴게요.

A. main 함수: 쿼리 검증(누락체크) -> 쿼리 실행 -> 메일 전송

B. checkResult 함수: 쿼리 검증(누락 체크용)

저의 경우 한개의 value로만 값을 체크할 수 있도록 해서 하기와 같이 코드 작성하였는데

다른 방법이 필요하신 분들은 경우에 맞게 수정하시면 됩니다.

C. runQuery 함수; 쿼리 실행

쿼리를 실행하고, 결과를 저장하는 코드입니다.

D. sendcheckmail: checkresult의 결과에 따라 성공/실패 메일을 전송합니다.

function main() {
  var checkcheck = checkResult()
  runQuery();
  sendcheckmail(checkcheck) 
  
}


// Function to check Result
function checkResult() {
  var projectId = "YOUR-PROJECT-ID"; 
  var request = {
    "query" : "YOUR-QUERY ",      
    "useLegacySql" : false
  } 
  
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;
  
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }
  
  var checkcheck = queryResults.rows[0].f[0].v;
  return checkcheck;
  
}


// Function to Run Query
function runQuery() {
  var configuration = {
    "query": {
    "useQueryCache": false, 
    "destinationTable": { 
          "projectId": "YOUR-PROJECT-ID",
          "datasetId": "YOUR-DATASET",
          "tableId": "YOUR-DESTINATION-TABLE" 
        },
    "writeDisposition": "WRITE_APPEND", 
    "createDisposition": "CREATE_IF_NEEDED",
    "allowLargeResults": true,
    "useLegacySql": false,  
    "query": "YOUR-QUERY-HERE`"   
    }
    };
   
  var job = {
    "configuration": configuration
  };

  // 
  var jobResult = BigQuery.Jobs.insert(job, "YOUR-PROJECT-ID");

}


// SEND ALERT MAIL
function sendcheckmail(checkcheck) {
  if (checkcheck > 0){
    MailApp.sendEmail('YOUR-EMAIL-ADDRESS', 'MAIL-TITLE(SCCESS)','MAIL-CONTENT');   
  }
  else {
    MailApp.sendEmail( 'YOUR-EMAIL-ADDRESS', 'MAIL-TITLE(FAIL)','MAIL-CONTENT');      
  }

}

코드 작성에 한가지 팁을 드리자면, script 환경에서 긴 쿼리를 작성하는 것이 불편하실 수 있습니다.

저는 뷰를 만들어서 select * from 뷰이름 으로 간단히 해결했으니 참고하시면 좋을 것 같아요.

4. 스케쥴 걸기

실행함수를 main으로 설정한 후, 시계버튼 클릭 > 트리거 추가 > 시간 설정 > 저장

원하는 시간데에 설정하시면 되나 기존 물리DB와는 달리 정확한 시간 선택이 불가합니다.

시간대 EX) 오전 03:00~ 04:00 으로 설정하셔야 하기에 보수적으로 설정하시는 것을 추천드립니다.

도움이 되셨기를 바랍니다.

간단하게 구현할 수 있는

빅쿼리와 앱스크립트를 연동하여 스케쥴 걸기 끝

눈에 쌍심지를 켜고