OUTFILEステートメントを使用したデータのエクスポート

SELECT INTO OUTFILE ステートメントは、一般的なデータエクスポート方法です。このステートメントでは、エクスポートするフィールドを制限できるため、主キーフィールドをエクスポートする必要がないシナリオに適しています。LOAD DATA INFILE ステートメントと組み合わせてデータをインポートすることで、非常に便利なデータのインポート・エクスポート手法となります。

背景

OceanBaseデータベースはこの構文に対応しています。

権限要件

• MySQLテナントでSELECT INTOステートメントを実行するには、FILE権限と対応するテーブルのSELECT権限が必要です。ユーザーにFILE権限を付与する必要がある場合は、以下のコマンド形式を使用できます：

   GRANT FILE ON *.* TO user_name;
  

  ここで、user_nameはSELECT INTOコマンドを実行するユーザーです。OceanBaseデータベースの権限の詳細については、MySQLモードの権限分類を参照してください。

• OracleテナントでSELECT INTOステートメントを実行するには、対応するテーブルのSELECT権限が必要です。OceanBaseデータベースの権限の詳細については、Oracleモードの権限分類を参照してください。

構文

SELECT [/*+parallel(N)*/] column_list_option
INTO {OUTFILE 'file_name' [PARTITION BY part_expr] [{CHARSET | CHARACTER SET} charset_name] [field_opt] [line_opt] [file_opt]
     | OUTFILE 'file_name' [PARTITION BY part_expr] [FORMAT = (external_file_format_list)] [file_opt]
     | DUMPFILE 'file_name'
     | into_var_list}
FROM table_name_list
[WHERE where_conditions]
[GROUP BY group_by_list [HAVING having_search_conditions]]
[ORDER BY order_expression_list];

column_list_option:
    column_name [, column_name ...]

field_opt:
    {COLUMNS | FIELDS} field_term_list

field_term_list:
  field_term [, field_term ...]

field_term:
    {[OPTIONALLY] ENCLOSED | TERMINATED | ESCAPED} BY string

line_opt:
    LINES line_term_list

line_term_list:
    line_term [, line_term ...]

line_term:
    {STARTING | TERMINATED} BY string

file_opt:
    file_option [, file_option ...]

file_option:
    SINGLE [=] {TRUE | FALSE}
    | MAX_FILE_SIZE [=] {int | string}
    | BUFFER_SIZE [=] {int | string}

external_file_format_list:
    type_csv_option
    | type_parquet_option
    | type_orc_option

type_csv_option:
    TYPE = 'CSV'
    LINE_DELIMITER = '<string>' | <expr>
    FIELD_DELIMITER = '<string>' | <expr>
    ESCAPE = '<character>' | <expr>
    FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>
    FIELD_ENCLOSED_BY = '<character>' | <expr>
    ENCODING = 'charset'
    COMPRESSION = [NONE, GZIP, DEFLATE, ZSTD] | '<string>'
    FILE_EXTENSION = ['<string>']

type_parquet_option:
    TYPE = 'PARQUET',
    COMPRESSION = '<string>'
    ROW_GROUP_SIZE = '<string>' | <int>

type_orc_option:
    TYPE = 'ORC'
    COMPRESSION = '<string>'
    COMPRESSION_BLOCK_SIZE = '<string>' | <int>
    STRIPE_SIZE = '<string>' | <int>
    ROW_INDEX_STRIDE = <int>

パラメータの説明

file_name

file_name の形式は以下のとおりです：

• エクスポートファイルをOBServerノードに保存する場合、形式は次のとおりです：/\$PATH/\$FILENAME。各パラメータの説明は以下のとおりです：

  • $PATH：エクスポートファイルを保存するパスを指定します。これは、OBServerノード上のエクスポートファイルのパスを指定します。
  • $FILENAME：エクスポートファイルの名前を指定します。SINGLE = FALSE の場合、エクスポートファイルのプレフィックスを表します。指定しない場合、デフォルトのプレフィックス data が生成され、システムがサフィックスを自動生成します。

