Oracle APEXへのオープンデータの取り込み 新型コロナウイルス感染症 陽性患者データを例に 中越祐治 プリンシパル・ソリューション・エンジニア 日本オラクル株式会社 2020年7月29日

Safe harbor statement 以下の事項は、弊社の⼀般的な製品の⽅向性に関する概要を説明するものです。また、 情報提供を唯⼀の⽬的とするものであり、いかなる契約にも組み込むことはできません。以 下の事項は、マテリアルやコード、機能を提供することを確約するものではないため、購買 決定を⾏う際の判断材料になさらないで下さい。 オラクル製品に関して記載されている機能の開発、リリース、時期及び価格については、弊 社の裁量により決定され、変更される可能性があります。 Copyright © 2020, Oracle and/or its affiliates 2

3 Copyright © 2020, Oracle and/or its affiliates [Date] 1. データ・ロードの準備 2. データ・ワークショップを使ったインポート 3. データ・ロード・ウィザード 4. APEX_DATA_PARSERパッケージ アジェンダ

4 Copyright © 2020, Oracle and/or its affiliates データ・ロードの準備 http://apexugj.blogspot.com/2020/07/data-loading-1.html

一般財団法人 コード・フォー・ジャパン 新型コロナウイルス感染症対策に関するオー プンデータ項目定義書 2020年5月25日更新版を参照 データセット名：陽性患者属性 詳しくは以下のサイトを参照してください。 [新型コロナウイルス感染症対策のための データ公開支援] https://www.code4japan.org/activity/stopc ovid19 データをロードする表の定義 Copyright © 2020, Oracle and/or its affiliates 5 01 陽性患者属性 No. 項目名 制約 説明 形式 入力例*1 参考ガイドライン等 備考 1No 推奨 患者を識別できるように、データセット 内で一意に定まる番号を設定し記載。 文字列（半角英数 字） 113 2 全国地方公共団体 コード 推奨 総務省の定める全国地方公共団体コード （6桁コード）を記載。 6桁に満たない場合、先頭は0埋めとする。 文字列（半角数字） 130001 総務省 > 全地方公共団体コード > 都道府県コード及び市区町村コード https://www.soumu.go.jp/denshijiti/ code.html 3都道府県名 必須 情報の管理主体である地方公共団体名に ついて、都道府県名を記載。 文字列 東京都 4市区町村名 必須 情報の管理主体である地方公共団体名に ついて、市区町村名を記載。 ※都道府県については記載不要。 文字列 5 公表_年月日 必須 患者情報を公表した年月日を記載。 日付（YYYY-MM- DD） 2020-03-20 政府CIOポータル > 標準ガイドライン > 行政基本情報データ連携モデル > 日付時刻 https://cio.go.jp/guides#renkeimode l 6発症_年月日 発症が確認された年月日を記載。 日付（YYYY-MM- DD） 2020-03-11 政府CIOポータル > 標準ガイドライン > 行政基本情報データ連携モデル > 日付時刻 https://cio.go.jp/guides#renkeimode l 7患者_居住地 推奨 患者の居住地を記載。 文字列 東京都 都道府県によって公表する情報の粒度 が異なるため、自由記述としている 8患者_年代 推奨 患者の年代を次のいずれかの値で記載。 （不明やその他公開できない場合は空欄 とする） {10歳未満, 10代, 20代, 30代, 40代, 50代, 60代, 70代, 80代, 90歳以上} 文字列 40代 人口の少ない地域で、特定の年代の人 が極端に少ない場合は、トップ（ボト ム）コーディングを行う（例：80代 と90代以上をまとめて「80代以上」 とする等） 9患者_性別 推奨 患者の性別。 次のいずれかの文字列で記載。（不明や その他公開できない場合は空欄とする） {男性, 女性, その他} 文字列 男性 10患者_職業 推奨 患者の職業を記載。（不明やその他公開 できない場合は空欄とする） 文字列 会社員 11患者_状態 推奨 患者の状態を次のいずれかの文字列で記 載。（不明やその他公開できない場合は 空欄とする） {無症状, 軽症, 中等症, 重症, 死亡} 文字列 重症 12 患者_症状 患者の症状を記載。 複数ある場合は「;」（半角のセミコロ ン）区切りで記載する。 文字列 咳;発熱 13 患者_渡航歴の有無フ ラグ 推奨 以下のいずれかの値を記載（数字は半 角） 海外渡航歴あり：1 海外渡航歴なし：0 不明：（空のまま） 数値（整数） 0 14患者_退院済フラグ 推奨 以下のいずれかの値を記載（数字は半 角） 退院済：1 入院中：0 不明：（空のまま） 数値（整数） 1 死亡退院の場合も退院済：1とする。 15 備考 備考があれば記載。 文字列 基礎疾患あり *1) 入力例はダミーデータであり、実際の自治体が公開するデータとは異なる

