PowerPoint Presentation

Size: px

Start display at page:

Download "PowerPoint Presentation"

いおりしのしま
5 years ago
Views:

1 Oracle Direct Seminar <Insert Picture Here> 意外と知らない?! データローディングの基礎日本オラクル株式会社

2 Agenda データロードの種類 SQL*Loader を使用したデータロード外部表を使用したデータロード無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談パフォーマンスクリニック Oracle 構成相談 Oracle Database 高可用性診断システム連携アセスメントシステムセキュリティ診断簡易業務診断メインフレーム資産活用 2

3 データロードの種類 1. 中間ファイルを利用するもの Data Import/Export Data Pump SQL*Loader 外部表トランスポータブル表領域 ( 参考 ) 中間ファイル 2. ネットワークを利用するもの ( 参考 ) DB Link + SQL Oracle Transparent Gateway Oracle Data Integrator Oracle Golden Gate 3

4 中間ファイルを利用するデータロード Data Import/Export Data Pump クライアントサーバ Export Utility EXP xxx.dmp Import Utility IMP Export Utility EXPDP データの抽出 xxx.dmp サーバデータの抽出サーバデータのロードサーバ転送データのロード Import Utility IMPDP xxx.dmp SQL*Loader 外部表 +SQL サーバ外部ファイル SQL*Loader データのロード INSERT INTO. SELECT * FROM 外部ファイルネットワーク経由も可ロード先表外部表 4

5 参考 : トランスポータブル表領域 (TTS) Enterprise Edition 表領域の情報を抜き出すデータファイルをコピーする転送コピー元の表領域を読みとり専用に xxx.dmp データの抽出 xxx.dmp Import Utility IMPDP (IMP) xxx.dbf Export Utility EXPDP (EXP) データのロード Oracle Database Oracle Database 5

6 中間ファイルを利用するデータロード ( 特徴 ) ソース主な用途利用可能な Version 実行形態ファイル形式 Data Export/Import Data pump SQL*Loader 外部表 Oracle DB Oracle DB 外部ファイル外部ファイル /Oracle DB 論理 Backup ツール論理 Backup ツールテキストローディングテキストローディング Datapump ローディング Version5.X 以前 ~ 10g~ Version6.X 以前 ~ 9i~ 10g~( アンロード ) Client 側で実行可 (Net 経由 ) 独自 Server 側で実行独自 (Data Exp/Imp と互換性はなし ) Client 側で実行可 (Net 経由 ) テキスト ( 固定長 / 可変長 ) Server 側で実行テキスト ( 固定長 / 可変長 ) 独自 (DataPump) 本セミナー内容 6

7 参考 :Data Export/Import と Data Pump Data Import/Export クライアントサーバ Data Pump サーバ Export Utility EXP xxx.dmp データの抽出 Import Utility IMP サーバデータのロードサーバ Export Utility EXPDP データの抽出 xxx.dmp 転送データのロード Import Utility IMPDP xxx.dmp 特徴 : ダンプファイルはクライアント側に格納される Data Pump Import/Export と比べた場合比較的低速 Oracle Database 10g 以降は Data Pump Import/Export の使用を推奨特徴 : ダンプファイルはサーバ側に格納される 9i までの exp/imp と同等の機能 + 更なる付加機能を持つ新たな機能を搭載 Exp/imp に比べ数倍高速パラレル処理が実行可能 exp/imp と Data Pump でダンプファイルの互換性はない 7

8 Agenda はじめにデータロードの種類 SQL*Loaderを使用したデータロード外部表を使用したデータロード無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談パフォーマンスクリニック Oracle 構成相談 Oracle Database 高可用性診断システム連携アセスメントシステムセキュリティ診断簡易業務診断メインフレーム資産活用 8

9 SQL*Loader を使用したデータロードデータのロード外部ファイル (text,csv) SQL*Loader Oracle Database SQL*Loader を使用することで外部ファイルのデータを Oracle データベースの表にロードすることが可能 9

10 SQL*Loader の特徴柔軟性 / 高速性に応じた 2 つのモードを用意従来型パスモードダイレクトパスモード様々な形式 ( 固定長や可変長 ) 様々な文字コード (EUC,SJIS) の外部データの取り込みが可能同時に複数の表へロードが可能複数のデータファイルから一度に同じ表へロードが可能 10

