다운로드 Oracle Database XE Oracle Instant Client Ruby ruby-oci8

태그 ruby, scripting, All

필터링과 그룹핑: SQL, Linux 스크립팅, Ruby의 비교

Oracle SQL, Linux, Ruby를 이용한 데이터 셋 처리 문제의 해결

게시일: 2008년 1월

"우리는 이미 존재하는 것에 작은 변화를 주거나, 기존의 아이디어들을 새로운 방법으로 조합함으로써 미적인 아름다움을 창조한다."

- 폴 그레이엄, Hackers and Painters

소프트웨어 개발은 창조적인 행위, 그리고 더 나아가 예술적인 작업으로 간주되곤 합니다. 개발자들은 기능적으로 우수할 뿐 아니라 우아하고, 유용하고, 균형 잡힌 소프트웨어의 구현을 목표로 노력합니다. 또 IT 전문가들은 때로 재즈 뮤지션과 같은 "연주자"의 역할을 담당하기도 합니다. 훌륭한 재즈 뮤지션은 주어진 컨텍스트에 들어맞는 음을 선택할 줄 압니다. 개발자들은 스크립팅 테크놀로지에 대한 철저한 지식을 바탕으로 비즈니스/기술적 문제를 효과적으로 해결할 수 있는 간단한 커맨드, 구문, 프로그램을 구현합니다. 이들은 매우 중요한 가치의 제공자이며, 이러한 작업이 일견 쉬워 보인다는 것도 재즈 뮤지션과의 공통점이라 할 수 있습니다.

은 리눅스 운영 체제를 지원하는 오라클 데이터베이스 제품을 1998년 처음으로 출시하였습니다. 그 이후 다양한 오라클 애플리케이션, 미들웨어 제품이 리눅스를 지원하기 시작했습니다. Oracle Unbreakable Linux 프로그램이 공식 출시되면서, 많은 기업들이 오라클 환경을 리눅스 플랫폼으로 이전하기 시작했습니다. 그 당연한 결과로, DBA들은 리눅스 기반 서버 환경의 관리를 위한 스크립트 활용 방법에 관심을 보이고 있습니다.

앞서 필자가 기고한 아티클(한글)을 통해, 시스템 운영자와 DBA의 역할이 언제나 명쾌하게 구분되는 것은 아니라는 사실을 설명한 바 있습니다. 기업은 IT 부서의 전문가들이 데이터베이스, 운영 체제의 문제를 동시에 해결할 수 있기를 기대합니다. 또 DBA가 오라클 데이터베이스 소프트웨어를 설치하기 위해서는 하부 운영 체제에 대한 기본적인 지식이 필수적으로 요구됩니다.

다양한 스크립팅 언어들이 전성기를 맞고 있는 요즘이지만, 모든 시스템에서 공통적으로 지원되는 쉘 스크립팅 환경은 여전히 운영자들에게 필수적인 아이템으로 활용됩니다. 또 오래 전에 개발되어 여전히 실행 중인 스크립트의 유지보수와 업그레이드도 필요합니다. 쉘 스크립트는 모든 IT 전문가들에게 매우 중요한 가치를 갖는 툴입니다.

이번 아티클에서는, Oracle SQL, Linux 시스템 커맨드, 스크립트에서 공통적으로 통용되는 몇 가지 개념에 대해 학습해 보기로 하겠습니다. 특히 데이터 셋의 그룹, 소팅, 필터링 처리를 위해 "집합(set)" 개념을 사용하는 몇 가지 방법을 집중적으로 설명하려 합니다. SQL 언어에 친숙한 사용자라면 잘 알고 있을만한 내용이지만 리눅스 커맨드, Ruby 프로그래밍 언어에서도 이러한 작업이 종종 요구될 때가 있습니다.

여기서 수학적인 집합 이론에 대해 설명하지는 않겠습니다. 그보다는 인간의 언어에서 (그리고 실제 비즈니스 문제에서) 자주 발견되는 다소 애매모호한 영역에 대해 설명하고자 합니다. 스크립트 언어와 비교했을 때, Oracle SQL은 기본적으로 매우 강력한 데이터 셋 처리 기능을 제공합니다. 각각의 컬럼은 미리 정의된 타입의 필드를 포함하고 있으며, 논리적 데이터는 물리적인 표현 방식과 무관하게 독립적으로 처리됩니다. 쿼리에서 반환되는 데이터는 쉘 커맨드의 출력과 근본적으로 다른 형식을 갖습니다. 본 문서는 SQL에 어느 정도 친숙한 사용자들을 대상으로, 데이터베이스 외부에서 데이터 셋을 처리하는 방법을 설명하고 있습니다. 또 문제 해결을 위한 여러 가지 관점을 소개하고 새로운 영역으로 여러분들의 관심을 넓힐 수 있는 계기를 제공하게 될 것입니다.