PATIENTS "No" number MUNICIPALITY_CODE number PREFECTURE_NAME vc16 /nn CITY_NAME vc40 PUBLISHED_DATE date ONSET_DATE date PATIENT_LOCATION vc200 PATIENT_AGE vc40 PATIENT_SEX vc40 PATIENT_OCCUPATION vc200 PATIENT_STATUS vc200 PATIENT_SYMPTOM vc200 PATIENT_TRAVEL_HISTORY number /check 1,0 PATIENT_LEFT_HOSPITAL number /check 1,0 REMARK vc200 クイックSQLによる定義 Copyright © 2020, Oracle and/or its affiliates 6 カラム名 属性名 No No MUNICIPALITY_NAME 全国地方公共団体コード PREFECTURE_NAME 都道府県名 CITY_NAME 市区町村名 PUBLISHED_DATE 公表_年月日 ONSET_DATE 発症_年月日 PATIENT_LOCATION 患者_居住地 PATIENT_AGE 患者_年代 PATIENT_SEX 患者_性別 PATIENT_OCCUPATION 患者_職業 PATIENT_STATUS 患者_状態 PATIENT_SYMPTOM 患者_症状 PATIENT_TRAVEL_HISTORY 患者_渡航歴の有無フラグ PATIENT_LEFT_HOSPITAL 患者_退院済フラグ REMARK 備考 テーブル名: COVID19_PATIENTS 設定 オブジェクト接頭辞: COVID19 主キー: 12c identity型 (トリガー生成を抑止) 自動主キー: OFF

アプリケーションの作成 Copyright © 2020, Oracle and/or its affiliates 7

ユーザー・インターフェース・デフォルトの適用 Copyright © 2020, Oracle and/or its affiliates 8

アプリケーションの完成 Copyright © 2020, Oracle and/or its affiliates 9

10 Copyright © 2020, Oracle and/or its affiliates Oracle APEX標準ツール データ・ワークショップを使ったインポート http://apexugj.blogspot.com/2020/07/data-loading-2.html

東京都のオープンデータ Copyright © 2020, Oracle and/or its affiliates 11 東京都 新型コロナウイルス感染症 対策サイト https://stopcovid19.metro.tokyo.lg.jp/

CSVファイルのダウンロード Copyright © 2020, Oracle and/or its affiliates 12

SQLワークショップに含まれる標準ユーティリティ データ・ワークショップ Copyright © 2020, Oracle and/or its affiliates 13 CSV, XLSX, XML, JSON形式のファイルのデータをロードすることができます。

CSVファイルのロード Copyright © 2020, Oracle and/or its affiliates 14

ロードしたデータの確認 Copyright © 2020, Oracle and/or its affiliates 15

16 Copyright © 2020, Oracle and/or its affiliates アプリケーションへの組み込み データ・ロード・ウィザード http://apexugj.blogspot.com/2020/07/data-loading-3.html

17 Copyright © 2020, Oracle and/or its affiliates データ・ロード・ウィザードの作成

データ・ロード・ウィザードを使ったデータ・ロード Copyright © 2020, Oracle and/or its affiliates 18

19 Copyright © 2020, Oracle and/or its affiliates データ・ロードのためのAPI APEX_DATA_PARSERパッケージの使用 http://apexugj.blogspot.com/2020/07/data-loading-4.html

