Morning Girl

Web API, Windows, C#, .NET, Dynamics 365/CRM etc..

Embulk を使って kintone から MySQL にデータをロード:CData JDBC kintone Driver

前回の記事では、シンプルなCSVファイルからMySQLへ、Embulkを使ってETL処理を実施しました。

今回は趣向を変えて、クラウドサービス kintone から MySQLにデータをロードしてみたいと思います。

対象データ

対象データは kintoneのテンプレートアプリである顧客リストを対象にしました。

f:id:sugimomoto:20190828235013p:plain

こんな感じの項目がそろっています。そして、日本語カラム名というのがポイントですね。

f:id:sugimomoto:20190828235021p:plain

DWHやBIを実施する上でも顧客マスタは必須のデータです。最近は何かとデータがクラウドに分散されているので、こういったアプローチを取りたい方は多いのではないかなと。

どうやって Kintone からデータを吸い上げる?

f:id:sugimomoto:20190828235041p:plain

通常 kintone はAPIアクセスもしくはCSV連携でDWHなどにデータをロードするのが一般的かと思います。

CSVは言わずもがなですが、手動でCSVをダウンロードする必要があるため、常に最新のデータを分析したいユーザーにはひと手間多いです。

また、Embulk でもすでに公開されている Salesforce Plugin のように頑張って Plugin を書けば実現できますが、API をどのようにテーブルに咀嚼するか、ページングやフィルター処理はどうするか等、勘案することはかなり多いのですし、あまり手軽ではありません。(その点、SalesforceだとSOQLのようなクエリ言語があるので、案外親しみやすいかもしれません)

なので、今回は CData Software で公開している CData JDBC Kintone Driver を使って、この部分を解消します。

上記の図で示すように、Embulk ではJDBC Input Pluginが公開されているので、これをCData JDBC Kintone Driver と組み合わせて、データのロードを実現します。

CData Kintone JDBC のインストール

それでは実際にやってみましょう。

まず、以下のURLから CData Kintone JDBC Driver をダウンロードします。30日間のトライアルがあるので、それを使っても大丈夫です。

https://www.cdata.com/jp/drivers/kintone/jdbc/

ダウンロードしたZIPファイルを解答し、setup.jarを実行して、インストールします。

f:id:sugimomoto:20190828235051p:plain

インストール後、「C:\Program Files\CData\CData JDBC Driver for kintone 2019J\lib」にドライバーのjarファイルが生成されます。ここのファイルパスを後ほど使います。

f:id:sugimomoto:20190828235057p:plain

JDBC Input Plugin のインストール

次に、EmbulkとCData JDBC Driverをつなぎこむための、JDBC Input Plugin をインストールします。

https://github.com/embulk/embulk-input-jdbc/tree/master/embulk-input-jdbc

embulk gem install embulk-input-jdbc

MySQL Output Plugin のインストール

前回のBlogでもインストールしていますが、念の為。

https://github.com/embulk/embulk-output-jdbc/tree/master/embulk-output-mysql

以下のコマンドでMySQLの Output Pluginがインストールできます。

embulk gem install embulk-output-mysql

configファイルの構成

最後にEmbulkのconfigファイルで、JDBC Inputの設定を記述します。

接続のための詳しい内容はこちらのオンラインヘルプでも確認できます。

kintone-mysql.yml というファイル名で作成しました。

in:
  type: jdbc
  driver_path: C:\Program Files\CData\CData JDBC Driver for kintone 2019J\lib\cdata.jdbc.kintone.jar
  driver_class: cdata.jdbc.kintone.KintoneDriver
  url: jdbc:kintone:URL=https://XXX.cybozu.com/;User=XXXXX;Password=XXXX;
  table: "顧客リスト"
out: 
    type: mysql
    host: localhost
    database: DatabaseName
    user: UserId
    password: UserPassword
    table: "顧客リスト"
    mode: insert

ポイントはJDBC URLですね。以下のように接続したいKintoneテナントのUserID・パスワード・URLを入力します。

kintoneの接続情報 設定項目
ユーザID User
パスワード Password
kintoneのサブドメイン含むサイトURL  Url

あとは、専用のクラス名と kintone JDBC Driverのファイルパスを指定すればOKです。

テーブル名では、取得したいアプリの名前を記述します。日本語のリスト名でも大丈夫です。

実行

これで準備完了です。あとは「embulk run」で実行するだけです。

embulk run kintone-mysql.yml

実行すると、途中 kintone のレコードをInsertしているのが見えますね。

f:id:sugimomoto:20190828235113p:plain

