架构不一致

不一致的架构是指某些行中的值具有不同的数据类型。假设我们有两个json文件

// inside s3://path/to/bad.json
{"name":"1Patrick","age":35}
{"name":"1Carlos","age":"eleven"}
{"name":"1Fabiana","age":22}

// inside s3://path/to/good.json
{"name":"2Patrick","age":35}
{"name":"2Carlos","age":11}
{"name":"2Fabiana","age":22}

然后简单的查询SELECT * FROM some_table将失败，并显示

  

HIVE_BAD_DATA：解析字段1的字段值“十一”时出错：对于输入字符串：“十一”

但是，我们可以在WHERE子句中排除该文件

SELECT 
    "$PATH" AS "source_s3_file",* 
FROM some_table 
WHERE "$PATH" != 's3://path/to/bad.json'

结果：

        source_s3_file | name     | age
---------------------------------------
s3://path/to/good.json | 1Patrick | 35
s3://path/to/good.json | 1Carlos  | 11
s3://path/to/good.json | 1Fabiana | 22

当然，当我们知道哪些文件损坏时，这是最好的情况。但是，您可以采用这种方法来手动地推断哪些文件是好的。您还可以使用LIKE或regexp_like一次遍历多个文件。

SELECT 
    COUNT(*)
FROM some_table 
WHERE regexp_like("$PATH",'s3://path/to/go[a-z]*.json')
-- If this query doesn't fail,that those files are good.

这种方法的明显缺点是执行查询的成本和所花费的时间，尤其是逐个文件完成时。

格式错误的记录

在AWS Athena眼中，好的记录是那些格式化为每行一个JSON的记录：

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

AWS Athena支持OpenX JSON SerDe库，该库可以设置为通过指定将格式错误的记录评估为NULL

-- When you create table
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')

创建表时。因此，以下查询将显示记录格式错误的文件：

SELECT 
    DISTINCT("$PATH")
FROM "some_database"."some_table" 
WHERE(
    col_1 IS NULL AND 
    col_2 IS NULL AND 
    col_3 IS NULL
    -- etc
)

注意：如果您100％确信除损坏的行以外，它不包含空字段，则只能使用一个col_1 IS NULL。

通常，只要'ignore.malformed.json' = 'true'，格式错误的记录就没什么大不了的。例如，以下查询仍将成功 例如，如果文件包含：

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

以下查询仍将成功

SELECT 
    "$PATH" AS "source_s3_file",*
FROM some_table

结果：

              source_s3_file |     name | age | address
-----------------------------|----------|-----|-------------
1 s3://path/to/malformed.json| 2Patrick | 35  | North Street
2 s3://path/to/malformed.json|          |     |
3 s3://path/to/malformed.json|          |     |
4 s3://path/to/malformed.json|          |     |
5 s3://path/to/malformed.json|          |     |
6 s3://path/to/malformed.json|          |     |
7 s3://path/to/malformed.json| 2Fabiana | 22  | Main Street

使用'ignore.malformed.json' = 'false'（这是默认行为）时，完全相同的查询将引发错误

  

HIVE_CURSOR_ERROR：行不是有效的JSON对象-JSONException：JSONObject文本必须在2 [字符3第1行]处以'}'结尾