데이터 셋

SQL은 프로시저 기반의 언어가 아니며, 데이터 집합의 조회, 요약, 처리를 목적으로 설계되었습니다. 하지만 쉘 커맨드를 통해 출력된 결과도 데이터의 집합으로 취급할 수 있는 경우가 있습니다. (물론 이 출력 결과를 구조화된 데이터 셋으로 보기는 어려울 것입니다.) 대부분의 사용자들이 잘 알고 있는 ls 커맨드를 예로 들어 설명해 보겠습니다. 특정 디렉토리의 파일 목록을 표시하는 방법은 커맨드 옵션에 따라 달라집니다. -l 옵션을 이용한 간단한 예를 먼저 살펴 봅시다:

ls -l	  

total 60K
-rw-r--r--  1 root   dba 1.7K May 13 09:02 xe_s000_2072.trc
-rw-r--r--  1 cas    dba  929 Apr 28 22:21 xe_smon_3664.trc
-rw-r--r--  1 cas    dba  794 Apr 26 17:19 xe_smon_3676.trc
-rw-r--r--  1 oracle dba  792 Apr 23 21:52 xe_smon_2120.trc
-rw-r--r--  1 oracle dba  794 Apr 19 17:37 xe_smon_3364.trc
-rw-r--r--  1 root   dba 5.5K Jun 20  2006 xe_s000_2412.trc
-rw-r--r--  1 cas    dba  790 May 25  2006 xe_smon_540.trc

이 데이터를 저장하는 테이블을 아래와 같이 머리 속에 그려 볼 수 있을 것입니다:

이 "가상의 테이블"(ls -l 커맨드의 출력 결과)은 아래에서 설명되는 데이터의 필터링, 소팅, 요약 작업을 위한 예제의 근거 데이터로 활용될 것입니다.

몇 가지 주의할 점 이 데이터는 적어도 우리의 눈으로 보기에는 명백한 구조를 가지고 있습니다. 하지만 첫 번째 라인("total 60K")은 구조에 들어 맞지 않고 있음을 주목하시기 바랍니다. 이처럼 커맨드의 출력 결과의 일부가 구조화된 데이터에 포함될 수 없다는 점은 기본적인 제약 사항으로 작용합니다. 데이터의 정제를 위해, 먼저 출력 결과를 파일로 리다이렉트 처리합니다. ls -l > test.txt 이제 텍스트 편집기에서 파일을 열어 첫 번째 라인을 삭제할 수 있습니다. 이처럼 구조화된 데이터를 얻을 때까지 데이터의 편집 작업을 수행해 주어야 합니다. 요구되는 구조화 수준은 데이터의 사용 목적에 따라 달라집니다. 단지 사용자들이 읽기 위한 보고서의 데이터는 오라클 테이블에 로드하는 데이터와는 다른 구조화 수준을 가질 것입니다. 위에서 설명한 것처럼 수작업으로 데이터를 처리하기 어렵다면 grep -v 명령을 사용하여 출력 결과를 파이프(pipe) 처리하는 방법을 고려할 수 있습니다(여기에 대해서는 뒷부분에서 설명합니다). 이 커맨드는 특정 패턴과 매치되는 라인을 제외시키기 위한 목적에서 사용됩니다. 또 위의 출력 결과에서 날짜 포맷에 일관성이 존재하지 않음을 확인할 수 있습니다. 이런 상태에서는 다른 포맷으로 날짜를 표시할 수 없을 뿐 아니라, 정렬도 불가능합니다. 또 공백 기호가 포함되어 있다는 것도 문제가 됩니다. 여기서 데이터베이스의 데이터타입이 갖는 유용성을 다시 한 번 확인할 수 있습니다. 오라클은 날짜 데이터의 처리 및 정렬을 위한 다양한 함수를 제공하며, 따라서 데이터베이스 내에서 구조화된 데이터로 날짜를 처리하는 것이 훨씬 편리합니다. 커맨드 라인에서 출력된 날짜 데이터를 정렬하고자 하는 경우라면, -t와 같은 옵션을 사용하여 결과의 파이프 처리 이전에 정렬된 결과를 얻는 방법을 고려할 수 있습니다.

