AWS CLIを使用しS3のバケット内のファイルを部分一致で削除する

概要

タイトルの通りです。 AWS CLIを使用してS3のバケット内にあるファイルを、部分一致で条件が一致したものだけ削除する方法です。

AWS CLIの設定方法は以下の記事でも簡単に触れているので、もしわからない方は参考までに。

024minion.hatenablog.jp

やりかた

rmコマンドを使用し、以下の方法で部分一致で削除が可能です。

aws s3 rm s3://BUCKETNAME/ --exclude '*' --include '*HOGE*' --recursive

BUCKETNAMEには対象ファイルが格納されているバケットのパスを、HOGEの部分には部分一致させたい文字列を入力してください。

一度 --exclude '*' で全ファイルを除外し、 --include '*HOGE*' で一致するものを含めます。--recursive は一致したすべてのファイルに対して実行するオプションです。

確認のため --dryrun を一度実行した方が安全そうです。

aws s3 rm s3://BUCKETNAME/ --exclude '*' --include '*HOGE*' --recursive --dryrun

MySQLで同一ユーザの複数レコードから最新のデータのみ抽出する

概要

MySQLでテーブル内に同一ユーザ（ユーザじゃなくてもいいですが）のレコードが複数入っている状態で、ユーザごとの最新もしくは一番古いレコードのみを抽出する方法を紹介します。

例えば、user_purchase という、ユーザごとに何をいつ購入しているかの情報を保有しているテーブルがあるとします。
今回のケースだと、赤文字で示されている最新データだけを引っ張ってくる方法になります。

数字のみの場合

抽出するデータが数字のみの場合は以下のように簡単に抽出が可能です。今回のケースだと revenue のみを抽出するパターンです。

SELECT
  user_id
, MAX(created_at*10000+revenue)%10000 AS last_revenue
FROM user_purchase
GROUP BY user_id
;

user_id で GROUP BY し、MAX() 関数を使用し一番最新の created_at のレコードを引っ張ってきます、その際datetime型である created_at に10000を掛け（桁数は抽出するデータに合わせて適当に）対象のカラムのデータを足します。最後にそれを%10000で余りを出してあげれば最新レコードの数字のみが残るという理屈です。

順を追って実行してみるとこういった実行結果になります。

SELECT created_at ...
> "2018-01-01 01:00:00"

SELECT craeted_at * 10000 ...
> 201801010100000000

SELECT created_at * 10000 + 10 ...
> 201801010100000010

SELECT (created_at * 10000 + 10)%10000 ...
> 10

文字列を抽出する場合その①

文字列を抽出する場合は少しだけ複雑になりますが、以下のように実現可能です。

SELECT
  us1.user_id AS user_id
, us2.name AS last_name
FROM
  (SELECT
    user_id
  , MAX(created_at) AS last_created_at
  FROM user_purchase
  GROUP BY user_id
  ) AS us1
JOIN user_purchase AS us2
  ON us1.user_id = us2.user_id
  AND us1.last_created_at = us2.created_at
;

ネスト内でユーザごとの最新レコードの created_at を抽出し、そこにもう一度同じテーブルをJOINし user_id と created_at でデータの紐づけをしてあげれば最新のレコードだけ取り出すことが可能です。

文字列を抽出する場合その②

ネストしてJOINを行うとクエリが複雑になるので、数字のみを抽出した場合と同じような書き方をしてあげることも可能です。

SELECT
  user_id
, SUBSTR(MAX(CONCAT(created_at, name)), 20) AS last_name
FROM user_purchase
GROUP BY user_id
;

createt_at と name を CONCAT 関数で文字列としてくっつけてしまい MAX() 関数で一番大きい（最新）のデータのみにしたあと SUBSTR() 関数で created_at の部分のみ（20文字目以前のみ）除外します。

順を追って実行するとこうなります。

SELECT user_id, CONCAT(created_at, name) ...
> 1234　"2018/05/03 12:00:00リンゴ"
> 1234　"2018/05/02 12:00:00メロン"
> 1234　"2018/05/01 12:00:00バナナ"

SELECT user_id, MAX(CONCAT(created_at, name)) ...
> 1234　"2018/05/03 12:00:00リンゴ"

SELECT user_id, SUBSTR(MAX(CONCAT(created_at, name)), 20) ...
> 1234　"リンゴ"

AdjustのローデータをS3に毎時自動でアップロードする方法

概要

