Unterstützte Formate für semistrukturierte Daten¶

Was ist JSON?¶

JSON (JavaScript-Objektnotation) ist ein einfaches, nur aus Text bestehendes Datenaustauschformat, das auf einer Teilmenge der Programmiersprache JavaScript basiert.

JSON-Daten können von jeder Anwendung erstellt werden. Hier einige allgemeine Beispiele:

• JavaScript-Anwendungen, die zum Generieren von JSON-Daten native Methoden verwenden

• Nicht-JavaScript-Anwendungen, die zum Generieren von JSON-Daten Bibliotheken (meist mit Erweiterungen) verwenden

• Ad-hoc-JavaScript-Generatoren

• Verkettung von JSON-Dokumenten (mit oder ohne Zeilentrennung)

Da es keine formale Spezifikation gibt, gibt es signifikante Unterschiede zwischen verschiedenen Implementierungen. Diese Unterschiede machen den Import von JSON-ähnlichen Datasets unmöglich, wenn der JSON-Parser in seiner Sprachdefinition streng ist. Um den Import von JSON-Datasets so problemlos wie möglich zu gestalten, folgt Snowflake der Regel „Sei liberal in dem, was du akzeptierst“. Die Absicht ist es, den größtmöglichen Bereich von JSON- und JSON-ähnlichen Eingaben zu akzeptieren, die eine eindeutige Interpretation ermöglichen.

Unter diesem Thema wird die Syntax für JSON-Dokumente beschrieben, die von Snowflake akzeptiert werden.

Weitere Informationen zu JSON finden Sie unter json.org.

Grundlegende JSON-Syntax¶

JSON-Daten sind eine hierarchische Sammlung von Name/Wert-Paaren, die in Objekte und Arrays gruppiert sind:

• Doppelpunkte : trennen Namen und Werte in Name/Wert-Paaren.

• Geschweifte Klammern {} kennzeichnen Objekte.

• Eckige Klammern [] kennzeichnen Arrays.

• Kommas , trennen Entitäten in Objekten und Arrays.

Name/Wert-Paare¶

Name/Wert-Paare in JSON bestehen aus einem Feldnamen (in doppelten Anführungszeichen), gefolgt von einem Doppelpunkt und dann einem Wert.

Beispiel:

{"firstName":"John", "empid":45611}

Unterstützte Datentypen¶

Ein Wert in einem Name/Wert-Paar kann sein:

• Eine Zahl (Ganzzahl oder Gleitkommazahl)

• Eine Zeichenfolge (in doppelten Anführungszeichen)

• Ein Boolescher Wert (wahr oder falsch)

• Ein Array (in eckigen Klammern)

• Ein Objekt (in geschweiften Klammern)

• Null

Objekte¶

JSON-Objekte werden in geschweifte Klammern geschrieben. Ein Objekt kann mehrere Name/Wert-Paare enthalten, die durch Kommas getrennt sind. Beispiel:

{"firstName":"John", "lastName":"Doe"}

Arrays¶

JSON-Arrays werden in eckige Klammern geschrieben. Ein Array kann mehrere Objekte enthalten, die durch Kommas getrennt sind. Beispiel:

{"employees":[
    {"firstName":"John", "lastName":"Doe"},
    {"firstName":"Anna", "lastName":"Smith"},
    {"firstName":"Peter", "lastName":"Jones"}
  ]
}

Beispiele für JSON-Dokumente¶

FILE NAME: json_sample_data1

Enthält ein Array mit drei einfachen Mitarbeiterdatensätzen (Objekten):

{"root":[{"employees":[
    {"firstName":"John", "lastName":"Doe"},
    {"firstName":"Anna", "lastName":"Smith"},
    {"firstName":"Peter", "lastName":"Jones"}
]}]}

Copy

FILE NAME: json_sample_data2

Enthält ein Array mit drei Mitarbeiterdatensätzen (Objekte) und den zugehörigen abhängigen Daten (Kinder, Namen und Alter der Kinder, Orte, in denen der Mitarbeiter gelebt hat, und die Jahre, in denen der Mitarbeiter an diesen Orten gelebt hat):