• エクスポートファイルをOSSに保存する場合、形式は次のとおりです：oss://\$PATH/\$FILENAME/?host=\$HOST&access_id=\$ACCESS_ID&access_key=\$ACCESSKEY。各パラメータの説明は以下のとおりです：

  • $PATH：エクスポートファイルを保存するパスを指定します。これは、バケット内のファイルパスを指定します。

  • $FILENAME：エクスポートファイルの名前を指定します。SINGLE = FALSE の場合、エクスポートファイルのプレフィックスを表します。指定しない場合、デフォルトのプレフィックス data が生成され、システムがサフィックスを自動生成します。

  • $HOST：OSSサービスのホスト名またはCDN高速化のドメイン名、つまりアクセスするOSSサービスのアドレスを指定します。

  • $ACCESS_ID：OSSサービスへのアクセスに必要なAccess Key IDを指定します。これは認証に使用されます。

  • $ACCESSKEY：OSSサービスへのアクセスに必要なAccess Key Secretを指定します。これは認証に使用されます。

    説明

    • OceanBaseデータベースは、データエクスポートの宛先としてS3およびS3プロトコルに基づくオブジェクトストレージをサポートしています。
    • Alibaba Cloud OSSにはファイルサイズの制限があるため、5GBを超えるファイルをOSSにエクスポートすると、5GB未満の複数のファイルに分割されます。

• エクスポートファイルをHDFSに保存する場合、形式は次のとおりです：

  注意

  HDFS外部テーブル書き込み機能を使用する場合、OceanBaseデータベースはJAVA SDKを備えた環境で使用する必要があります。JAVA SDK環境の詳細なデプロイ方法については、OceanBaseデータベースJAVA SDK環境のデプロイを参照してください。

  • SIMPLE認証のエクスポート形式：

    • 基本認証（Kerberosなし）：

      hdfs://\$NAMENODE:\$PORT/\$PATH/\$FILENAME[?username=\$USERNAME]
      

    • 説明：

      • 基本認証（SIMPLE）を使用する場合、username パラメータでHDFSユーザー名を指定できます。
      • username パラメータが指定されていない場合、OceanBaseデータベースの設定で定義されているHDFSユーザー名がデフォルトで使用されます。

  • KERBEROS認証のエクスポート形式：

    hdfs://\$NAMENODE:\$PORT/\$PATH/\$FILENAME?principal=\$PRINCIPAL&keytab=\$KEYTAB&krb5conf=\$KRB5CONF&configs=\$CONFIGS
    

    説明：

    • Kerberos認証では、Kerberosプリンシパル（principal）、キータブ（keytab）、Kerberos設定ファイル（krb5conf）が必要です。
    • username パラメータはこのシナリオでは適用されません。Kerberos認証は principal で身元を識別します。

  各パラメータの説明は以下のとおりです：

  • 汎用パラメータ：

    • $NAMENODE：HDFS NameNodeのIPアドレスを表します。
    • $PORT：HDFS NameNodeのポートを表します。
    • $PATH：ファイルのHDFS内の保存パスを表します。
    • $FILENAME：エクスポートファイルの名前を表します。

  • SIMPLE認証専用パラメータ：

    • ?username=\$USERNAME：オプションです。HDFSユーザー名を表します。username が指定されていない場合、OceanBaseデータベースの設定で定義されているHDFSユーザー名がデフォルトで使用されます。

  • KERBEROS認証専用パラメータ：

    • $PRINCIPAL：Kerberosプリンシパル名を表します。例：principal=hdfs/hadoop@EXAMPLE.COM。
    • $KEYTAB：Kerberosキータブファイルのパスを表します。例：keytab=/path/to/hdfs.keytab。
    • $KRB5CONF：Kerberos設定ファイルのパスを表します。例：krb5conf=/path/to/krb5.conf。
    • $CONFIGS：HDFSの追加設定パラメータを表します。形式はキーと値のペアのリストです。例：configs=dfs.data.transfer.protection=authentication,privacy。

  HDFS上にエクスポートファイルを保存する場合のfile_nameの値の例は以下のとおりです：

  • SIMPLE認証（基本認証）：

    • Kerberos認証が不要なHDFSクラスタに適用されます。

    • 例1（デフォルトのユーザー名）：

      hdfs://10.10.10.1:9000/data/export/sales_data.csv
      

    • 例2（ユーザー名の指定）：

      hdfs://10.10.10.1:9000/data/export/sales_data.csv?username=hiveuser
      

  • KERBEROS認証：

    • Kerberos認証が必要なHDFSクラスタに適用されます。

    • クエリパラメータは必要に応じて記入し、複数の構成項目（例：configs）をサポートします。

    • 例：

      hdfs://10.10.10.1:9000/data/export/sales_data.csv?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/etc/security/keytabs/hdfs.keytab&krb5conf=/etc/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy
      

