MaxCompute:ODS

このチュートリアルでは、ODSレイヤーのデータに、注文の詳細、ユーザーの詳細、およびトランザクションデータシステムの製品の詳細が含まれています。 論理的には、生データは2次元テーブルに記憶される。 ODSレイヤーは、データウェアハウスのデータモデリングから除外される。 ただし、ODSレイヤーを適切に計画し、データを同期する必要があります。 このチュートリアルでは、例として次のODSテーブルを使用します。

• s_auction: オークションの製品に関する情報を記録します。

• s_sale: 販売用製品に関する情報を記録します。

• s_users_extra: ユーザーに関する詳細情報を記録します。

• s_biz_order_delta: 新しい製品の注文に関する情報を記録します。

• s_logistics_order_delta: 新しいロジスティクス注文に関する情報を記録します。

• s_pay_order_delta: 新しい支払い注文に関する情報を記録します。

以下のCREATE TABLEステートメントは、便宜上提供されています。 テーブルの作成方法の詳細については、「テーブル操作」をご参照ください。

CREATE TABLE IF NOT EXISTS s_auction
(
    id                             STRING COMMENT 'Product ID',
    title                          STRING COMMENT 'Product name',
    gmt_modified                   STRING COMMENT 'Last modification date of the product',
    price                          DOUBLE COMMENT 'Product price in CNY',
    starts                         STRING COMMENT 'Time when the product is put on the shelf',
    minimum_bid                    DOUBLE COMMENT 'Product opening bid in CNY',
    duration                       STRING COMMENT 'Validity period of the product for sale, in days',
    incrementnum                   DOUBLE COMMENT 'Increment of the auction price',
    city                           STRING COMMENT 'City of the product',
    prov                           STRING COMMENT 'Province of the product',
    ends                           STRING COMMENT 'Time when the product is taken off the shelf',
    quantity                       BIGINT COMMENT 'Quantity',
    stuff_status                   BIGINT COMMENT 'Product status: 0: new; 1: idle; 2: second-hand',
    auction_status                 BIGINT COMMENT 'Product sales status: 0: normal; 1: deleted; 2: taken off the shelf; 3: never put on the shelf',
    cate_id                        BIGINT COMMENT 'Product category ID',
    cate_name                      STRING COMMENT 'Product category name',
    commodity_id                   BIGINT COMMENT 'Commodity ID',
    commodity_name                 STRING COMMENT 'Commodity name',
    umid                           STRING COMMENT 'Buyer UMID'
)
COMMENT 'ODS table of products for auction'
PARTITIONED BY (ds         STRING COMMENT 'Format: YYYYMMDD')
LIFECYCLE 400;

CREATE TABLE IF NOT EXISTS s_sale
(
    id                             STRING COMMENT 'Product ID',
    title                          STRING COMMENT 'Product name',
    gmt_modified                   STRING COMMENT 'Last modification date of the product',
    starts                         STRING COMMENT 'Time when the product is put on the shelf',
    price                          DOUBLE COMMENT 'Product price in CNY',
    city                           STRING COMMENT 'City of the product',
    prov                           STRING COMMENT 'Province of the product',
    quantity                       BIGINT COMMENT 'Quantity',
    stuff_status                   BIGINT COMMENT 'Product status: 0: new; 1: idle; 2: second-hand',
    auction_status                 BIGINT COMMENT 'Product sales status: 0: normal; 1: deleted; 2: taken off the shelf; 3: never put on the shelf',
    cate_id                        BIGINT COMMENT 'Product category ID',
    cate_name                      STRING COMMENT 'Product category name',
    commodity_id                   BIGINT COMMENT 'Commodity ID',
    commodity_name                 STRING COMMENT 'Commodity name',
    umid                           STRING COMMENT 'Buyer UMID'
)
COMMENT 'ODS table of products for sale'
PARTITIONED BY (ds      STRING COMMENT 'Format: YYYYMMDD')
LIFECYCLE 400;


CREATE TABLE IF NOT EXISTS s_users_extra
(
    id                  STRING COMMENT 'User ID',
    logincount        BIGINT COMMENT 'Number of logons',
    buyer_goodnum     BIGINT COMMENT 'Number of favorable comments received as a buyer',
    seller_goodnum    BIGINT COMMENT 'Number of favorable comments received as a seller',
    level_type        BIGINT COMMENT '1: level-1 shop; 2: level-2 shop; 3: level-3 shop',
    promoted_num      BIGINT COMMENT '1: class-A service; 2: class-B service; 3: class-C service',
    gmt_create        STRING COMMENT 'Creation time',
    order_id          BIGINT COMMENT 'Order ID',
    buyer_id            BIGINT COMMENT 'Buyer ID',
    buyer_nick        STRING COMMENT 'Buyer nickname',
    buyer_star_id     BIGINT COMMENT 'Buyer star ID',
    seller_id         BIGINT COMMENT 'Seller ID',
    seller_nick       STRING COMMENT 'Seller nickname',
    seller_star_id      BIGINT COMMENT 'Seller star ID',
    shop_id             BIGINT COMMENT 'Shop ID'
    shop_name         STRING COMMENT 'Shop name'
)
COMMENT 'User information extended table'
PARTITIONED BY (ds       STRING COMMENT 'yyyymmdd')
LIFECYCLE 400;

