.png&blockId=29b97262-fa1c-41ac-8961-370b3d197e08)
목적
구글 앱스 스크립트의 JDBC 서비스를 활용해서 오라클 DB에서 데이터를 읽어오는 방법을 이해합니다.
앱스 스크립트와 JDBC 서비스
Apps Script는 표준 자바 데이터베이스 연결 기술의 래퍼인 JDBC 서비스를 통해 외부 데이터베이스에 연결할 수 있습니다. JDBC 서비스는 Google Cloud SQL MySQL, MySQL, Microsoft SQL Server, Oracle 데이터베이스를 지원합니다.
JDBC로 외부 데이터베이스를 업데이트하려면 스크립트가 데이터베이스 연결을 연 다음 SQL 문을 전송하여 변경해야 합니다.
오라클 DB의 데이터를 빅쿼리로 보내야 하는 이유
회사 자체 서버로 오라클 DB를 사용하는 경우, 데이터 분석 또는 BI 시각화 구축을 위해서 데이터 스튜디오를 사용하려고 하면 구글에서 제공하는 커넥터를 사용할 수 없는 불편함이 있습니다. Skyvia 와 같은 서비스를 이용할 수는 있지만, 비용도 들고 중간에서 데이터를 가공할 수도 없어서 데이터 스튜디오 구축이 더 불편할 때가 많습니다.
또한 MSSQL, MySQL, PostgreSQL의 경우에는 데이터스튜디오와 바로 연결하는 것이 가능하기는 하지만, 빅쿼리에서 끌어오는 편이 속도가 훨씬 빠릅니다. 직접 연결해서 사용해보신 분들은 빅쿼리와 데이터스튜디오를 연결하는 것을 선호하게 됩니다.
이런 이유로 오라클 DB의 데이터를 분석하기 위해서 먼저 빅쿼리로 데이터를 넣고, 데이터 스튜디오와 연결하는 것이 속도와 가공의 측면에서 좋습니다.
이번 포스팅에서는 앱스 스크립트의 JDBC 서비스로 오라클 DB의 데이터를 불러와서 가공한 뒤에, 빅쿼리 테이블에 넣어주는 방법을 다룹니다.
오라클 DB의 데이터를 불러오는 JDBC 서비스
기본적인 프로세스 3단계로 이루어집니다.
1.
DB와의 커넥션을 오픈
2.
쿼리 & 쿼리 결과 처리
3.
DB와의 커넥션을 클로즈
한 단계씩 살펴보겠습니다.
DB와의 커넥션을 오픈
다음의 코드로 커넥션을 오픈합니다.
const conn = Jdbc.getConnection('jdbc:oracle:thin:@ipaddress:서비스이름', 'USERNAME', 'PASSWORD')
JavaScript
복사
쿼리 & 쿼리 결과 처리
다음의 코드로 쿼리를 보내고 결과를 반환받습니다.
const stmt = conn.createStatement()
const results = stmt.executeQuery('SELECT * FROM 테이블명')
JavaScript
복사
일반적으로 구글 시트 또는 빅쿼리에 입력해주는 형태로 데이터를 얻으려면 결과를 한 번 더 처리해주어야 합니다.
다음과 같이 처리합니다.
쿼리의 결과는 행과 열로 구성되는데, 각 행에 액세스 한 다음 각 열의 값을 가져오는 방식입니다.
결과인 rows는 구글 시트에 바로 입력할 수 있는 배열의 형태로 생성됩니다.
const numCols = results.getMetaData().getColumnCount();
let rows = [];
while (results.next()) {
let arr = [];
for (let col = 0; col < numCols; col++) {
arr.push(results.getString(col + 1));
}
rows.push(arr);
}
JavaScript
복사
DB와의 커넥션을 클로즈
쿼리 작업을 완료한 뒤에는 커넥션을 닫아줍니다.
results.close();
stmt.close();
JavaScript
복사
실무 예제
오라클 DB에서 데이터를 불러와서 구글 시트에 표시하고, 빅쿼리에도 입력해주어 데이터 스튜디오에서 활용하는 다음과 같은 예제를 생각해보겠습니다. 주석을 따라 보세요.
실무 환경이 다르기 때문에 상세하게는 이해하기 어려우실 수도 있지만, 원칙만 파악하시면 상황에 따라 적용하실 수 있을 것입니다.
개괄적인 프로세스는 다음과 같습니다.
1.
오라클DB의 ptTable에서 수량과 중량이 0이 아닌 데이터를 가져와서 일자 부분을 가공합니다.
2.
구글 시트와 빅쿼리에 가공이 완료된 데이터를 bq_pt에 입력합니다.
3.
빅쿼리 상의 다른 테이블 meat_id와 bp_pt를 조인하여 결과를 bq_inventory에 입력합니다.
function readPt() {
// 구글 시트의 pt 탭의 콘텐츠를 클리어하여 준비합니다.
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ss_data = ss.getSheetByName('pt')
ss_data.getRange('A2:AN3000').clearContent()
SpreadsheetApp.flush()
// 빅쿼리의 job 을 정의합니다.
const projectId = 'projectId'
const datasetId = 'Datastudio'
const tableId = 'bq_pt'
const job = {
configuration: {
load: {
destinationTable: {
projectId: projectId,
datasetId: datasetId,
tableId: tableId
},
skipLeadingRows: 0,
writeDisposition: 'WRITE_TRUNCATE'
}
}
}
// 오라클DB와 커넥션을 오픈하고 쿼리를 실행해서 결과를 반환받습니다.
try {
const conn = Jdbc.getConnection('jdbc:oracle:thin:@ipaddress:서비스이름', 'USERNAME', 'PASSWORD')
const start = new Date()
Logger.log(start)
const stmt = conn.createStatement()
let query = `SELECT * FROM ptTable WHERE (qty <> 0 OR wgt <> 0) AND status LIKE '%완료' `
const results = stmt.executeQuery(query);
const numCols = results.getMetaData().getColumnCount();
let rows = [];
while (results.next()) {
let arr = [];
for (let col = 0; col < numCols; col++) {
// 결과 중 일부 열은 오라클DB의 날짜 형식이기 때문에 빅쿼리에서 바로 인식하지 못할 수 있습니다. 이 경우에는 값을 조작하여 준비합니다.
if (col == 5 || col == 21 || col == 22 || col == 39) {
try {
let dateString = results.getString(col + 1)
let dateValue = `${dateString.substring(0, 4)}-${dateString.substring(5, 7)}-${dateString.substring(8, 10)}`
arr.push(dateValue);
} catch (err) {
arr.push(null)
}
} else {
arr.push(results.getString(col + 1));
}
}
rows.push(arr);
}
// DB와의 커넥션을 닫습니다.
results.close();
stmt.close();
// lastrow_of_col은 탭의 특정 열의 마지막 행을 구하는 함수입니다. 1행의 라벨 아래쪽에 데이터를 붙여넣습니다.
let lastrow = lastrow_of_col(ss_data, 'A')
ss_data.getRange(lastrow + 1, 1, rows.length, rows[0].length).setValues(rows)
// 빅쿼리에 데이터를 넣기 위해서 blob을 생성합니다.
let dataCSV = rows.join('\n')
let blob = Utilities.newBlob(dataCSV, 'application/octet-stream')
// 빅쿼리에 데이터를 추가하는 job을 실행합니다.
try {
BigQuery.Jobs.insert(job, projectId, blob)
Logger.log('데이터를 테이블에 추가합니다.')
Utilities.sleep(10000)
// job이 완료될 때까지, 10초를 대기한 뒤에 joinPt 함수를 실행합니다.
joinPt()
} catch (err) {
Logger.log(err)
}
// 경과 시간 로그를 표시합니다.
const end = new Date();
Logger.log('경과 시간: %sms', end - start);
} catch (err) {
Logger.log('오류발생 %s', err.message);
}
}
// joinPt는 방금 위에서 입력한 bq_pt 테이블과 meat_id 테이블을 조인하여 bq_inventory 로 만들어주는 함수입니다.
function joinPt() {
const projectId = 'projectId '
const datasetId = 'Datastudio'
const tableId = 'bq_pt'
const joinTableId = 'bq_inventory'
const meatTableId = 'meat_id'
// 빅쿼리 상의 meat_id 테이블은 구글 드라이브를 연결해둔 것이기 때문에 드라이브 액세스 권한이 필요합니다.
// 아래와 같이 작성함으로 처음 코드를 실행할 때 Drive 서비스를 추가할 수 있습니다. 그렇지 않으면 오류를 표시합니다.
DriveApp.getRootFolder()
// 빅쿼리 상에서 조인 쿼리를 실행합니다. 그 결과는 joinTableId(bq_inventory)에 저장합니다.
const job = {
configuration: {
query: {
query: `
(SELECT L.*, R.bl_no FROM ${datasetId}.${tableId} L
LEFT OUTER JOIN
(SELECT meat_id, bl_no from ${datasetId}.${meatTableId}) R
ON L.hand_meat_id = R.meat_id WHERE pa_no is not null)`,
destinationTable: {
projectId: projectId,
datasetId: datasetId,
tableId: joinTableId
},
writeDisposition: 'WRITE_TRUNCATE'
}
}
}
try {
BigQuery.Jobs.insert(job, projectId)
Logger.log(`이력번호데이터와 조인을 완료하였습니다.`)
} catch (err) {
Logger.log(err)
}
}
JavaScript
복사
이제 bq_inventory 를 데이터스튜디오와 연결하여 빠른 속도로 재고를 조회할 수 있습니다.
readPt() 를 트리거로 예약해둔다면 정기적으로 업데이트된 재고를 조회할 수 있게 됩니다.
