すべてのプロダクト
Search
ドキュメントセンター

:OSS を使用してデータを並行してインポートおよびエクスポートする

最終更新日:Mar 19, 2020

AnalyticDB for PostgreSQL では、OSS の外部テーブル (gpossext 機能) を使って、OSS へのデータのインポート、および OSS からのエクスポートを同時に行うことができます。また、OSS の外部テーブルファイルを gzip で圧縮することで、ストレージスペースとコストを削減します。

gpossext関数は、text / csvファイルまたはtext / csvファイルをgzip形式で読み書きできます。

OSS 外部テーブルの拡張子を作成する (oss_ext)

OSS 外部テーブルを使用する前に、最初に oss_ext 拡張子 (デ ータベースの oss_ext) を作成する必要があります。

  • oss_ext を作成するには、次のコマンドを実行します。 CREATE EXTENSION IF NOT EXISTS oss_ext;
  • oss_ext を削除するには、コマンド DROP EXTENSION IF EXISTS oss_ext; を実行します。

データを同時にインポートする

データをインポートするには、次の手順を実行します。

  1. 複数の OSS ファイルにデータを均等に分散して保存します。ファイル数は、 AnalyticDB for PostgreSQL データノード数 (セグメント数) の整数倍であることが望ましいです。

  2. AnalyticDB for PostgreSQL データベースに、READABLE 外部テーブルを作成します。

  3. 次のコマンドを実行して、データを同時にインポートします。

    1. INSERT INTO <target table> SELECT * FROM <external table>

データを同時にエクスポートする

データをエクスポートするには、次の手順を実行します。

  1. AnalyticDB for PostgreSQL データベースに WRITABLE 外部テーブルを作成します。

  2. 次のコマンドを実行して、OSS にデータを同時にエクスポートします。

    1. INSERT INTO <external table> SELECT * FROM <source table>

OSS 外部テーブルの構文を作成する

