使用 AWS re:Post 即表示您同意 AWS re:Post 使用條款
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.
主題
分析
標籤
Amazon Athena
語言
English
wtruong424
已提問 5 年前檢視次數 877 次
3 個答案
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
已回答 5 年前
0

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

wtruong424
已回答 5 年前
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
已回答 5 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋，同時有助於提問者的專業成長。

回答問題指南

相關內容