CODEWITHSUNDEEP
amazon-web-servicesamazon-athena
Spencer Sutton
Spencer Sutton

Reputation: 3427

Can't Query Athena Table Because of Dash Character

I have a table called fpa-dev in Athena (created by Glue). When I run this simple query:

SELECT * FROM 
fpa-dev
LIMIT 10

it gives me this error:

extraneous input '-' expecting {, '.', ',', 'add', 'as', 'all', 'some', 'any', 'where', 'group', 'order', 'having', 'limit', 'at', 'no', 'substring', 'position', 'tinyint', 'smallint', 'integer', 'date', 'time', 'timestamp', 'interval', 'year', 'month', 'day', 'hour', 'minute', 'second', 'zone', 'join', 'cross', 'inner', 'left', 'right', 'full', 'natural', 'filter', 'over', 'partition', 'range', 'rows', 'preceding', 'following', 'current', 'row', 'schema', 'comment', 'view', 'replace', 'grant', 'revoke', 'privileges', 'public', 'option', 'explain', 'analyze', 'format', 'type', 'text', 'graphviz', 'logical', 'distributed', 'validate', 'show', 'tables', 'views', 'schemas', 'catalogs', 'columns', 'column', 'use', 'partitions', 'functions', 'union', 'except', 'intersect', 'to', 'system', 'bernoulli', 'poissonized', 'tablesample', 'array', 'map', 'set', 'reset', 'session', 'data', 'start', 'transaction', 'commit', 'rollback', 'work', 'isolation', 'level', 'serializable', 'repeatable', 'committed', 'uncommitted', 'read', 'write', 'only', 'call', 'input', 'output', 'cascade', 'restrict', 'including', 'excluding', 'properties', 'nfd', 'nfc', 'nfkd', 'nfkc', 'if', 'nullif', 'coalesce', identifier, digit_identifier, quoted_identifier, backquoted_identifier} (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: 1b9aaf21-a83f-4678-b2da-19994e11cfd7)

Is there any way to query a table with a '-' in it or do I have to rename the table?

Upvotes: 13

Views: 10309

Answers (2)

rmooney
rmooney

Reputation: 6229

I ran into a similar issue with column names where escaping with backticks did not work (as it does for me in spark). Use quotations instead.

SELECT * FROM 
"fpa-dev"
LIMIT 10

And if you have columns that need escaping (like . in the name), you can also use quotations:

SELECT "project.userId" from sometable

I see Spencer Sutton had the same suggestion in a comment, but it needs to be an answer.

Upvotes: 1

Caldazar
Caldazar

Reputation: 3802

You should escape the table name by using backtick, like with any reserved words:

SELECT * FROM 
`fpa-dev`
LIMIT 10

Upvotes: 22

Related Questions