実行後、MySQL Workbenchからテーブルを確認してみると、以下のようにデータが取得できました。

日本語のテーブル名・カラム名でテーブルが作成され、データが登録されていることが確認できますね。

f:id:sugimomoto:20190828235119p:plain

補足

ちなみに、上記の例ではテーブル名を直接指定しましたが、以下のようにSQL Queryを書いてもいいです。

Where句で作成日や修正日を指定すれば、最新のデータだけを対象にすることも可能です。

in:
  type: jdbc
  driver_path: C:\Program Files\CData\CData JDBC Driver for kintone 2019J\lib\cdata.jdbc.kintone.jar
  driver_class: cdata.jdbc.kintone.KintoneDriver
  url: jdbc:kintone:URL=https://XXX.cybozu.com/;User=XXXXX;Password=XXXX;
  query: "SELECT [RecordId],[会社名],[部署名] FROM [顧客リスト] WHERE [RecordId] = 1"
out: 
    type: mysql
    host: localhost
    database: DatabaseName
    user: UserId
    password: UserPassword
    table: "顧客リスト"
    mode: insert

このあたりのサポートしているSQLこちらのリファレンスをどうぞ。

MySQLへの登録はInsert以外にも、MergeやReplaceもあるので、上記のSQLと組み合わせて柔軟に移行できると思います。

https://github.com/embulk/embulk-output-jdbc/tree/master/embulk-output-mysql#modes

また、CData JDBC Driver は kintone以外にもなんだかいっぱいあります。私は次にTwitterを試してみようかと画策中です。

CData JDBC Driver List

f:id:sugimomoto:20190828235130p:plain

Embulk を使ってCSVからMySQLにデータをロード:Windows 環境

最近 Embulk という良さげなETLツールを見つけました。

JDBC DriverもPluginで追加できるという、個人的に大ヒットなすぐれもの。基本的にはLinuxベースで動かす人が多そうですが、クロスプラットフォーム対応しているので Windows でも動かせます。

なので、とりあえずさくっと Windows で動かしてみました。

Embulkとは?

ログファイルやCSVファイルなどの大量データをBlukで各種データストア、DWHなどのサービスへロードできるオープンソースのETLツールです。

f:id:sugimomoto:20190828193500p:plain

特徴的なのはPluginモデルを採用している点で、このPluginを取り込むことで様々なデータソースにInputとOutput処理が対応できます。

例えばCSVからRedisにいれたり、MySQLからCassandraに入れたりと。Pluginの一覧は以下で公開されていますし、JDBCなどの汎用の口を使えば、CData で提供している 150種類以上の JDBC Driver をデータソースにして取り込むこともできます。

Embulk plugin list

ざっくり Embulkを理解したい場合は、以下のスライドと開発者の方のリリースBlogがおすすめです。

www.slideshare.net

frsyuki.hatenablog.com

上記の通り、EmbulkはPluginモデルを採用しています。以下のページから Embluk の Plugin一覧が確認できます。

Embulk のインストール

以下のGithubのQuick Startに詳しく書かれています。

github.com

まず以下のPowerShellコマンドでbatファイルをインストールします。私は「C:\Work\Software\Embulk」に保存。

PowerShell -Command "& {[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::TLS12; Invoke-WebRequest http://dl.embulk.org/embulk-latest.jar -OutFile embulk.bat}"

このままだと使いにくいので環境変数に保存した場所を追加。

f:id:sugimomoto:20190828193509p:plain

とりあえず Quick Start

Embulk はコマンドラインで実行しますが、そこまでコマンドは多くありません。使いたいプラグインをインストールして、どのような処理を行うのか? をYAMLの設定ファイルに記述、そしてそれを実行する、というのが大まかな流れです。

   mkbundle   <directory>                             # create a new plugin bundle environment.
   bundle     [directory]                             # update a plugin bundle environment.
   run        <config.yml>                            # run a bulk load transaction.
   cleanup    <config.yml>                            # cleanup resume state.
   preview    <config.yml>                            # dry-run the bulk load without output and show preview.
   guess      <partial-config.yml> -o <output.yml>    # guess missing parameters to create a complete configuration file.
   gem        <install | list | help>                 # install a plugin or show installed plugins.
   new        <category> <name>                       # generates new plugin template
   migrate    <path>                                  # modify plugin code to use the latest Embulk plugin API
   example    [path]                                  # creates an example config file and csv file to try embulk.
   selfupdate [version]                               # upgrades embulk to the latest released version or to the specified version.

Quick Startに乗っているもので簡単に試すことができるので、それを見てみましょう。

