JSON-Daten mit externen Tabellen in Object Storage abfragen

Beispiel 1: Zeilengetrennte JSON-Dokumente als JSON-Objekte abfragen

In diesem Beispiel wird gezeigt, wie Sie eine JSON-Datei abfragen, die mehrere zeilenbegrenzte JSON-Dokumente im Objektspeicher enthält.

Eine JSON-Datei ist beispielsweise fruitLineDelimited.json im Objektspeicher vorhanden, der drei Zeilen mit einem Objekt pro Zeile enthält.

{ "name": "apple", "count": 20 }
{ "name": "orange", "count": 42 }
{ "name": "pear", "count": 10 }

1. Erstellen Sie eine externe Tabelle in Ihrer Datenbank mit der JSON-Datei.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_1',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitLineDelimited.json';
   END;
   /
   

   Die Parameter sind:

   • table_name: Der Name der externen Tabelle.

   • credential_name: Der Name der Zugangsdaten, die im vorherigen Schritt erstellt wurden. Der Parameter credential_name muss den Benennungskonventionen von Oracle-Objekten entsprechen. Weitere Informationen finden Sie unter Benennungsregeln für Datenbankobjekte.

   • format: Definiert die Optionen, die Sie zur Beschreibung des Formats der Quelldatei angeben können.

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

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * FROM fruit_1;

Beispiel 2: JSON-Arrays als JSON-Objekte abfragen

In diesem Beispiel wird gezeigt, wie Sie ein einzelnes Array von JSON-Objekten abfragen. Arrays werden standardmäßig nicht komprimiert.

Eine JSON-Datei, z.B. fruitArray.json, ist im Objektspeicher vorhanden und enthält die folgenden Daten.

[
   {
      "name" : "apple",
      "count": 20
   },
   {
      "name" : "orange",
      "count": 42
   },
   {
      "name" : "pear",
      "count": 10
   }
]

1. Erstellen Sie eine externe Tabelle in Ihrer Datenbank mit der JSON-Datei.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_2a',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
   END;
   /
   

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * FROM fruit_2a;

3. Mit der Option jsonpath können Sie die Arraystruktur beibehalten und ein Array mit JSON-Objekten zurückgeben.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_2b',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc', 'jsonpath' value '$'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
   END;
   /
   

4. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * FROM fruit_2b;

Beispiel 3: Von einem Abfrageobjekt umschlossene JSON-Arrays als JSON-Objekte

In diesem Beispiel wird gezeigt, wie Sie eine JSON-Datei abfragen, die in einem äußeren JSON-Dokument gewrappt ist. In diesem Beispiel geben Sie einen Pfad mit der Formatoption jsonpath für die Daten an, die Sie laden möchten. Der Pfad muss zu einem Array führen. Die Zeilen werden wie im vorherigen Beispiel zugeordnet.

Eine JSON-Datei, z.B. fruitEmbeddedArray.json, ist im Objektspeicher vorhanden und enthält die folgenden Daten:

{
  "last_updated": 1434054678,
  "ttl": 0,
  "version": "1.0",
  "fruit": [
    { "name" : "apple", "count": 20 },
    { "name" : "orange", "count": 42 },
    { "name" : "pear", "count": 10 }
  ]
}

1. Erstellen Sie eine externe Tabelle in Ihrer Datenbank mit der JSON-Datei.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_3',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc', 'jsonpath' value '$.fruit[*]'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEmbeddedArray.json';
   END;
   /
   

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * FROM fruit_3;

Beispiel 4: Erweiterte JSON-(EJSON-)Dokumente als JSON-Objekte abfragen

In diesem Beispiel wird gezeigt, wie Sie eine EJSON-Datei abfragen. Der SQL-Typ "JSON" kann erweiterte JSON-Typen wie TIMESTAMP, DOUBLE, FLOAT und RAW darstellen. Der JSON-Text kann erweiterte JSON-Typen mit dem erweiterten JSON-Format darstellen. Diese EJSON-Annotationen werden automatisch in die entsprechenden Typen konvertiert.

Eine EJSON-Datei, z.B. fruitEjson.json, ist im Objektspeicher vorhanden und enthält die folgenden Daten:

{ "name" : "apple", "count": 20, "modified":{"$date":"2020-06-29T11:53:05.439Z"} }
{ "name" : "orange", "count": 42 }
{ "name" : "pear", "count": 10 }

1. Erstellen Sie eine externe Tabelle in Ihrer Datenbank mit der JSON-Datei.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_4',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEjson.json';
   END;
   /
   

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * FROM fruit_4;

Beispiel 5: Einzelne JSON-Dokumente mit mehrzeiligen Dateien als JSON-Objekt abfragen

In diesem Beispiel wird gezeigt, wie Sie ein einzelnes JSON-Dokument mit mehrzeiligen Dateien abfragen.

• Ein einzelnes JSON-Dokument mit mehrzeiligen Dateien kann einer Tabelle zugeordnet werden, wobei jede JSON-Datei im Verzeichnis einer einzelnen Zeile zugeordnet ist.

