[ad_1]
Nhập thư viện Python, thao tác và xuất bảng SQL, v.v., tất cả đều không cần rời khỏi máy chủ SQL.
Trong dự án này, chúng tôi phải đối mặt với thách thức quản lý 37.000 tên công ty có nguồn gốc từ hai nguồn gốc khác nhau. Sự phức tạp nằm ở sự khác biệt tiềm ẩn giữa cách liệt kê các công ty giống hệt nhau trên các nguồn này.
Mục tiêu của bài viết này là dạy bạn chạy Python nguyên bản trong máy chủ Microsoft SQL. Để sử dụng các tiện ích bổ sung và thư viện bên ngoài, cũng như thực hiện xử lý thêm trên các bảng kết quả bằng SQL.
Đây là chiến lược tôi sẽ tuân theo khi xây dựng các thuật toán:
- Chặn — Chia tập dữ liệu thành các khối hoặc nhóm nhỏ hơn dựa trên các thuộc tính chung để giảm độ phức tạp tính toán khi so sánh các bản ghi. Nó thu hẹp không gian tìm kiếm và nâng cao hiệu quả trong các nhiệm vụ tìm kiếm tương tự.
- Sơ chế — Làm sạch và chuẩn hóa dữ liệu thô để chuẩn bị phân tích bằng các tác vụ như chuyển đổi chữ thường, xóa dấu câu và dừng loại bỏ từ. Bước này cải thiện chất lượng dữ liệu và giảm nhiễu.
- Ứng dụng mô hình tìm kiếm tương tự — Áp dụng các mô hình để tính toán độ tương tự hoặc khoảng cách giữa các cặp bản ghi dựa trên các biểu diễn được mã hóa. Điều này giúp xác định các cặp tương tự, sử dụng các số liệu như độ tương tự cosine hoặc khoảng cách chỉnh sửa, cho các tác vụ như liên kết hoặc loại bỏ bản ghi.
Chặn
Bộ dữ liệu của tôi rất không cân xứng – tôi có 1.361.373 thực thể trong một bảng và chỉ có 37.171 tên công ty trong bảng thứ hai. Nếu tôi cố gắng so khớp trên bảng chưa được xử lý, thuật toán sẽ mất rất nhiều thời gian để thực hiện việc đó.
Để chặn các bảng, chúng ta cần xem có những đặc điểm chung nào giữa 2 tập dữ liệu. Trong trường hợp của tôi, các công ty đều liên quan đến các dự án nội bộ. Do đó tôi sẽ làm như sau:
- Trích xuất tên công ty và mã dự án riêng biệt từ bảng nhỏ hơn.
- Lặp lại các mã dự án và cố gắng tìm chúng trong bảng lớn hơn.
- Hãy lập bản đồ tất cả số vốn cho dự án đó và lấy nó ra khỏi bàn lớn.
- Lặp lại cho dự án tiếp theo!
Bằng cách này, tôi sẽ giảm tập dữ liệu lớn sau mỗi lần lặp, đồng thời đảm bảo rằng việc ánh xạ diễn ra nhanh chóng do tập dữ liệu được lọc nhỏ hơn ở cấp dự án.
Bây giờ, tôi sẽ lọc cả hai bảng theo mã dự án, như sau:
Với cách tiếp cận này, bảng nhỏ của chúng tôi chỉ có 406 hàng cho dự án ‘ABC’ để chúng tôi lập bản đồ, trong khi bảng lớn có 15.973 hàng để chúng tôi lập bản đồ. Đây là một mức giảm lớn so với bảng thô.
Cấu trúc chương trình
Dự án này sẽ bao gồm cả hàm Python và SQL trên máy chủ SQL; đây là bản phác thảo nhanh về cách chương trình sẽ hoạt động để hiểu rõ hơn về từng bước:
Thực hiện chương trình:
- In mã dự án theo vòng lặp là phiên bản đơn giản nhất của chức năng này:
Rõ ràng là con trỏ SQL sử dụng quá nhiều tài nguyên. Tóm lại, điều này xảy ra vì con trỏ hoạt động ở cấp hàng và đi qua từng hàng để thực hiện thao tác.
Thông tin thêm về lý do tại sao con trỏ trong SQL hoạt động kém hiệu quả và tốt nhất nên tránh chúng có thể tìm thấy ở đây: https://stackoverflow.com/questions/4568464/sql-server-temporary-tables-vs-cursors (câu trả lời 2)
Để tăng hiệu suất, tôi sẽ sử dụng các bảng tạm thời và loại bỏ con trỏ. Đây là hàm kết quả:
Việc này hiện mất khoảng 3 giây cho mỗi dự án để chọn mã dự án và dữ liệu từ bảng ánh xạ lớn, được lọc theo dự án đó.
Với mục đích trình diễn, tôi sẽ chỉ tập trung vào 2 dự án, tuy nhiên tôi sẽ quay lại chạy chức năng trên tất cả các dự án khi thực hiện việc này trong sản xuất.
Hàm cuối cùng mà chúng ta sẽ làm việc trông như thế này:
Chuẩn bị bảng bản đồ
Bước tiếp theo là chuẩn bị dữ liệu cho các hàm ánh xạ và xử lý trước Python, để làm được điều này, chúng ta sẽ cần 2 bộ dữ liệu:
- Dữ liệu được lọc theo mã dự án từ bảng ánh xạ lớn
- Dữ liệu được lọc theo mã dự án từ bảng các công ty nhỏ
Đây là giao diện của hàm được cập nhật với dữ liệu từ 2 bảng đang được chọn:
Quan trọng: các hàm Pythonic trong SQL chỉ nhận 1 bảng đầu vào. Đảm bảo đưa dữ liệu của bạn vào một bàn rộng đơn trước khi đưa nó vào hàm Python trong SQL.
Nhờ chức năng này, chúng tôi có được các dự án, tên công ty và nguồn cho từng dự án.
Bây giờ chúng ta đã sẵn sàng cho Python!
Python trong SQL Server, thông qua sp_execute_external_scriptcho phép bạn chạy mã Python trực tiếp trong SQL Server.
Nó cho phép tích hợp các khả năng của Python vào quy trình công việc SQL bằng việc trao đổi dữ liệu giữa SQL và Python. Trong ví dụ được cung cấp, tập lệnh Python được thực thi, tạo DataFrame gấu trúc từ dữ liệu đầu vào.
Kết quả được trả về dưới dạng một đầu ra duy nhất.
Thật tuyệt vời làm sao!
Có một số điều quan trọng cần lưu ý khi chạy Python trong SQL:
- Chuỗi được xác định bằng dấu ngoặc kép (“), không phải dấu ngoặc đơn (‘). Đảm bảo kiểm tra điều này, đặc biệt nếu bạn đang sử dụng biểu thức chính quy, để tránh mất thời gian truy tìm lỗi
- Chỉ cho phép 1 đầu ra – vì vậy mã Python của bạn sẽ tạo ra 1 bảng ở đầu ra
- Bạn có thể sử dụng câu lệnh in để gỡ lỗi và xem kết quả được in vào tab ‘Thông báo’ trong máy chủ SQL của bạn. Giống như vậy:
Thư viện Python trong SQL
Trong SQL Server, một số thư viện được cài đặt sẵn và có thể truy cập dễ dàng. Để xem danh sách đầy đủ các thư viện này, bạn có thể thực hiện lệnh sau:
Đây là kết quả đầu ra sẽ như thế nào:
Quay lại bảng đã tạo, giờ đây chúng ta có thể khớp tên công ty từ các nguồn khác nhau bằng Python. Quy trình Python của chúng tôi sẽ lấy bảng dài và xuất ra một bảng có các thực thể được ánh xạ. Nó sẽ hiển thị sự trùng khớp mà nó cho là có nhiều khả năng xảy ra nhất từ bảng ánh xạ lớn bên cạnh mỗi bản ghi từ bảng công ty nhỏ.
Để thực hiện việc này, trước tiên hãy thêm hàm Python vào thủ tục SQL của chúng ta. Bước đầu tiên chỉ cần nạp tập dữ liệu vào Python, tôi sẽ thực hiện việc này với tập dữ liệu mẫu và sau đó với dữ liệu của chúng tôi, đây là mã:
Hệ thống này cho phép chúng ta đưa cả hai bảng của mình vào hàm Pythonic làm đầu vào, sau đó nó in cả hai bảng làm đầu ra.
Xử lý trước bằng Python
Để khớp các chuỗi một cách hiệu quả, chúng ta phải tiến hành một số quá trình tiền xử lý trong Python, bao gồm:
- Xóa dấu và các ký tự đặc biệt dành riêng cho ngôn ngữ khác
- Loại bỏ các khoảng trắng
- Xóa dấu câu
Bước đầu tiên sẽ được thực hiện với việc đối chiếu trong SQL, trong khi 2 bước còn lại sẽ có trong bước tiền xử lý của hàm Python.
Đây là chức năng của chúng tôi với quá trình tiền xử lý trông như thế nào:
Kết quả của việc này là 3 cột, một cột có tên công ty viết chữ nhỏ, viết hoa thấp hơn và không có dấu cách, cột thứ hai là cột dự án và cột thứ ba là nguồn.
So khớp chuỗi trong Python
Ở đây chúng ta phải sáng tạo vì chúng ta khá hạn chế về số lượng thư viện mà chúng ta có thể sử dụng. Do đó, trước tiên hãy xác định xem chúng ta muốn đầu ra của mình trông như thế nào.
Chúng tôi muốn khớp dữ liệu từ nguồn 2 với dữ liệu trong nguồn 1. Do đó, đối với mỗi giá trị trong nguồn 2, chúng tôi phải có một loạt các giá trị khớp từ nguồn 1 với điểm số để thể hiện mức độ gần nhau của trận đấu.
Chúng tôi sẽ sử dụng thư viện tích hợp python đầu tiên, để tránh nhu cầu nhập thư viện và do đó đơn giản hóa công việc.
Hợp lý:
- Lặp lại qua từng dự án
- Tạo một bảng chứa số tiền theo nguồn, trong đó nguồn 1 là bảng lớn chứa dữ liệu ánh xạ và 2 là tập dữ liệu ban đầu của công ty
- Chọn dữ liệu từ tập dữ liệu nhỏ vào một mảng
- So sánh từng phần tử trong mảng kết quả với từng phần tử trong khung dữ liệu ánh xạ lớn
- Trả về điểm cho từng thực thể
Mật mã:
Và đây là đầu ra cuối cùng:
Trong bảng này, chúng ta có tên từng công ty, dự án chứa nó và nguồn — cho dù đó là từ bảng ánh xạ lớn hay bảng công ty nhỏ. Điểm ở bên phải biểu thị thước đo tương tự giữa tên công ty từ nguồn 2 và nguồn 1. Điều quan trọng cần lưu ý là công ty4, đến từ nguồn 2, sẽ luôn có điểm trùng khớp từ 1–100%, như đang được thực hiện. phù hợp với chính nó.
Thực thi các tập lệnh Python trong SQL Server thông qua Dịch vụ học máy là một tính năng mạnh mẽ cho phép thực hiện các tác vụ học máy và phân tích trong cơ sở dữ liệu. Sự tích hợp này cho phép truy cập dữ liệu trực tiếp mà không cần di chuyển dữ liệu, tối ưu hóa đáng kể hiệu suất và bảo mật cho các hoạt động sử dụng nhiều dữ liệu.
Tuy nhiên, có những hạn chế cần lưu ý. Môi trường hỗ trợ một đầu vào duy nhất, điều này có thể hạn chế độ phức tạp của các tác vụ có thể được thực hiện trực tiếp trong ngữ cảnh SQL. Ngoài ra, chỉ có một bộ thư viện Python có sẵn có giới hạn, có thể yêu cầu các giải pháp thay thế cho một số loại nhiệm vụ phân tích dữ liệu hoặc máy học nhất định không được thư viện mặc định hỗ trợ. Hơn nữa, người dùng phải điều hướng những điều phức tạp trong môi trường SQL Server, chẳng hạn như khoảng cách phức tạp trong các truy vấn T-SQL bao gồm mã Python, điều này có thể là nguyên nhân gây ra lỗi và nhầm lẫn.
Bất chấp những thách thức này, có rất nhiều ứng dụng trong đó việc thực thi Python trong SQL Server là một lợi thế:
1. Làm sạch và chuyển đổi dữ liệu – Python có thể được sử dụng trực tiếp trong SQL Server để thực hiện các tác vụ tiền xử lý dữ liệu phức tạp, như xử lý dữ liệu bị thiếu hoặc chuẩn hóa các giá trị, trước khi phân tích hoặc báo cáo thêm.
2. Phân tích dự đoán — Triển khai các mô hình máy học Python trực tiếp trong SQL Server cho phép dự đoán theo thời gian thực, chẳng hạn như dự báo tỷ lệ khách hàng rời bỏ hoặc doanh số bán hàng, sử dụng dữ liệu cơ sở dữ liệu trực tiếp.
3. Phân tích nâng cao — Khả năng của Python có thể được tận dụng để thực hiện phân tích thống kê phức tạp và khai thác dữ liệu trực tiếp trên cơ sở dữ liệu, hỗ trợ quá trình ra quyết định mà không có độ trễ khi truyền dữ liệu.
4. Báo cáo và trực quan hóa tự động — Tập lệnh Python có thể tạo trực quan hóa dữ liệu và báo cáo trực tiếp từ dữ liệu SQL Server, cho phép cập nhật và bảng điều khiển tự động.
5. Vận hành các mô hình học máy — Bằng cách tích hợp Python trong SQL Server, các mô hình có thể được cập nhật và quản lý trực tiếp trong môi trường cơ sở dữ liệu, đơn giản hóa quy trình vận hành.
Tóm lại, mặc dù việc thực thi Python trong SQL Server đặt ra một số thách thức nhưng nó cũng mở ra vô số khả năng để tăng cường và đơn giản hóa việc xử lý, phân tích và lập mô hình dự đoán dữ liệu trực tiếp trong môi trường cơ sở dữ liệu.
Tái bút để xem thêm các bài viết của tôi, bạn có thể theo dõi tôi trên LinkedIn tại đây: https://www.linkedin.com/in/sasha-korovkina-5b992019b/
[ad_2]
Source link