ApsaraDB for SelectDB：SELECT INTO OUTFILE

功能介紹

SELECT INTO OUTFILE可用於備份或者遷移SelectDB中的資料，該命令具有以下特點。

• 屬於同步命令。

  • 該命令執行完畢後，會立即返回一行結果作為匯出執行狀態。

  • 由於同步機制下任務狀態僅在完成後返回，若串連中斷，系統無法擷取中間狀態，因此無法判斷匯出是否完整完成”。

    為避免任務中斷後無法判斷匯出是否完整，建議您在建立匯出任務時，在Properties中配置"success_file_name" = "SUCCESS"，任務成功後在輸出目錄會產生指定標識檔案。您可通過驗證該檔案是否存在，確認匯出完整性。

• 本質是執行了一個SQL查詢。

  該命令本質為執行SQL查詢，預設採用單線程輸出結果。

• 匯出耗時：匯出總耗時=查詢執行時間+結果集寫出耗時

  若處理大規模查詢，需通過會話變數query_timeout調大逾時閾值。如何設定查詢會話變數，請參見查詢變數。

• 匯出結果為檔案。

  該命令用於將查詢結果匯出為檔案。目前支援通過S3或HDFS協議匯出到遠端儲存，如OSS、HDFS等。

• 匯出時不檢查檔案及檔案路徑是否存在。

  該命令不檢查檔案及檔案路徑是否存在，也不會自動建立目錄或覆蓋現有檔案，具體行為完全取決於遠端儲存系統（如S3/HDFS）的規則。

  SelectDB不會對匯出的檔案進行任何管理，所有匯出檔案（包括成功產物及失敗殘留檔案）均需您在遠端儲存側自行管理及清理。

使用限制

目前僅支援通過S3或HDFS協議匯出資料。

注意事項

• 資料類型不同，支援匯出至的檔案類型會有所不同。

  • 基礎資料型別 (Elementary Data Type)：支援匯出至CSV、PARQUET、CSV_WITH_NAMES、CSV_WITH_NAMES_AND_TYPES和ORC格式的檔案中。

  • 複雜資料類型：

    • 僅支援將複雜資料類型（ARRAY、MAP、STRUCT）匯出到CSV、CSV_WITH_NAMES、CSV_WITH_NAMES_AND_TYPES和ORC格式的檔案中。

    • 不支援匯出嵌套的複雜資料類型。

• 結果檔案注意事項：

  • 對於結果集為空白的查詢，依然會產生一個檔案。

  • 檔案切分會保證一行資料完整的儲存在單一檔案中，因此檔案的大小並不嚴格等於max_file_size。

• 結果檔案內容輸出注意事項：

  • 對於部分輸出為非可見字元的函數，如BITMAP、HLL類型，輸出為\N，即NULL。

  • 目前部分地理位置函數的實際輸出值內容為經過編碼的二進位字元，請使用ST_AsText進行輸出。這類地理位置函數有：

    ST_CIRCLE、ST_POINT、ST_POLYGON、ST_GEOMETRYFROMTEXT、ST_LINEFROMTEXT、ST_GEOMETRYFROMWKB

<query_stmt>
INTO OUTFILE "<file_path>"
[format_as]
[properties]

[PROPERTIES ("key"="value", ...)]

並發匯出

預設情況下，查詢結果集的匯出是非並發的，也就是由單個BE節點，單線程匯出的。因此匯出時間和匯出結果集大小正相關。

如果希望查詢結果集可以並發匯出，可開啟並發匯出以降低匯出的時間。操作如下：

1. 設定會話變數以開啟並發匯出：set enable_parallel_outfile = true;。

