E-MapReduce：遷移ClickHouse資料到StarRocks

前提條件

• 已建立Serverless StarRocks執行個體，詳情請參見建立執行個體。

• 已在EMR on ECS上建立包含ClickHouse服務的OLAP叢集，詳情請參見建立叢集，或建立雲資料庫ClickHouse叢集，詳情請參見建立叢集。

  說明

  本文以在EMR on ECS上建立包含ClickHouse服務的叢集（OLAP）為例。

使用限制

EMR Serverless StarRocks執行個體和OLAP叢集需要在同一VPC下。

使用DataX同步資料

操作步驟

1. 下載並解壓縮DataX安裝包。

   DataX的具體使用方式請參見DataX。

2. 配置RDBMSReader。

   說明

   RDBMSReader是DataX內建的一個通用的關聯式資料庫讀外掛程式，可以通過添加、註冊資料庫驅動等方式增加各種關係型資料庫的讀支援。

   1. 複製datax/plugin/writer/clickhousewriter/libs目錄下的所有JAR包到datax/plugin/reader/rdbmsreader/libs目錄下。

      cp datax/plugin/writer/clickhousewriter/libs/* datax/plugin/reader/rdbmsreader/libs

3. 刪除datax/plugin/reader/rdbmsreader/libs目錄下的guava-r05.jar。

4. 修改datax/plugin/reader/rdbmsreader下的plugin.json檔案，給drivers的參數值添加上,"ru.yandex.clickhouse.ClickHouseDriver"。

   {
     "name": "rdbmsreader",
     "class": "com.alibaba.datax.plugin.reader.rdbmsreader.RdbmsReader",
     "description": "useScene: prod. mechanism: Jdbc connection using the database, execute select sql, retrieve data from the ResultSet. warn: The more you know about the database, the less problems you encounter.",
     "developer": "alibaba",
     "drivers":["dm.jdbc.driver.DmDriver", "com.sybase.jdbc3.jdbc.SybDriver", "com.edb.Driver","ru.yandex.clickhouse.ClickHouseDriver"]
   }

5. 配置StarRocks Writer。

   StarRocks Writer的配置方式，請參見DataX Writer。

6. 配置DataX的JSON指令碼。

   為匯入作業建立一個JSON格式的檔案ck2strs.json。

   {
     "job": {
       "setting": {
         "speed": {
           "channel": 3
         },
         "errorLimit": {
           "record": 0,
           "percentage": 0
         }
       },
       "content": [
         {
           "reader": {
             "name": "rdbmsreader",
             "parameter": {
               "username": "default",
               "password": "**",
               "column": ["**"],
               "connection": [
                 {"jdbcUrl": ["jdbc:clickhouse://<clickhouse_host>:8123/default"],
                   "table": ["**"]
                 } ],
               "where": ""
             }
           },
           "writer": {
             "name": "starrockswriter",
             "parameter": {
               "username": "admin",
               "password": "**",
               "database": "ck",
               "table": "uk_price_paid_sr",
               "column": ["**"],
               "preSql": [],
               "postSql": [],
               "jdbcUrl": "jdbc:mysql://<fe_host>:<fe_query_port>",
               "loadUrl": [
                 "<fe_host>:<fe_http_port>",
                 "<fe_host>:<fe_http_port>",
                 "<fe_host>:<fe_http_port>"
               ],
               "loadProps": {
                 "column_separator": "\\x03",
                 "row_delimiter": "\\x02"
               }
             }
           }
         }
       ]
     }
   }
   

   各參數含義如下表所示。

   • reader

     參數	說明
     username	ClickHouse使用者名稱。本文使用的是ClickHouse部署完後的預設使用者default，密碼預設值為空白。如果需要更換使用者，請根據您的實際情況修改並設定相應的密碼。
     password	ClickHouse使用者的密碼。預設使用者default對應的密碼為空白。
     column	可以指定需要查詢的列。
     jdbcUrl	JDBC串連串。填寫格式為jdbc:clickhouse://<clickhouse_host>:<clickhouse_port>/<database_name>。 格式中各參數如下： <clickhouse_host>：ClickHouse的IP地址。 <clickhouse_port>：官方驅動的連接埠預設為8123。如果使用TCP協議的第三方驅動，則預設連接埠為9000。 <database_name>：ClickHouse的資料庫名。
     table	ClickHouse的表名稱。

   • writer

     參數	說明
     username	StarRocks的管理使用者，預設為admin。
     password	admin的密碼。
     database	StarRocks資料庫的名稱。
     table	StarRocks表的名稱。
     column	可以指定需要查詢的列。多個欄位時之間用英文逗號（,）分隔。例如，"column": ["id","name","age"]。 重要 該參數必須指定。如果希望匯入所有欄位，可以使用["*"]。
     preSql	寫入資料到目的表前，會先執行設定的標準語句。
     postSql	寫入資料到目的表後，會先執行設定的標準語句。
     jdbcUrl	JDBC串連資訊，用於執行preSql和postSql。填寫格式為jdbc:mysql://<fe_host>:<fe_query_port>。
     loadUrl	StarRocks FE的地址，填寫格式為<fe_host>:<fe_http_port>。

7. 執行同步任務。

   python datax/bin/datax.py --jvm="-Xms1G -Xmx1G" -p"-Dstart=$i -Dend=$end" datax/job/ck2strs.json>>/bigdata/log/ck2strs/${i}.log

   說明

   • 範例程式碼中的Xms和Xmx ，通常建議將記憶體設定為4G或者8G，可根據任務機的實際配置，提升-Xms與-Xmx，來防止OOM。

   • 調整JVM Xms和Xmx參數的兩種方式：一種是直接更改datax.py；另一種是在啟動的時候，加上對應的參數，例如python datax/bin/datax.py --jvm="-Xms8G -Xmx8G"XXX.json。

使用StarRocks外部表格同步資料

方案介紹

該方案是通過StarRocks的外部表格，將源叢集資料同步至目的地組群；通過JDBC方式，在目的地組群建立目標表，在源叢集建立外部表格，將資料插入外部表格實現資料移轉。

操作步驟

1. 建立JDBC資源。

   create external resource ck_jdbc
   properties (
       "type"="jdbc",
       "user"="default",
       "password"="****",
       "jdbc_uri"="jdbc:clickhouse://172.16.**.**:8123/test",
       "driver_url"="https://****.oss-cn-****-internal.aliyuncs.com/clickhouse-jdbc-0.3.2-all.jar",
       "driver_class"="com.clickhouse.jdbc.ClickHouseDriver"
   );

   相關參數如下表所示。

   參數	說明
   ck_jdbc	JDBC資源的名稱。您可以自訂。
   type	資源類型。固定值為jdbc。
   user	ClickHouse使用者名稱。本文使用的是ClickHouse部署完後的預設使用者default，密碼預設值為空白。如果需要更換使用者，請根據您的實際情況修改並設定相應的密碼。
   password	ClickHouse使用者的密碼。預設使用者default對應的密碼為空白。
   jdbc_uri	填寫格式為jdbc:clickhouse://<clickhouse_host>:<clickhouse_port>/<database_name>。 格式中各參數如下： <clickhouse_host>：ClickHouse的IP地址。 <clickhouse_port>：官方驅動的連接埠預設為8123。如果使用TCP協議的第三方驅動，則預設連接埠為9000。 <database_name>：ClickHouse的資料庫名。
   driver_url	OSS上存放驅動的內網地址。 本樣本使用的是官方驅動，下載clickhouse-jdbc-0.3.2-all.jar，然後上傳至OSS。
   driver_class	驅動類，預設為com.clickhouse.jdbc.ClickHouseDriver。

   建立完JDBC資源後，您還可以進行以下操作：

   • 查看JDBC資源

     -- 查看所有JDBC資源。
     -- 說明：ResourceType列為jdbc。
     SHOW RESOURCES;

   • 刪除JDBC資源

     DROP RESOURCE "ck_jdbc";

     說明

     刪除JDBC資源會導致使用該JDBC資源建立的JDBC外部表格不可用，但目標資料庫的資料並不會丟失。刪除JDBC資源後，如果您仍需通過StarRocks查詢目標資料庫的資料，可以重新建立JDBC資源和JDBC外部表格。

2. 建立JDBC外部表格。

   -- 建立外部表格。
   create external table uk_price_paid_ex (
       price BIGINT(20) NULL COMMENT "",
       date String NULL COMMENT "",
       postcode1 String NULL COMMENT "",
       postcode2 String NULL COMMENT "",
       type String NULL COMMENT "",
       is_new BIGINT(20) NULL COMMENT "",
       duration String NULL COMMENT "",
       addr1 String NULL COMMENT "",
       addr2 String NULL COMMENT "",
       street String NULL COMMENT "",
       locality String NULL COMMENT "",
       town String NULL COMMENT "" ,
       district String NULL COMMENT "",
       county String NULL COMMENT "" 
   ) ENGINE=jdbc 
   properties (
       "resource"="ck_jdbc",
       "table"="uk_price_paid",
       "compression"="ZSTD"
   );

   說明

   • 不支援索引。

   • 不支援通過PARTITION BY、DISTRIBUTED BY來指定資料分布規則。

3. 建立資料表。

   適用於所有資料模型。

   create table uk_price_paid (
       price BIGINT(20) NULL COMMENT "",
       date DATE NULL COMMENT "",
       postcode1 String NULL COMMENT "",
       postcode2 String NULL COMMENT "",
       type String NULL COMMENT "",
       is_new BIGINT(20) NULL COMMENT "",
       duration String NULL COMMENT "",
       addr1 String NULL COMMENT "",
       addr2 String NULL COMMENT "",
       street String NULL COMMENT "",
       locality String NULL COMMENT "",
       town String NULL COMMENT "" ,
       district String NULL COMMENT "",
       county String NULL COMMENT "" 
   ) ENGINE=OLAP
   duplicate key (price)
   distributed by hash(date) buckets 32
   properties(
       "replication_num"="1"
   );

4. 插入資料。

   insert into uk_price_paid select * from uk_price_paid_ex;

常見問題

• 問題報錯

  ERROR 1064 (HY000): Type is not supported on column[date1], JDBC result type is [java.time.LocalDate]

  操作詳情如下圖所示。

  

• 解決方式

  該問題主要是ClickHouse和StarRocks的date欄位類型不符導致的。您可以先修改ClickHouse表中的date欄位類型為String或其他類型，然後設定StarRocks目標表為DATE類型，最後資料插入StarRocks目標表時會自動轉換類型。