「システム基盤構築のプロフェッショナル」レック・テクノロジー・コンサルティングJapanese | English

レック・テクノロジー・コンサルティング株式会社

技術ブログ

HOME > 技術ブログ > 月別アーカイブ: T.W

月別アーカイブ: T.W

【GCP】BigQueryを利用した迅速かつ安価なデータ分析基盤の構築検証

はじめに

皆さん、こんにちは。クラウド基盤技術部のT.W.です。

弊社ではGoogle Cloud Platform (以下、GCP)を使ったシステム提案/開発に注力しており、各種イベントやGCPサービスを展開しております。

技術ブログでもGCP関連のネタを随時公開していきますので、是非ご活用下さい。

やりたいこと

GCP内に閉じた環境で迅速なデータ分析基盤構築と安価に構築できるデータ分析基盤の構築

検証で採用するGCPコンポーネント

GCP BigQuery(データウエアハウス)
GCP Cloud Data Fusion(ETL/抽出・変換・格納)
GCP Cloud Storage(データストレージ)
GCP Compute Engine(仮想マシン)
GCP Cloud SQL (データベース MySQL)

構成図

01.png

構成図の説明

まず初めに、今回の検証におけるデータの一連の流れを軽く説明します。

今回は、検証に利用するデータをデータカタログサイトから取得して、MySQLのデータベースに格納し、これをデータソースとして検証を実施します。

具体的には、データソースからSQLで必要なデータのみを抽出して、CSV形式でCloud Storageに格納し、このデータを取得、解析、加工して、BigQueryに投入します。

BigQueryに格納しているデータを如何に見える化・可視化するかは、Google社が提供する無料のBIツール「データポータル」を利用します。

最終的に、データポータルで作成したグラフなどの分析情報を分析依頼者に共有して完了とします。

事前準備

検証データ取得

02.png

https://www.data.go.jp/data/dataset/meti_20141009_0022

機能検証の段階では、なかなか本番システムのマスタデータを使用するのは難しいと思います。
そこで今回は、日本政府が二次利用が可能な公共データの案内・横断的検索を目的で公開しているオープンデータを利用します。

検証データをDBへ格納

03.png

検証で利用するCSV形式のデータを如何にMySQLのデータベースに投入するかは、いろいろやり方があると思います。

長年利用され続けてきたほとんどのRDBMSと一部のNoSQLに対応し、データベース統合開発環境として利用されている、JetBrains社のDataGripを今回は利用して、CSVデータをDBに投入します。
https://www.jetbrains.com/ja-jp/datagrip/

一般的なやり方ですと、テーブルの作成、データ型の指定、場合によっては、エラーハンドリングなどの煩雑なオペレーションを行わないといけないのに対し、DataGripを利用するとほぼ全自動でやってくれる利点があります。

DataGripから対象データベースに接続したら、事前に入手したCSVデータファイルをDataGrip画面の対象データベースにドラッグアンドドロップするだけで、テーブル定義、データ型の定義などが瞬時に作成されます。もちろん、細かい要件に応じて、微調整も行えます。

04.png

05.png

データ分析基盤構築

GCP Cloud Storageバケット作成

06.png

次はGCP Cloud Storageのバケットを作成します。
細かいところを無視して、バケットの名前のみ入力して作成します。

BigQuery テーブル作成

07.png

次はBigQueryテーブルを作成します。
自動生成されたカラム名とデータ型が問題ないか確認して、テーブルを作成します。

GCP Cloud Compute Engine

08.png

ここでは定期的にデータベースからデータを抽出することを想定してGCP Compute Engineを使用しますが、Cloud Functionでも対応できると思います。MySQLクライアントから、以下のSQL文でデータを抽出し、CSVデータとして一時的にローカルに保存します。

09.png

10.png

CSVファイルをCloud Storageへアップロードするには、以下のコマンドで実現します。
# gsuitl cp <CSVデータファイル名> gs://<バケット名>/<パス>

11.png

該当ファイルがCloud Storageにアップロードされたことを確認します。

12.png

GCP Cloud Data Fusion (ETLツール)

13.png

次はGCP Cloud Data Fusionの環境を構築します。
GCP Cloud Data Fusionを簡単で説明しますと、GUI環境でコードを意識せずに ETL / ELT パイプラインが作成できるサービスです。

