astamuse Lab

astamuse Labとは、アスタミューゼのエンジニアとデザイナーのブログです。アスタミューゼの事業・サービスを支えている知識と舞台裏の今を発信しています。

DenoのハイパフォーマンスなWebフレームワーク

お久しぶりです、植木です。前回記事を書いてから、かれこれ一年が経ち、また当番が回ってきました。

現在も、ICPのエンジニアを担当しており、バックエンドとフロントエンド問わずに両方やってます。

はじめに

さて、今回はJS界隈では話題のDenoに関するトピックスです。

弊社でもSwaggerで設計した内容を実際のコードにきちんとエンドポイントに落とし込んでいるのかを確認するツールや、Slack botで活用しており、徐々に浸透が広まっております。
個人的にもコードレビューでの気になるコードの動作確認をDenoのReplで行ったり、書き捨てのコードが必要な時もJSでささっと書いてDenoで実行するケースは多いです。

Denoはこの一年でますます勢いを増し、deno.land/xを見てみると、サードパーティモジュールの数は約2000にも及んでいます。

今回は、そのサードパーティモジュールの中でも人気があったり、パフォーマンスを一番発揮するようなフレームワークはどれなの?という疑問に対して、比較検証した結果を紹介します!

準備

下記を使用して、検証しました。(インストール方法は割愛)

  • Deno 1.8.2
  • autocannon v7.0.5

autocannonでは、Req/Sec、Byte/Sec、Latencyを計測できるので、それを中心に測定していきましょう

Webフレームワークを選定する

個人的に運営しているdeno-x-rankingを活用しながら、最近人気がありそうな11個を選定しました。
deno.land/xではGithubのdescriptionまで見れないので、deno-x-rankingで読んでWebフレームワークと言えるか判断していきいました。

ノミネートはこちら。A-Z順に並べます

abc
alosaur
aqua
deno-drash ※ 通称はdrashですが、この記事ではリポジトリ名の方で統一します dragon
mandarinets
oak
opine
pogo
reno
servest

この中では、oakが1番人気で聞いたことがある人も多いかもしれません。かく言う僕も心の中でもoakを上回るのはどれかなと楽しみです。

コードを書く

Hello worldを返すサンプルコードを1つづつ用意していきます。
ついでに、一言ずつコメントと、スター数とフォーク数(※2021/03/31時点)をつけて紹介していきます。計測結果だけ知りたい方は飛ばしてください。

abc

expressライクに書けて、分かりやすいです。2018年からあるフレームワークで、メンテされ続けてます。スター数も今回紹介する中では5番目とやや多め。
Star: 515 / Fork: 45

import { Application } from "https://deno.land/x/abc@v1.3.0/mod.ts";

const app = new Application();

console.log("http://localhost:8080/");

app
  .get("/", (c) => {
    return "Hello, World!";
  })
  .start({ port: 8080 });

alosaur

デコレーターで定義されてるタイプです。Javaのアノテーションとよく似てますが、JSでは定義するときはただの関数と変わりません @Controller
ロードマップがあるので、今後も機能追加されていくことでしょう。スター数は4番手。

Star: 515 / Fork: 45

import {
  App,
  Area,
  Controller,
  Get,
} from "https://deno.land/x/alosaur@v0.29.3/mod.ts";

@Controller() // or specific path @Controller("/home")
export class HomeController {
  @Get() // or specific path @Get("/hello")
  text() {
    return "Hello World!";
  }
}

// Declare module
@Area({
  controllers: [HomeController],
})
export class HomeArea {}

// Create alosaur application
const app = new App({
  areas: [HomeArea],
});

app.listen('0.0.0.0:8080');

aqua

今回紹介する中ではコード量が少なくシンプルです。こちらもExpressライクです。
READMEにあるベンチマーク紹介では、abcやdeno-drashを上回ると書いてあるので、期待が高まります

Star: 121 / Fork: 4

import Aqua from "https://deno.land/x/aqua@v1.1.0/mod.ts";

const app = new Aqua(8080);

app.get("/", (req) => {
    return "Hello World!";
});

deno-drash

ドキュメントのチュートリアルがしっかりしてますDrash Land - Drash。ひっそりYoutubeもあります。
「サードパーティに依存しないHTTPサーバー」と紹介されてる通り、deps.tsの中をみてみると、https://deno.land/x/から始まるものが1つもありません
スター数は2番手。

Star: 806 / Fork: 24

import { Drash } from "https://deno.land/x/drash@v1.4.1/mod.ts";

class HomeResource extends Drash.Http.Resource {
  static paths = ["/"];
  public GET() {
    this.response.body = "Hello World!";
    return this.response;
  }
}

