반응형
블로그 이미지
개발자로서 현장에서 일하면서 새로 접하는 기술들이나 알게된 정보 등을 정리하기 위한 블로그입니다. 운 좋게 미국에서 큰 회사들의 프로젝트에서 컬설턴트로 일하고 있어서 새로운 기술들을 접할 기회가 많이 있습니다. 미국의 IT 프로젝트에서 사용되는 툴들에 대해 많은 분들과 정보를 공유하고 싶습니다.
솔웅

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

카테고리

[VBA] 엑셀과 Teradata 연동하기

2014. 2. 7. 10:35 | Posted by 솔웅


반응형

지금 참여하는 프로젝트에서는 Oracle 데이터베이스를 Teradata로 바꾸고 있습니다.

그래서 엑셀로 Teradata와 연동해서 현역들의 Report 작업을 도와줄 수 있는 방법이 있지 않을까 해서 그 방법을 좀 Research 하고 있습니다.


좋은 글이 있어서 한번 번역해 봤습니다.



Teradata and MS Excel VBA

How to Get Data from Teradata Directly from Your VBA Excel Application



DB와 Reportings 에 대해 일하는 사람들은 시간을 단축시키는 작업이 가장 필요한 사람들이다. Teradata SQL Macro: A Fast Introduction 글에서 봤듯이 우리 쿼리 안의 fast variable을 대체함으로서 단지 명령 하나로 우리 일을 아주 간단하게 처리할 수 있었다. 만약 이걸 Excel로 하면 어떨까? worksheet에 값들을 copy and paste 해야하고 링크를 만들어야하고 등등 의 일들을 해야 한다. 새로 report를 update 해야 된다면? 이런 작업들을 다시 반복적으로 해야 한다. 또 다시 데이터들이 바뀌면 또 다시 반복해야 하고.
이러한 이유로 VBA와 ODBC 를 사용해서 Teradata로 연결할 수 있으면 아주 편할 것이다. 이러한 반복적인 작업들을 VBA 에게 하라고 시키고 우리는 그 일이 끝나기만을 기다리면 된다. 실제로 이 작업이 어떻게 진행되는지 보자.





우선 VBA 에디터를 열면 몇개의 reference들을 include 해야 한다.



Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library



위의 버전만을 사용해야 되는 것은 아니다. 하지만 이 글에서 사용되는 기능들은 위의 버전들에 최적화 돼 있다.



이 reference들을 include 했으면 몇개의 객체들을 생성해야 한다.



ADODB.Connection
ADODB.Recordset
ADODB.Command



이 connection은 DBMS 와 연결하는데 사용된다. Recordset은 SELECT 쿼리를 통해서 얻은 결과들 처리할 거고 Command 는 sql request를 Teradata에 보낼 것이다.



이제 VBA의 평범한 코딩을 하면 된다. 객체들을 선언하고 초기화 한다.



Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cmdSQLData As ADODB.Command
Set cmdSQLData = New ADODB.Command



Dim 이후의 이름은 여러분들이 원하는 이름 아무거나 사용하면 된다.



그 다음 단계는 Teradata와의 connection을 여는 것이다. 그러려면 아래 방법대로 코딩을 하는 방법도 있다.



cn.Open "Data Source=ODBC_connection_name; Database=db_name; Persist Security Info=True; User ID=your_username; Password=your_password; Session Mode=ANSI;"


일단 connection이 이루어지면 이제 SQL 뭐리를 실행하라고 명령하기만 하면 된다. 이 command는 ADODB.Command 객체를 통해서 보내진다. 아래가 그 예제이다.



Set cmdSQLData.ActiveConnection = cn
' 이 라인은 이 쿼리가 전달될 데이터베이스가 무엇인지를 얘기하는 것이다.
query = "SELECT * FROM table_name;"
' 실행하기를 원하는 쿼리
cmdSQLData.CommandText = query
' 이 쿼리를 command text로 할당한다.
cmdSQLData.CommandType = adCmdText
' 어떤 종류의 command VBA가 실행될지를 알려준다.
cmdSQLData.CommandTimeout = 0
' 여기에는 아무런 timeout도 설정하지 않을 것이다. 우리가 보낸 쿼리가 실행하는데 얼마의 시간이 걸리던지 결과가 나올때까지 기다리게 된다.
Set rs = cmdSQLData.Execute()
' VBA 가 쿼리를 보내서 실행하도록 하고 그 결과값을 받는다.


일단 한번에 한개의 request만 실행될 수 있음을 명심하자. 한번에 한개의 SELECT문이나 한개의 INSERT문 혹은 한개의 DELETE문만 실행될 수 있다. Teradata의 Macro에 대한 이전 글을 읽었다면 아마 여러개의 쿼리를 작동시킬 수 있는 script를 만들 수 있을 것이다. 이것은 EXEC sql의 키워드를 사용해야 한다. 아래에 몇개의 예제를 보여주겠다.



query = "DELETE FROM table_name ALL;"
cmdSQLData.CommandText = query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()

query = "INSERT INTO table_name (" & Sheet5.Range("A" & z).Value & "," & Sheet5.Range("B" & z).Value & ");"
cmdSQLData.CommandText = query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()
z = z + 1

query = "SELECT * FROM SVKCCP_MSI_D.stanicek_Delivery_per_weeks;"
cmdSQLData.CommandText = query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()

query = "EXEC macro_name ('2009-01-01','2009-02-15','2008-01-01','2008-02-15','2009-02-09','2009-02-15','2008-02-11','2008-02-17');"
cmdSQLData.CommandText = query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()



