amazon-web-services - Amazon Athena : HIVE_METASTORE_ERROR: name expected at the position 22 of [. ..] 但找到 ' '

Question

我使用无服务器文件 + CloudFormation 在 AWS Athena 服务上创建表。

我的无服务器.yml:

...
CardBulkWorkgroup:
  Type: AWS::Athena::WorkGroup
  Properties:
    Name: ${opt:stage}-${opt:client}-CardBulk
    WorkGroupConfiguration:
      ResultConfiguration:
        OutputLocation: s3://${lower:${opt:stage}}-${lower:${opt:client}}-card-bulk-athena-result

CardBulkDatabase:
  Type: AWS::Glue::Database
  Properties:
    CatalogId: !Ref AWS::AccountId
    DatabaseInput:
      Name: ${lower:${opt:stage}}_${lower:${opt:client}}_bulkcard

CardBulkTable:
  Type: AWS::Glue::Table
  Properties:
    CatalogId: !Ref AWS::AccountId
    DatabaseName: !Ref CardBulkDatabase
    TableInput:
      Name: card
      StorageDescriptor:
        Columns:
          - Name: cardId
            Type: int
          - Name: metadata
            Type: struct<orderId:string, convertVirtualToPhysicalErrors:string>
          - Name: orderId
            Type: string
          - Name: errors
            Type: string
        Location: s3://${lower:${opt:stage}}_${lower:${opt:client}}-files/cards
        InputFormat: org.apache.hadoop.mapred.TextInputFormat
        OutputFormat: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat
        SerdeInfo:
          SerializationLibrary: org.openx.data.jsonserde.JsonSerDe
          Parameters:
            "serialization.format": "1"

CardBulkAthenaBucketResult:
  Type: AWS::S3::Bucket
  Properties:
    BucketName: ${lower:${opt:stage}}-${lower:${opt:client}}-card-bulk-athena-result
...

当我部署堆栈时，数据库 dev_connect_bulkcard 和表 card 已正确创建。

问题:

使用我的 API，当我想要从我的 dev_connect_bulkcard 的 card 表中检索数据时 数据库，我收到此错误。

"HIVE_METASTORE_ERROR: Error: name expected at the position 22 of'struct<orderId:string, convertVirtualToPhysicalErrors:string>' but '' is found.

但是，如果我直接从 AWS 控制台(从 Athena 服务)删除 card 表，并使用以下查询生成它:

CREATE EXTERNAL TABLE `card`(
  `cardid` int COMMENT 'from deserializer', 
  `orderid` string COMMENT 'from deserializer', 
  `metadata` struct<orderid:string,convertvirtualtophysicalerrors:string> COMMENT 'from deserializer', 
  `errors` array<string> COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  's3://my-bucket-files/cards'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'transient_lastDdlTime'='1627378097')

成功了，我可以使用我的 API 从卡片表中返回数据。

您知道为什么我必须手动删除表，然后重新创建它才能取回结果吗？

提前致谢，

编辑:Prabhakar Reddy 的解决方案:

The error is due to the space present in the column metadata. Removethe space between orderId and convertVirtualToPhysicalErrors . Athenawill not accept space as a special character in it's column name.Refer this for more information.

Answer 1

该错误是由于元数据列中存在空格造成的。删除 orderId 和 convertVirtualToPhysicalErrors 之间的空格。 Athena 不接受空格作为其列名称中的特殊字符。请参阅this了解更多信息。