{"root":
   [
    { "kind": "person",
      "fullName": "John Doe",
      "age": 22,
      "gender": "Male",
      "phoneNumber":
        {"areaCode": "206",
         "number": "1234567"},
      "children":
         [
           {
             "name": "Jane",
             "gender": "Female",
             "age": "6"
           },
           {
              "name": "John",
              "gender": "Male",
              "age": "15"
           }
         ],
      "citiesLived":
         [
            {
               "place": "Seattle",
               "yearsLived": ["1995"]
            },
            {
               "place": "Stockholm",
               "yearsLived": ["2005"]
            }
         ]
      },
      {"kind": "person", "fullName": "Mike Jones", "age": 35, "gender": "Male", "phoneNumber": { "areaCode": "622", "number": "1567845"}, "children": [{ "name": "Earl", "gender": "Male", "age": "10"}, {"name": "Sam", "gender": "Male", "age": "6"}, { "name": "Kit", "gender": "Male", "age": "8"}], "citiesLived": [{"place": "Los Angeles", "yearsLived": ["1989", "1993", "1998", "2002"]}, {"place": "Washington DC", "yearsLived": ["1990", "1993", "1998", "2008"]}, {"place": "Portland", "yearsLived": ["1993", "1998", "2003", "2005"]}, {"place": "Austin", "yearsLived": ["1973", "1998", "2001", "2005"]}]},
      {"kind": "person", "fullName": "Anna Karenina", "age": 45, "gender": "Female", "phoneNumber": { "areaCode": "425", "number": "1984783"}, "citiesLived": [{"place": "Stockholm", "yearsLived": ["1992", "1998", "2000", "2010"]}, {"place": "Russia", "yearsLived": ["1998", "2001", "2005"]}, {"place": "Austin", "yearsLived": ["1995", "1999"]}]}
    ]
}

Copy

Was ist Avro?¶

Avro ist ein Open-Source-Datenserialisierungs- und RPC-Framework, das ursprünglich für die Verwendung mit Apache Hadoop entwickelt wurde. Es verwendet Schemas, die in JSON definiert sind, um serialisierte Daten in einem kompakten Binärformat zu erstellen. Die serialisierten Daten können an jedes beliebige Ziel (z. B. Anwendung oder Programm) gesendet und am Ziel einfach deserialisiert werden, da das Schema in den Daten enthalten ist.

Ein Avro-Schema besteht aus einer Zeichenfolge, einem Objekt oder einem Array im JSON-Format, das den Typ des Schemas und die Datenattribute (Feldnamen, Datentypen usw.) für den Schematyp definiert. Die Attribute unterscheiden sich je nach Schematyp. Es werden komplexe Datentypen wie Arrays und Zuordnungen unterstützt.

Snowflake liest Avro-Daten in eine einzige VARIANT-Spalte. Sie können die Daten in einer VARIANT-Spalte genauso wie JSON-Daten abfragen und dabei ähnliche Befehle und Funktionen verwenden.

Weitere Informationen dazu finden Sie unter avro.apache.org.

Beispiel für ein Avro-Schema¶

{
 "type": "record",
 "name": "person",
 "namespace": "example.avro",
 "fields": [
     {"name": "fullName", "type": "string"},
     {"name": "age",  "type": ["int", "null"]},
     {"name": "gender", "type": ["string", "null"]}
     ]
}

Was ist ORC?¶

ORC (Optimized Row Columnar) ist ein Binärformat, das zur Speicherung von Hive-Daten verwendet wird. ORC wurde gegenüber früheren Hive-Dateiformaten für eine effiziente Komprimierung und bessere Leistung beim Lesen, Schreiben und Verarbeiten von Daten entwickelt. Weitere Informationen zu ORC finden Sie unter https://orc.apache.org/.

Snowflake liest ORC-Daten in eine einzige VARIANT-Spalte. Sie können die Daten in einer VARIANT-Spalte genauso wie JSON-Daten abfragen und dabei ähnliche Befehle und Funktionen verwenden.