const server = new Drash.Http.Server({
  response_output: "text/html",
  resources: [HomeResource]
});

server.run({
  hostname: "0.0.0.0",
  port: 8080
});

dragon

リポジトリ作成日が2020/11/29となっていて、かなり新しいです。Readmeのベンチマークに関する記載があり、そこではoakを上回っているので、期待を込めて選びました

Star: 61 / Fork: 9

import {
  Application,
  HttpRequest,
  HttpResponse,
  RequestMethod,
} from "https://deno.land/x/dragon@v1.1.5/lib/mod.ts";

async function main(args: string[]): Promise<void> {
  const app = new Application();

  const r = app.routes({ maxRoutes: 1 });

  r.Path("/").withMethods(RequestMethod.GET).handleFunc(
    async function (Request: HttpRequest, ResponseWriter: HttpResponse) {
      ResponseWriter.withBody("Hello Dragon").end();
    },
  );

  app.listenAndServe({ port: 8080 });
}

await main(Deno.args);

mandarinets

MVCで、こちらもデコレーションが使われてます。
mandarineorgというorganizationで、orangemicrolemonという関連プロダクトがあります。
ドキュメントもかなりしっかりしてます MandarineTS

Star: 170 / Fork: 9

import { MandarineCore, Controller, GET } from "https://deno.land/x/mandarinets@v2.3.2/mod.ts";

@Controller()
export class MyController {

    @GET('/')
    public httpHandler() {
        return "Hello World!";
    }

}

new MandarineCore().MVC().run();

oak

サードパーティの中では一番人気なのがこれです。コードもスッキリしてます
Denoのメンテナでもある@kitsonkさんが主体で活動してるので、メンテナンスされていくことは確実でしょう。

Star: 3084 / Fork: 159

import { Application } from "https://deno.land/x/oak@v6.5.0/mod.ts";

const app = new Application();

app.use((ctx) => {
  ctx.response.body = "Hello World!";
});

await app.listen({ port: 8080 });

opine

こちらもexpressライクに書けるタイプです。
以前から、Reactのexampleをはじめexamples配下が充実しており、実装の際に参考になることでしょう。とはいえReactに関しては、aleph.jsが台頭する前は、と言うのがあるので、今だとaleph.js使うのが良いとは思います。

Star: 512 / Fork: 25

import { opine } from "https://deno.land/x/opine@1.2.0/mod.ts";

const app = opine();

app.get("/", function(req, res) {
  res.send("Hello World!");
});

app.listen(8080);

pogo

こちらもシンプルでいい感じで、routesの書き方もAdding routesで紹介されてるような書き方も自由です。
ReactとJSXもサポートしてます。

Star: 344 / Fork: 28

import pogo from 'https://deno.land/x/pogo@v0.5.2/main.ts';

const server = pogo.server({ hostname: '0.0.0.0', port : 8080 });

server.router.get('/', () => {
    return 'Hello World!';
});

server.start();

reno

Errorハンドリングがはじめから入っている分、少し長くは見えますが、複雑なところはないです。
そもそも標準ライブラリのhttp/server.tsを薄くラップする思想で作られているので、これが正しい姿と言えますね。

Star: 96 / Fork: 3

import { listenAndServe } from "https://deno.land/std@0.90.0/http/server.ts";

import {
  createRouter,
  createRouteMap,
  textResponse,
  NotFoundError,
} from "https://deno.land/x/reno@v1.3.11/reno/mod.ts";

function createErrorResponse(status: number, { message }: Error) {
  return textResponse(message, {}, status);
}

export const routes = createRouteMap([
  ["/", () => textResponse("Hello World!")],
]);

const notFound = (e: NotFoundError) => createErrorResponse(404, e);
const serverError = (e: Error) => createErrorResponse(500, e);

const mapToErrorResponse = (e: Error) =>
  e instanceof NotFoundError ? notFound(e) : serverError(e);

const router = createRouter(routes);

await listenAndServe(
  ":8080",
  async (req) => {
    try {
      const res = await router(req);
      return req.respond(res);
    } catch (e) {
      return req.respond(mapToErrorResponse(e));
    }
  },
);

servest

Denoコントリビュータで、日本人(@keroxp)が作ってます。スター数も3番手。(すごい!)
デフォルト設定でログが出るので、公平を期すために今回はLoglevelをERRORで設定することで余計なログは抑えて、勝負する事にしました。

Star: 694 / Fork: 38

import { createApp, Loglevel, setLevel } from "https://deno.land/x/servest@v1.2.0/mod.ts";

