AWS Study Group

Di chuyển một mảng kết hợp của Oracle sang Amazon Aurora PostgreSQL hoặc Amazon RDS for PostgreSQL

bởi Bikash Chandra Rout, Gokul Gunasekaran, Neha Sharma và Sashikanta Pattanayak | vào ngày 08 tháng 01 năm 2024 |

Quy trình di chuyển tiêu biểu cho cơ sở dữ liệu Oracle sang Amazon Aurora PostgreSQL-Compatible Edition hoặc Amazon Relational Database Service (Amazon RDS) for PostgreSQL đòi hỏi cả quy trình tự động và thủ công. Công cụ Chuyển Đổi Schema AWS (AWS SCT) có thể xử lý các nhiệm vụ tự động trong quá trình chuyển đổi schema. Đối với các đối tượng cụ thể trong cơ sở dữ liệu không thể tự động di chuyển, các nhiệm vụ thủ công liên quan đến việc chỉnh sửa sau khi chuyển đổi schema bằng AWS SCT.

Hầu hết mã tùy chỉnh và schema của cơ sở dữ liệu nguồn được chuyển đổi tự động bởi AWS SCT thành định dạng tương thích với cơ sở dữ liệu đích. AWS SCT tự động chuyển đổi mã Oracle PL/SQL thành mã tương tự PL/pgSQL trong PostgreSQL trong quá trình di chuyển cơ sở dữ liệu từ Oracle sang PostgreSQL.

Mảng kết hợp, còn được biết đến là index-by tables trong Oracle, cung cấp một cách mạnh mẽ và linh hoạt để quản lý cặp khóa-giá trị một cách hiệu quả trong mã PL/SQL. Tuy nhiên, khi di chuyển từ Oracle sang PostgreSQL, các nhà phát triển thường gặp khó khăn do sự khác biệt trong cách triển khai mảng kết hợp của họ. PostgreSQL không có tương đương trực tiếp với mảng kết hợp của Oracle. Trong bài viết này, chúng ta sẽ khám phá quá trình di chuyển mảng kết hợp từ Oracle sang PostgreSQL, bao gồm các cấu trúc dữ liệu thay thế và các biện pháp tạm thời để đạt được chức năng tương tự.

Tổng quan về mảng kết hợp của Oracle, mảng của PostgreSQL và tổng quan về giải pháp

Một mảng kết hợp là một tập hợp các cặp khóa-giá trị, trong đó một khóa liên kết với một giá trị. Mối liên kết giữa một khóa và một giá trị có thể được gọi là một ánh xạ. Khóa là một chỉ số duy nhất được sử dụng để định vị giá trị liên kết. Khóa có thể là kiểu văn bản hoặc kiểu số nguyên có thể được ánh xạ với bất kỳ kiểu giá trị nào.

Một mảng kết hợp thích hợp cho các mục sau:

  • Bảng tra cứu tương đối nhỏ, có thể được xây dựng trong bộ nhớ mỗi khi bạn gọi phần con hoặc khởi tạo gói khai báo nó.
  • Truyền bộ sưu tập vào và ra khỏi máy chủ cơ sở dữ liệu.

PostgreSQL cho phép bạn xác định một cột là một mảng của bất kỳ kiểu dữ liệu hợp lệ nào bao gồm kiểu dữ liệu tích hợp sẵn, kiểu dữ liệu người dùng tự định nghĩa, kiểu dữ liệu đếm, hoặc kiểu dữ liệu hỗn hợp có thể được tạo ra. Ngoài ra, trong PL/pgSQL của PostgreSQL, chúng ta có thể khai báo một biến là kiểu ARRAY.

Di chuyển mảng kết hợp từ Oracle sang PostgreSQL đòi hỏi một cách tiếp cận cẩn thận vì PostgreSQL không cung cấp một cấu trúc dữ liệu tương đương trực tiếp. Tuy nhiên, PostgreSQL cung cấp nhiều cấu trúc dữ liệu thay thế để xử lý cặp khóa-giá trị, như mảng và JSONB. Bằng cách sử dụng các loại dữ liệu tùy chỉnh và mảng trong PostgreSQL, chúng ta có thể mô phỏng một cách hiệu quả chức năng của mảng kết hợp của Oracle. Bài viết này thể hiện một ví dụ cụ thể từng bước để giúp bạn hiểu và áp dụng những khái niệm này vào dự án di chuyển của bạn.

Yêu cầu tiên quyết

Để bắt đầu với các giải pháp mà bài viết này mô tả, bạn cần có những điều sau đây:

  • Một AWS account đang hoạt động.
  • Một cơ sở dữ liệu Oracle nguồn (trên nền địa hoặc Amazon RDS cho Oracle).
  • Một cơ sở dữ liệu đích Amazon Aurora PostgreSQL-Compatible Edition hoặc Amazon RDS PostgreSQL.
  • Một người dùng cơ sở dữ liệu có đặc quyền SELECT trên cơ sở dữ liệu đích.

Duyệt qua một mảng kết hợp

Trong phần này, chúng ta sẽ so sánh các phương pháp với Oracle và PostgreSQL để duyệt qua một mảng kết hợp.

Phương pháp của Oracle

Dưới đây là một ví dụ về mã Oracle để lặp qua một mảng kết hợp:

