By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Athena "Generate Create Table DDL" call results in a Java Exception

0

Hi,

I'm getting an java exception when describing the ddl for a table that I have. The table's ddl uses a '-' for one of its keys. I believe this is a bug.

Here is an example of a record:

{"updated_at": "2019-01-17T00:51:17Z", "tenant_guid": "123456708", "payload": {"2017": {"UpdatedAt": "2019-01-17T00:51:17Z", "Members": [{"Summary": [{"DataID": "Members", "WithDocs": 0, "Total": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "Documents": [{"Summary": [{"DataID": "Documents", "Acquired": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "CodeActions": [{"Summary": [{"DataID": "Code Actions", "Suggested": 0}], "Accepted": [{"Percent": 0, "ActualValue": 0}], "Rejected": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "ManAdd": [{"Percent": 0, "ActualValue": 0}]}], "CodesLinkedToClaim": [{"Summary": [{"DataID": "Codes Linked to a Claim", "Accepted_Added": 0}], "Linked": [{"Percent": 0, "ActualValue": 0}], "NotLinked": [{"Percent": 0, "ActualValue": 0}]}]}, "2017-2018": {"UpdatedAt": "2019-01-17T00:51:17Z", "Members": [{"Summary": [{"DataID": "Members", "WithDocs": 0, "Total": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "Documents": [{"Summary": [{"DataID": "Documents", "Acquired": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "CodeActions": [{"Summary": [{"DataID": "Code Actions", "Suggested": 0}], "Accepted": [{"Percent": 0, "ActualValue": 0}], "Rejected": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "ManAdd": [{"Percent": 0, "ActualValue": 0}]}], "CodesLinkedToClaim": [{"Summary": [{"DataID": "Codes Linked to a Claim", "Accepted_Added": 0}], "Linked": [{"Percent": 0, "ActualValue": 0}], "NotLinked": [{"Percent": 0, "ActualValue": 0}]}]}, "2017-2019": {"UpdatedAt": "2019-01-17T00:51:17Z", "Members": [{"Summary": [{"DataID": "Members", "WithDocs": 0, "Total": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "Documents": [{"Summary": [{"DataID": "Documents", "Acquired": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "CodeActions": [{"Summary": [{"DataID": "Code Actions", "Suggested": 0}], "Accepted": [{"Percent": 0, "ActualValue": 0}], "Rejected": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "ManAdd": [{"Percent": 0, "ActualValue": 0}]}], "CodesLinkedToClaim": [{"Summary": [{"DataID": "Codes Linked to a Claim", "Accepted_Added": 0}], "Linked": [{"Percent": 0, "ActualValue": 0}], "NotLinked": [{"Percent": 0, "ActualValue": 0}]}]}, "2018": {"UpdatedAt": "2019-01-17T00:51:17Z", "Members": [{"Summary": [{"DataID": "Members", "WithDocs": 0, "Total": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "Documents": [{"Summary": [{"DataID": "Documents", "Acquired": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "CodeActions": [{"Summary": [{"DataID": "Code Actions", "Suggested": 0}], "Accepted": [{"Percent": 0, "ActualValue": 0}], "Rejected": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "ManAdd": [{"Percent": 0, "ActualValue": 0}]}], "CodesLinkedToClaim": [{"Summary": [{"DataID": "Codes Linked to a Claim", "Accepted_Added": 0}], "Linked": [{"Percent": 0, "ActualValue": 0}], "NotLinked": [{"Percent": 0, "ActualValue": 0}]}]}, "2018-2019": {"UpdatedAt": "2019-01-17T00:51:17Z", "Members": [{"Summary": [{"DataID": "Members", "WithDocs": 0, "Total": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "Documents": [{"Summary": [{"DataID": "Documents", "Acquired": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "CodeActions": [{"Summary": [{"DataID": "Code Actions", "Suggested": 0}], "Accepted": [{"Percent": 0, "ActualValue": 0}], "Rejected": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "ManAdd": [{"Percent": 0, "ActualValue": 0}]}], "CodesLinkedToClaim": [{"Summary": [{"DataID": "Codes Linked to a Claim", "Accepted_Added": 0}], "Linked": [{"Percent": 0, "ActualValue": 0}], "NotLinked": [{"Percent": 0, "ActualValue": 0}]}]}, "2019": {"UpdatedAt": "2019-01-17T00:51:17Z", "Members": [{"Summary": [{"DataID": "Members", "WithDocs": 0, "Total": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "Documents": [{"Summary": [{"DataID": "Documents", "Acquired": 0}], "Reviewed": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "WithoutOpportunity": [{"Percent": 0, "ActualValue": 0}]}], "CodeActions": [{"Summary": [{"DataID": "Code Actions", "Suggested": 0}], "Accepted": [{"Percent": 0, "ActualValue": 0}], "Rejected": [{"Percent": 0, "ActualValue": 0}], "Remaining": [{"Percent": 0, "ActualValue": 0}], "ManAdd": [{"Percent": 0, "ActualValue": 0}]}], "CodesLinkedToClaim": [{"Summary": [{"DataID": "Codes Linked to a Claim", "Accepted_Added": 0}], "Linked": [{"Percent": 0, "ActualValue": 0}], "NotLinked": [{"Percent": 0, "ActualValue": 0}]}]}}, "dashboard_type": "scout_progress"}

Your query has the following error(s):

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.IllegalArgumentException: Error: : expected at the position 1025 of 'string:string:struct<2017:struct<UpdatedAt:string,Members:array<struct<Summary:array<struct<DataID:string,WithDocs:int,Total:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,Documents:array<struct<Summary:array<struct<DataID:string,Acquired:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,CodeActions:array<struct<Summary:array<struct<DataID:string,Suggested:int>>,Accepted:array<struct<Percent:int,ActualValue:int>>,Rejected:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,ManAdd:array<struct<Percent:int,ActualValue:int>>>>,CodesLinkedToClaim:array<struct<Summary:array<struct<DataID:string,Accepted_Added:int>>,Linked:array<struct<Percent:int,ActualValue:int>>,NotLinked:array<struct<Percent:int,ActualValue:int>>>>>,2017-2018:struct<UpdatedAt:string,Members:array<struct<Summary:array<struct<DataID:string,WithDocs:int,Total:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,Documents:array<struct<Summary:array<struct<DataID:string,Acquired:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,CodeActions:array<struct<Summary:array<struct<DataID:string,Suggested:int>>,Accepted:array<struct<Percent:int,ActualValue:int>>,Rejected:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,ManAdd:array<struct<Percent:int,ActualValue:int>>>>,CodesLinkedToClaim:array<struct<Summary:array<struct<DataID:string,Accepted_Added:int>>,Linked:array<struct<Percent:int,ActualValue:int>>,NotLinked:array<struct<Percent:int,ActualValue:int>>>>>,2017-2019:struct<UpdatedAt:string,Members:array<struct<Summary:array<struct<DataID:string,WithDocs:int,Total:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,Documents:array<struct<Summary:array<struct<DataID:string,Acquired:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,CodeActions:array<struct<Summary:array<struct<DataID:string,Suggested:int>>,Accepted:array<struct<Percent:int,ActualValue:int>>,Rejected:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,ManAdd:array<struct<Percent:int,ActualValue:int>>>>,CodesLinkedToClaim:array<struct<Summary:array<struct<DataID:string,Accepted_Added:int>>,Linked:array<struct<Percent:int,ActualValue:int>>,NotLinked:array<struct<Percent:int,ActualValue:int>>>>>,2018:struct<UpdatedAt:string,Members:array<struct<Summary:array<struct<DataID:string,WithDocs:int,Total:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,Documents:array<struct<Summary:array<struct<DataID:string,Acquired:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,CodeActions:array<struct<Summary:array<struct<DataID:string,Suggested:int>>,Accepted:array<struct<Percent:int,ActualValue:int>>,Rejected:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,ManAdd:array<struct<Percent:int,ActualValue:int>>>>,CodesLinkedToClaim:array<struct<Summary:array<struct<DataID:string,Accepted_Added:int>>,Linked:array<struct<Percent:int,ActualValue:int>>,NotLinked:array<struct<Percent:int,ActualValue:int>>>>>,2018-2019:struct<UpdatedAt:string,Members:array<struct<Summary:array<struct<DataID:string,WithDocs:int,Total:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,Documents:array<struct<Summary:array<struct<DataID:string,Acquired:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,CodeActions:array<struct<Summary:array<struct<DataID:string,Suggested:int>>,Accepted:array<struct<Percent:int,ActualValue:int>>,Rejected:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,ManAdd:array<struct<Percent:int,ActualValue:int>>>>,CodesLinkedToClaim:array<struct<Summary:array<struct<DataID:string,Accepted_Added:int>>,Linked:array<struct<Percent:int,ActualValue:int>>,NotLinked:array<struct<Percent:int,ActualValue:int>>>>>,2019:struct<UpdatedAt:string,Members:array<struct<Summary:array<struct<DataID:string,WithDocs:int,Total:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,Documents:array<struct<Summary:array<struct<DataID:string,Acquired:int>>,Reviewed:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,WithoutOpportunity:array<struct<Percent:int,ActualValue:int>>>>,CodeActions:array<struct<Summary:array<struct<DataID:string,Suggested:int>>,Accepted:array<struct<Percent:int,ActualValue:int>>,Rejected:array<struct<Percent:int,ActualValue:int>>,Remaining:array<struct<Percent:int,ActualValue:int>>,ManAdd:array<struct<Percent:int,ActualValue:int>>>>,CodesLinkedToClaim:array<struct<Summary:array<struct<DataID:string,Accepted_Added:int>>,Linked:array<struct<Percent:int,ActualValue:int>>,NotLinked:array<struct<Percent:int,ActualValue:int>>>>>>:string' but '-' is found.

This query ran against the "rascout" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: a66ffc54-81a3-4ef5-b2b2-0e33befb0b0f.
Topics
Analytics
Tags
Amazon Athena
Language
English
wtruong424
asked 6 years ago978 views
3 Answers
0

did you create a table in Athena ?
Athena does not support "-" however, datacatalog shared by other services like glue,emr and redshift which might not have same validation hence you may get above error.

Shivan
answered 6 years ago
0

The table was created with glue. Should Athena support "-" for consistency???

wtruong424
answered 6 years ago
0

Hi,

Athena table, view, database, and column names cannot contain special characters, other than underscore (_).

Link : https://docs.aws.amazon.com/athena/latest/ug/tables-databases-columns-names.html

To work with special characters within the statements we can try to use backtics for the columns or table names. Example:
We can use backtics to enclose table, view, or column names that begin with an underscore. For example:
CREATE TABLE _myunderscoretable (
_id string,
_indexstring,
…

Similarly for the select statement as well we can use backtics when having a “-“ (or any other special character). Example:
SELECT * FROM
fpa-dev
LIMIT 10

Regarding the describe table behavior with tables having special characters within the nested columns you can use column mapping within the SerDe properties of the table. We can handle forbidden characters with the help of mapping. Please refer the below blog for using the mapping parameter within the serve properties:
https://aws.amazon.com/blogs/big-data/create-tables-in-amazon-athena-from-nested-json-and-mappings-using-jsonserde/

I hope this helps but in case you have any doubts or any queries/issues when using Athena that need more in-depth analysis you can definitely contact AWS Support. And we will be glad to assist as always.

Have a AWSome Day :)

AWS
AWS-Harsh
answered 6 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

Relevant content