데이터의 필터링, 정렬

대부분의 리눅스 커맨드는 데이터 정렬을 위한 옵션을 별도로 제공하고 있습니다. ls 커맨드의 경우 시간 기준 정렬을 위한 -t 옵션, 확장자 기준 정렬을 위한 -X 옵션 등이 지원됩니다. 본 예제에서는 소유자와 파일 네임만을 선택한 후 소유자를 기준으로 결과를 정렬하는 방법을 사용하기로 합니다. 하지만 ls 명령에는 이러한 경우를 위한 정렬 옵션을 제공하지 않습니다. 데이터가 테이블에 저장되어 있다면 아래와 같은 명령을 사용할 수 있을 것입니다:

SELECT file_owner, file_name FROM imaginary_table ORDER BY owner;

[root@linux-server test]# ls -l | awk '{print $3"\t"$9}'

root    xe_s000_2072.trc
root    xe_s000_2412.trc
oracle  xe_smon_2120.trc
oracle  xe_smon_3364.trc
cas     xe_smon_3664.trc
cas     xe_smon_3676.trc
cas     xe_smon_540.trc

데이터를 좀 더 자세히 들여다 보면 날짜 데이터가 3개의 컬럼으로 분리되어 있음을 알 수 있습니다. 이 세 컬럼은 공백 기호로 구분되고 있으며, 따라서 3개의 컬럼이 별개의 값을 갖는 것처럼 보이는 것입니다.

이제 결과를 다른 커맨드로 파이프 처리하여 정렬된 결과를 얻습니다. sort 유틸리티는 사용자가 정의한 정렬 방식에 따라 라인을 재배치합니다. 아래와 같은 명령을 사용하여 원하는 결과를 얻을 수 있습니다:

[root@linux-server test]# ls -l | awk '{print $3"\t"$9}' | sort

cas     xe_smon_3664.trc
cas     xe_smon_3676.trc
cas     xe_smon_540.trc
oracle  xe_smon_2120.trc
oracle  xe_smon_3364.trc
root    xe_s000_2072.trc
root    xe_s000_2412.trc

[root@linux-server test]# ls -l | awk '{print $3"\t"$9}' | sort | grep oracle

oracle  xe_smon_2120.trc
oracle  xe_smon_3364.trc

[root@linux-server test]# ls -l | grep -v oracle

cas     xe_smon_3664.trc
cas     xe_smon_3676.trc
cas     xe_smon_540.trc
root    xe_s000_2072.trc
root    xe_s000_2412.trc

유니크 데이터와 데이터의 그룹핑

"SELECT owner FROM imaginary_table ORDER BY 1" 구문과 같은 결과를 얻기 위한 커맨드가 아래와 같습니다.

[root@linux-server test]# ls -l | awk '{print $3}' | sort

cas
cas
cas
oracle
oracle
root
root

SELECT DISTINCT owner FROM imaginary_table ORDER BY 1

[root@linux-server test]# ls -l | awk '{print $3}' | sort | uniq

cas
oracle
root

SELECT count(*), owner FROM imaginary_table GROUP BY owner

[root@linux-server test]# ls -l | awk '{print $3}' | sort | uniq -c
      1
      3 cas
      2 oracle
      2 root

[root@linux-server test]# ls -l |grep -v total |awk '{print $3}' | sort | uniq -c
      3 cas
      2 oracle
      2 root

쉘 커맨드에서 Ruby까지

이러한 고전적인 방법을 익히는 것보다는 새로운 테크놀로지를 배우는데 시간을 투자하는 것이 훨씬 유익하다고 생각하는 사용자라면, Ruby 프로그래밍 언어를 먼저 고려해 보시기 바랍니다. Ruby는 Rails 웹 애플리케이션 개발 프레임워크의 핵심 컴포넌트로서 인기를 얻고 있습니다. 하지만 또 한 편으로, Ruby는 시스템 운영자, DBA들을 위한 범용적인 언어로서 그 가치를 발휘합니다.

Ruby와 SQL은 집약적이고 간결한 언어라는 특성을 공유하며, 매우 강력한 기능의 구문을 간단한 문법으로 표현할 수 있는 가능성을 제공합니다. 따라서 한층 명확하고 편리한 방법으로 프로그램을 개발할 수 있습니다.

