AnalyticDB:データレイクテーブルの作成

カテゴリフィールド

• XIHE

  -- c_mktsegment (市場セグメント) でパーティション分割します。
  -- このフィールドには 'AUTOMOBILE'、'BUILDING'、'FURNITURE' などの 5 つの一意の値しかなく、ID パーティション分割に最適です。
  CREATE TABLE db_iceberg.customer (
      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
  )
  PARTITIONED BY (c_mktsegment)
  STORED AS ICEBERG
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';
  

• Spark SQL

  CREATE TABLE db_iceberg.customer (
      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 iceberg
  PARTITIONED BY (c_mktsegment)
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

時間ディメンション (years/months/days) による階層的なパーティション分割

• XIHE

  -- 注文日で日単位のパーティション分割を行います。これは最も一般的なアプローチであり、クエリパフォーマンスと管理オーバーヘッドのバランスが取れています。
  CREATE TABLE db_iceberg.orders (
      o_orderkey      BIGINT,
      o_custkey       BIGINT,
      o_orderstatus   STRING,
      o_totalprice    DECIMAL(15,2),
      o_orderdate     DATE,        -- ネイティブ TPC-H フィールド
      o_orderpriority STRING,
      o_clerk         STRING,
      o_shippriority  INT,
      o_comment       STRING
  )
  PARTITIONED BY (day(o_orderdate))  -- パーティション数とプルーニング効率のバランスを取るために推奨されます。
  STORED AS ICEBERG
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

• Spark SQL

  CREATE TABLE db_iceberg.orders (
      o_orderkey      BIGINT,
      o_custkey       BIGINT,
      o_orderstatus   STRING,
      o_totalprice    DECIMAL(15,2),
      o_orderdate     DATE,
      o_orderpriority STRING,
      o_clerk         STRING,
      o_shippriority  INT,
      o_comment       STRING
  )
  USING iceberg -- 注：Spark の Iceberg は days() 変換関数を使用し、明示的に作成された列を必要としません。
  PARTITIONED BY (days(o_orderdate))
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

時間単位のパーティション分割

• XIHE

  -- l_receiptdate が TIMESTAMP 型に拡張され、リアルタイムの受領タイムスタンプをシミュレートすると仮定します。
  CREATE TABLE db_iceberg.lineitem_realtime (
      l_orderkey      BIGINT,
      l_partkey       BIGINT,
      l_suppkey       BIGINT,
      l_linenumber    INT,
      l_quantity      DECIMAL(15,2),
      l_extendedprice DECIMAL(15,2),
      l_discount      DECIMAL(15,2),
      l_tax           DECIMAL(15,2),
      l_returnflag    STRING,
      l_linestatus    STRING,
      l_shipdate      DATE,
      l_commitdate    DATE,
      l_receipttime   TIMESTAMP,   -- シミュレーション：秒単位まで正確な受領時間。
      l_shipmode      STRING
  )
  PARTITIONED BY (hour(l_receipttime))  -- 時間単位でパーティション分割して、ほぼリアルタイムのモニタリングをサポートします。
  STORED AS ICEBERG
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';
  

• Spark SQL

  CREATE TABLE db_iceberg.lineitem_realtime (
      l_orderkey      BIGINT,
      l_partkey       BIGINT,
      l_suppkey       BIGINT,
      l_linenumber    INT,
      l_quantity      DECIMAL(15,2),
      l_extendedprice DECIMAL(15,2),
      l_discount      DECIMAL(15,2),
      l_tax           DECIMAL(15,2),
      l_returnflag    STRING,
      l_linestatus    STRING,
      l_shipdate      DATE,
      l_commitdate    DATE,
      l_receipttime   TIMESTAMP,
      l_shipmode      STRING
  )
  USING iceberg -- Iceberg の機能：hours() 変換関数を使用して、新しい列を追加せずに隠れパーティション分割を行います。
  PARTITIONED BY (hours(l_receipttime))
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

ハッシュバケット化

• XIHE

  -- カーディナリティの高い外部キー l_partkey にハッシュバケット化を適用して、小さなファイルを防ぎ、JOIN パフォーマンスを向上させます。
  CREATE TABLE db_iceberg.lineitem (
      l_orderkey      BIGINT,
      l_partkey       BIGINT,      -- 高カーディナリティ (約 2,000 万の一意の値)
      l_suppkey       BIGINT,
      l_linenumber    INT,
      l_quantity      DECIMAL(15,2),
      l_extendedprice DECIMAL(15,2),
      l_discount      DECIMAL(15,2),
      l_tax           DECIMAL(15,2),
      l_returnflag    STRING,
      l_linestatus    STRING,
      l_shipdate      DATE,
      l_commitdate    DATE,
      l_receiptdate   DATE,
      l_shipmode      STRING
  )
  PARTITIONED BY (bucket(l_partkey,64))  -- データを 64 個のバケットに均等に分散させます。
  STORED AS ICEBERG
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

• Spark SQL

  CREATE TABLE db_iceberg.lineitem (
      l_orderkey      BIGINT,
      l_partkey       BIGINT,      -- 高カーディナリティ
      l_suppkey       BIGINT,
      l_linenumber    INT,
      l_quantity      DECIMAL(15,2),
      l_extendedprice DECIMAL(15,2),
      l_discount      DECIMAL(15,2),
      l_tax           DECIMAL(15,2),
      l_returnflag    STRING,
      l_linestatus    STRING,
      l_shipdate      DATE,
      l_commitdate    DATE,
      l_receiptdate   DATE,
      l_shipmode      STRING
  )
  USING iceberg
  -- Iceberg 構文：bucket(バケット数, 列名)
  -- これにより、ハッシュ値に基づいてデータを 64 の論理バケットに分散する暗黙的なパーティション列が生成されます。
  PARTITIONED BY (bucket(64, l_partkey))
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

truncate[len] 文字列プレフィックスによるパーティション分割

• XIHE

  -- 電話番号の国別コードのプレフィックスでパーティション分割します。(例: '13-' は中国のキャリアを表します)。
  CREATE TABLE db_iceberg.customer_by_phone (
      c_custkey     BIGINT,
      c_name        STRING,
      c_phone       STRING,        -- フォーマット: '13-888-999-1234'
      c_acctbal     DECIMAL(15,2),
      c_mktsegment  STRING
  )
  PARTITIONED BY (truncate(c_phone,3))  -- 先頭 3 文字 ('13-') に切り詰めます。
  STORED AS ICEBERG
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

• Spark SQL

  CREATE TABLE db_iceberg.customer_by_phone (
      c_custkey     BIGINT,
      c_name        STRING,
      c_phone       STRING,
      c_acctbal     DECIMAL(15,2),
      c_mktsegment  STRING
  )
  USING iceberg
  -- Iceberg は truncate(width, column_name) 変換をネイティブにサポートしています。
  -- データが書き込まれると、Iceberg は c_phone の最初の 3 文字を自動的に計算し、対応するディレクトリにデータを保存します。
  PARTITIONED BY (truncate(3, c_phone))
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

複合パーティション分割

複数レベルのパーティション分割は、パーティション数のバランスを取り、過剰なパーティション分割を回避すると同時に、均等なデータ分散を確保してデータスキューを防ぎます。この戦略は、大規模なデータセットのクエリに最適です。

次の例は、汎用的なログテーブルの作成方法を示しています。

• XIHE

  -- ユーザーイベントログテーブル：時間、高カーディナリティ ID のバケット化、およびリージョン切り捨てを使用する 3 レベルの複合パーティション戦略。
  CREATE TABLE db_iceberg.user_event_log (
      event_id      BIGINT,
      user_id       BIGINT,          -- 高カーディナリティのユーザー ID
      session_id    STRING,
      event_type    STRING,
      event_time    TIMESTAMP,       -- 秒単位まで正確なタイムスタンプ
      country_code  STRING,          -- 国コード (例：'CN'、'US'、'DE')
      device_type   STRING,
      payload       STRING
  )
  PARTITIONED BY (
      day(event_time),            -- レベル 1：日単位でパーティション分割し、効率的な時間ベースのプルーニングを実現します。
      bucket(user_id,64),         -- レベル 2：高カーディナリティの user_id に 64 バケットのハッシュバケット化を適用して、小さなファイルを防ぎます。
      truncate(country_code,2)    -- レベル 3：国コードの最初の 2 文字に切り捨てて、リージョン別の集計を行います。
  )
  STORED AS ICEBERG
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

  -- TPC-H lineitem テーブル：出荷日でパーティション分割され、部品 ID でバケット化された最適化されたファクトテーブル。
  CREATE TABLE db_iceberg.lineitem_multiple_part (
      l_orderkey      BIGINT,
      l_partkey       BIGINT,        -- 高カーディナリティの外部キー (約 2,000 万の一意の値)
      l_suppkey       BIGINT,
      l_linenumber    INT,
      l_quantity      DECIMAL(15, 2),
      l_extendedprice DECIMAL(15, 2),
      l_discount      DECIMAL(15, 2),
      l_tax           DECIMAL(15, 2),
      l_returnflag    STRING,
      l_linestatus    STRING,
      l_shipdate      DATE,          -- TPC-H のコアタイムフィールド
      l_commitdate    DATE,
      l_receiptdate   DATE,
      l_shipinstruct  STRING,
      l_ship

• Spark SQL

  CREATE TABLE db_iceberg.user_event_log (
      event_id      BIGINT,
      user_id       BIGINT,
      session_id    STRING,
      event_type    STRING,
      event_time    TIMESTAMP,
      country_code  STRING,
      device_type   STRING,
      payload       STRING
  )
  USING iceberg
  PARTITIONED BY (
      days(event_time),           -- 自動変換：日単位でパーティション分割
      bucket(64, user_id),        -- 自動ハッシュ化：user_id を 64 個のバケットにハッシュ化
      truncate(2, country_code)   -- 自動切り捨て：最初の 2 文字に切り捨て
  )
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';

  CREATE TABLE db_iceberg.lineitem_multiple_part (
      l_orderkey      BIGINT,
      l_partkey       BIGINT,
      l_suppkey       BIGINT,
      l_linenumber    INT,
      l_quantity      DECIMAL(15, 2),
      l_extendedprice DECIMAL(15, 2),
      l_discount      DECIMAL(15, 2),
      l_tax           DECIMAL(15, 2),
      l_returnflag    STRING,
      l_linestatus    STRING,
      l_shipdate      DATE,
      l_commitdate    DATE,
      l_receiptdate   DATE,
      l_shipinstruct  STRING,
      l_shipmode      STRING,
      l_comment       STRING
  )
  USING iceberg
  PARTITIONED BY (
      days(l_shipdate),      -- 日単位でパーティション分割します。
      bucket(32, l_partkey)  -- 32 個のバケットにハッシュ化します。注：最初のパラメーターはバケット数です。
  )
  LOCATION 'oss://<YOUR_OSS_BUCKET>/PATH/SUBPATH/';