AWS Athena查询报错：JSON解析遇意外<标记求助

我在Amazon S3中存储了一份名为movies.json的JSON文件，尝试通过AWS Lambda调用Athena执行查询时触发错误：
SyntaxError: Unexpected token < in JSON at position 0 at JSON.parse (<anonymous>)

以下是相关代码、Glue表CloudFormation模板及JSON示例：

代码实现 #

const athena_client = new AthenaClient({ region: process.env.AWS_REGION });

const sql = `SELECT * FROM customer_data`

const params = {
            QueryString: sql,
            WorkGroup: 'customer-data-workgroup-prod'
        };

const command = new StartQueryExecutionCommand(params);

console.log("command", command)

try {
        const data = await client.send(command);
        console.log("data response: ", data)
  } catch (error) {
        console.log("error", error)
  }

Glue表CloudFormation模板 #

Type: AWS::Glue::Table
  Properties:
    CatalogId: !Ref AWS::AccountId
    DatabaseName: !Ref GlueDatabase
    TableInput:
      Name: customer_data
      StorageDescriptor:
        Columns:
          - Name: createdAt
            Type: timestamp
          - Name: fullJson  
            Type: string
          - Name: data  
            Type: string  
          - Name: title
            Type: string                
        Location: 's3://customer-data-bucket-prod/'
        InputFormat: org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat
        OutputFormat: org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat
        SerdeInfo:
          SerializationLibrary: org.openx.data.jsonserde.JsonSerDe
      Parameters: {'classification': 'json'}
      TableType: "EXTERNAL_TABLE"

JSON示例 #

{
  "page": 1,
  "per_page": 10,
  "total": 2770,
  "total_pages": 277,
  "data": [
    {
      "Title": "Waterworld",
      "Year": 1995,
      "imdbID": "tt0114898"
    },
    {
      "Title": "Waterworld",
      "Year": 1995,
      "imdbID": "tt0189200"
    },
    {
      "Title": "The Making of 'Waterworld'",
      "Year": 1995,
      "imdbID": "tt2670548"
    },
    {
      "Title": "Waterworld 4: History of the Islands",
      "Year": 1997,
      "imdbID": "tt0161077"
    },
    {
      "Title": "Waterworld",
      "Year": 1997,
      "imdbID": "tt0455840"
    },
    {
      "Title": "Waterworld",
      "Year": 1997,
      "imdbID": "tt0390617"
    },
    {
      "Title": "Swordquest: Waterworld",
      "Year": 1983,
      "imdbID": "tt2761086"
    },
    {
      "Title": "Behind the Scenes of the Most Fascinating Waterworld on Earth: The Great Backwaters, Kerala.",
      "Year": 2014,
      "imdbID": "tt5847056"
    },
    {
      "Title": "Louise's Waterworld",
      "Year": 1997,
      "imdbID": "tt0298417"
    },
    {
      "Title": "Waterworld",
      "Year": 2001,
      "imdbID": "tt0381702"
    }
  ]
}

1. 修复Glue表的输入/输出格式配置 #

当前配置用了Parquet格式的Input/Output类，但实际存储的是JSON文件，这会导致Athena无法解析，返回HTML错误页面（错误中的<就是HTML标签开头）。替换为JSON对应的格式类：

InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

2. 修正列定义与JSON结构不匹配问题 #

当前列定义和实际JSON结构完全不符，会导致解析失败。根据提供的JSON示例，调整列定义为：

Columns:
  - Name: page
    Type: int
  - Name: per_page
    Type: int
  - Name: total
    Type: int
  - Name: total_pages
    Type: int
  - Name: data
    Type: array<struct<Title:string, Year:int, imdbID:string>>

如果需要将data数组中的每个电影条目单独成行，查询时可使用UNNEST(data)语法。

3. 修正Lambda代码变量名错误 #

代码中初始化的客户端是athena_client，但调用时用了未定义的client，需统一变量名：

// 要么初始化时命名为client
const client = new AthenaClient({ region: process.env.AWS_REGION });
// 要么调用时使用athena_client
const data = await athena_client.send(command);

4. 配置SerDe支持多行JSON #

如果S3中的JSON是多行格式，需在SerdeInfo中添加参数确保解析正常：

SerdeInfo:
  SerializationLibrary: org.openx.data.jsonserde.JsonSerDe
  Parameters:
    'serialization.format': '1'
    'ignore.malformed.json': 'true'

内容的提问来源于stack exchange，提问作者Trey Copeland