embulk example ./try1 

exampleを実行すると、gz圧縮されたサンプルのCSVファイル(sample_01.csv.gz)と、対象のフォルダに実行用のconfigファイルを生成するための元ファイル(seed.yml)が生成されます。

seed.ymlは以下のようになっていて、InputするファイルとOutput先がざっくり書かれているだけのものです。

in:
  type: file
  path_prefix: 'C:\Work\Blog\20190826_EmbulkTrial\Trial\.\try1\csv\sample_'
out:
  type: stdout

このファイルを元にguess(推測処理)を実施し、設定ファイル(config.yml)を生成します。Embulkの特徴の一つだと思います。これによって取得したいCSVファイルの構成を読み取り、おおまかな設定ファイルが生成できます。

embulk guess   ./try1/seed.yml -o config.yml

生成された結果が以下の通りです。

in:
  type: file
  path_prefix: C:\Work\Blog\20190826_EmbulkTrial\Trial\.\try1\csv\sample_
  decoders:
  - {type: gzip}
  parser:
    charset: UTF-8
    newline: LF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    null_string: 'NULL'
    trim_if_not_quoted: false
    skip_header_lines: 1
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: id, type: long}
    - {name: account, type: long}
    - {name: time, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
    - {name: purchase, type: timestamp, format: '%Y%m%d'}
    - {name: comment, type: string}
out: {type: stdout}

ポイントはdecoders、charaset、newlineなどCSVファイルを読み取って、フォーマット情報を選別し、設定ファイルにピクアップしてくれているところと、CSVのカラム情報などもあらかじめ構成してくれるところでしょうか。このあたりを一つ一つ書くのは大変だと思うので、CSV取り込み時には大変ありがたい機能だと思います。

この設定ファイルを元にOutput先に定義したところへ出力するわけですが、その前にpreviewコマンドで実行結果の想定を確認できます。ここでInputした定義がどのようになっているかが確認できますね。

embulk preview config.yml

f:id:sugimomoto:20190828193522p:plain

最後に run コマンドで、設定ファイルを実行できます。デフォルトのサンプルはコマンドラインに出力するだけですが、以下のような感じです。

embulk run     config.yml

f:id:sugimomoto:20190828193533p:plain

MySQL Output を試す

これだけだとつまらないので MySQL への Outputを試します。

github.com

前述の通り、Embulk は Pluginモデルなので、公開されている・開発したPluginを自由に取り込んで接続先を拡張することができます。

以下のコマンドでMySQLの Output Pluginがインストールできます。

embulk gem install embulk-output-mysql

インストールしているPluginは 以下のコマンドで一覧確認ができます。

embulk gem list

f:id:sugimomoto:20190828193540p:plain

次にconfigファイルをいじります。Outの部分にMySQLの設定を加えました。Modeは色々とありますが、今回はシンプルにInsertだけです。テーブル名に指定した部分は自動的に生成されます。

in:
  type: file
  path_prefix: C:\Work\Blog\20190826_EmbulkTrial\sample_
  parser:
    charset: UTF-8
    newline: LF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    null_string: 'NULL'
    trim_if_not_quoted: false
    skip_header_lines: 1
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: id, type: long}
    - {name: account, type: long}
    - {name: time, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
    - {name: purchase, type: timestamp, format: '%Y%m%d'}
    - {name: comment, type: string}
out: 
    type: mysql
    host: localhost
    database: DatabaseName
    user: UserId
    password: UserPassword
    table: example
    mode: insert

これで embulk run を実行。

f:id:sugimomoto:20190828193550p:plain

こんな感じでデータが生成されました!

f:id:sugimomoto:20190828193557p:plain

次回は JDBC Plugin を使って、 CData JDBC Driver と組み合わせた処理を実行してみたいと思います。

kageura.hatenadiary.jp

Reference

github.com

qiita.com

dev.classmethod.jp

christina04.hatenablog.com

既存のGraphQLサービスからJSON形式のSchemaを生成する prisma/get-graphql-schema

何度かやってるんですが、毎回何でやったか忘れてしまうので書き残し。

GraphQLのスキーマを生成したい

GraphQLではスキーマが仕様上重要なポイントになっています。

f:id:sugimomoto:20190825154057p:plain

このスキーマを元に、クライアントやサーバーサイドのベースとなるソースコードを生成したりしますが、オープンなAPIにこれを取得するベーシックなアプローチは提供されていません。(ODataであれば、明示的にMetadataエンドポイントが提供されているのですが)

しかしながら、Github上でいくつかGraphQLサービスからスキーマを生成するプロジェクトがありましたので、その一つの使い方を紹介したいと思います。

対象のGraphQLサービス

おそらくオープンなGraphQLの中で一番メジャーかなと思う、Github API V4を対象としました。

Referenceはこちら。

developer.github.com

Rootendpointはこちら

https://api.github.com/graphql

GithubのAccessToken取得

GraphQLにアクセスするために、あらかじめGithubのAccessTokenを取得しておきましょう。

GithubはOAuthでAccessTokenを取得しますが、UIから手動生成する手順も提供されていますので、今回はそれを使ってAccessTokenを取得し、使用します。

https://help.github.com/en/articles/creating-a-personal-access-token-for-the-command-line

ちなみにスキーマを取得するだけなので、パーミッションは最低限だけで大丈夫です。

prisma/get-graphql-schema のインストール

スキーマファイルの生成にはGraphQL as a Service のPrismaが提供している get-graphql-schema を使います。

github.com

実行には npm が必要になるので、あらかじめインストールしておきましょう。

手順はそんなに難しくありません。まずGraphQL をコマンドから実行できるようにする「get-graphql-schema」をnpmでインストールします。

npm install -g get-graphql-schema

スキーマファイルの生成

以下のようなコマンドで対象のGraphQL API を指定すれば、カレントフォルダにスキーマファイル(JSON形式)が生成されます。

get-graphql-schema -h 'Authorization=Bearer <取得したAccessToken>'  https://api.github.com/graphql > schema.graphql

ちなみにパラメータとして-jを渡すとJson形式で生成可能です。

get-graphql-schema -j -h 'Authorization=Bearer <取得したAccessToken>'  https://api.github.com/graphql > schema.json

f:id:sugimomoto:20190825154112p:plain

スキーマファイル生成後の使い道

最近Postmanのアップデートに伴いGraphQLリクエストをサポートしました。

blog.getpostman.com

しかも、スキーマファイルを取り込むことで、オートコンプリートを有効化し、GraphQLクエリを入力しやすくできます。

Meetup #8 GraphQL Tokyo 2019 Summer のまとめにもならない参加メモ

はじめて GraphQL関係の勉強会「Meetup #8 GraphQL Tokyo 2019 Summer」に参加してきたました。

f:id:sugimomoto:20190816114035p:plain

www.meetup.com

セッションはオープンスペースという形式で参加者から募集したトピックと投票によって決定します。初めてだったんですが、事前にスライドの準備などはなく、ライブで進んでいくのが新鮮で面白かったですねー。

bliki-ja.github.io

全部で4セッション(2セッション X 前半後半)で、私は「マイクロサービスのサービス間通信で GraphQL を使うのは他の手法と比べてどうかの議論」と「SDL first or code-first ? https://www.prisma.io/blog/the-problems-of-schema-first-graphql-development-x1mn4cb0tyl3」に参加しました。

以下、勉強会が終わったあとに、思い出すままに書いているざっくりしたメモの走り書きです。正直ついていくだけでめちゃくちゃハードだったので、まとまってもいないです。

マイクロサービスのサービス間通信で GraphQL を使うのは他の手法と比べてどうかの議論

@mtsmfm さんの社内での展開も含めた課題感からの議論

現在社内ではgRPCでマイクロサービスを構成している → Why?

・今まではRESTで実装されていたが、API Spec的な仕様の共有方法も無く、つらみが多くなっていた

・gRPCというよりも、ProtocolBufferがほしかった。 → これによって、クライアントサイド・サーバーサイドの実装がスキーマから生成できて、楽になるんじゃない? みたいなところを狙っていた

・でも、gRPCは仕様的にちょっとネックポイントがある → メインがProtocolBufferによるSpecの共有観点ならばGraphAPIもありなんじゃない? というのが今回の話の流れ

なぜgRPCはネック?

  1. HTTP2 → 社内のインフラがHTTP1前提で構成されていたのでgRPCを採択するにあたって、かなり手を加えなければいけない

  2. gRPCリッチすぎ →「Unary」「Server streaming」「Client streaming」「Bidirectional streaming」とあるけど、プロトコルの要件的にそこまで高機能を求めていない。

  3. ProtocolBufferの型ゆるくない? → 特にオプション周りがゆるすぎ感

他のスキーマファーストもどうなのよ?

  1. OpenAPI(Swagger)

  2. JSON Schema

などなど。(RAMLもか)(あまり話にはのぼらなかった)

ProtocolBufferが大事なら、ProtocolBuffer+HTTP1という手もいいんじゃないの?

(なんかツールの名前を話していた気がするが思い出せない)

(でもそれなら、OpenAPI(Swagger)・RAML・JSON Schemaという手もあるよね?)

rejoinerというアプローチもあるよね

gRPCを束ねて、GraphQLのサービスを提供する仕組み

https://github.com/google/rejoiner

API Management系のサービスでもよくあるパターンか。SOAP→RESTへのプロトコル変換 バックエンドのAPIリクエストを束ねるタイプのもの的な)

