bigquery - ゆるふわキャンパー

ゆるふわキャンパー

Sign in

bigquery

A collection of 22 posts

Rewrite SQL for GCP billing export v1

Rewrite SQL for GCP billing export v1

先日課金情報エクスポート機能の正式版が発表され、課金情報のテーブルのスキーマが変更されました。 * GCP billing export機能のGA版がリリースされて作成されるカラム名等が変わった GCP の課金情報を Google BigQuery へ送り Redash で可視化しているので、このための SQL を書き直す必要があります。 * Visualize GCP Billing Data 書き換えた箇所こんな感じで毎月の Billing 情報を集計しています。これのための SQL を書き換えていきます。 * before SELECT SUM(cost) AS sum_cost, product, LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_MONTH(TIMESTAMP_TO_USEC(start_time))), 7) AS month

rsyslogd was HUPed

rsyslogd was HUPed

/var/log/secureをGoogle BigQueryへ送っています。 * Send secure log to BigQuery 最近/var/log/secureがGoogle BigQueryに送られていないことに気づき(Redashのグラフを見ていて気づいた)、なんでだろうと思ったら/var/log/messagesにこんなログが複数あった。 Oct 14 18:07:01 lorentzca rsyslogd: [origin software="rsyslogd" swVersion="7.4.7" x-pid="451" x-info="http://www.rsyslog.com"] rsyslogd was HUPed logrorateがHUPを送っているだけ？今回の件に関係があるのか不明…。 Google BigQueryのテーブルを見ると9月22日を最期に止まっていたようだった。おそらくGhost1.0にアップデートするついでにサーバも作り直したタイミング。なぜrsyslogが止まったのか、

GCP billing export GA version has released

GCP billing export GA version has released

Google BigQueryへGCPのBilling情報を送っています。 * Export GCP Billing data to Google BigQuery このbilling export機能について、先日メールで案内が配信されました。内容は、billing export機能がGA(正式版)になり、それにともなってスキーマが変わるから移行してね、というものでした。ざっくり要約すると以下のような内容です。 * 暫くの間は新しいスキーマと古いスキーマ両方作成される * 2018年1月に古いスキーマの作成が行われなくなるのでそれまでに既存のワークフローの更新が必要具体的には以下のような変更があるとのことです。 * productカラムがservice.descriptionにリネーム * resource_typeカラムがsku.descriptionにリネーム * start_timeとend_timeカラムがusage_start_timeとusage_end_timeにリネーム * service. idカラムの追加 *

Post GCP Billing data to Mackerel with Node.js

Post GCP Billing data to Mackerel with Node.js

Node.jsのBigQueryライブラリを使ってGCPのBillingを取得してみました。 * Get GCP Billing with Node.js 次はこのBillingの情報をMackerelにサービスメトリックとして送ってみます。 Mackerel側のインターフェースの確認サービスメトリックは以下のデータ形式で送れば良いみたいです。 [ , , … ] 詳細はこちら。 * サービスメトリック - Mackerel API ドキュメント (v0) つまり、今回の場合は以下のようなデータを送れば良いみたいです。 [ {name: "gcp.monthly_cost", time: "1234567890", value: "0.1567"} ] Node.jsのコードを書く以下のようなコードを用意します。 // Imports the Google Cloud client library // Instantiates a client var bigquery

Get GCP Billing with Node.js

Get GCP Billing with Node.js

Node.jsのBigQueryクライアントライブラリを使ってみました。 * Node.js client library for Google BigQuery 今回はこれを使ってGCPのBillingデータを取得してみます。 ※ GCPのBillingデータをBigQueryに送る方法は以下。 * Export GCP Billing data to Google BigQuery 以下のようなコードを書いてみます。 // Imports the Google Cloud client library // Instantiates a client var bigquery = require('@google-cloud/bigquery')({ projectId: 'GCPのプロジェクトID', keyFilename: 'BigQueryへアクセスするためのjsonキーのpath' }); // Query var query = 'SELECT SUM(cost) AS sum_cost, LEFT (FORMAT_UTC_

Visualize illegal ssh access attempts and access source

Visualize illegal ssh access attempts and access source

