In the AWS console (for Athena), one has the option of directly running the statement SHOW CREATE TABLE `foo`; or doing the same thing but via the GUI (in the tables option) to provide a single DDL (for that table).
I've created multiple DDLs this way and am now (just for experimentation) trying to run them for another database (db2, db3, ...). Here's one of them for example:
CREATE EXTERNAL TABLE db1.anomaly_eh_pred(
"node" string,
"workzone_desc" string,
"eh_pred" double,
"create_dt" timestamp)
COMMENT "test"
PARTITIONED BY (
"site_id" string,
"dt" date)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
WITH SERDEPROPERTIES (
'escape.delim'='\\')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://foo/s3-stuff'
TBLPROPERTIES (
'transient_lastDdlTime'='1616731896')
After trying to run this, I get the following error:
line 1:8: mismatched input 'EXTERNAL'. Expecting: 'OR', 'SCHEMA', 'TABLE', 'VIEW'
This error is documented in other places (here, here, and here), and I've compared/tried those potential solution to no avail. Regardless, even if they did work, I'm confused why a raw, AWS-supplied DDL for an Athena table would simple not work. I've applied no editing whatsoever.
What could be a solution to this issue?