Athena Sql

Amazon Athenaとは

S3のオブジェクトに対してSQLを書くことができるAmazonのサービス。
S3バケット毎にデータベースを作成することができる。

基本的な書き方は下記の通り
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/functions.html

テーブルのCRUD

テーブル作成

テーブル変更

テーブル削除

カラムのCRUD

カラム作成

カラム変更

カラム削除

クエリー

概要

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT [ count | ALL ] ]

with

WITH

select from

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/select.html

cast

1
2
3


SELECT DISTINCT processid FROM "webdata"."impressions"
WHERE cast(processid as int) BETWEEN 1500 and 1800
ORDER BY processid

CONVERT()やSTR_TO_DATE()、DATE_FORMAT()もあります。

1

SELECT CONVERT(NOW(), CHAR);

1

SELECT STR_TO_DATE('09-01-2023', '%d-%m-%Y')

https://www.freecodecamp.org/news/sql-convert-the-date-to-string-or-datetime-function-2/