CREATE TABLE IF NOT EXISTS s_biz_order_delta
(
    biz_order_id         STRING COMMENT 'Order ID',
    pay_order_id          STRING COMMENT 'Payment order ID',
    logistics_order_id    STRING COMMENT 'Logistics order ID',
    buyer_nick           STRING COMMENT 'Buyer nickname',
    buyer_id              STRING COMMENT 'Buyer ID',
    seller_nick          STRING COMMENT 'Seller nickname',
    seller_id             STRING COMMENT 'Seller ID',
    auction_id             STRING COMMENT 'Product ID',
    auction_title        STRING COMMENT 'Product title',
    auction_price        DOUBLE COMMENT 'Product price',
    buy_amount           BIGINT COMMENT 'Quantity of purchased products',
    buy_fee              BIGINT COMMENT 'Purchase order amount',
    pay_status           BIGINT COMMENT 'Payment status: 1: unpaid; 2: paid; 3: refunded',
    logistics_id           BIGINT COMMENT 'Logistics order ID',
    mord_cod_status      BIGINT COMMENT 'Logistics status: 0: initial state; 1: order received; 2: order receiving timed out; 3: package picked up; 4: package pickup failed; 5: package signed off; 6: package sign-off failed; 7: logistics order canceled by the user',
    status               BIGINT COMMENT 'Order status: 0: normal; 1: invisible',
    sub_biz_type         BIGINT COMMENT 'Business type: 1: auction; 2: purchase',
    end_time             STRING COMMENT 'Transaction end time',
    shop_id                BIGINT COMMENT 'Shop ID'
)
COMMENT 'Daily incremental table of successful orders'
PARTITIONED BY (ds       STRING COMMENT 'yyyymmdd')
LIFECYCLE 7200;


CREATE TABLE IF NOT EXISTS s_logistics_order_delta
(
    logistics_order_id STRING COMMENT 'Logistics order ID',
    post_fee           DOUBLE COMMENT 'Logistics fee',
    address            STRING COMMENT 'Shipping address',
    full_name          STRING COMMENT 'Recipient full name',
    mobile_phone       STRING COMMENT 'Mobile number',
    prov               STRING COMMENT 'Province',
    prov_code          STRING COMMENT 'Province ID',
    city               STRING COMMENT 'City',
    city_code          STRING COMMENT 'City ID',
    logistics_status   BIGINT COMMENT 'Logistics status:
1: not shipped
2: shipped
3: received
4: returned
5: picking',
    consign_time       STRING COMMENT 'Shipping time',
    gmt_create         STRING COMMENT 'Order creation time',
    shipping           BIGINT COMMENT 'Shipping method:
1: surface mail
2: express delivery
3: EMS',
    seller_id          STRING COMMENT 'Seller ID',
    buyer_id           STRING COMMENT 'Buyer ID'
)
COMMENT 'Daily incremental table of logistics orders'
PARTITIONED BY (ds                 STRING COMMENT 'Date')
LIFECYCLE 7200;


CREATE TABLE IF NOT EXISTS s_pay_order_delta
(
    pay_order_id     STRING COMMENT 'Payment order ID',
    total_fee        DOUBLE COMMENT 'Total amount payable (Quantity × Unit price)',
    seller_id STRING COMMENT 'Seller ID',
    buyer_id  STRING COMMENT 'Buyer ID',
    pay_status       BIGINT COMMENT 'Payment status:
1: waiting for payment 
2: waiting for shipping 
3: transaction succeeded',
    pay_time         STRING COMMENT 'Payment time',
    gmt_create       STRING COMMENT 'Order creation time',
    refund_fee       DOUBLE COMMENT 'Refund amount (freight included)',
    confirm_paid_fee DOUBLE COMMENT 'Total revenue of received and confirmed products'
)
COMMENT 'Daily incremental table of payment orders'
PARTITIONED BY (ds        STRING COMMENT 'Date')
LIFECYCLE 7200;

履歴データを分析するには、時間ディメンションをパーティションフィールドとしてODSテーブルに追加します。 実際には、増分ストレージ、フルストレージ、またはジッパーストレージを選択して、ODSテーブルにデータを保存できます。

