Azure Data Factory にて Oracle Database から Delta Lake コネクターにて制御文字のデータ連携可否の検証

## 概要

Azure Data Factory にて Oracle Database から Delta Lake コネクターにて制御文字のデータ連携可否の検証結果を共有します。Databricks にて制御文字が連携したデータを表示したところ、次のような結果が表示され、制御文字のデータ連携が可能であることを確認できました。

```python
[
    Row(text="NUL", tgt_string="␀"),
    Row(text="SOH", tgt_string="␁"),
    Row(text="STX", tgt_string="␂"),
    Row(text="ETX", tgt_string="␃"),
    Row(text="EOT", tgt_string="␄"),
    Row(text="ENQ", tgt_string="␅"),
    Row(text="ACK", tgt_string="␆"),
    Row(text="BEL", tgt_string="␇"),
    Row(text="BS", tgt_string="␈"),
    Row(text="HT", tgt_string="␉"),
    Row(text="LF", tgt_string="␊"),
    Row(text="VT", tgt_string="␋"),
    Row(text="FF", tgt_string="␌"),
    Row(text="CR", tgt_string="␍"),
    Row(text="SO", tgt_string="␎"),
    Row(text="SI", tgt_string="␏"),
    Row(text="DLE", tgt_string="␐"),
    Row(text="DC1", tgt_string="␑"),
    Row(text="DC2", tgt_string="␒"),
    Row(text="DC3", tgt_string="␓"),
    Row(text="DC4", tgt_string="␔"),
    Row(text="NAK", tgt_string="␕"),
    Row(text="SYN", tgt_string="␖"),
    Row(text="ETB", tgt_string="␗"),
    Row(text="CAN", tgt_string="␘"),
    Row(text="EM", tgt_string="␙"),
    Row(text="SUB", tgt_string="␚"),
    Row(text="ESC", tgt_string="␛"),
    Row(text="FS", tgt_string="␜"),
    Row(text="GS", tgt_string="␝"),
    Row(text="RS", tgt_string="␞"),
    Row(text="US", tgt_string="␟"),
    Row(text="DEL", tgt_string="␡"),
]
```

## 検証手順と実行結果

### 1. Oracle Database にてテーブル作成とデータの挿入

```sql
-- DROP TABLE control_character_table
-- ;
CREATE TABLE control_character_table (
    text VARCHAR2(255),
    tgt_string VARCHAR2(10)
);
INSERT INTO control_character_table (text, tgt_string)
SELECT 'NUL' AS text,'␀' AS tgt_string from dual
UNION ALL
SELECT 'SOH','␁' from dual
UNION ALL
SELECT 'STX','␂' from dual
UNION ALL
SELECT 'ETX','␃' from dual
UNION ALL
SELECT 'EOT','␄' from dual
UNION ALL
SELECT 'ENQ','␅' from dual
UNION ALL
SELECT 'ACK','␆' from dual
UNION ALL
SELECT 'BEL','␇' from dual
UNION ALL
SELECT 'BS','␈' from dual
UNION ALL
SELECT 'HT','␉' from dual
UNION ALL
SELECT 'LF','␊' from dual
UNION ALL
SELECT 'VT','␋' from dual
UNION ALL
SELECT 'FF','␌' from dual
UNION ALL
SELECT 'CR','␍' from dual
UNION ALL
SELECT 'SO','␎' from dual
UNION ALL
SELECT 'SI','␏' from dual
UNION ALL
SELECT 'DLE','␐' from dual
UNION ALL
SELECT 'DC1','␑' from dual
UNION ALL
SELECT 'DC2','␒' from dual
UNION ALL
SELECT 'DC3','␓' from dual
UNION ALL
SELECT 'DC4','␔' from dual
UNION ALL
SELECT 'NAK','␕' from dual
UNION ALL
SELECT 'SYN','␖' from dual
UNION ALL
SELECT 'ETB','␗' from dual
UNION ALL
SELECT 'CAN','␘' from dual
UNION ALL
SELECT 'EM','␙' from dual
UNION ALL
SELECT 'SUB','␚' from dual
UNION ALL
SELECT 'ESC','␛' from dual
UNION ALL
SELECT 'FS','␜' from dual
UNION ALL
SELECT 'GS','␝' from dual
UNION ALL
SELECT 'RS','␞' from dual
UNION ALL
SELECT 'US','␟' from dual
UNION ALL
SELECT 'DEL','␡' from dual
;

COMMIT;

SELECT * FROM control_character_table
;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/3522375d-5fd9-ce77-1f1c-ae1397208d49.png)

### 2. Databricks にてスキーマとテーブルを作成

```python
%sql
CREATE SCHEMA IF NOT EXISTS hive_metastore.control_character_table;
CREATE OR REPLACE TABLE hive_metastore.control_character_table.control_character_table (
    text string,
    tgt_string string
);
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5692eb5d-15ea-e229-2167-a19ddfdcf860.png)

### 3. Azure Data Factory にてコピーアクティビティの実行

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/87cde25e-2a18-3b31-596b-f9cf4ce34bc7.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6f551ea8-114d-025c-320a-b0ec279a0f4e.png)

### 4. Databricks にて実行結果を確認

```python
df = spark.table("hive_metastore.control_character_table.control_character_table")

print(df.collect())
df.display()
```


```python
[
    Row(text="NUL", tgt_string="␀"),
    Row(text="SOH", tgt_string="␁"),
    Row(text="STX", tgt_string="␂"),
    Row(text="ETX", tgt_string="␃"),
    Row(text="EOT", tgt_string="␄"),
    Row(text="ENQ", tgt_string="␅"),
    Row(text="ACK", tgt_string="␆"),
    Row(text="BEL", tgt_string="␇"),
    Row(text="BS", tgt_string="␈"),
    Row(text="HT", tgt_string="␉"),
    Row(text="LF", tgt_string="␊"),
    Row(text="VT", tgt_string="␋"),
    Row(text="FF", tgt_string="␌"),
    Row(text="CR", tgt_string="␍"),
    Row(text="SO", tgt_string="␎"),
    Row(text="SI", tgt_string="␏"),
    Row(text="DLE", tgt_string="␐"),
    Row(text="DC1", tgt_string="␑"),
    Row(text="DC2", tgt_string="␒"),
    Row(text="DC3", tgt_string="␓"),
    Row(text="DC4", tgt_string="␔"),
    Row(text="NAK", tgt_string="␕"),
    Row(text="SYN", tgt_string="␖"),
    Row(text="ETB", tgt_string="␗"),
    Row(text="CAN", tgt_string="␘"),
    Row(text="EM", tgt_string="␙"),
    Row(text="SUB", tgt_string="␚"),
    Row(text="ESC", tgt_string="␛"),
    Row(text="FS", tgt_string="␜"),
    Row(text="GS", tgt_string="␝"),
    Row(text="RS", tgt_string="␞"),
    Row(text="US", tgt_string="␟"),
    Row(text="DEL", tgt_string="␡"),
]
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a4b3325a-fc39-35b9-55d8-d3f49466d9f5.png)