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年前

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