DECLARE

  TYPE varchar_assoc_array_type IS TABLE OF 

       VARCHAR2(100) INDEX BY VARCHAR2(100);

  varchar_assoc_array varchar_assoc_array_type;

  current_key VARCHAR2(100);

  first_key VARCHAR2(100);

BEGIN

  -- Populate the associative array with key-value pairs

  varchar_assoc_array('Key 1') := 'Value 1';

  varchar_assoc_array('Key 2') := 'Value 2';

  varchar_assoc_array('Key 3') := 'Value 3';

  -- Loop through the associative array

  DBMS_OUTPUT.PUT_LINE('*** Loop through the associative array ***');

  current_key := varchar_assoc_array.FIRST;

  WHILE current_key IS NOT NULL LOOP

  -- Retrieve the value associated with the current key

DBMS_OUTPUT.PUT_LINE('Key: ' || current_key || ', Value: ' || varchar_assoc_array(current_key));

      current_key := varchar_assoc_array.NEXT(current_key);

  END LOOP;

END;

/

Output 

Statement processed.

*** Loop through the associative array ***

Key: Key 1, Value: Value 1

Key: Key 2, Value: Value 2

Key: Key 3, Value: Value 3

Trong ví dụ này, chúng ta xác định varchar_assoc_array_type là một mảng kết hợp được chỉ mục bởi các khóa VARCHAR2(100) để lưu trữ các cặp khóa-giá trị. Chúng ta điền mảng kết hợp với ba cặp khóa-giá trị mẫu. Chúng ta khởi tạo biến current_key với khóa đầu tiên trong mảng kết hợp bằng cách sử dụng phương thức FIRST. Sau đó, chúng ta bắt đầu một vòng lặp WHILE với điều kiện là current_key không phải là NULL. Bên trong vòng lặp, bạn có thể lấy giá trị tương ứng bằng cách sử dụng varchar_assoc_array(current_key) và thực hiện các thao tác trên cặp khóa-giá trị.

Sau khi xử lý khóa hiện tại, chúng ta cập nhật biến current_key với khóa tiếp theo trong mảng kết hợp bằng cách sử dụng phương thức NEXT. Điều này cho phép chúng ta lặp qua tất cả các khóa trong mảng kết hợp.

Phương pháp của PostgreSQL sử dụng một mảng

Dưới đây là phương pháp di chuyển cho mã PostgreSQL để lặp qua một mảng kết hợp.

Trước tiên, chúng ta cần tạo một loại dữ liệu tùy chỉnh để mô phỏng mảng kết hợp. Chúng ta sử dụng một loại hỗn hợp bao gồm hai thuộc tính: index_col để lưu trữ các khóa và index_value để lưu trữ các giá trị tương ứng:

CREATE TYPE varchar_assoc_array_type AS

(

    index_col  character varying(10),

    index_value character  varying(15)

);

Tiếp theo, chúng ta điền mảng kết hợp trong PostgreSQL bằng cách sử dụng một mảng của loại dữ liệu tùy chỉnh:

DO

$$

DECLARE

    v_index character varying := 'HYD';

    varchar_assoc_array varchar_assoc_array_type[];

    I RECORD;

BEGIN

    -- Populate the associative array with key-value pairs

    varchar_assoc_array := ARRAY[row('Key 1', 'Value 1'), row('Key 2', 'Value 2'), row('Key 3', 'Value 3')];

    -- Loop through the associative array

    RAISE NOTICE '*** Loop through the associative array ***';

    FOREACH i IN ARRAY varchar_assoc_array

    LOOP

        RAISE NOTICE 'KEY : % VALUE : % ', i.index_col, i.index_value;

    END LOOP;

END;

$$;

Output:

NOTICE: *** Loop through the associative array ***

NOTICE: KEY : Key 1 VALUE : Value 1

NOTICE: KEY : Key 2 VALUE : Value 2

NOTICE: KEY : Key 3 VALUE : Value 3

DO

Trong ví dụ này, chúng ta đã tạo một loại có tên là varchar_assoc_array_type và sử dụng thuộc tính extra member là index_col để lưu trữ các giá trị cột chỉ mục để mô phỏng chức năng của mảng kết hợp của Oracle. Chúng ta đã sử dụng hàm array_length để lặp qua mảng kết hợp.

Phương pháp của PostgreSQL sử dụng JSONB

Để sử dụng JSONB, trước tiên chúng ta tạo một hàm bao. Hàm này có tên là add_update_item_json_map, nhận ba tham số: một đối tượng JSONB hiện có có tên là field_entity_map, một khóa có tên là key_item, và một giá trị có tên là value_item. Nó trả về một đối tượng JSONB đã được sửa đổi với cặp khóa-giá trị được thêm hoặc cập nhật. Hàm sử dụng hàm jsonb_set để thực hiện điều này. Xem mã sau:

CREATE OR REPLACE FUNCTION add_update_item_json_map

(

     field_entity_map jsonb,

     key_item         text,

     value_item       text

)

    RETURNS jsonb

    LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN   

    return jsonb_set(field_entity_map , ('{'|| key_item ||'}')::text[],('"' ||      value_item || '"') :: jsonb);     

END;

$BODY$;

Chúng ta có thể sử dụng hàm bao add_update_item_json_map để điền mảng kết hợp bằng cách sử dụng logic sau:

