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

31747 ワード
AWS Athena SQL Presto AWS テキストリンク

はじめに

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

データソース

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

SQL クエリ、関数、および演算子

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すると


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である必要があります。


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。


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

結果。

_col0
1 34.42857142857143

条件分岐のCASE文

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

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

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


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

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

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

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を返すとすることで、
特定の値を持つレコードのカウントが可能です。


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)


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ではないです。


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 演算子

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


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。


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です。


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

基数変換

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 

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

ばっちしでした。

C++ Macro Usage
unbuntu20.04 ROS neoticのインストール