中野智文のブログ

データ・マエショリストのメモ

SQLクエリのみでCREATE TABLEを使わず一時的な小さなテーブルを作る

背景

SQLクエリのみでCREATE TABLEを使わず一時的な小さなテーブルを作りたい時がある。

例えば、{'ios': 1, 'android': 2, 'windows': 3} みたいな小さなテーブルが一時的に欲しいことはある。 それがないために、IF(os_id=1, 'ios', IF(os_id=2, 'android', IF(os_id=3, 'windows', NULL))) AS os_name みたいな謎のクエリを書いた上で WHERE os_name is not NULL のような条件式があったり、難しい。

もし小さなテーブルがあればJOINするだけなのにと思う。ある条件にて複数行にすることは ARRAY と UNNEST を使ってできなくはないが、より可読性が落ちる。(JOINの方が可読性が高いと思っているのは私だけかもしれないが)

UNION ALL でひたすら結合する方法

例:

SELECT
  1 AS os_id, 'ios' AS os_name
UNION ALL SELECT
  2 AS os_id, 'android' AS os_name
UNION ALL SELECT
  3 AS os_id, 'windows' AS os_name

ARRAY を UNNEST する方法

例:

SELECT
  _os.id as os_id,
  _os.name as os_name
FROM (
  SELECT
    [STRUCT(1 AS id, 'ios' AS name),
    STRUCT(2 AS id, 'android' AS name),
    STRUCT(3 AS id, 'windows' AS name)] os)
JOIN
  UNNEST(os) _os

両社とも BigQuery (標準SQL)にて確認。

luigi RangeDaily にて謎のエラー

背景

luigi RangeDaily の謎のエラーについて書く。

なお、RangeDaily については下記記事を参照。 nakano-tomofumi.hatenablog.com

ValueError: invalid literal for int() with base 10: 'None'

RangeDaily のパラメータ now が None であると怒っている。しかし、デフォルトがNone なのである。謎であるが…

対応

import time
...
    RangeDaily(....
           now=int(time.time()),
           ...)

now を具体的に与える。「他は datetime.date 型とかで与えているのに、ここだけunixtime秒かよ」とか突っ込みたくなる気持ちがある。

luigi.parameter.UnknownParameterException: XxxXxxx[args=(), kwargs={'parameter': 'XXXX', 'None': datetime.date(2018, 4, 26)}]: unknown parameter None

date パラメータとかに入ってほしいのに、None というパラメータに無理やり日付を入れようとしている。

対応

    RangeDaily(....
           param_name='date')
           ...)

RangeDaily にて日付の入ってほしいパラメータを、param_name にて指定する(この例の場合は date)。 これを指定しなくてもうまくいく場合もあるんだが…

aws s3 のファイルを圧縮しながらストリーミングでgcsに置く

背景

早い話が、gsutil の -z オプションの挙動(静的ホスティングじゃないとき)がよくわからなかったから。

cp - Copy files and objects  |  Cloud Storage Documentation  |  Google Cloud

コマンド

aws s3 cp s3://your-s3-bucket/your-file.txt - | gzip -c | gsutil cp - gs://your-gs-bucket/your-file.txt.gz

ちなみにこういう場合、通信料金的には s3 が置いてあるリージョンのEC2の(マイクロ)インスタンスがおおすすめ。

注意

aws のプロファイルが一つじゃなく、.boto ファイルとかを頑張れ、-z オプションの挙動に納得しているなら gsutil だけでいけそう。 もっというと、複数同時に走らせたりとかも可能みたいなので、できる方はそちらで。

google cloud storage の権限は defacl でバケット単位で設定する

背景

google cloud storage を使って共有しようと思ったが、設定はどうやるのか?

方法

  • gsutil defacl コマンドで(バケット単位で)設定する。
  • 既存のファイルについては、gsutil acl コマンドの -r オプションで追加する

例(user@example.comさんにread 権限を与えるケース):

gsutil defacl ch -u user@example.com:R gs://your-bucket
gsutil acl ch -u user@example.com:R -r gs://your-bucket

どうしてか

単なる ACL の設定だと、ファイル単位でしか設定できない。 よって新規のファイルが現われたときに、その都度設定する必要がある。

新規ファイルのデフォルトのACLを設定をするのが、gsutil の defacl コマンド。 Webのコンソール画面からは、デフォルトのACLの設定ができない(確認さえできない。糞)。

詳しくは以下のページが参考になる。

iga-ninja.hatenablog.com

その他

可能ならば、ユーザ単位で権限を設定するのでなくグループ単位で設定したい。

ec2 でシンプルな mail クライアント mailx

背景

ec2 の cron を実行したとき、標準出力に何か出ると、メールで送られてくる仕組みになっている。 多くの tips では、ハードディスクがいっぱいになるから、これを停止するものが多いが、 停止する前にエラーなどが出ていないか、確認したいものである。