DO $$

DECLARE

  field_entity_map jsonb  :='{}' ;

BEGIN

  RAISE NOTICE 'Jsonb processed.'; 

-- Populate the associative array with key-value pairs

field_entity_map = add_update_item_json_map(field_entity_map , 'Key1'::text,'Value1'::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , 'Key 2'::text,'Value 2'::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , 'Key 3'::text,'Value 3'::text)::jsonb;

-- Look through the associative array

RAISE NOTICE '*** Associative array Key:Value *** %',     field_entity_map;

END;

$$;

Output:

NOTICE: Jsonb processed.

NOTICE: *** Associative array Key:Value *** {“Key1”: “Value1”, “Key 2”: “Value 2”, “Key 3”: “Value 3”}

DO

Query returned successfully in 53 msec.

Trong ví dụ này, chúng ta điền đối tượng JSONB field_entity_map bằng cách gọi hàm add_update_item_json_map với các cặp khóa-giá trị khác nhau. Hàm này thêm hoặc cập nhật mỗi cặp khóa-giá trị trong đối tượng JSONB.

Tìm phần tử đầu tiên và cuối cùng và lấy kích thước hiện tại của một mảng kết hợp

Trong phần này, chúng ta sẽ so sánh các phương pháp với Oracle và PostgreSQL để tìm phần tử đầu tiên và cuối cùng và lấy kích thước hiện tại của một mảng kết hợp.

Phương pháp của Oracle

Dưới đây là mã Oracle để tìm phần tử đầu tiên và cuối cùng trong mảng kết hợp:

DECLARE

TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);

   varchar_assoc_array varchar_assoc_array_type;

   first_key VARCHAR2(100);

   last_key VARCHAR2(100);

   max_size NUMBER; 

BEGIN

  -- Populate the associative array with key-value pairs

   varchar_assoc_array('Key 1') := 'Value 1';

   varchar_assoc_array('Key 2') := 'Value 2';

   varchar_assoc_array('Key 3') := 'Value 3';

-- Find the first and last elements in the associative array

DBMS_OUTPUT.PUT_LINE('*** Find the first and last elements in the associative array ***');

  first_key := varchar_assoc_array.FIRST;

  last_key := varchar_assoc_array.LAST;

  -- Output the first and last key-value pairs

DBMS_OUTPUT.PUT_LINE('First Key: ' || first_key || ', Value: ' ||  varchar_assoc_array(first_key));

DBMS_OUTPUT.PUT_LINE('Last Key: ' || last_key || ', Value: ' || varchar_assoc_array(last_key));

  -- Get the current size of the associative array

  DBMS_OUTPUT.PUT_LINE('*** Get the current size of the associative array ***');

  max_size := varchar_assoc_array.COUNT;

  -- Output the size

  DBMS_OUTPUT.PUT_LINE('Size of the Associative Array: ' || max_size );

END;

/

Statement processed.

*** Find the first and last elements in the associative array ***

First Key: Key 1, Value: Value 1

Last Key: Key 3, Value: Value 3

*** Get the current size of the associative array ***

Size of the Associative Array: 3

Trong ví dụ này, chúng ta sử dụng các phương thức của bộ sưu tập là FIRST và LAST. Phương thức FIRST trả về khóa đầu tiên và phương thức LAST trả về khóa cuối cùng trong mảng kết hợp. Cuối cùng, chúng ta xuất các cặp khóa-giá trị liên quan đến các khóa đầu tiên và cuối cùng. Tương tự, chúng ta sử dụng phương thức COUNT để tìm kích thước tối đa của mảng kết hợp.

Phương pháp của PostgreSQL sử dụng mảng

Dưới đây là mã PostgreSQL để tìm phần tử đầu tiên và cuối cùng trong mảng kết hợp:

DO

$$

DECLARE

    v_index             character varying :='HYD';

    varchar_assoc_array varchar_assoc_array_type[];

    first_key           integer;

    last_key            integer;

BEGIN

    -- Populate the associative array with key-value pairs

    varchar_assoc_array := ARRAY[row('Key 1','Value 1'),row('Key 2','Value 2'),row('Key 3','Value 3')];

    -- Find the first and last elements in the associative array

    RAISE NOTICE '*** Find the first and last elements in the associative array ***';

     first_key := coalesce(array_lower(varchar_assoc_array,1),0);

     last_key  :=  coalesce(array_upper(varchar_assoc_array,1),0);

    -- Output the first and last key-value pairs

RAISE NOTICE 'First Key: % , Value: % '    ,varchar_assoc_array[first_key].index_col , varchar_assoc_array[first_key].index_value;

RAISE NOTICE 'Last Key: % , Value: % ' , varchar_assoc_array[last_key].index_col , varchar_assoc_array[last_key].index_value; 

END;

$$

Output:

NOTICE: *** Find the first and last elements in the associative array ***

NOTICE: First Key: Key 1 , Value: Value 1

NOTICE: Last Key: Key 3 , Value: Value 3

DO

