Oracle Cloud Infrastructure Data Scienceを使って、Oracle Autonomous Database(ADW)のデータにアクセスしてみる

#本記事の狙い
2020/2に、Oracle Cloud Infrastructure Data Science(OCI-Data Science)がリリースされました。
本記事では、実際にOCI-Data Scienceを使って、Oracle Autonomous Database(ADW)上のデータにクエリする手順を、実際に実施してみたいと思います。

#参考文献
###実施に参考になるリンク
- [Oracle Cloud Infrastructure Data Science（OCI-Data Science）を使ってみよう](https://community.oracle.com/docs/DOC-1036110)
- [Autonomous Database(ADB,ADW,ATP)の作成とSQL Developerからの接続](https://qiita.com/ts_carp/items/a22bdd565bdf5c6e4ea8)
- [SQL Developer Webによるローカル・ファイルからのデータのロード](https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/load-data-sqldeveloper-web.html#GUID-25CEEF91-ACE7-47BF-AF0A-4F4964018719)
- [Qiita記事：SQLAlchemy から Pandas にデータを読み込む](https://qiita.com/propella/items/6480f6c8ef612cd2283e)

###その他、参考文献
- [【翻訳記事】：What Is Oracle Cloud Infrastructure Data Science?](https://blogs.oracle.com/bigdata-dataintegration-jp/%E3%80%90%E7%BF%BB%E8%A8%B3%E8%A8%98%E4%BA%8B%E3%80%91%EF%BC%9Awhat-is-oracle-cloud-infrastructure-data-science)
- [OCI-Data Science 公式ドキュメント](https://docs.cloud.oracle.com/en-us/iaas/data-science/using/overview.htm)
- [Oracle Accelerated Data Science SDK (ADS) 公式ドキュメント](https://docs.cloud.oracle.com/en-us/iaas/tools/ads-sdk/latest/index.html)


#手順
以下のような手順で実施します。
1.OCI-Data Scienceの設定
2.Oracle Autonomous Database(ADW)の構築
3.OCI-Data ScienceからADWに接続できるようにするための設定
4.実際にOCI-Data Scienceから、ADW上のデータにクエリする

###1.OCI-Data Scienceの設定
上記参考文献の「Oracle Cloud Infrastructure Data Science（OCI-Data Science）を使ってみよう」を参考にOCI-Data Scienceのノートブック環境を構築、設定をします。
手順とおりに実施すれば、それほど難しくないのでは、と思います。

1. Oracle Cloudの基本的な設定の後に、ノートブック環境を構築します。
2. **getting-started.ipynb**を使って、ノートブック環境（JupyerLab）の初期作業を行います。
 

###2.Oracle Autonomous Database(ADW)の構築
上記参考文献の「Autonomous Database(ADB,ADW,ATP)の作成とSQL Developerからの接続」を参考に、ADWを構築、設定をします。
今回は簡単なクエリを実施できればいいので、あまり難しく考えずに、「ADWの作成」を見ながら、ADWを作成します。「使用可能」のステータスまで１０分ぐらいでしょうか。超簡単。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/533c14cc-fe01-b09b-44c5-ab62b9d8e533.png)


###3.OCI-Data ScienceからADWに接続できるようにするための設定
OCI-Data Scienceの中には、**/home/datascience/ads-examples** 配下のディレクトリに、多くのサンプルノートブックファイルが存在します。
これらサンプルファイルで、基本的な使い方は大体網羅されていますが、今回はその中の一つ、**ads_load_data_from_ADW_1.ipynb** というサンプルファイルを使います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/0d2aca95-fd9f-523b-d330-67fd3e277300.png)

このサンプルノートブックの順序に従ってやればできるようになっています。
実際やってみます。下記のa-hは、サンプルノートブック内記載の手順に準拠しています。

#### a.ADWのDB接続ボタンをクリック
まずは、OCI-Data ScienceとADWを接続設定します。
ADWのDB接続ボタンをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/e26c69f5-d053-6d92-f4d3-4ca391400d6d.png)

#### b.ウオレットのダウンロード
ダウンロードボタンより、ウオレットをダウンロードします。
ウォレットためのパスワード指定を求められるので入力してダウンロードします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/4f483047-dd1a-0167-85b5-3c648380497e.png)

#### c.ノートブック環境にフォルダ作成
ノートブック環境に、ウオレットをおく、フォルダを作成します。
　**/home/datascience/block_storage**　配下に新規ディレクトリ（下記の場合、MOADW）を作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/cce3e655-d1b9-a902-63a3-cfdee3dc1351.png)

#### d.接続設定ファイルアップロード
ダウンロードしてきたzipファイルを解凍し、全てのファイルを、上記フォルダにアップロードします。
ノートブック環境であるJupyterLabのアップロードボタンを使ってアップロードします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/f8259df8-0b4a-78bd-ddd6-47e7cd8094dd.png)

#### d.sqlnet.oraのMETHOD_DATAの変更
アップロードした、sqlnet.oraの**METHOD_DATA**を、上記ディレクトリのパスに変更します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/101401ec-68d5-7eda-1816-e66f6912a3c7.png)

