오라클 12.1과 12.2 자체의 JSON 활용에 관한예제 및 스크립트를 준비해놓고 너무 많은 시간이 지나버렸습니다.  개인적인 욕심에 이것저것 새로운 것들을 해보고 혼자 기뻐하고 끝나는게 아쉬워서 블로그를 시작했지만, 게으름으로 인해서 두번째 페이지의 보강이 늦어져서 검색후 오시는분들에게 실무적인 내용이 없는데에 미안하게 생각하고 있습니다. 

현재는 AWS, 오라클 클라우드 아키텍처 구성과 데이터분석관련 헤딩을 하고 있어서 마음의 여유가 생기는데로 정리하도록 하겠습니다.

Oracle 9i 에서 처음 제공된 XML DB의 기능을 개인적으로 많이 사용하지는 않았지만 버전이 올라가면서 기능적으로 추가가 되었습니다.

 XMLType이  데이터 타입으로 지원되어 데이터베이스의 XML 문서를 SQL에서 액세스 할 수 있으며 XML 개발자는 문서에서 XML 표준을 사용할 수 있습니다. 이는 XML 문서에 대해 쿼리를 수행 할 수 있도록 했습니다. Varchar 또는 CLOB 구현 대신 XML Type을 사용하여 Table, Column 또는 View를 만들 수 있습니다. 매개 변수 및 변수에 대한 데이터 유형으로 사용할 수도 있었습니다.

하지만 12C 이전 버전에서는 JSON TYPE에 대한 기능등을 제공하지 않아서 APEX_JSON 과 PL/JSON 이라는 오픈소스의 라이브러리를 사용하여서 필요한 기능들을 사용해야 했습니다.

 오라클 Database 12c 는 트랜잭션, 인덱싱, 선언적 쿼리 및 View 를 포함한 관계형 데이터베이스 기능을 기본으로 JSON (JavaScript Object Notation) 데이터를 지원한다고 합니다.

XML 과 JSON 의 특징비교는 넘어가도록 하고 데이터 베이스에서의 비교를 하겠습니다.

오라클 데이터 베이스 안에서 XML 과 JSON 의 차이는 XML 은 XMLTpye 를 사용 하여 저장 가능 하지만 JSON 데이터는 Varchar2,BLOB,CLOB 를 이용하여 저장 됩니다. 권고사항은 Check 제약조건을 항상 사용 하여 Column 값이 유효한 JSON 인지 확인 하는 것이 좋다고 합니다.

LAX(느슨한) Strict(엄격한) Column 으로 관리 할지에 따라서 옵션을 부여 하면 됩니다. (Default: LAX)

-- EX) Table 생성시 CLOB JSON column script

{column_name} CLOB CONSTRAINT {constraint_name} CHECK ({column_name}IS JSON)

{column_name} CLOB CONSTRAINT {constraint_name} CHECK ({column_name} IS JSON STRICT)

18C 버전기준으로 XML 과 공간데이터(Spatial Types), ANYTYPE 등 생소한 데이터 타입들이 많이 있지만 JSON  type 은 예정에 없나 봅니다.

다음 문서는 CLOB TYPE 의 JSON 칼럼을 기준으로 작성된 스크립트의 예제 수정해서 사용합니다.

출처 : Oracle Database Online Documentation 12c Release 1 (12.1) 

* Object Creation Scripts

 A. ELEMENT Query

-- Example A-1 직접 조건 절에 따른 데이터 조회

-- Example A-2  Column 추출후 데이터 비교 조회

-- Example A-3 JSON Arry를 JSON DATA로 조회 

-- Example A-4 JSON 배열 요소 표시 

-- Example A-5 JSON Array 더하기 다른 데이터 조회

-- Example A-6 JSON_TABLE: NESTED 사용 배열 elements

-- Example A-7 JSON 점(.) 표기 AND JSON_VALUE 쿼리 비교 1  

-- Example A-8 JSON 표기 AND JSON_VALUE 쿼리 비교 2

 B.INDEX : Bitmap 은 Enterprise Version 만 사용가능하며 B-tree 기본  