Trong PostgreSQL, chúng ta sử dụng các phương thức array_lower và array_upper. Phương thức array_lower trả về giới hạn dưới (chỉ mục) của mảng và phương thức array_upper trả về giới hạn trên (chỉ mục). Bởi vì mảng PostgreSQL được bắt đầu từ 1 (khác với mảng kết hợp của Oracle bắt đầu từ 0), chúng ta sử dụng hàm coalesce để xử lý mảng trống và đặt giá trị mặc định là 0. Chúng ta cũng sử dụng phương thức array_length để tìm độ dài tối đa của mảng.

Phương pháp của PostgreSQL sử dụng JSONB

Để sử dụng JSONB, chúng ta cần hàm bao add_update_item_json_map được tạo trong bước trước.

Sau đó, chúng ta tạo một hàm bao mới có tên là get_item_json_map, nó trả về giá trị liên quan với một khóa cụ thể từ một đối tượng JSONB. Nó nhận hai tham số: đối tượng JSONB (field_entity_map) và khóa (key_item). Nó trả về giá trị liên quan với khóa đã chỉ định dưới dạng văn bản. Xem mã sau:

CREATE OR REPLACE FUNCTION get_item_json_map(

field_entity_map jsonb,

key_item text

)

    RETURNS text

    LANGUAGE 'plpgsql'

  AS $BODY$

  DECLARE

  BEGIN

       RETURN   field_entity_map->>(key_item); 

   END;

$BODY$;

Đoạn mã dưới đây thể hiện logic chính:

DO $$

DECLARE

    field_entity_map jsonb := '{}';

    keys text[];

    first_key text;

    last_key text;

    first_value text;

    last_value text;

    key_value text;

    array_size integer;

BEGIN

    -- Populate the JSONB object with key-value pairs

    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 1'::text, 'Value 1'::text)::jsonb;

    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 2'::text, 'Value 2'::text)::jsonb;

    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 3'::text, 'Value 3'::text)::jsonb;

    -- Loop through the keys and populate the keys array

    FOR key_value IN SELECT jsonb_object_keys(field_entity_map)

    LOOP

        keys := keys || key_value;

    END LOOP;

    -- Get the first and last keys

    first_key := keys[1];

    last_key := keys[array_upper(keys, 1)];

    -- Get the values corresponding to the first and last keys

    first_value := get_item_json_map(field_entity_map, first_key);

    last_value := get_item_json_map(field_entity_map, last_key);

    -- Output results

    RAISE NOTICE '*** Find the first and last elements in the associative array ***';

    RAISE NOTICE 'First Key: %, Value: %', first_key, first_value;

    RAISE NOTICE 'Last Key: %, Value: %', last_key, last_value;

END;

$$;

Trong ví dụ này, chúng ta đã lấy được khóa đầu tiên từ mảng khóa đã được sắp xếp bằng cách sử dụng keys[1] và lấy được khóa cuối cùng từ mảng khóa đã được sắp xếp bằng cách sử dụng keys[array_upper(keys, 1)]. Hàm get_item_json_map lấy giá trị liên quan với các khóa đầu tiên và cuối cùng.

Tìm xem một chỉ mục có tồn tại trong một mảng kết hợp và cập nhật giá trị chỉ mục đó

Trong phần này, chúng ta sẽ so sánh các phương pháp trong Oracle và PostgreSQL để tìm xem một chỉ mục có tồn tại trong một mảng kết hợp và cập nhật giá trị chỉ mục đó.

Phương pháp của Oracle

Dưới đây là mã Oracle để kiểm tra xem một khóa cụ thể có tồn tại trong mảng hay không và cập nhật giá trị liên quan với khóa đó nếu nó tồn tại:

DECLARE

TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);

      varchar_assoc_array varchar_assoc_array_type;

      current_key VARCHAR2(100);

BEGIN

  -- Populate the associative array with key-value pairs

  varchar_assoc_array('Key 1') := 'Value 1';

  varchar_assoc_array('Key 2') := 'Value 2';

  varchar_assoc_array('Key 3') := 'Value 3';

 -- Check if a specific key exists in the associative array and Update only if it exists 

  IF varchar_assoc_array.EXISTS('Key 2') THEN

     DBMS_OUTPUT.PUT_LINE('*** Key 2 exists with Value : ' ||       varchar_assoc_array('Key 2'));

     DBMS_OUTPUT.PUT_LINE('*** Update a specific element in the associative array ***');

     varchar_assoc_array('Key 2') := 'New Value 2';

     DBMS_OUTPUT.PUT_LINE('*** Key 2 updated with Value : ' ||   varchar_assoc_array('Key 2'));

  ELSE

    DBMS_OUTPUT.PUT_LINE('Key 2 does not exist in the associative array.');

  END IF;

END;

/

Output:

Statement processed.

*** Key 2 exists with Value : Value 2

*** Update a specific element in the associative array ***

*** Key 2 updated with Value : New Value 2

Chúng ta sử dụng phương thức EXISTS của mảng kết hợp để kiểm tra xem ‘Key 2’ có tồn tại trong mảng hay không. Sau đó, chúng ta cập nhật giá trị liên quan với ‘Key 2‘ thành ‘New Value 2’ bằng cách sử dụng câu lệnh gán varchar_assoc_array(‘Key 2’) := ‘New Value 2’.

Phương pháp của PostgreSQL sử dụng mảng

Dưới đây là mã PostgreSQL để đạt được mục tiêu tương tự:

DO

$$

DECLARE