11 SQL*Loader に関するファイルデータファイルロードするデータが格納されたファイル制御ファイルデータの場所データの挿入先データの分析などが記述されたファイル SQL*Loader データベースログファイルロード件数やロード中に発生したエラーログなどを記録するファイル不良ファイルエラーによりロードされなかったレコードを記録するファイル廃棄ファイルエラー以外の理由によりロードされなかったレコードを記録するファイル 11

12 従来型パスロード SQL コマンドの生成 SQL*Loader 従来型パスロードではテキストデータを読み取りながら内部的に INSERT を発行 SGA データベースバッファキャッシュキャッシュ上のブロックへの書き込み REDO ログバッファ共有プール Insert と同程度の時間がかかるディスクへの書き込み 12

13 ダイレクトパスロードブロックイメージを作成 SQL*Loader バッファキャッシュを迂回し表に割り当てられたエクステントに直接ブロックが保存されるデータベースバッファキャッシュ REDOログバッファ共有プール制限事項例 :) ロードする間テーブルにロックがかかる参照整合性制約トリガーなどディスクへの書き込み 13

14 従来型パスロード VS ダイレクトパスロード 1 回あたりのロード時間従来型パスロードのロード時間を 1 とした場合の相対処理時間 ( 実際の処理時間に任意の数を掛けています ) 資料中に掲載されている検証内容は特定環境における検証結果についての報告でありすべての環境において同様の動作を保証するものではありませんので予めご了承くださいダイレクトパスロード従来型パスロードダイレクトパスロードは従来型パスロードと比べ非常に高速従来型パスロード 1 ダイレクトパスロード ,000 レコードロードの結果 14

15 参考 :SQL*Loader の使用方法 1. 外部ファイルを準備する例 :emp.dat, emp.csv,emp.txt 2. 制御ファイルの作成 vi エディタ等で制御ファイルを作成 < 制御ファイルサンプル samp1.ctl> 1 OPTIONS( 2 DIRECT = TRUE, 3 ) 4 5 LOAD DATA 6 INFILE emp.dat' 7 INTO TABLE tab_samp1 8 APPEND 9 FIELDS TERMINATED BY ',' 10 (empno,ename) emp.dat ,ALLEN ,WARD ,JONES モードを選択例 : ダイレクトパスロードを使用する場合 15

16 参考 :SQL*Loader の使用方法 3. SQL*Loader の実行 % sqlldr scott/tiger control=samp1.ctl SQL*Loader: Release Production on 水 Sept 1 21:14: (c) Copyright 1982, 2009, Oracle and/or its affiliates. All rights reserved. コミットポイントに達しました - 論理レコード件数 3 4. ロード結果の確認 1 SQL> select * from tab_samp1; 2 EMPNO ENAME Yamada 5 2 Suzuki 6 3 Tanaka 16

17 パラレルダイレクトパスロード複数のセッションを同時に実行して同一の表またはパーティション表の同一パーティションに対してダイレクトパスロードを実行パラレル処理ダイレクトパスロードのパフォーマンスが向上シリアル処理待機 P1 P2 P3 P4 P1 17

18 参考 : パラレルダイレクトパスロード設定方法 1. ローディング用ファイルを準備する例 :emp1.dat, emp2.dat, emp3.dat 並列化させるセッション毎にそれぞれロードする外部ファイルを準備する 2. 制御ファイルをそれぞれのセッション毎に作成 < 制御ファイルサンプル samp1.ctl> 1 OPTIONS( 2 DIRECT = TRUE, 3 PARALLEL = TRUE 4 ) 5 LOAD DATA 6 INFILE emp1.dat' 7 INTO TABLE tab_samp1 8 APPEND 9 FIELDS TERMINATED BY ',' 10 (empno,ename) < 制御ファイルサンプル samp2.ctl> PARALLEL オプションを TRUE に設定することでパラレル化を有効化もしくはコマンドプロンプトから有効化 sqlldr USERID=scott/tiger CONTROL=samp1.ctl DIRECT=TRUE PARALLEL=true sqlldr USERID=scott/tiger CONTROL=samp2.ctl DIRECT=TRUE PARALLEL=true sqlldr USERID=scott/tiger CONTROL=samp3.ctl DIRECT=TRUE PARALLEL=true 18

19 SQL*Loader ( ダイレクトパスモード ) の考慮点ローディング中はテーブル全体がロック検索処理は可能だが更新処理は待たされるデータを加工しながらローディングできない一度ワークテーブルを用意する必要がある運用が複雑インデックスのメンテナンス手順パラレルでロードする場合複数の制御ファイルを作成するのが面倒パラレルローディングの指定が面倒 etc. 19

