BigQuery含むGCPに関する情報を提供する
gcloudコマンドしようとすると、デフォルトではbashへinstallすることが前提となってしまっている。
zshでgcloudコマンドを設定する。
gcloudコマンドがinstallされていること。
まだの場合は公式手順を参考にinstallする。
https://cloud.google.com/sdk/downloads?hl=ja
デフォルトでは “` ~/google-cloud-sdk/ “` にインストールされる
もし存在しない場合はfindなどで調べる
google-cloud-sdk配下に
“` path.zsh.inc “` というファイルがある。これを.zshrcへ追加する
source /Users/sumito.tsukada/google-cloud-sdk/path.zsh.inc
このような感じ。
その後は再読み込みを実施する
“` source ~/.zshrc “`
以上。
BigQueryのtimestampの戻り値はデフォルトでUTCである。
クエリを修正することでJSTを表示できる。
#standardSQL
select
created_at as UTC,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', created_at, 'Asia/Tokyo') AS JST
from `BQ.sample`
ビッギデータ解析について分かりやすくまとまっているのでおすすめ。
Google Cloud StorageへAPI経由でファイルをuploadする。
Introduction to APIs in Google を受講して、基本的な使い方を覚えたのでメモを残す
OAuthのアクセストークンを生成する
https://developers.google.com/oauthplayground/
頻繁に使うので認証を環境変数に入れる
export OAUTH2_TOKEN=<YOUR_TOKEN>
プロジェクトIDも入れる
export PROJECT_ID=<YOUR_PROJECT_ID>
バケット生成の定義のjsonファイルを作成、読み込む
{ "name": "<YOUR_BUCKET_NAME>",
"location": "us",
"storageClass": "multi_regional"
}
curl -X POST --data-binary @values.json \
-H "Authorization: Bearer $OAUTH2_TOKEN" \
-H "Content-Type: application/json" \
"https://www.googleapis.com/storage/v1/b?project=$PROJECT_ID"
wget https://gcpstaging-qwiklab-website-prod.s3.amazonaws.com/bundles/assets/138f92c75d08d0705e3853c1d790453f37fdfff38afad7fb4b431b9fa690f1fc.png mv 138f92c75d08d0705e3853c1d790453f37fdfff38afad7fb4b431b9fa690f1fc.png demo-image.png
フルパスを確認する
realpath demo-image.png
結果を環境変数に入れる
export OBJECT=<DEMO_IMAGE_PATH>
バケット名を環境変数に入れる
（先ほど作成したやつ）
export BUCKET_NAME=<YOUR_BUCKET>
curl -X POST --data-binary @$OBJECT \
-H "Authorization: Bearer $OAUTH2_TOKEN" \
-H "Content-Type: image/png" \
"https://www.googleapis.com/upload/storage/v1/b/$BUCKET_NAME/o?uploadType=media&name=demo-image"
JSONの構文チェックに便利なサイト
Cloud StorageのAPIリファレンス（英語）
https://cloud.google.com/storage/docs/json_api/v1/
Kubernetesはバズワードになりつつあるが、スケールアップ・ローリングアップデートを含む運用のノウハウはまだまだそれほど多くないのでまとめる。
var http = require('http');
var handleRequest = function(request, response) {
response.writeHead(200);
response.end("Hello World!");
}
var www = http.createServer(handleRequest);
www.listen(8080);
起動
node server.js
FROM node:6.9.2 EXPOSE 8080 COPY server.js . CMD node server.js
docker hubからnodeのversion 6.9.2をダウンロード。
8080ポートを外部に公開、
先ほど作ったserver.jsをdocker内にコピー、
nodeコマンドでserver.jsを起動する。と言った内容。
docker build -t gcr.io/PROJECT_ID/hello-node:v1 .
動作確認
docker run -d -p 8080:8080 gcr.io/PROJECT_ID/hello-node:v1
curlで自身に接続し接続確認をする
curl http://localhost:8080
非公開リポジトリ Google Container Registry にpushする
gcloud docker -- push gcr.io/PROJECT_ID/hello-node:v1
クラスタはGCPで用意する。
gcloud config set project PROJECT_ID
2 つの n1-standard-1 ノードのあるクラスタ
gcloud container clusters create hello-world \
--num-nodes 2 \
--machine-type n1-standard-1 \
--zone us-central1-a
こちらは先ほど作ったクラスタの中につくられる。
Kubernetesのコマンドkubectlを使って作っていく。
kubectl run hello-node \
--image=gcr.io/PROJECT_ID/hello-node:v1 \
--port=8080
デプロイメントオブジェクトが作成される。
確認のコマンドはこちら
kubectl get deployments
デプロイメントによって作成されたポッドは以下のコマンドで確認
kubectl get pods
Kubernetesの状態を確認するコマンド
kubectl cluster-info kubectl config view kubectl get events kubectl logs <pod-name>
デフォルトではポッドにはクラスタ内部IPからしかアクセスできないようになっている。kubectl exposeコマンドと –type=”LoadBalancer”で公開する事ができる。
kubectl expose deployment hello-node --type="LoadBalancer"
（ポッドを直接公開する手順ではなく、デプロイメントを公開の手順）
公開IPを確認
kubectl get services
今までは2つのレプリカを用意していたが、それをさらに増やしたい場合以下のコマンドでスケールアップする事が可能
kubectl scale deployment hello-node --replicas=4
確認
kubectl get deployment
podの状態を確認
kubectl get pods
再度nodeで作ったserver.jsを適当に編集し、保存する。
その後buildしてpush
docker build -t gcr.io/PROJECT_ID/hello-node:v2 . gcloud docker -- push gcr.io/PROJECT_ID/hello-node:v2
その後、kubectlコマンドでデプロイするコンテナイメージのタグを変更する
kubectl edit deployment hello-node
変更するフィールドは
spec.template.spec.containers.image
containers:
- image: gcr.io/PROJECT_ID/hello-node:v1
をv1からv2へ変更する
kubectl get deployments
を行うと順次ポッドがローリングアップデートされる。
古いポッドは削除されてしまうので注意。
gcloud container clusters get-credentials hello-world \
--zone us-central1-a --project <PROJECT_ID>
トークンを取得する
kubectl -n kube-system describe $(kubectl -n kube-system \ get secret -n kube-system -o name | grep namespace) | grep token:
8081ポートを使うことにする（どこでもよい）
kubectl proxy --port 8081
コンソール画面でchange portをクリックし、8081ポートに変更する。
?authuser=0を削除し、以下のとおり/uiに変更する
例:
https://8081-dot-6387619-dot-devshell.appspot.com/ui
https://google.qwiklabs.com/focuses/564?locale=ja&parent=catalog
GAE(Google App Engine)を使い始めたので、一度ここで整理をした。
これがGAEの根幹
runtime: python27 api_version: 1 threadsafe: true handlers: - url: /.* script: main.app
使用する言語や、url /で受ける際に呼び出されるscript （この場合はmail.app）が書かれている。
https://cloud.google.com/appengine/docs/standard/python/quickstart?hl=ja#mainpy
を少し終了し、編集した。
import webapp2
class MainPage(webapp2.RequestHandler):
def get(self):
self.response.headers['Content-Type'] = 'text/plain'
self.response.write('Wecome to Main Page')
class sub(webapp2.RequestHandler):
def get(self):
self.response.headers['Content-Type'] = 'text/plain'
self.response.write('This page is sub')
app = webapp2.WSGIApplication([
('/', MainPage),
('/sub', sub),
], debug=True)
/ でリクエストされるとMainPageクラスが呼び出され、
/subでリクエストがくるとsunクラスが呼び出されるという簡単なページ。
Googleのプラットフォームにあげる前に、自身の環境でテストをすることが可能である。
dev_appserver.py app.yaml
ちなみに、dev_appserver.pyはgoogle-cloud-sdkをinstallすればbinの配下に入っている。
$ which dev_appserver.py /Users/sumito.tsukada/google-cloud-sdk/bin/dev_appserver.py
デフォルトでこのdev_appserver.pyを実施した場合、localhost:8080に繋げればアプリケーションの試験が可能だ
http://localhost:8080/
gcloud app deploy
デプロイされたことを確認できた。
自動でブラウザが立ち上がり動作確認をすることが可能。
gcloud app browse
コマンドでGoogle App Engineを停止する。
前提条件としてgcloudコマンドがインストールされてあること。
#!/bin/bash
set -eu
env=$1
method=$2
# allocations ID取得
ID=$(gcloud app services describe $1 | tail -n1 | awk '{print $1}' | sed "s/://g")
echo $ID
if [ $method == 'stop' ]; then
gcloud app versions stop $ID -q
elif [ $method == 'start' ]; then
gcloud app versions start $ID -q
else
echo ‘check your parameter’
fi
gcloud app versions list
引数にサービス名を加えて実行する “` ./script.sh tsukada-test stop “` 
“` ./script.sh tsukada-test start “` 
本来ならCloudFunctionで実装したかったが、大いにはまってしまったので、急遽この方法で対応した。
コマンドを見てもらえばわかる通り、versionの指定はあっても、serviceの指定がない。
そのため、万が一 service は違うけど、動いているversionが同じ。と言う場合、意図しない方も影響を受けてしまう。
実はオプションで “` –service “` が用意されている。
https://cloud.google.com/sdk/gcloud/reference/app/versions/stop?hl=ja
必要な場合はこのオプションを活用すればよい。
awsであればファイル置き場にS3を使うことも多いだろう。
s3への転送したい場合はaws s3 cpコマンドを使うことが多い。 似たようなことをGCPでCloud Storageに対して行いたいことがある。
ローカルからファイルをコピーする場合 gsutilコマンドを利用する
gsutil cp /tmp/hogehoge gs://bucket/dir/
削除する場合は
gsutil rm gs://bucket/dir/hogehoge
オフィシャル情報はこちら https://cloud.google.com/storage/docs/gsutil?hl=ja
ちなみにプログラム(Python)に組み込む際はこちら https://tsukada.sumito.jp/2018/11/20/gcp-python-cloud-strage-upload/
BigQueryにbqコマンドでテーブル作成・データ投入・スキーマ変更する。 ネストされたデータも扱いたいので、今回はjsonファイルを読み込むようにする CSVは現段階でネストされたデータのimportをサポートされていない
CSV ファイルはネストされたデータや繰り返しデータに対応していません。
https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv ちょうどいいサンプルデータ( https://gist.github.com/isdyy/5072792 )を置いてくださる方がいたので、それを拝借。
https://gist.github.com/isdyy/5072792 にあるように2つのデータを用意する。1つはスキーマ。もう1つはデータ
[
{
"name": "kind",
"type": "string"
},
{
"name": "fullName",
"type": "string"
},
{
"name": "age",
"type": "integer"
},
{
"name": "gender",
"type": "string"
},
{
"name": "citiesLived",
"type": "record",
"mode": "repeated",
"fields": [
{
"name": "place",
"type": "string"
},
{
"name": "numberOfYears",
"type": "integer"
}
]
},
{
"name": "note",
"type": "string"
}
]
{"gender": "Male", "fullName": "John Doe", "age": 22, "citiesLived": [{"numberOfYears": 5, "place": "Seattle"}, {"numberOfYears": 6, "place": "Stockholm"}], "kind": "person"}
{"gender": "Female", "fullName": "Jane Austen", "age": 24, "citiesLived": [{"numberOfYears": 2, "place": "Los Angeles"}, {"numberOfYears": 2, "place": "Tokyo"}], "kind": "person"}
{"note": "with newline (\\n)", "kind": "person", "gender": "Male", "age": 30, "fullName": "newline", "citiesLived": [{"place": "Shinjuku-ku,\nTokyo"}]}
{"note": "with tab (\\t)", "age": 30, "fullName": "tab", "citiesLived": [{"place": "Shinjuku-ku,\tTokyo"}], "kind": "person"}
{"note": "japanese / unicode escape sequence", "gender": "Female", "citiesLived": [{"place": "\u6a2a\u6d5c\u5e02"}], "fullName": "\u6a2a\u6d5c \u82b1\u5b50 (1)", "kind": "person"}
{"note": "japanese / raw utf-8", "gender": "Female", "citiesLived": [{"place": "横浜市"}], "fullName": "横浜 花子 (2)", "kind": "person"}
{"note": "double-quoted string", "fullName": "\"quoted\"", "kind": "person"}
# bq mk --table logs.sample nested01.fields.json Table '****:logs.sample' successfully created.
ブラウザ上で以下の通り作成されている 
また、bq showコマンドでスキーマを確認できる
# bq show logs.sample
Table ****:logs.sample
Last modified Schema Total Rows Total Bytes Expiration Time Partitioning Labels
----------------- ----------------------------------- ------------ ------------- ------------ ------------------- --------
19 Nov 21:09:54 |- kind: string 0 0
|- fullName: string
|- age: integer
|- gender: string
+- citiesLived: record (repeated)
| |- place: string
| |- numberOfYears: integer
|- note: string
# bq load --source_format=NEWLINE_DELIMITED_JSON logs.sample nested01.data.json Upload complete. Waiting on bqjob_r4b711f3cb6f6854b_000001672be52e12_1 ... (0s) Current status: DONE
無事投入された。適切にネストもされている。
スキーマ作成と同時に、データの投入も可能
# bq load --source_format=NEWLINE_DELIMITED_JSON logs.sample nested01.data.json nested01.fields.json Upload complete. Waiting on bqjob_r49704f78c779ebd5_000001672bd96baa_1 ... (0s) Current status: DONE
もちろん結果は同じ
“` –autodetect “` オプションを使う。
# bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect logs.sample nested01.data.json Upload complete. Waiting on bqjob_r49704f78c779ebd115_00001672bd96baa_1 ... (0s) Current status: DONE
nested01.fields.jsonを以下の通り変更
[
{
"name": "kind",
"type": "string"
},
{
"name": "fullName",
"type": "string"
},
{
"name": "age",
"type": "integer"
},
{
"name": "gender",
"type": "string"
},
{
"name": "country",
"type": "string"
},
{
"name": "company",
"type": "string"
},
{
"name": "citiesLived",
"type": "record",
"mode": "repeated",
"fields": [
{
"name": "place",
"type": "string"
},
{
"name": "numberOfYears",
"type": "integer"
}
]
},
{
"name": "note",
"type": "string"
}
]
# bq update logs.sample nested01.fields.json Table '****:logs.sample' successfully updated.
スキーマが追加。 今まで投入されていたデータに追加されたカラムはnullが入る。
（スキーマは変更したが、投入するデータは以前と同じ）
# bq load --source_format=NEWLINE_DELIMITED_JSON logs.sample nested01.data.json Upload complete. Waiting on bqjob_r73583e1d3c68485e_000001672bece222_1 ... (0s) Current status: DONE
エラーにはならず、単純に新しいスキーマにはnullが入る 
{"gender": "Male", "fullName": "John Doe", "age": 22, "citiesLived": [{"numberOfYears": 5, "place": "Seattle"}, {"numberOfYears": 6, "place": "Stockholm"}], "kind": "person", "country": "Japan", "company": "A company"}
{"gender": "Female", "fullName": "Jane Austen", "age": 24, "citiesLived": [{"numberOfYears": 2, "place": "Los Angeles"}, {"numberOfYears": 2, "place": "Tokyo"}], "kind": "person", "country": "USA", "company": "B company"}
{"note": "with newline (\\n)", "kind": "person", "gender": "Male", "age": 30, "fullName": "newline", "citiesLived": [{"place": "Shinjuku-ku,\nTokyo"}], "country": "Japan", "company": "A company"}
{"note": "with tab (\\t)", "age": 30, "fullName": "tab", "citiesLived": [{"place": "Shinjuku-ku,\tTokyo"}], "kind": "person", "country": "USA", "company": "B company"}
{"note": "japanese / unicode escape sequence", "gender": "Female", "citiesLived": [{"place": "\u6a2a\u6d5c\u5e02"}], "fullName": "\u6a2a\u6d5c \u82b1\u5b50 (1)", "kind": "person", "country": "Japan", "company": "A company"}
{"note": "double-quoted string", "fullName": "\"quoted\"", "kind": "person"}
# bq load --source_format=NEWLINE_DELIMITED_JSON logs.sample nested01.data.json Upload complete. Waiting on bqjob_r34be71bb151950ee_000001672bf33c1d_1 ... (0s) Current status: DONE
投入された
jsonファイルを作成する
[
{
"name": "register_day",
"type": "STRING"
},
{
"name": "rtime",
"type": "STRING"
},
{
"name": "tid",
"type": "INTEGER"
},
{
"name": "sid",
"type": "INTEGER"
},
{
"name": "flag",
"type": "INTEGER"
},
{
"name": "id",
"type": "INTEGER"
},
{
"name": "lesson_date",
"type": "TIMESTAMP"
}
]
jsonファイルを用意して、bqコマンドで作成する
bq mk --table --expiration 3600 --description "This is my table" --label organization:development logs.mytable testTable.json
Table ‘***:logs.mytable’ successfully created. と表示されたら成功。 ブラウザで確認すると、問題なく作成されている。 
[
{
"name": "register_day",
"type": "STRING"
},
{
"name": "rtime",
"type": "STRING"
},
{
"name": "tid",
"type": "INTEGER"
},
{
"name": "sid",
"type": "INTEGER"
},
{
"name": "flag",
"type": "INTEGER"
},
{
"name": "id",
"type": "INTEGER"
},
{
"name": "lesson_date",
"type": "TIMESTAMP"
},
{
"name": "lesson_date2",
"type": "TIMESTAMP"
}
]
lesson_date2を追加し、bq update コマンドを実施
bq update logs.mytable testTable.json
Table ‘****:logs.mytable’ successfully updated.
となれば成功 無事カラムが追加された 
bq --format=prettyjson show --schema tsukadaproject:logs.logsPartition > bbbb
[
{
"mode": "NULLABLE",
"name": "register_day",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "rtime",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "lesson_date",
"type": "TIMESTAMP"
}
]
当然このformatを読み込ませることができる
bq mk --table --expiration 3600 --description "This is my table" --label organization:development logs.bbbb bbbb
JSONはスキーマ情報のみ。パーティションテーブルの指定はbqコマンドのオプションで行う。
[
{
"mode": "NULLABLE",
"name": "register_day",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "rtime",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "lesson_date",
"type": "TIMESTAMP"
}
]
パーテョションテーブル作成
bq mk --table --expiration 3600 --description "This is my table" --time_partitioning_field=lesson_date --time_partitioning_type=DAY --label organization:development logs.cccc cccc
Table ‘****:logs.cccc’ successfully created.
ストーリー仕立てで分かりやすくまとまっているのでおすすめ。
 |
BigQueryでクエリスキャンを行うと、いくらwhereで絞ったとしても、テーブル内の指定したカラムは全行読み、それに応じて課金される。BigQueryに分割テーブル(partitioned table)を作成することにより、時間や日付を絞り膨大なスキャンならびに課金を限定的にすることが可能だ。今回はembulkからデータのinsertを行うことにする。
MySQL -> (embulk) -> BigQuery
in:
よしなに
out:
type: bigquery
auth_method: json_key
json_keyfile: xxxx.json
path_prefix: /tmp/
file_ext: .csv.gz
source_format: CSV
project: xxxxx
dataset: logs
time_partitioning:
type: DAY
auto_create_dataset: true
auto_create_table: true
delete_from_local_when_job_end: true
table: sample
formatter: {type: csv, charset: UTF-8, delimiter: ',', header_line: false}
encoders:
- {type: gzip}
パラメータの詳細はこちら
https://github.com/embulk/embulk-output-bigquery
実施
docker run -t -v ${PWD}:/work tsukada/embulk run bq.yml
select _PARTITIONTIME AS pt, lesson_date rtime from [logs.sample] WHERE _PARTITIONTIME >= "2018-11-13 00:00:00" AND _PARTITIONTIME < "2018-11-16 00:00:00"
_PARTITIONTIMEは見えないカラムのようなもので、その日付はinsertで更新される。
WHERE _PARTITIONTIME でスキャン対象カラムを絞ることが可能。
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で実施はできないので注意。
ストーリー仕立てで分かりやすくまとまっているのでおすすめ。
|