v_index character varying :='HYD';

varchar_assoc_array varchar_assoc_array_type[];

v_pos int;

BEGIN

    -- Populate the associative array with key-value pairs

    varchar_assoc_array := ARRAY[row('Key 1','Value 1'),row('Key 2','Value 2'),row('Key 3','Value 3')];

      -- Check if a specific key exists in the associative array and Update only if it exists 

    IF EXISTS (

SELECT * FROM unnest(varchar_assoc_array) a  WHERE a.index_col='Key 2'

 ) 

    THEN

raise notice '*** Update a specific element in the associative array ***';

       SELECT a.v_rnum into v_pos from (

                                        SELECT *,row_number() over () v_rnum 

from unnest(varchar_assoc_array)

                                    ) a where a.index_col='Key 2';

raise notice '*** Key 2 exists with Value : %',         varchar_assoc_array[v_pos].index_value;

varchar_assoc_array[v_pos].index_value := 'New Value 2';

raise notice '*** Key 2 updated with Value :  %', 

varchar_assoc_array[v_pos].index_value;

    ELSE

        RAISE NOTICE 'Key 2 does not exist in the associative array.';

    END IF;

END;

$$;

Output:

Statement processed.

*** Key 2 exists with Value : Value 2

*** Update a specific element in the associative array ***

*** Key 2 updated with Value : New Value 2

Trong ví dụ PostgreSQL này, chúng ta mô phỏng hành vi của một mảng kết hợp tương tự như đã thể hiện trong ví dụ Oracle PL/SQL. Để xác định vị trí của ‘Key 2’ trong mảng, chúng ta sử dụng hàm unnest cùng với hàm cửa sổ row_number() và lưu kết quả vào biến v_pos. Nếu ‘Key 2’ tồn tại, chúng ta nhập khối IF. Sau đó, chúng ta cập nhật giá trị liên quan với ‘Key 2’ trong mảng kết hợp tại vị trí v_pos thành ‘New Value 2’.

Phương pháp của PostgreSQL sử dụng JSONB

Để sử dụng JSONB, chúng ta cần hàm bao add_update_item_json_map mà chúng ta đã tạo trước đó. Hàm này cho phép chúng ta thêm hoặc cập nhật các cặp khóa-giá trị trong một đối tượng JSONB.

Sau đó, chúng ta gọi hàm add_update_item_json_map để cập nhật giá trị:

DO $$

DECLARE

  field_entity_map jsonb  :='{}' ;

BEGIN

  RAISE NOTICE 'Jsonb processed.';   

-- Populate the associative array with key-value pairs

field_entity_map = add_update_item_json_map(field_entity_map , 

'Key 1'::text,'Value 1'::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , 

'Key 2'::text,'Value 2'::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , 

'Key 3'::text,'Value 3'::text)::jsonb;

-- Look through the associative array

RAISE NOTICE '*** Look through the associative array *** %',  field_entity_map;

-- Check if a specific key exists in the JSONB object and update only if it exists

   RAISE NOTICE '*** Update a specific element in the JSONB object ***';

   -- Check if 'Key 2' exists in the JSONB object

   IF field_entity_map ? 'Key 2' THEN

field_entity_map = add_update_item_json_map(field_entity_map, 'Key 2', 'New Value 2')::jsonb;

RAISE NOTICE '*** Key 2 updated, and the JSONB object now has New Value of key 2 *** %', field_entity_map;

   ELSE

     RAISE NOTICE '*** Key 2 does not exist in the JSONB object ***';

   END IF;

END;

$$;

Output:

NOTICE: Jsonb processed.

NOTICE: *** Look through the associative array *** {“Key 1”: “Value 1”, “Key 2”: “Value 2”, “Key 3”: “Value 3”}

NOTICE: *** Update a specific element in the JSONB object ***

NOTICE: *** Key 2 updated, and the JSONB object now has New Value of key 2 *** {“Key 1”: “Value 1”, “Key 2”: “New Value 2”, “Key 3”: “Value 3”}

DO

Trong ví dụ này, chúng ta đã gọi hàm add_update_item_json_map để cập nhật giá trị của khóa ‘Key 2’ từ ‘Value 2’ thành ‘New Value2’ trong đối tượng JSONB field_entity_map.

Chèn một phần tử mới vào mảng kết hợp

Trong cả hai ví dụ Oracle và PostgreSQL, chúng ta thể hiện cách chèn một phần tử mới vào một mảng kết hợp. Tuy nhiên, các phương pháp khác nhau do tính chất của mảng kết hợp trong mỗi hệ thống cơ sở dữ liệu.

Phương pháp của Oracle

Dưới đây là mã Oracle để chèn một phần tử vào một mảng kết hợp:

DECLARE

  TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);

  varchar_assoc_array varchar_assoc_array_type;

  current_key VARCHAR2(100);

BEGIN

  -- Populate the associative array with key-value pairs

   varchar_assoc_array('Key 1') := 'Value 1';

   varchar_assoc_array('Key 2') := 'Value 2';

   varchar_assoc_array('Key 3') := 'Value 3';

  -- Insert a new element into the associative array

  DBMS_OUTPUT.PUT_LINE('*** Insert a new element into the associative array ***');

  varchar_assoc_array('Key 4') := 'Value 4';

 DBMS_OUTPUT.PUT_LINE('*** Newly inserted Key 4  Value : ' || varchar_assoc_array('Key 4'));