次のコマンドを実行して、OSS 外部テーブルの構文を作成します。

  1. CREATE [READABLE] EXTERNAL TABLE tablename
  2. ( columnname datatype [, ...] | LIKE othertable )
  3. LOCATION ('ossprotocol')
  4. FORMAT 'TEXT'
  5. [( [HEADER]
  6. [DELIMITER [AS] 'delimiter' | 'OFF']
  7. [NULL [AS] 'null string']
  8. [ESCAPE [AS] 'escape' | 'OFF']
  9. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  10. [FILL MISSING FIELDS] )]
  11. | 'CSV'
  12. [( [HEADER]
  13. [QUOTE [AS] 'quote']
  14. [DELIMITER [AS] 'delimiter']
  15. [NULL [AS] 'null string']
  16. [FORCE NOT NULL column [, ...]]
  17. [ESCAPE [AS] 'escape']
  18. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  19. [FILL MISSING FIELDS] )]
  20. [ ENCODING 'encoding' ]
  21. [ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count
  22. [ROWS | PERCENT] ]
  23. CREATE WRITABLE EXTERNAL TABLE table_name
  24. ( column_name data_type [, ...] | LIKE other_table )
  25. LOCATION ('ossprotocol')
  26. FORMAT 'TEXT'
  27. [( [DELIMITER [AS] 'delimiter']
  28. [NULL [AS] 'null string']
  29. [ESCAPE [AS] 'escape' | 'OFF'] )]
  30. | 'CSV'
  31. [([QUOTE [AS] 'quote']
  32. [DELIMITER [AS] 'delimiter']
  33. [NULL [AS] 'null string']
  34. [FORCE QUOTE column [, ...]] ]
  35. [ESCAPE [AS] 'escape'] )]
  36. [ ENCODING 'encoding' ]
  37. [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
  38. ossprotocol:
  39. oss://oss_endpoint prefix=prefix_name
  40. id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
  41. ossprotocol:
  42. oss://oss_endpoint dir=[folder/[folder/]...]/file_name
  43. id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
  44. ossprotocol:
  45. oss://oss_endpoint filepath=[folder/[folder/]...]/file_name
  46. id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

パラメータの説明

一般的なパラメータ

  • プロトコルとエンドポイント:フォーマットは protocol name: //oss_endpoint です。「protocol name」は OSS で、「oss_endpoint」は対応する OSS リージョンのドメイン名です。

    注:アクセスリクエストが Alibaba Cloud ホストからのものである場合は、イントラネットドメイン名 (つまり、ドメイン名に「internal」) を使用して、パブリックデータの使用を回避します。

  • id: OSS アカウント ID です。

  • key: OSS アカウントキーです。

  • bucket: データファイルがあるバケットを指定します。OSS で事前に作成する必要があります。

  • prefix: データファイルの対応するパス名のプレフィックスを指定します。正規表現をサポートしていないため、マッチングプレフィックスのみです。また、filepath および dir と相互に排他的です。つまり、同時に指定することはできません。

    • データインポート用の READABLE 外部テーブルを作成すると、このプレフィックスを持つすべての OSS ファイルがインポートされます。

      • prefix = test / filename を指定した場合は、次のファイルがすべてインポートされます。

        • test/filename
        • test/filenamexxx
        • test/filename/aa
        • test/filenameyyy/aa
        • test/filenameyyy/bb/aa
      • prefix = test / filename / を指定した場合、次のファイルだけがインポートされます。 (先にリストしたファイルはインポートされません)

        • test/filename/aa
    • データエクスポートのための WRITABLE 外部テーブルを作成すると、エクスポートしたファイルに名前を付けるためのプレフィックスに基づき、一意のファイル名が自動的に生成されます。

      注:複数のファイルをエクスポートすると、すべてのデータノードが 1 つ以上のファイルをエクスポートします。エクスポートするファイル名の形式は prefix_tablename_uuid.x です。具体的に言うと、 uuid は生成された int64 整数値 (マイクロ秒単位のタイムスタンプ) であり、x はノード ID です。AnalyticDB for PostgreSQL は、同じ外部テーブルを使用して複数のエクスポート操作をサポートします。すべてのエクスポート操作からエクスポートされたファイルは UUID によって区別され、同じエクスポート操作でエクスポートされたファイルは同じ UUID を共有します。

  • dir: OSS 内の仮想フォルダのパスです。プレフィックスとファイルパスは相互に排他的です。

    • フォルダのパスは「/」で終わります。たとえば、test/mydir/ です。

    • データのインポート中にこのパラメータを使用して外部テーブルを作成すると、指定した仮想ディレクトリの下にあるすべてのファイル (サブディレクトリおよびサブディレクトリ下のファイルを除く) がインポートされます。ファイルパスとは異なり、dir ディレクトリ下にあるファイルの命名要件はありません。

    • データのエクスポート中にこのパラメータを使用して外部テーブルを作成すると、すべてのデータがこのディレクトリの下にある複数のファイルにエクスポートされます。出力ファイル名は filename.x の形式に従います。具体的には、 x は数字ですが、不連続でもかまいません。

  • filepath: OSS のパスを含むファイル名です。プレフィックスと dir とは相互排他的です。READABLE 外部テーブルの作成時にのみ指定することができます。(つまり、データのインポート時のみ使用可能です)

    • ファイル名にはファイルパスが含まれていますが、バケット名は含んでいません。

    • ファイルの命名規則は、データのインポート中は filename または filename.x に従う必要があります。x は 1 から始まり、連続する必要があります。たとえば、 filepath = filename を指定し、OSS に以下のファイルが含まれている場合、インポートされるファイルには filename、filename.1、および filename.2 が含まれます。filename.3 は存在しないため、filename.4 はインポートされません。

      1. filename
      2. filename.1
      3. filename.2
      4. filename.4,

インポートモードのパラメータ

  • async: 非同期モードでデータを読み込むかどうかを指定します。

    • ワーカースレッドを有効にして OSS からデータを読み込むと、インポートのパフォーマンスが向上します。
    • 非同期モードはデフォルトで有効になっており、通常モードよりも多くハードウェアリソースを消費します。async = false または async = f を使って無効にすることができます。
  • compressiontype: インポートしたファイルの圧縮形式です。

    • none (デフォルト値) に指定すると、インポートしたファイルは圧縮されません。
    • gzip に指定すると、インポートした形式は gzip になります。gzip 圧縮形式のみがサポートされています。

エクスポートモードのパラメータ

  • oss_flush_block_size:OSS にフラッシュされる単一データのバッファサイズです。デフォルトは、32 MB です。値の範囲は 1 MB ~ 128 MB です。

  • oss_file_max_size:OSS に書き込まれるファイルの最大サイズ。この制限を超えると、エクスポートは別のファイルに切り替わり、データの書き込みを続行します。デフォルト値は、 1,024 MB で、範囲は 8 MB から 4,000 MB です。

また、エクスポートモードでは次の項目に注意してください。

  • WRITABLE は、エクスポートモードの外部テーブルのキーワードです。外部テーブルを作成するときは、明示的に指定する必要があります。

  • エクスポートモードでは、プレフィックスおよび dir パラメーターモードのみがサポートされ、ファイルパスはサポートされません。

  • エクスポートモードの DISTRIBUTED BY 節を使用すると、データノード (セグメント) は、指定された分散キーで OSS にデータを書き込むことができます。

その他の一般的なパラメータ

インポートモードとエクスポートモードには、次のフォールトトレラントなパラメータもあります。

  • oss_connect_timeout:接続のタイムアウトを設定します。単位は秒で、デフォルト値は 10 秒です。

  • oss_dns_cache_timeout:DNS タイムアウトを設定します。単位は秒で、デフォルト値は 60 秒です。

  • oss_speed_limit:最小許容レートを制御します。デフォルト値は、1,024 (つまり 1 K) です。

  • oss_speed_time:最大許容時間を制御します。デフォルト値は、15 秒です。

上記のすべてのパラメータをデフォルトとして設定すると、連続する 15 秒間に、伝送速度が 1 K より遅い場合にタイムアウトが発生します。詳細は、OSS SDK エラー処理 をご参照ください。

  1. # Create an external table for OSS import
  2. create readable external table ossexample
  3. (date text, time text, open float, high float,
  4. low float, volume int)
  5. location('oss://oss-cn-hangzhou.aliyuncs.com
  6. prefix=osstest/example id=XXX
  7. key=XXX bucket=testbucket' compressiontype=gzip)
  8. FORMAT 'csv' (QUOTE '''' DELIMITER E'\t')
  9. ENCODING 'utf8'
  10. LOG ERRORS INTO my_error_rows SEGMENT REJECT LIMIT 5;
  11. create readable external table ossexample
  12. (date text, time text, open float, high float,
  13. low float, volume int)
  14. location('oss://oss-cn-hangzhou.aliyuncs.com
  15. dir=osstest/ id=XXX
  16. key=XXX bucket=testbucket')
  17. FORMAT 'csv'
  18. LOG ERRORS SEGMENT REJECT LIMIT 5;
  19. create readable external table ossexample
  20. (date text, time text, open float, high float,
  21. low float, volume int)
  22. location('oss://oss-cn-hangzhou.aliyuncs.com
  23. filepath=osstest/example.csv id=XXX
  24. key=XXX bucket=testbucket')
  25. FORMAT 'csv'
  26. LOG ERRORS SEGMENT REJECT LIMIT 5;
  27. # Create an external table for OSS export
  28. create WRITABLE external table ossexample_exp
  29. (date text, time text, open float, high float,
  30. low float, volume int)
  31. location('oss://oss-cn-hangzhou.aliyuncs.com
  32. prefix=osstest/exp/outfromhdb id=XXX
  33. key=XXX bucket=testbucket') FORMAT 'csv'
  34. DISTRIBUTED BY (date);
  35. create WRITABLE external table ossexample_exp
  36. (date text, time text, open float, high float,
  37. low float, volume int)
  38. location('oss://oss-cn-hangzhou.aliyuncs.com
  39. dir=osstest/exp/ id=XXX
  40. key=XXX bucket=testbucket') FORMAT 'csv'
  41. DISTRIBUTED BY (date);
  42. # Create a heap table to load data
  43. create table example
  44. (date text, time text, open float,
  45. high float, low float, volume int)
  46. DISTRIBUTED BY (date);
  47. # Load data from ossexample to example in parallel
  48. insert into example select * from ossexample;
  49. # Export data from example to OSS in parallel
  50. insert into ossexample_exp select * from example;
  51. # We can see from the following query plan that every segment participates in the work.
  52. # They pull data from the OSS in parallel and then use the Redistribute Motion node to distribute the hashed data to corresponding segments. Data-receiving segments store the data to the database through the Insert node.
  53. explain insert into example select * from ossexample;
  54. QUERY PLAN
  55. -----------------------------------------------------------------------------------------------
  56. Insert (slice0; segments: 4) (rows=250000 width=92)
  57. -> Redistribute Motion 4:4 (slice1; segments: 4) (cost=0.00..11000.00 rows=250000 width=92)
  58. Hash Key: ossexample.date
  59. -> External Scan on ossexample (cost=0.00..11000.00 rows=250000 width=92)
  60. (4 rows)
  61. # We can see from the following query plan that the segment exports local data directly to the OSS without data redistribution.
  62. explain insert into ossexample_exp select * from example;
  63. QUERY PLAN
  64. ---------------------------------------------------------------
  65. Insert (slice0; segments: 3) (rows=1 width=92)
  66. -> Seq Scan on example (cost=0.00..0.00 rows=1 width=92)
  67. (2 rows)

注意

  • ロケーション関連のパラメータとは別に、外部テーブルの作成と使用に関する構文の残りの部分は、Greenplum の構文と一貫性があります。

  • データのインポートパフォーマンスは、 AnalyticDB for PostgreSQL クラスタリソース (CPU、IO、メモリ、ネットワークなど) および OSS と関連しています。最適なインポートパフォーマンスを実現するために、テーブルを作成するときは、圧縮列ストアを使用することをお勧めします。たとえば、WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576) という節を指定できます。

  • ossendpoint リージョンは、データのインポートのパフォーマンスを保証するために、 AnalyticDB for PostgreSQL リージョンと一致する必要があります。最適なパフォーマンスを得るには、同じリージョン内の OSS および AnalyticDB for PostgreSQL インスタンスを設定することをお勧めします。関連情報については、OSS エンドポイント情報 をご参照ください。

TEXT / CSV形式の説明

外部表のDDLパラメータに次のパラメータを指定して、OSSの読取り/書込み操作のファイル形式を指定できます。

  • TEXT / CSVの行区切り文字は改行文字である '\ n'です。
  • DELIMITERは、列を定義するために使用される区切り文字です。
  • DELIMITERがユーザーデータに含まれている場合は、QUOTEパラメーターが必要です。*推奨される列区切り文字は '、'、 '\ t'、 '|'またはまれな文字です。
  • QUOTEは、特殊文字を含むユーザー・データを列にラップするために使用されます。特殊文字を含む文字列は、ユーザデータを制御文字と区別するためにQUOTEで囲まれています。 パフォーマンス最適化の考慮事項(たとえば、整数の場合)のために、QUOTEでデータをラップする必要がないことがあります。
  • QUOTEはDELIMITERと同じにすることはできません。デフォルトのQUOTEは二重引用符です。*ユーザーデータにQUOTE文字が含まれている場合は、エスケープ文字ESCAPEを使用してユーザーデータを区別する必要があります。
  • ESCAPEは特殊文字用のエスケープ文字です*エスケープ文字は、特殊文字の前に表示され、特殊文字でないことを示します。
  • ESCAPEはデフォルトでQUOTEと同じですが、二重引用符で囲みます。
  • '\'(MySQLのデフォルトのエスケープ文字)またはその他の文字で指定することもできます。

典型的なデフォルトのTEXT / CSV制御文字

制御文字\書式 テキスト CSV
DELIMITER(列区切り文字) \ t(タブ) 、 (コンマ)
クォート(引用) “(二重引用符) “(二重引用符)
エスケープ(エスケープ) (該当なし) QUOTEと同じ
NULL(NULL) \ N(バックスラッシュ-N) (引用符のない空文字列)

すべての制御文字は1バイト文字でなければなりません。

SDK エラー処理

データのインポートおよびエクスポート中の操作エラーの場合、エラーログには次の情報が表示されます。

  • code:誤ったリクエストの HTTP ステータスコードです。

  • error_code:OSS のエラーコードです。

  • error_msg:OSS のエラーメッセージです。

  • req_id:リクエストの UUID です。問題を解決できない場合は、req_id を使用して OSS 開発エンジニアにヘルプを依頼してください。

詳細は、OSS API エラー応答 をご参照ください。タイムアウト関連のエラーは、oss_ext 関連のパラメーターを使用して処理できます。

よくある質問

インポートが遅い場合は、前述の 注意点 セクションのインポートパフォーマンスの説明をご参照ください。

参照