Athenaでデータ抽出するときによく使う関数まとめ

# はじめに
CloudFrontログなどのアクセスログからデータ抽出してちょっと加工して渡すみたいなことをするときに
いい感じに加工するのによく使う関数をピックアップしておく。

# データソース
基本は以下のリンクの中に関数があります。
Prestoの情報はあまり調べても出ないことも多いので、
ここを見ながら情報を探していくのが確実です。 

 [SQL クエリ、関数、および演算子](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/functions-operators-reference-section.html)

# CSVデータの読み込み
Athenaのクエリ結果を一旦ローカルに落としたあとに、
CSVデータを読み込むと""も値として読み込まれてしまう。

例えば以下のようなデータを読み込む場合

```test.csv

name,sex,age,prefecture
"田中",1,18,1
"山田",1,26,47
"加藤",2,39,25
"佐藤",2,27,14
"大野",1,50,11
"高橋",1,45,6
"阿部",2,36,4
```

普通にCreate Tableすると

```sql

CREATE EXTERNAL TABLE test.test(
  `name` string, 
  `sex` bigint, 
  `age` bigint, 
  `prefecture` bigint)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test/work/'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'skip.header.line.count'='1', 
  'transient_lastDdlTime'='1558577493')

```

こうなる。
データ件数が多いと置換するにも時間がかかるのでめちゃくちゃ邪魔です。

|id| name | sex | age | prefecture |
|:-----------------:|:------------------:|:------------------:|:------------------:|:------------------:|
|1|"田中"|1|18|1|
|2|"山田"|1|26|47|
|3|"加藤"|2|39|25|
|4|"佐藤"|2|27|14|
|5|"大野"|1|50|11|
|6|"高橋"|1|45|6|
|7|"阿部"|2|36|4|


**そこで、OpenCSVSerDe クラスを ROW FORMAT で参照し、**
**文字の区切り記号、引用符文字、およびエスケープ文字の SerDe プロパティを指定してあげます。**
**また、OpenCSVSerDe クラスを参照する場合、カラムはすべてstringである必要があります。**


```sql

CREATE EXTERNAL TABLE test.test2(
  `name` string, 
  `sex` string, 
  `age` string, 
  `prefecture` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar' = '"',
   'escapeChar' = '\\'
   )
LOCATION
  's3://test/work/'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'skip.header.line.count'='1', 
  'transient_lastDdlTime'='1558577493')
```

結果。気持ちよくなりました。

| id | name | sex | age | prefecture |
|:-----------------:|:------------------:|:------------------:|:------------------:|:------------------:|
|1|田中|1|18|1|
|2|山田|1|26|47|
|3|加藤|2|39|25|
|4|佐藤|2|27|14|
|5|大野|1|50|11|
|6|高橋|1|45|6|
|7|阿部|2|36|4|


# データ型の変更
上記のCSV読み込みを実施した場合など、
得てしてこちらの想定のデータ型と異なる場合がある。
```cast(string as データ型)```とすればOK。

```sql

select AVG(cast(age as bigint)) from test.test2 ;
```

結果。

|  | _col0 |
|:-----------------:|:------------------:|
| 1 | 34.42857142857143 |


# 条件分岐のCASE文
CASE文を使って条件分岐が可能です。
また、集計関数と組み合わせることで全レコードのうち特定レコードの数のカウントなども可能です。
その他縦型のデータを横型に変更したりと色々できるので重宝します。

### 単純な条件分岐（フラグ立て）

基本はCASE WHENのあと、条件を記載し、THENの後に合致した場合の処理を記載。
非合致の場合の処理はELSEに記載します。

```sql

CASE
    WHEN 条件 THEN 合致したときの値
    WHEN 条件 THEN 合致したときの値
    ElSE 非合致のときの値 END 
```

例えば以下のようなエリアの値を都道府県の値を見て
振って行きたい場合、、

|no|エリア|
|:------------------:|:------------------:|
|1|北海道|
|2|東北地方|
|3|関東地方|
|4|中部地方|
|5|近畿地方|
|6|中国地方|
|7|四国地方|
|8|九州地方|

```sql

SELECT name,
         sex,
         age,
         prefecture,
    CASE
    WHEN prefecture = '1' THEN 1
    WHEN prefecture in ('2','3','4','5','6','7') THEN 2
    WHEN prefecture in ('8','9','10','11','12','13','14') THEN 3
    WHEN prefecture in ('15','16','17','18','19','20','21','22','23','24') THEN 4
    WHEN prefecture in ('25','26','27','28','29','30') THEN 5
    WHEN prefecture in ('31','32','33','34','35') THEN 6
    WHEN prefecture in ('36','37','38','39') THEN 7
    WHEN prefecture in ('41','42','43','44','45','46','47') THEN 8
    ElSE NULL END as area
FROM test.test2 ;
```

結果。

| id | name | sex | age | prefecture | area  |
|:-----------------:|:------------------:|:------------------:|:------------------:|:------------------:|:------------------:|
|1|田中|1|18|1|1|
|2|山田|1|26|47|8|
|3|加藤|2|39|25|5|
|4|佐藤|2|27|14|3|
|5|大野|1|50|11|3|
|6|高橋|1|45|6|2|
|7|阿部|2|36|4|2|

### 特定の値を持つレコードのカウント
先程のケース文を使えば、
特定の値を持っている場合のみ1を返すとすることで、
特定の値を持つレコードのカウントが可能です。

```sql

SELECT
 SUM(CASE WHEN name LIKE '%田%' THEN 1 ELSE 0 END) as name_count
FROM test.test2 ;
```

結果。

