Python と Scala を組み合わせた Azure SQL Database 操作の新手法：Databricksノートブックによる効率的なデータベース操作

<!-- 
# IT エンジニア向けのブログ記事における記載対象の見出しの文章を記述

## 制限事項
- IT プロフェッショナル向けの文章を記述すること
- 出力形式はコピー＆ペーストを実施できるように、"````markdown" ではじまり"````"で終わる markdown 形式で出力すること
- 記載内容の箇条書きに基づき、簡潔な文章で段落の文章に変換すること
- 記載内容の内容に記載している見出しを変更しないこと
- 記載内容に記載されていない Markdown の見出し（例：`##`）を追加しないこと
- 記載内容にて説明が不足している場合には追記すること
- 段落上で箇条書きで記述しないこと
- コードプロックにて説明文が記載されていない場合には、100 字程度の簡潔な説明文を記載すること

## 出力サンプル

````markdown
## 概要
Databricks (Spark) における Update 文の動作仕様を確認した上で、 Update 文を多用した際のパフォーマンスの懸念事項をその対処方法を記述します。
````
-->

<!-- 
## 記事の見出し

```markdown
## 概要
## 事前準備
```
 -->

<!-- 
## 記載対象の見出し

```markdown
## 概要
```

## 記載内容

````markdown
````
-->

## 概要

本記事では、DatabricksのPythonノートブックを使用して、追加コンポーネントを必要とせずにAzure SQL Databaseに対してScalaでSQLを実行する方法を紹介します。以前に[Python にて databricks から  Azure SQL Database を操作する方法](https://qiita.com/manabian/items/31f542b45fd63eb211f1)についての記事を共有しましたが、継続的な利用にリスクがあることや追加のコンポーネントが必要となるなどのデメリットがありました。そこで、PythonのノートブックでAzure SQL Databaseへの操作をデフォルトでインストールされているAzure SQL Databaseのjdbc (mssql-jdbc)を使用してScalaで実行する方法を検証しました。

ノートブック上で複数の言語を使用する際には、マジックコマンド（`%python`や`%scala`）を指定することで実行できますが、変数の値の受け渡し方法を検討する必要があります。Databricksで利用できる言語であれば、Sparkと接続することができ、その設定値（`spark.conf`）により変数値の受け渡しが可能です。Pytho nで`spark.conf.set`を使用してScalaに渡す変数値を定義し、Scalaで`spark.conf.get`を使用してその値を取得します。パスワードなどのシークレットは、Databricksシークレット（`dbutils.secrets.get`）を使用してそれぞれの言語で取得することが望ましいです。

本記事では、検証時のコードと実行結果を共有します。

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

### 事前準備

Azure SQL Database 上に検証用のテーブルを作成します。

```python
server = "ads-01"  ## サーバー名
database = "ads-01"  ## データベース名
username = dbutils.secrets.get(scope="qiita", key="sql_authentificate_user")  ## Azure SQL DB のユーザー名
password = dbutils.secrets.get(scope="qiita", key="sql_authentificate_password")  ## Azure SQL DB のパスワード
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/156da54c-b294-ee09-c5d2-2d09442c8d42.png)

```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
)
;
"""

df = (
    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", "dbo.create_tble_01")
    .load()
)

df.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d84d2442-02a6-844b-09dd-d29665ccf054.png)

### Python から Scala に渡す変数を定義

```python
# Scala に渡す変数を定義
spark.conf.set("PyToScaal.server", server)
spark.conf.set("PyToScaal.database", database)

sql= """
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
;
"""
spark.conf.set("PyToScaal.executed_sql", sql)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/680d1f9e-f975-1ee0-4ca9-e333de39fb2a.png)

### Scala にて Azure SQL Database に対する SQL を実行

```scala
%scala
import java.sql.DriverManager
import java.sql.Connection

var connection: Connection = null

try {
  val executed_sql = spark.conf.get("PyToScaal.executed_sql")

  // Get jdbcUsername from dbutils secrets
  val server = spark.conf.get("PyToScaal.server")
  val database = spark.conf.get("PyToScaal.database")
  val jdbcUsername = dbutils.secrets.get(scope="qiita", key="sql_authentificate_user")
  val jdbcPassword = dbutils.secrets.get(scope="qiita", key="sql_authentificate_password")
  val jdbcUrl = s"""jdbc:sqlserver://${server}.database.windows.net:1433;
    |databaseName=${database};
    |encrypt=true;
    |trustServerCertificate=false;
    |hostNameInCertificate=*.database.windows.net;
    |loginTimeout=300""".stripMargin.replaceAll("\n", "")
  connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
  val statement = connection.createStatement()
  statement.execute(executed_sql)

} finally {
  if (connection != null) {
    connection.close()
  }
}
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a85fffeb-20d4-19b4-e0db-060e41b75bfc.png)

### データを確認

```python
# データを確認
df = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("dbTable", "[dbo].[create_tble_01]")
    .load()
)

df.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/349c0a6a-bfc0-7a9c-a32c-7cc698ffa639.png)

### 検証用に作成したテーブルを Drop

```python
# DROP 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]
;
"""

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/6ef201fc-bdbb-d7c4-7da7-c54cc97f258c.png)