表COVID19_MUNICIPALITIESの作成 Copyright © 2020, Oracle and/or its affiliates 20 "CODE","NAME" "10006","北海道" "20001","青森県" "30007","岩手県" "40002","宮城県" "50008","秋田県" "60003","山形県" "70009","福島県" "80004","茨城県" "90000","栃木県" "100005","群馬県" "110001","埼玉県" "120006","千葉県" "130001","東京都" "140007","神奈川県" "150002","新潟県" "160008","富山県" "170003","石川県" "180009","福井県" "190004","山梨県" "200000","長野県" "210005","岐阜県" "220001","静岡県" "230006","愛知県" "240001","三重県" "250007","滋賀県" "260002","京都府" "270008","大阪府" "280003","兵庫県" "290009","奈良県" "300004","和歌山県" "310000","鳥取県" "320005","島根県" "330001","岡山県" "340006","広島県" "350001","山口県" "360007","徳島県" "370002","香川県" "380008","愛媛県" "390003","高知県" "400009","福岡県" "410004","佐賀県" "420000","長崎県" "430005","熊本県" "440001","大分県" "450006","宮崎県" "460001","鹿児島県" "470007","沖縄県" alter table covid19_municipalities add content_blob blob; alter table covid19_municipalities add last_update_date timestamp with local time zone; alter table covid19_municipalities add file_name varchar2(200); alter table covid19_municipalities add content_url varchar2(400);

APEX_WEB_SERVICE.MAKE_REST_REQUEST(_B) Copyright © 2020, Oracle and/or its affiliates 21 APEX_WEB_SERVICE.MAKE_REST_REQUEST( p_url IN VARCHAR2, p_http_method IN VARCHAR2, p_username IN VARCHAR2 default null, p_password IN VARCHAR2 default null, p_scheme IN VARCHAR2 default 'Basic', p_proxy_override IN VARCHAR2 default null, p_transfer_timeout IN NUMBER default 180, p_body IN CLOB default empty_clob(), p_body_blob IN BLOB default empty_blob(), p_parm_name IN apex_application_global.VC_ARR2 default empty_vc_arr, p_parm_value IN apex_application_global.VC_ARR2 default empty_vc_arr, p_wallet_path IN VARCHAR2 default null, p_wallet_pwd IN VARCHAR2 default null, p_https_host IN VARCHAR2 default null, p_credential_static_id IN VARCHAR2 default null, p_token_url IN VARCHAR2 default null ) RETURN CLOB; パラメータ 説明 p_url WebサービスのURLエンドポイント。 p_http_method 使用するHTTPメソッド(PUT、POST、GET、HEADまたはDELETE)。 p_username このサービスに基本認証が必要な場合はユーザー名。 p_password このサービスに基本認証が必要な場合はパスワード。 p_scheme ご使用のデータベース・リリースでサポートされている場合、認証スキームのBasic (デフォルト)、AWS、Digest、またはOAUTH_CLIENT_CREDのいずれか。 p_proxy_override リクエストに使用するプロキシ。指定したプロキシは、アプリケーション属性で定 義したプロキシより優先されます。 p_transfer_timeout レスポンスを待機する時間(秒単位)。 p_body CLOBとして送信するHTTPペイロード。 p_body_blob バイナリBLOBとして送信するHTTPペイロード。ファイルのポストなど。 p_parm_name 名前/値ペアで使用するパラメータの名前。 p_parm_value 名前/値ペアで使用するパラメータの値。 p_wallet_path URLエンドポイントがHTTPSの場合はウォレットへのファイル・システム・パス。 file:/usr/home/oracle/WALLETSなど。指定されたウォレットのパスは、インスタ ンス設定で定義したウォレットより優先されます。 p_wallet_pwd ウォレットにアクセスするためのパスワード。 p_https_host HTTPS要求に対してリモート・サーバーの証明書の共通名(CN)と照合するホスト名。 p_credential_static_id 使用するWeb資格証明の名前(共有コンポーネントに構成)。 p_token_url トークンベースの認証フロー(OAuth2など): トークンの取得元のURL。 https://docs.oracle.com/cd/F23071_01/aeapi/MAKE_REST_REQUEST-Function.html 末尾にに_BがあるAPIは返り値の型がBLOB

