전용 Exadata 인프라에서 Autonomous Database로 외부 데이터 쿼리

Autonomous Database on Dedicated Exadata Infrastructure로 데이터를 쿼리하고 검증하는 패키지 및 도구에 대해 설명합니다.

외부 데이터는 데이터베이스에서 관리되지 않지만 DBMS_CLOUD 프로시저를 사용하여 외부 데이터를 질의할 수 있습니다. 외부 데이터에 대한 질의가 데이터베이스 테이블에 대한 질의만큼 빠르지는 않지만, 이 접근 방식을 사용하여 외부 소스 파일 및 외부 데이터에 대한 질의 실행을 빠르게 시작할 수 있습니다.

DBMS_CLOUD 프로시저를 사용하여 문제를 식별하고 외부 테이블의 데이터를 수정하거나 데이터를 사용하기 전에 부적합한 데이터를 제외할 수 있도록 외부 소스 파일의 데이터를 외부 테이블에 대해 검증할 수 있습니다.

주:

ADMIN 사용자를 사용하지 않는 경우 사용자에게 사용자가 수행해야 하는 작업에 필요한 권한이 있는지 확인합니다. 자세한 내용은 Manage Database User Privileges를 참조하십시오.

외부 데이터 질의

클라우드의 파일에 있는 데이터를 쿼리하려면 먼저 오브젝트 스토리지 자격 증명을 Autonomous Database에 저장한 다음 PL/SQL 프로시저 DBMS_CLOUD.CREATE_EXTERNAL_TABLE을 사용하여 외부 테이블을 생성해야 합니다.

이 예의 소스 파일 channels.txt에는 다음 데이터가 있습니다.

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others

1. DBMS_CREDENTIAL.CREATE_CREDENTIAL 프로시저를 사용하여 클라우드 객체 스토리지 인증서를 저장합니다. 예를 들면, 다음과 같습니다.

   BEGIN
     DBMS_CREDENTIAL.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username => '[e-mail]',
       password => 'password'
     );
   END;
   /

   username 및 password에 제공하는 값은 사용 중인 클라우드 객체 스토리지 서비스에 따라 달라집니다.

   • Oracle Cloud Infrastructure Object Storage: username는 Oracle Cloud Infrastructure 사용자 이름이고 password는 Oracle Cloud Infrastructure 인증 토큰입니다. 인증 토큰 작업을 참조하십시오.

   • Oracle Cloud Infrastructure Object Storage Classic: username은 Oracle Cloud Infrastructure Classic 사용자 이름이며, password은 Oracle Cloud Infrastructure Classic 비밀번호입니다.

   이 작업은 인증서를 암호화된 형식으로 데이터베이스에 저장합니다. 인증서 이름에 대해 원하는 이름을 사용할 수 있습니다. 이 단계는 객체 저장소 인증서가 변경되지 않는 한 한 한 번만 필요합니다. 인증서를 저장한 후에는 모든 데이터 로드에 대해 동일한 인증서 이름을 사용할 수 있습니다.

2. DBMS_CLOUD.CREATE_EXTERNAL_TABLE 프로시저를 사용하여 소스 파일 위에 외부 테이블을 생성합니다.

   DBMS_CLOUD.CREATE_EXTERNAL_TABLE 절차는 지원되는 클라우드 객체 스토리지 서비스에서 외부 파일을 지원합니다. 인증서는 테이블 레벨 속성이므로 외부 파일은 동일한 객체 저장소에 있어야 합니다.

   예:

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'CHANNELS_EXT',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/channels.txt',
       format => json_object('delimiter' value ','),
       column_list => 'CHANNEL_ID NUMBER, 
   	CHANNEL_DESC VARCHAR2(20), 
   	CHANNEL_CLASS VARCHAR2(20)'
    );
   END;
   /
   

   다음과 같은 매개변수가 있습니다.

   • table_name: 외부 테이블 이름입니다.

   • credential_name: 이전 단계에서 생성된 인증서의 이름입니다.

   • file_uri_list: 질의할 소스 파일의 콤마로 구분된 목록입니다.

     이 예에서 file_uri_list는 us-phoenix-1 영역의 mybucket 버킷에 있는 channels.txt 파일을 지정하는 Oracle Cloud Infrastructure Swift URI입니다. idthydc0kinr는 버킷이 상주하는 오브젝트 스토리지 네임스페이스입니다. 지원되는 URI 형식에 대한 자세한 내용은 클라우드 오브젝트 스토리지 URI 형식을 참조하십시오.

   • format: 소스 파일의 형식을 설명하기 위해 지정할 수 있는 옵션을 정의합니다. 지정할 수 있는 형식 옵션에 대한 자세한 내용은 형식 매개변수를 참조하십시오.

   • column_list: 소스 파일에 있는 열 정의의 콤마로 구분된 목록입니다.

   이제 이전 단계에서 생성한 External Table에서 query를 실행할 수 있습니다. 예:

   SELECT count(*) FROM channels_ext;

   기본적으로 데이터베이스는 외부 데이터 파일의 모든 행이 적합해야 하며 대상 데이터 유형 정의와 파일의 형식 정의가 모두 일치해야 합니다. 소스 파일에 지정한 형식 옵션과 일치하지 않는 행이 있으면 쿼리가 오류를 보고합니다. format 매개변수 옵션(예: rejectlimit)을 사용하여 이러한 오류를 숨길 수 있습니다. 또는 생성한 External Table을 검증하여 오류 메시지와 거부된 행을 확인함으로써 형식 옵션을 적절하게 변경할 수도 있습니다. 자세한 내용은 외부 데이터 검증을 참조하십시오.

   매개변수에 대한 자세한 내용은 CREATE_EXTERNAL_TABLE Procedure를 참조하십시오.

