Sử Dụng Prepared Statements với Ngày trong Amazon Athena (Java)

Giới Thiệu

Amazon Athena là một dịch vụ truy vấn tương tác không máy chủ, cho phép bạn phân tích dữ liệu trong Amazon S3 bằng SQL chuẩn. Mặc dù việc chạy truy vấn bằng cách truyền chuỗi SQL đơn giản, nhưng phương pháp này có thể dễ gây lỗi, khó duy trì và dễ bị tấn công SQL injection khi xử lý với các giá trị động.

Khi làm việc với Amazon Athena, hầu hết các nhà phát triển thường bắt đầu bằng cách viết truy vấn SQL thô trực tiếp trong mã của họ. Ví dụ, truy vấn có thể trông như sau khi không sử dụng prepared statements:

Không Sử Dụng Prepared Statement (Phương Pháp Cũ)

String query = "SELECT * FROM orders WHERE order_date >= DATE '" + startDate + "' " +
               "AND order_date <= DATE '" + endDate + "'";

Mặc dù điều này hoạt động, nhưng hãy chú ý cách mà truy vấn được xây dựng bằng cách nối chuỗi. Nếu startDate hoặc endDate không được định dạng đúng, nó sẽ dẫn đến lỗi thời gian chạy. Hơn nữa, mẫu này không mở rộng tốt khi các truy vấn trở nên phức tạp hơn.

Để giải quyết vấn đề này, prepared statements cung cấp một cách sạch hơn và an toàn hơn để làm việc với các truy vấn trong Athena. Thay vì xây dựng truy vấn bằng cách nối chuỗi, các nhà phát triển có thể sử dụng các ký tự thay thế ? và ràng buộc các giá trị tại thời gian chạy. Athena hỗ trợ prepared statements và các tham số thực thi, giúp các truy vấn dễ đọc hơn, ít lỗi hơn và an toàn hơn trước các vấn đề phổ biến như không khớp kiểu dữ liệu hay SQL injection.

Với Prepared Statement (Phương Pháp Thích Hợp)
Sử dụng các ký tự thay thế ? giúp các truy vấn trở nên sạch hơn và có thể tái sử dụng:

String query = """
    SELECT * FROM orders
    WHERE order_date >= ?
      AND order_date <= ?
""";

List<Object> parameters = List.of(startDate, endDate);
List<Row> rows = athenaExecutor.execute(query, parameters);

Cách tiếp cận này cải thiện khả năng đọc, giảm lỗi và đảm bảo an toàn kiểu dữ liệu.

Tuy nhiên, việc sử dụng trong thế giới thực không phải lúc nào cũng đơn giản. Đặc biệt, các cột DATE thường gây ra lỗi không khớp kiểu dữ liệu vì Athena không tự động suy diễn kiểu tham số. Ở phần tiếp theo, chúng ta sẽ trình bày một cách tiếp cận an toàn để sử dụng các truy vấn có tham số trong Athena với Java, tập trung vào việc xử lý đúng các giá trị LocalDate.

Vấn Đề Nảy Sinh

Khi truy vấn Athena bằng cách sử dụng phương pháp giống như prepared statement, các trường ngày thường gây ra lỗi không khớp kiểu dữ liệu. Ví dụ, giả sử chúng ta cần lấy đơn hàng gần nhất của một khách hàng đến một ngày nhất định.

public Order fetchLatestOrder(Long customerId, String orderType, LocalDate targetDate) {
    String query = """
        SELECT * FROM %s.vw_orders
        WHERE customer_id = ?
          AND order_date <= ?
        ORDER BY order_date DESC
        LIMIT 1
    """.formatted(DatabaseSelector.getReportingDatabase());

    List<Object> parameters = List.of(customerId, targetDate);
    List<Row> rows = athenaExecutor.execute(query, parameters);

    if (rows.isEmpty()) {
        throw new DataNotFoundException("Không tìm thấy đơn hàng cho các tham số đã cho");
    }

    return mapRowToOrder(rows.get(0));
}

SQL Sinh Ra (không chính xác khi chạy)

SELECT * 
FROM mydb.vw_orders
WHERE customer_id = 12345
  AND order_date <= 2025-09-05  -- Athena xem điều này như số nguyên/chuỗi, không phải DATE
ORDER BY order_date DESC
LIMIT 1;

Tại thời gian chạy, Athena diễn giải targetDate không chính xác, dẫn đến một lỗi như:
Type mismatch: cannot apply operator date <= integer

Nguyên Nhân Gốc: Athena diễn giải tham số LocalDate như một chuỗi hoặc số thay vì một hằng DATE. So sánh order_date <= ? do đó thất bại.

Giải Pháp

