全部產品
Search
文件中心

Hologres:MaxCompute分區表資料匯入

更新時間:Aug 21, 2024

本文為您介紹如何將MaxCompute分區表資料匯入到Hologres分區表。

前提條件

背景資訊

通過Hologres中的MaxCompute外表方式向Hologres匯入資料是非常常見的資料匯入模式。在日常工作中會經常需要進行資料匯入,此時可以藉助DataWorks的強大調度和作業編排能力,實現周期性調度,配置一個調度作業覆蓋資料匯入兩個情境,詳情請參見DataWorks作業案例

考慮到作業較為複雜,所以可以利用DataWorks的遷移助手功能,將Data作業案例檔案匯入您的專案中,您即可獲得Data作業案例,之後按照您的具體業務需求更改部分參數或指令碼即可,詳情請參見使用遷移工具匯入DataWorks作業

注意事項

  • 使用暫存資料表的原因是為了保證原子性,只有在匯入完成後才綁定至分區表,為了避免匯入任務失敗時還需要重新刪除表等操作。

  • 對於更新子表分區資料情境,需要刪除子表和重新綁定暫存資料表放入一個事務過程中,保證該過程的事務性。

  • 使用遷移工具匯入DataWorks作業時需滿足以下條件:

詳細操作步驟

  1. MaxCompute資料準備

    1. 登入MaxCompute控制台

    2. 單擊左側導覽列資料分析

    3. SQL查詢頁面,輸入如下SQL語句用於建立分區表,單擊運行

      DROP TABLE IF EXISTS odps_sale_detail;
      
      --建立一張分區表sale_detail。
      CREATE TABLE IF NOT EXISTS odps_sale_detail 
      (
          shop_name STRING
          ,customer_id STRING
          ,total_price DOUBLE
      )
      PARTITIONED BY 
      (
          sale_date STRING
      )
      ;
    4. SQL查詢頁面,輸入如下SQL語句用於向分區表中匯入資料,單擊運行

      -- 向源表增加分區20210815
      ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210815')
      ;
      
      -- 向分區寫入資料
      INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210815') VALUES 
      ('s1','c1',100.1),
      ('s2','c2',100.2),
      ('s3','c3',100.3)
      ;
      
      -- 向源表增加分區20210816
      ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210816')
      ;
      
      -- 向分區寫入資料
      INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210816') VALUES 
      ('s1','c1',100.1),
      ('s2','c2',100.2),
      ('s3','c3',100.3)
      ;
      
      -- 向源表增加分區20210817
      ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210817')
      ;
      
      -- 向分區寫入資料
      INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210817') VALUES 
      ('s1','c1',100.1),
      ('s2','c2',100.2),
      ('s3','c3',100.3)
      ;
      
      -- 向源表增加分區20210818
      ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210818')
      ;
      
      -- 向分區寫入資料
      INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210818') VALUES 
      ('s1','c1',100.1),
      ('s2','c2',100.2),
      ('s3','c3',100.3)
      ;
  2. Hologres中建表

    • 建立外部表格

      1. 登入資料庫

        1. 在HoloWeb開發頁面,單擊中繼資料管理

        2. 中繼資料管理頁面,雙擊左側分類樹中已建立成功的資料庫名稱,單擊確認登入資料庫

      2. 建立外部表格

        1. SQL編輯器頁面,單擊左上方的image建立SQL查詢。

        2. 在新增的臨時Query查詢頁面,選擇已建立的執行個體名資料庫後,在SQL查詢的編輯框輸入如下語句,單擊運行

          DROP FOREIGN TABLE IF EXISTS odps_sale_detail;
          
          -- 建立外部表格
          IMPORT FOREIGN SCHEMA maxcompute_project LIMIT to
          (
              odps_sale_detail
          ) 
          FROM SERVER odps_server INTO public 
          OPTIONS(if_table_exist 'error',if_unsupported_type 'error');
    • 建立分區表(內部表)

      1. 登入資料庫

        1. 在HoloWeb開發頁面,單擊中繼資料管理

        2. 中繼資料管理頁面,雙擊左側分類樹中已建立成功的資料庫名稱,單擊確認登入資料庫

      2. 建立分區表

        1. SQL編輯器頁面,單擊左上方的image建立SQL查詢。

        2. 在新增的臨時Query查詢頁面,選擇已建立的執行個體名資料庫後,請您在SQL查詢的編輯框輸入如下語句,單擊運行

          DROP TABLE IF EXISTS holo_sale_detail;
          
          -- 建立Hologres分區表(內部表)
          BEGIN ;
          CREATE TABLE IF NOT EXISTS holo_sale_detail
          (
              shop_name TEXT
              ,customer_id TEXT 
              ,total_price FLOAT8
              ,sale_date TEXT
          )
          PARTITION BY LIST(sale_date);
          COMMIT;
  3. 分區資料匯入Hologres暫存資料表

    臨時Query查詢頁面,請您在SQL查詢的編輯框輸入如下語句,單擊運行

    此SQL語句將MaxCompute的hologres_test專案中的odps_sale_detail分區表的20210816分區匯入Hologres中的holo_sale_detail分區表的20210816分區。

    說明

    Hologres從V2.1.17版本起支援Serverless Computing能力,針對巨量資料量離線匯入、大型ETL作業、外表巨量資料量查詢等情境,使用Serverless Computing執行該類任務可以直接使用額外的Serverless資源,避免使用執行個體自身資源,無需為執行個體預留額外的計算資源,顯著提升執行個體穩定性、減少OOM機率,且僅需為任務單獨付費。Serverless Computing詳情請參見Serverless Computing概述,Serverless Computing使用方法請參見Serverless Computing使用指南

    -- 清理潛在的暫存資料表
    BEGIN ;
    
    DROP TABLE IF EXISTS holo_sale_detail_tmp_20210816;
    
    COMMIT ;
    
    -- 建立暫存資料表
    SET hg_experimental_enable_create_table_like_properties=on;
    
    BEGIN ;
    
    CALL HG_CREATE_TABLE_LIKE ('holo_sale_detail_tmp_20210816', 'select * from holo_sale_detail'); 
    
    COMMIT;
    
    -- (可選)推薦使用Serverless Computing執行巨量資料量離線匯入和ETL作業
    SET hg_computing_resource = 'serverless';
    
    -- 向暫存資料表插入資料
    INSERT INTO holo_sale_detail_tmp_20210816
    SELECT *
    FROM public.odps_sale_detail
    WHERE sale_date='20210816';
    
    -- 重設配置,保證非必要的SQL不會使用serverless資源。
    RESET hg_computing_resource;
  4. 暫存資料表綁定至Hologres分區表

    臨時Query查詢頁面,請您在SQL查詢的編輯框輸入如下語句,單擊運行

    • 存在舊的子表,則需要先刪除舊子表,再將暫存資料表綁定至Hologres分區表。

      此SQL語句用於刪除子表holo_sale_detail_20210816並將暫存資料表holo_sale_detail_tmp_20210816綁定至holo_sale_detail分區表的20210816分區。

      -- 已有子表時替換子表
      BEGIN ;
      
      -- 刪除舊子表
      DROP TABLE IF EXISTS holo_sale_detail_20210816;
      
      -- 將暫存資料表改名
      ALTER TABLE holo_sale_detail_tmp_20210816 RENAME TO holo_sale_detail_20210816;
      
      -- 將暫存資料表綁定至指定分區表
      ALTER TABLE holo_sale_detail ATTACH PARTITION holo_sale_detail_20210816
      FOR VALUES IN ('20210816')
      ;
      
      COMMIT ;
    • 不存在舊子表,直接將暫存資料表綁定至Hologres分區表。

      此SQL語句用於將暫存資料表holo_sale_detail_tmp_20210816綁定至holo_sale_detail分區表的20210816分區。

      BEGIN ;
      -- 將暫存資料表改名
      ALTER TABLE holo_sale_detail_tmp_20210816 RENAME TO holo_sale_detail_20210816;
      -- 將暫存資料表綁定至指定分區表
      ALTER TABLE holo_sale_detail ATTACH PARTITION holo_sale_detail_20210816
      FOR VALUES IN ('20210816');
      COMMIT ;
  5. ANALYZE Hologres分區表

    臨時Query查詢頁面,請您在SQL查詢的編輯框輸入如下語句,單擊運行

    此SQL語句用於ANALYZE holo_sale_detail分區表,驗證分區表執行計畫。ANALYZE分區表時,僅需ANALYZE父表。

    -- 大量資料匯入後執行ANALYZE分區表父表操作
    ANALYZE holo_sale_detail;
  6. 清理到期的分區子表(按需)

    生產環境中,資料具備生命週期,對於超期的分區需要清理。

    臨時Query查詢頁面,請您在SQL查詢的編輯框輸入如下語句,單擊運行

    此SQL語句清理20210631的分區。

    DROP TABLE IF EXISTS holo_sale_detail_20210631;