const app = createApp();
setLevel(Loglevel.ERROR);
app.get("/", async (req) => {
  const body = await req.respond({
    status: 200,
    headers: new Headers({
      "content-type": "text/html",
    }),
    body: "Hello World!",
  });;
});

app.listen({ port: 8080 });

速度検証結果

レイテンシー分析

dragon、alosaur、aqua、deno-drash、oakのレイテンシーは平均値で全て50ms以内で安定して早いことが分かります。
また、99.0%においても50ms以内に返すことができているのは、dragonのみという結果でした

平均値

Latency Avg (ms)
dragon 27.14
alosaur 32.01
aqua 32.2
deno-drash 32.96
oak 38.41
reno 86.67
abc 91.89
pogo 93.77
mandarinets 96.21
servest 144.38
opine 157.62

f:id:astamuse:20210331185458p:plain

Req/Sec分析

ここでもdragon、alosaur、aqua、deno-drash、oakが速度が早く安定していることが分かります。
dragonとoakでは、1000rps程度違うので、この差は大きいです。
alosaurは、1.00%, 2.50%が上位5つで比べて低いので、たまにうまくリクエストを捌けていないようです

平均値

Req/Sec Avg(rps)
dragon 36190.6
alosaur 30767.3
aqua 30584.6
deno-drash 29884
oak 25702.8
reno 11469.4
abc 10819.8
pogo 10604.6
mandarinets 10334.21
servest 6894.3
opine 6318.6

f:id:astamuse:20210331185442p:plain

伝送速度分析(Byte/Sec)

ここでもdragon、alosaur、aqua、deno-drash、oakが速度が早く安定していることが分かります。
レイテンシー分析とReq/Sec分析では、deno-drash→oakでしたが、ここで初めてoak→deno-drashと順位が入れ替わりました。
平均値でdragonは、renoやservestの実に4倍のスコアが出ています。
alosaurは、1.00%, 2.50%が上位5つで比べて低いので、たまにうまく伝送できていないようです

平均値

Bytes/Sec Avg
dragon 3510000
alosaur 2800000
aqua 2780000
oak 2360000
deno-drash 2270000
abc 1010000
opine 967000
pogo 965000
mandarinets 940000
reno 883000
servest 779000

f:id:astamuse:20210331185434p:plain

検証時に発生したerror: Uncaught (in promise) BrokenPipe: Broken pipe (os error 32)というエラーに関して

下記のモジュールはで検証中に、実はautocannonの実行後にエラーが発生しました

alosaur
aqua
deno-drash
dragon
mandarinets
opine
reno

error: Uncaught (in promise) BrokenPipe: Broken pipe (os error 32)
    at handleError (deno:core/core.js:186:12)
    at binOpParseResult (deno:core/core.js:299:32)
    at asyncHandle (deno:core/core.js:223:40)
    at Array.asyncHandlers.<computed> (deno:core/core.js:238:9)
    at handleAsyncMsgFromRust (deno:core/core.js:207:32)

今回使用した、autocannonの-p/--pipeliningオプションを使用した際に起こるようで、Deno本体の問題かどうかの切り分けがされていない状態のIssueは存在しました。
(本題ではないこともあり、)今回の検証で発生した情報はIssueに提供しておき、情報を待つことにしました

github.com

結論

パフォーマンス存分に出す必要があるなら、dragonを選びましょう。
パフォーマンスと人気度(今後もメンテナンスされる可能性が高そう)両方を必要とするなら、oak, deno-drash, alosaur の中から、記法や機能差(ここは調査できてないのでご自身で調べてみてください)を基準に1つを選びましょう。
・上記2つに当てはまらない(ささっと、とりあえず動かしたいというような時など)パフォーマンスとコード量の両方を重視したいなら、aqua, oakがおすすめです。

Denoの今後の動き

これまで説明してきたWebフレームワークはdeno_std/httpを使用している前提の話でしたが、Deno namespace APIに直接httpが扱えるようになるPull Requestが出ています。
[WIP] feat: native HTTP bindings by bartlomieju · Pull Request #9935 · denoland/deno · GitHub

つまり、Denoのドキュメントで紹介されるように、

import { serve } from "https://deno.land/std@0.91.0/http/server.ts";

const server = serve({ hostname: "0.0.0.0", port: 8080 });

のようにimportしてから利用するのではなく直接、
Deno.http.createServer("127.0.0.1:8080")と書くことができるようになると言うことです。

これがmergeされることでパフォーマンスは上がるので、今回の検証結果に一泡吹かせる事になるかもしれません。 また、途中で紹介したエラーもstdに起因する原因であれば発生しなくなる可能性もあります

感想

