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に設定すると、単一スレッド内のすべてのパーティションが共通のメモリブロックを使用することを意味します。

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を使用したデータのインポートを参照してください。