ApsaraDB for ClickHouse：從OSS匯入資料

前提條件

• 已開通OSS服務。如何開通，請參見開通OSS服務。

• 已建立儲存空間（Bucket），且Bucket與雲資料庫ClickHouse位於同一地區。如何建立，請參見建立儲存空間。

• 訪問OSS的帳號已具備對OSS對象的讀許可權。如何設定許可權，請參見許可權控制。

資料準備

將如下測試資料存放區為test.csv，並上傳至OSS中。在匯入資料時，雲資料庫ClickHouse支援的預設資料行分隔符號為,。上傳資料的具體操作，請參見上傳檔案。

1,yang,32,shanghai,http://example1.com
2,wang,22,beijing,http://example2.com
3,xiao,23,shenzhen,http://example3.com
4,jess,45,hangzhou,http://example4.com
5,jack,14,shanghai,http://example5.com
6,tomy,25,hangzhou,http://example6.com
7,lucy,45,shanghai,http://example7.com
8,tengyin,26,shanghai,http://example8.com
9,wangli,27,shenzhen,http://example9.com
10,xiaohua,37,shanghai,http://example10.com

操作步驟

1. 串連雲資料庫ClickHouse叢集。具體操作，請參見串連叢集。

2. 建立本地表oss_test_tbl_local。

   重要

   • 雲資料庫ClickHouse表的結構需與OSS外表的結構一致，並保證相容OSS中的資料格式（尤其注意null欄位等），防止出現因資料無法解析導致叢集異常。

   • 請根據叢集的副本配置選擇對應的建表語句。您可以在控制台叢集資訊頁面的叢集屬性地區查看副本配置。

   • 您可以根據業務需求選擇合適的表引擎。表引擎的詳細資料，請參見表引擎。

   單副本版叢集

   CREATE TABLE oss_test_tbl_local ON CLUSTER default
   (
   id UInt8,
   user_name String,
   age UInt16,
   city String,
   access_url String
   )
   ENGINE = MergeTree()
   ORDER BY id;

   雙副本版叢集

   CREATE TABLE oss_test_tbl_local ON CLUSTER default
   (
   id UInt8,
   user_name String,
   age UInt16,
   city String,
   access_url String
   )
   ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
   ORDER BY id;

3. （可選）建立分布式表oss_test_tbl_distributed。

   說明

   如果您想同時將資料分發到集合中的各個本地表時需要建立分布式表。

   CREATE TABLE oss_test_tbl_distributed ON CLUSTER default
   (
   id UInt8,
   user_name String,
   age UInt16,
   city String,
   access_url String
   )
   ENGINE = Distributed(default, default, oss_test_tbl_local, rand());

