Hologres：Hologres批量寫入最佳實務

基礎環境準備

您需準備以下環境：

• 開通V2.2.25或以上版本的Hologres執行個體，並建立資料庫。

• 建立EMR-Spark叢集（Spark版本需大於或等於3.3.0）。詳情請參見建立叢集。

• 下載Spark-Connector包。

  您可通過Maven中央倉庫下載Spark讀寫Hologres時需要引用的連接器JAR包hologres-connector-spark-3.x。

本文中使用的環境資訊如下：

測試資料準備

1. 準備未經處理資料。

   1. 登入EMR-Spark叢集的master節點，詳情請參見串連ECS執行個體的方式。

   2. 單擊TPC-H_Tools_v3.0.0.zip下載TPCH工具，將其複製到叢集master節點的ECS機器中進行解壓，然後進入TPC-H_Tools_v3.0.0/TPC-H_Tools_v3.0.0/dbgen目錄。

   3. 執行如下代碼，在dbgen目錄下產生1 TB測試集檔案customer.tbl，原tbl格式檔案大小為23 GB。

      ./dbgen -s 1000 -T c

      TPC-H 1 TB資料集中customer表的詳細資料如下：

      表資訊	說明
      欄位資料量	8
      欄位類型	INT、BIGINT、TEXT、DECIMAL
      資料行數	150,000,000
      表Shard數	40

2. 測試資料匯入Spark。

   執行如下命令，將customer.tbl檔案上傳到Spark叢集。

   hadoop fs -put customer.tbl <spark_resource>

   其中spark_resource為建立EMR-Spark叢集時，配置的叢集儲存根路徑。

3. 在Hologres中建立不同儲存格式的表。SQL命令如下：

   行列共存

   CREATE TABLE test_table_mixed (
       C_CUSTKEY BIGINT PRIMARY KEY,
       C_NAME TEXT,
       C_ADDRESS TEXT,
       C_NATIONKEY INT,
       C_PHONE TEXT,
       C_ACCTBAL DECIMAL(15, 2),
       C_MKTSEGMENT TEXT,
       C_COMMENT TEXT
   )
   WITH (
       orientation = 'column,row'
   );

   列存（有主鍵）

   CREATE TABLE test_table_column (
       C_CUSTKEY BIGINT PRIMARY KEY,
       C_NAME TEXT,
       C_ADDRESS TEXT,
       C_NATIONKEY INT,
       C_PHONE TEXT,
       C_ACCTBAL DECIMAL(15, 2),
       C_MKTSEGMENT TEXT,
       C_COMMENT TEXT
   )
   WITH (
       orientation = 'column'
   );

   列存（無主鍵）

   CREATE TABLE test_table_column_no_pk (
       C_CUSTKEY BIGINT,
       C_NAME TEXT,
       C_ADDRESS TEXT,
       C_NATIONKEY INT,
       C_PHONE TEXT,
       C_ACCTBAL DECIMAL(15, 2),
       C_MKTSEGMENT TEXT,
       C_COMMENT TEXT
   )
   WITH (
       orientation = 'column'
   );

   行存（有主鍵）

   CREATE TABLE test_table_row (
       C_CUSTKEY BIGINT PRIMARY KEY,
       C_NAME TEXT,
       C_ADDRESS TEXT,
       C_NATIONKEY INT,
       C_PHONE TEXT,
       C_ACCTBAL DECIMAL(15, 2),
       C_MKTSEGMENT TEXT,
       C_COMMENT TEXT
   )
   WITH (
       orientation = 'row'
   );

   行存（無主鍵）

   CREATE TABLE test_table_row_no_pk (
       C_CUSTKEY BIGINT,
       C_NAME TEXT,
       C_ADDRESS TEXT,
       C_NATIONKEY INT,
       C_PHONE TEXT,
       C_ACCTBAL DECIMAL(15, 2),
       C_MKTSEGMENT TEXT,
       C_COMMENT TEXT
   )
   WITH (
       orientation = 'row'
   );

測試組態

本文主要測試不同模式下的匯入效能。