|  | _col0 |
|:-----------------:|:------------------:|
| 1 | 2 |



# 特定の文字列の抽出
URIパースやら加工やらが多いので、
ちょっとだけアクセスログによせて以下のようなテーブルと仮定します。


|　id | name | sex | age | prefecture | uri |cookie |
|:-----------------:|:------------------:|:------------------:|:------------------:|:------------------:|:------------------:|:------------------:|
|1|田中|1|18|1|/aaa/abc/123456|a=123;b=a1a1a1a|
|2|山田|1|26|47|/aaa/bcd/456789|a=abc;b=b2b2b2b|
|3|加藤|2|39|25|/bbb/abc/123456|a=cde;b=a1b2a1b2|
|4|佐藤|2|27|14|/bbb/bcd/456789|a=123;b=a1a1a1a|
|5|大野|1|50|11|/ccc/efg/789101|a=abc;b=b2b2b2b|
|6|高橋|1|45|6|/ccc/hij/121314|a=cde;b=a1b2a1b2|
|7|阿部|2|36|4|/ddd/klm/123456|a=123abc;b=aaaa1111|


### 置換
REPLACE(string1, old_chars, new_chars)

```sql

SELECT *,REPLACE(cookie, 'a=', 'aaa=') FROM test.test4 ;
```

|cookie |col6 |
|:------------------:|:------------------:|
|a=123;b=a1a1a1a|aaa=123;b=a1a1a1a|
|a=abc;b=b2b2b2b|aaa=abc;b=b2b2b2b|
|a=cde;b=a1b2a1b2|aaa=cde;b=a1b2a1b2|
|a=123;b=a1a1a1a|aaa=123;b=a1a1a1a|
|a=abc;b=b2b2b2b|aaa=abc;b=b2b2b2b|
|a=cde;b=a1b2a1b2|aaa=cde;b=a1b2a1b2|
|a=123abc;b=aaaa1111|aaa=123abc;b=aaaa1111|

### 該当する文字から○文字目の抽出
substr(string, start, length) 
でstartからlength文の文字列を返してくれます。
なお、１文字目は１からです。0ではないです。

```sql

SELECT cookie,substr(cookie, 3, 3) FROM test.test4 ;
```

|cookie |col6 |
|:------------------:|:------------------:|
|a=123;b=a1a1a1a|123|
|a=abc;b=b2b2b2b|abc|
|a=cde;b=a1b2a1b2|cde|
|a=123;b=a1a1a1a|123|
|a=abc;b=b2b2b2b|abc|
|a=cde;b=a1b2a1b2|cde|
|a=123abc;b=aaaa1111|123|

### 正規表現で合致する部分のうち一部を抽出
regexp_extract(string, pattern, group)
でstringの中から、正規表現に該当するgroupを抽出します。
なお、使用できる正規表現一覧は下記にあります。
 [POSIX 演算子](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/pattern-matching-conditions-posix.html)

以下のようにpattern()で囲うことで、
groupわけを行うことができ、3つめの引数の数値のgroupを抽出します。

```sql

SELECT cookie, regexp_extract(cookie, '(a=)([^;]+)(;)(b=)([^;]+)', 2) FROM test.test4 ;
```

|cookie |col1 |
|:------------------:|:------------------:|
|a=123;b=a1a1a1a|123|
|a=abc;b=b2b2b2b|abc|
|a=cde;b=a1b2a1b2|cde|
|a=123;b=a1a1a1a|123|
|a=abc;b=b2b2b2b|abc|
|a=cde;b=a1b2a1b2|cde|
|a=123abc;b=aaaa1111|123abc|


# parse系
### parseして特定の位置の値を取得
split_part(uri, 'パースする文字列', 取得する位置)
こちらも最初が1。

```sql

SELECT uri, split_part(uri, '/', 2) FROM test.test4 ;
```

|uri|_col1|
|:------------------:|:------------------:|
|/aaa/abc/123456|aaa|
|/aaa/bcd/456789|aaa|
|/bbb/abc/123456|bbb|
|/bbb/bcd/456789|bbb|
|/ccc/efg/789101|ccc|
|/ccc/hij/121314|ccc|
|/ddd/klm/123456|ddd|



# 正規表現でLIKE
### 正規表現でLIKE
regexp_like(string, pattern)
戻り値はbooleanです。

```sql

SELECT cookie FROM test.test4 where regexp_like(cookie, 'a=[^;0-9]+;b=.*') ;
```
|cookie |
|:------------------:|
|a=abc;b=b2b2b2b|abc|
|a=cde;b=a1b2a1b2|cde|
|a=abc;b=b2b2b2b|abc|
|a=cde;b=a1b2a1b2|cde|



# 基数変換
from_base(string, radix) を使います。
radixでstringの基数を指定し、変換結果を10進数で返してくれます。

今回は例として16進数文字列から10進数へ変換します。
かなり雑ですが以下のようなテーブルを用意します。

|　id | name | 16base |
|:-----------------:|:------------------:|:------------------:|
|1|田中|12D687|
|2|山田|1E240|
|3|加藤|1E240|
|4|佐藤|12D687|
|5|大野|1E240|
|6|高橋|1E240|
|7|阿部|12D687|


```sql

SELECT *,from_base("16base", 16)  FROM test.test5 ;
```

|　id | name | 16base |_col2|
|:-----------------:|:------------------:|:------------------:|:------------------:|
|1|田中|12D687|1234567|
|2|山田|1E240|123456|
|3|加藤|1E240|123456|
|4|佐藤|12D687|1234567|
|5|大野|1E240|123456|
|6|高橋|1E240|123456|
|7|阿部|12D687|1234567|

ばっちしでした。