/var/log/secureをBigQueryに送り始め、sshdへのアクセスがあるIPアドレスを多い順に集計してみたりしています。 * Send secure log to BigQuery * Visualization of IP address accessing sshd 今回は日毎にssh接続の試行失敗回数を集計して、それに対するIPアドレスの内訳も出してみます。こんなクエリを作成してみます。 SELECT LEFT(FORMAT_UTC_USEC(UTC_USEC_TO_DAY(time * 1000000)), 10) AS day, COUNT(ident) AS count_ident, REGEXP_EXTRACT(message, r'(\d{1,3}\.\d{1,3}\.\d{1,3}

Visualization of IP address accessing sshd

Visualization of IP address accessing sshd

/var/log/secureをBigQueryに送り始めました。 * Send secure log to BigQuery 1日あたり十数MB分ほどのペースで溜まってきているようです。これを使って何かしたいなということで、とりあえずsshdへアクセスしてきているIPアドレスを可視化してみます。 sshdへのアクセスのログはFailed keyboard-interactive/pam for root from port 46472 ssh2のような感じでメッセージ内にアクセス元のIPアドレスが記録されているので、これを使います。以下のクエリを使います。 * REGEXP_EXTRACTで正規表現に合致した文字列を抽出できるので頑張ってIPアドレスを抽出(数値1~3桁.数値1~3桁.数値1~3桁.数値1~3桁からなる文字列) * 期間はTABLE_DATE_RANGE関数で指定 * ident(identification?識別子?)がsshd * アクセスが多い順にTOP50個に絞っている SELECT REGEXP

Send secure log to BigQuery

Send secure log to BigQuery

/var/log/secureをBigQueryに送ってRe:dashで攻撃っぽいアクセスなどを可視化してみたかったので送ってみました。準備 * /var/log/secureをtd-agentでも読めるようにパーミッションを下げる * /var/log/secureがローテートされてもパーミッションが戻らないようにlogrotateの設定を変更する td-agentの設定 td-agentの設定を追加します。 * 部分でログの送信元のサーバのホスト名をレコードに追加している # syslog (secure log) @type tail format syslog path /var/log/secure tag syslog.secure pos_file /var/log/td-agent/syslog.secure.pos @type record_transformer

Node.js client library for Google BigQuery

Node.js client library for Google BigQuery

先日Google Cloud Functionsを試したところ、まだNode.jsしか対応していないことがわかりました。 * Hello Google Cloud Functions やりたいこととして、Google Cloud FunctionsからGoogle BigQueryにためているGCP Billingデータを取得したいというものがあるので、今回はNode.jsでGoogle BigQueryのAPIを叩くところまでやってみます。 GoogleがBigQuery用のクライアントライブラリをNode.jsでも提供しているので、それを使います。準備ドキュメントをみながら進めます。 * BigQueryクライアントライブラリ - Node.js まずgcloudコマンドを使ってGCPにログインしておきます。 gcloud beta auth application-default login 次にnpmコマンドでGoogle BigQueryのクライアントライブラリをインストールします。 npm install --save @

Change H2O log format to LTSV

Change H2O log format to LTSV

h2oのログをtd-agentを使ってGoogle BigQueryに送っています。 * Send h2o log to BigQuery 今まで私はtd-agentのtailプラグインの設定で、フォーマットを愚直に正規表現で設定していました。 format /(?[^ ]*) (?[^ ]*) (?[^ ]*) (?[^\]]*) (?[^ ]* +\S* +\S*) (?[^ ]*) (?[^ ]*) "(?[^\"]*)" "(?[^\"]*)"/ 課題として、以下の不満がありました。 * 見辛い * h2oのログフォーマットを変えたとき追従して正規表現を書き直さないといけないそこでh2oのアクセスログのフォーマットをLTSVに変更してtd-agentでltsvフォーマットを使えるよ

Bdash

BIツールにre:dashを使っています。 * redash - ゆるふわキャンパー今朝Twitterを見ていたら、新しいBIツールのBdashが発表されていたのでどんなものか使ってみました。 * BdashというBIツールをリリースしました - hokaccha hamalog v3 * GitHub - bdash-app/bdash: A simple business intelligence application. 特徴 BIツールはre:dashしか使ったことがないのですが、re:dashと比べると以下のような特徴があります。 * Electronで作成されたデスクトップアプリケーション * gistで共有できる使ってみるデータソースとしてGoogle BigQueryをつなぎたいのでまずGCPの管理画面から新しくBdash用の認証アカウントを作成します。 * 認証情報(とサービスアカウント)の作成参照鍵ファイルを取得したらBdashに設定して完了。こんな感じで直感的に使えます。 gistへの