Alternativ können Sie ausgewählte Spalten einer ORC-Stagingdatei mit einer CREATE TABLE AS SELECT-Anweisung in separate Tabellenspalten extrahieren.

Beispiel für ORC-Daten, die in eine VARIANT-Spalte geladen werden.¶

+--------------------------------------+
| SRC                                  |
|--------------------------------------|
| {                                    |
|   "boolean1": false,                 |
|   "byte1": 1,                        |
|   "bytes1": "0001020304",            |
|   "decimal1": 12345678.654745,       |
|   "double1": -1.500000000000000e+01, |
|   "float1": 1.000000000000000e+00,   |
|   "int1": 65536,                     |
|   "list": [                          |
|     {                                |
|       "int1": 3,                     |
|       "string1": "good"              |
|     },                               |
|     {                                |
|       "int1": 4,                     |
|       "string1": "bad"               |
|     }                                |
|   ]                                  |
| }                                    |
+--------------------------------------+

Was ist Parquet?¶

Parquet ist eine komprimierte, effiziente spaltenweise Datenrepräsentation, die für Projekte im Hadoop-Ökosystem entwickelt wurde. Das Dateiformat unterstützt komplexe verschachtelte Datenstrukturen und die Verwendung der Zerlegungs- und Rekonstruktionsalgorithmen von Dremel. Parquet-Dateien können nicht mit einem Texteditor geöffnet werden. Weitere Informationen dazu finden Sie unter parquet.apache.org/docs/.

Abhängig von Ihrem Anwendungsfall beim Laden liest Snowflake entweder Parquet-Daten in eine einzelne VARIANT-Spalte oder direkt in Tabellenspalten (z. B. wenn Sie Daten aus Iceberg-kompatiblen Parquet-Dateien laden).

Sie können die Daten in einer VARIANT-Spalte genauso wie JSON-Daten abfragen und dabei ähnliche Befehle und Funktionen verwenden. Alternativ können Sie ausgewählte Spalten einer bereitgestellten Parquet-Datei mit einer CREATE TABLE AS SELECT-Anweisung in separate Tabellenspalten extrahieren.

Beispiel für Parquet-Daten, die in eine VARIANT-Spalte geladen werden.¶

+------------------------------------------+
| SRC                                      |
|------------------------------------------|
| {                                        |
|   "continent": "Europe",                 |
|   "country": {                           |
|     "city": {                            |
|       "bag": [                           |
|         {                                |
|           "array_element": "Paris"       |
|         },                               |
|         {                                |
|           "array_element": "Nice"        |
|         },                               |
|         {                                |
|           "array_element": "Marseilles"  |
|         },                               |
|         {                                |
|           "array_element": "Cannes"      |
|         }                                |
|       ]                                  |
|     },                                   |
|     "name": "France"                     |
|   }                                      |
| }                                        |
+------------------------------------------+

Was ist XML?¶

XML (eXtensible Markup Language) ist eine Markup-Sprache, die eine Menge von Regeln für die Codierung von Dokumenten definiert. Ursprünglich basierte es auf SGML, einer weiteren Markup-Sprache, die zur Standardisierung von Strukturen und Elementen entwickelt wurde, aus denen ein Dokument besteht.

Seit seiner Einführung ist XML über den anfänglichen Fokus auf Dokumente hinausgewachsen und umfasst ein breites Anwendungsspektrum, einschließlich der Darstellung beliebiger Datenstrukturen und als Basissprache für Kommunikationsprotokolle. Aufgrund ihrer Erweiterbarkeit, Vielseitigkeit und Benutzerfreundlichkeit hat sich XML zu einem der am häufigsten verwendeten Standards für den Datenaustausch im Internet entwickelt.

Ein XML-Dokument besteht im Wesentlichen aus den folgenden Konstrukten:

• Tags (gekennzeichnet durch spitze Klammern, < und >)

• Elemente

