How to query column name with space using AWS S3 SQL syntax?

I have the following csv file saved in an AWS S3 bucket:

Currency Pair,Spot,Date
AUDJPY,70.123,2019/12/12
SGDUSD,0.72,2019/12/12
CADUSD,0.75,2019/12/12

When I use the AWS s3 web interface and choose the “select from” header, the following syntax runs successfully:

select * from s3object s limit 2

But when I try to reference Currency Pair as below, I get the error Invalid Ion literal at line 1. I also get errors trying [Currency Pair], Currency Pair, and ‘Currency Pair’

select * from s3object s where `Currency Pair` = 'AUDJPY'

How can I select from a table with a column name with a space? From searching, it seems like special characters apart from underscore are not allowed. Can I either reference columns by index or re-define the schema? I would like to run this in python using boto3.

Answer

Try using the alias with double quotes.

Select * from s3object s where s."Currency Pair" = 'AUDJPY'.

Reference: https://docs.aws.amazon.com/AmazonS3/latest/dev/s3-glacier-select-sql-reference-select.html

Advertisement

Answer