More than 5 years have passed since last update.

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

Last updated at 2019-11-07Posted at 2019-11-07

はじめに

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

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

ばっちしでした。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up