Elemente bestehen in der Regel aus einem „Start“-Tag und einem passenden „End“-Tag, wobei der Text zwischen den Tags den Inhalt des Elements ausmacht. Ein Element kann auch aus einem „empty-element“-Tag ohne „End“-Tag bestehen. die Tags „Start“ und „empty-element“ können Attribute enthalten, mit denen die Eigenschaften oder Metadaten des Elements definiert werden.

Wenn Sie XML-Daten abfragen, gibt der Dollarzeichen-Operator ($) den Inhalt des Wertes, auf den er wirkt, als VARIANT-Wert zurück. Für ein Element wird der Inhalt dieses Elements zurückgegeben:

• Wenn das Element Text enthält, wird Text als VARIANT-Wert zurückgegeben.

• Wenn das Element ein anderes Element enthält, wird das Element als VARIANT-Wert im XML-Format zurückgegeben.

• Wenn das Element eine Reihe von Elementen enthält, wird ein Array der Elemente als VARIANT-Wert im JSON-Format zurückgegeben.

Verwenden Sie die folgenden Operatoren, um auf den VARIANT-Wert in einer Abfrage zuzugreifen:

• $ für den Inhalt des Wertes.

• @ für den Namen des Wertes. Dieser Operator ist nützlich, wenn Sie durch Elemente mit unterschiedlichen Namen iterieren.

  Verwenden Sie @attribute_name für den Inhalt eines benannten Attributs. Für @attr lautet der Attributname zum Beispiel attr. Die Abfrage gibt den Inhalt des Attributs mit dem Namen zurück, der direkt auf das kaufmännische Und folgt. Wenn kein Attribut gefunden wird, wird NULL zurückgegeben.

Für Beispiele, die XML-Daten abfragen, siehe Beispiele für die Abfrage von XML-Daten.

Sie können die folgenden Funktionen verwenden, um mit XML-Daten zu arbeiten:

• CHECK_XML

• PARSE_XML

• TO_XML

• XMLGET

Beispiele für die Arbeit mit XML¶

Die folgenden Beispiele zeigen Ihnen, wie Sie die XML-Daten laden und abfragen können.

<?xml version="1.0"?>
<!DOCTYPE parts system "parts.dtd">
<?xml-stylesheet type="text/css" href="xmlpartsstyle.css"?>
<parts>
   <part count="4">
      <item>Spark Plugs</item>
      <partnum>A3-400</partnum>
      <manufacturer>ABC company</manufacturer>
      <price units="dollar"> 27.00</price>
   </part>
   <part count="1">
      <item>Motor Oil</item>
      <partnum>B5-200</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 14.00</price>
   </part>
   <part count="1">
      <item>Motor Oil</item>
      <partnum>B5-300</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 16.75</price>
   </part>
   <part count="1">
      <item>Engine Coolant</item>
      <partnum>B6-120</partnum>
       <source>XYZ company</source>
      <price units="dollar"> 19.00</price>
   </part>
   <part count="1">
      <item>Engine Coolant</item>
      <partnum>B6-220</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 18.25</price>
   </part>
</parts>

SELECT src FROM sample_xml_parts;

+----------------------------------------------+
| SRC                                          |
|----------------------------------------------|
| <parts>                                      |
|   <part count="4">                           |
|     <item>Spark Plugs</item>                 |
|     <partnum>A3-400</partnum>                |
|     <manufacturer>ABC company</manufacturer> |
|     <price units="dollar">27.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Motor Oil</item>                   |
|     <partnum>B5-200</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">14.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Motor Oil</item>                   |
|     <partnum>B5-300</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">16.75</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Engine Coolant</item>              |
|     <partnum>B6-120</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">19.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Engine Coolant</item>              |
|     <partnum>B6-220</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">18.25</price>      |
|   </part>                                    |
| </parts>                                     |
+----------------------------------------------+

SELECT src:"$" FROM sample_xml_parts;