使ったことがないWebフレームワークも多く、また最終的なパフォーマンス結果に差が思っていたよりあったので、検証してみて面白かったです!
中でもdragonaquaはスター数も少なく、この記事を読むまで知らない人が多かった事でしょう!隠れたヒーローを見つけたような気分になりました。
今回の作業内容はこちらのリポジトリにあります。 github.com

至らない点や、足りないフレームワークを見つけたら、Issueをあげてリクエストするか、Pull Requestでコードベースで投げつけてくださると幸いです。

最後までお読みいただきありがとうございました。
アスタミューぜでは引き続き、一緒に働いてくれるデザイナー・エンジニア、さらにはDenoを使って活躍したい方を大募集中です!
下記に採用サイトもありますので、是非ご覧ください。

参考

デザインガイドラインについて考えよう

f:id:astamuse:20210331101211p:plain

こんにちは。デザイン部の橋本です。

企業やブランド、サービスのデザインを行う際「デザインルール」や「デザインレギュレーション」、「デザインシステム」といったデザインのガイドラインをつくることがあると思います。

弊社でも上記のようなデザインガイドラインを作成し、業務に役立てています。今回はそんなデザインガイドラインについて、他社事例を含めて、いろいろと調査してみました。

デザインガイドラインでよく整備されているもの

デザインガイドラインとは、色・文字・レイアウトなど様々なデザイン要素について、ルールを綿密に定義したドキュメントのことです。 ※今回は主に “Webページやアプリにおけるデザインガイドライン” について調査しています。

デザインガイドラインで整備するものはプロジェクトの規模で変わってきますが、割と整備されていることが多いものをまとめてみました。

f:id:astamuse:20210330193235p:plain

Brand - ブランド
まずはサービスのブランドイメージを決定します。デザインガイドライン全体を通して、統一感を持たせて、共通のメッセージを発信するのに必要な土台となります。
言語化したり、写真を使用したりしてブランドイメージを伝えます。
Typography - タイポグラフィ
タイポグラフィーをスタイルガイドによってどのように最適化するかは、サイト全体のクオリティーを左右する重要なポイントです。
まず見出しの種類( h1〜h6) についてや、本文テキスト、太字やイタリックなどについての設定を行います。続いて、リンクやリード文などに使えるような小さめのカスタムフォント、そして文字のフォントや太さ、色についての検討を行います。
Color - 色
色はブランドイメージと強く関連づけて利用されることが多いので、カラーパレットの選定は、ブランドの追求結果にもとづいて、慎重に行われます。
カラーパレットを作成する際はプライマリーカラー(サービスを特徴づける色)は必ず設定しますが、セカンダリーカラーは設定していない場合もあります。ブランドの主色をより効果的に見せるために、白、灰色、黒などの中間色を加えます。
Icon - アイコン
アイコンは文字よりもはるかに高い伝達能力を発揮します。ユーザーはアイコンを見るだけで、今何が行われているのか、また次に何が起きるのかを瞬時に把握することができます。
アイコンを決定する際はブランドバリューとアイコンの適合性について考慮する必要があります。
Form - フォーム
フォームはユーザーとサイトを結ぶインタラクティブ性の高いパーツです。
ユーザーはフォームを介してデータ入力をし、サイト側はそれに対する操作を行います。起動時やホバー時の表示、また、パスワードが弱すぎることやIDが無効なことを伝えるためのエラーや警告、「支払い完了」などの成功……このようなフィードバック要素について、あらかじめ決定しておきます。
Button - ボタン
ボタンはカラーパレットやフォーム、トンマナを合わせて構成されるツールです。
ボタンデザインには統一感を持たせ、ユーザーに一貫したサイト体験を提供できるように工夫します。
Margin - 余白
統一された正確な余白は、Webサイト全体にまとまりと洗練された印象を生み出すと言われています。
見出しやボタン、画像、フォームにおける余白など、あらゆる要素の余白について設定します。具体的には余白の設定ルール(5の倍数、8の倍数など)を決めて、適応していくパターンが多いです。
UI Element - エレメント
カードデザインや、テーブルデザイン、ページャーなどのUIコンポーネントを収録します。更新頻度が最も高く、収録数も多い要素です。
Logo - ロゴデザイン
ロゴデザインについては、詳細な運用ルールは別紙でまとめられており、デザインガイドラインでは一部のルール(カラー、余白スペース、最小サイズ)だけ触れられているケースが多い気がします。
Photo - 写真
ビジュアルのデザインは、サービスのイメージを左右する重要な要素です。Webサイトのキービジュアルやサムネイル、挿絵などで使用する画像など、主なビジュアル要素についてもガイドラインを設定します。
写真の選定は感覚的な側面も強いため、はっきりとしたルール作りは難しいのですが、OK例とNG例を示すことで目指すエリアを浮かび上がらせることができます。
Illustrations - イラスト
イラストも写真と同様にサービスのイメージを左右する重要な要素です。使用できるイラストの方向性をあらかじめ決めておくと、サービス全体に統一感を持たせることができます。