20 従来型パスロードとダイレクトパスロードの使い分け従来型パスロードを使用するケースロードと並行して表にアクセスする場合参照整合性制約および列チェック整合性制約のある大きな表に比較的少数の行をロードする場合ダイレクトパスロードを使用するケース従来型パスロードと比較すると非常に高速なので短時間でデータをロードしたい場合 20

21 Agenda はじめにデータロードの種類 SQL*Loaderを使用したデータロード外部表を使用したデータロード無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談パフォーマンスクリニック Oracle 構成相談 Oracle Database 高可用性診断システム連携アセスメントシステムセキュリティ診断簡易業務診断メインフレーム資産活用 21

索引も作成できない 2 種類のアクセスドライバ ORACLE_LOADER アクセスドライバ ORACLE_DATAPUMP アクセス

22 外部表とは外部ファイル (CSV ファイル等 ) をデータベース内に格納されているテーブルのように扱うことが可能 CSV ファイルの各列のマッピングをテーブル作成時に定義 DML 操作 (UPDATE INSERT または DELETE) は実行できず索引も作成できない 2 種類のアクセスドライバ ORACLE_LOADER アクセスドライバ ORACLE_DATAPUMP アクセスドライバ外部表ディレクトリオブジェクト SELECT * FROM Oracle Database 外部ファイル (text,csv etc.) 22

23 アクセスドライバ : ORACLE_LOADER と ORACLE_DATAPUMP ORACLE_LOADER 外部表ディレクトリオブジェクト ORACLE_DATAPUMP ディレクトリオブジェクトディレクトリオブジェクト転送アンロード.dmp.dmp 外部ファイル (.txt,.csv) 内部表外部表外部表特徴 : 9i から使用可能ロードのみ可能データはテキストデータファイルからロードデフォルトで使用 CSV ファイル等のデータをロードする場合に利用特徴 : 10g から使用可能アンロードロードが可能データはダンプファイルからロード ( 注 : 外部表のアンロードで作成されるダンプファイルと Data pump で作成されるダンプファイルは異なります ) 別の Oracle Database 内にあるデータをロードする場合に利用 23

24 外部表の作成 (ORACLE_LOADER) 1. 外部ファイルを準備する例 :emp.dat, emp.csv,emp.txt 2. ディレクトリオブジェクトの作成外部ファイルがあるディレクトリの場所を DB へ登録する SQL> CREATE DIRECTORY load_src as C: Documents and SQL> Settings My Documents'; 3. ディレクトリオブジェクトの読み込み書き込み権限を付与 SQL> GRANT READ ON DIRECTORY load_src TO scott; SQL> GRANT WRITE ON DIRECTORY load_src TO scott; 24