+--------------------------------+
| SRC:"$"                        |
|--------------------------------|
| [                              |
|   {                            |
|     "$": [                     |
|       {                        |
|         "$": "Spark Plugs",    |
|         "@": "item"            |
|       },                       |
|       {                        |
|         "$": "A3-400",         |
|         "@": "partnum"         |
|       },                       |
|       {                        |
|         "$": "ABC company",    |
|         "@": "manufacturer"    |
|       },                       |
|       {                        |
|         "$": 27,               |
|         "@": "price",          |
|         "@units": "dollar"     |
|       }                        |
|     ],                         |
|     "@": "part",               |
|     "@count": 4,               |
|     "item": 0,                 |
|     "manufacturer": 2,         |
|     "partnum": 1,              |
|     "price": 3                 |
|   },                           |
|   {                            |
|     "$": [                     |
|       {                        |
|         "$": "Motor Oil",      |
|         "@": "item"            |
|       },                       |
|       {                        |
|         "$": "B5-200",         |
|         "@": "partnum"         |
|       },                       |
|       {                        |
|         "$": "XYZ company",    |
|         "@": "source"          |
|       },                       |
|       {                        |
|         "$": 14,               |
|         "@": "price",          |
|         "@units": "dollar"     |
|       }                        |
|     ],                         |
|     "@": "part",               |
|     "@count": 1,               |
|     "item": 0,                 |
|     "partnum": 1,              |
|     "price": 3,                |
|     "source": 2                |
|   },                           |
|                                |
|              ...               |
|                                |
+--------------------------------+

SELECT src:"@" FROM sample_xml_parts;

+---------+
| SRC:"@" |
|---------|
| "parts" |
+---------+

SELECT src:"$"[0]."@count", src:"$"[1]."@count" FROM sample_xml_parts;

+---------------------+---------------------+
| SRC:"$"[0]."@COUNT" | SRC:"$"[1]."@COUNT" |
|---------------------+---------------------|
| 4                   | 1                   |
+---------------------+---------------------+

SELECT XMLGET(src, 'part') FROM sample_xml_parts;

SELECT XMLGET(src, 'part', 0) FROM sample_xml_parts;

+--------------------------------------------+
| XMLGET(SRC, 'PART')                        |
|--------------------------------------------|
| <part count="4">                           |
|   <item>Spark Plugs</item>                 |
|   <partnum>A3-400</partnum>                |
|   <manufacturer>ABC company</manufacturer> |
|   <price units="dollar">27.00</price>      |
| </part>                                    |
+--------------------------------------------+

SELECT XMLGET(src, 'part', 3) FROM sample_xml_parts;

+---------------------------------------+
| XMLGET(SRC, 'PART', 3)                |
|---------------------------------------|
| <part count="1">                      |
|   <item>Engine Coolant</item>         |
|   <partnum>B6-120</partnum>           |
|   <source>XYZ company</source>        |
|   <price units="dollar">19.00</price> |
| </part>                               |
+---------------------------------------+

SELECT XMLGET(VALUE, 'item'):"$"::VARCHAR AS item,
       XMLGET(VALUE, 'partnum'):"$"::VARCHAR AS partnum,
       COALESCE(XMLGET(VALUE, 'manufacturer'):"$"::VARCHAR,
                XMLGET(VALUE, 'source'):"$"::VARCHAR) AS manufacturer_or_source,
       XMLGET(VALUE, 'price'):"$"::VARCHAR AS price,
  FROM sample_xml_parts,
    LATERAL FLATTEN(INPUT => SRC:"$");

+----------------+---------+------------------------+-------+
| ITEM           | PARTNUM | MANUFACTURER_OR_SOURCE | PRICE |
|----------------+---------+------------------------+-------|
| Spark Plugs    | A3-400  | ABC company            | 27    |
| Motor Oil      | B5-200  | XYZ company            | 14    |
| Motor Oil      | B5-300  | XYZ company            | 16.75 |
| Engine Coolant | B6-120  | XYZ company            | 19    |
| Engine Coolant | B6-220  | XYZ company            | 18.25 |
+----------------+---------+------------------------+-------+