아래의 예제는 Ruby 배포본에 기본적으로 포함된 Interactive Ruby Shell(irb)을 사용하고 있습니다. 따라서 Ruby 프로그램 이외에는 다른 어떤 소프트웨어도 따로 설치할 필요가 없습니다. 루비의 Array 클래스는 앞에서 설명한 sort, uniq, grep 기능에 대응하는 메소드를 제공하고 있습니다.

먼저 커맨드 라인에서 "irb"를 입력하여 irb 프롬프트를 띄웁니다:

[root@linux-server test]# irb
irb(main):001:0>  

myList = ['ruby','sql','ruby','bash','python','perl','java','sql']
=> ["ruby", "sql", "ruby", "bash", "python", "perl", "java", "sql"]

먼저 소팅을 수행해 봅시다:

irb(main):002:0> myList.sort
=> ["bash", "java", "perl", "python", "ruby", "ruby", "sql", "sql"]

irb(main):003:0> myList.uniq
=> ["ruby", "sql", "bash", "python", "perl", "java"]

irb(main):004:0> myList.grep(/r/)
=> ["ruby", "ruby", "perl"]

irb(main):005:0> myList.uniq.size
=> 6

계층간 통합 언어로서의 Ruby

SQL은 오라클 데이터베이스 내부의 데이터 셋을 처리하는데 유용한 언어입니다. (리눅스 커맨드로 구성되는) 쉘 스크립트는 파일 시스템 레벨에서 유용합니다. 그렇다면 데이터베이스 내부/외부의 데이터 셋을 동시에 처리해야 하는 경우는 어떤 대안을 고려할 수 있을까요?

Ruby는 서로 다른 테크놀로지 또는 애플리케이션 계층 간의 커뮤니케이션을 위한 "통합" 언어로 활용될 수 있습니다. 그 한 예로 데이터베이스와 운영 체제 간의 상호작용을 위해 Ruby를 활용할 수 있습니다. 웹 애플리케이션 환경에 구현된 데이터베이스의 레코드 내에 파일 또는 파일 시스템에 대한 참조가 포함된 경우를 흔히 찾아 볼 수 있습니다. 예를 들어 사용자가 이미지를 업로드한 경우 사용자 아이디는 데이터베이스에, 이미지는 파일 시스템에 저장되는 것이 일반적입니다. 어떤 사용자가 어떤 파일을 업로드했는지 확인하려면 데이터베이스와 운영 체제를 모두 참조하는 솔루션을 구현해야 합니다.

이러한 구현 작업에는 Ruby 이외에도 별도의 소프트웨어가 추가로 필요합니다. 아래 예제를 실행하려면 Oracle Client, OCI8 gem(ruby-oci9)이 기본적으로 설치되어 있어야 하며(페이지 상단의 "다운로드" 링크 참고), HR 스키마를 포함하는 오라클 데이터베이스로의 연결이 설정되어 있어야 합니다.

샘플 데이터의 생성

먼저 위 환경을 시뮬레이션하는 파일을 생성할 디렉토리로 이동합니다. irb에서 아래 프로그램을 라인 단위로 입력하거나, 전체 목록을 파일에 저장한 후 파일을 일괄 실행(ruby <name of file>)할 수 있습니다. "#" 기호로 시작하는 라인은 주석을 의미합니다:

# First, enter some preliminary commands to create files that will 
# simulate data that was uploaded by users. Load the OCI8 package 
# and make a connection to your database:

require 'OCI8'
conn =OCI8.new('hr', 'hr', 'xe')

# Next create a SQL query that will return all of the users 
# that have an "a" in their last name.  
# This simulates arbitrary user uploaded files:

sql="select employee_id||'_'||upper(last_name)|| '.gif' " 
sql+="from employees where upper(last_name) like '%A%'"

# Now we will create the actual (empty) files.  
# They will be named using the employee id and last name. 
# as specified in the first (and only) field in the query
conn.exec(sql) { |r| File.new(r[0],'w') }

파일시스템/데이터베이스 통합 리포트의 생성

# Load OCI8
require 'OCI8'

# Make a connection to the Oracle Database containing the HR sample schema
conn =OCI8.new('hr', 'hr', 'xe')

# Create a SQL query
sql="select employee_id||'_'||upper(last_name)|| '.gif',"
sql+=" last_name, first_name from employees order by 2,3"

# Execute the query.  Each result is an array of strings representing
# each field in the query.  If a file exists on the file system that matches
# the pattern we specified earlier, display an X
conn.exec(sql) do |r| 
  if File.exists?(r[0]) 
      print 'X ' 
  else
      print '  '
  end 

