SENSY Product Dev Tech Blog

SENSYプロダクト開発チームのTechBlogです。

VS CodeでGCEインスタンスの実験環境構築+Jupyter Notebookを使う方法

SENSYデータサイエンティストチーム井上です。

今回もVS Codeを用いた業務効率化関連の記事になります。

背景&目的

前回

補足事項

  • 今回は再現性を取るために、python:3.10-slimのイメージを使用して環境を揃えておきます
    • そのため、Dockerの知識があるとわかりやすいです。
      • Dockerの記事は他の方々が素晴らしい記事を書いているので、そちらを確認頂けたらと思います。

VS Codeで追加が必要な拡張機能

手順

1. インスタンス上でDocker初期環境構築

⁠コマンド

sudo apt update
sudo apt install -y docker.io
sudo systemctl start docker
sudo systemctl enable docker
sudo apt install docker-compose
sudo usermod -aG docker $(whoami)
sudo reboot

2. .devcontainer等必要なディレクトリ&ファイルの作成

ディレクトリ構造

sample-vscode-dev-env <- 自分で作成したプロジェクト(ディレクトリ)
├── .devcontainer
│   ├── Dockerfile
│   ├── devcontainer.json
│   └── docker-compose.yml
└── requirements.txt

Dockerfile

# Python 3.10 base image.
FROM python:3.10-slim