4. 匯入OSS資料至雲資料庫ClickHouse。

   雲資料庫ClickHouse提供了表引擎和表函數兩種方法匯入OSS資料。

   重要

   請根據叢集的核心版本選擇對應的建表語句。您可以在控制台叢集資訊頁面的叢集屬性地區查看版本。

   方法一：通過表引擎匯入OSS資料

   1. 建立OSS外表oss_test_tbl。

      22.8以下版本的建表文法

      CREATE TABLE <table_name> [ON CLUSTER cluster]
      (
      'col_name1' col_type1,
      'col_name2' col_type2,
      ...
      )
      ENGINE = OSS('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>');

      22.8及以上版本的建表文法

      CREATE TABLE <table_name> [ON CLUSTER cluster]
      (
      'col_name1' col_type1,
      'col_name2' col_type2,
      ...
      )
      ENGINE = OSS('https://<BucketName>.<oss-endpoint>/<file-name>', '<access-key-id>', '<access-key-secret>', '<file-format-name>');

      參數說明如下：

      參數名	描述
      table_name	表名。
      ON CLUSTER cluster	在每一個節點上都建立一個本地表，固定為ON CLUSTER default。
      col_name1,col_name2	列名。
      col_type1,col_type2	列類型。 重要 OSS外表的結構類型需與OSS資料對應。
      BucketName	Bucket的名稱。
      oss-endpoint	訪問OSS的Endpoint。如何擷取，請參見OSS地區和訪問網域名稱。 重要 請確儲存儲空間（Bucket）與雲資料庫ClickHouse叢集在同一地區，並使用阿里雲VPC網路訪問OSS。
      file-name	檔案名稱。
      access-key-id	訪問OSS資料的Accesskey ID。如何擷取，請參見如何擷取AccessKey。
      access-key-secret	訪問OSS資料的AccessKey Secret。如何擷取，請參見如何擷取AccessKey。
      oss-file-path	檔案的儲存路徑。一般格式為oss://<bucket-name>/<path-to-file>。 說明 oss-file-path參數支援通過萬用字元進行模糊比對。更多資訊，請參見萬用字元模糊比對OSS的儲存路徑。
      file-format-name	檔案的格式，本文為CSV。

      樣本語句：

      22.8以下版本的樣本語句

      CREATE TABLE oss_test_tbl on cluster default
      (
      id UInt8,
      user_name String,
      age UInt16,
      city String,
      access_url String
      )
      ENGINE = OSS('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://testBucketName/test.csv', 'CSV');

      22.8及以上版本的樣本語句

      CREATE TABLE oss_test_tbl on cluster default
      (
      id UInt8,
      user_name String,
      age UInt16,
      city String,
      access_url String
      )
      ENGINE = OSS('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'STS.****************', 'STS.****************','CSV')

   2. 將OSS外表oss_test_tbl資料匯入至分布式表oss_test_tbl_distributed中。

      說明

      如果您只需要匯入OSS資料至本地表，請將匯入語句中的分布式表名更換為本地表名。

      INSERT INTO oss_test_tbl_distributed SELECT * FROM oss_test_tbl;

      如果您匯入的CSV檔案裡指定的分隔字元不是,，則需要在匯入語句中通過format_csv_delimiter指定其他資料行分隔符號。例如CSV檔案列與列之間使用豎線分割，則命令樣本如下。

      INSERT INTO oss_test_tbl_distributed SELECT * FROM oss_test_tbl settings format_csv_delimiter='|';

   方法二：通過表函數匯入OSS資料

   22.8以下版本的匯入文法

   INSERT INTO <table_name> SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<col_name> <col_type>(,...)');

   22.8及以上版本的匯入文法

   INSERT INTO <table_name> SELECT * FROM oss('https://<BucketName>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

   參數的詳細資料，請參見參數說明。

   樣本語句：

   22.8以下版本的樣本語句

   INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://testBucketName/test.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

   22.8及以上版本的樣本語句

   INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'STS.****************', 'STS.****************', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

   如果您匯入的CSV檔案裡指定的分隔字元不是,，則需要在匯入語句中通過format_csv_delimiter指定其他資料行分隔符號。例如CSV檔案列與列之間使用豎線分割，則樣本語句如下。

   INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>',  '<col_name> <col_type>(,...)') settings format_csv_delimiter='|';

5. 查詢分布式表oss_test_tbl_distributed中資料，驗證OSS資料是否成功匯入至雲資料庫ClickHouse。

   SELECT * FROM oss_test_tbl_distributed; 

   返回結果如下：

   ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
   │  1 │  yang     │   32  │  shanghai  │  http://example1.com  │
   │  2 │  wang     │   22  │  beijing   │  http://example2.com  │
   │  3 │  xiao     │   23  │  shenzhen  │  http://example3.com  │
   │  4 │  jess     │   45  │  hangzhou  │  http://example4.com  │
   │  5 │  jack     │   14  │  shanghai  │  http://example5.com  │
   │  6 │  tomy     │   25  │  hangzhou  │  http://example6.com  │
   │  7 │  lucy     │   45  │  shanghai  │  http://example7.com  │
   │  8 │  tengyin  │   26  │  shanghai  │  http://example8.com  │
   │  9 │  wangli   │   27  │  shenzhen  │  http://example9.com  │
   │ 10 │  xiaohua  │   37  │  shanghai  │  http://example10.com │
   └────┴───────────┴───────┴────────────┴───────────────────────┘

萬用字元模糊比對OSS的儲存路徑

OSS中通常存在多個具備相同命名規則的小檔案，為了簡化對於小檔案的分析，oss-file-path參數支援通過如下萬用字元進行模糊比對。

• *：匹配任意檔案名稱、目錄名。如/dir/*匹配/dir下的所有檔案。

• {x, y, z}：匹配大括弧中的任意一個值。如file_{x,y,z}匹配file_x、file_y或file_z。

• {num1..num2}：匹配[num1,num2]展開後的任意一個值。如file_{1..3}匹配file_1、file_2或file_3。

• ?：匹配任意一個單字元。如file_?匹配file_a、file_b、file_c等等。

樣本

上傳檔案的目錄結構如下。

oss://testBucketName/
               doc-data/
                    oss-import/
                        small_files/
                            access_log_csv_1.txt
                            access_log_csv_2.txt
                            access_log_csv_3.txt

oss-file-path參數的部分參考取值如下。

• oss://testBucketName/doc-data/oss-import/small_files/*

• oss://testBucketName/doc-data/oss-import/small_files/access*

• oss://testBucketName/doc-data/oss-import/small_files/access_log_csv_{1,2,3}.txt

• oss://testBucketName/doc-data/oss-import/*/access_log_csv_{1,2,3}.txt

• oss://testBucketName/doc-data/oss-import/*/*

• oss://testBucketName/doc-data/oss-import/*/access_log_csv_{1..3}.txt

• oss://testBucketName/doc-data/oss-import/*/access_log_csv_?.txt