アプリの広告計測ツールである Adjust にはローデータを毎時S3に自動で転送する機能があるのですが、ドキュメントがまぁとっつきにくい、そしてサポートが塩対応なので解決の手助けを一切してくれない、結果設定にぼちぼち時間を使ってしまったので、これから設定する人が僕と同じ過ちを侵さないために設定方法を備忘録としてまとめました。

ちなみに、ローデータエクスポートの設定方法はこちらにまとまっています。 docs.adjust.com

尚、本記事ではAWSの設定が英語になっているので、英語ベースでAWS設定方法を記載しています（堪忍）。
では、さっそく設定していきましょう。

S3のバケットを作成する

AWSのアカウントがない人は以下を参考にアカウントを用意してください。

aws.amazon.com

さっそくローデータをアップロードするS3のバケットを作成をしていきましょう。

ちなみにS3は、従量課金制なので本設定によって発生する料金は各自の責任でお願いします。とはいえ、あんまり激しい使い方をしない限り心配するような額にはならないと思います。

まずはAWSにログインし、サービス検索にS3といれるか、ストレージのS3を選択しS3に移動します。

S3に移動したら Create Bucket を押下しバケットの作成を行っていきます。

設定モーダルが表示されたら、必要な情報を記載していましょう。
今回の例では、 Bucket name を adjust-test に、Region を Asian Pacific (Tokyo) に設定し、Copy settings from an existing bucket は特に設定しないで Next ボタンを押下。

続いて、 Set properties と Set permissions 画面が表示されるので、それぞれ今回は特に何も設定せずに Next ボタンを押下。

ここまで設定すると、 Review 画面が表示されるので、Create bucket ボタンを押下してバケットを作成します。

IAMでUserを作成する

続いてAdjustがS3にアクセスするためのKEY情報を作成するために、IAMでUserを作成します。

S3同様、AWSのダッシュボードにてサービス検索にIAMといれるか、ストレージのIAMを選択しIAMに移動します。

IAMに移動したら、メニュー内の Users を選択し、Add user ボタンを押下してください。

Userの作成画面に移動するので、User name に任意の名前を（今回の例では Adjust にしています）、Access type の Programmatic access にチェックをし、Next permissions を押下。



続いてPermissionの設定画面が表示されるので、AdjustがS3にデータを転送するために必要な権限を付与します。

Attach existing policies directly を選択し、Create policy ボタンを押下しPolicyの作成を行います。



Policyの作成画面が別タブで開かれるので、JSON でPolicyを直接編集します。



Adjustの設定ドキュメントに記載されている、AdjustがS3にアクセスするためのPermission情報をもとにJSONを作成します。

今回は先程作成したS3バケットの adjust-test を使用するので、以下のように入力します。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::adjust-test"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::adjust-test/*"
            ]
        }
    ]
}

それぞれ設定した内容が気になる人は以下のドキュメントを参照してみてください。

docs.aws.amazon.com
入力が完了したら、Review に進みます。Name を入力しPolicyの作成を行いましょう。今回の例では adjust-policy という名前を付けます。

Policyが完成したので、Userの作成画面に戻り Refresh ボタンを押下しPolicyを最新にします。

更新を行ったので先程作成したPolicyが反映されているはずです、Policyの検索窓に作成したPolicy名を入力すると表示されるのでチェックを入れ Review に進み Create Userを押します。

ここまでの作業でUserの作成が完了しました、Access key ID と Secret access key が吐き出されるので、コピーもしくはCSVをダウンロードし大切に補完しましょう。このKEY情報はAdjust側での設定時に必要になります。

Adjsutダッシュボードで転送設定をおこなう

S3のバケット作成と、アクセスするためのUserを作成したので今度はAdjust側のダッシュボードにログインし転送の設定を行います。

まずは設定したいアプリの設定ボタンを押下。



ナビゲーションが表示されるので ローデータエクスポート を選択。



CSVアップロード を選択。



転送の設定画面が表示されるので、各項目を埋めていきましょう。

まず。転送設定を有効にするため一番上部に表示されているスライダーを Enabled] に設定します。

続いて キー と SECRET に先程AWSで作成したUserのキー情報を入力し、バケットは一番最初に作成したバケット名を入れます。

エクスポートするイベントを選択してください と表示されている部分を押して必要な項目を選択してください。ちなみにデフォルトだと何も選択されていないので、ここの設定を飛ばすとAWS側の設定が完了していてもデータは転送されないので注意です。

