0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

AWS ATHENAでは、テーブル名に数字から始めるとエラーとなる

Last updated at Posted at 2021-12-22

AWS ATHENAで、テーブルを作ろうとしたら、エラーで作成できませんでした。エラーメッセージが意味不明で少しハマったのですが、数字から始まるテーブルは作成できないようです。修正して解決しました。

(参考)ドキュメントに書いてある通りのCREATE文を組んだつもりでした。(CloudFrontからのログからテーブル作成)
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/cloudfront-logs.html

(参考)ハイフンも駄目なようです。
https://qiita.com/snaka/items/500a34be2c16af7701b5

line 1:8: mismatched input 'EXTERNAL'. Expecting: 'OR', 'SCHEMA', 'TABLE', 'VIEW'

image.png

以下のCREATE文はエラーになります。

CREATE EXTERNAL TABLE IF NOT EXISTS default.123news_cloudfront_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  request_ip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  user_agent STRING,
  query_string STRING,
  cookie STRING,
  result_type STRING,
  request_id STRING,
  host_header STRING,
  request_protocol STRING,
  request_bytes BIGINT,
  time_taken FLOAT,
  xforwarded_for STRING,
  ssl_protocol STRING,
  ssl_cipher STRING,
  response_result_type STRING,
  http_version STRING,
  fle_status STRING,
  fle_encrypted_fields INT,
  c_port INT,
  time_to_first_byte FLOAT,
  x_edge_detailed_result_type STRING,
  sc_content_type STRING,
  sc_content_len BIGINT,
  sc_range_start BIGINT,
  sc_range_end BIGINT
)
PARTITIONED BY(
         date string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://123news-logs/cf-prod'
TBLPROPERTIES ( 'skip.header.line.count'='2' )
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?