field_term

• [OPTIONALLY] ENCLOSED BY string：フィールド値を囲む記号を指定します。デフォルトでは引用記号はありません。例えば、ENCLOSED BY '"' は文字値を二重引用符で囲むことを意味します。OPTIONALLY キーワードを使用する場合、指定された文字は文字列型の値にのみ適用されます。
• TERMINATED BY string：フィールド値間の区切り文字を指定します。例えば、TERMINATED BY ',' はカンマを2つのフィールド値の区切り文字として指定します。
• ESCAPED BY string：エスケープ文字を指定し、特殊文字の処理や特殊形式のデータの解析を可能にします。デフォルトのエスケープ文字はバックスラッシュ（\）です。

line_term

• TERMINATED BY string：各行の終端文字を指定します。デフォルトでは改行文字が使用されます。例えば、... LINES TERMINATED BY '\n' ... は、行が改行文字で終了することを示します。

file_option

• SINGLE [=] {TRUE | FALSE}：データを単一ファイルまたは複数ファイルにエクスポートするかどうかを制御します。

  • SINGLE [=] TRUE：デフォルト値で、単一ファイルへのエクスポートのみを許可します。

  • SINGLE [=] FALSE：複数ファイルへのエクスポートを許可します。

    注意

    並列度が1より大きく、SINGLE = FALSE の場合、複数ファイルにエクスポートでき、並列読み込み・並列書き込みによるエクスポート速度の向上が得られます。

• MAX_FILE_SIZE [=] {int | string}：エクスポート時の単一ファイルのサイズを制御します。SINGLE = FALSE の場合にのみ有効です。

• BUFFER_SIZE [=] {int | string}：エクスポート時に各スレッドが各パーティション用に割り当てるメモリサイズを制御します（パーティションを分けない場合は単一パーティションと見なされます）。デフォルト値は1MBです。

  説明

  BUFFER_SIZE はエクスポート性能のチューニングに使用されます。マシンのメモリが十分でエクスポート効率の向上を望む場合は、より大きい値（例：4MB）を設定できます。マシンのメモリが不足している場合は、より小さい値（例：4KB）を設定できます。0に設定した場合、単一スレッド内のすべてのパーティションが1つの共有メモリを使用することを意味します。

external_file_format_list

• TYPE = 'CSV' の場合、以下のフィールドが含まれます：

  • LINE_DELIMITER：CSVファイルの行区切り文字を指定します。デフォルトは LINE_DELIMITER='\n' です。
  • FIELD_DELIMITER：オプションです。CSVファイルの列区切り文字を指定します。デフォルトは FIELD_DELIMITER='\t' です。
  • ESCAPE：CSVファイルのエスケープ文字を指定します。1バイトでなければなりません。デフォルトは ESCAPE ='\' です。
  • FIELD_OPTIONALLY_ENCLOSED_BY：オプションです。CSVファイルでフィールド値を囲む記号を指定します。デフォルトは空です。このオプションを使用すると、一部の型のフィールド（CHAR、VARCHAR、TEXT、JSONなど）にのみ囲み文字が付加されます。
  • FIELD_ENCLOSED_BY：オプションです。CSVファイルでフィールド値を囲む記号を指定します。デフォルトは空で、囲み文字を付加しないことを意味します。このオプションは、すべての型のフィールドに囲み文字の設定を適用します。
  • ENCODING：ファイルの文字セットエンコーディング形式を指定します。指定しない場合、デフォルト値は UTF8MB4 です。
  • COMPRESSION：オプションです。ファイルの圧縮形式を指定します。NONE、GZIP、DEFLATE、ZSTDの4種類の圧縮形式をサポートしています。
    • GZIP/DEFLATE：GZIP形式でファイルを圧縮します。
    • ZSTD：ZSTD形式でファイルを圧縮します。
    • NONE：ファイルが圧縮されていないことを示します（デフォルト値）。
  • FILE_EXTENSION：オプションです。ユーザー定義のファイル拡張子を指定します。複数ファイルのエクスポート時にのみ使用され、CSV形式のみが指定をサポートしています。このパラメータを指定しない場合、ファイル拡張子は形式タイプによって決定されます。CSV形式のデフォルトファイル拡張子は .csv です。