25 外部表の作成 (ORACLE_LOADER) 4. 外部表の作成 SQL> CREATE TABLE emp_ext ( empno NUMBER, ename varchar2(10) : ) ORGANIZATION EXTERNAL ( DEFAULT DIRECTORY load_src ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',') LOCATION ( emp.dat') ) 外部表の宣言ロードするデータファイルの指定 25

26 外部表の作成 (ORACLE_LOADER) 5. 通常の表へのアクセスと同様に SQL 文を実行する SQL> select empno,ename from emp_ext; EMPNO ENAME SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 11 行が選択されました 26

27 外部表 - アンロード (ORACLE_DATAPUMP) 外部表の機能を利用してデータのアンロードが可能 9i でも利用できた外部表の構文でデータ型には ORACLE_DATAPUMP を指定 SQL の問い合わせを記述することで自由にアンロード可能 SQL> create table dept_external1 organization external ( type oracle_datapump default directory dpump_dir location('dept.dmp') ) as select * from dept; ORACLE_DATAPUMP を指定 AS SELECT ~ でアンロードする対象を記述 DPUMP_DIR で指定されたディレクトリ下に dept.dmp というダンプファイルが作成される 27

28 参考 : 外部表のエラー処理データファイルと外部表の間で変換エラーが発生するとエラーの発生した行は無視される SQL*Loader 同様エラーログやエラーでロードされなかったデータはログファイル不良ファイルおよび廃棄ファイルに記録されるログファイル不良ファイルおよび廃棄ファイルはディレクトリオブジェクト内に出力される SELECT * FROM EMP; 変換エラー EMP 表 28

29 外部表を使用したデータロード INSERT INTO SELECT 文や CREATE TABLE AS SELECT 文を利用して外部表を読み込みながらロード先のターゲットとなるテーブルにデータをロードする方法 SQL*Loader と異なり SQL でロードするデータの選択が可能ディレクトリオブジェクト INSERT INTO EMP SELECT * FROM EXT_EMP; EMP 表 ( ロード先表 ) EXT_EMP 表 ( 外部表 ) 外部ファイル (text,csv etc.) 29

30 外部表を利用したデータのロード 1. 外部表を作成する SQL>CREATE TABLE emp_ext ( empno NUMBER, ename varchar2(10) : ) ORGANIZATION EXTERNAL ( DEFAULT DIRECTORY load_src ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',') LOCATION ( emp.dat') ) / 2.INSERT.Select 文外部表としてテキストデータを読み込みながらデータをロード SQL> INSERT /*+ APPEND */ INTO EMP SELECT * FROM EMP_EXT; 30

31 外部表でのパラレルロード外部表を利用した場合でもパラレルでのロードが可能 1 CREATE TABLE emp_ext 2 ( empno NUMBER 3, ename varchar2(10) 4 : 5 ) 6 ORGANIZATION EXTERNAL 7 ( DEFAULT DIRECTORY load_src 8 ACCESS PARAMETERS 9 ( RECORDS DELIMITED BY NEWLINE 10 FIELDS TERMINATED BY ',') 11 LOCATION ( emp.dat') 12 ) 13 PARALLEL 2 PARALLEL 句で並列度を指定 SQL 文のヒント句を使用した並列化も可能ロード元のソースデータの文字コードがACIIとUTF-8の場合ソースファイルを分割することなく Oracle 側が自動分割して並列に読み込む Shift_JISやEUC-JPでCSV 形式のような可変長データの場合は並列度と同じ数だけファイルを分割する必要がある 31

32 圧縮ファイルでの外部表 11gR2 新機能圧縮ファイル形式などのテキストファイル以外のデータも外部表としてロードが可能外部表に読み取るデータファイルの前処理 (Preprocessor) を行うプログラムを指定して実行解凍とロードを一括して行えるのでディスクスペースを節約 1 CREATE TABLE sales_external 2 (...) 3 ORGANISZATION EXTERNAL 4 ( TYPE ORACLE_LOADER 5 DEFAULT DIRECTORY data_dir1 6 ACCESS PARAMETERS 7 (RECORDS DELIMITED BY NEWLINE 8 PREPROCESSOR exec_dir: 9 'gunzip.sh' 10 FIELDS TERMINATED BY 11 ' ' 12 ) 13 LOCATION (...) 14 ); 32

33 SQL*LoaderVS 外部表 1 回あたりのロード時間 SQL*Loader のロード時間を 1 とした場合の相対処理時間 ( 実際の処理時間に任意の数を掛けています ) 資料中に掲載されている検証内容は特定環境における検証結果についての報告でありすべての環境において同様の動作を保証するものではありませんので予めご了承ください外部表 SQL*Loader ロード時間は大きな違いはない SQL*Loader 1 外部表約 300 万レコードロードの結果 33

34 SQL*Loader と外部表 SQL*Loader 外部表ソース外部ファイル外部ファイル /Oracle DB 利用可能な Version 実行形態ファイル形式ロードスピード Version6.X 以前 ~ Client 側で実行 (Net 経由 ) テキスト ( 固定長 / 可変長 ) 外部表と SQL*Loader では大差はない 9i~ 10g~( データのアンロード ) Server 側で実行テキスト ( 固定長 / 可変長 ) 独自 (DataPump) 外部表と SQL*Loader では大差はないデータクレンジングデータを加工しながらのローディングにはワークテーブルが必要直接データを加工しながらローディングができるパラレルロード設定が面倒設定が容易ロード対象データの選択ロードするデータの選択は不可 SQL でロードするデータの選択が可能外部ファイルのロードには外部表を利用したロードが効果的 34

35 まとめ Oracle では 2 種類の外部ファイルのデータロード方法を提供 SQL*Loader 外部表 SQL*Loader は従来型パスロードとダイレクトパスロード 2 つのロード方法があり用途によってそれぞれを使い分ける外部表は外部ファイルをデータベース内に格納されているテーブルのように扱うことができる機能外部表を利用することで SQL*Loader と比べ容易にデータのローディングが可能 35

36 OTN ダイセミでスキルアップ!! 一般的な技術問題解決方法などを知りたい! セミナ資料など技術コンテンツがほしい! Oracle Technology Network(OTN) を御活用下さい一般的技術問題解決には OTN 掲示版のデータベース一般をご活用ください OTN 掲示版は基本的に Oracle ユーザー有志からの回答となるため 100% 回答があるとは限りませんただ過去の履歴を見ると質問の大多数に関してなんらかの回答が書き込まれております過去のセミナ資料動画コンテンツは OTN の OTN セミナーオンデマンドコンテンツへダイセミ事務局にダイセミ資料を請求頂いてもお受けできない可能性がございますので予めご了承くださいダイセミ資料は OTN コンテンツオンデマンドかセミナ実施時間内にダウンロード頂くようお願い致します 36

37 Oracle Direct 新サービスができました新規 Oracle Direct Concierge ( 無償支援サービス ) Web システムボトルネック診断サービス WebLogic Server バージョンアップ支援サービス Oracle Application Server その他アプリケーションサーバーからの Oracle WebLogic Server への移行支援サービスお問い合わせフォーム 37

38 OTN セミナーオンデマンドコンテンツダイセミで実施された技術コンテンツを動画で配信中!! ダイセミのライブ感はそのままにお好きな時間で受講頂けます OTN オンデマンド掲載のコンテンツ内容は予告なく変更になる可能性があります期間限定での配信コンテンツも含まれておりますお早めにダウンロード頂くことをお勧めいたします 38

Oracle エンジニアのための技術情報サイトオラクルエンジニア通信 http://blogs.oracle.

製品機能別コラムオラクル製品に関する技術コラムを毎週お届けします決してニッチではなく誰もが明日から使える技術のあそうだったんだ!

39 Oracle エンジニアのための技術情報サイトオラクルエンジニア通信技術資料ダイセミの過去資料や製品ホワイトペーパースキルアップ資料などを多様な方法で検索できますキーワード検索レベル別カテゴリ別製品機能別コラムオラクル製品に関する技術コラムを毎週お届けします決してニッチではなく誰もが明日から使える技術のあそうだったんだ! をお届けしますオラクルエンジニア通信先月はこんな資料が人気でした Oracle Database 11gR2 RAC インストレーションガイド ASM 版 Microsoft Windows x86-64 Oracle Database 11gR2 旧バージョンからのアップグレード 39

40 オラクルクルクルキャンペーンあの Oracle Database Enterprise Edition が超おトク!! Oracle Database のライセンス価格を大幅に抑えてご導入いただけます多くのお客様でサーバー使用期間とされる 5 年間にライセンス期間を限定期間途中で永久ライセンスへ差額移行 5 年後に新規ライセンスを購入し継続利用 5 年後に新システムへデータを移行 Enterprise Edition はここが違う!! 圧倒的なパフォーマンス! データベース管理がカンタン! データベースを止めなくていい! もちろん障害対策も万全! 詳しくはコチラお問い合わせフォーム 40

あなたにいちばん近いオラクル Oracle Direct まずはお問合せください Oracle Direct 検索システムの検討構築から運用まで ITプロジェクト全般の相談窓口としてご支援いたしますシステム構成やライセンス / 購入方法などお気軽にお問い合わせ下さい Web 問い合わせフォームフリーダイヤル専用お問い合わせフォームにてご相談内容を承ります http://www.

41 あなたにいちばん近いオラクル Oracle Direct まずはお問合せください Oracle Direct 検索システムの検討構築から運用まで ITプロジェクト全般の相談窓口としてご支援いたしますシステム構成やライセンス / 購入方法などお気軽にお問い合わせ下さい Web 問い合わせフォームフリーダイヤル専用お問い合わせフォームにてご相談内容を承りますフォームの入力には Oracle Direct Seminar 申込時と同じログインが必要となりますこちらから詳細確認のお電話を差し上げる場合がありますのでご登録されている連絡先が最新のものになっているかご確認下さい月曜 ~ 金曜 9:00~12:00 13:00~18:00 ( 祝日および年末年始除く ) 41

今さら聞けない!? Oracle入門　～前編～

今さら聞けない!? Oracle入門　～前編～ Oracle Direct Seminar 今さら聞けない!? Oracle 入門 ~ 前編 ~ 日本オラクル株式会社 Agenda 1. Oracle の基本動作 2. Oracle のファイル群 3. Oracle のプロセス群と専用メモリ領域 4. データベース内部動作今さら聞けない!? オラクル入門 ~ 後編 ~ 4. データベース内部動作