• 増分ストレージ

  増分ストレージモードでは、データは日単位で格納され、データタイムスタンプをパーティションフィールドとして使用します。 各パーティションは、毎日の増分ビジネスデータを格納します。 例：

  • 2016年1月1日、ユーザAは会社AのEコマースショップBを訪問した。ログエントリt1は会社Aのeコマースログに生成された。1月2日、ユーザAは会社AのEコマースショップCを訪問した。ログエントリt2は会社Aのeコマースログに20160101された。t2は20160102パーティションに格納される。

  • 2016年1月1日、ユーザーAは会社Aのeコマースウェブサイトで製品Bを購入しました。ログエントリt1は会社Aのトランザクションログに生成されました。1月2日、ユーザーAは製品Bを返しました。ログエントリt1は会社Aのトランザクションログで更新されました。増分格納モードでは、最初のログエントリt1は20160101パーティションに格納されました。更新されたログエントリt1が20160102パーティションに格納されました。

  説明

  増分ストレージは、トランザクションやログなどのトランザクション性の高いデータを格納するODSテーブルに適しています。 これらのテーブルは大量のデータを格納し、フルストレージを採用する場合は高いストレージコストを必要とします。 さらに、これらのテーブルの子孫アプリケーションは、履歴フルデータにアクセスすることはめったにありません。 完全なデータにアクセスするための要件は、データウェアハウス内のデータの集約に基づいて満たすことができる。 たとえば、ログのODSテーブルは更新されません。 この場合、すべての増分パーティションに対してUNION操作を実行することで、完全なデータを取得できます。

• フルストレージ

  フルストレージモードでは、データは日単位で保存され、データタイムスタンプをパーティションフィールドとして使用します。 各パーティションは、データタイムスタンプの時点で完全なビジネスデータを格納します。 2016年1月1日に、売り手Aは、会社Aのeコマースウェブサイトに製品BおよびCを掲載した。データエントリt1およびt2は、フロントエンド製品テーブルに生成された。 1月2日、売り手Aは製品Bを棚から取り出し、製品Dを掲示した。フロントエンド製品テーブルでは、データエントリt1が更新され、データエントリt3が生成された。 フルストレージモードでは、データエントリt1及びt2は20160101パーティションに格納され、更新されたデータエントリt1及びデータエントリt2及びt3は20160102パーティションに格納される。

  説明

  フルストレージは、製品カテゴリなど、少量のデータを含むゆっくりと変化するディメンション (SCD) に適しています。

• ジッパーストレージ

  ジッパーストレージモードでは、2つのタイムスタンプフィールドstart_dtとend_dtが追加され、すべてのデータ変更が日単位で記録されます。 ほとんどの場合、2つのタイムスタンプフィールドはパーティションフィールドとしても使用されます。

  次の表は、ジッパーストレージモードでデータを保存する方法を示しています。

  プロダクト	start_dt	end_dt	Seller	Status
  B	20160101	20160102	A	棚に置く
  C	20160101	30001231	A	棚に置く
  B	20160102	30001231	A	棚を脱ぐ

  ジッパーストレージモードでは、ディセンダントアプリケーションはタイムスタンプフィールドを指定して履歴データを取得できます。 たとえば、2016年1月1日のデータにアクセスする場合、タイムスタンプフィールドstart_dtおよびend_dtには、start_dt<=20160101およびend_dt>20160101の条件を指定するだけです。

MaxComputeディメンションテーブルの主キーとして、代理キーではなく自然キーを使用することを推奨します。 これは次の理由によるものです。

1. MaxComputeは分散型計算エンジンです。 MaxComputeがグローバルに一意な代理キーを生成するための作業負荷が重くなります。 特に、大量のデータの場合、代理キーの生成は、より複雑で不要である。

2. 代理キーを使用すると、抽出、変換、ロード (ETL) プロセスの複雑さが増し、ETLジョブの開発および維持のコストが増加します。

たとえば、製品データの完全なスナップショットは、製品ディメンションに対して毎日生成されます。 その日の商品情報または最新の商品情報は、任意の日のファクトテーブルから取得できます。 これを実現するには、日付を指定し、製品ディメンションテーブルのナチュラルキーを使用して、ファクトテーブルのスナップショットデータをディメンションテーブルのスナップショットデータに関連付けることができます。 スナップショットモードには、次の利点があります。

• このモードは、SCDを処理するのに簡単で効果的です。 開発コストとメンテナンスコストが低くなります。

• このモードは使いやすく理解しやすいです。 日付を指定することで、その日のスナップショットデータを簡単に取得できます。 ディメンションテーブルの自然キーを使用して、任意の日のファクトテーブルスナップショットをその日のディメンションテーブルスナップショットに関連付けることができます。

さらに開発するには、ODSレイヤーのデータをさまざまなデータソースシステムからMaxComputeに同期する必要があります。 DataWorks Data Integration機能を使用してデータを同期することを推奨します。 詳細については、「概要」をご参照ください。 DataWorks Data Integration機能を使用する場合は、次のルールに従うことを推奨します。

• テーブルスキーマの一貫性を維持するために、システムのソーステーブルのデータをMaxComputeに1回だけ同期できます。

• Data Integrationは、データ同期ソリューションを提供します。 同期ルールを設定して、フルデータと増分データを同期し、増分データをリアルタイムで書き込み、増分データとフルデータを新しいテーブルパーティションに自動的にマージして書き込むことができます。 詳細については、「完全同期機能と増分同期機能の概要」をご参照ください。

• データストレージコストを削減し、ポリシー制御を課すために、統計日時ごとにパーティションテーブルのODSレイヤーにデータを保存することをお勧めします。