Send GCP Billing metrics to Mackerel

Send GCP Billing metrics to Mackerel

Google BigQueryへエクスポートしたGCP Billingデータを使ってmackerelにGCP Billingのメトリクスを送ってみます。メトリクスを送るためのプラグインは夜な夜な作りました。オプションにプロジェクトIDとデータセット名とテーブル名を指定すると現日付けの月の合計のBillingデータを取得することが出来るプラグインです。早く使ってみたい！ * GitHub - Lorentzca/mackerel-plugin-gcp-billing: GCP billing costom metrics plugin for mackerel 準備このプラグインは実行するための条件がいくつかあります。テーブルに以下のスキーマが必要 * cost(FLOAT型) * start_time(TIMESTAMP型) 課金データのエクスポート機能でGoogle BigQueryにデータを送っていれば特に考えることはないはず。サーバ上で実行するにはGoogle BigQueryに対する権限を持っている認証アカウントのjsonキーが必

Trying out golang BigQuery client library

Trying out golang BigQuery client library

GCPのbillingをbigqueryにエクスポートしてre:dashで可視化してみました。 * Export GCP Billing data to Google BigQuery * Visualize GCP Billing Data 次はmackerelにbillingの情報を送ってメトリクスとして記録してみたいので、まずはgolangからbigqueryに対し何かしらのクエリを叩くところまでやってみます。以下のドキュメントを見ながら試してみます。 * BigQuery クライアントライブラリ準備ライブラリのインストール。 go get cloud.google.com/go/bigquery go get google.golang.org/api/iterator * cloud.google.com/go/bigqueryはbigqueryのapiを叩くライブラリ * ちなみにこのライブラリはまだβ版なので注意 * google.golang.org/api/iteratorはgoogle ap

Visualize GCP Billing Data

Visualize GCP Billing Data

GCPのBillingデータをGoogle BigQueryに送り始めました。 * Export GCP Billing data to Google BigQuery 早速re:dashで可視化してみます。可視化する内容 Google BigQueryへ送られるデータの詳細は以下です。この中からてきとうに使えそうなものを拾っていきます。 * Billing data in BigQuery - Cloud Platform Console Help 今回は、 * X軸は月 * Y軸はコストの合計 * また、Y軸にプロダクトの種類(BigQuery, Computing Engineなど)も含めるみたいな感じで集計してみます。クエリこんなクエリでやってみる。 SELECT SUM(cost) AS sum_cost, product, LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_

Schedule query execution with re:dash

Schedule query execution with re:dash

heroku上にre:dashを立てて利用しています。 * re:dash on heroku 今回はre:dashのスケジュール機能を使って、特定のクエリを定期実行させてみます。設定方法クエリ編集画面からスケジューリングしたいクエリを選択して編集。 Yay!

Export GCP Billing data to Google BigQuery

Export GCP Billing data to Google BigQuery

Google BigQueryを使うようになって暫く経ちました。 * bigquery - Page 1 - ゆるふわキャンパーそこで気になるのはGCPの使用料金です。Billingアラートを5ドルに設定したり、使いすぎないように気を使ってはいますが、どうせならちゃんとトラッキングしたいものです。というわけでBillingの情報をGoogle BigQueryに記録する設定を有効にしてみました。 * Export billing data to BigQuery - Cloud Platform Console Help この機能、割と最近のもの？らしいですね。BillingデータをGoogle BigQueryに送る方法で調べると、いったんバケットにファイルとしてエクスポートしてそれをごにょごにょしてGoogle BigQueryに保存している記事が散見されました。やりかた「お支払い」メニューからエクスポート先にGoogle BigQueryを選んで、プロジェクト名とデータセットを選択するだけ。なんて簡単なんだ。 Billingデー

Visualization referral domains using google BigQuery

Visualization referral domains using google BigQuery

re:dashとGoogle BigQueryでアクセスログを可視化して遊んでいます。 * bigquery - ゆるふわキャンパー * redash - ゆるふわキャンパー最近Google BigQueryのドキュメントを眺めていてDOMAIN関数の存在を知ったので、これを使ってリファラのドメインを集計してre:dashで可視化してみます。 h2oでリファラをログに出力する方法ログフォーマットでリクエストヘッダを指定(%{Referer}i)することでリファラをアクセスログに出力することができます。 * Access Log Directives - Configure - H2O - the optimized HTTP/2 server こんな感じでGoogle BigQueryに送られていっています。 $ bq query "SELECT referer FROM prod.h2o_access_log_20170118 where referer != \"-\" group by referer LIMIT

