Forum Discussion
Error ODBC connecting to Athena data migrated from mongoldb
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 ?
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
1 Reply
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