でも GraphQL の仕様的にマイクロサービスに向かなくない?(結論的な)

GraphQLの思想的にネストされたリソースや複数リソースを効率的に取得する、UIベースのクライアントサイドで処理しやすいような取得を求められるケースが多い。

そもそもRPC的なアプローチが多いと考えると、GraphQLでマイクロサービスを実装した場合、全部Mutationを使うことになりそうじゃない? とか。(私的意見)

マイクロサービスの文脈だと、もっと固定的(?なんて表現したらいいんだろう)な処理が多くなりそうだから、GraphQLの特性を活かしきれないのではないか?

SDL first or code-first ? https://www.prisma.io/blog/the-problems-of-schema-first-graphql-development-x1mn4cb0tyl3 スキーマファーストかコードファーストか

www.prisma.io

Prismaの中の人がスキーマファーストに問題があるよーみたいな話をしているけれど、それって実際どうなのよ? というのを議論したい

そもそも、PrismaはPrisma1でスキーマファーストいいよ! って言っていた立場じゃないの? そこからリゾルバの生成とDBのスキーマ生成もやってなかったっけ?

ただし、ライブラリ・ GraphQL as a Service の人たちのBlogなので、ポジショントーク的なところも意識しながら読む必要あり。

上記Blogでは、以下のスキーマファースト問題点がピックアップされていたので、それをベースに話をしました。