-- Example B-1 Creating a Bitmap Index for JSON_EXISTS  

-- Example B-2 Creating a Bitmap Index for JSON_VALUE                                  

-- Example B-3 Creating a Function-Based Index for a JSON Object Field: JSON_VALUE

-- Example B-4 Function-Based Index(FBI) : 펑션 기반 인덱스 사용

-- Example B-5 JSON_VALUE 명시 적  RETURNING NUMBER

-- Example B-6 Numerical Conversion 숫자형 변환 조회

-- Example B-7 묵시적 형변환  Numerical Conversion

 C. Indexing Multiple JSON Fields Using a Composite B-Tree Index

-- Example C-1 Creating Virtual Columns for JSON Object Fields

-- Example C-2 Creating a Composite B-tree Index for JSON Object Fields

-- Example C-3 Two Ways to Query JSON Data Indexed with a Composite Index

 D. Full-Text Search of JSON Data

 E. Loading External JSON Data

 F. Replication of JSON Data

NULL 관련 함수

-- NULLIF (exp1,expr2)

NULLIF 는 expr1 과 expr2 를 비교합니다. 같은 경우는 NULL을 리턴합니다. 다르면 expr1 을 리턴합니다. expr1 은 리터럴 NULL 을 지정할 수 없습니다.

동일문장

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

-- COALESCE(expr1,expr2,expr3,…)

COALESCE 는 식의 목록의 첫 번째 NULL이 아닌 expr 을 반환합니다. 2 개 이상의 식을 지정할 필요가 있습니다. 모든 expr 이 NULL로 계산되는 경우,이 함수는 NULL을 리턴합니다.

동일문장 

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

통계관련 함수

COVAR_POP (expr1, expr2) / (STDDEV_POP (expr1) * STDDEV_POP (expr2))

1. 몽고디비에서 0~1사이의 Random 데이터 생성 (50만건)

for(i=0; i<500000; i++) {
db.test.insert({"seq" : i,"random": Math.random(), "date": new Date() }); 
}

2. R 에서 rmongodb 패키지 사용해서 plot 찍기 (10건 : limit=10L)

library(rmongodb) 
mongo <-mongo.create(host="-- HOST IP --", name="",username="", password="", db="test")
if (mongo.is.connected(mongo)) {
    buf <- mongo.bson.buffer.create() 
    query <- mongo.bson.from.buffer(buf)
    cursor <- mongo.find(mongo, "test.test", query, limit=10L)
    # Step though the matching records and display them
    while (mongo.cursor.next(cursor)) { 
     a<- mongo.bson.to.list(mongo.cursor.value(cursor))
    print(a$seq)
    print(a$random)
    plot(a$seq,a$random,xlim=c(0,10),ylim=c(0,1))
    axis(1,at=seq(0,10,1))
    axis(2,at=seq(0,1,0.1))
    par(new=T)
    }
}

결과 확인 

무료 MongoDB 호스팅 (무료 240M, 더 많은 공간을 선택 사용 할경우 유료)

https://mongolab.com/home

전 위의 사이트와는 전혀 상관없는 사람입니다.

1. 가입 및 Database 생성

Database명 :mongolab

User 명 :mongolab

- 생성후 정보 확인

Shell 접속이 가능한지 아직 미확인이지만 사용시 아래와 같은 방법으로 접속

2. Toad for Cloud Database 에서 접속

Data source name : Cloud_MongoDB  (임의로 생성)

위에서 생성한 Database 명,Port, 사용자 정보 입력

- 가상환경 원격 디비에서 접속시와 동일한절차(내용추가는 언제쯤이나;;;)

1.환경

LOCAL : Windows 7 64 bit

가상머신 : oracle linux 5 32bit

mongo db : linux 32bit (파일명 : mongodb-linux-i686-2.0.4.tar)

http://www.mongodb.org/downloads

참조 문서 : http://docs.mongodb.org/master/tutorial/install-mongodb-on-linux/