整備するものに特別な決まりはなく、なかにはデザインガイドラインを作っていないプロジェクトもあります。

デザインガイドラインのメリット/デメリット

デザインガイドラインのメリット

f:id:astamuse:20210331102226p:plain

メリット1. デザイン品質の担保

制作対象の規模が大きくなると、デザインを複数人で行うことがあります。 こうした時にデザインガイドラインがあれば、個別に作業していても統一感のあるデザインで制作することが可能です。 新しいデザイナーが着任した時には、立ち上がりの拠り所になります。

メリット2. ユーザビリティの担保

デザインガイドラインによって統一感のあるわかりやすいUIが実現できていれば、ユーザーの学習コストが下がり、操作性も向上します。

メリット3. 制作コスト削減

デザインガイドラインによって制作のテンプレートができていれば、新規に制作するページであってもそれらを組み合わせたり引用したりすることで、制作コストを削減することができます。

また、デザイナーだけでなくプロダクトオーナーやディレクター、エンジニアなどデザイン関係者にもガイドラインを浸透させることができれば(ガイドライン制作時から多くの人を巻き込むのが理想的です…!)、共通言語ができることでコミュニケーションロスを最小化でき、デザインのミスも見つけてもらいやすくなります。

デザインガイドラインのデメリット

デメリット1. アップデートが必要

サービスというのは成長していくものなので、サービスが成長すれば、デザインガイドラインもアップデートしていく必要があります。デザインガイドラインの品質を保つためには、デザイナーがある程度手間をかけて運用していかなければなりません。

デメリット2. 表現の幅が狭まる

デザインガイドラインは統一性を高めることでブランディングにも繋がるものですが、細かく決め過ぎると、表現や制作の自由度を奪ってしまい、新しいことやその時々にふさわしい表現や提案ができなくなってしまうこともあります。また、視覚的に変化の乏しい画一的なデザインになってしまい、ユーザーの興味の持続が難しくなる可能性があります。

デザインガイドラインの事例

Material Design Guidelines(Google)

f:id:astamuse:20210330193353p:plain

https://material.io/

マテリアルデザインは、Googleが提唱するデザインシステム。リアルな世界の「物体(マテリアル)」が奥行と厚みを持つように、Web上に表現されたデザインでも奥行と厚みを見せることによって、より操作性の高いインターフェイスを実現しようとするものです。

Human Interface Guidelines(Apple)

f:id:astamuse:20210330193417p:plain

https://developer.apple.com/design/human-interface-guidelines/

Apple社が設けたデザインガイドライン。iPhoneをはじめとする様々なApple社デバイスで動作するアプリに対するルールが規定されています。1978年の初版から更新され続けている歴史あるデザインガイドラインです。

Spectrum(Adobe)

f:id:astamuse:20210330193431p:plain

https://spectrum.adobe.com/

Adobeのデザインシステム。制作ソフト寄りのアイコンが多くあり、見てみると面白いです。「International design」のページには、世界的にユーザーのいるAdobeだからこその配慮などが書かれています。

IBM Design Language(IBM)

f:id:astamuse:20210330193448p:plain

https://www.ibm.com/design/language/

タイポグラフィからモーションUIまでとても細かく表記されているデザインガイドライン。ギャラリーのページは、ムードボードのようになっており、見ているだけでIBMの世界観が伝わってきて、デザイナーが迷った時に、立ち戻って冷静になれるページだと思います。

Orbit(Kiwi.com)

f:id:astamuse:20210330193506p:plain

https://orbit.kiwi/

チェコを拠点とする航空予約サイトKiwi.comが開発したデザインシステム。UIコンポーネントの種類が豊富で、利用ルールも丁寧に定めているため、複雑な画面を設計する際のヒントになります。

さいごに

参考文献・資料

最後までお読みいただきありがとうございました。 アスタミューぜでは引き続き、一緒に働いてくれるデザイナー・エンジニアを大募集中です! 採用サイトもありますので、是非ご覧ください。

BigQueryテーブルのレコードを直にスプレッドシートに出力する

みなさんこんにちは。ご無沙汰しておりますKJです。

前回投稿したのは、昨年2020年春の緊急事態宣言が発令される前だったので、ほぼ1年ぶりになります。 昨年春の時は保育園に預けることができずにお家に自粛していた娘はすっかりFireTVにハマってしまい、 2歳半近くになった今はFireTVのリモコンを使いこなすようになりました。。

