Databricks  から Python にて Azure SQL Database を操作する方法

## 概要

Databricks から Python にて Azure SQL Database へ下記表の方法により実行する方法を共有します。Spark dataframe (jdbc) 経由で操作する方法にて、`prepareQuery`オプションにて DDL 等の実行できましたが、継続的に実行可能であることが保証できないため、本番環境での利用をおすすめしません。

| #    | 操作方法                             | 事前準備                    |
| ---- | ------------------------------------ | --------------------------- |
| 1    | Spark dataframe (jdbc) 経由で操作    | 特になし                    |
| 2    | pyodbc にて操作                      | ODBC を事前にインストール   |
| 3    | pymsql にて操作                      | pymsql を事前にインストール |


Azure SQL Database に対して Spark コネクターにより接続することも可能ですが、サポートの観点で検証対象外としました。詳細については、次の記事に記載しています。

- [Databricks ベースにしたデータ分析基盤から Azure SQL Database へのデータ連携（書き込みと読み込み）方法の検討メモ #SQLDatabase - Qiita](https://qiita.com/manabian/items/4cb3109ab3ea7ece08ba#1-databricks-から-spark-コネクター-によりデータ連携する方法)


Azure SQL Database から参照（SELECT 操作）だけを実施する場合には、Lakehouse フェデレーション機能の利用を検討してください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/01b92917-3cda-fa0c-d5fc-e424d924a848.png)

引用元：[Lakehouse フェデレーションを使用してクエリを実行する - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/query-federation/#--supported-data-sources)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5d8d3271-19dd-c707-0939-025e4de1e618.png)

引用元：[Lakehouse フェデレーションを使用してクエリを実行する - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/query-federation/#limitations)

## 基本的な情報の整理

### Spark dataframe (jdbc) に関する情報

Spark Dataframe 操作時に`format`を`sqlserver`と指定することで、読み込みと書き込みが実施できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/3718d2f7-0b7b-e13d-bd80-bdff1f806700.png)

引用元：[Azure Databricks を使用して SQL Server にクエリを実行する - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/connect/external-systems/sql-server#configure-a-connection-to-sql-server)

従来の jdbc URL で接続する方法はレガシー扱いとなってしまったようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/1037ae9a-68df-e7be-50ab-5adfd4be9d90.png)

引用元：[Azure Databricks を使用して SQL Server にクエリを実行する - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/connect/external-systems/sql-server#configure-a-connection-to-sql-server)

`prepareQuery`オプションにて SQL を実行できるようですが、スキーマ作成などのいくつかの SQL ではエラーとなりました。

```python
sql = """
CREATE SCHEMA abc;
"""
sql_db_table_01 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("prepareQuery", sql)
    .option("dbTable", "(select 1 as test) src")
    .load()
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2a099c13-a88e-e5be-44cf-bcae00e6f882.png)

```text
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'SELECT'.
```


### pyodbc と pymsql に関する情報

Python から Azure SQL Database を操作するドライバーとして次のように紹介されており、 pyodbc を利用することが推奨されているようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/4f54711b-9a9f-ba8e-7d60-2467837cb890.png)

引用元：[SQL Server 用 Python ドライバー - Python driver for SQL Server | Microsoft Learn](https://learn.microsoft.com/ja-jp/sql/connect/python/python-driver-for-sql-server?view=azuresqldb-current)


pyodbc については、Databricks Runtime 11.3 LTS 以降であれば、Runtime にデフォルトでインストールされております。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5529c414-2bde-c0c0-055c-e861a0ed8030.png)

引用元：[Databricks Runtime 11.3 LTS - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/release-notes/runtime/11.3lts#installed-python-libraries)

## 実行コードと実行結果

### Spark dataframe (jdbc) 経由で操作

Azure SQL Database に対する接続情報に関する値を変更にセット。

```python
server = "ads-01"  ## サーバー名
database = "ads-01"  ## データベース名
username = "user"  ## Azure SQL DB のユーザー名
password = "password"  ## Azure SQL DB のパスワード
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a24a3d20-9b0a-8fea-30b1-23c768f8099e.png)

テーブル作成とデータ挿入を実施する SQL 文を実行。

```python
# CREATE TABLE の実行
sql = """
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[create_tble_01]') AND type in (N'U'))
	DROP TABLE [dbo].[create_tble_01]
;
CREATE TABLE dbo.create_tble_01
(
    str_col varchar(100) COLLATE Japanese_CI_AS
)
;
INSERT INTO dbo.create_tble_01
SELECT 'abc' as str_col
UNION ALL
SELECT N'あいう' as str_col
UNION ALL
SELECT NULL as str_col
;
"""

sql_db_table_01 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("prepareQuery", sql)
    .option("dbTable", "(select 1 as test) src")
    .load()
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/1b5021e9-34ec-ea8f-b27f-17919c91f8b5.png)

テーブルにデータが書き込まれたことを確認。

```python
# SELECT の実行
tgt_tbl_name  = "dbo.create_tble_01"

sql_db_table_01 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("dbTable", tgt_tbl_name)
    .load()
)