最後の作業です、CSVの定義 にエクスポートするCSVの構成情報を記載します。

CSVの定義はこちらを参照して下さい。

adjust placeholder list

書き方が不明な場合、以下僕が作った定義を真似して編集してみてください。

{app_name},{app_name_dashboard},{store},{network_name},{campaign_name},{adgroup_name},{creative_name},{click_referer},{click_time},{activity_kind},{adid},{idfa},{android_id},{user_agent},{ip_address},{region},{country},{language},{device_name},{timezone},{event_name},{installed_at},{created_at}

定義の入力が完了したら、SAVE ボタンを押下し設定を保存します。以上で全ての設定が完了しました。
一時間おきにデータがS3のバケットにPUTされるはずなので、タイミングを見てデータが転送されているか確認をおこなってください。

賢いシートを作ろう！Googleスプレッドシートの便利技 - 中級編 -

一年前くらいに書いた記事 かっこいいシートを作ろう！Googleスプレッドシートの便利技 - 初級編 - - How to make datas our friends の続き、中級編です。（もう書いてから1年経つのか...）

本記事では、シートがスマートになる便利な技をいくつか紹介します。

その①：条件に合致した行のみカウント

下記の表で、優先度ごとにToDoの数をカウントしてみましょう。

COUNTIF関数を使用し条件にマッチする行のみをカウントすることが可能です。COUNTIF関数の構文は以下の通りです。

=COUNTIF(範囲, 条件)

上記画像表C2の計算を行う場合はこのように記載します。

=COUNTIF(A1:A6,"A")

条件が複数ある場合はCOUNTIFS関数を使用しましょう。

その②：条件に合致したデータ同士を紐付ける

下画像内の表では、受注管理シートと商品金額が別々に管理されています。
受注管理シート内の金額列が空なのでVLOOKUP関数を使用し商品データ表から対応する商品の金額を引っ張ってみましょう。

VLOOKUP関数の構文は以下です。

=VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み]) *[]は任意

検索キーには検索対象の商品名が記載されているセルを指定、範囲は商品データの範囲をまるっと指定、指数は表の何列目の値を返すか指定、今回のケースでは金額が二列目にあるので指数は 2 になります。
今回はシート内D3に下記のように記載し、D9までコピーします。

=VLOOKUP(C3,F$2:G$4,2)
※コピーするとき値がスライドしちゃうので$で値固定を忘れずに

無事に金額表示されました。

その③：条件に合致した行の値のみSUMする

最初に紹介したCOUNTIF関数のSUM版です。上で使用した受注管理シートから状況が発送済みになっている商品Aの合計金額を売上として計算しましょう。
SUMIFS関数の構文は以下です。

=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, ...], [条件2, ...])

合計範囲に合計する値が入っている列を、条件範囲には条件にあたる「状況」「商品名」が入っている列を指定し、更にそれぞれの列ごとに条件を指定します。 今回は以下のように関数を記載します。

=SUMIFS(D2:D9,B2:B9,"発送済み",C2:C9,"A")

Pivot機能を使うのもありですが、自由度が高いので行列が多い複雑なデータを扱う際に便利です。

その④：関数エラー時の表示を整えよう

受注管理シートに先程組み込んだVLOOKUP関数のオプション[並べ替え済み]をFALSEに変更し、商品データの商品名Bの行を削除するとD列にエラーが発生します（#N/Aとなっている部分）。

これはVLOOKUP関数が商品Bの金額を商品データ側から見つけられないためにエラーを返している状態です。（[並べ替え済み]でFALSEを指定すると対応するデータがないとエラーを返す、TRUEを指定すると最も近い値を返す、何も指定しないとデフォルトでTRUEが設定される）

今回はIFERROR関数を使用し、もしエラーが出た場合「商品データに値がありません」と表示されるようにしましょう。

IFERROR関数の構文は以下です。

=IFERROR(値, [エラー値])

値に関数をまるっと入れて、[エラー値]にエラー時の挙動を記載するので、表示したい文字列を記載しましょう。

今回はこんな感じにしました。

=IFERROR(VLOOKUP(C3,F$2:G$4,2,FALSE),"商品データに値がありません")

見た目はさておき、エラーが文字列に置き換わりました。

その⑤：ARRAYFORMULA関数で無駄なコピペを減らそう

例えば上の受注管理シートが常にデータが追加されていくような類ものだとします。その場合、データが追加される度にD列に記載した関数を追加した行までコピーしなくてはいけなくなります。

