Khách hàng sử dụng bộ lưu trữ dữ liệu bán cấu trúc hoặc không cấu trúc cho các trường hợp sử dụng kinh doanh khác nhau, về bản chất là không có sơ đồ và linh hoạt. Một loại dữ liệu bán cấu trúc như vậy là Ký hiệu đối tượng JavaScript (JSON) . JSON lưu trữ dữ liệu ở dạng định dạng KEY, VALUE, LIST và ARRAY.
Cơ sở dữ liệu Oracle lưu trữ dữ liệu đối tượng JSON trong các cột kiểu dữ liệu CLOB . PostgreSQL cung cấp các cột JSON (văn bản) và JSONB (nhị phân) để lưu trữ dữ liệu JSON và bạn cũng có thể chọn lưu trữ dữ liệu JSON trong cột TEXT nếu cột đó không tuân thủ RFC 8259.
Trong bài đăng này, chúng tôi trình bày một giải pháp giúp bạn chuyển đổi các truy vấn JSON SQL từ cơ sở dữ liệu Oracle sang cơ sở dữ liệu PostgreSQL. Giải pháp của chúng tôi chủ yếu tập trung vào việc chuyển đổi dữ liệu liên quan đến JSON sang định dạng BẢNG (ROW và COLUMN) và ngược lại bằng cách sử dụng Oracle và PostgreSQL. Bạn có thể sử dụng giải pháp này khi di chuyển từ Oracle tại chỗ hoặc Amazon Relational Database Service (Amazon RDS) for Oracle sang Amazon RDS for PostgreSQL hoặc Amazon Aurora PostgreSQL-Compatible Edition .
Tổng quan về giải pháp
Giải pháp này hoạt động cho Cơ sở dữ liệu Oracle nguồn phiên bản 12.2 trở lên và RDS đích cho các phiên bản cơ sở dữ liệu PostgreSQL hoặc Aurora PostgreSQL có phiên bản 9.5 trở lên.
Là một phần của quá trình chuyển đổi truy vấn JSON, chúng tôi sử dụng các hàm sau trong Oracle:
- JSON_TABLE – Chuyển đổi dữ liệu JSON thành định dạng TABLE
- JSON_ARRAYAGG – Tổng hợp định dạng JSON
- JSON_OBJECT – Chuyển dữ liệu BẢNG sang định dạng JSON
Chúng tôi sử dụng các chức năng sau trong PostgreSQL:
- JSONB_BUILD_OBJECT – Tạo và trả về một đối tượng JSON từ danh sách tham số biến đổi bao gồm các khóa và giá trị xen kẽ
- JSONB_AGG – Hàm tổng hợp trả về một mảng JSON chứa tất cả các giá trị trong một nhóm
- JSONB_ARRAY_ELEMENTS – Mở rộng mảng JSON cấp cao nhất thành một tập hợp các giá trị JSON
Lưu ý rằng các hàm JSON_ARRAYAGG và JSON_OBJECT tiêu chuẩn SQL có sẵn và được hỗ trợ từ PostgreSQL 16 trở đi.
Trong các phần sau, chúng tôi trình bày quá trình chuyển đổi từng bước các truy vấn SQL dựa trên JSON từ Oracle sang PostgreSQL.
Điều kiện tiên quyết
Để làm theo bài đăng này, bạn cần có các điều kiện tiên quyết sau:
- Một tài khoản AWS đang hoạt động
- Phiên bản Oracle DB tại chỗ (cơ sở dữ liệu nguồn) đang hoạt động với phiên bản 12.2 trở lên
- Một phiên bản RDS cho PostgreSQL hoặc Aurora PostgreSQL DB (cơ sở dữ liệu đích) được thiết lập và chạy với phiên bản 9.5 trở lên
Chúng tôi sử dụng Công cụ hội thoại lược đồ AWS (AWS SCT) để chuyển đổi các hàm cơ sở dữ liệu Oracle nguồn sang PostgreSQL . Việc chuyển đổi tạo ra các tệp SQL di chuyển được PostgreSQL hỗ trợ . Bạn có thể chạy các tệp .SQL này trên phiên bản cơ sở dữ liệu PostgreSQL của mình.
Chuyển đổi dữ liệu JSON sang định dạng BẢNG
Trong phần này, chúng tôi thảo luận về hai tùy chọn để chuyển đổi dữ liệu JSON sang định dạng BẢNG :
- Chuyển đổi dữ liệu JSON có sẵn trong bảng
- Chuyển đổi dữ liệu JSON tĩnh được truyền sang hàm bảng JSON
Các tùy chọn này giải thích cách chuyển đổi dữ liệu JSON của Oracle và PostgreSQL sang định dạng BẢNG cho dữ liệu được lưu trữ trong các bảng và các giá trị tĩnh được truyền từ lớp ứng dụng.
Chuyển đổi dữ liệu Oracle JSON có sẵn trong bảng
Hàm SQL/JSON JSON_TABLE tạo chế độ xem quan hệ của dữ liệu JSON. Nó ánh xạ kết quả đánh giá dữ liệu JSON thành các hàng và cột quan hệ. Bạn có thể truy vấn kết quả được hàm trả về dưới dạng bảng quan hệ ảo bằng cách sử dụng SQL. Mục đích chính của JSON_TABLE là tạo một hàng dữ liệu quan hệ cho từng đối tượng bên trong một mảng JSON và xuất các giá trị JSON từ bên trong đối tượng đó dưới dạng các giá trị cột SQL riêng lẻ.
Bạn chỉ có thể chỉ định JSON_TABLE trong mệnh đề FROM của câu lệnh SELECT. Trước tiên, hàm này áp dụng biểu thức đường dẫn JSON, được gọi là biểu thức đường dẫn hàng , cho dữ liệu JSON được cung cấp. Giá trị JSON khớp với biểu thức đường dẫn hàng được gọi là nguồn hàng , tạo ra một hàng dữ liệu quan hệ. Mệnh đề COLUMNS đánh giá nguồn hàng, tìm các giá trị JSON cụ thể trong nguồn hàng và trả về các giá trị JSON đó dưới dạng giá trị SQL trong các cột riêng lẻ của một hàng dữ liệu quan hệ.
JSON_TABLE có các tham số sau:
- Biểu thức – Truyền tài liệu hoặc cột JSON có nội dung JSON
- Biểu thức đường dẫn JSON – Đường dẫn hoặc thứ bậc của tài liệu JSON
- Mệnh đề ERROR – Xử lý lỗi khi trường JSON tương ứng không tồn tại
- Danh sách COLUMN – Danh sách các cột ánh xạ các trường JSON trong đối tượng JSON
Qua bảng này chúng ta có thể hiểu như sau:
- Để có được profileTypegiá trị, hệ thống phân cấp hoặc đường dẫn sử dụng làmetadata -> profile type
- Để có được positionIdgiá trị, hệ thống phân cấp hoặc đường dẫn sử dụng làdata -> positionId
- Để có được accountNumbergiá trị, hệ thống phân cấp hoặc đường dẫn sử dụng làdata -> account -> accountNumber
Hãy chèn dữ liệu JSON mẫu của chúng tôi vào bảng aws_test_table, với cột json_dockiểu dữ liệu CLOB. Sử dụng truy vấn SQL được cung cấp để chuyển đổi các trường JSON đã chọn sang định dạng BẢNG.
- Kết nối với cơ sở dữ liệu Oracle nguồn của bạn bằng SQL Developer hoặc một công cụ ưa thích khác.
- Chạy lệnh sau để tạo bảng kiểm tra:
- Chạy lệnh sau để chèn dữ liệu JSON mẫu vào bảng:
- Chạy truy vấn SQL sau để chuyển đổi dữ liệu thành HÀNG và CỘT:
Từ truy vấn trước, chúng ta có thể hiểu rằng hàm JSON_TABLE có các tham số sau:
- JSON_DOC – Đây là json_doccột chứa nội dung JSON.
- $ – Đường dẫn được xác định là $. Theo mặc định, $cho biết tất cả các trường trong tài liệu JSON. Ví dụ: $.account[*]có nghĩa là tất cả các trường bên dưới accountkhóa (tham khảo dữ liệu mẫu). $.metadata[*]có nghĩa là tất cả các trường dưới metadatakhóa.
- ERROR ON ERROR – Đây là cơ chế xử lý lỗi. Nó có ba biến thể:
- NULL TRÊN LỖI – Trả về null khi xảy ra lỗi. Đây là mặc định.
- LỖI TRÊN LỖI – Trả về lỗi Oracle thích hợp khi xảy ra lỗi.
- Chữ DEFAULT ON ERROR – Trả về một chữ khi xảy ra lỗi. Nếu kiểu dữ liệu của giá trị được hàm này trả về là VARCHAR2 thì bạn phải chỉ định một hằng văn bản. Nếu kiểu dữ liệu là NUMBER thì bạn phải chỉ định một chữ số.
- COLUMNS – Đây là danh sách tên cột cùng với kiểu dữ liệu và đường dẫn. Nếu đường dẫn tương ứng tồn tại trong JSON, nó sẽ trả về giá trị được liên kết với trường đó. Nếu không, một lỗi sẽ được xử lý.
Ảnh chụp màn hình sau đây hiển thị kết quả của truy vấn trước đó.
Chuyển đổi dữ liệu JSON PostgreSQL có sẵn trong bảng
Oracle có hàm JSON_TABLE để chuyển dữ liệu JSON sang định dạng TABLE. Tuy nhiên, PostgreSQL hiện không có hàm JSON_TABLE của SQL/JSON, vì vậy thay vào đó chúng ta phải đọc dữ liệu trực tiếp từ tài liệu JSON để chuyển đổi nó thành bảng. Để minh họa điều này, chúng tôi sử dụng dữ liệu JSON mẫu sau.
- Kết nối với cơ sở dữ liệu PostgreSQL đích bằng pgAdmin hoặc một công cụ ưa thích khác.
- Chạy lệnh sau để tạo bảng kiểm tra:
- Chạy lệnh sau để chèn dữ liệu JSON vào bảng kiểm tra
- Chạy truy vấn SQL sau để chuyển đổi dữ liệu thành HÀNG và CỘT:
Việc sử dụng một toán tử lớn hơn ( ->) sẽ trả về giá trị được xác định cho kiểu dữ liệu JSON đó. Ví dụ: khóa positionIdvà giá trị “0100”. Tuy nhiên, nếu bạn muốn giá trị khóa là INT hoặc TEXT, bạn không thể gõ trực tiếp kiểu dữ liệu JSON. Bạn có thể sử dụng toán tử lớn hơn ( ->>) gấp đôi để trả về giá trị khóa ở định dạng INT hoặc TEXT.
Sử dụng gấp đôi lớn hơn ( ->>) sẽ trả về giá trị thực được xác định cho khóa đó. Ví dụ: khóa positionIdvà giá trị 0100(không có dấu ngoặc kép). Việc đánh máy được cho phép khi chúng tôi sử dụng ->>.
Trong trường hợp trước, parentAccountNumber, accountNumber, và businessUnitIdđược định kiểu thành INT và positionIdđược định kiểu thành TEXT.
Chuyển đổi dữ liệu JSON Oracle tĩnh được truyền sang hàm bảng JSON
Chúng ta có thể sử dụng cùng hàm JSON_TABLE để chuyển đổi dữ liệu JSON tĩnh sang định dạng TABLE.
Chuyển đổi dữ liệu JSON PostgreSQL tĩnh được chuyển sang truy vấn mệnh đề FROM
Hàm JSONB_ARRAY_ELEMENTS giúp chuyển đổi dữ liệu JSON tĩnh (được chuyển đến truy vấn mệnh đề FROM) sang định dạng TABLE. Phương pháp chúng tôi sử dụng ở phần trước không có tác dụng chuyển đổi dữ liệu JSON tĩnh sang định dạng BẢNG.
Việc sử dụng ->trả về giá trị được xác định cho khóa đó. Ví dụ: khóa businessUnitIdvà giá trị “7”. Việc định kiểu không được phép đối với các kiểu dữ liệu nguyên thủy khi chúng ta sử dụng ->.
Việc sử dụng ->>trả về giá trị thực được xác định cho khóa đó. Ví dụ: khóa businessUnitIdvà giá trị 7(không có dấu ngoặc kép). Việc đánh máy được cho phép khi chúng tôi sử dụng ->>.
Trong trường hợp trước, parentAccountNumber, accountNumbervà businessUnitIdđược định kiểu thành INT.
Chuyển đổi dữ liệu định dạng BẢNG sang định dạng JSON
Trong phần này, chúng tôi trình bày cách chuyển đổi dữ liệu định dạng TABLE của Oracle và PostgreSQL thành dữ liệu định dạng JSON.
Định dạng BẢNG Oracle
Chúng ta có thể sử dụng hàm JSON_OBJECT để chuyển đổi dữ liệu TABLE sang định dạng JSON (cặp khóa-giá trị). Điều này có các thông số sau:
- KEY và một biểu thức khóa – Ví dụ:account
- VALUE và một biểu thức giá trị – Ví dụ: column name FROM table, lấy giá trị từ bảng
Hàm SQL và JSON JSON_ARRAYAGG là hàm tổng hợp. Nó lấy đầu vào là một cột gồm các biểu thức SQL, chuyển đổi từng biểu thức thành một giá trị JSON và trả về một mảng JSON chứa các giá trị JSON đó. Điều này chủ yếu được sử dụng để chuyển đổi nhiều hàng thành một mảng ở định dạng JSON.
Dữ liệu định dạng BẢNG PostgreSQL
Kể từ PostgreSQL 16, PostgreSQL hỗ trợ các hàm JSON_OBJECT và JSON_ARRAYAGG, là một phần của tiêu chuẩn SQL/JSON. Đối với các phiên bản PostgreSQL cũ hơn, bạn sẽ cần sử dụng một số hàm khác để hỗ trợ các truy vấn này.
Các hàm PostgreSQL sau đây đọc dữ liệu TABLE và chuyển đổi nó thành định dạng JSON:
- JSONB_BUILD_OBJECT – Điều này hoạt động tương tự như Oracle JSON_OBJECT. Tuy nhiên, cú pháp lại khác: JSONB_BUILD_OBJECT (key1, value1, key2, value2, key3, value3, keyn, valuen).
- JSONB_AGG – Điều này hoạt động tương tự như Oracle JSON_ARRAYAGG. Cú pháp cũng giống như Oracle.
Dọn dẹp
Vì giải pháp này sử dụng chuyển đổi mã thủ công nên không cần phải dọn dẹp mã, chính sách hoặc vai trò. Xóa cơ sở dữ liệu đích nếu không cần thiết nữa.
Bản tóm tắt
Trong bài đăng này, chúng tôi đã giải thích các chức năng khác nhau để tạo và xử lý dữ liệu JSON trong cơ sở dữ liệu Oracle và PostgreSQL. Chúng tôi cũng đã chỉ ra cách chuyển đổi mã liên quan đến Oracle JSON (dữ liệu JSON thành định dạng ROW và COLUMN hoặc TABLE và ngược lại) sang PostgreSQL theo cách thủ công.
PostgreSQL đang tiến hành công việc ngược dòng có thể giúp đơn giản hóa việc di chuyển dữ liệu JSON từ Oracle sang PostgreSQL. Cụ thể, PostgreSQL đang nỗ lực đưa các hàm tiêu chuẩn SQL/JSON, bao gồm JSON_TABLE, vào bản phát hành trong tương lai.
Nếu bạn có bất kỳ câu hỏi hoặc nhận xét nào về bài đăng này, vui lòng chia sẻ suy nghĩ của bạn trong phần bình luận.
Giới thiệu về tác giả
Pinesh Singal là Chuyên gia tư vấn cấp cao của AWS ProServe, GCC Ấn Độ. Anh làm việc với tư cách là Chuyên gia tư vấn di chuyển cơ sở dữ liệu, hỗ trợ và tạo điều kiện cho khách hàng di chuyển máy chủ và cơ sở dữ liệu sang AWS. Ông đã kiến trúc và thiết kế nhiều cơ sở dữ liệu và giải pháp di chuyển thành công nhằm giải quyết các yêu cầu kinh doanh đầy thách thức. Trọng tâm chính của anh là di chuyển đồng nhất và không đồng nhất các cơ sở dữ liệu tại chỗ sang Amazon RDS cùng với các giải pháp tự động hóa di chuyển hoàn chỉnh.
Lokesh Gurram là Chuyên gia tư vấn chính của AWS ProServe, GCC Ấn Độ. Anh hỗ trợ và cho phép khách hàng di chuyển máy chủ và cơ sở dữ liệu sang AWS. Anh ấy thích làm việc ngược và tự động hóa các quy trình thủ công để nâng cao trải nghiệm người dùng với ít Business As Usual nhất. Trọng tâm chính của anh là di chuyển đồng nhất và không đồng nhất các cơ sở dữ liệu tại chỗ sang Amazon RDS cùng với các giải pháp tự động hóa di chuyển hoàn chỉnh.
AWS Study Group 