最近はEテレの幼児向け番組である、いないいないばあっ!のDVD ワンツー!パンツー! にハマっています。

www.nhk-ep.com

「わん!ちゅー!! ぱん!ちゅー!!!」と言いながら毎日無限リピート再生しています。

 

一方、その頃私は何をしているかというと、

弊社内に蓄えられているデータから、目的によって様々な集計を行い事業チームに連携しております。 この際、データの集計にはBigQueryをよく利用しており、 事業チームはExcel等で更にアドホックな分析や顧客提案用のグラフに落とし込んでいたりしています。

もちろん、日次や週次など定期的に決まった手段で集計が必要なものや、頻繁に利用される集計であるならば、 データスタジオを利用したり、もしくはダッシュボードを用意するのが便利でしょう。

一方で、顧客提案に利用する場合だと、データの抽出条件や集計条件が案件ごとに異なったりするため、 同一のクエリを再利用できることはほとんどありません。 こういった場合は、データスタジオにビューを定義したり、ダッシュボードを作成する手間をかけるよりも、 CSV等のファイルにエクスポートしたものを渡すほうが手っ取り早いです。 また、受け取る事業チームも使い慣れたツールにすぐに取り込むことができるので、ファイルで連携するのが便利だったりします。

このため、データ集計や抽出の業務は大まかに以下のような流れで運用していました。

  1. 本番環境セグメントでBigQueryで集計
  2. 上記の結果をGCSにCSV形式でエクスポートし、それを本番環境セグメントに配置されているGCEにダウンロード
  3. 本番環境セグメントからローカルマシンにファイルを移す
  4. GoogleDriveにアップロードして事業チームに連携

もしくは

  1. ブラウザUIにてBigQueryで集計
  2. ブラウザからローカルマシンにダウンロード
  3. GoogleDriveにアップロードして事業チームに連携

上記の手順でしばらく運用していたのですが、次第に 「ファイルをダウンロードしてGoogleDriveにアップロードするのが地味に手間」 という風に思うようになりました。 定型的な業務でありながら時間が取られるため、なんとか簡略化できないかと考えたわけです。

結論として、「BigQueryで集計した結果を、GoogleDrive上のスプレッドシートに直にエクスポートする」環境を整えたので、 それをコード例含めて紹介したいと思います。

そもそもですが。。。

実は、BigQueryとスプレッドシートの連携についてですが、 今回対象となる「BigQueryのデータをスプレッドシートで閲覧する」ための機能はGoogleから提供されています。

support.google.com

しかし、この機能を利用するには Google Workspace (旧GSuite) のEnterpriseプランが前提となります。 Enterpriseアカウントがなければこの機能は利用できませんが、 既にEnterpriseプランを利用しているのでしたら、この機能を積極的に利用しましょう。

今回紹介するのは、Enterpriseプランなしで、BigQueryとGoogleDrive、およびスプレッドシートから提供されているAPIを利用して実現します。

本題

以下の流れをプログラムで処理することで、BigQueryのクエリ結果をスプレッドシートで閲覧できるようにします。

  1. BigQueryにて集計したいクエリを実行し、結果をBigQuery上のテーブルに保存する。
  2. GoogleDrive APIを利用して、エクスポート先のスプレッドシートをGoogleDrive上に作成する。
  3. BigQueryのAPIを利用して、上記1で作成したテーブルのレコードを順次取得する。
  4. SheetAPIを利用して、上記3で取得したレコードをスプレッドシートに順次書き込む。
  5. 最後にスプレッドシートのURLを事業チームに連携するだけで、データの連携は完了。

上記のフローのメリットは以下のとおりです。

  • GCPへの接続のみで、本番環境セグメントを意識することなく、データをGoogleDriveに出力できる。
  • 上記1のクエリ実行による課金コスト、およびAPI実行によるデータ取得等のネットワーク通信による課金コストのみ意識すればよい。
    • スプレッドシートへの保存については、現時点ではGoogleDriveの利用容量を消費しませんが、Googleのストレージポリシー変更により、2021/06/01 以降はスプレッドシートの保存にもGoogleDriveの容量を消費するようになります。
  • プログラムで全て解決できるので、手作業の手間がかからない。
  • 本プログラムを実行する環境をGCP内に配置すれば、通信はGCP内で完結するので、ローカルのインターネット速度制約に縛れれない

一方で、以降の実装例を見れば分かると思いますが、下記のようなデメリットもあるので、この点はご了承ください。

また、以下の実装例はPython3.7+になります。

実装詳細

利用するライブラリのインストール