#### e.環境変数：TNS_ADMINの設定
環境変数：**TNS_ADMIN**を、上記ディレクトリに設定します。
各自の環境のパスに変更後、ノートブック上でShift+Returnで実行します。

```
# Replace with your TNS_ADMIN value here:
%env TNS_ADMIN=/home/datascience/block_storage/MOADW
```
#### f.環境変数：ADW_SIDの設定
環境変数：**ADW_SID**を、**tnsnames.oraのSID**に変更します。
tnsnames.oraを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/0aef18ba-24bd-420d-05c1-f866c51b8490.png)

環境変数を読み込みます。

```
# Replace with your SID name here: 
%env ADW_SID=moadw_high
```

#### g.環境変数：ユーザー名とパスワードの設定
ADWにアクセスできる、ユーザー名/パスワードを入力します。

```
# Replace with your ADW user name and password here: 
%env ADW_USER=<ADWのユーザー名>
%env ADW_PASSWORD=<ADWのユーザーのパスワード>
```

#### h.接続テスト
最後に接続テストを行います。
ADWに接続、Disconnectedすれば成功です。

```
!echo exit | sqlplus $ADW_USER/$ADW_PASSWORD@$ADW_SID

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 10 05:45:33 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Tue Mar 10 2020 04:33:50 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
```

以上で、OCI-Data ScienceとADWを接続設定設定は終了です。
これで、OCI-Data ScienceからADWにアクセスができるようになっています。

もしエラーが出る場合は、特に以下を確認ください。
- ADWは正常に起動しているか、パスワードなどは間違いないか。
- 上記a-gはきちんと設定されているか。
- getting-started.ipynbは、エラーなく実行できているか。特に「Testing the Access to our API」まで正常に設定できているか。

###4.実際にOCI-Data Scienceから、ADW上のデータにクエリする
####0.SQL Developer Webを使ったサンプルデータのロード
実際にクエリアクセスをする前に、ADWにサンプルデータを入力します。
今回は下記のようなサンプルデータをローカルPCでExcelで作り、SQL Developer Webを使って、ADWにテーブル作成とデータロードしました。
SQL Developer Webの使い方は、上記参考文献、「SQL Developer Webによるローカル・ファイルからのデータのロード」を参考にしてください。
**SQL Developer Web、超簡単、超便利 ！！**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/b9aa9a41-3582-4c73-e940-73011f036411.png)

いよいよ、OCI-Data ScienceからADW上のデータにクエリしますが、これにはいくつかの方法があります。
今回は、上記設定手順も記載されているサンプルノートブック**ads_load_data_from_ADW_1.ipynb** に記載されている、1−3の方法を実施します。

**
1.Load an ADW database Table as an ADSDataset object
2.Query data from ADW using pandas
3.Query data from ADW using cx_Oracle


####1.Load an ADW database Table as an ADSDataset object
では実際にADW上のデータにOCI-Data Scienceからアクセスしてみたいと思います。
1つ目の方法は、ADSの**DatasetFactory**クラスを使って、ADWのデータをクエリし、**ADSDataset**にロードする方法です。
ADSとは、OCI-Data Scienceに含まれる、独自のPythonライブラリです。詳細は、参考文献のADSのドキュメントを参考にしてください。

