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

Question

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.

Ideally, athena should not have allowed a space in the column name docs.aws.amazon.com/athena/latest/ug/… — CR7SMS
– CR7SMS, Commented Feb 13, 2020 at 22:41
Try using the alias with double quotes. Select * from s3object s where s."Currency Pair" = 'AUDJPY'. See if this reference helps: docs.aws.amazon.com/AmazonS3/latest/dev/… — SS_DBA
– SS_DBA, Commented Feb 13, 2020 at 22:56

SS_DBA · Accepted Answer · 2020-02-13 23:12:16Z

3

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

answered Feb 13, 2020 at 23:12

SS_DBA

2,4211 gold badge13 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Behrens Over a year ago

I found also that one can use that sort of syntax to return a specific field in the input that has a space: Expression="SELECT s.\"Currency Pair\" FROM s3object s WHERE s."Currency Pair" = 'AUDJPY'

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related