2. 순서

 A.mongo db 다운로드 (약 36.5M) 후 파일 이동

 B.압축풀기

tar -xvf mongodb-linux-i686-2.0.4.tar

 C. 데이터 저장 디렉토리생성후 권한주기

  mkdir -p /data/db

  chdown mongo /data/db

D. 실행

mongod --dbpath /data/db

(warning 떴으나 무슨얘기인지 모르겠고 그냥 진행: 물어볼사람도 없고,  일단 실행이 되는지만 확인;;)

E. 확인

또다른 콘솔창열어서 실행

./mongo

MongoDB shell version: 2.0.4
connecting to: test

데이터 하나 입력 테스트

> db.test.save( { a: 1 } )
> db.test.find()
{ "_id" : ObjectId("4fa0d3b1980533510f360dfb"), "a" : 1 }

--입력된 내용 확인 끝--

3. Toad for Cloud Database  (FreeWare 입니다.)

http://toadforcloud.com/index.jspa

 A. 설치 (별다른거 없이 next)

 B. 설치후 가상환경의 mongo db 접속

   - Data Hub -> Map Data Source -> Data source 이름적고 source type 는 MongoDB 선택

   - Host 는 가상환경의 호스트명 혹은 ip , 포트는 디폴트설치 했으므로 그냥두고

접속 확인 

SQL 에디터 열어서 조회작업

어라 에러나네?  빠진 작업추가

매핑 작업

체크후 OK

결과확인 (SQL 문장으로 NOSQL DB조회)

참고
http://docs.oracle.com/cd/NOSQL/html/quickstart.html

linux 비트 확인
1.
 getconf LONG_BIT
2.
file /sbin/init

/**************** 자바 관련 설정 *************/
http://www.oracle.com/technetwork/java/javase/downloads/index.html
1.자바 다운로드 .rpm.bin
   -- A. NO SQL rpm.bin 실행법
   -- B. 권한 확인후
   -- C. ./파일명
  
  
2.복사후 ./파일명

3.위치 확인
4. 기존 자바 버젼 변경

/usr/sbin/alternatives --install /usr/bin/java java /usr/java/default/bin/java 2

/usr/sbin/alternatives --config java

There are 2 programs which provide 'java'.

  Selection    Command
-----------------------------------------------
*+ 1           /usr/lib/jvm/jre-1.4.2-gcj/bin/java
   2           /usr/java/default/bin/java
  
Enter to keep the current selection[+], or type selection number: 2
/**************** 자바 관련 설정 *************/

1. download
http://www.oracle.com/technetwork/database/nosqldb/downloads/index.html
 gunzip kv-ce-1.2.116.tar.gz
 tar xvf kv-ce-1.2.116.tar

설치후 테스트
콘솔1
java -jar lib/kvstore-1.2.116.jar kvlite

콘솔2
java -jar lib/kvstore-1.2.116.jar ping -port 5000 -host localhost.localdomain

자바 예제
javac -cp examples:lib/kvclient-1.2.116.jar examples/hello/HelloBigDataWorld.java
java -cp examples:lib/kvclient-1.2.116.jar hello.HelloBigDataWorld

결과
Hello Big Data World!

-----------------------------------------------------------------------
테스트 종료

------------------------------------------------------------------------

추가1 : Nosql host 변경작업 테스트  (config.xml 에서 host 부분 설정)

추가2 : Toad For Cloud Database 사용해서 접속시도 (삽질후 포기; 지원을 아직 안하는지 내가 엄한데 파고 있는지 파악 불가)

추가3 : mongo db  설치후 Toad For Cloud Database 툴 테스트예정  (2012.05.)

추가4: mongo db 설치후 리눅스에서 작동확인 -Toad For Cloud Database 사용해서 접속시도 (실패: 삽질후 방화벽 해제 (원인 :가상머신 재부팅후 방화벽 자동 실행됨: 망할;;;;;) ->방화벽 해제후 접속성공

-- 로컬 가상머신에서 테스트 및 http console 확인 되었을경우 방화벽 포트 확인(기본적으로 방화벽 부터 확인해놓자)