END;

Output:

Statement processed.

*** Insert a new element into the associative array ***

*** Newly inserted Key 4 Value : Value 4

Trong ví dụ này, chúng ta sử dụng câu lệnh gán varchar_assoc_array(‘Key 4’) := ‘Value 4’. Chúng ta xuất khóa mới được chèn và giá trị liên quan của nó bằng câu lệnh DBMS_OUTPUT.PUT_LINE.

Phương pháp của PostgreSQL sử dụng mảng

Dưới đây là mã PostgreSQL để chèn một phần tử vào một mảng kết hợp:

DO

$$

DECLARE

v_index character varying :='HYD';

varchar_assoc_array varchar_assoc_array_type[];

v_pos int;

BEGIN

    -- Populate the associative array with key-value pairs

    varchar_assoc_array := ARRAY[row('Key 1','Value 1'),row('Key 2','Value 2'),row('Key 3','Value 3')];

    RAISE NOTICE ' *** Insert a new element into the associative array *** ';

    varchar_assoc_array[coalesce(array_length(varchar_assoc_array,1),0)+1] := row('key 4','Value 4');

   -- in postrgreSQL we need to find the position of "Key 4" index , before priniting its value

     SELECT a.v_rnum into v_pos from (

                                        SELECT *,row_number() over () v_rnum 

    from unnest(varchar_assoc_array)

                      ) a where a.index_col='key 4';

  RAISE NOTICE '*** Newly inserted Key 4  Value : %' ,      varchar_assoc_array[v_pos].index_value;

END;

$$;

Output:

Statement processed.

*** Insert a new element into the associative array ***

*** Newly inserted Key 4 Value : Value 4

Để chèn một phần tử mới vào mảng kết hợp, chúng ta sử dụng chỉ mục của mảng. Chúng ta tính vị trí cho phần tử mới bằng cách sử dụng coalesce(array_length(varchar_assoc_array, 1), 0) + 1, điều này cung cấp cho chúng ta vị trí kế tiếp có sẵn trong mảng. Sau đó, chúng ta gán cặp khóa-giá trị mới là row(‘key 4’, ‘Value 4’) vào vị trí đó.

Để lấy cặp khóa-giá trị mới được chèn, chúng ta sử dụng một câu truy vấn con với hàm unnest và hàm cửa sổ row_number() để tìm vị trí của ‘Key 4’ trong mảng. Chúng ta lưu trữ vị trí này trong biến v_pos.

Phương pháp của PostgreSQL sử dụng JSONB

Chúng ta cần hàm bao add_update_item_json_map mà chúng ta đã tạo trước đó để thêm hoặc cập nhật các cặp khóa-giá trị trong một đối tượng JSONB.

Chúng ta gọi hàm add_update_item_json_map để chèn một cặp khóa-giá trị mới vào đối tượng JSONB field_entity_map:

DO $$

DECLARE

field_entity_map jsonb  :='{}' ;

BEGIN

RAISE NOTICE 'Jsonb processed.';  

-- Populate the associative array with key-value pairs

field_entity_map = add_update_item_json_map(field_entity_map , 'Key 1'::text,'Value 1'::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , 'Key 2'::text,'Value 2'::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , 'Key 3'::text,'Value 3'::text)::jsonb;

-- Look through the associative array

RAISE NOTICE '*** Look through the associative array *** %',  field_entity_map;

-- Check if a specific key exists in the associative array and Update only if it exists 

  RAISE NOTICE '*** Add a new element in the associative array ***';

  field_entity_map = add_update_item_json_map(field_entity_map,'Key 2','New Value 2')::jsonb;  

RAISE NOTICE '*** Key 2 updated and the associative array now has New Value of key 2  *** %',  field_entity_map;

END;

$$

Output:

NOTICE: Jsonb processed.

NOTICE: *** Look through the associative array ***

{“Key 1”: “Value 1”, “Key 2”: “Value 2”, “Key 3”: “Value 3”}

NOTICE: *** Add a new element in the associative array ***

NOTICE: *** Key 2 updated and the associative array now has New Value of key 2 *** {“Key 1”: “Value 1”, “Key 2”: “New Value 2”, “Key 3”: “Value 3”}

DO

Trong ví dụ này, chúng ta đã gọi hàm add_update_item_json_map để chèn một phần tử mới với khóa ‘Key 4’ và ‘Value 4’ vào đối tượng JSONB field_entity_map.

Xóa một phần tử khỏi mảng kết hợp

Trong phần này, chúng ta sẽ so sánh các phương pháp trong Oracle và PostgreSQL để xóa một phần tử khỏi một mảng kết hợp.

Phương pháp của Oracle

Dưới đây là mã Oracle để xóa một phần tử khỏi một mảng kết hợp:

DECLARE

  TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);

  varchar_assoc_array varchar_assoc_array_type;

  current_key VARCHAR2(100);