実態はビックデータ解析・分析として有名はApache Sparkなので、GUIのあるApache Sparkサービスとも言えるでしょう。

14.png

作成が完了するまでは数分かかります。完了したら「インスタンスを表示」をクリックします。

15.png

「Wrangler」をクリックします。

16.png

事前にCloud StorageへアップロードしたCSVをクリックします。

17.png

最初はテキストファイルとして認識されていますが、以下のように数回クリックすることで簡単にCSVファイルとして認識させることができます。

18.png

今回の検証では利用しない機能ですが、以下のように機密情報などは簡単にマスキングすることもできます。

19.png

ほかにも、データ加工する機能、計算、変換、フォーマット変更などいろいろあるので、ぜひ触って見てください。CSV認識、カラム削除、データ型変換など合計25ステップのデータトランスフォーメーションが提供されています。

20.png

さて、Wranglerの右側にSink機能のBigQueryを追加して、加工したデータの出力先をBigQueryに指定します。あわせて、BigQueryのプロパティの出力先のプロジェクトID、データセット名とテーブル名を入力して保存し、デプロイます。

21.png

検証なので、クラスターのCPU、メモリ、ノート数などのサイジングはすべてデフォルトのままで実行します。ここで最重要かつ行わなければいけないことは、エラーが発生しないように祈ることです。笑

22.png

さて、Status箇所で「Succeeded」が確認できました。

23.png

GCP BigQueryにデータが入っているかどうかを確認します。

24.png

BigQueryにデータが確認できたら、データの見える化、可視化に移ります。

データポータル(BIツール)

25.png

データポータルは、Google社が提供する無料のBIツールです。Googleアカウント同士であれば、簡単にユーザ間で結果を共有することができます。

データポータルのメインページはテンプレートがたくさんありますが、今回は空のレポートを指定します。

https://datastudio.google.com/

26.png


データの接続先として、BigQueryを選択します。

27.png

プロジェクト、データセット、テーブルを指定します。

28.png

対象テーブルを指定すると、以下のような画面が表示されます。

29.png

グラフやチャートのタイプを選択するなど数回のクリックと、数分待つことで、以下のような綺麗なダッシュボードになります。

30.png

最後、完成した可視化できたデータを依頼者へ共有して完了となります。

検証は以上となります。

31.png

気を付けるべき点

1. BigQueryのデフォルト課金はオンデマンド課金のため、対策をせずに大量・不必要なクエリを実行すると、恐ろしい金額で請求される場合がある
2. GCP Cloud Data Fusionで文字列を数字に変換する際、Integer型へ変換失敗した場合はLong型を検討する
3. 現時点では、GCP Cloud Data Fusionのユーザ・インタフェースは英語しかない
4. GCP BigQueryとCloud Data Fusionのテーブルは日本語のカラム名に対応していない
 → 早い段階でカラムを英語へ変換したほうが楽ですね。

今後について

今後については、以下の3点をブログで公開予定です。


1. ソースとなるデータベースをAWSのRDSと連携
2. AWS Lambda・GCP Cloud Functionsでデータを抽出して、CloudStorageにアップロードする部分を自動化
3. マルチクラウド環境で、如何に効率よく、かつ、セキュアなデータ連携を実現できるか、を考察

今後も是非、弊社の技術ブログを見守ってください!

まとめ

最後までお読みいただきありがとうございます。
このように、小規模から大規模なデータセットを簡単かつ迅速に安価なデータ分析基盤を作成できました。蓄積したデータを有効に活用できていないなと感じている方、ぜひお試しください!!!

【GCP】BigQueryを利用した迅速かつ安価なデータ分析基盤の構築検証

Capability Tour 2019 ~Singapore Part2 - Oracle19c New Features~

こんにちは。Re:QのT.Wです。

A.Yと共に、私もOracle OpenWorld Asia: Singapore 2019に参加してきました。A.Yによる前回の投稿では、当社のCapability TourやOOW、そして全体的な旅程などの概要について触れましたが、今回はOOWのセッション内容について少しご紹介したいと思います。

内容としては、Oracle Database: What's New and Comming Up Next with 19c を中心に、Oracle Database最新バージョンの新機能についてです。

・SUPPORT - LONG TERM SUPPORT