まずは必要なライブラリをインポートします。

```python

%load_ext autoreload
%autoreload 2

import warnings
warnings.filterwarnings("ignore", category=DeprecationWarning) 

import pandas as pd
import logging
import os

logging.basicConfig(format='%(levelname)s:%(message)s', level=logging.INFO)

from ads.dataset.factory import DatasetFactory
```

接続に必要なURIを設定します。

```python

uri=f'oracle+cx_oracle://{os.environ["ADW_USER"]}:{os.environ["ADW_PASSWORD"]}@{os.environ["ADW_SID"]}'
```

Table名と、Indexを指定し、データをロードします。

```python

table="PURCHASE_ITEM"
index_col="cust_id"
ds = DatasetFactory.open(uri, format="sql", table=table, index_col=index_col)
```
確認してみます。

```python

ds.head()
```
でもいいのですが、ADSには`show_in_notebook`という便利な可視化ライブラリがあるので、こちらでざっと属性を見てみます。

```python

ds.show_in_notebook()
```
各属性ごとに下記のような形で、特徴や値の分布が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/624cbf2f-cd5a-4e0b-e05f-47efe57d87cd.png)

それぞれの属性の相関図も見ることもできます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109157/339dd538-3d16-ba7c-829c-8ac60e08abc6.png)

これらが、`ds.show_in_notebook()`　の、一行のコマンドでできてしまう！
超便利！


####2.Run a SQL query on your ADW database using SQLAlchemy and Pandas
次のサンプルは、pandasとsqlalchemyを使って、ADWから読み込む方法です。
参考文献「SQLAlchemy から Pandas にデータを読み込む」も参考ください。

```python
from sqlalchemy import create_engine

engine = create_engine(uri)
ds_01 = pd.read_sql('SELECT * from PURCHASE_ITEM', con=engine)
```
`read_sql_query`を使って、`where`句を使うこともできます。

```python
ds_02=pd.read_sql_query(
    '''
    SELECT cust_id, age from PURCHASE_ITEM
    '''
    , uri)
```
pandasのDataFrameを、ADSのDataframeに変換することもできます。
サンプルノートブック**ads_load_data_from_ADW_1.ipynb**には、`DatasetFactory.open()`を使ったサンプルがのっていますが、

```python
ds_03 = DatasetFactory.open(ds_02)
```
`DatasetFactory.from_dataframe()`を使うこともできます。
これは、参考文献のADSのドキュメントにのっていました。

```python
ds_03 = DatasetFactory.from_dataframe(ds_02)
```

####3.Query data from ADW using cx_Oracle
cx_ORACLEを使ってクエリを実施することもできます。

```python
import cx_Oracle
connection = cx_Oracle.connect(os.environ["ADW_USER"], os.environ["ADW_PASSWORD"], os.environ["ADW_SID"])
cursor = connection.cursor()
results = cursor.execute("""
    SELECT * from PURCHASE_ITEM
    """)
data = results.fetchall()
df = pd.DataFrame(np.array(data))
ds_04 = DatasetFactory.from_dataframe(df)
```

#終わりに
今回は、OCI-Data Scienceにサンプルとして含まれる、**ads_load_data_from_ADW_1.ipynb** をベースに、OCI-Data ScienceからADWにクエリしてみましたが、それ以外にも方法はあります。

また、ADSを使って、ADW上のデータからモデル生成をしたり、推論した結果をADW上のデータにInsertする方法も、サンプルノートブックや、[ADSのドキュメントの「Loading Data」](https://docs.cloud.oracle.com/en-us/iaas/tools/ads-sdk/latest/user_guide/loading_data/loading_data.html#)に記載しているので参考にしてみてください。
それ以外にも、AWSのS3上のデータからOCI-Data Scienceにデータをロードする方法も記載されています。


OCI-Data Scienceは、最大30日間300$分の無償クレジットが使える[Oracle Free Trial](https://www.oracle.com/jp/cloud/free/#free-cloud-trial)の、対象サービスですので、ぜひ実際に使ってみることをおすすめします。

#### Let's Enjoy Data Science!