Livebook から Microsoft SQL Server に接続して SQL を実行する

はじめに

Livebook Launch Week 2 を自分でやってみるシリーズ

• Day1: Smart Cell からのリモート接続
• Day2: Whisper による音声認識の新機能
• Day3: ファイルをドラッグ＆ドロップすると、扱うためのコードを自動生成する
• Day4:
  • データクラウド Snowflake に接続する
  • Microsoft SQL Server に接続する　 <- ここ
• Day5: Vim と Emacs のキーバインド

Day 4 はデータベースとの統合です

今までも PostgreSQL や MySQL などには接続できていましたが、 Snowflake と SQL Server にも接続できるようになっています

本記事では　Livebook 上で KinoDB を利用し、 SQL Server への接続、 SQL 実行をやってみます

PostgreSQL への接続、 SQL 実行についてはこちら

実装したノートブックはこちら

SQL Server の準備

今回は SQL Server を Livebook と一緒にコンテナで起動します

実運用で使うわけではないため、無償の Developer エディションを使います

Livebook と同時に起動するよう docker　compose の設定を作りましょう

今回は docker-compose.with-db.yml に以下の内容を記載したとします

version: '3.2'

services:
  livebook_with_db:
    image: ghcr.io/livebook-dev/livebook:0.11.3
    container_name: livebook_with_db
    ports:
      - '4000:4000'
      - '8080:8080'
      - '8081:8081'

  sql_server:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: sql_server_for_livebook
    ports:
      - '1433:1433'
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=<パスワード>
    volumes:
      - sql_server_for_livebook:/var/opt/mssql

volumes:
  sql_server_for_livebook:

パスワードの値は 8 文字以上かつ、英字大文字・英字小文字・数字・記号の全てを含むようにしてください（パスワードポリシーに違反していると起動しません）

Livebook と SQL Server のコンテナをカスタマイズしたい場合はそれぞれ Dockerfile を作ります

以下のコマンドで Livebook と SQL Server を起動します

docker compose -f docker-compose.with-db.yml up --build

ログが以下のように表示されます

...
sql_server_for_livebook  | SQL Server 2019 will run as non-root by default.
sql_server_for_livebook  | This container is running as user mssql.
sql_server_for_livebook  | Your master database file is owned by mssql.
sql_server_for_livebook  | To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
...
livebook_with_db         | [Livebook] Application running at http://localhost:8080/?token=xxx
...

livebook_with_db のログに表示された URL をブラウザで開き、 Livebook を使用します

セットアップ

Livebook で新しいノートブックを開きます

セットアップセルに以下のコードを入力して実行しましょう

Mix.install([
  {:kino_db, "~> 0.2"},
  {:tds, "~> 2.3"}
])

Tds は SQL Server 用のドライバーです

master DB への接続

Smart Cell　の Database connection を追加します

すると、以下のような入力フォームが表示されます

コンテナで起動して初期状態の場合、まだ自分でデータベースを作っていないため、 master DB　に接続します

以下のように入力してください

• Hostname: sql_server_for_livebook (docker compose の container_name で指定した値)
• Instance: 空欄
• Port: 1433 (デフォルトのまま)
• Database: master
• User: SA
• Password: docker compose の環境変数 MSSQL_SA_PASSWORD で指定した値

Smart Cell 左上の Evaluate をクリックし、結果が {:ok, #PID<0.1299.0>} のようになれば接続できています

サンプル DB の作成

Smart Cell で SQL query を追加します

以下のようなフォームが表示されます

まずはデータベース作成用のSQL に変更して、左上 Evaluate をクリックします

CREATE DATABASE sample_db

続いてもう一つ SQL query の Smart Cell を追加し、以下の SQL を実行します

SELECT Name from sys.databases

実行すると、 sample_db ができていることが分かります

サンプル DB への接続

もう一つ Database connection の Smart Cell を追加し、作成した sample_db に接続します

入力する値は Database 以外、 master DB への接続時と同じです

• Hostname: sql_server_for_livebook
• Instance: 空欄
• Port: 1433
• Database: sample_db
• User: SA
• Password: docker compose の環境変数 MSSQL_SA_PASSWORD で指定した値

SQL の実行

SQL query の Smart Cell を追加し、テーブル作成の SQL を実行します

CREATE TABLE
  sample
(
  id integer,
  name varchar(20)
)

更に SQL query の Smart Cell を追加し、データ追加の SQL を実行します

INSERT INTO
  sample
(
  id,
  name
)
OUTPUT
  inserted.id
VALUES
(
  1,
  'John'
)

OUTPUT 句を書いていたので、結果として追加したデータの id 列の値が返ってきました

最後にもう一つ SQL query の Smart Cell を追加し、データ取得の SQL を実行します

SELECT
  *
FROM
  sample

結果をテーブル形式で取得できました

sqlcmd での確認

SQL Server の CLI である sqlcmd でも作成したテーブルを確認してみましょう

まず、 SQL Server コンテナ内の bash に入ります

docker exec -it sql_server_for_livebook /bin/bash

コンテナ内で以下のコマンドを実行します

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P <パスワード>

DB に接続できるので、以下のコマンドを実行します

SELECT * FROM sample;
GO

sqlcmd では GO を実行しないと SQL が適用されないことに注意してください

結果は以下のようになります

id          name                
----------- --------------------
          1 John                

(1 rows affected)

まとめ

SQL Server にも Livebook から簡単に接続できました

2023年11月現在、以下のデータベースに対応しています

• PostgreSQL
• MySQL
• SQLite
• Google BigQuery
• AWS Athena
• Snowflake
• SQL Server

今後も対象は増えていき、ほぼ全ての DB に接続できるようになりそうです