BigQueryではFROMに複数のテーブルを指定することができる
#legacySQL SELECT * FROM [project:logs.table_20181114];
#legacySQL
SELECT
*
FROM
TABLE_DATE_RANGE( [logs.table_] ,
TIMESTAMP('2018-11-13'),
TIMESTAMP('2018-11-14'))
legacySQLのみでstandardSQLで実施はできないので注意。
ストーリー仕立てで分かりやすくまとまっているのでおすすめ。
 |
embulkでmysqlへ接続しデータを抽出しようとした際、以下のようなエラーが発生し取得できなかった
The server time zone value 'Asia/Tokyo' is unrecognized or represents more than one time zone.
デフォルトのタイムゾーンの設定に誤りがある。1つ以上設定されてある？
embulkのconfigのinのところに以下のオプションを追加
options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Tokyo}
以降問題発生せず、対応完了
BigQueryの「大容量のデータを高速でスキャンできる」「従量課金の料金形態」という性質から、テーブル設計をはじめるとRDBMSといろいろ違いがでてくる。 今回は配列をカラムに格納する上で便利なunnest（アンネスト）関数を紹介。
サンプルコードを動かす前に、with関数を使ってメモリー上に試験的に使うデータを保持するようにする。
#standardSQL WITH data AS ( SELECT "primes under 15" AS description, [1,2,3,5,7,11,13] AS primes_array)
この時点で
#standardSQL WITH data AS ( SELECT "primes under 15" AS description, [1,2,3,5,7,11,13] AS primes_array) SELECT count(*) FROM data
を実施すると 1 が返ってくる。つまり1レコード登録されている状態。
#standardSQL WITH data AS ( SELECT "primes under 15" AS description, [1,2,3,5,7,11,13] AS primes_array) SELECT description, prime FROM data, UNNEST (primes_array) as prime
from テーブル名, unnest(内部カラム) ;
という形式でデータを取り出す。 
この時点で
#standardSQL WITH data AS ( SELECT "primes under 15" AS description, [1,2,3,5,7,11,13] AS primes_array) SELECT count(*) FROM data, UNNEST (primes_array) as prime
を実施すると 7 レコード返ってくる。
Firebase blogのドキュメントに具体的な利用方法が書かれてある。 https://developers-jp.googleblog.com/2017/04/bigquery-tip-unnest-function.html
ストーリー仕立てで分かりやすくまとまっているのでおすすめ。
|
脆弱性スキャンツールから攻撃されることが度々ある。Nginxでその攻撃を防ぐ。
access logをみると、useragentに”OpenVAS”というのが記されていた。nginxを使っている場合、このuseragentを403 errorにさせることにした
serverディレクティブに設定を追加する。
もしhttp_user_agentにopenVASが含まれていたら 403 にさせる。という設定
server {
if ( $http_user_agent ~* (OpenVAS) ) {
return 403;
}
この設定を入れた後nginxを再起動させるのみ
もちろんuseragentを偽装されてしまえばこの対応では防げないが、デフォルトのuseragentから来ることが多いので、大半のアタックは防ぐことができる。
redashやwordpressなどシステムが送信するメール場合があるが、外へのメール送信が制限されている環境だとメールを確認することができない。
redashであればuser作成したあとのpassword設定であったり、
wordpressであればメールアドレスの変更など。
システムの投げるメールの本文を確認したい事は結構多い。
メールキューの中身を確認する
システムが何らかの理由でメールが送信できない場合、メールキューというところに蓄積される。(その後指定しか期間を過ぎたらexpireされ、破棄される)
キューの確認
# mailq
-Queue ID- --Size-- ----Arrival Time---- -Sender/Recipient-------
BBC672189242 1762 Mon Oct 22 11:51:30 httpd@kusanagi-aaa.localdomain
(connect to mail.xxx.co.jp[xxx.xxx.xxx.xxx]:25: No route to host)
system@sumito.jp
-- 10 Kbytes in 1 Requests.
Queue IDを控え、postcat -qコマンドで中身を確認する
# postcat -q BBC672189242
*** ENVELOPE RECORDS deferred/B/BC1DD21B7AD9 ***
message_size: 1207 183 1 0 1207
message_arrival_time: Thu Oct 25 14:28:08 2018
create_time: Thu Oct 25 14:28:08 2018
named_attribute: rewrite_context=local
sender_fullname:
sender: httpd@kusanagi-uxd.localdomain
*** MESSAGE CONTENTS deferred/B/BC1DD21B7AD9 ***
Received: by kusanagi-uxd.localdomain (Postfix, from userid 1000)
id BC1DD21B7AD9; Thu, 25 Oct 2018 14:28:08 +0900 (JST)
To: system@sumito.jp
Subject: =?UTF-8?B?W1ImRCBSbDmlrDopo/jg6H7zjg6vjgqLjg4njg6zjgrk=?=
X-PHP-Originating-Script: 1001:class-phpmailer.php
Date: Thu, 25 Oct 2018 05:28:08 +0000
From: WordPress <wordpress@sumito.com>
Message-ID: <1a0a2f7069595074466e7d9ef2744d49@r-and-d.rarejob.com>
X-Mailer: PHPMailer 5.2.22 (https://github.com/PHPMailer/PHPMailer)
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
こんにちは wpadmin さん
お持ちのアカウントのメールアドレスの変更がリクエストされました
もしこれに間違いがなければ、以下のリンクをクリックして変更を行ってください。
https://sample.sumito.jp/wp-admin/profile.php?newuseremail=1edbc57873c6f3cbae541a8f3ebe
変更をしない場合は、このメールを無視し、削除してかまいません。
この場合はメールアドレス変更のメール。
キューの中に記載のあるURLをブラウザに入力すれば、wordpressのメールアドレスを変更することが可能になる。
[本記事はredash meetup 4.0.0で発表させていただいた内容に肉付けしたものです]
redashはデータベースに接続できるツールなのでuserの管理がとても重要になる。
userを定期的に棚卸しをし、メンテナンスしていくことはセキュリティを維持する上で必須だ。弊社では
というフローを採用し運用している。
しかし、想定していた運用を採用するうえではソースを変更する必要があった。
残念ながらこの画像からわかる通り、非常に貧弱である。
version 5からuserをdisableする機能が導入されたが、userがenableなのか、disableなのかという情報がCLIからだとわからない。
機能としては存在しているのに、CLIで見れないとなると単純に表示させていないだけなのかもと察しソースを読んだ。
Userのenableなのか、disableの情報を表示するメソッドを見つけた
おそらくGUIであればこの処理を叩いて画面に表示している。
機能があるのであればCUIも同じように叩けばよいのでは？という仮説のもとファイルを変更した
https://github.com/getredash/redash/pull/2951/files
これでenableなのか、disableなのかがコマンドラインで確認できるようになった。
自分の管理するredashはdockerで動いているので、linux上でviコマンドで編集し、docker cpコマンドでコンテナ内に転送することで変更を動いているredashに反映させた。
OSSのソースに手を入れるというと相当な知識がないと変更できないかと思っていたが、progateでpython覚えた程度でも変更できた。
最初テストケースを書かずにpushしたらCIでコケたので、恥ずかしながらそこでredashのテストコードの存在を知った。。
せっかくなのでpull reqを出すと翌日mergeしてもらい、これがコントリビュートにつながった。
その後CLI機能を拡張すべく2つpull requestを出させていただき、いずれも承認された。
gitlab-cliの設定する必要があるので事前にセットアップする
https://tsukada.sumito.jp/2018/10/17/python-gitlab/
shellをcronで動かし、その中でpythonのスクリプトを動かす
#!/bin/bash
hostname=`uname -n`
_roomid=123456789
output=/tmp/$hostname
cd ~/redash-user/
git pull
nohup /usr/bin/docker exec -i redash_server_1 ./manage.py users list > ~/redash-user/$hostname &
sleep 10;
ps -ef | grep "./manage.py users list" | grep -v grep | awk '{print $2}' | xargs kill -9
git checkout -b $hostname
git add $hostname
git commit -m $hostname
git push origin $hostname
python merge.py
pythonはGitLabへpushするのに使っている。コードはこちら
import os
import gitlab
hostname = '%s' % os.uname()[1]
gl = gitlab.Gitlab('https://gitlab.sumito.com/', private_token='abcdefgggggggg')
gl.auth()
project = gl.projects.get(123, lazy=True)
mr = project.mergerequests.create({'source_branch': (hostname),
'target_branch': 'master',
'title': (hostname) + ' user list'
})
mrs = project.mergerequests.list(state='opened', order_by='updated_at')[0]
print(mrs.attributes['web_url'])
以下のようにcronを仕込み月に一度listを更新し、GitLabにpushするようにしている。
0 0 1 * * sh /root/redash-user/redashUsers.sh 1> /tmp/stdout.log 2> /tmp/stderr.log
これも同様にshellからpythonを動かすようにしている。
#!/bin/bash
cd /root/redash-user/
hostname=`uname -n`
_roomid=123456789
tmp=$(/usr/bin/python ./chat.py)
tmowl=`echo $tmp | grep git.sumito.com | wc -l`
output=/tmp/gomi
if [ $tmowl -eq 0 ] ; then
exit
else
/usr/bin/cat << _EOT_ > /tmp/gomi
[toall]
[info]
[title]latest redash account Please check it!!
[/title]
`/usr/bin/python ./chat.py`
[/info]
_EOT_
_body=`/usr/bin/cat $output`
/usr/bin/curl -X POST -H "X-ChatWorkToken: 123456789" -d "body=${_body}" "https://api.chatwork.com/v2/rooms/${_roomid}/messages"
fi
pythonはこちら
import os
import gitlab
hostname = '%s' % os.uname()[1]
gl = gitlab.Gitlab('https://git.sumito.com/', private_token='123456789')
gl.auth()
# your project ID
project = gl.projects.get(123, lazy=True)
mrs = project.mergerequests.list(state='opened', order_by='updated_at')
mr_url = [mr.attributes['web_url'] for mr in mrs]
print("{}".format("\n ".join(mr_url)))
以下のように表示される
この状態ではmerge requestが発行された状態なので、
管理者はこのリクエストを確認し、最新の状態を確認。問題なければmergeする
当然gitなので最新merge日は更新され、この日が棚卸し完了日と位置づけすることにより、最終棚卸し日がいつだったのかわかるようにしている。
気がついたらswapを喰っていることがある
# top top - 10:39:58 up 26 days, 16:25, 1 user, load average: 0.80, 0.31, 0.20 Tasks: 132 total, 2 running, 130 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.0 us, 0.7 sy, 0.0 ni, 99.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 1883152 total, 83772 free, 1363060 used, 436320 buff/cache KiB Swap: 1048572 total, 957948 free, 90624 used. 315320 avail Mem
この場合は90624 used.
memoryに抱えきれなかった領域がストレージにdumpされてしまったようだ。ストレージがSSDだったとしても遅いのだが、これがHDDだったりすると阿鼻叫喚だ。
一時的にswapをオフにして、再度オンにする。
swapoff -a && swapon -a
ただし、swap outしている領域よりもmemoryが十分に空きがある必要がある。
他にもアプローチはあることはあるが、
これで解放できなかったらOS再起動を考えた方が手っ取り早い。
MySQL Workbenchに備わっている sshトンネリング機能を使い、手元のPC/MacからリモートのサーバのDBに繋ぐ方法を紹介。
手元の環境から、MySQL Workbenchがserverにつなぎに行き、そのサーバ内のMySQLに接続させたい。
Connection Methodを “` Stadard TCP/IP over SSH “` を選択。諸々入力する。 
つながらない
mysql内に作成したユーザの接続元を
127.0.0.1ではなく、localhostとして登録していた。 意味的には同じように思えるが、mysqlの場合はこの辺厳密に区別されるらしい。
接続元を127.0.0.1にしてユーザを再作成
GRANT USAGE ON *.* TO 'tsukada'@'127.0.0.1' IDENTIFIED BY 'password'; GRANT select ON `database`.* TO 'tsukada'@'127.0.0.1' ; MariaDB [(none)]> select user,host from mysql.user; +----------------+-----------+ | user | host | +----------------+-----------+ | tsukada | 127.0.0.1 | +----------------+-----------+ 14 rows in set (0.00 sec) MariaDB [(none)]>
問題なく接続できるようになった
リモート接続の際、この本の第三章が非常に良くできている。
 |
第3章 一歩進んだOpenSSHの使い方
3-1 TCPポートフォワード
3-2 プロキシと多段SSH
3-3 SSHのセッションをコントロールする
第三章だけでも読む価値はある。とてもおすすめ。
稀にサーバの現在時間を確認しようとした際、想定していた時間と大きくズレていて、見てみるとJST（日本時間）ではなく、UTC（協定世界時）になってある事がある。
# date Wed Oct 17 02:03:17 UTC 2018
一度設定したらほとんど変更しないタイムゾーン。
デフォルトでUTCになっていることもあり、JSTに変更したい事が多々ある。
しかし、変更したい時はだいたい手順を忘れているのでメモを記す。
現在のタイムゾーンを確認
# timedatectl
Local time: Wed 2018-10-17 02:05:01 UTC
Universal time: Wed 2018-10-17 02:05:01 UTC
RTC time: Wed 2018-10-17 02:04:41
Time zone: n/a (UTC, +0000)
NTP enabled: yes
NTP synchronized: no
RTC in local TZ: no
DST active: n/a
日本時間に変更する
# timedatectl set-timezone Asia/Tokyo
再度タイムゾーンを確認
# timedatectl
Local time: Wed 2018-10-17 11:05:17 JST
Universal time: Wed 2018-10-17 02:05:17 UTC
RTC time: Wed 2018-10-17 02:04:57
Time zone: Asia/Tokyo (JST, +0900)
NTP enabled: yes
NTP synchronized: no
RTC in local TZ: no
DST active: n/a
Local timeのところがUTCからJSTに変わった。
この状態でtimeコマンドで現在時間を確認すると,
# date Wed Oct 17 11:05:31 JST 2018
JSTになり期待通りの結果になる
もちろん、ログインしなおしてもこの状態は引き継がれる。
本作業を行うとcronが正常に稼働しない。cronを利用している場合は再起動する必要がある
systemctl restart crond
GitLabでコマンドベースでmerge requestを作成する。
postでAPIを叩いていろいろ設定すればそれでも可能だけど、python-gitlabというpython製のツールがとても便利だったので紹介。
pipでpython-gitlabをインストール
pip install --upgrade python-gitlab
vi ~/.python-gitlab.cfg
[global] default = somewhere ssl_verify = true timeout = 5 [somewhere] url = https://gitlab.com private_token = xXxxxxxxxxxxxxxxxxx api_version = 4
ここで必要になるprivate_tokenはGitLabのユーザ画面から作成することが可能
mergeするスクリプトを作成
import os
import gitlab
hostname = '%s' % os.uname()[1]
# gitlabへのURLを入れる
gl = gitlab.Gitlab('https://gitlab.com/', private_token='xxxxxxxxxxx')
gl.auth()
# プロジェクト番号をgitlabを調べる
project = gl.projects.get(123, lazy=True)
mr = project.mergerequests.create({'source_branch': (hostname),
'target_branch': 'master',
'title': (hostname) + ' user list'
})
mrs = project.mergerequests.list(state='opened', order_by='updated_at')[0]
print(mrs.attributes['web_url'])
実行
$python merge.py https://gitlab.com/infra/redash-user/merge_requests/30
作られたmerge requestのページへのリンクが表示される。
チャットやメールでこのリンクをreviewerに知らせれば、フローも簡略化できる。
詳しい使い方はこちら
https://python-gitlab.readthedocs.io/en/stable/index.html