ARRAYFORMULA関数を使用し、行が追加された際に自動でD列の関数が適用されるようにしましょう。

ARRAYFORMULA関数の構文が以下です。

=ARRAYFORMULA(配列数式)

配列数式...なんのこっちゃですよね笑
C3:C9みたいな複数の値を含むものが配列ですね。
とにかく関数を書いてみましょう。

=ARRAYFORMULA(IFERROR(VLOOKUP(C3:C,F$2:G$4,2,FALSE),"商品データに値がありません"))

上で書いたD列の関数をARRAYFORMULA()で囲ってあげて、VLOOKUP関数の検索キーをC3から配列であるC3:Cに書き換えてあげます。
これで、ARRAYFORMULA関数がC3:CまでひたすらにVLOOK関数の計算を繰り返してくれます。
D3にしか関数を書いていないのに、下まで関数を適用し計算してくれていますね。 しかし、困ったことに範囲をC3:Cで指定したので、下の方まで計算をおこないエラーになってしまったため、IFERROR関数が「商品データに値がありません」と表示する挙動をしてしまっています。
IF関数を使って関数の暴走を沈めましょう。

=ARRAYFORMULA(IF(C3:C="","",IFERROR(VLOOKUP(C3:C,F$2:G$4,2,FALSE),"商品データに値がありません")))

IF文でC3:Cにデータがない(空文字"")なら空文字を返すように記載してあげます。簡単にIF分の要素だけ書くとこんな感じ。

=IF(C列が空文字,TRUE=空文字を返す,FALSE=関数を実行)

無事、暴走が収まりました。

力尽きたので終了

力尽きてきたので、今回はここで終了します。笑
今回紹介させていただいた関数は普段僕も業務で頻繁に使うものです。
スプレッドシート上で大量のデータを扱うことが多い人は覚えておいて損はない関数だと思います。

スプレッドシートの中級編は第二弾をいずれ書こうかなと思っています。ぷらす、上級編としてGASを使った自動化等記載予定でいます。

超初心者がDockerをいじってみた話 - その1 インストール編 -

あらすじ

普段データに関係する業務しかやってこなかったため、Dockerを使ってどーのこーのという経験はほとんどなかったが、ちょっといじってみたいな〜という気持ちは以前からあった。

最近友達となにかWEBサービスを作ろうぜ！というノリでDockerをいじることになったので備忘録的な感じでまとめていく予定。

ちなみに本記事ではDockerを以下の環境で実行しています。
（無駄にいいPCを使ってますｗ）

MacBook Pro 13-inch
OS: Mac OS v 10.12.6 (Sierra)
CPU: 3.3 GHz Intel Core i7
メモリ: 16 GB

Mac OS前提で書いているので、Winマシンだと基本的な操作は一緒ですが、インストール等準備の部分は変わってくると思います。

Dockerって何って話

Dockerってなんぞ、という説明をちゃんとできる気がしない（というか理解できていない）ので、気になってしょうがない or 理解したいという人は綺麗にまとめてくれている人がいっぱいいるのでそちらを読んで下さい笑

e-words.jp

paiza.hatenablog.com

簡単にまとめると、開発用の仮想環境を作れるぞっていう認識( ˘ω˘)ｽﾔｧ

Dockerのインストール

ここからダウンロードできるのでいい感じにインストールしてください。

Install Docker for Mac | Docker Documentation

ちなみに僕はHomebrewでインストールしています。

techracho.bpsinc.jp

docker versionとターミナルで打ってこんな感じになれば正しくインストールできています。

$ docker version
Client:
 Version:   18.01.0-ce
 API version:   1.35
 Go version:    go1.9.2
 Git commit:    03596f5
 Built: Wed Jan 10 20:05:58 2018
 OS/Arch:   darwin/amd64
 Experimental:  false
 Orchestrator:  swarm

Server:
 Engine:
  Version:  18.01.0-ce
  API version:  1.35 (minimum version 1.12)
  Go version:   go1.9.2
  Git commit:   03596f5
  Built:    Wed Jan 10 20:13:12 2018
  OS/Arch:  linux/amd64
  Experimental: false

ちなみにDockerはdocker-machine sshかアプリケーションから実行できます。