2. 驗證查詢是否可以並發匯出。

   您開啟並發匯出後，如果想驗證當前查詢是否能進行並發匯出，可以使用EXPLAIN分析查詢匯出語句。文法如下：

   EXPLAIN <select_into_outfile>;

   select_into_outfile為您要執行的SELECT INTO OUTFILE語句，具體文法，請參見文法。

   對查詢進行EXPLAIN後，SelectDB會返回該查詢的規劃，您需分析查詢規劃判斷詢是否可以並發匯出。

   • RESULT FILE SINK在PLAN FRAGMENT 1中：查詢可並發匯出。

     滿足並發的查詢，您可通be_instance_num * parallel_fragment_exec_instance_num計算並發匯出查詢結果集的並發度。

     • be_instance_num是BE叢集的節點數。

       • 計算資源為32核及以下的叢集為單節點。

       • 計算資源為32核以上的叢集，每32核為一個節點。

     • parallel_fragment_exec_instance_num是並發參數。

       您可通過以下語句查看此參數。

       SHOW variables LIKE  '% parallel_fragment_exec_instance_num%'; 

       通過以下語句設定此參數。

       SET  parallel_fragment_exec_instance_num = <parallel_fragment_exec_instance_num>;  

   • RESULT FILE SINK在PLAN FRAGMENT 0中：查詢不能並發匯出。

     以下為一個SELECT INTO OUTFILE任務的查詢規劃，RESULT FILE SINK在PLAN FRAGMENT 0中，該SELECT INTO OUTFILE不能並發匯出。

     並發匯出的規劃樣本：
     +-----------------------------------------------------------------------------+
     | Explain String                                                              |
     +-----------------------------------------------------------------------------+
     | PLAN FRAGMENT 0                                                             |
     |  OUTPUT EXPRS:<slot 2> | <slot 3> | <slot 4> | <slot 5>                     |
     |   PARTITION: UNPARTITIONED                                                  |
     |                                                                             |
     |   RESULT SINK                                                               |
     |                                                                             |
     |   1:EXCHANGE                                                                |
     |                                                                             |
     | PLAN FRAGMENT 1                                                             |
     |  OUTPUT EXPRS:`k1` + `k2`                                                   |
     |   PARTITION: HASH_PARTITIONED: `default_cluster:test`.`multi_tablet`.`k1`   |
     |                                                                             |
     |   RESULT FILE SINK                                                          |
     |   FILE PATH: s3://ml-bd-repo/bpit_test/outfile_1951_                        |
     |   STORAGE TYPE: S3                                                          |
     |                                                                             |
     |   0:OlapScanNode                                                            |
     |      TABLE: multi_tablet                                                    |
     +-----------------------------------------------------------------------------+

3. 根據SELECT INTO OUTFILE的文法建立匯出任務，匯出資料。

使用樣本

-- fileSystem_port預設值為9000
SELECT * FROM tbl
INTO OUTFILE "hdfs://${host}:${fileSystem_port}/path/to/result_"
FORMAT AS CSV
PROPERTIES
(
    "fs.defaultFS" = "hdfs://ip:port",
    "hadoop.username" = "work"
);

--HA fileSystem_port預設值為8020
SELECT * FROM tbl
INTO OUTFILE "hdfs:///path/to/result_"
FORMAT AS CSV
PROPERTIES
(
'fs.defaultFS'='hdfs://hacluster/',
'dfs.nameservices'='hacluster',
'dfs.ha.namenodes.hacluster'='n1,n2',
'dfs.namenode.rpc-address.hacluster.n1'='192.168.0.1:8020',
'dfs.namenode.rpc-address.hacluster.n2'='192.168.0.2:8020',
'dfs.client.failover.proxy.provider.hacluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);

SELECT k1 FROM tbl1 UNION SELECT k2 FROM tbl1
INTO OUTFILE "s3://oss-bucket/result_"
FORMAT AS PARQUET
PROPERTIES
(
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.access_key" = "****",
    "s3.secret_key" = "****",
    "s3.region" = "cn-hangzhou"
);

SET enable_parallel_outfile = true;

SELECT k1 FROM tb1 LIMIT 1000
INTO outfile "s3://my_bucket/export/my_file_"
format AS csv
properties
(
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.access_key" = "****",
    "s3.secret_key" = "****",
    "s3.region" = "cn-hangzhou"
)

SET enable_parallel_outfile = true;

SELECT k1 FROM tb1 ORDER BY k1 LIMIT 1000
INTO outfile "s3://my_bucket/export/my_file_"
format AS csv
properties
(
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.access_key" = "****",
    "s3.secret_key" = "****",
    "s3.region" = "cn-hangzhou"
)

資料類型映射

Parquet 和 ORC 檔案格式都有各自的資料類型。SelectDB的匯出功能能夠自動將SelectDB的資料類型匯出為Parquet/ORC檔案格式所對應的資料類型。

• SelectDB匯出到ORC檔案格式的資料類型映射：

  SelectDB Type	Orc Type
  boolean	boolean
  tinyint	tinyint
  smallint	smallint
  int	int
  bigint	bigint
  largeInt	string
  date	string
  datev2	string
  datetime	string
  datetimev2	timestamp
  float	float
  double	double
  char / varchar / string	string
  decimal	decimal
  struct	struct
  map	map
  array	array

• 在將資料從SelectDB匯出到Parquet檔案格式時，會先將SelectDB記憶體中的資料轉換為Arrow記憶體資料格式，然後由Arrow寫出到Parquet檔案格式。SelectDB資料類型到Arrow資料類的映射關係為：

  SelectDB Type	Arrow Type
  boolean	boolean
  tinyint	int8
  smallint	int16
  int	int32
  bigint	int64
  largeInt	utf8
  date	utf8
  datev2	utf8
  datetime	utf8
  datetimev2	utf8
  float	float32
  double	float64
  char / varchar / string	utf8
  decimal	decimal128
  struct	struct
  map	map
  array	list