Externe Daten mit Autonomous Database on Dedicated Exadata Infrastructure abfragen

Beschreibt Packages und Tools zum Abfragen und Validieren von Daten mit Autonomous Database on Dedicated Exadata Infrastructure.

Externe Daten werden nicht von der Datenbank verwaltet. Sie können jedoch DBMS_CLOUD-Prozeduren verwenden, um externe Daten abzufragen. Obwohl Abfragen für externe Daten nicht so schnell wie Abfragen für Datenbanktabellen sind, können Sie mit diesem Ansatz schnell Abfragen für externe Quelldateien und externe Daten ausführen.

Mit DBMS_CLOUD-Prozeduren können Sie die Daten in den externen Quelldateien für eine externe Tabelle validieren, sodass Sie Probleme erkennen und die Daten in der externen Tabelle korrigieren oder ungültige Daten ausschließen können, bevor Sie die Daten verwenden.

Hinweis:

Wenn Sie nicht den ADMIN-Benutzer verwenden, stellen Sie sicher, dass der Benutzer über die erforderlichen Berechtigungen für die Vorgänge verfügt. Weitere Informationen finden Sie unter Datenbankbenutzerberechtigungen verwalten.

Externe Daten abfragen

Um Daten in Dateien in der Cloud abzufragen, müssen Sie zunächst die Objektspeicherzugangsdaten in der autonomen Datenbank speichern und dann eine externe Tabelle mit der PL/SQL-Prozedur DBMS_CLOUD.CREATE_EXTERNAL_TABLE erstellen.

Die Quelldatei in diesem Beispiel channels.txt enthält die folgenden Daten: 

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others
  1. Speichern Sie die Zugangsdaten für den Cloud-Objektspeicher mit der Prozedur DBMS_CREDENTIAL.CREATE_CREDENTIAL. Beispiel:
    BEGIN
  DBMS_CREDENTIAL.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@oracle.com',
    password => 'password'
  );
END;
/

    Die Werte, die Sie für username und password angeben, hängen vom verwendeten Cloud-Objektspeicherservice ab:

    • Oracle Cloud Infrastructure Object Storage: username ist Ihr Oracle Cloud Infrastructure-Benutzername, und password ist Ihr Oracle Cloud Infrastructure-Auth-Token. Siehe Mit Authentifizierungstoken arbeiten.

    • Oracle Cloud Infrastructure Object Storage Classic: username ist Ihr Oracle Cloud Infrastructure Classic-Name. password ist Ihr Oracle Cloud Infrastructure Classic-Kennwort.

    Dieser Vorgang speichert die Zugangsdaten in einem verschlüsselten Format in der Datenbank. Sie können einen beliebigen Namen als Zugangsdatenname verwenden. Beachten Sie, dass dieser Schritt nur einmal erforderlich ist, es sei denn, Ihre Objektspeicherzugangsdaten werden geändert. Nachdem Sie die Zugangsdaten gespeichert haben, können Sie denselben Zugangsdatennamen für alle Dataloads verwenden.

  2. Erstellen Sie eine externe Tabelle auf Basis der Quelldateien mit der Prozedur DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

    Die Prozedur DBMS_CLOUD.CREATE_EXTERNAL_TABLE unterstützt externe Dateien in den unterstützten Cloud-Objektspeicherservices. Die Zugangsdaten stellen eine Eigenschaft auf Tabellenebene dar. Daher müssen sich die externen Dateien in demselben Objektspeicher befinden.

    Beispiel:

    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;
/

    Parameter:

    • table_name: Der Name der externen Tabelle.

    • credential_name: Der Name der Zugangsdaten, die im vorherigen Schritt erstellt wurden.

    • file_uri_list: Eine durch Komma getrennte Liste der Quelldateien, die Sie abfragen möchten.

      In diesem Beispiel ist file_uri_list eine Oracle Cloud Infrastructure-Swift-URI, die die Datei channels.txt im Bucket mybucket in der Region us-phoenix-1 angibt. (idthydc0kinr ist der Objektspeicher-Namespace, in dem sich der Bucket befindet.) Informationen zu den unterstützten URI-Formaten finden Sie unter URI-Formate für Cloud-Objektspeicher.

    • format: Definiert die Optionen, die Sie zur Beschreibung des Formats der Quelldatei angeben können. Informationen zu den Formatoptionen, die Sie angeben können, finden Sie unter Formatparameter.

    • column_list: Eine durch Komma getrennte Liste der Spaltendefinitionen in den Quelldateien.

    Jetzt können Sie Abfragen für die externe Tabelle ausführen, die Sie im vorherigen Schritt erstellt haben. Beispiel:

    SELECT count(*) FROM channels_ext;

    Standardmäßig erwartet die Datenbank, dass alle Zeilen in der externen Datendatei gültig sind und sowohl mit den Zieldatentypdefinitionen als auch mit der Formatdefinition der Datei(en) übereinstimmen. Wenn die Quelldateien Zeilen enthalten, die nicht mit den angegebenen Formatoptionen übereinstimmen, gibt die Abfrage einen Fehler aus. Sie können format-Parameteroptionen wie rejectlimit verwenden, um diese Fehler zu unterdrücken. Alternativ können Sie auch die erstellte externe Tabelle validieren, um die Fehlermeldungen und abgelehnten Zeilen anzuzeigen, damit Sie die Formatoptionen entsprechend ändern können. Weitere Informationen finden Sie unter Externe Daten validieren.

    Ausführliche Informationen zu den Parametern finden Sie unter CREATE_EXTERNAL_TABLE-Prozedur.