Problem 1: Inconsistencies between schema definition and resolvers

Problem 2: Modularization of GraphQL schemas

・GraphQLは公式の仕様として、ファイルのモジュール化をサポートしていないよね? という話

・でも、Prismaが拡張仕様としてgraphql-importを出して、他のライブラリなどもそれに追従しているのが流れ

・大量のスキーマを一ファイルで管理するのは得策ではない。だから、スキーマファーストは厳しいよね? という話だが→果たして本当にそうなのか? ファイルを分けて、あとでマージするのを内部で行ってもいいんじゃない? とか。

・確かにコードファーストでクラスファイルライクにモジュール分割できれば楽だけど、コードファーストのスキーマ表現力にはまだ各言語ごとの実装に不足がある?とか

・コードファーストにしてしまうと、GraphQLのスキーマから各言語ごとのスキャンフォールディングした実装を生成するのが困難になっちゃうよね、とか

スキーマファーストでカスタムLinterを定義して、そのカスタムLinterも共有できるようにしたいよね(なるほど)

Problem 3: Redundancy in schema definitions (code reuse)

Problem 4: IDE support & developer experience

・そもそもスキーマファイルってIDEで実装する際はプレーンなテキストファイルとしか認識されないよね? →で もVS CodeとかでシンタックスハイライトやLinterが出てるんだから、小さな問題じゃない?

その他トピックとして気になったものメモ

GraphQLでAPIを提供する GraphCMSというものがあるらしい

graphcms.com

Prisma2 について

www.prisma.io

AWS Appsync の話も聞きたかったな

aws.amazon.com

PowerApps Portals で問い合わせページを作る ③ エンティティ・ページアクセス許可の設定

前回の記事では一覧画面を作成しました。

今回はその一覧画面に対して、ログインしたユーザーでアクセス範囲を規定する設定を、ポータルを通じて実現してみたいと思います。

docs.microsoft.com

続きを読む

PowerApps Portals で問い合わせページを作る ② 問い合わせ履歴一覧の構成

前回に引き続き、PowerApps Portals でお問い合わせページを作ります。

今回作るもの

前回は問い合わせフォームを作りましたが、今回はその問い合わせの履歴を表示する一覧画面を作りたいと思います。

こんな感じで過去に投稿した問い合わせが表示されます。また、件名をクリックすることで、詳細画面に移動するようにもしました。

f:id:sugimomoto:20190801232516p:plain

続きを読む

PowerApps Portals で問い合わせページを作る ① フォームの構成

前回の記事では PowerApps Portals の全体的な要素について見渡してみました。

今回から、何回かに分けて、シナリオベースで PowerApps Portalsの「アプリの作り方」をざっくりと見ていきたいと思います。

続きを読む