# 必要なパッケージをインストール
RUN apt-get update && apt-get install -y \
    build-essential \
    bash-completion \
    coreutils \
    tree \
    && rm -rf /var/lib/apt/lists/*

# 作業ディレクトリを設定
WORKDIR /workspace

# requirements.txtをコンテナ内にコピー
COPY requirements.txt .

# 依存パッケージをインストール
RUN pip install --upgrade pip
RUN pip install -r requirements.txt

# lsコマンドで色を有効化
RUN echo "alias ls='ls --color=auto'" >> /etc/bash.bashrc

# Jupyter用のポートを開放
EXPOSE 8888

devcontainer.json

{
    "name": "Sample VScode Development Environment",
    "dockerComposeFile": ["docker-compose.yml"],
    "service": "app",
    "workspaceFolder": "/workspace",
    "settings": {
      "python.pythonPath": "/usr/local/bin/python"
    },
    "extensions": [
      "ms-python.python",
      "ms-toolsai.jupyter",
      "ms-python.vscode-pylance",
      "ms-vscode-remote.remote-containers"
    ]
}
  • 開発コンテナの設定を記述するメインのファイル
    • name: コンテナの名前
    • dockerComposeFile :開発環境に使用するDocker Composeファイルを指定
    • service :開発環境としてアタッチする(接続する)サービス名
      • Docker Compose内で定義されたサービス名にVS Codeが接続
    • workspaceFolder :コンテナ内でのプロジェクトディレクトリのパスを指定
    • settingsVS Code内で使用するPythonの実行パスを指定
    • extensions: コンテナ内で利用するVS Code拡張機能

docker-compose.yml

version: '3'

services:
  app:
    build:
      context: ..
      dockerfile: .devcontainer/Dockerfile
    volumes:
      - ..:/workspace:cached
    ports:
      - "8888:8888"
    tty: true
  • version :Docker Composeファイルのバージョンを指定
  • services :コンテナとして動かすアプリケーションやサービスを定義
  • app :サービスの名前
  • build:Dockerイメージをビルドするための設定を指定
  • context: .. :親ディレクトリ(..)をコンテキストとして指定
    • Dockerビルド時に、このディレクトリ以下のファイルがコンテナのビルドコンテキストとして利用
  • dockerfile: .devcontainer/Dockerfile.devcontainer/Dockerfile を指定
  • volumes :- ..:/workspace:cached
  • ports"8888:8888"
    • ホストのポート8888をコンテナのポート8888にマッピング
  • ttytrue
    • 仮想端末の有効化

requirements.txt

numpy
pandas
jupyter
notebook
ipykernel

3. Dockerをビルドする

1. プロジェクトのディレクトリを開く

  • コマンドパレット(cmd + shift + P)を開き、File: Open Folder...を選択する

  • プロジェクトのパスを指定し、OKを選択する

2. ビルドの実行

  • コマンドパレット(cmd + shift + P)を開き、Dev Containers: Rebuild and Reopen in Containerを選択する

3. ビルドの完了

  • VS Codeの左下のステータスバーが、こんな感じになっていればビルド成功!!

4. Jupyter Notebookの動作確認

1. 空のノートブックを作成してみる

.
├── .devcontainer
│   ├── Dockerfile
│   ├── devcontainer.json
│   └── docker-compose.yml
├── notebook
│   └── sample.ipynb <- 例
└── requirements.txt

2. VS codeでノートブックを開き、Kernelを選択

1.作業したいノートブックを開いて、下図のようにノートブックの右上にある『Select Kernel』を選択する

2.『Python Environments...』を選択する

3.dockerで構築時に指定したPythonのバージョンのPythonがあるはずなのでそれを選択する

4.上記3.で指定したPythonのバージョンが表示される

5.以下のようにコードが実行されれば、動作完了!!

⁠補足事項

一度作成したコンテナへ再度入る方法

  • Dev Containers: Reopen in Containerを選択すれば、コンテナに入れる!!

ホップフィールド型ニューラルネットワーク

はじめに

Chief Research Officer (CRO) の岡本です。

2024年のノーベル物理学賞は、「人工ニューラルネットワークを用いた機械学習を可能にする基礎的な発見と発明」に対して、ホップフィールド先生とヒントン先生に贈られました[1]。

ヒントン先生の業績は、誤差逆伝播法、畳込みニューラルネットワーク、深層学習等で大変よく知られたものであると思いますが、ホップフィールド先生の業績、とくにホップフィールド型ニューラルネットワークについては、あまり知られていないかもしれません。

本稿では、ホップフィールド型ニューラルネットワークについて紹介していきたいと思います。

階層型ニューラルネットワークと相互結合型ニューラルネットワーク

ニューラルネットワークには、大きく分けて2つの種類があります(と少なくとも第2次AIブームの頃には言われてました)。

1つは階層型ニューラルネットワーク、もう1つは相互結合型ニューラルネットワークと呼ばれるものです。

階層型ニューラルネットワークは、入力層、隠れ層、出力層という階層型構造を持つニューラルネットワークで、識別・分類・回帰、すなわち関数近似を行うためのニューラルネットワークです。 MLP、CNN、RNN*1、Transformerなど、現在の深層学習で主流となっているニューラルネットワークはこちらに属します。

相互結合型ニューラルネットワークは、各ニューロンが相互に結合している構造を持つニューラルネットワークで、最適化*2、連想記憶*3を行うためのニューラルネットワークです。 ホップフィールド先生が提案したホップフィールド型ニューラルネットワークは、相互結合型ニューラルネットワークに属します。

ホップフィールド型ニューラルネットワーク

ホップフィールド(相互結合)型ニューラルネットワークの具体的な動作モデルについて、文献[2]の説明に沿って説明します。

ホップフィールド型ニューラルネットワークでは、2次関数で表現されるエネルギー関数

 
\displaystyle \qquad E(\boldsymbol x) = \dfrac{1}{2} \sum_{n = 1}^N \sum_{m = 1}^N w_{nm} x_n x_m + \sum_{n = 1}^N b_n x_n 
(1)

の組合せ最適化問題

 
\displaystyle \qquad \underset{\boldsymbol x}{\text{minimize}}\ E(\boldsymbol x) \\
\displaystyle \qquad \text{subject to}\ x_n \in \{-1, 1\} ,\ n = 1, \dots, N
(2)

を解いています。

組合せ最適化問題(2)の解法を考えます。

まず、多変数2次関数が描く放物線に対して、図1に示すように、任意の2点 {\boldsymbol x}^P,  {\boldsymbol x}^Qの中点 {\boldsymbol x}^Mに対して、


\displaystyle \qquad E(\boldsymbol x^Q) - E(\boldsymbol x^P) = \nabla E(\boldsymbol x^M) (\boldsymbol x^Q - \boldsymbol x^P) \\
\displaystyle \qquad \hphantom{E(\boldsymbol x^Q) - E(\boldsymbol x^P)} = \sum_{n = 1}^N \dfrac{\partial E(\boldsymbol x^M)}{\partial x_n}(x_n^Q - x_n^P)
(3)

が成り立ちます。

図1


つぎに、 {\boldsymbol x}について、他の変数は固定し、ある特定の変数 x_kのみを動かす場合を考えます。  {\boldsymbol x}^P,  {\boldsymbol x}^Qを、それぞれ

 
\displaystyle \qquad {\boldsymbol x}^P = (x_1, \dots, x_{k-1}, -1, x_{k + 1}, \dots, x_N)^{\top} \\
\displaystyle \qquad {\boldsymbol x}^Q = (x_1, \dots, x_{k-1}, 1, x_{k + 1}, \dots, x_N)^{\top}

とすると、 式(3)から、


\displaystyle \qquad E(\boldsymbol x^Q) - E(\boldsymbol x^P) = 2 \left[\dfrac{\partial E(\boldsymbol x)}{\partial x_k}\right]_{x_k = 0} \\
\displaystyle \qquad \dfrac{E(\boldsymbol x^Q) - E(\boldsymbol x^P)}{2} = \sum_{m = 1, m \neq k}^N w_{km} x_m + b_k

が得られます。 この式から、

 
\displaystyle \qquad u_k = - \sum_{m = 1, m \neq k}^N w_{km} x_m
(4)

として、 u_k \gt b_kであれば、 E({\boldsymbol x}^Q) \lt E({\boldsymbol x}^P)であり、 u_k \lt b_kであれば、 E({\boldsymbol x}^Q) \gt E({\boldsymbol x}^P)であることがわかります。

したがって、ステップ tにおける {\boldsymbol x}(t)について、


\displaystyle \qquad x_k(t + 1) = \begin{cases} 1 & (u_k(t) > b_k) \\ -1 & (u_k(t) < b_k) \end{cases} \\
\displaystyle \qquad x_n(t + 1) = x_n(t),\ n = 1, \dots, N,\ n \neq k
(5)

というふうに {\boldsymbol x}(t)を更新し、ステップ tごとに状態遷移する成分 kを順次変更しながら状態遷移を繰り返せば、 E({\boldsymbol x})を減少させていくことができます。 ここで、式(5)は、符号関数sgn*4を用いて、


\displaystyle \qquad x_k(t + 1) = \text{sgn} \left( u_k(t) - b_k \right) \\
\displaystyle \qquad x_n(t + 1) = x_n(t),\ n = 1, \dots, N,\ n \neq k
(6)

と表現することができます。

動作モデル(6)を可視化すると、図2のようになります。 図2から、この動作モデルは、 -w_{nm},  n =1, \dots, N,  m= 1, \dots, Nが結合重み、 -b_n, n = 1, \dots, Nがバイアス、活性化関数が符号関数である相互結合型のニューラルネットワークとみなすことができます。

図2

ホップフィールド型ニューラルネットワークを用いた連想記憶

連想記憶とは、ニューラルネットワークにあらかじめ何らかの情報をパターンとして記憶しておき、新たなパターンをニューラルネットワークに入力すると、これに近いパターンを想起し出力するというタスクです。

一例として、6×6ビットで表現された「0」という数字のパターンを想起するタスクを示します。 まず、図3のIdeal Patternに示すようなパターンをニューラルネットワークに記憶させます。 つぎに、図3のNoisy Inputに示すようなパターン(Ideal Patternから1ビットだけ反転)を入力し、ニューラルネットワークを動作させると、記憶しておいたIdeal Patternを出力するようなタスクが連想記憶です。

図3

パターンの入力は、白を -1、黒を 1とし、各ピクセルの値を並べたベクトルデータで与えます。 図3のIdeal Patternであれば、下記の6×6の数字の列を36次元ベクトルとして入力します。

-1-111-1-1
-11-1-11-1
-11-1-11-1
-11-1-11-1
-11-1-11-1
-1-111-1-1


Hebb則[4]を用いたホップフィールド型ニューラルネットワークを用いた連想記憶では、記憶したいパターンが {\boldsymbol y}^p = (y^p_1, \dots, y^p_N)^{\top},  p = 1, \dots, Pで与えられるとき、式(1)の E({\boldsymbol x})の重み w_{nm},  n =1, \dots, N,  m= 1, \dots, N、バイアス b_n, n = 1, \dots, N


\displaystyle \qquad w_{nm} = \begin{cases} 0 & (n = m) \\ -\dfrac{1}{N} \displaystyle \sum_{p = 1}^P y_n^p y_m^p & (n \neq m) \end{cases},\ n = 1, \dots, N,\ m = 1, \dots, N \\
\displaystyle \qquad b_n = 0,\ n = 1, \dots, N
(7)

と与えます。

重みとバイアスを式(7)のように与えたホップフィールド型ニューラルネットワークによって連想記憶が実現できる理由について考えます。 簡単のため、記憶するパターンが1つ、すなわち P = 1の場合を考えます(以降の説明では上付き添字 pを省略します)。 重みとバイアスを式(7)のように与えた場合、目的関数 E(\boldsymbol x)は、


\displaystyle \qquad E(\boldsymbol x) = - \dfrac{1}{2 N} \sum_{n = 1}^N \sum_{m = 1, m \neq n}^N y_n y_m x_n x_m
(8)

となります。 式(8)から、 y_n y_m x_n x_mが、すべての n = 1, \dots, N,  m = 1, \dots, Nについて1になるときに、 E(\boldsymbol x)が最小(極小)になることがわかります。 ここから、すべての n = 1, \dots, Nについて x_n = y_nとなる場合、すなわち {\boldsymbol x}が記憶したパターン {\boldsymbol y}に一致したときに E({\boldsymbol x})が最小(極小)になり、想起すべきパターンが E({\boldsymbol x})の(局所的)最適解になっていることがわかります。 ホップフィールド型ニューラルネットワークは、 E(\boldsymbol x)を減少させるように動作するモデルですので、これによって(局所的)最適解を得ることで記憶したパターンを想起できることがわかります。

つぎに、 {\boldsymbol x} = {\boldsymbol y}で、ホップフィールド型ニューラルネットワークの更新が止まることについて示します。 式(4)から、 {\boldsymbol x} = {\boldsymbol y}のとき、

 
\displaystyle \qquad u_k = \dfrac{1}{N} \sum_{m = 1, m \neq k}^N y_k y_m y_m

であるので、 y_m y_m = 1であることに注意すると、

 
\displaystyle \qquad u_k = \dfrac{N - 1}{N} y_k

となり、 u_kの符号は y_kの符号と同じになります。 したがって、 b_k = 0であることを考慮すれば、ホップフィールド型ニューラルネットワークの動作モデル(6)において、 x_k(t + 1) = y_kとなるので、 {\boldsymbol x} = {\boldsymbol y}で更新が止まることがわかります。

以上から、ホップフィールド型ニューラルネットワークを用いることで、連想記憶が実現できることがわかります。

ホップフィールド型ニューラルネットワークを用いた連想記憶の実装

Jupyter Notebookによるホップフィールド型ニューラルネットワークを用いた連想記憶の実装例を示します。 この例では、6×6の0, 1, 2のパターンを記憶しています。

なお、この実装例は、ChatGPTに「0-9からの数字を記憶するホップフィールドニューラルネットワークPythonでの実装例を教えて下さい」「設定した数だけランダムにパターンを反転させる方法を教えて下さい」等の質問をして生成されたコードを加工して作っています(簡単です)。 chatgpt.com

ホップフィールド型ニューラルネットワークを用いた最適化

ホップフィールド型ニューラルネットワークは、組合せ最適化問題(2)を解くニューラルネットワークであり、 E(\boldsymbol x)の設定次第で様々な問題を解くことができます。

たとえば、巡回セールスマン問題[3]もホップフィールド型ニューラルネットワークで解くことができます。  {\boldsymbol x}の定義を

 
\displaystyle \qquad x_{ij} = \begin{cases} 1 & \left( \text{都市} i \text{を} j \text{番目に訪問} \right) \\ 0 & \left( \text{上記でないとき} \right) \end{cases},\ i = 1 \dots, N,\ j = 1, \dots, N

とし、都市 iと都市 jの距離を d_{ij}とすると、巡回セールスマン問題は、

 
\displaystyle \qquad \underset{\boldsymbol x}{\text{minimize}}\ D(\boldsymbol x) \\
\displaystyle \qquad \text{subject to}\ \sum_{k = 1}^N x_{ik} = 1,\ i = 1, \dots, N \\
\displaystyle \qquad \phantom{\text{subject to}}\ \sum_{i = 1}^N x_{ik} = 1,\ k = 1, \dots, N \\
\displaystyle \qquad \phantom{\text{subject to}}\ x_{ij} \in \{0, 1\},\ i = 1, \dots, N,\ j = 1, \dots, N \\
\displaystyle \qquad \text{where}\ D(\boldsymbol x) = \dfrac{1}{2} \sum_{i = 1}^N \sum_{j = 1}^N \sum_{k = 1}^N d_{ij} x_{ik} \left( x_{j(k - 1)} + x_{j (k + 1)} \right)
(9)

と定式化されます。  D({\boldsymbol x})は、都市 i k番目に訪問する、すなわち x_{ik} = 1であり、都市 i, jを連続して訪れる、すなわち x_{j(k + 1)} = 1、ないしは x_{j(k - 1)} = 1のときに距離 d_{ij}を加算することで、巡回路の総距離を表しています。 また、最適化問題(9)の1番目と2番目の制約条件は、 {\boldsymbol x}が巡回条件(各都市に割り当てられる訪問順は1つで、各訪問順に割り当てられる都市は1つという条件)を満たしていることを表しています。 最適化問題(9)を解くために、1番目と2番目の制約条件をペナルティ関数法*5を用いて無制約化すると、

 
\displaystyle \qquad \underset{\boldsymbol x}{\text{minimize}}\ D(\boldsymbol x) + r \sum_{i = 1}^N \left(\sum_{k = 1}^N x_{ik} - 1 \right)^2 + r \sum_{k = 1}^N \left(\sum_{i = 1}^N x_{ik} - 1 \right)^2  \\
\displaystyle \qquad \text{subject to}\ x_{ij} \in \{0, 1\},\ i = 1, \dots, N,\ j = 1, \dots, N \\
\displaystyle \qquad \text{where}\ r > 0
(10)

となります。 ここで、 rはペナルティ係数で、制約条件の違反が発生しないように、通常は大きな正数に設定されます。

詳しい導出は文献[2]を参照してほしいのですが、組合せ最適化問題(10)に対して、ホップフィールド型ニューラルネットワークによる解法を適用することができ*6、式(6)で与えられるようなモデルを導出することができます。

他にも、4色塗り分け問題、Nクイーン問題にも応用が可能です[2]。


ここまでは、ファクトに基づいた記事であり、普遍的かつ揺るがない内容ですが、以降は、著者および一般的な評価が入りますので、その点注意してください。


ホップフィールド型ニューラルネットワークの限界と価値

少し実験してみるとすぐわかるのですが、ホップフィールド型ニューラルネットワークによる連想記憶は、記憶させるパターンを少し増やすだけで、すぐにうまく動作しなくなります。 これは、意図していないパターンを記憶してしまう偽記憶の問題と、局所的最適解へのトラップに起因しています[5]。 また、巡回セールスマン問題の解法としても、局所的最適解が大量に存在し、本質的に局所的探索能力しか有しないことに起因して、他の解法と比較して優れた解法とは言えません。

これらの問題を解決するために、確率的状態遷移(ボルツマンマシン[6])の導入やカオス的なダイナミクスの導入などが行われているものの、現在では、主流と言えるアルゴリズムではありません。

たとえば、連想記憶(に近いこと)を実現するなら、深層学習を用いて大量のパターンを学習させて、その補間能力に頼ったアルゴリズムを作ったほうがうまくいくでしょうし(今や演習課題レベルと言っていい、CNNによるmnist手書き文字認識を考えてみてください)、組合せ最適化手法ならば、進化計算、メタヒューリスティクスをはじめとして、優れた解法が他に多数提案されています。

では、ホップフィールド型ニューラルネットワークの価値はどこにあるのでしょうか。

それは、第2次AIブームの初頭である1980年の前半に、ニューラルネットワークを使って連想記憶や最適化ができることを「やってみせた」点にあると考えます。 もっと言えば、このアルゴリズム自体の能力はそこまで大きくないものの、ニューラルネットワークが持つポテンシャル・可能性を世の中に示した点にあると考えます。 また、著者自身はその当時の注目度合いを知っているわけではありませんが、文献[2]の取りまとめ役であり、当時(今もですが)、一線で、最適化、ニューラルネットワークの研究を行っていた著者の師匠に聞いた限りでは、ホップフィールド型ニューラルネットワークが発表された当時の注目度はかなりのものがあり、それによって、ニューラルネットワークや最適化の研究が活性化されたことは、否定できない事実だそうです。 ノーベル賞公式の解説[7]でも、ホップフィールド型ニューラルネットワークはあくまでも基礎的な貢献であると評価していて、ボルツマンマシンを含むその後継と階層型ニューラルネットワークを含む人工ニューラルネットワークの研究によって、様々な問題が解決され、今日の深層学習につながっていると言う評価をしています(裏を返して言えば、まだ、AI・深層学習に対するノーベル賞贈呈の可能性を残しているとも言えそうです)。

参考文献

[1] The Nobel Prize in Physics 2024 - NobelPrize.org
[2] 電気学会 GA等組合せ最適化手法応用調査専門委員会 編:「遺伝アルゴリズムニューラルネット — スケジューリングと組合せ最適化」、コロナ社(1998)
[3] ja.wikipedia.org
[4] en.wikipedia.org
[5] www.jstage.jst.go.jp
[6] en.wikipedia.org
[7] The Nobel Prize in Physics 2024 - Scientific background - NobelPrize.org

*1:RNNのリカレント構造は、ある意味で相互結合型とみなすことも可能ですが、入力層と出力層を有している点において、階層型ニューラルネットワークとみなすべきであると著者は考えますし、こちらの解釈の方が一般的だと思います。

*2:最適な解を探索する計算

*3:入力されたパターンからあらかじめ記憶したパターンを想起する計算

*4:引数が正なら 1、負ならば -1を返す関数

*5:制約条件を満たすときに0、違反する場合に違反量に応じて罰(ペナルティ)を与えることで、制約条件の満足化を目的関数に組み込む方法

*6:本稿では、 {\boldsymbol x}の定義空間を -1 1で定義した場合のホップフィールド型ニューラルネットワークについて説明しましたが、 0 1の場合でもほぼ同様の説明ができます。

データ分析のためのGAS入門5(スプレッドシート上のデータをjsonに変換してDLするGAS)

データサイエンス部の荻です.

はじめに

データサイエンス部で隔週の社内勉強会を行なっているのでその共有です。

背景

みなさんは普段スプレッドシート使っていますか?
今回は、仕事の能率を上げるためにこの企画をやってみました。
ちょっとした小技でチームの能率アップを図る企画です。

一般的な課題

スプレッドシートで以下のような問題に直面していませんか?

そこで、スプレッドシートのデータを簡単にJSONファイルとしてエクスポートできる機能を自作しました。
この記事では、その実装方法と使用方法をご紹介します。

実装方法

Google Apps Script(GAS)の活用

この機能を実現するために、Google Apps Script(GAS)を使用しました。GASは、Googleのサービスと連携してカスタム機能を追加できる強力なツールです。

ソースコード

実装には主に2つのファイルを使用しています:

  1. GASのスクリプトファイル(.gs
  2. HTMLファイル(ダウンロードダイアログ用)

GASスクリプト

function onOpen() {
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('カスタムメニュー', [
      { name: 'JSONで出力', functionName: 'showJsonDownloadDialog' }
    ]);
}

function showJsonDownloadDialog() {
  const html = HtmlService.createHtmlOutputFromFile('downloadDialog')
      .setWidth(300)
      .setHeight(200);
  SpreadsheetApp.getUi().showModalDialog(html, 'JSONファイルダウンロード');
}

function fetchSheetData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
  const rows = sheet.getDataRange().getValues();
  const keys = rows[0];
  const jsonData = rows.slice(1).map(function(row) {
    const obj = {};
    row.forEach(function(item, index) {
      obj[keys[index]] = item;
    });
    return obj;
  });
  return JSON.stringify(jsonData, null, 2);
}

HTMLファイル(downloadDialog.html)

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <h3>JSONファイルをダウンロード</h3>
  <button id="downloadBtn">ダウンロード</button>

  <script>
    document.getElementById('downloadBtn').onclick = function() {
      google.script.run.withSuccessHandler(function(content) {
        const blob = new Blob([content], { type: 'application/json' });
        const url = URL.createObjectURL(blob);
        const a = document.createElement('a');
        a.href = url;
        a.download = 'data.json';
        document.body.appendChild(a);
        a.click();
        document.body.removeChild(a);
      }).fetchSheetData();
    };
  </script>
</body>
</html>

使用方法

製品ID 製品名 担当者 開発開始日 進捗状況 予算 完了予定日
P001 スマートウォッチX 鈴木 健太 2023/01/15 0.8 5000000 2023/12/31
P002 AI家電アシスタント 田中 美香 2023/03/01 0.6 8000000 2024/02/28
... ... ... ... ... ... ...
  1. スプレッドシートのメニューから「カスタムメニュー」→「JSONで出力」を選択します。

  2. 表示されたダイアログで「ダウンロード」ボタンをクリックします。

  3. JSONファイルがダウンロードされます。

出力されるJSONデータの例

[
  {
    "製品ID": "P001",
    "製品名": "スマートウォッチX",
    "担当者": "鈴木 健太",
    "開発開始日": "2023-01-14T15:00:00.000Z",
    "進捗状況": 0.8,
    "予算": 5000000,
    "完了予定日": "2023-12-30T15:00:00.000Z"
  },
  {
    "製品ID": "P002",
    "製品名": "AI家電アシスタント",
    "担当者": "田中 美香",
    "開発開始日": "2023-02-28T15:00:00.000Z",
    "進捗状況": 0.6,
    "予算": 8000000,
    "完了予定日": "2024-02-27T15:00:00.000Z"
  },
  // ... 他のデータ
]

まとめ

この機能を使用することで、Google スプレッドシートのデータを簡単にJSONフォーマットで出力できます。これにより、スプレッドシートで管理しているデータを他のシステムやアプリケーションで活用する際の橋渡しが容易になります。

開発者の方々やデータ分析に携わる方々にとって、この機能が業務効率化の一助となれば幸いです。

ユーザ定義関数(UDF)の利用

はじめに

はじめまして。SENSY PDチームの加藤です。
弊社では主にBigQueryを用いてデータ分析やデータマートの作成を行っております。
そこで本記事ではユーザ定義関数(UDF)というツールについて実用例を混ぜながら、
概要や利用するメリットについてご紹介させていただければと思います。

ユーザー定義関数(UDF:User-Defined Function)とは?

BigQueryで標準SQLの機能を拡張するために用いられるカスタム関数です。
通常の関数(SUM、COUNT、AVGなど)では対応できない特殊なロジックや計算を
柔軟に実行することができ、また恒久的に保存され再利用可能となっています。

利用するメリット

1. 複雑なロジックの簡略化
- 標準SQLでは記述が複雑になる処理を、シンプルな関数として分離できます。
 これにより、コードの可読性とメンテナンス性が向上します。
2. 再利用性
- よく使う処理を関数化することで、同じロジックを複数のクエリで再利用できます。
3. 動的なカスタマイズ
- ユーザーの要件に合わせた特定のロジックやデータ変換を実装可能です。

実用例1

以前blogで紹介させていただいたランク付関数を作成して利用してみましょう
基本的にはテーブル作成時と同じように
{プロジェクト}.{データセット}の下に関数を作成します。

関数作成クエリ

CREATE OR REPLACE FUNCTION `sensy_pjt.sample_schema._RANK`(x FLOAT64) 
RETURNS INT64 
AS (
  CAST(
    FLOOR(x / POW(10, FLOOR(LOG10(GREATEST(x, 1))))) * 
    POW(10, FLOOR(LOG10(GREATEST(x, 1))))  
    AS INT64
  )
);


実行

WITH sensy AS (
SELECT
 x * rand()*100 AS x
FROM 
 UNNEST(generate_array(1, 10)) AS x
 )

SELECT
 x,
 `sensy_pjt.sample_schema._RANK`(x) AS ranked
FROM
 sensy
ORDER BY
 x 


結果

実用例2

多少複雑な計算と動的に変換できる例として
緯度経度から任意の地域メッシュコードに変換する関数を作成して利用してみましょう

関数作成クエリ

CREATE OR REPLACE FUNCTION `sensy_pjt.sample_schema.latlon_to_mesh`(
  lat FLOAT64,
  lon FLOAT64,
  level INT64
)
RETURNS STRING
AS (
  CASE 
    WHEN level = 2 THEN
      -- 2次メッシュの計算
      CONCAT(
        CAST(FLOOR(lat * 1.5) AS STRING),
        CAST(FLOOR(lon - 100) AS STRING)
      )
      
    WHEN level = 3 THEN
      -- 3次メッシュの計算
      CONCAT(
        CAST(FLOOR(lat * 1.5) AS STRING),
        CAST(FLOOR(lon - 100) AS STRING),
        CAST(FLOOR((lat * 1.5 - FLOOR(lat * 1.5)) * 8) AS STRING),
        CAST(FLOOR((lon - FLOOR(lon)) * 8) AS STRING)
      )
      
    WHEN level = 4 THEN
      -- 4次メッシュの計算
      CONCAT(
        CAST(FLOOR(lat * 1.5) AS STRING),
        CAST(FLOOR(lon - 100) AS STRING),
        CAST(FLOOR((lat * 1.5 - FLOOR(lat * 1.5)) * 8) AS STRING),
        CAST(FLOOR((lon - FLOOR(lon)) * 8) AS STRING),
        CAST(FLOOR(((lat * 1.5 * 8) - FLOOR(lat * 1.5 * 8)) * 10) AS STRING),
        CAST(FLOOR(((lon * 8) - FLOOR(lon * 8)) * 10) AS STRING)
      )
      
    ELSE 
      -- レベルが2, 3, 4以外の場合はNULLを返す
      NULL
  END
);


実行

WITH geo_data AS (
  SELECT 35.6895 AS lat, 139.6917 AS lon -- 東京
  UNION ALL
  SELECT 34.6937 AS lat, 135.5023 AS lon -- 大阪
  UNION ALL
  SELECT 43.0621 AS lat, 141.3544 AS lon -- 札幌
)

SELECT 
  lat, 
  lon, 
  `sensy_pjt.sample_schema.latlon_to_mesh`(lat, lon, 2) AS mesh_2,
  `sensy_pjt.sample_schema.latlon_to_mesh`(lat, lon, 3) AS mesh_3,
  `sensy_pjt.sample_schema.latlon_to_mesh`(lat, lon, 4) AS mesh_4
FROM geo_data;


結果

まとめ

ユーザー定義関数(UDF)は、柔軟なデータ処理を可能にする非常に便利な機能です。
また、本記事で紹介したものは一例にすぎませんので
ぜひプロジェクトに応じたUDFを活用してみてください!


おまけ:作成したUDFの確認方法と削除方法

コンソールやコマンドでも確認/削除は可能ですが、
今回はクエリで行う方法をご紹介します。

  • 確認
SELECT 
  routine_catalog AS project_name,
  routine_schema AS dataset_name,
  routine_name,
  routine_type,
  routine_definition
FROM 
  `sample_schema.INFORMATION_SCHEMA.ROUTINES`
WHERE 
  routine_type = 'FUNCTION'
  • 削除
DROP FUNCTION `sensy-pjt.sample_schema._RANK`;
DROP FUNCTION `sensy-pjt.sample_schema.latlon_to_mesh

BigQueryにおけるEXECUTE IMMEDIATEを使った動的なクエリ生成

はじめに

SENSYプロダクトチームの清水です。今回はBigQuery上で動的にクエリを生成し実行することのできるEXECUTE IMMEDIATE関数を紹介いたします。 常日頃から利用する機能ではないですが、特定の場面ではとても役に立つものです。

問題設定

BigQueryのpublicデータで公開されている4つのデータセットを例とします。

  • bigquery-public-data.crypto_bitcoin
  • bigquery-public-data.crypto_bitcoin_cash
  • bigquery-public-data.crypto_dash
  • bigquery-public-data.crypto_dogecoin

これらのパブリックデータには以下の特徴があります。

データ格納状態

全てのデータセットからblocksテーブル全てをまとめてクエリの対象にする場合、愚直にクエリを書くと以下のようなものになってしまいます。

CREATE TEMP TABLE union_all_table as (
  SELECT "bitcoin" as table_name, * FROM `bigquery-public-data.crypto_bitcoin.blocks` WHERE timestamp_month >= "2024-11-01"
  UNION ALL
  SELECT "bitcoin_cash" as table_name, * FROM `bigquery-public-data.crypto_bitcoin_cash.blocks` WHERE timestamp_month >= "2024-11-01"
  UNION ALL
  SELECT "dash" as table_name, * FROM `bigquery-public-data.crypto_dash.blocks` WHERE timestamp_month >= "2024-11-01"
  UNION ALL
  SELECT "dogecoin" as table_name, * FROM `bigquery-public-data.crypto_dogecoin.blocks` WHERE timestamp_month >= "2024-11-01"
);
SELECT * FROM union_all_table;

上記のクエリを見ると、BigQueryで複数のデータセットに跨って配置されたテーブルを扱う場合、以下のような課題があることが分かります。

  1. データセット名の列挙

    • 例えば、4つのデータセットなら問題ありませんが、数十個ともなると、全てのデータセット名を列挙するのは非効率です。
  2. 既存の解決策の限界

    • テーブル名の接尾辞には_TABLE_SUFFIXを使用できますが、それ以外の部分にはワイルドカードなどの適用が困難です。
    • Jinjaなどのテンプレートエンジンを使用すれば動的にクエリを生成できますが、BigQueryのコンソール画面内で完結させたい場合もあります。

これらの課題に対する効果的な解決策として、EXECUTE IMMEDIATEが挙げられます。この機能を使用することで、BigQueryのコンソール画面内で動的にクエリを生成し、実行することができます。これにより、多数のデータセットを効率的に扱うことが可能になります。

cloud.google.com

実際の使い方

上記のクエリと全く同じ内容を、動的に生成して実行するクエリが以下のクエリです。

DECLARE query STRING;
SET query = (
   SELECT 
    CONCAT(
      "CREATE TEMP TABLE  union_all_table as (",
      STRING_AGG(
        FORMAT(
          """
          SELECT '%s' as table_name, * 
          FROM `bigquery-public-data.crypto_%s.blocks` 
          WHERE timestamp_month >= '2024-11-01'
          """, name, name
        ),
        " UNION ALL "
      ), 
      ")"
    )
  FROM UNNEST(["bitcoin", "bitcoin_cash", "dash", "dogecoin"]) AS name
);

EXECUTE IMMEDIATE query;
SELECT * FROM  union_all_table

データセットがさらに増えたとしても、UNNESTの引数に記述している接尾辞の追加のみで対応できます。また、もし接尾辞が数字の際は、GENERATE_ARRAYを使うことでさらに効率的に記述することができます。このように、テーブルの接尾辞以外に対してワイルドカードを用いたくなるような場面では、とても便利な機能なのでおすすめです。

注意点

この機能を利用する際、クエリが動的に生成される関係でデータのスキャン量が表示されません。

重いクエリが実行されることにならないか、あらかじめ見積もりを立ててから実行する必要があります。

また対象となるデータセットが複数のリージョンに跨っている場合は、そもそも実行することができません。対象のデータセットが全て同じリージョンにある場合に限ります。

最後に

SENSY / プロダクト開発チームではSENSY CLOUDを一緒に開発するデータサイエンティスト・機械学習エンジニアを随時募集しています。

herp.careers

Composer/Airflowの乗り換え先を検討してみた

SENSYプロダクトチームねぎしです。

はじめに

弊社ではComposer/Airflowでワークフローを構築するのがデファクトスタンダードになっています。 一方で、そのサービス(GKEも含めて)を維持する金額的コストや、メンテナンスにかかる工数コスト、対応できる人材のアサインなど様々な問題が起き始めています。 そのため、将来的な乗り換えをみすえて、Airflowのようなワークフローの代替、および、それをデプロイするサーバーについて他のサービスやオープンソースについて調査・比較を行いました。

サービス別概要

1. Cloud Composer(Apache Airflow)

メリット:

  • フルマネージドサービス: インフラ管理やスケーリングを自動化し、運用負荷を軽減
  • 豊富なプラグインとコミュニティサポート: Airflowのエコシステムを活用でき、多種多様なオペレーターが利用可能
  • ワークフローの可視化: Web UIでDAG(有向非巡回グラフ)の状態やタスクの進行状況を直感的に監視可能
  • GCPサービスとの深い統合: BigQuery、Cloud DataflowなどのGCPサービスと容易に連携で可能

デメリット:

  • コストが高い: マネージドサービスのため、リソース使用量に応じたコストが継続的に発生
  • カスタマイズの制限: Airflowのバージョン固定や一部設定の変更が制限される場合がある
  • 学習コスト: Airflow特有の概念や構文を理解する必要があり、初心者にはハードルが高い場合がある

2. Cloud Workflows

メリット:

  • サーバーレス: インフラ管理が不要で、自動的にスケーリング
  • シンプルな定義: YAMLまたはJSONでワークフローを記述でき、直感的に理解しやすい
  • 低コスト: 実行時間に応じた課金モデルで、コスト効率が高い
  • 迅速な開発: シンプルなワークフローの場合、迅速に構築・デプロイが可能

デメリット:

  • 機能の制約: 複雑なワークフローや高度なエラー処理には向いていない
  • デバッグの難しさ: ロギングやデバッグ機能が限定的で、トラブルシューティングが難しい場合がある
  • コミュニティサポートの不足: 新しいサービスのため、情報や事例が少ない

3. Prefect

メリット:

  • 柔軟性: Pythonでワークフローを定義でき、高度なカスタマイズが可能
  • 現代的な設計: Airflowの課題を解決するために設計されており、直感的なAPIを提供してる
  • エラー処理と再試行: タスクの失敗時の挙動を詳細に制御可能
  • オープンソース: コミュニティエディションは無料で利用可能で、拡張性がある

デメリット:

  • マネージドサービスの欠如: GCP上でのネイティブなマネージドサービスがなく、自身でインフラを管理する必要がある
  • 学習曲線: 新しいツールであるため、習得に時間がかかる場合がある
  • エコシステムの成熟度: Airflowと比較すると、プラグインサードパーティ統合が少ない

4. Cloud Dataflow

メリット:

  • サーバーレスでフルマネージド: インフラ管理が不要で、自動スケーリング
  • 大規模データ処理に最適: バッチ処理とストリーミング処理の両方をサポートし、大量のデータを効率的に処理
  • Apache Beam SDKの利用: シングルAPIで複数の実行エンジンをサポートし、コードの再利用性が高い
  • GCPサービスとの統合: BigQuery、Cloud Storage、Pub/Subなどと容易に連携可能

デメリット:

  • 複雑な学習曲線: Apache Beamのプログラミングモデルを理解する必要があり、習得に時間がかかる
  • ワークフローオーケストレーション機能の欠如: タスクのスケジューリングや依存関係管理には向いておらず、別途オーケストレーションツールが必要
  • コスト管理の難しさ: 大規模なデータ処理ではコストが増加しやすく、最適化が必要

5. Cloud Run Jobs

メリット:

  • サーバーレスでフルマネージド: インフラ管理が不要で、自動的にスケーリング
  • コンテナ化されたバッチジョブの実行: 任意の言語やランタイムで作成したコンテナを実行可能
  • シンプルなデプロイ: コンテナイメージを指定するだけでジョブをデプロイ可能
  • コスト効率: 使用したリソースに対してのみ課金されるため、コストを最適化できる

デメリット:

  • オーケストレーション機能の不足: 複数のジョブ間の依存関係やスケジューリングはサポートされてない
  • ステートレスな実行: 各ジョブは独立しており、状態管理が必要な場合は別途対応が必要
  • デバッグの難しさ: ログは提供されますが、複雑なデバッグやトレースは難しい場合がある

メリット・デメリットまとめ

サービス名 メリット デメリット
Cloud Composer - フルマネージド
- Airflowのエコシステム活用
- 可視化機能
- 高コスト
- カスタマイズ制限
- 学習コスト
Cloud Workflows - サーバーレス
- シンプルな定義
- 低コスト
- 機能制約
- デバッグの難しさ
- サポート情報の不足
Prefect - 柔軟性
- 現代的な設計
- エラー処理
- オープンソース
- マネージドサービスなし
- 学習曲線
- エコシステムの成熟度
Cloud Dataflow - サーバーレス
- 大規模データ処理
- Apache Beam利用
- 学習曲線の高さ
- オーケストレーション機能不足
- コスト管理
Cloud Run Jobs - サーバーレス
- コンテナ化されたバッチジョブ
- コスト効率
- オーケストレーション機能不足
- ステートレス
- デバッグの難しさ

結論

社内で議論した結果、このような意見がでました。

  • Cloud Run Jobs
    • シンプルなバッチ実行ツールとしては良い
    • ただし、メモリに制限があるためモデル学習・予測系のタスクがCloud Run Jobs内で対応できない
  • Cloud Workflows
  • Cloud Dataflow (Apache Beam)
    • Dataflow上でdbtやLightGBMの実行が可能
    • Airflowと比べると実行履歴管理などの観点でUIUX面で劣る
  • Prefect
    • Airflow (Cloud Composer)の後継
    • バッチだけの利用としてであればCloud Run Jobsで走らせられるため費用削減可能
    • Airflowと同様の使い方だとComposer同様にサーバーコストがかかるがUIがアプデされた感じ

引き続き検討を進めたいと思います。

最後に

SENSY / プロダクト開発チームではSENSY CLOUDを一緒に開発するデータサイエンティスト・機械学習エンジニアを随時募集しています。

herp.careers

VS CodeでGCEインスタンスにRemoteアクセス

SENSYデータサイエンティストチーム井上です。

今回はVS Codeを用いた業務効率化関連の記事になります。

背景&目的

  1. リモート開発時にターミナルを使わずにVS Codeで全て完結したい
  2. 開発用インスタンスでは外部IPを固定していないので、起動毎に外部IPが変わってしまう。それにより、.ssh/configに登録している外部IPを毎回変えなければならない。そのため、Identity-Aware Proxy(IAP)を用いてこの問題を解決する。

事前準備

Identity-Aware Proxy(IAP)とは?

Identity-Aware Proxy(IAP)の有効化

  1. GCPでプロジェクトを選択
  2. ナビゲーションメニューから『IAMと管理』->『Identity-Aware Proxy』を選択
  3. APIを有効にする』を選択

必要なIAM

  • IAP で保護されたトンネル ユーザー

VS Codeで必要な拡張機能

手順

1. configの作成

コマンド(ターミナルやiTerm等で実行)

gcloud compute ssh --zone "{your zone}" "{your vm name}" --project "{your project}" --tunnel-through-iap --dry-run

結果

/usr/bin/ssh -t -i /Users/{user name}/.ssh/google_compute_engine -o CheckHostIP=no -o HashKnownHosts=no -o HostKeyAlias=compute.{machine number} -o IdentitiesOnly=yes -o StrictHostKeyChecking=yes -o UserKnownHostsFile=/Users/{user name}/.ssh/google_compute_known_hosts -o "ProxyCommand {python3 path} -S /Users/{user name}/google-cloud-sdk/lib/gcloud.py compute start-iap-tunnel '{your vm name}' '%p' --listen-on-stdin --project={project id} --zone={your zone} --verbosity=warning" -o ProxyUseFdpass=no {user name}@compute.{machine number}

2. Connect to Host

  • コマンドパレット(command + Shift + P)から「Connect to HOST」を選択

3. Add New SSH Host

  • 「Add New SSH Host」を選択:新しい接続先の追加

4. sshコマンドの作成

⁠修正前

/usr/bin/ssh -t -i /Users/{user name}/.ssh/google_compute_engine -o CheckHostIP=no -o HashKnownHosts=no -o HostKeyAlias=compute.{machine number} -o IdentitiesOnly=yes -o StrictHostKeyChecking=yes -o UserKnownHostsFile=/Users/{user name}/.ssh/google_compute_known_hosts -o "ProxyCommand {python3 path} -S /Users/{user name}/google-cloud-sdk/lib/gcloud.py compute start-iap-tunnel '{your vm name}' '%p' --listen-on-stdin --project={project id} --zone={your zone} --verbosity=warning" -o ProxyUseFdpass=no {user name}@compute.{machine number}

修正後

  • 上記のままだと使えないので以下のように修正して使用する ⁠ - 冒頭のssh
    • 修正前:/usr/bin/ssh
    • 修正後:ssh
  • ProxyCommandの箇所
    • 修正前:"ProxyCommand
    • 修正後:ProxyCommand="
ssh -t -i /Users/{user name}/.ssh/google_compute_engine -o CheckHostIP=no -o HashKnownHosts=no -o HostKeyAlias=compute.{machine number} -o IdentitiesOnly=yes -o StrictHostKeyChecking=yes -o UserKnownHostsFile=/Users/{user name}/.ssh/google_compute_known_hosts -o ProxyCommand="{python3 path} -S /Users/{user name}/google-cloud-sdk/lib/gcloud.py compute start-iap-tunnel '{your vm name}' '%p' --listen-on-stdin --project={project id} --zone={your zone} --verbosity=warning" -o ProxyUseFdpass=no {user name}@compute.{machine number}
  • ⁠修正後のコマンドを以下に貼り付ける

  • アップデートするconfigを選択

5. .ssh/configの確認

cat .ssh/config

Host compute.{machine number}
  HostName compute.{machine number}
  IdentityFile /Users/{user name}/.ssh/google_compute_engine
  CheckHostIP no
  HashKnownHosts no
  HostKeyAlias compute.{machine number}
  IdentitiesOnly yes
  StrictHostKeyChecking yes
  UserKnownHostsFile /Users/{user name}/.ssh/google_compute_known_hosts
  ProxyCommand {python3 path} -S /Users/{user name}/google-cloud-sdk/lib/gcloud.py compute start-iap-tunnel {your vm name} %p --listen-on-stdin --project={project id} --zone={your zone} --verbosity=warning
  ProxyUseFdpass no
  User {your name}

補足事項)

Hostをインスタンス名(instance name)にした方がわかりやすいので、以下のように変更する

Host {instance name}
  HostName compute.{machine number}
  IdentityFile /Users/{user name}/.ssh/google_compute_engine
  CheckHostIP no
  HashKnownHosts no
  HostKeyAlias compute.{machine number}
  IdentitiesOnly yes
  StrictHostKeyChecking yes
  UserKnownHostsFile /Users/{user name}/.ssh/google_compute_known_hosts
  ProxyCommand {python3 path} -S /Users/{user name}/google-cloud-sdk/lib/gcloud.py compute start-iap-tunnel {your vm name} %p --listen-on-stdin --project={project id} --zone={your zone} --verbosity=warning
  ProxyUseFdpass no
  User {your name}

6. Remote Explorerインスタンスに接続

7. 接続確認

  • こんな感じになっていれば成功!!
    • これができれば、後は『Explorer』で『Open Folder』で任意のディレクトリで作業をすれば良い!!