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

## はじめに

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

- Day1: [Smart Cell からのリモート接続](https://qiita.com/RyoWakabayashi/items/d2f19a674c261790d999)
- Day2: [Whisper による音声認識の新機能](https://qiita.com/RyoWakabayashi/private/4db05611945f3bba6e30)
- Day3: [ファイルをドラッグ＆ドロップすると、扱うためのコードを自動生成する](https://qiita.com/RyoWakabayashi/private/0a2760c88001076c0c7b)
- Day4:
  - [データクラウド Snowflake に接続する](https://qiita.com/RyoWakabayashi/private/98db4a51c6a275541ec2)
  - Microsoft SQL Server に接続する　 <- ここ
- Day5: [Vim と Emacs のキーバインド](https://qiita.com/RyoWakabayashi/items/c95934c690d663918a8a)

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

https://news.livebook.dev/integration-with-snowflake-and-microsoft-sql-server---launch-week-2---day-4-2o4z9C

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

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

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

https://qiita.com/RyoWakabayashi/items/24ecd58f3ba8ad960347

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

https://github.com/RyoWakabayashi/elixir-learning/blob/main/livebooks/kino/kino_db_sql_server.livemd

## SQL Server の準備

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

https://learn.microsoft.com/ja-jp/sql/linux/sql-server-linux-docker-container-deployment?view=sql-server-ver16&pivots=cs1-bash#production

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

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

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

```yaml
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 を起動します

```bash
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 で新しいノートブックを開きます

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

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

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

https://github.com/elixir-ecto/tds

## master DB への接続

Smart Cell　の Database connection を追加します

![スクリーンショット 2023-11-04 0.02.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1485835/fd9ef78a-f20d-45f4-b63c-dff651f8d87f.png)

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

![スクリーンショット 2023-11-04 9.07.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1485835/78a1f36a-b777-b12a-0d26-2dcb7a927dea.png)

コンテナで起動して初期状態の場合、まだ自分でデータベースを作っていないため、 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>}` のようになれば接続できています

![スクリーンショット 2023-11-04 9.18.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1485835/e0c8528e-e4d5-5b7b-fa97-819d624503e6.png)

## サンプル DB の作成

Smart Cell で SQL query を追加します

![スクリーンショット 2023-11-04 0.21.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1485835/f2307592-ab79-12f2-a6d8-0ae5470b2ae5.png)

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

![スクリーンショット 2023-11-04 9.22.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1485835/3fe33520-3324-1eb7-060e-1a92ae05c11e.png)

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

```SQL
CREATE DATABASE sample_db
```

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

```SQL
SELECT Name from sys.databases
```

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

![スクリーンショット 2023-11-04 9.23.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1485835/2e6095f2-f58e-f65c-2b88-4cb6cb5c4756.png)

## サンプル 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 を実行します

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

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

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

![スクリーンショット 2023-11-04 9.29.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1485835/d49b8417-90f1-820b-b37f-90b9304f2aac.png)

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

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

```SQL
SELECT
  *
FROM
  sample
```

![スクリーンショット 2023-11-04 9.30.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1485835/afbe7025-2b53-f8fe-cc89-de99361f6628.png)

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

## sqlcmd での確認

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

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

```bash
docker exec -it sql_server_for_livebook /bin/bash
```

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

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

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

```SQL
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 に接続できるようになりそうです