Externe Daten validieren

Zur Validierung einer externen Tabelle verwenden Sie die Prozedur DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE.

Bevor Sie eine externe Tabelle validieren, müssen Sie die externe Tabelle mit der Prozedur DBMS_CLOUD.CREATE_EXTERNAL_TABLE erstellen. Validieren Sie sie dann mit der Prozedur DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE. Beispiel: 

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

Diese Prozedur scannt die Quelldateien und validiert sie mit den Formatoptionen, die beim Erstellen der externen Tabelle angegeben wurden.

Bei der Validierung werden standardmäßig alle Zeilen in den Quelldateien gescannt. Der Vorgang wird gestoppt, wenn eine Zeile abgelehnt wird. Wenn Sie nur eine Teilmenge der Zeilen validieren möchten, verwenden Sie den Parameter rowcount. Wenn der Parameter rowcount festgelegt ist, scannt der Validierungsvorgang Zeilen und stoppt, wenn eine Zeile abgelehnt wird oder wenn die angegebene Anzahl von Zeilen ohne Fehler validiert wurde.

Beispiel: Der folgende Validierungsvorgang scannt 100 Zeilen und stoppt, wenn eine Zeile abgelehnt wird oder wenn 100 Zeilen ohne Fehler validiert wurden:

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

Wenn die Validierung nicht gestoppt werden soll, wenn eine Zeile abgelehnt wird, und Sie alle abgelehnten Zeilen anzeigen möchten, setzen Sie den Parameter stop_on_error auf FALSE. In diesem Fall scannt VALIDATE_EXTERNAL_TABLE alle Zeilen und meldet alle abgelehnten Zeilen.

Wenn Sie nur eine Teilmenge der Zeilen validieren möchten, verwenden Sie den Parameter rowcount. Wenn rowcount festgelegt und stop_on_error auf FALSE gesetzt ist, scannt der Validierungsvorgang Zeilen und stoppt, wenn die angegebene Anzahl von Zeilen abgelehnt wird oder wenn die angegebene Anzahl von Zeilen ohne Fehler validiert wurde. Beispiel: Im folgenden Beispiel werden 100 Zeilen gescannt. Der Vorgang wird gestoppt, wenn 100 Zeilen abgelehnt oder 100 Zeilen ohne Fehler validiert wurden: 

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

Ausführliche Informationen zu DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE-Parametern finden Sie unter VALIDATE_EXTERNAL_TABLE-Prozedur.

Informationen zu den Ergebnissen von Validierungsvorgängen in den Tabellen dba_load_operations und user_load_operations finden Sie unter Logs für Datenvalidierung anzeigen.

Logs für Datenvalidierung anzeigen

Nachdem Sie eine externe Tabelle validiert haben, können Sie das Ergebnis des Validierungsvorgangs anzeigen, indem Sie eine Tabelle mit Ladevorgängen abfragen:

  • dba_load_operations: Zeigt alle Validierungsvorgänge an.

  • user_load_operations: Zeigt die Validierungsvorgänge in Ihrem Schema an.

Mit diesen Tabellen können Sie Informationen zur Ladevalidierung anzeigen. Beispiel: Verwenden Sie diese SELECT-Anweisung, um user_load_operations abzufragen: 

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

Wenn Sie diese SQL-Anweisung mit der WHERE-Klausel in der Spalte TYPE verwenden, werden alle Ladevorgänge mit dem Typ VALIDATE angezeigt.

In der Spalte LOGFILE_TABLE wird der Name der Tabelle angezeigt, die Sie abfragen können, um das Log eines Validierungsvorgangs anzuzeigen. Beispiel: Die folgende Abfrage zeigt das Log für diesen Validierungsvorgang an: 

SELECT * FROM VALIDATE$21_LOG;

Die Spalte BADFILE_TABLE zeigt den Namen der Tabelle an, die Sie abfragen können, um die Zeilen anzuzeigen, in denen Fehler bei der Validierung aufgetreten sind. Beispiel: Die folgende Abfrage zeigt die abgelehnten Datensätze für den oben genannten Validierungsvorgang an: 

SELECT * FROM VALIDATE$21_BAD;

Je nach den im Log angezeigten Fehlern und den in BADFILE_TABLE angezeigten Zeilen können Sie den Fehler beheben, indem Sie die externe Tabelle mit dem Befehl DROP TABLE löschen und unter Angabe der korrekten Formatoptionen in DBMS_CLOUD.CREATE_EXTERNAL_TABLE neu erstellen.

Hinweis:

Die Tabellen LOGFILE_TABLE und BADFILE_TABLE werden für jeden Validierungsvorgang zwei Tage gespeichert und dann automatisch entfernt.