DataWorks作業案例

日常工作中往往需要周期性調度以上SQL,此時可以藉助DataWorks的強大調度和作業編排能力,實現周期性調度,且使用一個調度作業覆蓋以上兩個情境。請仔細閱讀以下內容,便於您使用遷移工具匯入DataWorks作業時按照您的具體業務需求更改部分參數或指令碼。商務程序總覽如下。流程總覽

商務程序模組詳解

  • 基礎參數

    基礎參數用於管理整個商務程序中用到的所有參數,主要用到的參數如下。

    編號

    參數名

    類型

    取值

    描述

    1

    datepre31

    變數

    ${yyyymmdd-31}

    用於控制清理到期分區的參數,此處含義為清理31天前的分區。

    2

    datetime1

    變數

    $bizdate

    用於控制建立分區的參數。

    3

    holo_table_name

    常量

    holo_sale_detail

    Hologres分區表名。

    4

    odps_project

    常量

    hologres_test

    MaxCompute專案名。

    5

    odps_table_name

    常量

    odps_sale_detail

    MaxCompute分區表名。

    6

    partition_key

    常量

    sale_date

    MaxCompute分區欄位。

    系統配置圖如下。基礎參數

  • 寫入分區資料至暫存資料表

    該步驟是一個Hologres SQL模組,其中SQL代碼如下。

    說明

    Hologres從V2.1.17版本起支援Serverless Computing能力,針對巨量資料量離線匯入、大型ETL作業、外表巨量資料量查詢等情境,使用Serverless Computing執行該類任務可以直接使用額外的Serverless資源,避免使用執行個體自身資源,無需為執行個體預留額外的計算資源,顯著提升執行個體穩定性、減少OOM機率,且僅需為任務單獨付費。Serverless Computing詳情請參見Serverless Computing概述,Serverless Computing使用方法請參見Serverless Computing使用指南

    -- 清理潛在的暫存資料表
    BEGIN ;
    
    DROP TABLE IF EXISTS ${holo_table_name}_tmp_${datetime1};
    
    COMMIT ;
    
    -- 建立暫存資料表
    SET hg_experimental_enable_create_table_like_properties=on;
    
    BEGIN ;
    
    CALL HG_CREATE_TABLE_LIKE ('${holo_table_name}_tmp_${datetime1}', 'select * from ${holo_table_name}'); 
    
    COMMIT;
    
    -- 向暫存資料表插入資料
    
    -- (可選)推薦使用Serverless Computing執行巨量資料量離線匯入和ETL作業
    SET hg_computing_resource = 'serverless';
    
    INSERT INTO ${holo_table_name}_tmp_${datetime1}
    SELECT *
    FROM public.${odps_table_name}
    WHERE ${partition_key}='${datetime1}';
    
    -- 重設配置,保證非必要的SQL不會使用Serverless資源
    RESET hg_computing_resource;

    需要將基礎參數綁定至該模組上遊,用於控制其中的參數變數,系統配置如下:寫入分區資料至暫存資料表

  • 替換子表

    該步驟是一個Hologres SQL模組,用於替換已有子表。將替換子表相關過程放在一個事務中,保證執行的事務性,SQL代碼如下。

    -- 已有子表時替換子表
    BEGIN ;
    
    -- 刪除已經存在的子表
    DROP TABLE IF EXISTS ${holo_table_name}_${datetime1};
    
    -- 將暫存資料表改名
    ALTER TABLE ${holo_table_name}_tmp_${datetime1} RENAME TO ${holo_table_name}_${datetime1};
    
    -- 將暫存資料表綁定至指定分區表
    ALTER TABLE ${holo_table_name} ATTACH PARTITION ${holo_table_name}_${datetime1}
    FOR VALUES IN ('${datetime1}');
    
    COMMIT ;

    需要將基礎參數綁定至該模組上遊,用於控制其中的參數變數,系統配置如下。替換子表

  • 收集分區表的統計資訊

    該步驟是一個Hologres SQL模組,收集父表的統計資訊,SQL代碼如下。

    -- 大量資料匯入後執行ANALYZE分區表父表操作
    ANALYZE ${holo_table_name};

    需要將基礎參數綁定至該模組上遊,用於控制其中的參數變數,系統配置如下。收集分區表的統計資訊

  • 清理到期子表

    生產環境中,資料具備生命週期,對於超期的分區需要清理。

    現以僅在Hologres中儲存最近31天的分區為例,由於之前設定的參數為datepre31=${yyyymmdd-31},所以清理到期子表的SQL代碼如下。

    -- 清理到期子表
    
    BEGIN ;
    
    DROP TABLE IF EXISTS ${holo_table_name}_${datepre31};
    
    COMMIT ;

    所以在作業運行時,如果bizdate=20200309,則datepre31=20200207,這樣即可達到清理分區的目的。

    同時需要將基礎參數綁定至該模組上遊,用於控制其中的參數變數,系統配置如下。清理到期子表