1. 登入EMR-Spark叢集的master節點（登入方式請參見串連ECS執行個體的方式），上傳已下載的Spark-Connector包，然後執行如下命令進入spark-sql互動介面。

   說明

   通過調整spark.sql.files.maxPartitionBytes參數值， 可以控制Spark讀取HDFS檔案的並發。此處並發控製為40。

   # 進入spark-sql互動介面
   
   spark-sql --jars <path>/hologres-connector-spark-3.x-1.5.2-jar-with-dependencies.jar \
   --conf spark.executor.instances=40 \
   --conf spark.executor.cores=1 \
   --conf spark.executor.memory=4g \
   --conf spark.sql.files.maxPartitionBytes=644245094

   其中path為hologres-connector-spark-3.x-1.5.2-jar-with-dependencies.jar所在的根路徑。

2. 在spark-sql互動介面執行如下SQL，通過建立暫存資料表的方式寫入資料。

   由於測試過程中，需要多次調整參數以測試不同模式的寫入效能，此處選擇建立暫存資料表的方式。

   說明

   實際使用中，您可直接使用Catalog載入Hologres表，更加方便。

   -- 建立csv格式的暫存資料表
   CREATE TEMPORARY VIEW csvtable (
     c_custkey BIGINT,
     c_name STRING,
     c_address STRING,
     c_nationkey INT,
     c_phone STRING,
     c_acctbal DECIMAL(15, 2),
     c_mktsegment STRING,
     c_comment STRING)
   USING csv OPTIONS (
     path "<spark_resources>/customer.tbl", sep "|"
   );
   
   CREATE TEMPORARY VIEW hologresTable (
     c_custkey BIGINT,
     c_name STRING,
     c_address STRING,
     c_nationkey INT,
     c_phone STRING,
     c_acctbal DECIMAL(15, 2),
     c_mktsegment STRING,
     c_comment STRING)
   USING hologres OPTIONS (
     jdbcurl "jdbc:postgresql://<hologres_vpc_endpoint>/<database_name>",
     username "<accesskey_id>", 
     password "<accesskey_secret>", 
     table "<table_name>",
     direct_connect "false",
     write.mode "auto",
     write.insert.thread_size "3",
     write.insert.batch_size "2048"
   );
   
   INSERT INTO hologresTable SELECT * FROM csvTable;

   參數說明如下：

   參數名	描述
   spark_resources	建立EMR-Spark叢集時，配置的叢集儲存根路徑。 您可登入EMR on ECS控制台，單擊目的地組群ID，在基礎資訊頁面的叢集資訊地區擷取叢集儲存根路徑。
   hologres_vpc_endpoint	Hologres執行個體對應的VPC網路類型的網域名稱。 您可登入Hologres管理主控台，單擊目標執行個體ID，在執行個體詳情頁的網路資訊地區，擷取指定VPC的網域名稱。例如：杭州地區的VPC Endpoint格式為<Instance ID>-cn-hangzhou-vpc-st.hologres.aliyuncs.com:80。
   database_name	Hologres執行個體的資料庫名稱。
   accesskey_id	具有Hologres相應Database讀取許可權的AccessKey ID。
   accesskey_secret	具有Hologres相應Database讀取許可權的AccessKey Secret。
   table_name	待寫入的Hologres目標表名稱。
   write.mode	寫入模式，取值如下： auto ：預設值，Connector自行選擇最佳的模式。 insert：使用INSERT INTO VALUES方式寫入。 stream：使用FIXED COPY流式寫入。 bulk_load ：使用COPY模式大量匯入無主鍵表。 bulk_load_on_conflict ：使用COPY模式大量匯入有主鍵表。
   write.insert.thread_size	寫入並發，僅使用INSERT寫入時生效。
   write.insert.batch_size	寫入攢批，僅使用INSERT寫入時生效。
   write.on_conflict_action	INSERT_OR_REPLACE（預設）：主鍵衝突更新。 INSERT_OR_IGNORE： 主鍵衝突忽略。

   更多參數資訊請參見參數說明。

測試情境

測試結果

測試結果包括以下欄位：

SELECT
    COUNT(*), AVG(duration) AS avg_duration_ms
FROM
    hologres.hg_query_log
WHERE
    query_start >= '<start_time>' AND query_start <= '<end_time>'
    AND query LIKE '%<test_table_column>%' AND command_tag = 'COPY';

詳細測試結果如下：