I want to create an external table on AWS Athena.
My query is:
CREATE EXTERNAL TABLE test (
"timestamp" timestamp
, "start" timestamp
, "end" timestamp
, "recipe" string
, "transient_duration" double
, "1st_regime_1n" double
, "1st_regime_2" double
, "1st_regime_3" double
, "index" timestamp
, "date" date
)
PARTITIONED BY ("date" date)
STORED AS 'PARQUET'
location 's3://somewhere/somefolder'
My columns are those, I can’t change them, so there’s one named timestamp, another one index, another one date, another one end etc etc. I have to list all table’s columns because if I don’t the column “transient_duration” is taken as a bigint when it’s a double. Running this query gives me this error:
line 1:8: mismatched input 'EXTERNAL'. Expecting: 'MATERIALIZED', 'MULTI', 'OR', 'PROTECTED', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'
What can I do?
I have tried removing the ” but some columns can’t be read with those names. I’ve tried also to add/remove parameters, set STORED AS to INPUTFORMAT and selectiong parquet as classification in TBLPARAMETERS, but nothing seems to work. I really don’t know what to do.
Martino Quaglia is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.