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