$ docker-machine ssh
                        ##         .
                  ## ## ##        ==
               ## ## ## ## ##    ===
           /"""""""""""""""""\___/ ===
      ~~~ {~~ ~~~~ ~~~ ~~~~ ~~~ ~ /  ===- ~~~
           \______ o           __/
             \    \         __/
              \____\_______/
 _                 _   ____     _            _
| |__   ___   ___ | |_|___ \ __| | ___   ___| | _____ _ __
| '_ \ / _ \ / _ \| __| __) / _` |/ _ \ / __| |/ / _ \ '__|
| |_) | (_) | (_) | |_ / __/ (_| | (_) | (__|   <  __/ |
|_.__/ \___/ \___/ \__|_____\__,_|\___/ \___|_|\_\___|_|
Boot2Docker version 18.01.0-ce, build HEAD : 0bb7bbd - Thu Jan 11 16:32:39 UTC 2018
Docker version 18.01.0-ce, build 03596f5

いちいちかわいいからテンション上がるｗ

アプリケーションから起動するときはこいつを立ち上げればOK！！

いったん、今回はここまで。

次はDockerを簡単にさわさわした記事を書こうと思います( ･`д･´)

BigQueryでint32とint64の型の不一致で苦しんだ話

概要

CASE文を使って条件にマッチすればINT型のデータを、それ以外は0にしてSUMしようとしたら、型が違うと怒られたので、どう対処したかの備忘録。

要はこんな感じのクエリ。

SUM(CASE WHEN 条件 THEN 数(INT) ELSE 0 END) AS cnt

ちなみにデータがない場合、NULL値ではなく0を入れたかったのでこういう書き方をしている。

前提として、BigQueryはLegacySQLで書いている。

ちなみにテーブルの中身は超絶端折ってこんなイメージ。

user_id	created_at	id	count
1	"2017-12-29 10:00:00"	hoge	0
2	"2017-12-28 10:00:00"	fuga	100
3	"2017-12-29 10:00:00"	hoge	90
4	"2017-12-28 10:00:00"	fuga	0
5	"2017-12-29 10:00:00"	hoge	15

怒られた内容

まず、こんな感じのクエリを叩きました。

SELECT
  user_id
, DATE(DATE_ADD(created_at, 9, "HOUR")) AS dt
, SUM(CASE WHEN id = 'hoge' THEN count ELSE 0 END) AS hoge_count
FROM TABLE_NAME
GROUP BY user_id, dt
ORDER BY user_id ASC, dt DESC
;

するとこんなエラーが。THENとELSEの型は揃えてねと怒られる。

Error: CASE expects THEN and ELSE expressions to have similar types, but got different type int32 from ELSE expression which is not compatible with type uint64.

うーん、面倒くさいなーと思いIFNULLで回避しようと試みる。

SELECT
  user_id
, DATE(DATE_ADD(created_at, 9, "HOUR")) AS dt
, IFNULL(SUM(CASE WHEN id = 'hoge' THEN count ELSE NULL END), 0) AS hoge_count
FROM TABLE_NAME
GROUP BY user_id, dt
ORDER BY user_id ASC, dt DESC
;

また怒られる。

Error: Argument type mismatch in function IFNULL: 'f0_' is type uint64, '0' is type int32.

対処法

どうやらBigQueryのバグらしい。

https://issuetracker.google.com/issues/35905178

そういえば、これUNIONする時にたまーに出るやつや。

BigQueryはINTEGER型しか持たいないが内部的にはint32とint64に分かれているらしいので、そいつが悪さしているのかもしれない。

対処方法は、countをINTEGER()で囲ってあげたら怒られなくなった。

SUM(CASE WHEN id = 'hoge' THEN INTEGER(count) ELSE 0 END) AS hoge_count

ちなみに、今回テーブルは結構複雑なクエリを保存したviewテーブルを読み込んでいたので、何回もネストしているような複雑なクエリ等で内部的な型の不一致が発生しやすいのかも。（よくわかんないけど）

pandasでunix時間をdatetimeに変換する

概要

某計測ツールから落としてきたCSVファイルを pandas で読み込んでゴニョニョしていたのだが、日時情報がUNIX時間だったので、pandas で datetime に変換した備忘録。

やったこと

to_datetimeを使用して変換しました。

import pandas as pd

dataset = pd.read_csv(PATH_TO_CSV, encoding="utf_8")

# created_at が unix 時間だったので datetime に
dataset['created_at'] = pd.to_datetime(dataset['created_at'].astype(int), unit='s')

無事、datetime になりました。

以上。