Warum erhalte ich Fehler, wenn ich versuche, JSON-Daten in Amazon Athena zu lesen?

Lesedauer: 4 Minute

Wenn ich versuche, JSON-Daten in Amazon Athena zu lesen, erhalte ich NULL- oder Falsche-Daten-Fehler.

Behebung

Informieren Sie sich über die folgenden häufig auftretenden Probleme:

Verwenden Sie das richtige JSON SerDe

Athena verwendet eines der folgenden JSON SerDes, um JSON zu verarbeiten:

Wenn Sie eine Abfrage vom Typ INSERT INTO für die erstellte Tabelle ausführen müssen, verwenden Sie das Hive JSON SerDe.

Wenn Sie das OpenX JSON SerDe verwenden, können Sie falsch formatierte Datensätze ignorieren, um die Dateien zu identifizieren, welche die Fehler verursachen. Im folgenden Beispiel werden falsch formatierte Datensätze als NULL zurückgegeben, wenn ignore.malformed.json auf true gesetzt ist:

CREATE EXTERNAL TABLE json (    id int,
    name string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/';

Fragen Sie die neue Tabelle ab, um die Dateien mit fehlerhaften Datensätzen zu identifizieren, die dem folgenden ähneln:

SELECT "$PATH", * FROM your_table where your_column is NULL

Hinweis: Wenn Sie Athena-Abfragen für Pretty-Print-JSON-Dateien ausführen, können Sie das Amazon Ion Hive SerDe verwenden. Das Amazon Ion Hive SerDe erwartet nicht, dass sich jede Datenzeile in einer einzigen Zeile befindet. Verwenden Sie diese Funktion, um JSON-Datensätze abzufragen, die ein Pretty-Print-Format haben, oder unterteilen Sie die Felder in einer Reihe mit Zeilenumbruchzeichen.

Verwenden Sie eine Zeile pro Datensatz

Die folgenden JSON-Datensätze sind korrekt formatiert und können von allen drei JSON SerDes gelesen werden:

{ "id" : 50, "name":"John" }
{ "id" : 51, "name":"Jane" }
{ "id" : 53, "name":"Jill" }

Die folgenden JSON-Datensätze können nur vom Amazon Ion Hive SerDe gelesen werden:

{  "id" : 50,
  "name":"John"
}
{
  "id" : 51,
  "name":"Jane"
}
{
  "id" : 53,
  "name":"Jill"
}

Die folgenden JSON-Daten enthalten mehrere Datensätze in einer einzigen Zeile und sind falsch formatiert:

{ "id" : 50, "name":"John" } { "id" : 51, "name":"Jane" } { "id" : 53, "name":"Jill" }

Verwenden Sie in jeder Spalte den richtigen Datentyp

Die zweite Zeile im folgenden Beispiel enthält einen falschen Datentyp für „Alter“. Der Spaltenwert sollte „11" statt „elf“ sein. Dies führt zu der folgenden Fehlermeldung: HIVE_BAD_DATA: Error parsing field value ‚eleven‘ for field 1: For input string: „eleven“.

{"name":"Patrick","age":35,"address":"North Street"}
{"name":"Carlos","age":"eleven","address":"Flowers Street"}
{"name":"Fabiana","age":22,"address":"Main Street"}

Verwenden Sie die richtige Erweiterung für komprimierte JSON-Dateien

Wenn Sie eine komprimierte JSON-Datei verwenden, muss die Datei im Format „.gz“ enden. Beispielsweise sind „myfile.json.gz“ oder "myfile.gz" korrekt formatierte Erweiterungen.

Verwenden Sie Spalten, bei denen die Groß- und Kleinschreibung nicht beachtet wird, oder setzen Sie die Eigenschaft case.insensitive auf false

Athena unterscheidet standardmäßig nicht zwischen Groß- und Kleinschreibung. Wenn Sie Spaltennamen haben, die sich je nach Groß- und Kleinschreibung unterscheiden, zum Beispiel „Spalte“ und „spalte“, generiert Athena den folgenden Fehler:

„HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key“

Außerdem sind Ihre Daten in Athena nicht sichtbar. Um diesen Fehler zu vermeiden, generieren Sie Ihre Daten mit Spalten, bei denen die Groß- und Kleinschreibung nicht berücksichtigt wird.

Wenn Sie OpenX SerDe verwenden, verwenden Sie Schlüsselnamen, bei denen zwischen Groß- und Kleinschreibung unterschieden wird. Setzen Sie dazu die Eigenschaft case.insensitive von SerDe auf false und fügen Sie eine Zuordnung für den Schlüssel in Großbuchstaben hinzu. Um beispielsweise Spalten in Groß- und Kleinschreibung wie folgt zu verwenden:

{"Username": "bob1234", "username": "bob" }

Verwenden Sie diese SerDe-Eigenschaften:

CREATE external TABLE casesensitive_json (user_name String,username String)
ROW FORMAT serde 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'mapping.user_name' = 'Username','case.insensitive'='false')
LOCATION 's3://awsexamplebucket/';

Wenn die JSON-Datei doppelte Spaltennamen enthält, bei denen zwischen Groß- und Kleinschreibung unterschieden wird, aktualisieren Sie die Datei, um die doppelten Spalten zu entfernen:

{"username": "bob1234", "username": "bob" }

Stellen Sie sicher, dass alle Zeilen in der JSON-SerDe-Tabelle im JSON-Format vorliegen

Gehen Sie wie folgt vor, um herauszufinden, ob JSON-Zeilen oder Dateinamen in der Athena-Tabelle nicht gültig sind:

Erstellen Sie eine Tabelle mit einem Trennzeichen, das in den Eingabedateien nicht vorhanden ist. Führen Sie einen Befehl ähnlich dem folgenden aus:

CREATE EXTERNAL TABLE IF NOT EXISTS json_validator (jsonrow string) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '%'
location 's3://awsexamplebucket/';

Führen Sie eine Abfrage ähnlich der folgenden aus, um den Dateinamen, die Zeilendetails und den Amazon-S3-Pfad für die JSON-Zeilen anzuzeigen, die ungültig sind:

WITH testdataset AS (SELECT "$path" s3path,jsonrow,try(json_parse(jsonrow)) isjson FROM json_validator)
SELECT * FROM testdataset WHERE ISJSON IS NULL;