CONCAT & CAST & LPAD

Amazon Athenaで表題の関数について。Amazon Athenaに限った話ではなくSQLの話だけれども…。

■ 環境

  • Amazon Athena

■ きっかけ

やりたいこととしては単純に『カラムの値を連結したい』ということだけのはずだった。もうずっと単純なSQLしか書いていなかったのでここら辺の関数についていろいろ忘れておりはまってしまった。

■ CONCAT

文字列として連結したいんだから、”CONCAT“でいけるだろうと思い試してみたところ、下記のエラーが出た。

Error running query: SYNTAX_ERROR: line 1:185: Unexpected parameters (integer) for function concat. Expected: concat(array(E), E) E, concat(E, array(E)) E, concat(array(E)) E, concat(varchar)

あれ?と思いドキュメントを見ていると下記を発見。

Concatenating Arrays
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/concatenating-arrays.html

いややりたいことは”文字列“としての連結であって、配列の連結ではない。ということでこれは違う。もう一度エラーメッセージをよく見てみると下記の通り。

concat(varchar)

できるはずである。としばし考えて気がついた。対象のカラムの型が”int“であった。では”CAST“を挟めば良いのか。ということで下記のような感じになった。

select concat(cast(year as varchar), cast(month as varchar), cast(day as varchar)) ...

しかし得られた結果がこれでは微妙であった。”2018316“。”0“パディングしなければならない。”LPAD“の出番である。”LPAD“だから戻ってくるのは”varchar“だよね、と思い下記のように書き換えた。

select concat(cast(year as varchar), lpad(month, 2, '0'), lpad(day, 2, '0')) ...

実行すると下記のようなエラーが…。

Error running query: SYNTAX_ERROR: line 1:215: Unexpected parameters (integer, integer, varchar(1)) for function lpad. Expected: lpad(varchar(x), bigint, varchar(y))

これもエラーログをよく読んでみれば解決した。ここも”CAST“が必要であった。

最終的に下記で思い通りの結果を得られた。

select concat(cast(year as varchar), lpad(cast(month as varchar), 2, '0'), lpad(cast(day as varchar), 2, '0')) ...

以上。

show databases on Athena

表題の通り。

■ 環境

  • Amazon Athena

■ show databases

SHOW DATABASES | Amazon Athena
https://docs.aws.amazon.com/athena/latest/ug/show-databases.html

互換ありということであればもちろんあるか。”Management Console“でアクセスしている場合には左側のメニューに”DATABASE“のプルダウンもあるので今まで気にもしていなかった。クエリの実行結果も実に質素なものである。

show databases

以上。

複数条件のcount()

SQLにおいて表題を行いたい。

■ 環境

  • AWS Athena

■ count()

where句で絞ると複数queryを実行しなければならない。Athenaを使用している為、読み込むデータ量は少なくさせたい。というわけで1回のクエリで複数の条件の”count()“を取得したい。

下記の方法にて一度のqueryで取得することができた。”column1“カラムの数値で”count()“したい。

SELECT
  regist_date,
  count(1) as total,
  count(column1 < 10 or null) as count1,
  count(column1 >= 10 or null) as count2
FROM testtable
GROUP BY regist_date

ポイントは”or null“である。

下記のような結果が得られた。満足である。

+--------------+-------+--------+--------+
| regist_date  | total | count1 | count2 |
+--------------+-------+--------+--------+
| 2018-02-01   |    51 |     31 |     20 |
| 2018-02-02   |     8 |      6 |      2 |
  :
+--------------+-------+--------+--------+

以上。

■ 関連

count(distinct (case when … then … end))

複数のcount()の結果を四則演算

フィールドが空のレコードを取得

Amazon Athenaで表題をやりたい。ちなみに対象のテーブルはCSVでS3に保存されている。

■ 環境

  • Amazon Athena

■ is null

特に調べずにこんな感じでいけるかな?と試してみた。SQL感覚。

select * from testtable where field1 is null

しかし結果は下記であった。

Zero records returned.

残念。

■ = ”

空文字であろうか?と試してみる。

select * from testtable where field1 = ''

こちらでは思い通りの結果を得ることができた。

Hiveとかでも確かこんな感じだったような記憶がある。

以上。

awscliでAthenaのpartition作成

表題の通り。画面からSQLを実行するのが面倒なので`aws`コマンドで一気に作成したい。

■ 環境

  • awscli 1.14.20
  • Amazon Athena
  • Mac OSX El Capitan

■ awscli

コマンドを確認すると、下記で実行が可能そうである。

$ aws athena start-query-execution

`help`を確認すると書きオプションを指定すれば問題なさそうである。

  • –query-string
  • –result-configuration

–query-string“で指定するのは、普段ManagementConsoleで直接書いて実行しているクエリをそのまま書けば良さそうである。”–result-configuration“は結果を出力させるS3上のpathを指定すれば問題がなさそうである。例えば下記のように実行すれば良さそうだ。

$ aws athena start-query-execution \
  --query-string "ALTER TABLE" test.table1 ADD PARTITION (year=2018, month=1) location 's3://withsin/testtable/2018/01/' \
  --result-configuration OutputLocation=s3://withsin/athena-logs/

引っかかったのがテーブル名を”DB.TABLE“としなければいけなかった事だろうか。

あとは上記をスクリプトでループさせたりすれば、必要な分は一気に作成することができそうである。

以上。

■ 関連

Athenaのpartition一覧

partition削除