BEGIN

  -- Populate the associative array with key-value pairs

   varchar_assoc_array('Key 1') := 'Value 1';

   varchar_assoc_array('Key 2') := 'Value 2';

   varchar_assoc_array('Key 3') := 'Value 3';

  DBMS_OUTPUT.PUT_LINE(' *** Delete a specific element from the associative array ***');

  -- Delete a specific element from the associative array

  varchar_assoc_array.DELETE('Key 2');

   -- Loop through the associative array to confirm the deletion

  current_key := varchar_assoc_array.FIRST;

  WHILE current_key IS NOT NULL LOOP

    -- Retrieve the value associated with the current key

    DBMS_OUTPUT.PUT_LINE('Key: ' || current_key || ', Value: ' || varchar_assoc_array(current_key));

    current_key := varchar_assoc_array.NEXT(current_key);

  END LOOP;

END;

/

Output:

Statement processed.

*** Delete a specific element from the associative array ***

Key: Key 1, Value: Value 1

Key: Key 3, Value: Value 3

Trong Oracle, để xóa một phần tử cụ thể khỏi mảng kết hợp, chúng ta sử dụng phương thức DELETE với khóa mà chúng ta muốn loại bỏ. Trong trường hợp này, chúng ta xóa phần tử với khóa ‘Key 2’.

Phương pháp của PostgreSQL sử dụng mảng

Dưới đây là mã PostgreSQL, trong đó chúng ta tạo một hàm trợ giúp có tên là array_rm để xóa một phần tử khỏi mảng kết hợp.

Trước tiên, chúng ta tạo hàm bao để xóa giá trị của khóa:

CREATE OR REPLACE FUNCTION array_rm(

    p_input anyarray,

    p_index integer,

    INOUT p_new_val anyarray)

    RETURNS anyarray

    LANGUAGE 'plpgsql'

AS $BODY$

Declare

 j NUMERIC:=0;

begin   

    For i in 1 .. coalesce(array_length(p_input,1),0) Loop 

      IF i != p_index THEN 

        j:= j+1;

        p_new_val[j] := p_input[i];

      END IF; 

    End Loop; 

end;

$BODY$;

Hàm này nhận một mảng đầu vào, chỉ mục của phần tử cần xóa và một mảng đầu ra (được truyền vào dưới dạng INOUT). Hàm lặp qua mảng đầu vào, loại trừ phần tử tại chỉ mục cụ thể và điền mảng đầu ra với các phần tử còn lại. Tiếp theo, chúng ta sử dụng đoạn mã sau:

DO

$$

DECLARE

v_index character varying :='HYD';

varchar_assoc_array varchar_assoc_array_type[];

varchar_assoc_array_local varchar_assoc_array_type[];

v_pos int;

BEGIN

    -- Populate the associative array with key-value pairs

    varchar_assoc_array := ARRAY[row('Key 1','Value 1'),row('Key 2','Value 2'),row('Key 3','Value 3')];

-- indentify the position of Key 2 

    SELECT a.v_rnum into v_pos from (

                                        SELECT *,row_number() over () v_rnum from unnest(varchar_assoc_array)

                                    ) a where a.index_col='Key 2';

    RAISE NOTICE ' *** Delete a specific element from the associative array using array_rm helper function ***';

    select array_rm(

                    varchar_assoc_array,

                    v_pos,

                    null) 

    into varchar_assoc_array_local;

    FOR i in 1..coalesce(array_length(varchar_assoc_array_local,1),0) 

    LOOP

RAISE NOTICE 'KEY : % VALUE : % ', varchar_assoc_array_local [i].index_col, varchar_assoc_array_local [i].index_value;

    END LOOP;    

END;

$$

Output:

NOTICE: *** Delete a specific element from the associative array using array_rm helper function ***

NOTICE: KEY : Key 1 VALUE : Value 1

NOTICE: KEY : Key 3 VALUE : Value 3

DO

Chúng ta sử dụng một câu truy vấn con với hàm unnest và hàm cửa sổ row_number() để xác định vị trí (v_pos) của khóa mà chúng ta muốn xóa khỏi mảng kết hợp. Sau đó, chúng ta gọi hàm array_rm với varchar_assoc_array và v_pos. Hàm loại bỏ phần tử tại vị trí v_pos và trả về mảng đã được cập nhật vào một mảng được khai báo cục bộ. Chúng ta gán kết quả của hàm array_rm trở lại cho varchar_assoc_array để áp dụng việc xóa.

Phương pháp của PostgreSQL sử dụng JSONB

Đầu tiên, chúng ta tạo một hàm có tên là delete_item_json_map. Hàm này nhận một đối tượng JSONB (field_entity_map) và một khóa (key_item) làm tham số. Nó trả về một đối tượng JSONB với cặp khóa-giá trị đã chỉ định được loại bỏ. Xem mã sau:

CREATE OR REPLACE FUNCTION delete_item_json_map(

    field_entity_map jsonb,

    key_item text)

    RETURNS jsonb

    LANGUAGE ‘plpgsql’

    COST 100

    VOLATILE PARALLEL UNSAFE

AS $BODY$

DECLARE    

BEGIN

   return field_entity_map-key_item;     

END;

$BODY$;

Chúng ta sử dụng logic chính sau để xóa một cặp khóa-giá trị cụ thể khỏi mảng kết hợp bằng cách sử dụng hàm delete_item_json_map:

DO $$

DECLARE

field_entity_map jsonb  :='{}’ ;

BEGIN

RAISE NOTICE ‘Jsonb processed.’;    

