SELECT INTO

概要

このステートメントは、クエリ結果を変数またはファイルに保存するために使用されます。そのうち：

• SELECT ... INTO OUTFILE は、結果セットを外部ファイルに保存し、出力形式を指定できるようにします。

• SELECT ... INTO DUMPFILE は、フォーマットのない単一行を外部ファイルに書き込みます。

• SELECT ... INTO var_list は、結果セットを変数に保存します。

権限要件

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データベースV4.3.5では、SELECT INTO からV4.3.5 BP2以降のバージョンで、S3およびS3プロトコルに基づくオブジェクトストレージをデータエクスポートの宛先としてサポートしています。
  • Alibaba Cloud OSSにはファイルサイズの制限があるため、5GBを超えるファイルをOSSにエクスポートする際は、5GB未満の複数のファイルに分割されます。

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' ... は、1行が改行文字で終了することを示します。

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に設定すると、単一スレッド内のすべてのパーティションで共通のメモリが使用されることを意味します。
  • OceanBaseデータベースV4.3.2では、V4.3.2 BP1以降のバージョンからBUFFER_SIZEパラメータがサポートされています。

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：インデックスレコードの頻度を制御するパラメータで、どの程度の行ごとにインデックスを記録するかを定義します。指定しない場合のデフォルト値は10000です。デフォルト値の使用を推奨します。

例

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

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

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

   注意

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

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

      ssh admin@xxx.xxx.xxx.xxx
      

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

      obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
      

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

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

   4. ログアウトします。

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

   • 単一ファイルにシリアル書き込みを行い、ファイル名を test_tbl1.csv と指定します。

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

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

     Query OK, 9 rows affected
     

   • 複数ファイルに並列書き込みを行い、ファイル名を指定せず、各ファイルのサイズが4MBを超えないようにします。

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

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

     Query OK, 9 rows affected
     

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

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

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

     Query OK, 9 rows affected
     

   説明

   • 複数のエクスポートタスクが同時に同じパスにエクスポートされる場合、エラーが発生したり、一部のデータのみがエクスポートされるなどの問題が発生する可能性があります。適切なエクスポートパスを設定することで回避できます。
     例えば：
     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 の場合、エクスポートがすでに存在するなど、理由で失敗した後、エクスポートディレクトリ内のエクスポート先と同じプレフィックスを持つすべてのファイルを削除するか、エクスポートディレクトリを削除して再作成し、再度エクスポート操作を実行します。
     例えば：
     SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1; が失敗した後、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 列の組み合わせであり、同じ行は同一のパーティションに属し、同一のディレクトリにエクスポートされます。

obclient> SELECT /*+parallel(3)*/ *
  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'
FROM test_tbl2;

ストレージ位置は $DATA_FOLDER_NAME 変数で指定されますが、同時にOSSのホストアドレス、アクセスID、およびアクセスキーも提供する必要があります。

関連ドキュメント

OUTFILEステートメントを使用してデータをエクスポートする