오라클 ATP 데이터 로드(Autonomous Transaction Processing)

OS환경 : Oracle Cloud Linux (64bit)

DB 환경 : Oracle Cloud ATP Database 18.4.0.0

방법 : 오라클 ATP 데이터 로드(Autonomous Transaction Processing)

오라클 ATP 환경에서 DBMS_CLOUD 패키지를 이용해 데이터를 로드 하는 방법을 설명함

클라우드의 파일에서 ATP 데이터베이스로 데이터를 로드하려면 새 PL/SQL DBMS_CLOUD패키지를 사용하면됨

DBMS_CLOUD패키지는 Oracle Cloud Infrastructure Object Storage, Oracle Cloud Infrastructure Object Storage Classic 및 Amazon AWS S3과 같은 Cloud 소스에서 데이터 파일 로드를 지원함

데이터 파일을 Object Store에 업로드

오라클 클라우드에서 왼쪽 메뉴에서 Object Storage - Object Storage로 이동

컴포넌트가 많다면 왼쪽 컴포넌트에서 로드할 컴포넌트 선택

해당 컴포넌트에서 Create Bucket로 버킷 생성

버킷 이름 지정 후 Create Bucket 선택

생성된 버킷으로 이동

버킷 정보에서 하단 Upload Object 를 선택

datafiles_for_sh_tables.zip

위 파일을 다운로드 후 압축을 해제하고 드래그앤 드롭으로

하나하나 넣은 후 Upload Object 선택(1개씩 밖에 업로드 되지 않아서 10번 반복해야함)

모두 업로드 한 후 파일 갯수(10개) 확인

Object Store 인증 토큰 생성

오라클 클라우드 오른쪽 상단의 사람모양을 클릭한 뒤 User Settings 로 들어감

왼쪽 하단 Auth Tokens(인증 토큰)을 선택함

Auth Tokens에서 Generate Token 버튼을 눌려 인증 토큰을 생성함

해당 토큰의 설명을 적어준 뒤 Generate Token 버튼을 눌려 인증 토큰을 생성함

생성된 토큰을 꼭 복사해둬야함(토큰이 다시 나타나지 않음)

ATP 스키마에 Object Storage 자격 증명 생성

Object Storage 인증 토큰을 만들었으므로 atpc_user 데이터가 준비된 Object Storage의 자격 증명을 ATP 스키마에 저장함

SQL Developer atpc_user 유저로 로그인 한 뒤

워크 시트 create_credential 에서 DBMS_CLOUD 패키지의 프로시저를 사용하여 Object Storage 자격 증명을 atpc_user 스키마에 저장함

begin
  DBMS_CLOUD.create_credential (
    credential_name => 'OBJ_STORE_CRED',
    username => '<your username>',
    password => '<your Auth Token>'
  ) ;
end;
/

실 사용

이 스크립트를 실행하면 Object Storage의 자격 증명이 ATP atpc_user 스키마에 저장됨

혹시나 잘못 추가하여 자격증명( )을 수정해야 할 경우 Drop_credential을 이용하여 삭제 후 다시 생성해주면됨

SQL>
begin
  DBMS_CLOUD.Drop_credential (
    credential_name => 'OBJ_STORE_CRED'
  ) ;
end;
/

Object Storage에서 ATP 데이터베이스 테이블로 데이터 복사

DBMS_CLOUD.copy_data 프로시저를 실행 하여 Object Storage에 준비된 데이터를 ATP atpc_user 테이블로 복사함 

Object Storage에서 Object별 Details를 확인

Object Details 에서 URL Path를 확인

SQL Developer 워크시트에서 DBMS_CLOUD패키지의 copy_data 프로시저를 사용하여 Object Storage에 준비된 데이터를 복사

예제 스크립트

/* 테이블 이름, region 이름, 테넌트 이름, 버킷 이름 및 파일 이름을 변경해야함 */
 
begin
 dbms_cloud.copy_data(
    table_name =>'CHANNELS',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/b/tutorial_load_atpc/o/chan_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'COUNTRIES',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/coun_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'CUSTOMERS',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/cust1v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'SUPPLEMENTARY_DEMOGRAPHICS',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/dem1v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'SALES',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/dmsal_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'PRODUCTS',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/prod1v3.dat',
    format => json_object('delimiter' value '|', 'quote' value '^', 'ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'PROMOTIONS',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/prom1v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'SALES',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/sale1v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD', 'blankasnull' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'TIMES',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/time_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
 );
end;
/
 
    
begin
 dbms_cloud.copy_data(
    table_name =>'COSTS',
    credential_name =>'OBJ_STORE_CRED',
    file_uri_list =>'https://objectstorage.<region name>.oraclecloud.com/n/<tenant name>/tutorial_load_atpc/costs.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY-MM-DD', 'blankasnull' value 'true')
 );
end;
/

프로시저 실행 후 데이터가 Object Storage에서 ATP 데이터베이스의 테이블로 복사되었는지 확인

(나의 경우 ORA-00600 [ktfbns_update_ilmstat2]이 발생하며 데이터가 제대로 들어가지 않음..)

https://positivemh.tistory.com/383 로 해결

데이터가 제대로 들어갔다면 아래와 같이 표시됨

데이터로드 확인

DBMS_CLOUD 패키지를 사용하여 수행 된 모든 데이터로드 작업은 dba_load_operations 및 user_load_operations 테이블에 기록됨

테이블 설명

dba_load_operations : 모든 로드 작업을 보여줌

user_load_operations : 스키마의 로드 조작을 보여줌

아래 쿼리를 입력하면 진행중인 데이터로드 및 완료된 데이터로드에 대한 정보를 볼수 있음

SELECT table_name, owner_name, type, status, start_time, update_time, 
logfile_table, badfile_table 
FROM user_load_operations WHERE type = 'COPY';
 
TABLE_NAME      STATUS        ROWS_LOADED  LOGFILE_TABLE   BADFILE_TABLE
----------      ------------  -----------  -------------   -------------
CHANNELS        FAILED                     COPY$1_LOG      COPY$1_BAD
CHANNELS        COMPLETED              5   COPY$2_LOG      COPY$2_BAD

데이터 로드 중 아래 보이는 COPY$ 테이블이 생기는것을 확인할 수 있음 user_load_operations 에서 나오는 테이블들임

COPY$1_BAD, COPY$1_LOG, COPY$PGMAG~~

관련글

오라클 ATP 접속하기(Autonomous Transaction Processing)

오라클 ATP 유저 생성(Autonomous Transaction Processing)

오라클 ATP SH 테이블 생성(Autonomous Transaction Processing)

오라클 ATP 데이터 로드(Autonomous Transaction Processing)

오라클 ATP 데이터 로드 관련 트러블슈팅(Autonomous Transaction Processing)

참조 : https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/atp/obe_loading_your_data_into_autonomous_transaction_processing/loading_your-data_into_autonomous_transaction_processing.html

https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/load-data.html#GUID-76FC5A15-7ACC-4B59-87C0-20D4778E522D