Athena Query Editor Behaving Unexpectedly

0

I am just starting to use Athena, as well as AWS Glue. With my current setup I have a Kinesis Firehose pushing events to AWS Glue, which dumps into an S3 Bucket, where Athena should query off of.

I have successfully gotten a handful of events to record to my S3 Bucket under s3://{bucket_name}/2018/05/17/01/{file}.parquet

Doing a SELECT ** through Athena yielded no results, so after a search I found the *MSCK REPAIR TABLE command. I tried running this command but got the following error-

Query: "MSCK REPAIR TABLE sw-events;"
Result: "line 1:1: mismatched input 'msck' expecting {'(', 'select', 'desc', 'with', 'values', 'create', 'table', 'insert', 'delete', 'describe', 'grant', 'revoke', 'explain', 'show', 'use', 'drop', 'alter', 'set', 'reset', 'start', 'commit', 'rollback', 'call', 'prepare', 'deallocate', 'execute'} (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: c07e0c48-5972-11e8-8255-71d9cce4c600)"

Interestingly, this looked like an Antlr error so I figured the way the table was named meant the entire line read as garbage. Sure enough, adding back-ticks around sw-events gave a different error. But one I don't think I myself can navigate without renaming the database.

Query: "MSCK REPAIR TABLE sw-events;"
Result: "Your query has the following error(s):

FAILED: ParseException line 1:6 missing EOF at '-' near 'sw'

This query ran against the "sw-glue" database, unless qualified by the query..."

I also get this error by using a table that doesn't actually exist. If I run this query with a non-existant table (without the minus) I still get the above result. I assume this means it's getting hung up on the database having a minus in it as well.

So I tried specifying the database in a USE statement where I could safely wrap the db in backticks.

Query: "USE sw-glue;
MSCK REPAIR TABLE sw-events;"
Result: NONE

Here the query fails with no error message or results. Checking the network tab in chrome dev tools I see the error: {"status":-1,"statement":null,"id":null,"message":"Only one sql statement is allowed. Got: USE sw-glue;\nMSCK REPAIR TABLE sw-events; (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: f6d03058-5973-11e8-a0e9-ab7676807f59)","watch_url":null}

So I'm not sure how anyone would actually use the USE statement :)

So I found a slew of weirdness. Hopefully I'm just doing something wrong. I'm currently unable to use the MSCK REPAIR TABLE command or get any results from the streamed S3 data.

EDIT:

I believe I've confirmed the above by creating a new database and table without hyphens and I'm not getting the previously described error messages.

Edited by: pmicah on May 16, 2018 7:21 PM

pmicah
asked 6 years ago7697 views
2 Answers
0

It is the issue with "-" (dash) . it is a special character in Athena. Could you delete and recreate the table and database without "-" .

FAILED: ParseException line 1:6 missing EOF at '-' near 'sw'

Best practice on names :

https://docs.aws.amazon.com/athena/latest/ug/tables-databases-columns-names.html

you can change the database manually from left side drop down or use database name prefix with table name

select * from <database name>. <tablename>;

Edited by: Shivan on Sep 21, 2018 7:17 PM

Shivan
answered 6 years ago
0

{"status":-1,"statement":null,"id":null,"message":"Only one sql statement is allowed. Got: USE sw-glue;\nMSCK REPAIR TABLE sw-events; (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: f6d03058-5973-11e8-a0e9-ab7676807f59)","watch_url":null}

You cannot run more than one statement in Athena console at a time. run one by one.

Shivan
answered 6 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions