How to make datas our friends

「エンジニアは発信していくことが責務である」という言葉に感化されて始めた勉強したことを書き留めていく備忘録的なやつ。

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

あらすじ

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

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

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

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

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

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

f:id:minion024:20180127200637p:plain

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

次は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 になりました。

f:id:minion024:20171229021402p:plain

以上。

JupyterNotebookでboto3を使えるようにした話

概要

ローカルのJupyterNotebookからS3の操作をしたかったのでAWS SDKのboto3を入れたという話。

boto3に関してはこちらを参照: AWS SDK for Python | AWS

ちなみにJupyterNotebookでと書いているが、普通にPythonのboto3をローカルに落として使えるようにしただけです笑

やったこと

boto3のinstall

pip install boto3

で、インストール可能ですが、僕はanacondaでインストールしました。

conda install -c anaconda boto3=1.4.5

boto3のバージョン情報を見る場合はこれで見れます。

conda info boto3

クレデンシャル情報の登録

インストールが完了したら、aws cliを使ってクレデンシャル情報を登録。

aws configure

と叩くと情報を入力できます。

AWS Access Key ID : ACCESS_KEY_ID
AWS Secret Access Key : SECRET_ACCESS_KEY
Default region name : ap-northeast-1
Default output format : json

今回、リージョンは普段使っている ap-northeast-1 にしました。

多分大丈夫だと思いますが ACCESS_KEY_ID / SECRET_ACCESS_KEY に何を入れればいいのかわかんない人はこちらを参照してみてください: AWS アカウントのアクセスキー管理 - アマゾン ウェブ サービス

これで無事boto3がJupyterNotebookで使えるようになりました。

めでたし、めでたし。

LINE API x Google App Script で bot を作成した話

f:id:minion024:20170717181521p:plain

あらすじ

友達とLINEしてるときに、グループにBotを追加したら面白そう!便利そう!夢がある!と思いたちBotの作成にチャレンジしました。

LINEが Messaging API というものを提供しているので、今回はこれを使用します。
business.line.me

サーバーを用意するのが面倒だったので、GASで出来ないかちょっと調べたらGASで実装している先人が結構いたので、僕もGASを使用して実装することにしました。

作成手順

まずは、LINE BUSINESS CENTERからアカウントの作成を行います。
アカウントの作成は手順通りにやれば5分くらいで終わりました。
ちなみに今回は Developer Trial でやります。

ちなみに料金はこんな感じ。(2017/07/14時点)
f:id:minion024:20170716023401p:plain

アカウントの作成が完了したらアカウントリストからBot用のビジネスアカウントを作成します。

ビジネスアカウントの作成が完了したらLINE@ MANAGERから作成したビジネスアカウントの設定が変更できるようになります。

APIを有効にするため、FVに以下のような画面が表示されているので Enable API のボタンを押下。
f:id:minion024:20170716024407p:plain

APIを有効にするといくつか使用できない機能があるっぽいです。
f:id:minion024:20170716024719p:plain

問題ないのでそのまま進めると Setting 画面が表示されます。
全て使用したいので全て許可しました。笑
f:id:minion024:20170716025028p:plain:w300

BOTの名前が思いつかなったのでとえりあえず「さちこ」にしました。
よろしくね、さちこちゃん。
f:id:minion024:20170716030903p:plain:w300

つづいてGASを使用してサーバーをたてます。
GoogleDriveにアクセスしてGoogle App Scriptを新規作成します。
「その他」にGASがなければ「+アプリを追加」で選択可能になります。
f:id:minion024:20170716032157p:plain

Script内に下記を記載して適当なタイトルを付けて保存。

function doGet() {
  return ContentService.createTextOutput('さちこです')
}

メニューの「公開」から→「ウェブアプリケーションとして導入」を選択。
権限周りの設定を行い、更新すると公開用のURLが作成されるので、コピーしてどこかに保存しておきましょう。

f:id:minion024:20170717173759p:plain:w300

このURLにアクセスすると「さちこです」と健気に表示されるはず。
これでサーバーの用意は完了したので、先程のURLをLINE側で設定します。

LINE Developersにアクセスして、Basic information から Webhook URL の設定を変更できるので先程のURLを設定。

これで、Botが返信できるようになったはずです。

とりあえず、メッセージを送信するとオウム返しで "「◯◯」ですか?" と返信するコードを書いてみます。

var CHANNEL_ACCESS_TOKEN = 'LINE DevelopersからコピーしてきたChannel Access Token';

function doPost(e) {
  var reply_token= JSON.parse(e.postData.contents).events[0].replyToken;
  if (typeof reply_token === 'undefined') {
    return;
  }
  var user_message = JSON.parse(e.postData.contents).events[0].message.text;
  var url = 'https://api.line.me/v2/bot/message/reply';
  
  UrlFetchApp.fetch(url, {
    'headers': {
      'Content-Type': 'application/json; charset=UTF-8',
      'Authorization': 'Bearer ' + CHANNEL_ACCESS_TOKEN,
    },
    'method': 'post',
    'payload': JSON.stringify({
      'replyToken': reply_token,
      'messages': [{
        'type': 'text',
        'text': '「' + user_message + '」ですか?',
      }],
    }),
  });
  return ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON);*/
}

コードを書いたら、再度メニュー「公開」からデータの更新をかけます。
このとき、「プロジェクトバージョン」を「新規作成」にしないと、編集が反映されないので注意です。

ここまで、やればBotがオウム返しで返信してくれるようになっているはず。
友達追加で作成したBotを追加して、メッセージを送信します。

オウム返しでかえってくるはず。

f:id:minion024:20170717174231j:plain:w300

もし、返ってこないときはGASの公開範囲周りをいじってみてください。全員アクセスできる権限になっていないとレスポンスが返ってこないです。

とりあえずBotの土台が出来上がったので、後は好きなように改造するだけです。
初めてBotを作りましたが、意外と簡単にできました〜。

暇なときに改造して、賢いBotにしていこうと思います。笑

f:id:minion024:20170717175412j:plain:w300

ShellScriptでMySQLの実行結果を変数にぶち込む

概要

ShellScriptをかきかきしていたときに、MySQLの実行結果(今回は数字)をShellの変数に代入して使用したいタイミングがあり、どうやって実現したのか備忘録。

▼こうゆうことをやりたい。

# MySQLを実行して
mysql -uroot -e "SELECT SUM(hoge) FROM fuga.table;"
# この変数に実行結果を入れたい!!
RESULT = MySQL実行結果

解決方法

# MySQLを実行
MYSQLRES=`mysql -uroot -e "SELECT CONCAT('RESULT=', SUM(hoge)) AS 'id0=0' FROM fuga.table;"`
# evalで変数に代入
eval `echo $MYSQLRES`

1)まずはMySQLの実行結果を変数 MYSQLRES に代入

MYSQLRES=`mysql -uroot -e "SELECT CONCAT('RESULT=', SUM(hoge)) AS 'id0=0' FROM fuga.table;"`

このときCONCATで"RESULT=代入したい数字"にするのが味噌。
このクエリの実行結果はこんな感じになっているはず。(数字は適当)

id0=0
RESULT=10

2)クエリの実行結果をevalで実行する

eval `echo $MYSQLRES`

先程のクエリの実行結果を代入した変数 MYSQLRESecho して eval でまとめて実行すると変数 id0RESULT にそれぞれ数字が代入されます。

id0 はぶっちゃけなんでもいいです、エイリアスで実行不能な文字列をいれると eval したときにエラーになるので、実行できればなんでもいいです笑

これでスクリプト内で変数として自由に利用できます。

> echo $RESULT
> 10

こんな方法もある

RESULT=`mysql -uroot -e "SELECT SUM(hoge) FROM fuga.table;" -B | tail -n 1`

普通にこっちの方がスマート。
ちなみにこれは会社の仲間が考えたやつ。

負けた感がすごい。

かっこいいシートを作ろう!Googleスプレッドシートの便利技 - 初級編 -

普段、仕事で死ぬほどスプレッドシートを使用している024minonです。
今回は、初心者でもすぐに実践できそうなかっけぇぇぇシートを作成するための機能・関数をいくつかご紹介させていただこうと思います。

その1:プルダウンリストを埋め込んでシートかっけぇぇ感じにする

プルダウンリストってこれのことです↓
f:id:minion024:20170520195253p:plain:w300

さっそくプルダウンを作ってみる

まず下画像のように、事前にシート内にリスト内に格納する要素を用意します(画像内だとD列がそれにあたる)。
f:id:minion024:20170520195641p:plain

プルダウンリストを作成したいセル(画像内A2)で右クリック→データの入力規則を押す。モーダルが表示されるので、「条件」の項目を「リストを範囲で指定」に設定し赤枠の部分をクリック。
f:id:minion024:20170520200234p:plain:w450

「データ」と書かれたモーダルが表示されるのでリスト内に格納するセルを指定します(今回はD2:D7と指定しました)。
あとは「OK」ボタンを押して「保存」を選択すればプルダウンリストの完成です。

ちなみにモーダル内の「条件」を「リストを直接指定」に設定し、セルを参照しない形(直接要素を記載)でプルダウンリストを作成することも可能です。
その場合、項目を「,」区切りで記入すればOKです。
f:id:minion024:20170520200841p:plain

その2:タブに色をつけて個性を主張する

シート内のタブに色をつけてcolorfulな感じにしよう(●´ϖ`●)
f:id:minion024:20170520201653p:plain

さっそく色をつけてみる

色を変更したいタブの上で右クリック→「色を変更」→お好みの色を選択
f:id:minion024:20170520201942p:plain

ちなみに僕はメインで見るタブがすぐわかるように色をつけたりします。

その3:見られたくないものに蓋をする

例えばこのようなシートがあるとして(下画像)、計算に使用しているセルをかっこ悪いから表示させたくない!あるいは、見る人を混乱させてしまう可能性があるのでシート上に表示する情報を絞りたい!ってときありますよね?笑
f:id:minion024:20170520203248p:plain

そんなときは隠蔽しよう

非表示にしたい列を選択→選択した列の一番上(アルファベットが書いてある部分)で右クリック→「列○ - ☓を非表示」→消える!
再表示したい場合は上に「◀|▶」こんな感じのやつがあると思うのでクリックすればもとに戻ります。
f:id:minion024:20170520203727p:plain:w400

シート自体も隠蔽しちゃおう

編集中なので見られたくない!ってときはシートごと非表示にしてしまいましょう。
非表示にしたいタブの上で右クリック→「シートを非表示」を選択→消える!
f:id:minion024:20170520204248p:plain

もとに戻したいときは、メニューの「表示」→「非表示のシート」→表示したいシートを選択→表示される!
f:id:minion024:20170520204551p:plain

必要以上に非表示にすると可読性が落ちるのでほどほどに(´・ω・`)

その4:他のシートからデータを参照してスマートに同期させる

同じ(URLの)シート内からセルを参照できるのはご存知だと思いますが、スプレッドシートはとてもスマートなのでIMPORTRANGE関数を使用して(別URLの)他シートのセルを参照することもできます。

今回は、先程作成したプルダウンリストのセルを別シートで読み込んでみます。
f:id:minion024:20170520205829p:plain:w250

常に最新データのコピペ作業とはもうおさらばだ

早速データを挿入したいセルに関数を入力してみましょう。
IMPORTRANGE関数はこんな感じで使用します↓

=IMPORTRANGE("対象シートの固有識別ID","タブ名!範囲")

固有識別IDはURL内に記載されています(下URLの”ここの部分”ってところ)。

https://docs.google.com/spreadsheets/d/"ここの部分"/edit#gid=数字がごちゃごちゃ

今回はプルダウンリストのA1:A2の部分を持ってきたいのでこんな感じ↓
(ちなみに対象のタブ名は「O」です)

=IMPORTRANGE("対象シートの固有識別ID","O!A1:A2")

エラーが表示されるので、セルをクリックして「アクセスを許可」ボタンを押しシートの参照を許可してください。
それでもエラーが出る場合、参照先のシートが他からアクセスできないように設定されている可能性があるので公開範囲を調整してみてください。

無事表示されました(´・ω・`)
f:id:minion024:20170520211100p:plain

ちなみに参照元のプルダウンを鈴木に変更すると、今回作成したシート側も鈴木に変わります。素晴らしい。
※ただし参照元のデータが変更されると、シートのデータも変わってしまうので注意も必要。コピペとは別のものと思ったほうがよい。

その5:条件に一致するセルに色をつけて見て見てアピール

注目させたいセルに色をつけることってあると思うのですが、条件に一致するセルに自動で色付けすることも可能です。
今回は下記画像のように"日付"が今日なのに"ステータス"が未着手の項目に色をつけてみたいと思います。
f:id:minion024:20170520213151p:plain

さっそくおせっかいオートメーションを作成しよう

対象のセルで右クリック→「条件付き書式
f:id:minion024:20170520213354p:plain:w200

セルの書式設定の条件」を「カスタム関数」に変更するとカスタム関数を記入できるようになります。
f:id:minion024:20170520213730p:plain:w200

さっそく中に条件を記載していきしょう。

今回は列Bの「締め切り」が"今日"で列Cの「ステータス」が"未着手"という条件なので下記のように記載します。

=AND(B2:B6=TODAY(), C2:C6="未着手")

まず、対象の列が今日かどうか確認をしています。
> セルの範囲=TODAY()

その次に対象の列が指定した文字と完全一致するか確認しています。
> セルの範囲="文字"

そして条件を複数指定しているのでANDで接続します
> AND(条件1, 条件2)

条件を入力したら保存してください。
これで条件に一致するセルに色が自動でついたはずです。

これを利用すれば特定の曜日に色を付けたり、売上が○を下回った場合に赤い色を付けてアラートを出す等も可能です。

おわりに

今回は普段業務で使っているスプレッドシート芸を他の人にも発信していこうと思いこの記事を書きました。
まだまだ紹介していない便利技がいっぱいあるので中級者編&上級者編も今後書いていこうと思っています。