re:dash on heroku

re:dash on heroku

※ Re:dashのバージョン0.12.0向けの記事です(古い…)。 Re:dashの1系からは公式でherokuをサポートしなくなったようです。 * Remove Procfile.heroku - it's no longer up to date. · getredash/redash@0e68228 · GitHub bigqueryにサーバのログを送っています。 * Send h2o log to BigQuery せっかくなので、ビジネス・インテリジェンスツールの一つであるre:dashを使って可視化環境を構築してみます。 * GitHub - getredash/redash herokuでre:dashを動かしてパスワードログイン出来るところまでやってみます。基本的にドキュメントに沿ってやってます。 * Setting up Re:dash instance — Re:dash documentation まずはre:dashのダウンロードから。 * git cloneで取ってくるとうまくgit

Monitoring Fluentd with Mackerel and Tuning Fluentd buffer config

Monitoring Fluentd with Mackerel and Tuning Fluentd buffer config

h2oのログをbigqueryに送り始めました。 * Send h2o log to BigQuery 今のところ特に問題は無いのですが、正直バッファの各設定値やリトライ回数のチューニングについてはどうすれば良いのかよくわからないのでなんとかしたいところです。そこで、mackerel-agent-pluginsを導入してみます。メトリクスを取ることでチューニングしやすくなるだろうと言う目論見です。 * mackerel-agent-plugins/mackerel-plugin-fluentd バッファについては、下記のサイトが分かりやすかったです。 * Fluentdの仕組み -バッファ機能でログ収集漏れを防ぐ- | Tech-Sketch * BufferedOutput pluginの代表的なoptionについて - Qiita td-agent側の準備まずtd-agent.confでmonitor_agentプラグインを有効化します。下記設定を追加してtd-agentを再起動。 * Monitoring Fluentd | F

Add logsource hostname to record

Add logsource hostname to record

bigqueryに送るレコードに送り元のホスト名を追加してみます。送り元ホストを条件に検索できるようになります。今のところ個人で複数のサーバを運用する予定はないので別に送り元ホストを条件に検索することも無いのですが、こまけーこたぁいいんだよ！ filterプラグイン(組み込み)を利用して実現します。 * record_transformer Filter Plugin | Fluentd filterプラグインを使うと、td-agentのレコード(tailしたログファイルの行など)にデータを追加することが出来ます。イメージ的には以下のような感じでしょうか。 [time, tag, record = {ip: "1.2.3.4", status: "200"}] ↓ [time, tag, record = {ip: "1.2.3.4", status: "200", logsource_hostname: "ponpokopon.me"}] td-agentの(fluentdの)データ構造については、以下の記事が分かりやすかったです。 *

Add new column to BigQuery

Add new column to BigQuery

h2oのログにUesr-Agent情報も追加した。ログのフォーマットで%{HEADERNAME}iとすると、任意のレスポンスヘッダを出力させることが出来る。 * Access Log Directives - Configure - H2O - the optimized HTTP/2 server access-log: path: /var/log/h2o/access.log format: "%h %l %u %{%Y/%m/%d:%H:%M:%S}t \"%r\" %s %b \"%{User-agent}i\"" bigqueryにh2oのログを送っているので、td-agentとbigquery側でも対応する。既存のテーブルにUser-Agent用のカラムを追加するまず既存のテーブルのフォーマットを取得する。jqでフィールド情報のみを抽出すると便利。以下私のbigqueryの例。 bq --format=prettyjson

Send h2o log to BigQuery

Send h2o log to BigQuery

h2oのアクセスログをtd-agentを使ってGoogle BigQueryに送ってみる。幾つか手順が必要。 * bigqueryの設定 * スキーマ定義ファイルの作成 * td-agentの設定 bigqueryの設定 bigqueryへアクセスするためにapiを有効化し、アクセスキーを発行するのだが、この工程が長い…。(´・_・`) 慣れてないと混乱する。 gcpアカウントの作成作る。 * Google Cloud Computing, Hosting Services & APIs | Google Cloud Platform プロジェクトの作成請求先アカウントの設定 bigqueryのapiを有効化認証情報(とサービスアカウント)の作成これでやっと以下のようなjson形式の鍵ファイルがダウンロードされる。td-agnetの設定で使う。 { "type": "......", "project_id": "......", "pr