Table Creation Error - Following AWS Blog

0

Hi,

I'm trying to follow the AWS blog found here - https://aws.amazon.com/blogs/mt/how-to-query-your-aws-resource-configuration-states-using-aws-config-and-amazon-athena/

I would eventually like to be able to do reporting/visualisation as discussed in a more recent AWS blog here - https://aws.amazon.com/blogs/mt/visualizing-aws-config-data-using-amazon-athena-and-amazon-quicksight/

In trying to create a new external table, I'm getting the error

FAILED: ParseException line 1:521 mismatched input ',' expecting < near 'MAP' in map type

This is the result of running the shown code directly from the blog

CREATE EXTERNAL TABLE aws_config_configuration_snapshot (
 fileversion STRING,
 configsnapshotid STRING,
 configurationitems ARRAY < STRUCT <
        configurationItemVersion : STRING,
        configurationItemCaptureTime : STRING,
        configurationStateId : BIGINT,
        awsAccountId : STRING,
        configurationItemStatus : STRING,
        resourceType : STRING,
        resourceId : STRING,
        resourceName : STRING,
        ARN : STRING,
        awsRegion : STRING,
        availabilityZone : STRING,
        configurationStateMd5Hash : STRING,
        configuration : STRING,
        supplementaryConfiguration : MAP ,
        tags: MAP ,
        resourceCreationTime : STRING > >
) 
PARTITIONED BY ( dt STRING , region STRING )
ROW FORMAT SERDE 
 'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES ( 
  'case.insensitive'='false',
  'mapping.fileversion'='fileVersion',
  'mapping.configsnapshotid'='configSnapshotId',
  'mapping.configurationitems'='configurationItems',
  'mapping.configurationitemversion'='configurationItemVersion',
  'mapping.configurationitemcapturetime'='configurationItemCaptureTime',
  'mapping.configurationstateid'='configurationStateId',
  'mapping.awsaccountid'='awsAccountId',
  'mapping.configurationitemstatus'='configurationItemStatus',
  'mapping.resourcetype'='resourceType',
  'mapping.resourceid'='resourceId',
  'mapping.resourcename'='resourceName',
  'mapping.arn'='ARN',
  'mapping.awsregion'='awsRegion',
  'mapping.availabilityzone'='availabilityZone',
  'mapping.configurationstatemd5hash'='configurationStateMd5Hash',
  'mapping.supplementaryconfiguration'='supplementaryConfiguration',
  'mapping.configurationstateid'='configurationStateId'
  )
LOCATION 's3://awsexamplebucket/AWSLogs/';

I'm unsure what is wrong with the create query however. Is anyone able to help or has recently set up a similar system? If there is a more recent example of how to accomplish the same sort of system I am also open to other links.

Thanks,

AWS-User-5410463

asked 2 years ago349 views
1 Answer
0

For the datatype map mention the format map<primitive_type, data_type>

CREATE EXTERNAL TABLE default.aws_config_configuration_snapshot ( fileversion STRING, configsnapshotid STRING, configurationitems ARRAY < STRUCT < configurationItemVersion : STRING, configurationItemCaptureTime : STRING, configurationStateId : BIGINT, awsAccountId : STRING, configurationItemStatus : STRING, resourceType : STRING, resourceId : STRING, resourceName : STRING, ARN : STRING, awsRegion : STRING, availabilityZone : STRING, configurationStateMd5Hash : STRING, configuration : STRING, supplementaryConfiguration : MAP<string,int> , tags: MAP<string,int> , resourceCreationTime : STRING > > ) PARTITIONED BY ( dt STRING , region STRING ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ( 'case.insensitive'='false', 'mapping.fileversion'='fileVersion', 'mapping.configsnapshotid'='configSnapshotId', 'mapping.configurationitems'='configurationItems', 'mapping.configurationitemversion'='configurationItemVersion', 'mapping.configurationitemcapturetime'='configurationItemCaptureTime', 'mapping.configurationstateid'='configurationStateId', 'mapping.awsaccountid'='awsAccountId', 'mapping.configurationitemstatus'='configurationItemStatus', 'mapping.resourcetype'='resourceType', 'mapping.resourceid'='resourceId', 'mapping.resourcename'='resourceName', 'mapping.arn'='ARN', 'mapping.awsregion'='awsRegion', 'mapping.availabilityzone'='availabilityZone', 'mapping.configurationstatemd5hash'='configurationStateMd5Hash', 'mapping.supplementaryconfiguration'='supplementaryConfiguration', 'mapping.configurationstateid'='configurationStateId' ) LOCATION 's3://awsexamplebucket/AWSLogs/';

AWS
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions