MaxCompute:Pengelompokan rentang

Pengelompokan rentang membagi data menjadi beberapa rentang yang saling lepas berdasarkan pengurutan penuh kunci kluster. Setiap rentang dianggap sebagai bucket dan harus memenuhi kedua kondisi berikut:

• Nilai duplikat disimpan dalam bucket yang sama.

• Jumlah nilai di setiap bucket kira-kira sama.

Pernyataan contoh berikut membuat tabel bernama T.

CREATE TABLE T (C1 int) 
    RANGE CLUSTERED BY (C1) 
    SORTED BY (c1) 
    INTO 3 BUCKETS;

Nilai dalam kolom C1 adalah { 1, 8, -3, 2, 4, 1, 1, 3, 8, 20, -8, 9 }.

Setelah pengelompokan rentang diaktifkan, bucket berikut diperoleh.

• Bucket 0 : { -8, -3, 1, 1, 1 }

• Bucket 1 : { 2, 3, 4 }

• Bucket 2 : { 8, 8, 9, 20 }

Proses pengelompokan rentang diimplementasikan secara otomatis oleh MaxCompute. Anda tidak perlu menentukan rentang secara manual. Dalam skenario data besar, konfigurasi rentang manual tidak efisien atau bahkan tidak layak. MaxCompute secara otomatis mengurutkan dan mengambil sampel data, membuat histogram berdasarkan distribusi data di setiap rentang, lalu menggabungkan dan menghitung histogram tersebut untuk mencapai performa optimal pengelompokan rentang.

Saat membuat tabel, Anda dapat menentukan RANGE CLUSTERED BY dan SORTED BY untuk memastikan data diurutkan secara global. MaxCompute kemudian secara otomatis membuat dua tingkat indeks—indeks global dan indeks file—untuk menemukan dan mencari nilai kunci dengan cepat, seperti yang ditunjukkan pada gambar berikut.

Pengelompokan rentang memiliki keunggulan berikut dibandingkan pengelompokan hash:

• Mendukung kueri rentang.

  Misalnya, jika kondisi kueri adalah c < 3, sistem dapat mengecualikan Bucket 2 dan Bucket 3 berdasarkan indeks global, lalu hanya mengkueri data dari Bucket 0 dan Bucket 1. Sebaliknya, pengelompokan hash hanya mendukung pemangkasan bucket untuk kueri equality.

• Mendukung kueri multi-kunci.

  Misalnya, jika Anda menentukan RANGE CLUSTERED BY (c1, c2, c3) SORTED BY (c1, c2, c3) saat membuat tabel, pengelompokan rentang dan penyimpanan data diimplementasikan dalam urutan c1, c2, dan c3. Dengan demikian, Anda dapat mengkueri data berdasarkan kondisi kompleks seperti c1 = 100 AND c2 > 0 atau c1 = 100 AND c2 = 50 AND c3 < 5—jenis kueri yang tidak dapat dioptimalkan dengan pengelompokan hash.

  Penting

  Untuk kueri berbasis beberapa kunci, kunci dalam kondisi kueri harus diurutkan secara berurutan, dan hanya kunci terakhir yang dapat digunakan untuk menentukan rentang nilai.

• Pengelompokan Rentang merupakan implementasi efisien dari pengurutan global.

  Sebelum pengelompokan rentang, MaxCompute hanya dapat menggunakan satu instans untuk mengurutkan data secara global, sehingga efisiensinya rendah. Dengan pengelompokan rentang, data di setiap rentang dapat diurutkan secara konkuren lalu digabungkan, yang secara signifikan meningkatkan efisiensi.

Anda dapat menggunakan pernyataan CREATE TABLE untuk membuat tabel terkluster rentang. Dalam pernyataan ini, parameter RANGE CLUSTERED BY wajib ditentukan, sedangkan INTO number_of_buckets BUCKETS dan SORTED BY bersifat opsional. Umumnya, kami merekomendasikan agar nilai yang ditentukan dalam SORTED BY sama dengan yang ditentukan dalam RANGE CLUSTERED BY untuk mencapai efek optimasi optimal.

• Sintaks

  CREATE TABLE [IF NOT EXISTS] <table_name>
               [(<col_name> data_type [comment <col_comment>], ...)]
               [comment table_comment]
               [PARTITIONED BY (<col_name> data_type [comment <col_comment>], ...)]
               [RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
               [SORTED BY (<col_name> [ASC | DESC]
               [, <col_name> [ASC | DESC] ...])]
               [INTO <number_of_buckets> BUCKETS]]
               [AS select_statement]

• Contoh

  • Tabel non-partisi

    CREATE TABLE T1 (a string, b string, c int)
                 RANGE CLUSTERED BY (c)
                 SORTED by (c)
                 INTO 1024 BUCKETS;

  • Tabel partisi

    CREATE TABLE T1 (a string, b string, c int)
                 PARTITIONED BY (dt int)
                 RANGE CLUSTERED BY (c)
                 SORTED by (c)
                 INTO 1024 BUCKETS;

• Parameter

  • RANGE CLUSTERED BY

    Menentukan kunci untuk pengelompokan rentang. Setelah parameter ini ditentukan, MaxCompute mengurutkan dan mengambil sampel satu atau beberapa kolom data ke rentang yang sesuai berdasarkan jumlah bucket yang Anda tentukan. Untuk mencegah masalah kesenjangan data dan hot spot serta meningkatkan performa kueri konkuren, kami merekomendasikan agar Anda menentukan kolom dengan rentang nilai besar dan sedikit nilai kunci duplikat di RANGE CLUSTERED BY. Untuk mengoptimalkan performa kueri, gunakan kunci agregat atau kunci filter yang umum digunakan.

  • SORTED BY

    Menentukan cara mengurutkan bidang dalam bucket. Untuk mencapai performa kueri optimal, kami merekomendasikan agar nilai yang ditentukan dalam SORTED BY sama dengan yang ditentukan dalam RANGE CLUSTERED BY. Setelah SORTED BY ditentukan, MaxCompute secara otomatis menghasilkan indeks global dan indeks file serta menggunakannya untuk mempercepat kueri.

  • INTO number_of_buckets BUCKETS

    Berbeda dengan pengelompokan hash, INTO number_of_buckets BUCKETS bersifat opsional untuk pengelompokan rentang. Jika tidak ditentukan, MaxCompute secara otomatis menentukan jumlah bucket berdasarkan volume data. Secara umum, kami merekomendasikan agar Anda menentukan jumlah bucket sesuai situasi aktual.

    Seperti halnya pengelompokan hash, kami merekomendasikan agar jumlah bucket ditetapkan berdasarkan ukuran bucket ideal (512 MB hingga 1 GB). Untuk tabel yang sangat besar, diperlukan jumlah bucket yang besar, tetapi sebaiknya tidak melebihi 4.000 bucket per tabel.

Untuk tabel partisi, Anda dapat menjalankan pernyataan ALTER TABLE untuk menambahkan atau menghapus properti pengelompokan rentang.

• Sintaks

  -- Ubah tabel menjadi tabel terkluster rentang.
  ALTER TABLE <table_name> [RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
                           [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
                           [INTO <number_of_buckets> BUCKETS];
  -- Ubah tabel terkluster rentang menjadi tabel non-terkluster rentang.
  ALTER TABLE <table_name> NOT CLUSTERED;

• Catatan penggunaan

  • Pernyataan ALTER TABLE hanya dapat memodifikasi properti pengelompokan tabel partisi. Untuk tabel non-partisi, properti pengelompokan tidak dapat diubah setelah ditetapkan.

  • Pernyataan ALTER TABLE hanya berlaku untuk partisi baru, termasuk partisi yang dihasilkan melalui pernyataan INSERT OVERWRITE. Partisi baru disimpan sesuai properti pengelompokan yang baru, sedangkan format penyimpanan partisi yang sudah ada tetap tidak berubah.

  • Karena pernyataan ALTER TABLE hanya berlaku untuk partisi baru, Anda tidak dapat menentukan partisi tertentu dalam pernyataan ini.

Pernyataan ALTER TABLE cocok untuk tabel yang sudah ada. Setelah properti pengelompokan rentang ditambahkan, partisi baru akan disimpan sesuai properti tersebut.

Setelah membuat tabel terkluster rentang, Anda dapat menjalankan pernyataan berikut untuk melihat properti tabel. Properti pengelompokan rentang ditampilkan di bagian Extended Info pada hasil yang dikembalikan.

DESC EXTENDED <table_name>;

Contoh output:

Owner: ALIYUNxxx                | Project: xxx
  TableComment:

+----------------------------+
| CreateTime:               2018-01-15 16:05:30  |
| LastDDLTime:              2018-01-15 16:05:30  |
| LastModifiedTime:         2018-01-15 16:05:30  |
+----------------------------+
| InternalTable: YES   | Size: 0               |
+----------------------------+
| Native Columns:                                |
+----------------------------+
| Field            | Type     | Label | Comment  |
+----------------------------+
| l_orderkey       | bigint   |       |          |
| l_partkey        | bigint   |       |          |
| l_suppkey        | bigint   |       |          |
| l_linenumber     | bigint   |       |          |
| l_quantity       | double   |       |          |
| l_extendedprice  | double   |       |          |
| l_discount       | double   |       |          |
| l_tax            | double   |       |          |
| l_returnflag     | string   |       |          |
| l_linestatus     | string   |       |          |
| l_shipdate       | string   |       |          |
| l_commitdate     | string   |       |          |
| l_receiptdate    | string   |       |          |
| l_shipinstruct   | string   |       |          |
| l_shipmode       | string   |       |          |
| l_comment        | string   |       |          |
+----------------------------+
| Extended Info:                                 |
+----------------------------+
| TableID:                   xxx                 |
| IsArchived:                false               |
| PhysicalSize:              0                   |
| FileNum:                   0                   |
| ClusterType:               range               |
| BucketNum:                 1024                |
| ClusterColumns:            [l_orderkey]        |
| SortColumns:               [l_orderkey ASC]    |
+----------------------------+

Pada bagian Extended Info, ClusterType bernilai range, BucketNum bernilai 1024, ClusterColumns bernilai [l_orderkey], dan SortColumns bernilai [l_orderkey ASC], yang mengonfirmasi bahwa tabel telah berhasil dikonfigurasi dengan atribut Range Clustering.

Untuk tabel partisi, Anda juga dapat menjalankan pernyataan berikut untuk melihat properti pengelompokan partisi.

DESC EXTENDED <table_name> partition(<pt_spec>);

Dalam output, empat atribut ClusterType, BucketNum, ClusterColumns, dan SortColumns menunjukkan konfigurasi Range Clustering partisi.

odps@ tpch_100g>desc extended xndai_test_range partition(pt="20180115");

| PartitionSize: 0

| CreateTime:               2018-01-15 16:31:10
| LastDDLTime:              2018-01-15 16:31:10
| LastModifiedTime:         2018-01-15 16:31:10

| IsExstore:          false
| IsArchived:         false
| PhysicalSize:       0
| FileNum:            0
| ClusterType:        range
| BucketNum:          1024
| ClusterColumns:     [c1]
| SortColumns:        [c1 ASC]

Sebagai contoh, untuk kondisi kueri sederhana id < 3, sistem mengekstrak kondisi dari pengoptimal dan mengonversinya menjadi rentang nilai (-∞, 3). Dalam kasus ini, sistem menggunakan indeks global untuk pemangkasan bucket guna mengecualikan Bucket 2 dan Bucket 3 yang datanya berada di luar rentang tersebut. Selanjutnya, sistem menggunakan indeks file di Bucket 0 dan Bucket 1 untuk menemukan data dengan cepat. Proses ini disebut penurunan predikat, seperti yang ditunjukkan pada gambar berikut.Pernyataan contoh berikut adalah TPC-H Query 6 yang digunakan untuk mengkueri data dari set data 100 GB setelah pengelompokan rentang diterapkan. Dalam TPC-H Query 6, operasi agregat dilakukan berdasarkan penyaringan rentang. Pengelompokan rentang memanfaatkan indeks dua tingkat untuk menemukan data dengan cepat, sehingga durasi eksekusi kueri serta konsumsi CPU dan memori berkurang secara signifikan.

select sum(l_extendedprice * l_discount) as revenue
  from tpch_lineitem l
 where l_shipdate >= '1994-01-01'
   and l_shipdate < '1995-01-01'
   and l_discount >= 0.05
   and l_discount <= 0.07
   and l_quantity < 24;

Dalam contoh ini, pernyataan berikut digunakan untuk mengubah tabel mf_tab menjadi tabel terkluster rentang guna mempermudah pemahaman kueri multi-kunci.

ALTER TABLE mf_project.mf_tab 
            RANGE CLUSTERED BY (project_name, name)
            SORTED BY (project_name, name)
            INTO 1024 BUCKETS;

Setelah tabel diubah menjadi tabel terkluster rentang, Anda dapat melakukan kueri agregat di tingkat proyek. Contoh pernyataan:

SELECT COUNT(*)
  from mf_project.mf_tab
 WHERE project_name="xxxdw"
   AND ds="20180115"
   AND type="TABLE";

Anda juga dapat menggunakan beberapa kunci untuk menemukan tabel secara tepat. Contoh pernyataan:

SELECT count(*)
  from mf_project.mf_tab
 WHERE project_name="xxxdw"
   AND name="adm_ctu_cle_kba_midun_trade_dd"
   AND type="TABLE";

Anda juga dapat menggunakan beberapa kunci untuk kueri rentang. Pernyataan berikut mengkueri tabel yang namanya dimulai dengan adm.

SELECT count(*)
  from mf_project.mf_tab
 WHERE project_name="xxxdw"
   AND name>="adm"
   AND name < "adn"
   AND type="TABLE";

Semua kueri di atas dapat sepenuhnya memanfaatkan fitur pengurutan global pengelompokan rentang dan menerapkan penurunan predikat untuk mengurangi jumlah operasi I/O saat pemindaian tabel serta menghemat sumber daya CPU dan memori yang digunakan untuk penyaringan dan komputasi data.

Sebagai contoh, k1, k2 adalah kunci kluster dalam tabel bernama T.

• Jika kondisi kueri adalah k1 < 5, akselerasi kueri berbasis indeks dapat dicapai.

• Jika kondisi kueri adalah k1 = 10 AND k2 = 20, akselerasi kueri berbasis indeks dapat dicapai.

• Jika kondisi kueri adalah k1 = 10 AND k2 < 0, akselerasi kueri berbasis indeks dapat dicapai.

• Jika kondisi kueri adalah k2 < 0, akselerasi kueri berbasis indeks tidak dapat dicapai karena k1 tidak ditentukan dalam kondisi kueri.

• Jika kondisi kueri adalah k1 < 0 AND k2 > 0, akselerasi kueri berbasis indeks dapat digunakan untuk memperoleh data yang memenuhi kondisi k1 < 0. Namun, untuk data yang memenuhi kondisi k2 > 0, diperlukan pemindaian tabel penuh.

Sebagai contoh, tabel bernama T dibuat menggunakan pernyataan CREATE TABLE berikut. Saat mengkueri data dari tabel tersebut, Anda dapat melakukan operasi GROUP BY pada data tabel dalam tahap map.

CREATE TABLE T (department int, team string, employee string)
       RANGE CLUSTERED BY (department, team)
       SORTED BY (c1, c2)
       INTO 1024 BUCKETS;

SELECT COUNT(*) from T GROUP BY department, team;

Pernyataan berikut menunjukkan struktur data tabel foo.

create table foo(a bigint, b bigint, c bigint)
       range clustered by (a,b)
       sorted by(a,b) into 3 buckets;

Data yang disimpan dalam bucket tabel foo berada dalam rentang berikut:

Bucket 0: [1,1 : 3,3]
Bucket 1: [5,5 : 7,7]
Bucket 2: [8,8 : 9,9]

Rentang bucket di atas ditentukan dalam format Bucket N: [nilai batas bawah : nilai batas atas]. Jika data diagregat berdasarkan Kolom a, rentang bucket berikut digunakan sebagai gantinya:

Bucket 0: [1 : 3]
Bucket 1: [5 : 7]
Bucket 2: [8 : 9]

Anda dapat langsung menghasilkan rencana eksekusi untuk operasi agregat berdasarkan Kolom a dan Kolom b, memulai tiga instans untuk mengagregat data di setiap bucket, lalu mengembalikan hasil output.

Namun, jika nilai Kolom a tersebar di beberapa bucket, hasil yang tidak valid akan dikembalikan. Contoh:

Bucket 0: [1,1 : 3,3]
Bucket 1: [3,5 : 7,7]
Bucket 2: [7,8 : 9,9]

Kolom a memiliki dua nilai, yaitu 3 dan 7, yang disimpan secara terpisah di dua bucket. Untuk memperoleh hasil yang valid, tupel dengan nilai Kolom a yang sama harus ditempatkan dalam instans yang sama dan diagregat. Dengan demikian, bucket dibuat ulang, seperti yang ditunjukkan pada gambar berikut. Jarak antara dua garis putus-putus merah menentukan rentang data yang dapat dibaca oleh setiap instans.Histogram diperlukan untuk pengelompokan rentang. Untuk tabel terkluster rentang, jika kunci kluster dan kunci pengurutan sama, worker yang sesuai dengan setiap bucket mengambil sampel satu tupel untuk setiap 10.000 baris guna memperoleh nilai kunci kluster saat data dimasukkan ke tabel. Nilai yang diperoleh disimpan dalam histogram yang disimpan dalam file metadata kluster. Jenis histogram ini disebut histogram equi-depth.

Setelah histogram setiap bucket diperoleh, bucket dapat dibuat ulang untuk setiap worker berdasarkan aturan berikut:

• Tupel yang memiliki kunci pengelompokan yang sama disimpan dalam bucket yang sama.

• Data didistribusikan secara merata ke bucket.

Berdasarkan nilai batas bawah setiap bucket baru, setiap worker dapat membaca data dalam rentang yang valid dan mengembalikan hasil yang valid.

Konten berikut menggunakan tabel partsupp dengan data 1 TB dalam set data TPC-H untuk menguji peningkatan performa. Jalankan pernyataan berikut untuk mengubah tabel partsupp menjadi tabel terkluster rentang:

CREATE TABLE partsupp ( PS_PARTKEY BIGINT NOT NULL,
                        PS_SUPPKEY BIGINT NOT NULL,
                        PS_AVAILQTY BIGINT NOT NULL,
                        PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                        PS_COMMENT     VARCHAR(199) NOT NULL)
             RANGE CLUSTERED BY(PS_PARTKEY, PS_SUPPKEY)
             SORTED BY(PS_PARTKEY, PS_SUPPKEY) INTO 128 BUCKETS;

Jalankan pernyataan kueri berikut untuk melakukan pengujian:

SELECT ps_partkey, count(*) c FROM partsupp GROUP BY ps_partkey;

• Jalankan perintah berikut untuk menonaktifkan optimasi:

   set odps.optimizer.enable.range.partial.repartitioning=false;

  Berikut ini menunjukkan log eksekusi pekerjaan saat optimasi dinonaktifkan.

  
  resource cost: cpu 9.17 Core * Min, memory 15.15 GB * Min
  inputs:
      yuan_tpch_range_1t.partsupp: 800000000 (3376541880 bytes)
  outputs:
  Job run time: 14.000
  Job run mode: fuxi job
  Job run engine: execution engine
  M1:
      instance count: 128
      run time: 7.000
      instance time:
          min: 2.000, max: 4.000, avg: 2.000
      input records:
          TableScan1: 800000000 (min: 5496040, max: 6780663, avg: 6251772)
      output records:
          StreamLineWrite1: 200001977 (min: 1374023, max: 1695182, avg: 1562958)
      writer dumps:
          StreamLineWrite1: (min: 0, max: 0, avg: 0)
  R2_1:
      instance count: 43
      run time: 14.000
      instance time:
          min: 3.000, max: 4.000, avg: 3.000
      input records:
          StreamLineRead1: 200001977 (min: 4647288, max: 4654888, avg: 4651214)
      output records:
          AdhocSink1: 200000000 (min: 4647242, max: 4654834, avg: 4651168)
      reader dumps:
          StreamLineRead1: (min: 0, max: 0, avg: 0)

• Jalankan perintah berikut untuk mengaktifkan optimasi:

  set odps.optimizer.enable.range.partial.repartitioning=true;

  Contoh output:

  
  resource cost: cpu 4.38 Core * Min, memory 4.38 GB * Min
  inputs:
      yuan_tpch_range_1t.partsupp: 800000000 (18493328320 bytes)
  outputs:
  Job run time: 6.000
  Job run mode: fuxi job
  Job run engine: execution engine
  M1:
      instance count: 128
      run time: 6.000
      instance time:
          min: 1.000, max: 3.000, avg: 2.000
      input records:
          TableScan1: 800000000 (min: 5625876, max: 6259956, avg: 6254874)
      output records:
          AdhocSink1: 200000000 (min: 1406469, max: 1564989, avg: 1563718)

Hasil pengujian menunjukkan bahwa kecepatan kueri meningkat 57%, pemanfaatan CPU berkurang 52%, dan penggunaan memori berkurang 71% setelah optimasi diaktifkan. Peningkatan performa bervariasi tergantung pada volume data dan jenis kueri.

Secara default, akselerasi pengurutan global dinonaktifkan untuk tabel terkluster rentang. Untuk mengaktifkannya, jalankan perintah berikut:

set odps.optimizer.distribute.ordering.enable=true;

Dibandingkan dengan pengelompokan hash, pengelompokan rentang memiliki batasan berikut:

• Biaya pembuatan data pengelompokan rentang lebih tinggi daripada pengelompokan hash. Pengelompokan hash hanya melibatkan operasi hashing dan pengurutan data yang sederhana. Sebaliknya, pengelompokan rentang memerlukan pengambilan sampel data, pengurutan, dan penggabungan histogram. Konsumsi keseluruhan—termasuk durasi eksekusi, biaya CPU, dan biaya memori—lebih tinggi dibandingkan pengelompokan hash. Oleh karena itu, jika pengelompokan hash sudah cukup untuk menyelesaikan masalah, tidak perlu menggunakan pengelompokan rentang.

• Pengelompokan rentang tidak didukung dalam DYNAMIC PARTITION atau INSERT INTO.

• Pengelompokan rentang hanya didukung untuk operasi join berikut: inner join, left outer join, right outer join, dan semi join. Fitur ini tidak didukung untuk anti-join atau full outer join.

• Untuk tabel terkluster rentang, kunci yang ditentukan di RANGE CLUSTERED BY harus sama dengan yang ditentukan di SORTED BY. Misalnya, jika range clustered by (a,b) sorted by (a,b) ditentukan untuk tabel bernama foo, optimasi yang dijelaskan dalam topik ini dapat diterapkan. Namun, jika range clustered by(a,b) sorted by (b,a) ditentukan untuk tabel bernama bar, optimasi tersebut tidak dapat diterapkan.

• Kunci yang ditentukan di JOIN atau GROUP BY harus merupakan awalan atau seluruh kunci yang ditentukan di RANGE CLUSTERED BY. Misalnya, jika range clustered by(a,b,c) sorted by(a,b,c) ditentukan dalam pernyataan pembuatan tabel, optimasi hanya dapat diterapkan jika kunci di JOIN atau GROUP BY berupa a, a,b, atau a,b,c. Optimasi tidak dapat diterapkan jika kunci yang digunakan adalah b atau a,c.

• Untuk tabel partisi terkluster rentang, optimasi yang dijelaskan dalam topik ini tidak dapat diterapkan jika data dibaca dari dua atau lebih partisi. Optimasi hanya berlaku untuk tabel partisi dengan satu partisi dan tabel non-partisi.