ところが、デフォルトではメールクライアントは入っていない。 単なる cron の結果を読むだけなのだが、何が良いのだろうか。 スプールを直接読み書きするという方法もあると思うが…

とりあえず、 mailx

シンプルなものはよくわからないが、mailx というものがあるのでこれを入れてみる。

$ sudo yum install mailx
読み込んだプラグイン:priorities, update-motd, upgrade-helper
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ mailx.x86_64 0:12.4-8.8.amzn1 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

============================================================================================
 Package           アーキテクチャー   バージョン                  リポジトリー           容量
============================================================================================
インストール中:
 mailx             x86_64             12.4-8.8.amzn1              amzn-main            254 k

トランザクションの要約
============================================================================================
インストール  1 パッケージ

総ダウンロード容量: 254 k
インストール容量: 451 k
Is this ok [y/d/N]: 

消費される容量は 451k である。

早速 cron の実行結果が何なのか見てみる

....
make: pipenv: Command not found
...

なんと、pipenv が見つからずに失敗していた。

メールのヘッダーの下の方に何か書いてある。

X-Cron-Env: <LANG=en_US.UTF-8>
X-Cron-Env: <SHELL=/bin/sh>
X-Cron-Env: <HOME=/home/ec2-user>
X-Cron-Env: <PATH=/usr/bin:/bin>
X-Cron-Env: <LOGNAME=ec2-user>
X-Cron-Env: <USER=ec2-user>
Status: RO

環境変数がずらりと並んでいる。 おや? PATHが、/usr/bin:/bin だけしか設定されていない。これが原因だ。

…というわけで、イケてる mail クライアントだと、ヘッダーの部分は隠してしまうかもしれないが、 mailx だと cron のデバッグにちょうどいいのである。ただし、操作方法が分かりにくいのが難点である。

gcloud 系コマンド(gsutil bqなど)で、 which no python in ... というエラー

背景

gcloud 系のコマンドを実行すると、

which: no python2 in 自分のパス

というエラーが出る。

ただエラーが出るからと言って実行できないわけではなく、 python2.7 とか、python2 とかはパス上にあるからか、結局それを見つけに行って実行される。 ちなみに、たんなる python は python2.7 である。

その探しに行く時間(数秒)と上記のエラーメッセージがうっとうしい。

とりあえずの解決策

とりあえずシンボリックリンク

sudo ln -s /usr/bin/python2.7 /usr/bin/python2

これまでやったこと

  • gcloud config の確認
  • alias

luigi で resources を使ってリソース毎に同時実行数を制限する

背景

luigi は同時 workers オプションで同時 worker 数を制限できるが、CPU的には同時に16走っても問題ないが、DB的には一つしか接続したくない、という要望がある。

対応 resources を使う。

リソース毎の worker 数の設定と、それぞれのタスクがどれほどリソースを使うかをコードとして書く必要がある。

リソースごとの worker 数の設定

以下のリンクを読む。

Configuration — Luigi 2.7.5 documentation

ここで書いてない重要なことは2つある。

  • 例にある、hive や mysql は組み込み(ビルトイン)でもなんでもない。

例に書いてあるから、ここだけを設定すれば、hive での同時実行される worker 数が制限されると思ったら大間違い。 postgres など、書いていないリソースも同じ。これはあくまで例であって、実装しているタスクのコードに直接書かなくては効果がない。

  • リソース名は、自分で勝手に作っていい。

mysql のDBが2台あって、それぞれのリソース的には1接続づつしたい場合は、mysql1 と mysql2 として、

[resources]
mysql1=1
mysql2=1

と設定する。ただし先に述べたが、タスクのコードに書かないと全く意味はない。

タスクごとのリソースの設定

以下を読む。

http://luigi.readthedocs.io/en/stable/api/luigi.task.html#luigi.task.Task.resources

静的に、

  resources = {'mysql1': 1}

と書いてもいいし、@property を使って、動的に設定してもいい。

http://luigi.readthedocs.io/en/stable/luigi_patterns.html#avoiding-concurrent-writes-to-a-single-file

他にも設定したほうがいいこと

一つのクライアントでスケジューラーを使っている時はいいのだが、別のクライアントからスケジューラーにタスクが入って来てリソースの制限にぶつかると、 次のような状態になってペンディングタスクの実行をあきらめ止まってしまう。

   was not granted run permission by the scheduler

そんなことを防ぐためには、

[core]
worker_keep_alive=True

もしくは

[worker]
keep_alive=True

に設定しておく。

上記の二つが同じことはドキュメントでは確認できないが、下記のコードで確認できる。

https://github.com/spotify/luigi/blob/4af1d2236825375cdd064ce5d1c2a34a8b1414c0/luigi/worker.py#L357-L358

その他の参考

http://luigi.readthedocs.io/en/stable/luigi_patterns.html#tasks-that-regularly-overwrite-the-same-data-source