まずは予め利用するライブラリをインストールしてください。 なお、以下ではpipを利用していますが、仮想環境を利用する場合は、適宜ツールに合ったコマンドに置き換えてください。

$ pip install google-cloud-bigquery
$ pip install google-api-python-client
$ pip install gspread

今回、スプレッドシートを操作するにあたり、gspreadを利用しています。 Googleが提供するSheetAPIを直接利用してもよいのですが、こちらは柔軟な操作ができる分、API仕様が複雑です。 今回のようにスプレッドシートを作成して、シートにデータを記入するだけなら、gspreadで簡単に実装できます。

gspread.readthedocs.io

また、GCPのプロジェクト設定にて、スプレッドシートAPIを有効にしてください。

BigQuery APIを利用して取得対象のテーブル情報を取得

まずはBigQueryにアクセスするためのclientを作成のうえ、テーブル情報を取得します。 この時点ではテーブル情報までで、実レコードは取得していません。

import google.auth
from google.cloud import bigquery

table_name: str = ... # 取得対象のテーブル名をデータセット名含めて指定する

credentials, project_id = google.auth.default()
bq_client: bigquery.Client = bigquery.Client(project=project_id, credentials=credentials)
bq_table: bigquery.Table = bq_client.get_table(table_name)

clientを作成するにあたり、ハマりがちになるのがクレデンシャルの取得でしょう。 環境によってユーザアカウントやサービスアカウントを直接利用していたり、 もしくは、Application Default Credential を利用していたりなど様々かと思います。 私が観測している範囲のみになりますが、 google.auth.default() により大方問題なくクレデンシャルを取得できるかと思います。 詳細はこちらをご参照ください。 (今回、エンドユーザ認証は想定しておりません!)

エクスポート先のスプレッドシートを作成

では次にgspreadを利用して、スプレッドシートを作成します。 以下のコードではエクスポート先のシートの作成も合わせて行っています。

import gspread
from gspread.models import Spreadsheet, Worksheet

file_name: str = ... # エクスポート先となるスプレッドシートのファイル名

credentials, _ = google.auth.default(
    scopes=[
        'https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive'
    ]
)
gspread_client: gspread.client.Client = gspread.authorize(credentials)

# スプレッドシートを作成する
spread_file: Spreadsheet = gspread_client.create(file_name)
default_sheet: Worksheet = spread_file.get_worksheet(0)

# レコード出力先のシートを作成し、スプレッドシート作成時にデフォルトで作成されるシートを削除する
to_sheet: Worksheet = spread_file.add_worksheet(
    title=bq_table.table_id, rows=(bq_table.num_rows + 1), cols=len(bq_table.schema)
)
spread_file.del_worksheet(default_sheet)

gspreadのクライアントを作成するにあたり、bigqueryのクライアントと同様クレデンシャルが必要になります。 ここでは再度、新規にクレデンシャルを取得していますが、bigquery.Client作成時のクレデンシャルを流用しても問題ないと思います。 ただし、scopesに https://spreadsheets.google.com/feeds, https://www.googleapis.com/auth/drive の指定を 忘れないようにしてください。

次に、スプレッドシート作成後、エクスポート先となるシートを作成しているのですが、

to_sheet: Worksheet = spread_file.add_worksheet(
    title=bq_table.table_id, rows=(bq_table.num_rows + 1), cols=len(bq_table.schema)
)

シート名はbqテーブルのテーブル名(bq_table.table_id)とし、 さらにエクスポート元のテーブルのレコード数とカラム数に合わせて、シートの行数と列数を決めています。 カラム名の行を初めに入れるため、rowsパラメータに指定する値を「レコード数 + 1」としています。 (もちろん、シート名はご自由に指定しても問題ありません)

なお、スプレッドシートの制約上、1ファイルにつき最大500万セルまでしか作成できません。 このため、「(レコード数 + 1) * カラム数」が500万以上になるとエラーが発生します。 また、スプレッドシートを作成した際、初期シートとして「1000行 * 27列(AからZまで) = 2万7千セル」が作られているため、 シート追加時には、この初期シートのセル数を含めて500万セルを越えないよう、注意する必要があります。

スプレッドシートを特定のGoogleDrive上に移動

実は上記の処理でスプレッドシートが作成される場所は、 利用したクレデンシャルに紐づくアカウントのGoogleDriveホーム直下になります。 このままだと、ファイルの共有が難しいので、 「ファイルを共有する」もしくは「共有状態に予め設定している場所に移動する」必要があります。 今回は後者の方針をとります。

import googleapiclient.discovery as discovery

gdrive_service: discovery.Resource = discovery.build('drive', 'v3', cache_discovery=False)
gdrive_files: discovery.Resource = gdrive_service.files()

