SQL Developer Web で Autonomous Database にローカルファイル(xlsx)をロードする
この記事は「Oracle Cloud Infrastructure Advent Calendar 2019 - Adventar」の12月1日の記事として書かれています。
記事の内容は執筆時(2019/12/01)のものであり、現在とは異なる可能性がありますので適宜最新状況をご確認ください。
Autonomous Database へデータをロードする方法はいくつかありますが、一番手っ取り早そうな SQL Developer Web によるロードをご紹介します。
SQL Developer Web は、SQL Developer をご利用されてる方は、SQL Developer のブラウザ版だと思って頂ければ、理解が早いです。
ブラウザ環境さえあれば、SQLの実行が可能になり、Autonomous Database では、プロビジョニング直後から利用可能です。
もちろん Always Free で作成した Autonomous Database でも利用可能です。
前置き
本記事では、Autonomous Database 自体の作成やSQL Developer Web の接続方法は含みません。
SQL Developer Web で Autonomous Database 上の既存表へエクセルファイル(xlsx)をロードするために、必要なファイルと表の準備から紹介しています。
まずはマニュアル
読まなくても直感で使えますが、念のため本記事で紹介する機能のマニュアルを置いておきます。
マニュアルは、ADW/ATP で分かれてますが、"Load Data from Local Files with SQL Developer Web" という章でどちらも内容は同じです。
Using Oracle Autonomous Data Warehouse
docs.oracle.com
Using Oracle Autonomous Transaction Processing
docs.oracle.com
ロードするデータの準備
今回は、日本オラクルの事業所一覧を元に、データを作成しました。
www.oracle.com
下記のタブ区切り文字列をコピー、Excelに貼りつけて保存してください。
私は、日本オラクル事業所一覧.xlsxという名前で保存しました。
事業所名 郵便番号 所在地 電話番号 本 社 107-0061 東京都港区北青山2-5-8 オラクル青山センター 03-6834-6666 北海道オフィス 060-0001 札幌市中央区北一条西4-1-2 武田りそなビル 6F 011-252-6500 東北オフィス 980-0014 宮城県仙台市青葉区本町1-1-1 大樹生命仙台本町ビル 18F 022-212-3303 東海オフィス 453-6111 愛知県名古屋市中村区平池町4-60-12 グローバルゲート名古屋 11F 052-551-7511 北陸オフィス 920-0901 石川県金沢市彦三町1-2-1 アソルティ金沢彦三 1F リージャス金沢駅東センター 関西オフィス 530-0003 大阪府大阪市北区堂島2-4-27 新藤田ビル 9F 06-6442-6777 中国・四国オフィス 730-0051 広島市中区大手町2-8-1 大手町スクエア 8F 082-249-3371 九州オフィス 810-0001 福岡県福岡市中央区天神1-12-7 福岡ダイヤモンドビル 8F 092-736-0150 沖縄オフィス 900-0033 沖縄県那覇市久米2-3-15 COI那覇ビル 5F リージャス COI那覇ビルセンター 098-916-8353 赤坂オフィス 107-0051 東京都港区元赤坂1-3-13 赤坂センタービルディング 03-6834-6666 豊田オフィス 471-0833 愛知県豊田市山之手4-46 三井住友海上豊田ビル5F 0565-74-1600 トレーニングキャンパス大阪 530-0003 大阪府大阪市北区堂島2-4-27 新藤田ビル 06-6442-6172 トレーニングキャンパス赤坂 107-0051 東京都港区元赤坂1-3-13 赤坂センタービルディング12F 0120-155-092
北陸オフィスには、オフィス用の電話番号はないのですね。
例では、日本オラクル事業所一覧.xlsxという名前で保存しました。
ロード対象の表を準備
今回は初めてのデータ投入なので、OCJ_Officeという表を作成します。
CREATE TABLE OCJ_Office( OfficeName varchar2(60), ZipCode varchar2(8), Address varchar2(120), OfficeTEL varchar2(12) );
SQL Developer であれば、新規表もロード時に作成できますが、SQL Developer Web ではロード先の表を事前に作成しておく必要があります。
SQL Developer Web でローカルファイル(xlsx)をロード
準備したデータと表を使って SQL Developer Web で Autonomous Database にロードしていきます。
表を決めて、ファイルの選んで、表とファイルの列を結びつけておしまいです。
- ロードしたい表を右クリックしてデータをアップロードを選択
- インポートウィザードがブラウザ内に開く
- ロードしたいデータが入ったファイルを指定するとプレビュー画面へ
- ⚙を押すとスキップする行など選択も可能
- 表のどの列にファイルのどの列を対応させるか選択
- 1つ1つやるのは、面倒なので、⚙を押して、位置を選択
- 今回、列の並びは同じなのでマッピング完了
- 確認画面で良ければ、終了を教えてジョブ実行
- この画面は実行されたことがわかるだけなので、OK押しましょう。
- フッターに完了した旨通知されます。
- SELECT するとデータが入っているのが確認できます。日本語もできてますね。
SELECT * FROM OCJ_Office;
ADB Schema Advisor の利用
この記事は「Oracle Cloud その2 Advent Calendar 2018 - Adventar」の12月8日の記事として書かれています。
記事の内容は執筆時(2018/12/08)のものであり、現在とは異なる可能性がありますので適宜最新状況をご確認ください。
自律型データベース・クラウドサービスの1つである Autonomous Data Warehouse Cloud Service では、特定のデータベースオブジェクトの作成が制限されています。
Autonomous Data Warehouse for Experienced Oracle Database Users
経験豊富なOracle DatabaseユーザーのためのAutonomous Data Warehouse*1
制限対象のオブジェクトが入っているかどうかは、現行データベースで DBA_OBJECTS の OBJECT_TYPE 列などを見ていけば確認できます。
ただ、これらの作業は少し煩雑であるため、調査用に Schema Advisor*2 というパッケージを用意しています。
今回は、このパッケージの使い方を紹介したいと思います。
なお、パッケージの入手には、My Oracle Supportへのログインが必要になります。
- Autonomous Data Warehouse Schema Advisor って何?
- パッケージのダウンロード
- ユーザー作成と権限付与
- パッケージのインストール
- 実行と結果確認
- サポートしてないオブジェクトがある場合
- お掃除
- まとめ
- 追加更新<2020-01-10>
Autonomous Data Warehouse Schema Advisor って何?
Autonomous Data Warehouse にオブジェクトを移行する際に、制限事項に抵触しているオブジェクトの有無を調べるパッケージです。
スキーマを指定することで、対象スキーマのオブジェクトが、移行可能、不可なオブジェクトの件数やその理由を出力してくれるツールです。
パッケージのダウンロード
まずは、パッケージ(adw_advisor.plb)を入手し、調査対象環境で実行できるところに配置しておきます。
NOTE:2462677.1 から Oracle Autonomous Database Schema Advisor のパッケージをダウンロードしてください*3
Oracle Autonomous Data Warehouse Schema Advisor (Doc ID 2462677.1)
support.oracle.com
$ ls -l adw_advisor.plb -rw-rw-r-- 1 oracle oracle 5962 Sep 28 08:10 adw_advisor.plb
最新版は9月下旬にリリースされたもので、6k弱と非常にコンパクトです。
ユーザー作成と権限付与
必要に応じて、パッケージをインストールするユーザーと必要な権限を付与していきます。
同じ権限を保有しているものがあれば、既存ユーザーを利用することもできます。
今回は、12.1.0.2 の pdb 環境(testpdb1)で、新規に ADW_ADVISOR というユーザーを作成し、調査に必要な権限を付与していきます。
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 8 10:29:32 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options SQL> alter session set container = testpdb1; Session altered. SQL> CREATE USER ADW_ADVISOR IDENTIFIED by ADW_ADVISOR; User created. SQL> GRANT CREATE SESSION, CREATE PROCEDURE TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_USERS TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_OBJECTS TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_TABLES TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_INDEXES TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_NESTED_TABLES TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_MVIEW_LOGS TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_TYPES TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_CONSTRAINTS TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_TAB_COLS TO ADW_ADVISOR; Grant succeeded. SQL> GRANT SELECT ON DBA_EXTERNAL_TABLES TO ADW_ADVISOR; Grant succeeded. -- 12.1 以降の場合にはゾーンマップも。 SQL> GRANT SELECT ON DBA_ZONEMAPS TO ADW_ADVISOR; Grant succeeded.
パッケージのインストール
次に、調査用のスキーマにパッケージをインストールします。
インストールと言っても、ダウンロードしていた adw_advisor.plb を実行するだけです。
$ sqlplus ADW_ADVISOR/ADW_ADVISOR@testpdb1 SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 8 10:51:51 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options SQL> @adw_advisor.plb Package created. Package body created. -- 一応エラーチェック SQL> show errors; No errors. SQL> desc adw_advisor PROCEDURE REPORT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- I_SCHEMA VARCHAR2 IN
準備はこれで完了です。
実行と結果確認
実行は、調査対象のスキーマをadw_advisor.report('<スキーマ名>') に指定するだけです。
-- SH スキーマを調べた結果 SQL> set serveroutput on SQL> set linesize 95 -- default の 80 だと少しレイアウトが崩れるのでおまじないで 95 以上に。 SQL> exec adw_advisor.report('SH'); ========================================================================================== == SCHEMA MIGRATION REPORT (SH) ========================================================================================== ------------------------------------------------------------------------------------------ -- MIGRATION SUMMARY ------------------------------------------------------------------------------------------ Object Type Object Count ADW Object Count Objects Not Migrated ------------------------- -------------------- -------------------- -------------------- INDEX PARTITION 196 196 0 TABLE PARTITION 56 56 0 DIMENSION 5 5 0 MATERIALIZED VIEW 2 2 0 VIEW 1 1 0 TABLE 17 17 0 INDEX 26 26 0 CONSTRAINT 146 146 0 ------------------------------------------------------------------------------------------ -- OBJECTS NOT MIGRATED ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ -- OBJECTS MIGRATED WITH CHANGES ------------------------------------------------------------------------------------------ Index Organized Tables (Total=1) -------------------------------- Note: IOT tables are not supported in ADW but will migrate as regular tables when transformed by Data Pump using 'dwcs_cvt_iots:y'. DR$SUP_TEXT_IDX$K DR$SUP_TEXT_IDX$N External Tables (Total=1) ------------------------- Note: External tables will migrate as regular tables. Please re-architect them to use a cloud storage such as Oracle Object Store or Amazon S3 instead of a regular file system. SALES_TRANSACTIONS_EXT PL/SQL procedure successfully completed.
MIGRATION SUMMARY に対象スキーマのオブジェクト数と移行可否オブジェクトの数が出力されます。
サポートしていないものの対応策があるものについては、OBJECTS MIGRATED WITH CHANGES の項目内で簡単なガイドをしてくれます。
サポートしてないオブジェクトがある場合
Database Link(TEST_LINK) や LONG型のテーブル(LONG_TABLE)を作成したスキーマでテストした例
SQL> exec adw_advisor.report('ASATEST'); ========================================================================================== == SCHEMA MIGRATION REPORT (ASATEST) ========================================================================================== ------------------------------------------------------------------------------------------ -- MIGRATION SUMMARY ------------------------------------------------------------------------------------------ Object Type Object Count ADW Object Count Objects Not Migrated ------------------------- -------------------- -------------------- -------------------- DATABASE LINK 1 0 1 TABLE 1 0 1 ------------------------------------------------------------------------------------------ -- OBJECTS NOT MIGRATED ------------------------------------------------------------------------------------------ Tables with LONG/LONG RAW Columns (Total=1) ------------------------------------------- Note: LONG/LONG RAW columns are not supported in ADW. Consider changing the column type to LOB as it's designed to handle large data. LONG_TABLE Database Links Not Migrated (Total=1) ------------------------------------- Note: Database Links are currently not allowed in ADW. Consider alternatives to integrating data from other Oracle Databases. TEST_LINK ------------------------------------------------------------------------------------------ -- OBJECTS MIGRATED WITH CHANGES ------------------------------------------------------------------------------------------ <NONE> PL/SQL procedure successfully completed.
対象となるオブジェクトと理由がOBJECTS NOT MIGRATED 項目に出力されます。
データベース統合等、別の方法でDatabase Link を削除できないか、LONG型データの内容の精査し、変更可能有無などを判断してください。
お掃除
使い終わったら消しておきましょう。
-- 管理者ユーザーにて SQL> drop user adw_advisor cascade; User dropped. -- or -- -- パッケージをインストールしたユーザーにて SQL> DROP PACKAGE ADW_ADVISOR; Package dropped.
まとめ
今回は、Autonomous Data Warehouse Cloud Service を利用する前の事前チェックスクリプトを紹介しました。
サービス提供当初は、索引やパーティション、マテリアライズドビューについても制限事項に含まれておりましたので、チェックが今よりもだいぶ煩雑でした。
現状はだいぶ規制が緩まったので、チェック項目は少なくなっていますが、自家製スクリプトの抜け漏れリスクや作成・修正コストを考えるとサポートから提供されているツールを利用するのが、賢い選択ではないかと思います。
今日は、以上です。
追加更新<2020-01-10>
記載当時は、Autonomous Data Warehouse(ADW)だけでしたが、Autonomous Transaction Processing(ATP)にも対応しています。
そのため、NOTEやパッケージ名も Autonomous Database(ADB)に変わっています。この記事もタイトルだけ ADW から ADB に変更しました。
当時サポートしてなかった Database Link にも対応しています。
10.2以降で利用できるのでぜひご利用ください。