I am trying to use queries I have already tested locally in MySQL Workbench first to test run them in Amazon Athena's query editor, but nothing I do is making them work. I wasted hours last night reading through the documentation and official tutorials for how to use AWS, but none of them seem to indicate whether or not I can carry over the syntax of using `` around column names with spaces in them or not. That is one reason my queries may not be working.
Here is the original syntax I used to run this query in Workbench:
SELECT Loc Id
, Runway Id
, Length
, Width, Surface Type Condition
, Edge Light Intensity
, WBC Single
, WBC Dual
, WBC Two Dual
, WBC Tandem Two Dual
, Base Marking Condition
FROM runways
.runway
WHERE Loc Id
IN ('09J','14A','18A','1V6','22S');
When I just run that, it says Error: line 4:3: backquoted identifiers are not supported; use double quotes to quote identifiers.
So, then I try running this instead:
CREATE TABLE IF NOT EXISTS runway_table
WITH (external_location = 's3://us-east-1-s3-bucket/airports_and_runways_table/') AS
SELECT
"Loc Id", "Runway Id", "Length", "Width", "Surface Type Condition", "Edge Light Intensity", "WBC Single", "WBC Dual", "WBC Two Dual", "WBC Tandem Two Dual", "Base Marking Condition"
FROM
Runway
WHERE
"Loc Id" IN ('09J','14A','18A','1V6','22S');
But when I try to run this, I get this error message:
SYNTAX_ERROR: line 17:3: Table awsdatacatalog.runway_db_athena.runway does not exist. You may need to manually clean the data at location 's3://us-east-1-s3-bucket/airports_and_runways_table/runways_table/2023/04/06/tables/915d54ef-e803-4470-a8c3-4df181914989' before retrying. Athena will not delete data in your account.
This query ran against the "runway_db_athena" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 915d54ef-e803-4470-a8c3-4df181914989
I am thinking this is because I am not specifying the FROM clause correctly. Should I include the S3 address instead of the word Runway in the From clause perhaps? I am so very lost here and this is part of the capstone project in my masters degree, so I really can't afford to fail here!
p.s. out of curiosity, I also ran the same query but with no quotation marks around any column names, and to my shock and dismay, the error I got was the following:
line 4:49: mismatched input 'Condition'. Expecting: ',', 'EXCEPT', 'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'ORDER', 'UNION', 'WHERE', 'WITH', <EOF>
Where line 4:49 was indeed Condition, but that word is a little misleading here, it was the 'Condition' in Surface Type Condition in the line:
SELECT
Loc Id, Runway Id, Length, Width, Surface Type Condition, Edge Light Intensity, WBC Single, WBC Dual, WBC Two Dual, WBC Tandem Two Dual, Base Marking Condition
The fact that the error wasn't on Loc Id or Runway Id confuses me immensely!