MaxCompute：Fungsi SQL yang Ditentukan Pengguna (UDF)

Deskripsi fitur

UDF SQL membantu menyelesaikan masalah bahwa MaxCompute hanya dapat menggunakan Java atau Python untuk membuat UDF dan mendukung parameter input bertipe fungsi. UDF SQL meningkatkan fleksibilitas ekspresi logika bisnis serta fitur sederhana, sekaligus meningkatkan laju penggunaan ulang kode. Berikut adalah fitur-fitur utama UDF SQL:

• Anda dapat menggunakan skrip SQL untuk mendefinisikan dan memanggil UDF.

  • Anda dapat menggunakan pernyataan SQL untuk mendefinisikan UDF SQL permanen. Setelah didefinisikan, UDF tersebut akan terdaftar dalam daftar fungsi MaxCompute dan dapat digunakan di lingkungan tempat UDF berlaku. Untuk informasi lebih lanjut, lihat Buat UDF SQL Permanen.

  • Anda dapat menggunakan pernyataan SQL untuk mendefinisikan UDF SQL sementara. UDF tersebut tidak didaftarkan dalam daftar fungsi MaxCompute dan hanya dapat dipanggil langsung dalam skrip SQL tempat UDF didefinisikan. Untuk informasi lebih lanjut, lihat Buat UDF SQL Sementara.

• Anda dapat menggunakan parameter input bertipe fungsi untuk UDF saat mendefinisikan UDF dengan pernyataan SQL. Parameter input bertipe fungsi mencakup fungsi bawaan MaxCompute, UDF lainnya, dan fungsi anonim. Untuk informasi lebih lanjut, lihat Contoh Cara Membuat UDF SQL dengan Parameter Input Bertipe Fungsi dan Contoh Cara Membuat UDF SQL dengan Parameter Input sebagai Fungsi Anonim.

Skenario

Anda dapat menggunakan UDF SQL MaxCompute untuk menyelesaikan masalah-masalah berikut:

• Dalam banyak kasus, sejumlah besar kode serupa ada, yang sulit dipelihara dan rentan terhadap kesalahan. Jika Anda menggunakan UDF Java atau Python, Anda harus mengompilasi kode (diperlukan untuk UDF Java), membuat sumber daya, dan membuat fungsi setelah menulis kode. Proses ini rumit dan performanya tidak optimal.

  Sebagai contoh, Anda dapat menggunakan pernyataan SQL berikut untuk mendefinisikan UDF. Ini membantu meningkatkan efisiensi dan fleksibilitas definisi serta aplikasi UDF.

  select
      nvl(str_to_map(get_json_object(col, '$.key1')), 'default') as key1,
      nvl(str_to_map(get_json_object(col, '$.key2')), 'default') as key2,
      ...
      nvl(str_to_map(get_json_object(col, '$.keyN')), 'default') as keyN
  from t;

• UDF yang didefinisikan menggunakan pernyataan SQL mendukung parameter input bertipe fungsi. Oleh karena itu, fitur mirip ekspresi Lambda didukung untuk meneruskan fungsi sebagai parameter ke fungsi lain.

  Catatan

  Untuk informasi lebih lanjut tentang tindakan pencegahan saat menggunakan ekspresi Lambda di MaxCompute, lihat Fungsi Lambda.

MaxCompute memungkinkan Anda menjalankan pernyataan CREATE SQL FUNCTION untuk membuat UDF SQL. UDF yang dibuat menggunakan pernyataan ini adalah UDF SQL permanen. Setelah UDF dibuat menggunakan pernyataan CREATE SQL FUNCTION, UDF disimpan dalam sistem metadata MaxCompute. Anda dapat menanyakan UDF dalam daftar fungsi MaxCompute dan memanggilnya dalam operasi kueri berikutnya.

• Tindakan Pencegahan

  Buat UDF SQL dalam mode skrip SQL. Jika tidak, UDF SQL mungkin gagal dibuat. Untuk informasi lebih lanjut tentang mode skrip SQL, lihat SQL dalam Mode Skrip.

