Usando AWS re:Post, accetti AWS re:Post Termini di utilizzo
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.
Argomenti
Analisi
Tag
Amazon Athena
Lingua
English
wtruong424
posta 5 anni fa877 visualizzazioni
3 Risposte
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
con risposta 5 anni fa
0

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

wtruong424
con risposta 5 anni fa
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
con risposta 5 anni fa

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande

Contenuto pertinente