使用遷移工具匯入DataWorks作業

  • 考慮到作業較為複雜,所以可以利用DataWorks的遷移助手功能,將以下檔案匯入您的專案中,您即可獲得以上說明的DataWorks的作業,之後按照您的具體業務需求更改部分參數或指令碼即可。

  • 下載如下作業包:DataWorks作業包

  1. 進入DataWorks遷移助手,詳情請參見進入遷移助手

  2. 在遷移助手的左側導覽列,單擊DataWorks遷移 > DataWorks匯入

  3. DataWorks匯入頁面,單擊右上方的建立匯入任務

  4. 建立匯入任務對話方塊中,配置各項參數。

    建立匯入任務

    參數

    描述

    匯入名稱

    自訂名稱。匯入名稱僅支援大小寫字母、中文、數字、底線(_)和英文句號(.)。

    上傳方式

    上傳檔案的方式。

    • 本地上傳:上傳匯出包檔案小於或等於30 MB時,您可使用本方式上傳匯出包檔案到DataWorks工作空間中。

    • OSS連結:上傳匯出包檔案大於30 MB時,請將匯出包檔案上傳至OSS儲存。在OSS儲存控制台檔案詳情頁面複製URL連結,將擷取到的OSS連結上傳至DataWorks工作空間中。OSS上傳操作請參見控制台上傳檔案,擷取OSS下載連結請參見分享檔案下載連結

    備忘

    對匯入任務進行簡單描述。

  5. 單擊確認,進入匯入任務設定頁面,設定匹配關係。

  6. 單擊左下方的開始匯入,在請確認對話方塊中,單擊確認

    1. 匯入成功後,在您的資料開發模組中則會出現以上提及的周期性作業。匯入成功

    2. 同時在手工作業流程中會出現相關的DDL語句。手工作業流程