• Sintaks

  create sql function <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...]) 
  [returns @<parameter_out> <datatype>] 
  as [begin] 
  <function_expression> 
  [end];

  • function_name: Wajib. Menentukan nama UDF SQL yang Anda buat. Nama fungsi harus unik dalam sebuah proyek dan tidak boleh sama dengan nama fungsi bawaan. Fungsi dengan nama yang sama hanya dapat didaftarkan sekali. Anda dapat menjalankan pernyataan LIST FUNCTIONS untuk melihat semua fungsi dalam sebuah proyek dan memeriksa apakah fungsi yang sudah ada memiliki nama yang sama dengan fungsi yang ingin Anda buat.

  • parameter_in: Wajib. Menentukan parameter input UDF SQL yang ingin Anda buat. Parameter input dapat berupa tipe fungsi, termasuk fungsi anonim. Untuk informasi lebih lanjut tentang cara membuat UDF SQL dengan parameter input bertipe fungsi, lihat Contoh Cara Membuat UDF SQL dengan Parameter Input Bertipe Fungsi. Untuk informasi lebih lanjut tentang cara membuat UDF SQL dengan parameter input sebagai fungsi anonim, lihat Contoh Cara Membuat UDF SQL dengan Parameter Input sebagai Fungsi Anonim.

  • datatype: Wajib. Menentukan tipe data parameter input UDF. Untuk informasi lebih lanjut tentang tipe data yang didukung oleh MaxCompute, lihat Edisi Tipe Data MaxCompute V2.0.

  • returns: Opsional. Menentukan nilai balik UDF. Nilai balik adalah variabel. Jika tidak ditentukan, nilai parameter function_name dikembalikan secara default.

  • parameter_out: Wajib. Menentukan parameter respons UDF.

  • function_expression: Wajib. Menentukan ekspresi (logika implementasi) UDF.

• Kode Contoh

  • Kode contoh berikut memberikan contoh UDF dengan logika sederhana.

    create sql function my_add(@a BIGINT) as @a + 1;

    Dalam contoh sebelumnya, @a + 1 menunjukkan logika UDF SQL. Anda dapat menulisnya sebagai ekspresi. Ekspresi tersebut dapat berupa operator bawaan, fungsi bawaan, atau UDF.

  • Jika logika UDF kompleks, Anda dapat menggunakan begin dan end dalam pernyataan SQL untuk menentukan rentang ekspresi UDF. Anda dapat menulis beberapa pernyataan sebagai ekspresi UDF dalam rentang yang ditentukan oleh begin dan end. Kode contoh berikut memberikan contoh.

    create sql function my_sum(@a BIGINT, @b BIGINT, @c BIGINT) returns @my_sum BIGINT
    as begin 
        @temp := @a + @b;
        @my_sum := @temp + @c;
    end;

    Parameter:

    • returns menentukan nilai balik UDF. Nilai balik adalah variabel. Jika tidak ditentukan, nilai parameter function_name dikembalikan secara default.

    • Ekspresi dalam begin dan end adalah logika implementasi fungsi UDF SQL.

MaxCompute memungkinkan Anda menjalankan pernyataan FUNCTION untuk membuat UDF SQL. UDF yang dibuat menggunakan pernyataan ini adalah UDF SQL sementara. Setelah UDF dibuat menggunakan pernyataan FUNCTION, UDF tidak disimpan dalam sistem metadata MaxCompute. Anda tidak dapat menanyakan UDF dalam daftar fungsi MaxCompute. UDF SQL hanya dapat dipanggil dalam skrip tempat UDF didefinisikan dan tidak dapat dipanggil di lingkungan kueri lainnya.

• Tindakan Pencegahan

  Buat UDF SQL dalam mode skrip SQL. Jika tidak, UDF SQL mungkin gagal dibuat. Untuk informasi lebih lanjut tentang mode skrip SQL, lihat SQL dalam Mode Skrip.

• Sintaks

  function <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...]) 
  [returns @<parameter_out> <datatype>] 
  as [begin] 
  <function_expression> 
  [end];

  Untuk informasi lebih lanjut tentang parameter, lihat Buat UDF SQL Permanen.

• Kode Contoh

  function my_add(@a BIGINT) as @a + 1;

Anda dapat menanyakan UDF SQL dengan cara yang sama seperti Anda menanyakan UDF Java atau Python.

• Tindakan Pencegahan

  • Jika Anda menanyakan informasi pada klien MaxCompute, pastikan versi klien ditingkatkan ke 0.34.0 atau lebih baru. Untuk informasi lebih lanjut tentang cara melihat versi klien MaxCompute dan menginstal serta mengonfigurasi klien MaxCompute, lihat Klien MaxCompute (odpscmd).

  • Hanya UDF SQL permanen yang dibuat menggunakan pernyataan CREATE SQL FUNCTION yang disimpan di MaxCompute. UDF SQL sementara tidak disimpan di MaxCompute. Oleh karena itu, informasi tentang UDF SQL sementara tidak dapat ditanyakan.

• Sintaks

  desc function <function_name>;

  function_name: Nama UDF SQL yang telah dibuat.

• Kode Contoh

  desc function my_add;

  Hasil berikut dikembalikan:

  Name                                    my_add
  Owner                                   ALIYUN$s***_****@**.aliyunid.com
  Created Time                            2021-05-08 11:26:02
  SQL Definition Text                     CREATE SQL FUNCTION MY_ADD(@a BIGINT) AS @a + 1

Anda dapat memanggil UDF SQL dengan cara yang sama seperti Anda memanggil fungsi bawaan.

• Tindakan Pencegahan

  • UDF SQL permanen disimpan di MaxCompute dan dapat dipanggil di setiap fase.

  • UDF SQL sementara hanya dapat dipanggil dalam skrip tempat UDF didefinisikan dan tidak dapat dipanggil di lingkungan kueri lainnya.

• Sintaks

  select <function_name>(<column_name>[,...]) from <table_name>;

  • function_name: Nama UDF SQL yang telah dibuat.

  • column_name: Nama kolom tabel dari mana Anda ingin menanyakan data. Tipe data kolom harus sama dengan tipe data yang didefinisikan oleh UDF SQL.

  • table_name: Nama tabel dari mana Anda ingin menanyakan data.

• Kode Contoh

  -- Buat tabel bernama src. 
  create table src (c bigint, d string);
  insert into table src values (1,100.1),(2,100.2),(3,100.3);
  -- Panggil fungsi my_add. 
  select my_add(c) from src;
  -- Hasil berikut dikembalikan: 
  +------------+
  | _c0        |
  +------------+
  | 2          |
  | 3          |
  | 4          |
  +------------+

Anda dapat menghapus UDF SQL dengan cara yang sama seperti Anda menghapus UDF Java atau Python.

• Sintaks

  drop function <function_name>;

  function_name: Nama UDF SQL yang telah dibuat.

• Kode Contoh

  drop function my_add;

Saat membuat UDF SQL, Anda dapat menggunakan parameter input bertipe fungsi untuk UDF, termasuk fungsi bawaan MaxCompute, UDF lainnya, atau UDF SQL. Saat memanggil UDF SQL, Anda hanya perlu meneruskan parameter input bertipe fungsi. Kode contoh:

function add(@a BIGINT) as @a + 1;
function op(@a BIGINT, @fun function (BIGINT) returns BIGINT) as @fun(@a);
select op(key, add), op(key, abs) from values (1),(2) as t (key);

-- Hasil berikut dikembalikan: 
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 2          | 1          |
| 3          | 2          |
+------------+------------+

Dalam contoh sebelumnya, dua UDF SQL didefinisikan:

• add: Parameter input bertipe BIGINT didefinisikan.

• op: Dua parameter input didefinisikan.

  • Parameter input @a bertipe BIGINT.

  • Parameter input @fun bertipe fungsi. Parameter input dan output fungsi @fun keduanya bertipe BIGINT. Fungsi op meneruskan parameter input @a ke fungsi @fun.

• Saat memanggil fungsi op, fungsi ADD dan ABS diteruskan untuk melakukan operasi pada @a. ADD adalah UDF SQL dan ABS adalah fungsi bawaan MaxCompute. Untuk informasi lebih lanjut tentang fungsi ABS, lihat Fungsi Matematika.

Jika parameter input UDF SQL bertipe fungsi, parameter tersebut juga dapat berupa fungsi anonim. Kode contoh:

function op(@a BIGINT, @fun function (BIGINT) returns BIGINT) as @fun(@a);
select op(key, function (@a) as @a + 1) from values (1),(2) as t (key);

Dalam contoh sebelumnya, function (@a) as @a + 1 adalah fungsi anonim yang digunakan sebagai parameter input UDF SQL op. Parameter input fungsi anonim adalah @a. Anda tidak perlu menentukan tipe data parameter input. Kompiler menyimpulkan tipe data parameter @a berdasarkan definisi parameter fungsi op.

Metode:

• Metode 1: Gunakan UDF SQL. Kami merekomendasikan Anda menggunakan metode ini. Kode contoh:

  create sql function y_m_d2yyyymmdd(@y_m_d string) returns @yyyymmdd string
  as begin
      @yyyymmdd := concat(lpad(split_part(@y_m_d, '-', 1), 4, '0'), lpad(split_part(@y_m_d, '-', 2), 2, '0'), lpad(split_part(@y_m_d, '-', 3), 2, '0')) ;
  end;
  
  select y_m_d2yyyymmdd(d) from values('2020-11-21'),('2020-1-01'), ('2019-5-1'), ('19-12-1') t (d);

  Hasil berikut dikembalikan:

  +------------+
  | _c0        |
  +------------+
  | 20201121   |
  | 20200101   |
  | 20190501   |
  | 00191201   |
  +------------+

• Metode 2: Panggil fungsi secara berulang. Metode ini menurunkan laju penggunaan ulang kode. Oleh karena itu, kami tidak merekomendasikan Anda menggunakan metode ini. Kode contoh:

  select concat(lpad(split_part(d, '-', 1), 4, '0'), lpad(split_part(d, '-', 2), 2, '0'), lpad(split_part(d, '-', 3), 2, '0')) from values('2020-11-21'),('2020-1-01'), ('2019-5-1'), ('19-12-1') t (d);