맨 마지막 부분이 어떻게 데이터를 받고 이것을 처리하는지에 대한 부분이다. 우선 한번에 한개의 SELECT 쿼리에 대한 결과를 받을 수 있다는 것을 염두에 두자. 이 의미는 해당 macro가 SELECT * FROM table_name;에서 끝나고 다른 SELECT * FROM table_name은 실행하지 않을 수도 있다는 얘기다. 이 결과 값은 ADODB.Recordset 객체에 전달된다. 여기에 대한 몇가지 유용한 메소드와 프로퍼티들을 소개하겠다. MSDN 문서로 가면 더 많은 정보들을 얻을 수 있을 것이다.



우선 결과들의 어느 지점에 있는지를 알 수 있는 핵심 프로퍼티 두개를 보자.

rs.EOF
' EOF 는 End of File의 약자이다. true이면 받은 결과의 마지막 row에 왔다는 얘기다.

rs.BOF
'BOF는 Begin of File의 약자이다. true이면 받은 결과의 맨 처음에 있다는 얘기다.

아래 경우는 어떤 경우일까?

rs.EOF = True And rs.BOF = True
' EOF도 true이고 BOF도 true라는 얘기는 result에 데이터가 하나도 없다는 얘기다.



그리고 이 프로퍼티들을 아래와 같이 루프문에서 사용할 수 있다.



Do While (rs.EOF = False And rs.BOF = False) '이 루프는 recordset에 데이터가 있을 경우에만 시작한다.


p = rs.GetRows(1)
Sheet1.Range("B" & row).Value = p(0, 0)
Sheet1.Range("C" & row).Value = p(1, 0)
Sheet1.Range("D" & row).Value = p(2, 0)
Sheet1.Range("E" & row).Value = p(3, 0)
Sheet1.Range("F" & row).Value = p(4, 0)
Sheet1.Range("G" & row).Value = p(5, 0)
Sheet1.Range("H" & row).Value = p(6, 0)
Sheet1.Range("I" & row).Value = p(7, 0)
Sheet1.Range("J" & row).Value = p(8, 0)
row = row + 1
Loop



루프로 들어가기 전에 우리가 받은 결과를 제대로 검색하기 위해서는 첫번째 record에 pointer가 위치하도록 해야 한다.
아래 메소드를 사용하면 그 작업을 할 수 있다.



rs.MoveFirst



이건 Do While 루프문이 시작되기 전에 실행하도록 해야 할 것이다.



일단 do while 루프안으로 들어가면 한번에 한개의 row를 받을 것이다. 이것은 GetRows 메소드를 사용해서 이 작업을 할 수 있다.



p = rs.GetRows(1)



이 메소드는 적어도 한개의 값이 있을 경우에 가능하다. 이 값은 몇개의 row들을 받을 것인지를 나타낸다. p는 row들과 field들의 matrix이다.



한번에 한개 이상의 row들을 가질수 있다고 해도 나는 한번에 한개의 row를 처리하는걸 더 선호한다. 하지만 여러분들을 여러분들 방식대로 하면 된다. 배열등을 이용해서 한번에 여러개의 row들을 처리할 수도 있다. GetRows 메소드는 recordset안의 pointer 이동할 것이다. 그래서 다음 record를 읽을 수 있도록 point할 것이다. 위 예제를 보면 알 수 있듯이 p(0,0) 이렇게 배열을 이용하면 첫번째 field를 말하는 거고 두번째 field는 p(1,0)이 될 것이다.



이렇게 하면 우리가 필요한 데이터를 받아서 엑셀파일 어딘가에 copy해 넣을 수 있고 혹은 어뜬 계산을 하던지 해서 관리를 할 수가 있게 된다


일단 DB와의 작업이 끝나면 이 connection을 close 시켜야 한다. 그리고 모든 객체들을 free 시켜야 하고. 이 작업을 위해서는 아래와 같이 처리하면 된다.


cn.Close
Set cn = Nothing
Set rs = Nothing
Set cmdSQLData = Nothing


Additional useful informations



이 글을 마치면서 몇가지 쉽지 않은 작업에 대해서 언급하고 마무리 짓겠다.



여러개의 데이터베이스에 여러개의 connection을 open 할 수 있다. 그리고 여러개의 Recordset과 Command를 만들수도 있고. 이렇게 하면 데이터를 한 데이터베이스에서 다른 데이터베이스로도 옮길 수 있다.



오직 SELECT만이 recordset에 어떤 결과를 보내준다. DELETE나 INSERT INTO 뭐리를 가지고 surf할 생각은 말라.


어떤 쿼리에 에러가 있어서 실행시 에러가 발생했다면. 이 때 우리는 뭔가 잘못 됐다는 것을 알아야 한다.



On Error GoTo ErrorHandler '이 라인을 Sub의 시작부분에 넣자.

ErrorHandler:
If (Len(Err.Description) > 0) Then
    MsgBox (Err.Description)
End If



이 ErrorHandler 부분은 Sub 안의 아무곳에나 넣으면 된다. 만약 Error가 발생하면 VBA는 이 label을 볼것이고 if문 안에 있는 코드를 실행할 것이다. 여기서는 Err.Description을 MsgBox에 표시하도록 해 놨다.



Teradata Macro 에 SELECT 쿼리를 include하는 것이 가능해도 Teradata Macro에서는 DELETE, INSERT, UPDATE 쿼리를 사용하고 SELECT 쿼리는 따로 일반적인 쿼리처럼 사용하라고 권하고 싶다.



가끔 double quote 문자 (큰 따옴표)를 사용해야 할 때가 있다. 그런데 이것을 그냥 문자열 내에 사용하면 그 따옴표가 있는 부분을 문자열의 끝으로 인식할 수 있다는 것을 염두해 두자.

반응형