まず、Oracle Database 19cでは、LONG TERM SUPPORTを受けられることが確定しました!
ということは、プライマリーサポートは2022年まで、延長サポートは2025年までです。更に、延長サポートは別途追加料金がかかりません。Oracle Database 11g、Oracle Database 12cからのバージョンアップの際は、是非ご活用ください。

・PERFORMANCE - AUTOMATIC INDEXES

次は、インデックス自動作成機能です。Oracle Database 19cで最も注目される機能と言えます。パフォーマンス・チューニングの一環として、インデックスは正しく作成されたのか考慮する必要があります。必要な作業として、データベース全体統計、メモリ、頻繫にソートされる列、LOB、パーテーション、並列処理などの情報を収集及び分析する必要があり、場合によって情報の収集から実装まで数ヶ月かかるかもしれません。

このような大変と思われる作業がOracle Database 19cから変わります。

インデックス・アドバイザと違い、インデックス自動作成機能はただの推奨(recommendation)を提供するだけではありません。
この機能は実際のインデックスを作成まで実装してくれます。

キャプチャー → 識別 → 確認 → 判断および実装 → モニター → キャプチャー → ...

以上、五つのステップに加え、自らの間違いを学習する「強化学習」(Reinforcement Learning)により、情報収集、分析、スケジュール調整、インデックスの実装まで一連のコスト(時間や人力など)を削減できることが期待されます。

・PERFORMANCE - REAL-TIME STATICTICS COLLECTIONS

SQLクエリからテーブルへのアクセスをする際、パフォーマンスを向上させるため、事前に収集した統計情報を元に最適な手段でアクセスする必要があります。しかし、これまでの統計情報収集の方法は一定の時間をおいて実行されており、24/7システムにとっては、「最適」とは言えません。Oracle Database 19c のリアルタイム統計情報収集はDMLごと(insert、update、deleteなど)に統計情報が更新されるので、統計情報は常にリアルタイムに近い状態に保たれています。

・HIGH AVAILABILITY - DATA GUARD DML REDIRECT

これまでのOracle Active Data Guardの人気機能として、STANDBYデータベースは主にレポート作成やバックアップ用途として使われており、STANDBYデータベースに対して「書込み」オペレーションはできませんでした。しかし、19cの新機能 DATA GUARD DML Redirect を使用すると、ユーザーはSTANDBYデータベースに対する「書込み」オペレーションが可能です。

実際の実行順番:
1.STANDBYデータベースにDMLを実行
2.DMLがPRIMARYデータベースにリダイレクト
3.PRIMARYデータベースがDMLによりデータ変更
4.PRIMARYデータベースより生成されたログがSTANDBYデータベースに送信
5.STANDBYデータベースのデータが変更

補足ですが、実際のDMLによるデータ変更はPRIMARYデータベースしか処理しないので、STANBYデータベースに大量のDMLクエリを実行するのはできるだけ避けたほうが良いかもしれません。

・Oracle Hybrid PARTITION TABLES

一つの巨大テーブルを複数のパーティションに分割すると、その利点としてはより簡単に管理やパフォーマンスの向上が期待されます。
使用状況に応じて、基本的にはDMLによる更新されるテーブルと読み取り専用テーブルなどの種類があります。Oracle Databse 19c では分割された読み取り専用テーブルを外部テーブルに変更し、ファイルシステムに保存することもできます。さらにクラウドベースのオブジェクトストレージ(Oracle Cloud Object Storage、Amazon S3)に保存することができるようになりました。こちらの新機能もコスト(時間や人力など)を削減することに期待されます。

・APPICATION DEVELOPMENT - PARTIAL JSON UPDATE SUPPORT

これまでのJSONデータ列を更新する際、全く新しい列にデータをアップロードする必要がありました。この機能により、JSONデータ列の一部を更新することができるようになりました。

・Web SQLDeveloper - livesql.oracle.com

DBAとしてOracle Database 19cの新機能を確かめたい時に、以下の2つの方法で確認できます。

方法1のステップ:
1.Oracle公式サイトでOracle Database 19cをダウンロード
2.OSイメージを用意し、インストール
3.Oracle Database 19cインストール
4.確認開始

方法2のステップ:
1.livesql.oracle.com へログイン
2.確認開始

賢いあなたはどちらの方法を採用しますか?

Oracle livesqlを利用するメリットは以下になります。

・Oracle Database 19cが標準インストールされます
・無料
・SQL実行できる
・チュートリアルあり
・スクリプト共有できる

