ただ枯れゆく大樹が如く

技術の話やら愚痴やら節操もなく徒然と

redshiftでunnestを実装する

AWS sql Redshift

Redshiftは以下のページに記載されている通りunnestはサポートされていない。

docs.aws.amazon.com

しかしながら昨今のRDBMS以外も多用される状況ではネストされたデータも多く、UNNESTは結構欲しい機能である。

なのでちょっち面倒だけど実装してみた。 ETLではなくELTを選択するなら意外と必要になるし。

  • 前提

    • generate_seriesも使えないので、あらかじめ連番を入れたseqテーブルを作成

  • UNNESTを模倣したSQL

SELECT
    json_extract_array_element_text(m.array_hoge, seq.i) AS hoge
FROM
(
    SELECT '["hoge1","hoge2","hoge3"]' AS array_hoge
) AS m
INNER JOIN seq ON i < JSON_ARRAY_LENGTH(m.array_hoge)
ORDER BY hoge;

AWS lambdaでglobal変数を使用した際の失敗談

AWS

調べているとstaging用のS3バケットにproductionモードで実行したデータが存在したので調査したところ、 コードの中で使用されているglobal変数に問題があった。 該当コードを簡略化したテストコードとその結果を以下に記載。

bucket_path='production_path'

def lambda_handler(event, context):
    global bucket_path
    if "test_mode" in event:
        bucket_path='staging_path'
    print(bucket_path)

実行時のパラメータにtest_modeが含まれている場合にglobal変数を書き換えるだけのもの。

  • 実行時パラメータが以下のようにtest_modeが含まれない状況で実行
{
  "_test_mode": ""
}
START RequestId: 78045b16-ae03-4be0-9c89-e8841c7026f3 Version: $LATEST
production_path
END RequestId: 78045b16-ae03-4be0-9c89-e8841c7026f3
REPORT RequestId: 78045b16-ae03-4be0-9c89-e8841c7026f3  Duration: 10.97 ms  Billed Duration: 100 ms     Memory Size: 128 MB Max Memory Used: 22 MB

同然出力されるのはproduction_path

  • 次に実行時パラメータが以下のようにtest_modeが含まれる状況で実行後、すぐに再度含まれない状況で実行
{
  "test_mode": ""
}
START RequestId: 119bcfb9-75a4-4bc2-ad34-cedb4f8518c5 Version: $LATEST
staging_path
END RequestId: 119bcfb9-75a4-4bc2-ad34-cedb4f8518c5
REPORT RequestId: 119bcfb9-75a4-4bc2-ad34-cedb4f8518c5  Duration: 1.72 ms   Billed Duration: 100 ms     Memory Size: 128 MB Max Memory Used: 22 MB  
{
  "_test_mode": ""
}
START RequestId: edf3a5ee-9d84-46a3-8973-216b2b863e2c Version: $LATEST
staging_path
END RequestId: edf3a5ee-9d84-46a3-8973-216b2b863e2c
REPORT RequestId: edf3a5ee-9d84-46a3-8973-216b2b863e2c  Duration: 0.25 ms   Billed Duration: 100 ms     Memory Size: 128 MB Max Memory Used: 22 MB

テストモードで実行した後に連続的に通常モードで実行すると、 テストモードで実行した際にglobal変数へ代入した値が残ったままになっており、 これが原因で通常モードの際にテストモードのbucket_pathが表示されてしまった。

コールドスタートしてないので代入値が残りっぱなしになってたというだけだけど、意外と見つからなかった。

gitのcommit-hashからPhabricatorチケットNo.の取得方法

Phabricator API

Phabricatorでプロジェクトの管理をしているとリリースブランチに完了タスク以外のcommitが含まれていないかを確認したいときが出てくる。

具体的に言うのであれば、リリース管理として不正なコードがリリースされていないことの証明をすることをIT監査で求められたりする。

というわけで以下に取得の方法を書いていく。

commit-hashの取得

単純にgit logコマンドで前回のリリースブランチと今回のリリースブランチの差分を取得する。

git log --pretty=oneline $OLD_BRANCH..$NEW_BRANCH | awk '{print $1}'
  • $OLD_BRANCH: 前回のリリースブランチ
  • $NEW_BRANCH: 今回のリリースブランチ

Phacricator上のリポジトリIDを取得

次のCommitPHIDを取得するためにリポジトリのPHIDを調べる必要があるので、phid.lookupで取得する。

echo '{"names": ["$REPOSITORY_NAME"]}' | arc call-conduit --conduit-uri $PHABRICATOR_URL phid.lookup | jq .
  • $REPOSITORY_NAME: 取得したいリポジトリの名前
  • $PHABRICATOR_URL: 管理しているPhabricatorのURL

commit-hashに紐付くPhabricatorのIDを取得(CommitPHID)

PhabricatorのCommitPHIDはdiffusion.querycommitsを使用することで取得できる。

echo '{"repositoryPHID": "$REPOSITORY_PHID","names": $COMMIT_LIST}' | arc call-conduit --conduit-uri $PHABRICATOR_URL diffusion.querycommits | jq -r ".response.data[].phid"
  • $REPOSITORY_PHID: 上記で取得したPhabricatorPHID
  • $COMMIT_LIST: git logで取得したhashをリスト形式で記載したもの
    • 例) ["hoge", "hogehoge"]
  • $PHABRICATOR_URL: 管理しているPhabricatorのURL

CommitPHIDに紐付くDifferentialのIDを取得(DifferentialPHID)

ここからが探していても分かりづらいところで、CommitPHIDからDifferentialPHIDを取得する際に、diffusionとかdifferentialのAPIでは取得できない。 そこでedge.searchを使用する。これはsourcePHIDsに指定したPHIDからtypesに指定した情報を取得できる。commit.revisionでCommitPHIDに紐付くDifferentialのPHIDが取得できる。

echo '{"types": ["commit.revision"], "sourcePHIDs": $COMMIT_PHID_LIST}' | arc call-conduit --conduit-uri $PHABRICATOR_URL edge.search | jq -r ".response.data[].destinationPHID"
  • $COMMIT_PHID_LIST: 上記で取得したCommitPHIDをリスト形式で記載したもの
    • 例) ["PHID-CMIT-hoge", "PHID-CMIT-hogehoge"]
  • $PHABRICATOR_URL: 管理しているPhabricatorのURL

DifferentialPHIDからDIFFのチケット番号とタイトルを取得

diffの詳細については先で取得したDifferetialPHIDがあれば、differential.revision.searchを使用して簡単に取得できる。

echo '{"constraints": {"phids": $DIFF_PHID_LIST}}' | arc call-conduit --conduit-uri $PHABRICATOR_URL differential.revision.search | jq -rc '.response.data[] | {"id": .id, "title": .fields.title}'
  • $DIFF_PHID_LIST: 上記で取得したDifferentialPHIDをリスト形式で記載したもの
    • 例) ["PHID-DREV-hoge", "PHID-DREV-hogehoge"]
  • $PHABRICATOR_URL: 管理しているPhabricatorのURL

DifferentialPHIDに紐付くTaskのIDを取得(TaskPHID)

こちらはCommitPHIDからDifferentialPHIDを取得したようにDifferentialPHIDからTaskPHIDを取得する。この場合のtypesはrevision.taskとなる。

echo '{"types": ["revision.task"], "sourcePHIDs": $DIFF_PHID_LIST}' | arc call-conduit --conduit-uri $PHABRICATOR_URL edge.search | jq -r ".response.data[].destinationPHID"
  • $DIFF_PHID_LIST: 上記で取得したDifferentialPHIDをリスト形式で記載したもの
    • 例) ["PHID-DREV-hoge", "PHID-DREV-hogehoge"]
  • $PHABRICATOR_URL: 管理しているPhabricatorのURL

TaskPHIDからTaskのチケット番号とタイトルを取得

taskの詳細については先で取得したTaskPHIDがあれば、maniphest.searchを使用して簡単に取得できる。

echo '{"constraints": {"phids": $TASK_PHID_LIST}}' | arc call-conduit --conduit-uri $PHABRICATOR_URL maniphest.search | jq -rc '.response.data[] | {"id": .id, "title": .fields.name}'
  • $TASK_PHID_LIST: 上記で取得したTaskPHIDをリスト形式で記載したもの
    • 例) ["PHID-TASK-hoge", "PHID-TASK-hogehoge"]
  • $PHABRICATOR_URL: 管理しているPhabricatorのURL

最後に

この辺りはPhabricatorのドキュメントにも記載されてはいるが、率直に言って非常にわかりにくいドキュメントなのでなかなかに苦労した。 しかし、それなりに取得できる情報は多いので一度見てみるのも良いと思う。

https://secure.phabricator.com/conduit/

就職活動に向けて～離職率に隠された罠～

就職 転職

就活関連のニュースで、企業による離職率の情報提供ってのがあったけど、安易に公表される離職率だけ見て優良だとか思ってはいけない。

何故なら離職率なんてものは起業直後の企業以外は非常に小さな値に計算上はなるからだ。

当然退職者は若手が多い

例えばの話、従業員数1000人の会社があるとする。おそらくだか毎年の新卒採用人数は20人くらいだろう。 実質退職を考える年齢は限られてくる。40を超えて退職を考える人は少ない、50を超えると皆無に近いと言っていいだろう。30代も結婚生活を始めていたら退職など考えづらい。 と考えると、退職を主に考えるのは20代となる。

若手退職者から退職率を算出してみる

20代の中で主な退職のタイミングは1年目、3年目、5年目であると言われる。 これを元に考えると1000人の中で5年目までの社員人数は100人。すなわち5年目までの社員のうち半分が辞めたとしても、退職率は5%となる。 


退職率は当てにならん

まぁ上のような計算になるから、求人票などに「退職率5%のアットホームで過ごしやすい職場です。」とか書かれてるのを目にするけど、決して5%は低い数値じゃない。 所属している社員の顔つきや目を見れば何となく職場の状況はわかるので、そういった直感ベースで判断したほうがマシ。

最後に就職は何を優先すべきか…

それは間違いなく金銭条件。 私自身すでに3社目であるけど、自信を持って言えるのは職を探すときの最優先項目は金銭条件である。 ぶっちゃけ楽しいことでも仕事にすれば1、2年で飽きるし、かならず職場に嫌なヤツがいたり嫌な習慣がある。 そんなときに金さえ良ければ精神的に余裕があるし耐えられることも多く、またどうしても嫌ならすぐに預貯金を貯められるのですぐに転職もできるしね。

浮動小数点型の誤差による四捨五入の問題点

sql 浮動小数点数 四捨五入

SQLseverにてreal型の小数に対して、CONVERTでNUMERICに変換する際に小数第4位までにしようとした。

SELECT CONVERT(Numeric(10,4), CONVERT(real, 0.00005))

当然のことながら結果は0.0001になるべきだが、SQLserverで出力された結果は0.0000となってしまった。 微小な誤差ではあるが問題になることもあるため調査することにした。

原因は？

まずは本当に小数第5位を四捨五入しているのかを確認するために、以下の2パターンの値で結果を確認してみた。

SELECT CONVERT(Numeric(10,4), CONVERT(real, 0.00006))
SELECT CONVERT(Numeric(10,4), CONVERT(real, 0.000051))

結果は0.0001と初期の想定通りの動きをしていた。 0.00006だけであれば五捨六入の可能性もあり得るが、0.000051でも0.0001になっていることから四捨五入であることは明白だ。

ここまで調べて思ったのは、real型は浮動小数点型なので丸め誤差があるということだ。 そこで表示桁数を増大させて0.00005を見てみようと思う。

SELECT CONVERT(Numeric(38,38), CONVERT(real, 0.00005))

結果は0.00004999999873689000000000000000000000となった。

やはり誤差が発生しており、しかもそれにより小数第5位が5から4になってしまっている。 これが予期せぬ四捨五入の結果になっているわけだ。

対応策は？

前述の結果の通り、浮動小数点型の場合は微小な丸め誤差が存在する。 しかしながら、あくまでも誤差は微小でしかないため、本来四捨五入したい桁よりも一つ小さい桁で四捨五入した後に、再度四捨五入をすればよい。

SELECT CONVERT(Numeric(10,4), CONVERT(Numeric(10,5), CONVERT(real, 0.00005)))

感想

結構無理やりなやり方で面倒くさく、そして気持ち悪い。やはり精度が重要になる数値を扱う場合には、最初から浮動小数点型の使用は控えるべきだろう。 でもレガシーシステムで精度が考慮されていないものをよく見るのでこんなやり方をすることもまだありそうな気はしてる。