— Populate the associative array with key-value pairs

field_entity_map = add_update_item_json_map(field_entity_map , ‘Key 1′::text,’Value 1’::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , ‘Key 2′::text,’Value 2’::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , ‘Key 3′::text,’Value 3’::text)::jsonb;

— Delete an element into the associative array

RAISE NOTICE ‘ *** Delete a specific element from the associative array ***’;

RAISE NOTICE ‘before Delete varchar_assoc_array %’,  field_entity_map;

field_entity_map=delete_item_json_map(field_entity_map,’Key 2′);

RAISE NOTICE ‘After deleting Key 2 from the associative array *** %’,  field_entity_map;

END;

$$

Output:

NOTICE: Jsonb processed.

NOTICE: *** Delete a specific element from the associative array ***

NOTICE: before Delete varchar_assoc_array {“Key 1”: “Value 1”, “Key 2”: “Value 2”, “Key 3”: “Value 3”}

NOTICE: After deleting Key 2 from the associative array *** {“Key 1”: “Value 1”, “Key 3”: “Value 3”}

Trong ví dụ này, chúng tôi đã gọi hàm delete_item_json_map để xóa khóa ‘Key 2’ khỏi đối tượng JSONB field_entity_map.

Làm sạch:

Để làm sạch tài nguyên bạn đã tạo trong bài viết này, hãy hoàn thành các bước sau:

  • Nếu bạn đã tạo một phiên bản RDS for Oracle DB, hãy xóa phiên bản đó qua bảng điều khiển Amazon RDS.
  • Xóa phiên bản Amazon Aurora PostgreSQL-compatible hoặc Amazon RDS PostgreSQL của bạn qua bảng điều khiển Amazon RDS.

Đối với thông tin chi tiết, hãy tham khảo tài liệu “Xóa một DB instance” của Amazon RDS.

Kết luận

Quá trình di chuyển mảng kết hợp từ Oracle sang PostgreSQL đòi hỏi một cách tiếp cận cẩn thận và xem xét các cấu trúc dữ liệu và biện pháp tạm thời thay thế. Mảng kết hợp và JSONB của PostgreSQL cung cấp nhiều tùy chọn đa dạng để xử lý các cặp khóa-giá trị. Bằng cách hiểu sự khác biệt giữa hai hệ thống cơ sở dữ liệu và áp dụng các chiến lược thích hợp, bạn có thể đảm bảo một quá trình di chuyển thành công và duy trì chức năng mong muốn cho việc quản lý dữ liệu kết hợp trong môi trường PostgreSQL của bạn. Trong bài viết này, chúng tôi hướng dẫn cách giải quyết thách thức của việc di chuyển mảng kết hợp từ Oracle sang PostgreSQL. Quá trình này đòi hỏi kế hoạch cẩn thận và xem xét nhiều cấu trúc dữ liệu và biện pháp tạm thời khác nhau để đảm bảo một chuyển đổi thành công. Chúng tôi đã thảo luận về hai lựa chọn chính trong PostgreSQL để xử lý các cặp khóa-giá trị, đó là mảng và JSONB.

Bằng cách hiểu sự khác biệt giữa Oracle và PostgreSQL và áp dụng các chiến lược phù hợp, bạn có thể di chuyển và duy trì chức năng mong muốn để quản lý dữ liệu kết hợp trong môi trường PostgreSQL của bạn.

Chúng tôi mời bạn để lại phản hồi trong phần bình luận của bài viết này.

Về tác giả:

Bikash Chandra Rout là Chuyên gia tư vấn cơ sở dữ liệu chính của nhóm Dịch vụ chuyên nghiệp tại Amazon Web Services. Bikash tập trung vào việc giúp khách hàng xây dựng các giải pháp cơ sở dữ liệu có tính sẵn sàng cao, tiết kiệm chi phí và di chuyển cơ sở dữ liệu Oracle quy mô lớn của họ sang AWS. Anh ấy đam mê cơ sở dữ liệu và phân tích.

Sashikanta Pattanayak là Chuyên gia tư vấn chính của nhóm dịch vụ Chuyên nghiệp tại AWS. Anh làm việc với khách hàng để xây dựng các giải pháp có quy mô linh hoạt, tính sẵn sàng cao và an toàn trên đám mây AWS. Anh ấy chuyên về di chuyển cơ sở dữ liệu đồng nhất và không đồng nhất.

Neha Sharma là Chuyên gia tư vấn cơ sở dữ liệu của Amazon Web Services. Với hơn một thập kỷ kinh nghiệm làm việc với cơ sở dữ liệu, cô giúp khách hàng AWS di chuyển cơ sở dữ liệu của họ sang Đám mây AWS. Ngoài công việc, cô thích tích cực tham gia nhiều hoạt động thể thao và thích giao lưu với mọi người.

Gokul Gunasekaran là Chuyên gia tư vấn của nhóm Dịch vụ Chuyên nghiệp tại Amazon Web Services. Anh làm việc với tư cách là Chuyên gia di chuyển cơ sở dữ liệu để giúp khách hàng AWS di chuyển và hiện đại hóa khối lượng công việc cơ sở dữ liệu tại chỗ của họ sang các giải pháp cơ sở dữ liệu đám mây AWS.

←
→

Leave a comment