sql_db_table_01.display()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/31822b1e-4f20-e41b-9d65-690e380bb72a.png)


次のコードを実行することで、書き込みの実施も可能。

```python
src_schema = "str_col string"
src_data = [
    {
        "str_col": "abc",
    },
    {
        "str_col": "あいう",
    },
    {
        "str_col": None,
    },
]
df = spark.createDataFrame(src_data,src_schema)

tgt_tbl_name = "dbo.create_tble_01"

(
    df.write.format("sqlserver")
    .mode("overwrite")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("truncate", True)
    .option("dbTable", tgt_tbl_name)
    .save()
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e3efc2a9-1612-deaa-de08-ee9d7bf32fc8.png)

### pyodbc にて操作

Azure SQL Database (SQL Server)の ODBC をインストール。

```python
%sh
curl -s https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc > /dev/null
curl -s https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list > /dev/null
sudo apt-get update -qq
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 -qq
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/9c40e38d-c351-7c58-e339-9f4a82e8fda1.png)

Azure SQL Database に対する接続情報に関する値を変更にセット。

```python
server = "ads-01"  ## サーバー名
database = "ads-01"  ## データベース名
username = "user"  ## Azure SQL DB のユーザー名
password = "password"  ## Azure SQL DB のパスワード
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/0f0545ac-3cb6-fa92-54af-a3a2fa0b84da.png)

pyodbc にて利用する接続文字列を変数にセット。

```python
sql_connect_string = f"Driver={{ODBC Driver 18 for SQL Server}};SERVER=tcp:"
sql_connect_string += f"{server}.database.windows.net,1433;"
sql_connect_string += f"Database={database};Uid={username};Pwd={password};"
sql_connect_string += f"Encrypt=yes;TrustServerCertificate=no;ConnectionTimeout=30;"
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/064f1908-e038-1384-bb23-1667fb56d36e.png)

テーブル作成とデータ挿入を実施する SQL 文を実行。

```python
import pyodbc

sql = """
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[create_tble_02]') AND type in (N'U'))
	DROP TABLE [dbo].[create_tble_02]
;
CREATE TABLE dbo.create_tble_02
(
    str_col varchar(100) COLLATE Japanese_CI_AS
)
;
INSERT INTO dbo.create_tble_02
SELECT 'abc' as str_col
UNION ALL
SELECT N'あいう' as str_col
UNION ALL
SELECT NULL as str_col
;
"""

try:
    with pyodbc.connect(sql_connect_string, timeout=300) as conn:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            conn.commit()
except pyodbc.Error as e:
    print("An error occurred while connecting to the database.")
    print(e)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/af2c68aa-1794-9fcb-eefa-3f05e8883bba.png)

テーブルにデータが書き込まれたことを確認。

```python
# SELECT の実行
tgt_tbl_name  = "dbo.create_tble_02"

sql_db_table_01 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("dbTable", tgt_tbl_name)
    .load()
)

sql_db_table_01.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/377fbb84-d399-ae87-b055-174987ff816a.png)

### pymsql にて操作

```python
%pip install pymssql -q
dbutils.library.restartPython()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/05e9581e-02b5-6371-7bef-aaf4d01cc8fc.png)

Azure SQL Database に対する接続情報に関する値を変更にセット。

```python
server = "ads-01"  ## サーバー名
database = "ads-01"  ## データベース名
username = "user"  ## Azure SQL DB のユーザー名
password = "password"  ## Azure SQL DB のパスワード
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/51e6d67a-ae68-ec9d-3208-aad3c5bf2b4b.png)

テーブル作成とデータ挿入を実施する SQL 文を実行。

```python
import pymssql

server_for_pymssql = f"{server}.database.windows.net"

sql = """
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[create_tble_03]') AND type in (N'U'))
	DROP TABLE [dbo].[create_tble_03]
;
CREATE TABLE dbo.create_tble_03
(
    str_col varchar(100) COLLATE Japanese_CI_AS
)
;
INSERT INTO dbo.create_tble_03
SELECT 'abc' as str_col
UNION ALL
SELECT N'あいう' as str_col
UNION ALL
SELECT NULL as str_col
;
"""

# Connect to the database
with pymssql.connect(server_for_pymssql, username, password, database) as conn:
    # Create a cursor object
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/718665c5-435f-eeb3-4e8f-2ed2eeb7d0da.png)

テーブルにデータが書き込まれたことを確認。

```python
# SELECT の実行
tgt_tbl_name  = "dbo.create_tble_03"

sql_db_table_01 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("dbTable", tgt_tbl_name)
    .load()
)

sql_db_table_01.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e356b0ea-6f61-e3a2-b7ec-806d66ff3e01.png)

### 事後処理

Azure SQL Database に対して 次の SQL を実行してテーブルを削除。

```sql
DROP TABLE dbo.create_tble_01
GO
DROP TABLE dbo.create_tble_02
GO
DROP TABLE dbo.create_tble_03
GO
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/91fe19df-9a35-3565-1728-53649725532e.png)