もちろん、SQLを学びたい方、会社の新人研修なども活用できたらと思います。
Oracle Database 19cの新機能は以上のみではありません。
他の新機能に興味がある方はぜひOracle公式で提供しているドキュメントを確認ください。

参考になれば幸いです。

別のメンバが更にPart3を公開予定ですので、そちらも楽しみにしていてください!

Capability Tour 2019 ~Singapore Part2 - Oracle19c New Features~

MB と MiB の単位記号の使い分け

1MB = 1000kB ? それとも 1024kB ?
どちらが正しいと思いますか。

こんにちは。T.W.です。

普段我々が使っているkB、MB などのデータ単位の記号は、
実際にいくつのバイト数を指しているかご存知ですか。

kBだけの単位を注目いただけると、k と B の2つのアルファベットで構成されています。
この場合ですと、それぞれ下記のような意味を持っています。

・前者:国際単位系(SI)におけるSI接頭辞
 「1000」の代わりにアルファベット「k(1k)」で表現できる。
・後者は:固定サイズのデータ(バイト)の略
 「1000バイト」の代わりに「1kB」で書かれている場合が多い。

▼▲▼▲ 小ネタ ▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲
国際単位系(SI)では、小文字の「k」が正式表記になります。大文字ではありません。
(知ってました?これだけでもびっくり!?するかもしれませんね)
▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲

主によく使われる十進法から見るとk、MなどのSI接頭辞を使うことで、
非常に大きな数字を扱いやすくできるメリットがある反面、
コンピュータの世界では二進法で計算を行いますので、
k、Mなど十進法の接頭辞を二進法の数字に使うと数の誤差が生じる場合があります。

例えば、1024バイト(2^10バイト)を1.024kBに書き変えると、さらに扱いにくくなるし、
1kBに書き変えると24バイト分が少なくなるケースがあります。

そこで、上記の問題を解決するために、電気工学、電子工学、および関連した技術を扱う
国際的な標準化団体である国際電気標準会議(IEC)が決めた「2進接頭辞」を推奨しています。

2進接頭辞って、設計書などのドキュメントで見たことありますでしょうか。

2進接頭辞の書き方・読み方(バイトの数を表現する場合)

[table id=36 column_widths="30%|40%|30%" /]

記述方法は、SI接頭辞の後に「i」を付けるだけですね。

なぜ2進接頭辞普及しないのかとその理由について

2進接頭辞普及しない理由はいくつか考えられますが、よく言われるのはわずかな誤差を無視する慣習があったからです。二進法ベースのシステムでは、大きな量を表す際、SI接頭辞キロ(k)が表す乗数1000に近い1024、SI接頭辞メガ(M)が表す乗数1000000に近い1048576、その誤差を無視する慣習がありました。一気にSI接頭辞ではなく、2進接頭辞を使うことがなかなか社会・業界への浸透ができないのが現状です。別の観点からみると、キロバイトは1000バイトか1024バイトか曖昧な部分もあり、事前に明白・説明する必要があります。

これから2進接頭辞を普及させるべき理由とは

その理由も誤差に密着な関係にあります。
今現在、使用するデータ容量が大きくなってきていますので、桁が大きくなればなるほどにこれから無視できない誤差が生じるからです。

上記の内容で、
1000(k)に近い1024をキロで表現すると、24(2.4%)の誤差が発生し、
1000000(M)に近い1048576をメガで表現すると、48576(4.8576%)の誤差が発生します。
以下の表ご覧ください。

[table id=37 column_widths="30%|40%|30%" /]

ご覧頂いた通り、単位が大きければ大きいほと、誤差が大きくなることがわかります。

2018年現在TB単位のポータブルストレージを簡単に入手することができます。更に数年後には、PB単位のストレージが量販店で流通する時代がくるでしょう。そのときにあなたは12%の誤差を許容できますか?

そういったことを踏まえて、今から2進接頭辞の記述や読み方を覚えておきましょう。

自分は、今回執筆にあたって改めて詳細を調べたりもしましたが、こういう機会がなくても最新動向や情報に敏感な人ってちょっとカッコイイですよね。

参考になれば幸いです。

以上

MB と MiB の単位記号の使い分け
資料請求・お問い合わせはこちら

▲ ページトップに戻る

技術ブログ

2021年5月
« 2月  
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          
採用に関するお問い合わせはこちら