APEX_WEB_SERVICEパッケージを使用したCSVデータの取得 Copyright © 2020, Oracle and/or its affiliates 22 declare l_data blob; l_data_len number; begin l_data := apex_web_service.make_rest_request_b( p_url => 'https://stopcovid19.metro.tokyo.lg.jp/data/130001_tokyo_covid19_patients.csv', p_http_method => 'GET' ); dbms_output.put_line('Content Length is ' || dbms_lob.getLength(l_data)); end; URLとHTTPのメソッド(GET)の指定のみでデータ の取得が完了

取得したデータの保存 Copyright © 2020, Oracle and/or its affiliates 23 declare l_url covid19_municipalities.content_url%type; l_file_name covid19_municipalities.file_name%type; begin l_url := 'https://stopcovid19.metro.tokyo.lg.jp/data/130001_tokyo_covid19_patients.csv'; l_file_name := '130001_tokyo_covid19_patients.csv'; update covid19_municipalities set content_blob = apex_web_service.make_rest_request_b(l_url, 'GET'), content_url = l_url, file_name = l_file_name, last_update_date = systimestamp where name = '東京都'; end; UPDATE文一行で取得したデータの保存が完了

データのリフレッシュ Copyright © 2020, Oracle and/or its affiliates 24 update covid19_municipalities m set m.content_blob = apex_web_service.make_rest_request_b(m.content_url, 'GET'), m.last_update_date = systimestamp where m.name = '東京都'; (URLに変更が無ければ) UPDATE文一行で取得したデータの更新が完了

APEX_DATA_PARSER.PARSE Copyright © 2020, Oracle and/or its affiliates 25 function parse( p_content in blob, p_file_name in varchar2 default null, p_file_type in t_file_type default null, p_file_profile in clob default null, p_detect_data_types in varchar2 default 'Y', p_decimal_char in varchar2 default null, p_xlsx_sheet_name in varchar2 default null, p_row_selector in varchar2 default null, p_csv_row_delimiter in varchar2 default LF, p_csv_col_delimiter in varchar2 default null, p_csv_enclosed in varchar2 default '"', p_skip_rows in pls_integer default 0, p_add_headers_row in varchar2 default 'N', p_file_charset in varchar2 default 'AL32UTF8', p_max_rows in number default null, p_return_rows in number default null, p_store_profile_to_collection in varchar2 default null ) return wwv_flow_t_parser_table pipelined; パラメータ 説明 P_CONTENT BLOBとして解析されるファイル・コンテンツ P_FILE_NAME ファイル・タイプの導出にのみ使用されるファイルの名前。P_FILE_NAME、P_FILE_TYPEまた はP_FILE_PROFILEのいずれかを渡す必要があります。 P_FILE_TYPE 解析するファイルのタイプ。ファイル・タイプを明示的に渡す場合に使用します。 P_FILE_NAME、P_FILE_TYPEまたはP_FILE_PROFILEのいずれかを渡す必要があります。 P_FILE_PROFILE 解析に使用されるファイル・プロファイル。ファイル・プロファイルは、以前のPARSE()の呼出 しで計算されている可能性があります。再度渡した場合、このファンクションは、パフォーマン スを改善するために、一部のプロファイル検出ロジックをスキップして、渡されたプロファイル を使用します。 P_DETECT_DATA_TYPES 解析中にデータ型(NUMBER、DATE、TIMESTAMP)を検出するかどうか。'Y'に設定すると、この ファンクションはファイル・プロファイルを計算し、そのプロファイルにデータ型情報を追加し ます。'N'に設定すると、データ型は検出されず、すべての列がVARCHAR2になります。デフォル トは'Y'です。 P_DECIMAL_CHAR NUMBERデータ型を検出する場合は、この小数点文字を使用します。指定しない場合、プロシー ジャで小数点文字が自動検出されます。 P_XLSX_SHEET_NAME XLSXワークブックの場合。解析するワークシートの名前。省略した場合、このファンクションは 最初に検出されたワークシートを使用します。 P_ROW_SELECTOR JSONおよびXMLファイルの場合。JSONまたはXMLファイル内の行の配列/リストへのポインタ。 省略した場合、このファンクションは次のように処理します。 •XMLファイルの場合: "/*/*" (ルート・タグの下の最初のタグ)を行セレクタとして使用します。 •JSONファイルの場合: JSON配列を探し、最初に見つかった配列を使用します。 P_CSV_ROW_DELIMITER CSV解析のデフォルトの行デリミタをオーバーライドします。 P_CSV_COL_DELIMITER 特定のCSV列デリミタを使用します。省略した場合、ファンクションは最初の行の内容に基づい て列デリミタを検出します。 P_CSV_ENCLOSED CSV解析のデフォルトの囲み文字をオーバーライドします。 P_SKIP_ROWS 解析時に最初のN行をスキップします。 P_ADD_HEADERS_ROW XML、JSONの場合。列ヘッダー(タグ、属性名)を最初の行として表示します。 P_FILE_CHARSET ファイル・エンコーディング(UTF-8 (AL32UTF8)以外の場合)。 P_MAX_ROWS P_MAX_ROWSが返された後、解析を停止します。 P_RETURN_ROWS 返される行数。これはパーサーが返す予定の行数よりも多くの行が解析されそうな(データ型検出 のため)場合に役立ちます。指定された行数が出力されると、このファンクションは、 P_MAX_ROWSに達するまで、またはSQL問合せのROWNUM < x句に到達して実行が停止される まで、解析(および検出されたデータ型の調整)を続行します。 P_STORE_PROFILE_TO_COLLECTION 解析時に計算されたファイル・プロファイルをコレクションに格納します。コレクションが存在 する場合は、クリアされます。計算されたプロファイルにのみ使用できます。 https://docs.oracle.com/cd/F23071_01/aeapi/PARSE-Function.html