Cách khắc phục là dạy dịch vụ thực thi truy vấn cách định dạng các tham số một cách chính xác, đặc biệt là cho LocalDate. Bằng cách mở rộng trình định dạng tham số, chúng ta có thể đảm bảo ngày tháng được bọc trong định dạng hằng DATE 'yyyy-mm-dd' của Athena.

Ví dụ, trong trình thực thi truy vấn:

private String formatParameter(Object param) {
    if (param instanceof LocalDate date) {
        return String.format("DATE '%s'", date);
    } else if (param instanceof Number) {
        return param.toString();
    } else if (param != null) {
        return "'" + escapeQuotes(param.toString()) + "'";
    } else {
        return "NULL";
    }
}

Với sự điều chỉnh này, khi targetDate là một LocalDate, Athena sẽ nhận được:

SELECT * 
FROM mydb.vw_orders
WHERE customer_id = 12345
  AND order_date <= DATE '2025-09-05'
ORDER BY order_date DESC
LIMIT 1;

Athena bây giờ diễn giải ngày tháng một cách chính xác và truy vấn thực thi thành công.

Những Điều Cần Lưu Ý:

• Prepared statements trong Athena không hoạt động giống như prepared statements trong cơ sở dữ liệu truyền thống.
• Chúng không ngăn chặn SQL injection một cách tự động.
• Việc sử dụng các truy vấn có tham số vẫn được khuyến nghị để đảm bảo rõ ràng và dễ bảo trì, nhưng cần thêm sự cẩn trọng với dữ liệu đầu vào của người dùng.
• Athena yêu cầu kiểu dữ liệu rõ ràng cho các phép so sánh DATE.
• Luôn định dạng các tham số LocalDate thành hằng DATE 'YYYY-MM-DD'.
• Các số và chuỗi cũng nên được định dạng và thoát một cách an toàn.
• Sử dụng một trình thực thi chuyên dụng cho việc thay thế tham số giúp các truy vấn có thể tái sử dụng và ngăn chặn lỗi.
• Đối với sản xuất, hãy cân nhắc sử dụng các prepared statements hoặc tham số thực thi gốc của Athena thông qua AWS SDK để tránh việc thay thế chuỗi thủ công hoàn toàn.

Thực Hành Tốt Nhất

• Sử dụng Prepared Statements: Luôn sử dụng prepared statements để bảo mật và dễ bảo trì.
• Kiểm Tra Định Dạng Ngày: Kiểm tra định dạng của ngày tháng trước khi thực hiện truy vấn.
• Sử Dụng Các Tham Số Thay Thế: Sử dụng các ký tự thay thế ? để tránh lỗi khi xây dựng chuỗi SQL.

Cạm Bẫy Thường Gặp

• Bỏ Qua Định Dạng Ngày: Nếu bỏ qua định dạng ngày tháng, bạn có thể gặp lỗi không khớp kiểu dữ liệu.
• Sử Dụng Tham Số Không Đúng: Đảm bảo rằng các tham số truyền vào phải đúng với loại dữ liệu mà truy vấn yêu cầu.

Mẹo Hiệu Suất

• Tối Ưu Truy Vấn: Sử dụng chỉ mục trong bảng để tối ưu hóa hiệu suất truy vấn.
• Giảm Thiểu Lượng Dữ Liệu Truy Vấn: Hạn chế số lượng bản ghi được truy vấn bằng cách sử dụng các điều kiện hợp lý.

Giải Quyết Vấn Đề

• Lỗi Không Khớp Kiểu Dữ Liệu: Kiểm tra lại kiểu dữ liệu của tham số và định dạng đúng trước khi thực hiện truy vấn.

Câu Hỏi Thường Gặp

1. Amazon Athena hỗ trợ loại dữ liệu nào?
Athena hỗ trợ nhiều loại dữ liệu như STRING, INTEGER, DOUBLE, và DATE.

2. Làm thế nào để biết khi nào nên sử dụng prepared statements?
Khi bạn cần đảm bảo an toàn cho truy vấn SQL và tăng tính bảo trì cho mã của bạn.

3. Có cách nào để kiểm tra xem truy vấn có thành công không không?
Có, bạn có thể kiểm tra số lượng hàng trả về hoặc xử lý ngoại lệ để xác định kết quả của truy vấn.

Kết Luận

Việc sử dụng prepared statements trong Amazon Athena giúp bảo mật và nâng cao khả năng bảo trì mã của bạn. Đảm bảo rằng bạn luôn định dạng đúng các kiểu dữ liệu, đặc biệt là các trường ngày tháng, để tránh các lỗi không đáng có. Nếu bạn có bất kỳ câu hỏi nào hoặc muốn tìm hiểu thêm về cách tối ưu hóa truy vấn của mình trong Athena, hãy tham gia cộng đồng phát triển và chia sẻ kinh nghiệm của bạn!