• Ein einzelnes JSON-Dokument mit mehrzeiligen Dateien kann ein Verzeichnis mit JSON-Dateien sein, in dem jede JSON-Datei einer einzelnen Zeile in der Tabelle zugeordnet ist.

In diesem Beispiel werden JSON-Dateien mit den folgenden Daten verwendet:

fruitMultiLineFile1.json

{
   "name" : "apple",
   "count": 42
}

fruitMultiLineFile2.json

{
   "name" : "orange",
   "count": 5
}

fruitMultiLineFile3.json

{
   "name" : "pear",
   "count": 10
}

1. Erstellen Sie mit diesen JSON-Dateien eine externe Tabelle in Ihrer Datenbank.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_5',
       credential_name =>'DEF_CRED_NAME',
       format => json_object('type' value 'jsondoc'),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
   END;
   /
   

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * FROM fruit_5;

Wichtige Hinweise beim Zugriff auf JSON-Dokumente

• Definieren Sie die Option delimiter für das Feld in einem Wert oder Zeichen, der nicht in Ihren JSON-Daten enthalten ist. Das heißt, der Feldbegrenzungswert ist in Ihren JSON-Dokumenten nicht vorhanden. Wenn Sie eine Tabelle mit dem Package DBMS_CLOUD erstellen, ist das Standardfeldbegrenzungszeichen "|" (Pipe).

  Betrachten wir ein Szenario, in dem JSON-Daten "|" enthalten. Beispiel:

  {"attr1": "oK4IJ|V", "attr2": "igN”}

  Wenn das Feldbegrenzungszeichen nicht angegeben ist, wird "|" als Feldbegrenzungszeichen betrachtet, und eine Abfrage gibt Folgendes zurück.

  {"attr1": "oK4IJ

  Um sicherzustellen, dass Ihre JSON-Dokumente nicht versehentlich geteilt werden, setzen Sie die Formatoption für Feldbegrenzungszeichen auf einen Wert, der nicht in den Daten enthalten ist, und setzen Sie die Daten in ungültige JSON-Dokumente. Beispiel:

  json_object('delimiter' value 'X''0''')

• Wenn Ihre JSON-Dokumente als linienbegrenzte Daten für einzelne Dokumente gespeichert werden, werden sie ohne Probleme als Textdaten verarbeitet. Bei einem JSON-Dokument, das sich über mehrere Zeilen in einer externen Datei erstreckt, müssen Sie die Formatoption recorddelimiter auf einen Wert oder ein Zeichen setzen, der/das in der Datendatei nicht vorhanden ist. Beispiel:

  json_object('recorddelimiter' value '0x''1B''')

  Wenn die Option recorddelimiter nicht festgelegt ist, wird jede Zeile als einzelner Datensatz betrachtet. Das Standard-Datensatztrennzeichen newline rendert die Daten in ungültigen JSON-Dokumenten.

• Wenn in einer externen Datendatei ein Feld nicht explizit angegeben ist, wird der Standarddatentyp auf CHAR(255) gesetzt. Wenn Sie also JSON-Dokumente mit mehr als 255 Zeichen lesen, wird ein Fehler zurückgegeben. Daher müssen Sie den Parameter field_list auf einen Wert setzen, der höher ist als das größte JSON-Dokument in Ihren Dateien. Beispiel:

  field_list =>'"MYDATA" char(10000)'

• Die Standardgröße des Lesepuffers in Autonomous Database ist 10 MB. Bei JSON-Dokumenten mit mehr als 10 MB müssen Sie die Formatoption readsize auf einen höheren Wert setzen, z.B. 20 MB.

  json_object('readsize' value '20000000')

Im Folgenden finden Sie eine Beispieldefinition für eine externe Tabelle, die alle diese Parameter enthält.

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_5',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
END;
/

Die folgenden Beispiele zeigen, wie Sie mit Oracle Database 19c oder früher mit dem Datentyp CLOB auf JSON-Dokumente als Textdaten zugreifen. Alternativ können Sie den Datentyp VARCHAR2 je nach Größe Ihrer JSON-Dokumente verwenden.

• Durch Zeilen getrennte JSON-Dokumente

• JSON-Arrays

• Objekt-gewrappte JSON-Arrays

• Einzelnes JSON-Dokument mit mehrzeiligen Dateien

Beispiel 1: Zeilengetrennte JSON-Dokumente als Textdaten abfragen

In diesem Beispiel wird gezeigt, wie Sie eine JSON-Datei abfragen, die mehrere zeilenbegrenzte JSON-Dokumente im Objektspeicher enthält.

Eine JSON-Datei ist beispielsweise fruitLineDelimited.json im Objektspeicher vorhanden, der drei Zeilen mit einem Objekt pro Zeile enthält.

{ "name": "apple", "count": 20 }
{ "name": "orange", "count": 42 }
{ "name": "pear", "count": 10 }

1. Erstellen Sie eine externe Tabelle in Ihrer Datenbank mit der JSON-Datei.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_6',
       column_list => 'mydata clob',
       credential_name =>'DEF_CRED_NAME',
       field_list =>'"MYDATA" char(10000)',
       format => json_object('delimiter' value 'X''0'''),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitLineDelimited.json';
   END;
   /
   

   Die Parameter sind:

   • table_name: Der Name der externen Tabelle.

   • column_list: ist eine durch Komma getrennte Liste der Spaltennamen und Datentypen für die externe Tabelle. Die Liste enthält die Spalten innerhalb der Datendatei und die Spalten, die aus dem Objektnamen abgeleitet werden (aus den Namen im Dateipfad, der mit file_uri_list angegeben wird).

   • credential_name: Der Name der Zugangsdaten, die im vorherigen Schritt erstellt wurden. Der Parameter credential_name muss den Benennungskonventionen von Oracle-Objekten entsprechen. Weitere Informationen finden Sie unter Benennungsregeln für Datenbankobjekte.

   • field_list: Identifiziert die Felder in den Quelldateien und deren Datentypen.

   • format: Definiert die Optionen, die Sie zur Beschreibung des Formats der Quelldatei angeben können.

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

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * FROM fruit_6;

Beispiel 2: JSON-Arrays als Textdaten abfragen

In diesem Beispiel wird gezeigt, wie Sie ein einzelnes Array von JSON-Objekten abfragen. Sie können Arrays nicht automatisch als Teil des Datenzugriffs auf externe Tabellen entpacken. Sie können das Entpacken jedoch ausführen, während Sie auf die Daten zugreifen.

Eine JSON-Datei, z.B. fruitArray.json, ist im Objektspeicher vorhanden und enthält die folgenden Daten.

[
   {
      "name" : "apple",
      "count": 20
   },
   {
      "name" : "orange",
      "count": 42
   },
   {
      "name" : "pear",
      "count": 10
   }
]

1. Erstellen Sie eine externe Tabelle in Ihrer Datenbank mit der JSON-Datei.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_7',
       column_list => 'mydata clob',
       credential_name =>'DEF_CRED_NAME',
       field_list =>'"MYDATA" char(10000)',
       format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
   END;
   /
   

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * 
   FROM fruit_7
   NESTED mydata 
   COLUMNS (nested path '$[*]' 
   COLUMNS (data format json path '$'));

Beispiel 3: Von Abfrageobjekt umschlossene JSON-Arrays als Textdaten

In diesem Beispiel wird gezeigt, wie Sie eine JSON-Datei abfragen, die in einem äußeren JSON-Dokument gewrappt ist. Sie können Arrays nicht automatisch als Teil des Datenzugriffs auf die externe Tabelle entpacken. Sie können jedoch die Entpackung und JSON-Dokumentteilmenge ausführen, während Sie auf die Daten zugreifen.

Eine JSON-Datei, z.B. fruitEmbeddedArray.json, ist im Objektspeicher vorhanden und enthält die folgenden Daten:

{
  "last_updated": 1434054678,
  "ttl": 0,
  "version": "1.0",
  "fruit": [
    { "name" : "apple", "count": 20 },
    { "name" : "orange", "count": 42 },
    { "name" : "pear", "count": 10 }
  ]
}

1. Erstellen Sie eine externe Tabelle in Ihrer Datenbank mit der JSON-Datei.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_8',
       column_list => 'mydata clob',
       credential_name =>'DEF_CRED_NAME',
       field_list =>'"MYDATA" char(10000)',
       format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEmbeddedArray.json';
   END;
   /
   

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * 
   FROM fruit_8 
   NESTED mydata 
   COLUMNS (nested path '$.fruit[*]' 
   COLUMNS (data format json path '$'));
   

Beispiel 4: Einzelne JSON-Dokumente mit mehrzeiligen Dateien als Textdaten abfragen

In diesem Beispiel wird gezeigt, wie Sie ein einzelnes JSON-Dokument mit mehrzeiligen Dateien abfragen.

• Ein einzelnes JSON-Dokument mit mehrzeiligen Dateien kann einer Tabelle zugeordnet werden, wobei jede JSON-Datei im Verzeichnis einer einzelnen Zeile zugeordnet ist.

• Ein einzelnes JSON-Dokument mit mehrzeiligen Dateien kann ein Verzeichnis mit JSON-Dateien sein, in dem jede JSON-Datei einer einzelnen Zeile in der Tabelle zugeordnet ist.

In diesem Beispiel werden JSON-Dateien mit den folgenden Daten verwendet:

fruitMultiLineFile1.json

{
   "name" : "apple",
   "count": 42
}

fruitMultiLineFile2.json

{
   "name" : "orange",
   "count": 5
}

fruitMultiLineFile3.json

{
   "name" : "pear",
   "count": 10
}

1. Erstellen Sie mit diesen JSON-Dateien eine externe Tabelle in Ihrer Datenbank.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'fruit_9',
       column_list => 'mydata clob',
       credential_name =>'DEF_CRED_NAME',
       field_list =>'"MYDATA" char(10000)',
       format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
   END;
   /
   

2. Fragen Sie die externe Tabelle mit der Anweisung SELECT ab.

   SELECT * FROM fruit_9;