• TYPE = 'PARQUET' の場合、以下のフィールドが含まれます：

  • COMPRESSION：PARQUETファイルの圧縮形式を指定します。デフォルトの圧縮後のファイル拡張子は .parquet です。UNCOMPRESSED（ファイルが圧縮されていないことを示す）、SNAPPY、GZIP、BROTLI、ZSTD、LZ4、LZ4_HADOOP の7種類の圧縮形式をサポートしています。指定しない場合、デフォルトは UNCOMPRESSED です。
  • ROW_GROUP_SIZE：PARQUETファイルのROW GROUPサイズを指定します。単位はバイトです。このオプションには数値を入力するか、'64MB' のような文字列を入力できます。指定しない場合、デフォルトは 256MB です。デフォルト値の使用を推奨します。

• TYPE = 'ORC' の場合、以下のフィールドが含まれます：

  • COMPRESSION：ORCファイルの圧縮形式を指定します。デフォルトの圧縮後のファイル拡張子は .orc です。UNCOMPRESSED（ファイルが圧縮されていないことを示す）、SNAPPY、ZLIB、LZ4、ZSTD の5種類の圧縮形式をサポートしています。指定しない場合、デフォルトは UNCOMPRESSED です。
  • COMPRESSION_BLOCK_SIZE：データが圧縮時に分割されるブロックサイズを指定します。単位はバイトです。このオプションには数値を入力するか、'64KB' のような文字列を入力できます。指定しない場合、デフォルトは 256KB です。デフォルト値の使用を推奨します。
  • STRIPE_SIZE：ORCファイルのStripeサイズを指定します。単位はバイトです。このオプションには数値を入力するか、'64MB' のような文字列を入力できます。指定しない場合、デフォルトは 64MB です。デフォルト値の使用を推奨します。
  • ROW_INDEX_STRIDE：インデックスレコードの頻度を制御するパラメータで、何行ごとに1回インデックスを作成するかを定義します。指定しない場合、デフォルトは 10000 です。デフォルト値の使用を推奨します。

注意点

• 複数のエクスポートタスクが同じパスに同時にエクスポートすると、エラーが発生したり、データの一部しかエクスポートされなかったりする可能性があります。これを回避するには、エクスポートパスを適切に設定します。例えば、

  SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1;
  

  SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t2;
  

  同時実行時にエクスポートファイル名が同じためにエラーが発生する可能性があるため、エクスポートパスを test/data1 と test/data2 に設定することを推奨します。

• SINGLE = FALSE の場合、file already exist などの理由でエクスポートが失敗した後、エクスポートディレクトリ内のエクスポート対象と同じプレフィックスを持つすべてのファイルを削除するか、エクスポートディレクトリを削除して再作成し、再度エクスポート操作を実行できます。例えば、

  SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1;
  

  失敗後、test ディレクトリ内のすべての data プレフィックスを持つファイルを削除するか、test ディレクトリを直接削除して再作成し、再度エクスポート操作を試みることができます。

