SELECT INTO

説明

このステートメントは、クエリ結果を変数またはファイルに格納するために使用します。ここで：

• SELECT ... INTO OUTFILE は、結果セットを外部ファイルに保存するために使用され、出力形式を指定できます。

  説明

  SELECT ... INTO OUTFILE では、\N を使用して NULL を表現できます。

• SELECT ... INTO DUMPFILE は、形式のない1行を外部ファイルに書き込むために使用されます。

• SELECT ... INTO var_list は、結果セットを変数に格納するために使用されます。

権限要件

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

例：

ユーザーに FILE 権限を付与するには、以下のコマンド形式を使用できます：

GRANT FILE ON *.* TO user_name;

ここで、user_name は SELECT INTO コマンドを実行するユーザーです。

構文

select_stmt
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};

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

• STARTING BY string：各行の開始文字を指定します。
• 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 です。デフォルト値の使用を推奨します。

例

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

1. エクスポートファイルのパスを設定します。

   ファイルをエクスポートするには、まずシステム変数 secure_file_priv を設定し、エクスポートファイルがアクセス可能なパスを構成する必要があります。

   注意

   セキュリティ上の理由から、システム変数 secure_file_priv を設定する際は、ローカルソケット接続でデータベースに接続し、このグローバル変数を変更するSQLステートメントを実行する必要があります。詳細については、secure_file_privを参照してください。

   1. 接続するOceanBaseデータベースのOBServerノードにログインします。

      ssh admin@xxx.xxx.xxx.xxx
      

   2. 以下のコマンドを実行し、ローカルUnixソケット接続方式でテナント mysql001 に接続します。

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

   3. エクスポートパスを /home/admin/test_data に設定します。

      SET GLOBAL secure_file_priv = "/home/admin/test_data";
      

   4. ログアウトします。

2. データベースに再接続した後、SELECT INTO OUTFILE ステートメントを使用してデータをエクスポートします。カンマを2つのフィールド値の区切り文字として指定します。文字列型の値は " 文字で囲みます。改行文字を終端文字として使用します。

   • 単一ファイルへの直列書き込みを行い、ファイル名を test_tbl1.csv と指定します。

     SELECT /*+parallel(2)*/ * FROM test_tbl1
     INTO OUTFILE '/home/admin/test_data/test_tbl1.csv'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n';
     

     実行結果は次のとおりです：

     Query OK, 9 rows affected
     

   • 複数ファイルへの並列書き込みを行い、ファイル名を指定しません（ファイル名にデフォルトのプレフィックス data を使用）。各ファイルのサイズは4MBを超えません。

     SELECT /*+parallel(2)*/ * FROM test_tbl1
       INTO OUTFILE '/home/admin/test_data/'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       SINGLE = FALSE MAX_FILE_SIZE = '4MB';
     

     実行結果は次のとおりです：

     Query OK, 9 rows affected
     

   • 複数ファイルへの並列書き込みを行い、ファイル名のプレフィックスを dd2024 と指定します。各ファイルのサイズは4MBを超えません。

     SELECT /*+parallel(2)*/ * FROM test_tbl1
       INTO OUTFILE '/home/admin/test_data/dd2024'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       SINGLE = FALSE MAX_FILE_SIZE = '4MB';
     

     実行結果は次のとおりです：

     Query OK, 9 rows affected
     

   説明

   • 複数のエクスポートタスクが同じパスに同時にエクスポートする場合、エラーが発生したり、データの一部しかエクスポートされなかったりする問題が発生する可能性があります。エクスポートパスを適切に設定することで回避できます。
     例：
     SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; と SELECT /*+parallel(2)*/ * FROM t2 INTO OUTFILE 'test/data' SINGLE = FALSE; を同時に実行すると、エクスポートファイル名が同じためにエラーが発生する可能性があります。エクスポートパスを test/data1 と test/data2 に設定することを推奨します。
   • SINGLE = FALSE で、file already exist などの理由でエクスポートが失敗した場合、エクスポートディレクトリ内のエクスポート対象と同じプレフィックスを持つすべてのファイルを削除するか、エクスポートディレクトリを削除して再作成し、再度エクスポート操作を実行できます。
     例：
     SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; が失敗した場合、test ディレクトリ内のすべての data プレフィックスを持つファイルを削除するか、test ディレクトリを直接削除して再作成し、再度エクスポート操作を試みることができます。

3. マシンにログインし、OBServerノードの /home/admin/test_data ディレクトリでエクスポートされたファイル情報を確認します。

[xxx@xxx /home/admin/test_data]# ls

実行結果は次のとおりです：

data_0_0_0  data_0_1_0  dd2024_0_0_0  dd2024_0_1_0  test_tbl1.csv

ここで、test_tbl1.csv は単一ファイルへの直列書き込みの例としてエクスポートされたファイル名です。data_0_0_0 と data_0_1_0 は、複数ファイルへの並列書き込みでファイル名を指定しない例としてエクスポートされたファイル名です。dd2024_0_0_0 と dd2024_0_1_0 は、複数ファイルへの並列書き込みでファイル名のプレフィックスを dd2024 と指定した例としてエクスポートされたファイル名です。

データファイルを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にエクスポートする

SIMPLE認証によるエクスポート方法

OceanBaseデータベースのテーブル lineitem_external のデータをHDFSにエクスポートし、CSVファイル形式で保存します。

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;

KERBEROS認証によるエクスポート方法

SIMPLE認証とは異なり、KERBEROS認証ではさらに多くの設定ファイルとキーファイルを提供する必要があります。OceanBaseデータベースのテーブル lineitem_external のデータをHDFSにエクスポートし、CSVファイル形式で保存します。

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;

関連ドキュメント

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