Error ODBC connecting to Athena data migrated from mongoldb

[jhonatanacelas]

I created a DMS task on aws to fetch the collections as CSV files into s3 bucket, and then I using a crawler the columns were mapped on Athena table, however when I tried  to use the Athena connector on Power BI as source of data I'm getting:

Error de OLE DB u ODBC: [DataSource.Error] ODBC: ERROR [HY000] [AmazonAthena][AthenaClientError]:
ErrorType: 1502, ExceptionMessage: io.trino spi.TrinoException: Error: name expected at the position 24 of array < struct< _id:struct<Saw:string>,namestring,country_alpha_2_code:string >>' but '$' is found.
(Service: null; Status Code: 0; Error Code: null; Request ID: null; Proxy: null).

I noticed that there are $ symbols on column names. do you know what to do on this case ? 

[v-yiruan-msft · Accepted solution]

Hi @jhonatanacelas ,

It sounds like the issue lies in the column names that contain the '$' symbol, which Athena does not handle well. You can try the following methods and check if they can resolve your problem:
1. Modify the info in data source with any one of the following ones:

• Column Name Escaping: If your column names contain special characters like “$,” you should enclose them in double quotes when referencing them in your queries.
• Column Aliasing: For example, if you have a column named “_id,” you can alias it as “id” without the special character.

2. Reconnect to the Athena connector with modified query

3. Refresh the data

Best Regards