• PARQUETおよびORC形式の圧縮は、ファイル内部のデータブロックレベルでの圧縮であり、ファイル全体が圧縮形式であるわけではありません。したがって、圧縮後の拡張子はCSV形式にのみ適用され、PARQUETおよびORC形式には適用されません。

• CSV形式の圧縮ファイルをエクスポートする場合、単一ファイルと複数ファイルの命名方法は若干異なります：

  • 複数ファイルをエクスポートする場合（SINGLE = FALSE）、圧縮アルゴリズムを指定した場合は、その圧縮アルゴリズムに対応する拡張子をファイル名の最後に付加します。例えば、エクスポートファイル名が data_1_0_1 のファイルに対して、圧縮アルゴリズム gzip を指定すると、ファイル名は data_1_0_1.gz となります。

  • 単一ファイルをエクスポートする場合（SINGLE = TRUE）、出力ファイル名はユーザーが指定したファイル名と完全に一致します。つまり、圧縮拡張子を指定した場合、出力ファイル名にも圧縮拡張子が含まれます。圧縮拡張子を指定しない場合は、圧縮拡張子は出力されません。

• CSV形式ファイルをエクスポートする際の FILE_EXTENSION フィールドの論理順序：

  • COMPRESSION と FILE_EXTENSION の両方を同時に指定した場合、拡張子の順序は .file_extension.compression となります。例えば、エクスポートファイル名が data_0_0_1 のファイルに対して、FILE_EXTENSION を xls、COMPRESSION を gzip と指定した場合、複数ファイルをエクスポートするとファイル名は data_0_0_1.xls.gz となります。

  • 設定された FILE_EXTENSION が . で始まるかどうかにかかわらず、出力ファイルの拡張子には必ず1つの . が含まれます。例えば、エクスポートファイル名が data のファイルに対して、FILE_EXTENSION を xls または .xls と指定した場合、ファイル名はどちらも data.xls となります。

例

データファイルをローカルにエクスポートする

例1：SELECT INTO OUTFILE ステートメントを使用してCSVファイルをエクスポートする

例2：SELECT INTO OUTFILE ステートメントを使用してPARQUETファイルをエクスポートする

例3：SELECT INTO OUTFILE ステートメントを使用してORCファイルをエクスポートする

データファイルをOSSにエクスポートする

SELECT INTO OUTFILE ステートメントを使用して、test_tbl2 テーブルからパーティション単位でデータを指定されたOSSストレージ場所にエクスポートします。パーティションの分割基準は col1 と col2 列の組み合わせで、同じ行は同一のパーティションに属し、同じディレクトリにエクスポートされます。

SELECT /*+parallel(3)*/ * FROM test_tbl2
  INTO OUTFILE 'oss://$DATA_FOLDER_NAME/?host=$OSS_HOST&access_id=$OSS_ACCESS_ID&access_key=$OSS_ACCESS_KEY'
    PARTITION BY CONCAT(col1,'/',col2)
    SINGLE = FALSE BUFFER_SIZE = '2MB';

ストレージ場所は $DATA_FOLDER_NAME 変数で指定され、OSSのホストアドレス、アクセスID、アクセスキーも必要です。

データファイルをHDFSにエクスポートする

SELECT * FROM lineitem_external
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv'
FORMAT = (
    TYPE = 'CSV'
    LINE_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    )
SINGLE = TRUE;

SELECT * FROM lineitem_external
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/path/to/hdfs.keytab&krb5conf=/path/to/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy'
FORMAT = (
    TYPE = 'CSV'
    LINE_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    )
SINGLE = TRUE;

SELECT *
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv'
FORMAT = (
    TYPE = 'CSV'
    LINE_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    )
SINGLE = TRUE
FROM lineitem_external;

SELECT *
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/path/to/hdfs.keytab&krb5conf=/path/to/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy'
FORMAT = (
    TYPE = 'CSV'
    LINE_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    )
SINGLE = TRUE
FROM lineitem_external;

詳細を見る

SELECT INTO OUTFILE メソッドでエクスポートしたファイルは、LOAD DATA ステートメントを使用してインポートできます。詳細な方法については、LOAD DATAを使用したデータのインポートを参照してください。