spreadsheet_file: dict = gdrive_files.get(fileId=spread_file.id, fields='parents').execute()
previous_parents: str = ",".join(spreadsheet_file.get('parents'))

directory_id: str = ... # 移動先となるGoogleDriveのディレクトリID

# 親を変更することで、ファイル移動を実現する
gdrive_files.update(
    fileId=spread_file.id, fields='id, parents',
    addParents=directory_id, removeParents=previous_parents
).execute()

ここで、directory_id は移動先となるGoogleDriveのディレクトリIDとなります。 ディレクトリIDには、URLの https://drive.google.com/drive/folders/ 以降の文字列を指定してください。 ファイルを移動するにあたり、移動先のディレクトリに対して、 クレデンシャルが紐づくアカウントに編集権限が予め付与されている必要があります。

シートの1行目にカラム名を記載する

テーブルをレコードを出力する前に、各カラム名を先に出力します。 これは既に取得しているテーブル情報から対応できます。

from typing import Dict, List
from gspread.models import Cell

# カラム名->列番号 の対応関係を作成する
column_mapping: Dict[str, int] = {}
column: int = 1
for field in bq_table.schema:
    if field.mode.upper() == "REPEATED":
        raise Exception()
    if len(field.fields) > 0:
        raise Exception()
    column_mapping[field.name] = column
    column = column + 1

# スプレッドシートの1行目に各列のタイトルを追加
sheet_header: List[Cell] = [
    Cell(row=1, col=column_number, value=header_name)
    for (header_name, column_number) in column_mapping.items()
]
to_sheet.update_cells(sheet_header)
to_sheet.freeze(rows=1)

ここで、カラムが配列もしくは構造体の場合は例外を発行しています。 配列および構造体が絡むと出力時の形式が煩雑になるので、今回は対象外にしています(処理としては例外が投げられることでプログラムが中断されます)。 また、ここで作成した column_mapping は最後のエクスポート処理でも利用しています。

bqテーブルよりレコードを取得してスプレッドシートに出力

前置きが長くなりましたが、実レコードをbqテーブルより取得して、シートに書き込むのは以下のようになります。

row_iterator: bigquery.table.RowIterator = bq_client.list_rows(
    bq_table, start_index=0, max_results=bq_table.num_rows
)

row_number: int = 1
sheet_cells: List[Cell] = []
for record in row_iterator:
    row_number += 1
    for target_key, target_value in record.item():
        column_number: int = column_mapping[target_key]
        sheet_cells.append(
            Cell(row=row_number, col=column_number, value=target_value)
        )

to_sheet.update_cells(sheet_cells)

list_rows()bq head コマンドに該当するもので、このAPIでレコードを取得する分にはクエリコストはかかりません。

スプレッドシートに書き込む際は、 Cell オブジェクトに行番号と列番号、およびセルの値を指定した上で、 Worksheet.update_cells() の引数に指定するリストに渡します。 このAPIの呼び出しは、たとえ1セルだけであっても1秒程度時間かかるので、 更新するセルをある程度の件数まとめた上で呼び出すようにするとよいでしょう。

これで、bqテーブルのレコードをスプレッドシートにエクスポートするプログラムが作成できました。

その他、気をつけること

既に記載したことも含まれますが、実運用時は以下の点を考慮する必要があります。

  • スプレッドシートは最大500万セルまでしか作成できません。500万セルを超過するような場合は、bqテーブルのレコード数を絞る、もしくは、スプレッドシートの出力先を複数ファイルに分割する必要があります。
  • 1セルあたり最大文字列長は5万文字となりますので、超過しないよう制御する必要があります。
  • SheetAPIの流量制限はかなり厳しく設定されています。スプレッドシート書き込み時にエラーコード429が返ってきたらAPIリソース超過になります。しばらく待ってから処理を再開するよう、制御する必要があります。(半年ほど前はリソース超過時に500エラーが返却されることもありましたが、最近は安定しているように思えます)
  • 上記の実装例ではbqテーブルのレコードを全て取得してからスプレッドシートに書き込んでいますが、レコード数が多いとメモリが足りなくなる可能性があります。適宜、ページングしながらスプレッドシートに書き込むようにしてください。

データの連携というのは単純作業で地味な上、データサイズが大きいと時間がかかったりで、チリツモなストレス要因だったりします。 今回紹介したコードがみなさまのデータ連携の一助になれば幸いです。

最後になりますが、アスタミューゼでは、引き続きエンジニアを積極募集中です。是非、下記バナーよりお尋ねください。

Copyright © astamuse company, ltd. all rights reserved.