외부 데이터 검증

외부 테이블을 검증하려면 DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE 프로시저를 사용합니다.

External Table을 검증하기 전에 DBMS_CLOUD.CREATE_EXTERNAL_TABLE 프로시저를 사용하여 External Table을 생성해야 합니다. 그런 다음 DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE 프로시저를 사용하여 검증합니다. 예:

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
    table_name => 'CHANNELS_EXT' );
END;
/

이 프로시저는 소스 파일을 스캔하고 External Table을 생성할 때 지정된 형식 옵션을 사용하여 소스 파일을 검증합니다.

검증 작업은 기본적으로 소스 파일의 모든 행을 스캔하고 행이 거부되면 정지합니다. 행의 부분 집합만 검증하려면 rowcount 매개변수를 사용합니다. rowcount 매개변수가 설정되면 검증 작업은 행을 스캔하고 행이 거부되거나 지정된 수의 행이 오류 없이 검증될 때 정지합니다.

예를 들어, 다음 검증 작업은 100개의 행을 스캔하고 행이 거부되거나 100개의 행이 오류 없이 검증될 때 정지합니다.

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
     table_name => 'CHANNELS_EXT',
     rowcount => 100 ); 
END; 
/

행이 거부될 때 검증이 정지되지 않도록 하고 거부된 행을 모두 보려면 stop_on_error 매개변수를 FALSE로 설정합니다. 이 경우 VALIDATE_EXTERNAL_TABLE는 모든 행을 스캔하고 거부된 모든 행을 보고합니다.

행의 부분 집합만 검증하려면 rowcount 매개변수를 사용합니다. rowcount가 설정되고 stop_on_error가 FALSE로 설정된 경우 검증 작업은 지정된 수의 행이 거부되거나 지정된 수의 행이 오류 없이 검증될 때 행을 스캔하고 정지합니다. 예를 들어, 다음 예제는 100개의 행을 스캔하고 100개의 행이 거부되거나 100개의 행이 오류 없이 검증될 때 정지합니다.

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
     table_name => 'CHANNELS_EXT',
     rowcount => 100, 
     stop_on_error => FALSE );
END; 
/

DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE 매개변수에 대한 자세한 내용은 VALIDATE_EXTERNAL_TABLE Procedure를 참조하십시오.

dba_load_operations 및 user_load_operations 테이블에서 검증 작업 결과를 보려면 데이터 검증을 위한 로그 보기를 참조하십시오.

데이터 검증에 대한 로그 보기

External Table을 검증한 후 로드 작업 테이블을 query하여 검증 작업의 결과를 확인할 수 있습니다.

• dba_load_operations: 모든 검증 작업을 보여줍니다.

• user_load_operations: 스키마의 검증 작업을 보여줍니다.

이러한 테이블을 사용하여 로드 검증 정보를 볼 수 있습니다. 예를 들어, 다음 SELECT 문을 사용하여 user_load_operations를 query합니다.

SELECT table_name,owner_name,type,status,start_time,update_time,logfile_table,badfile_table
FROM user_load_operations
WHERE type = 'VALIDATE';

TABLE_NAME    OWNER_NAME  TYPE       STATUS     START_TIME     UPDATE_TIME    LOGFILE_TABLE    BADFILE_TABLE
------------- ----------  ---------- ---------- -------------- -------------  ---------------  ---------------
CHANNELS_EXT  SH          VALIDATE   COMPLETED  13-NOV-17...   13-NOV-17...   VALIDATE$21_LOG  VALIDATE$21_BAD

TYPE 열의 WHERE 절과 함께 이 SQL 문을 사용하면 VALIDATE 유형의 모든 로드 작업이 표시됩니다.

LOGFILE_TABLE 열에는 검증 작업의 로그를 보기 위해 질의할 수 있는 테이블의 이름이 표시됩니다. 예를 들어, 다음 질의는 이 검증 작업에 대한 로그를 보여줍니다.

SELECT * FROM VALIDATE$21_LOG;

BADFILE_TABLE 열은 검증 중 오류가 발생한 행을 조회하기 위해 질의할 수 있는 테이블의 이름을 보여줍니다. 예를 들어, 다음 질의는 위의 검증 작업에 대해 거부된 레코드를 보여줍니다.

SELECT * FROM VALIDATE$21_BAD;

로그에 표시된 오류 및 BADFILE_TABLE에 표시된 행에 따라 DROP TABLE 명령을 사용하여 External Table을 삭제하고 DBMS_CLOUD.CREATE_EXTERNAL_TABLE에 올바른 형식 옵션을 지정하여 External Table을 재생성하여 오류를 수정할 수 있습니다.

주:

LOGFILE_TABLE 및 BADFILE_TABLE 테이블은 각 검증 작업에 대해 2일 동안 저장된 후 자동으로 제거됩니다.

---

제목 및 저작권 정보

Copyright ©2021,2024,

Oracle and/or its affiliates.