# Display the last name followed by the first name
  puts "#{r[1]}, #{r[2]}"
end

X Abel, Ellen
X Ande, Sundar
X Atkinson, Mozhe
X Austin, David
X Baer, Hermann
X Baida, Shelli
X Banda, Amit
X Bates, Elizabeth
  Bell, Sarah
  Bernstein, David
  Bissot, Laura
  Bloom, Harrison
  Bull, Alexis
X Cabrio, Anthony
...
  Weiss, Matthew
X Whalen, Jennifer
  Zlotkey, Eleni

오라클은 위 작업을 데이터베이스 내부적으로 처리할 수 있는 다양한 방법을 제공합니다. 한 예로, BLOB을 이용하여 이미지를 데이터베이스 내부에 바이너리 데이터로 저장할 수도 있습니다. 하지만 이러한 설계가 적용되지 않은 환경에서 사용 가능한 대안을 알아 두는 것도 중요합니다.

요약 및 비교

아래 표를 통해, 데이터 소스는 다르지만 처리 방법은 서로 유사하다는 사실을 확인할 수 있습니다. SQL 데이터 셋은 데이터베이스 테이블에 대한 쿼리 결과로 반환됩니다. 리눅스 데이터 셋은 특정 커맨드가 다른 커맨드로 파이프 처리된 결과로 얻어집니다. Ruby에서는 Ruby 문자열 어레이가 사용되고 있습니다.

데이터의 복잡한 요약(aggregation) 처리가 필요한 경우에는 Oracle SQL의 강력한 기능을 활용하는 것이 편리할 수 있습니다. 또 파이프 처리된 리눅스 커맨드보다는 SQL 구문이 더 간결하고 편리합니다. 오라클은 일정한 형태로 구조화된 데이터의 가져오기(import)를 위한 다양한 툴을 제공합니다. Oracle External Table, SqlLoader(sqlldr) 등을 이용하면 이러한 데이터를 테이블로 쉽고 빠르게 가져올 수 있습니다.

스크립팅은 필요한 기능의 프로토타입을 신속하게 구현하는데 효과적입니다. 하지만 주어진 비즈니스 요구 사항이 효과적으로 구현되고 나면 언젠가는 기능 개선이 필요한 시점이 오게 마련입니다. 복잡한 필터링, 분석을 필요로 하는 데이터 셋을 처리해야 하는 경우에는, 오라클 데이터베이스 기반 프로그램을 활용함으로써 유지보수성과 데이터 저장 효율을 개선할 수 있습니다. 하지만 무엇보다도, Oracle SQL의 강력한 기능이 제공하는 혜택을 활용할 수 있다는 사실이 중요합니다.

결론

기업의 IT 조직은 반복 가능한 프로세스, 그리고 잘 설계된 시스템의 구현을 위해 노력해 왔습니다. 또 프로그래머들은 요구 사항을 이상적으로 만족하는 소프트웨어를 구현하기 위해 노력합니다. 기업의 비즈니스 요구 사항은 수시로 변화하며, 따라서 프로그래머들도 변화하는 상황에 신속하게 대응할 수 있어야 합니다. 시스템 운영자와 DBA들의 경우에는, 일반적으로 솔루션 개발을 위해 주어지는 시간과 리소스가 부족한 경우가 많습니다. 이들은 기본적인 알고리즘을 활용하여 문제를 최단 시간 내에 해결할 수 있어야 합니다. 프로그래머들과 달리, 이들에게는 철저한 테스트를 거칠만한 여유가 주어지지 않습니다. 하지만 독창적이고 창조적인 솔루션을 개발할 수 있는 기회는 충분합니다.

SQL을 잘 알고 있는 운영자라면 본 문서에서 예시된 스크립트, 커맨드를 활용하는 방법을 쉽게 익히실 수 있을 것입니다. 또 데이터베이스/시스템 운영 과정에서 익힌 문제 해결 전략을 다른 영역에 응용하여 적용하는 것도 가능합니다. 스크립트 언어는 프로시저 기반으로 구현되었지만, '집합' 개념의 적용을 위한 기본적인 리소스를 함께 제공하고 있습니다. 기존에 보유한 지식과 본 문서에서 설명되는 새로운 스킬을 조합하면 아무리 어려운 기술적 문제도 쉽게 해결하실 수 있을 것입니다.