CSVファイルの表形式への変換 Copyright © 2020, Oracle and/or its affiliates 26 select * from apex_data_parser.parse( p_content => apex_web_service.make_rest_request_b( p_url => 'https://stopcovid19.metro.tokyo.lg.jp/data/130001_tokyo_covid19_patients.csv', p_http_method => 'GET' ), p_file_name => 'file_is.csv', p_skip_rows => 1 ) SELECT文一行で、表形式への変換が完了

APEX_DATA_PARSERパッケージによるCSVデータのロード Copyright © 2020, Oracle and/or its affiliates 27 insert into covid19_patients( "No", municipality_code, prefecture_name, published_date, patient_location, patient_age, patient_sex, patient_left_hospital ) select to_number(col001), to_number(col002), col003, to_date(col005, 'YYYY-MM-DD'), col008, col009, col010, to_number(col016) from apex_data_parser.parse( p_content => (select content_blob from covid19_municipalities where name = ‘東京都’), p_file_name => 'file_is.csv', p_skip_rows => 1 ) where col001 is not null; INSERT文一行で、データの取得と表への挿入が完了

新たなCSVのデータによる表の更新 Copyright © 2020, Oracle and/or its affiliates 28 merge into covid19_patients p using ( select to_number(col001) "No", to_number(col002) municipality_code, col003 prefecture_name, to_date(col005, 'YYYY-MM-DD') published_date, col008 patient_location, col009 patient_age, col010 patient_sex, to_number(col016) patient_left_hospital from apex_data_parser.parse( p_content => (select content_blob from covid19_municipalities where name = ‘東京都’), p_file_name => 'file_is.csv', p_skip_rows => 1 ) where col001 is not null ) n on (p."No" = n."No" and p.prefecture_name = n.prefecture_name) when matched then update set p.published_date = n.published_date, p.patient_location = n.patient_location, p.patient_age = n.patient_age, p.patient_sex = n.patient_sex, p.patient_left_hospital = n.patient_left_hospital when not matched then insert( "No", municipality_code, prefecture_name, published_date, patient_location, patient_age, patient_sex, patient_left_hospital ) values ( n."No", n.municipality_code, n.prefecture_name, n.published_date, n.patient_location, n.patient_age, n.patient_sex, n.patient_left_hospital );

ありがとうございました！ 29 Copyright © 2020, Oracle and/or its affiliates

No content