The following CREATE statement was issued in AWS Athena's Query Editor to create an external table for CloudFront access log acquisition.
CREATE EXTERNAL TABLE IF NOT EXISTS cf_log (
request_date string,
request_time string,
x_edge_location string, sc_bytes int,
client_ip string,
cs_method string,
cs_host string,
cs_uri_stem string,
sc_status string,
cs_referer string,
user_agent string,
uri_query string,
cookie string,
x_edge_result_type string,
x_edge_request_id string,
x_host_header string,
cs_protocol string,
cs_bytes int,
time_taken DECIMAL(8,3),
x_forwarded_for string,
ssl_protocol string,
ssl_cipher string,
x_edge_response_result_type string,
cs-protocol-version string,
fle-status string,
fle-encrypted-fields string
) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '\t',
'field.delim' = '\t'
)
LOCATION 's3://{S3 bucket name}/'
TBLPROPERTIES (
'has_encrypted_data'='false',
'skip.header.line.count'='2'
);
However, I get the following error and cannot create the table.
line 1:8: no viable alternative at input 'create external' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: {request id}
The cause was due to the presence of hyphenated strings in column names.
cs-protocol-version string,
fle-status string,
fle-encrypted-fields string
After correcting to underbar, it can now be executed without error.
cs_protocol_version string,
fle_status string,
fle_encrypted_fields string
