ETL là gì?
ETL là một quy trình trích xuất dữ liệu từ nhiều hệ thống nguồn, thay đổi dữ liệu (thông qua tính toán, nối, v.v.), sau đó đưa dữ liệu vào hệ thống Kho dữ liệu. ETL là viết tắt của Trích xuất, Chuyển đổi và Tải.
Thật dễ dàng để tin rằng việc xây dựng Kho dữ liệu cũng đơn giản như lấy dữ liệu từ nhiều nguồn và đưa dữ liệu đó vào cơ sở dữ liệu Kho dữ liệu. Đây là trường hợp khác xa và cần có một thủ tục ETL phức tạp. Quy trình ETL phức tạp về mặt kỹ thuật bao gồm sự tham gia tích cực của nhiều bên liên quan, bao gồm nhà phát triển, nhà phân tích, người thử nghiệm và giám đốc điều hành cấp cao.
Để duy trì giá trị của nó như một công cụ ra quyết định, hệ thống kho dữ liệu phải phát triển đồng bộ với sự phát triển của doanh nghiệp. ETL là một quy trình thường xuyên (hàng ngày, hàng tuần, hàng tháng) của một hệ thống kho dữ liệu phải nhanh nhẹn, tự động hóa và được ghi lại đúng cách.
ETL hoạt động như thế nào?
Ở đây chúng ta sẽ tìm hiểu cách thức hoạt động của quy trình ETL từng bước:
Bước 1) Khai thác
Dữ liệu được trích xuất từ hệ thống nguồn và được đặt trong khu vực tổ chức trong quá trình trích xuất. Nếu cần có bất kỳ chuyển đổi nào, chúng sẽ được thực hiện trong khu vực tổ chức để hiệu suất của hệ thống nguồn không bị tổn hại. Việc khôi phục sẽ khó khăn nếu dữ liệu bị hỏng được chuyển trực tiếp từ nguồn vào cơ sở dữ liệu Kho dữ liệu. Trước khi di chuyển dữ liệu đã trích xuất vào Kho dữ liệu, dữ liệu đó có thể được xác thực trong khu vực tổ chức.
Kho dữ liệu có thể kết hợp các hệ thống với phần cứng, hệ thống quản lý cơ sở dữ liệu, hệ điều hành và giao thức truyền thông khác nhau. Kho dữ liệu phải kết hợp các hệ thống với DBMS, phần cứng, hệ điều hành và giao thức truyền thông khác nhau. Các nguồn có thể bao gồm các chương trình cũ như máy tính lớn, ứng dụng tùy chỉnh, thiết bị điểm tiếp xúc như máy ATM và chuyển mạch cuộc gọi, tệp văn bản, bảng tính, ERP, dữ liệu từ nhà cung cấp và đối tác, v.v.
Do đó, trước khi trích xuất dữ liệu và tải dữ liệu về mặt vật lý, cần có một bản đồ dữ liệu logic. Kết nối giữa nguồn và dữ liệu đích được hiển thị trong bản đồ dữ liệu này.
Ba phương pháp trích xuất dữ liệu:
- Trích xuất một phần – Nếu hệ thống nguồn thông báo cho bạn khi một bản ghi bị sửa đổi, thì đó là cách đơn giản nhất để lấy dữ liệu.
- Trích xuất một phần (không có thông báo cập nhật) – Không phải tất cả các hệ thống đều có thể gửi thông báo khi có bản cập nhật; nhưng, chúng có thể chỉ ra các bản ghi đã được thay đổi và cung cấp trích xuất các bản ghi đó.
- Trích xuất đầy đủ – Một số hệ thống không có khả năng xác định dữ liệu nào đã bị thay đổi. Trong trường hợp này, cách duy nhất để lấy dữ liệu ra khỏi hệ thống là thực hiện trích xuất toàn bộ. Cách tiếp cận này yêu cầu phải có một bản sao lưu của trích xuất trước đó trong
cùng một định dạng có sẵn để xác định những thay đổi đã được thực hiện.
Bất kể phương pháp được áp dụng là gì, việc trích xuất không được ảnh hưởng đến hiệu suất hoặc thời gian phản hồi của hệ thống nguồn. Đây là những cơ sở dữ liệu sản xuất thời gian thực. Bất kỳ sự chậm lại hoặc khóa nào cũng có thể ảnh hưởng đến lợi nhuận của công ty.
Bước 2) Chuyển đổi
Dữ liệu được truy xuất từ máy chủ nguồn là dữ liệu thô và không sử dụng được ở trạng thái ban đầu. Kết quả là, nó phải được làm sạch, lập bản đồ và biến đổi. Trên thực tế, đây là bước quan trọng trong đó quy trình ETL bổ sung giá trị và chuyển đổi dữ liệu để tạo ra các báo cáo BI có ý nghĩa.
Đây là một khái niệm ETL chính trong đó bạn áp dụng một tập hợp các hàm cho dữ liệu được trích xuất. di chuyển trực tiếp or đi qua data là loại dữ liệu không yêu cầu bất kỳ chuyển đổi nào.
Bạn có thể thực hiện các thao tác tùy chỉnh trên dữ liệu trong bước chuyển đổi. Ví dụ: giả sử khách hàng muốn có tổng doanh thu bán hàng không tồn tại trong cơ sở dữ liệu. hoặc nếu tên và họ trong một bảng nằm trong các cột riêng biệt. Trước khi tải, chúng có thể được nối.
Sau đây là một số ví dụ về các vấn đề về tính toàn vẹn của dữ liệu:
- Các cách viết khác nhau của cùng một cá nhân, chẳng hạn như Prashant, Parshant, v.v.
- Có nhiều cách để thể hiện tên công ty, chẳng hạn như Google, Google Inc.
- Nhiều tên khác nhau, chẳng hạn như Cleaveland và Cleveland, được sử dụng.
- Có thể nhiều số tài khoản được tạo bởi các ứng dụng khác nhau cho cùng một khách hàng.
- Một số tệp dữ liệu cần thiết được để trống.
Bước 3) Đang tải
Giai đoạn cuối cùng trong quy trình ETL là tải dữ liệu vào cơ sở dữ liệu kho dữ liệu đích. Một khối lượng lớn dữ liệu được tải trong một khoảng thời gian tương đối ngắn trong một kho dữ liệu điển hình. Do đó, quá trình tải phải được tối ưu hóa cho hiệu suất.
Trong trường hợp xảy ra lỗi tải, các quy trình khôi phục nên được thực hiện để các hoạt động có thể khởi động lại từ điểm lỗi mà không ảnh hưởng đến tính toàn vẹn của dữ liệu. Quản trị viên Kho dữ liệu phải theo dõi, tiếp tục và dừng tải dựa trên hiệu suất của máy chủ.
Các loại tải:
- Tải ban đầu - lấp đầy tất cả
bảng Kho dữ liệu - tải gia tăng - triển khai liên tục
sửa đổi khi cần thiết một cách thường xuyên - Làm mới hoàn toàn - xóa nội dung
của một hoặc nhiều bảng và tải lại chúng bằng dữ liệu mới
tải xác minh
- Kiểm tra xem dữ liệu trường chính có bị thiếu hoặc không.
- Các chế độ xem mô hình hóa dựa trên các bảng mục tiêu nên được kiểm tra.
- Kiểm tra các giá trị kết hợp3 và các biện pháp tính toán.
- Kiểm tra dữ liệu trong bảng thứ nguyên và lịch sử.
- Kiểm tra các báo cáo BI trên bảng dữ kiện và thứ nguyên đã tải.
Thiết lập ETL bằng PythonScript
Do đó, bạn phải thực thi Tải chuyển đổi trích xuất cơ bản (ETL) từ một số cơ sở dữ liệu sang kho dữ liệu để thực hiện tổng hợp dữ liệu cho nghiệp vụ thông minh. Có một số gói ETL có sẵn mà bạn cho là quá mức đối với trường hợp sử dụng cơ bản của mình.
Tôi sẽ chỉ cho bạn cách trích xuất dữ liệu từ MySQL, SQL-server và firebird trong bài viết này. Sử dụng Python 3.6, chuyển đổi dữ liệu và tải nó vào máy chủ SQL (kho dữ liệu).
Trước hết, chúng ta phải tạo một thư mục cho dự án của mình:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Để thiết lập ETL bằng Python, bạn cần tạo các tệp sau trong thư mục dự án của mình.
- db_credentials.py: Nên có tất cả các thông tin cần thiết để kết nối với tất cả các cơ sở dữ liệu. chẳng hạn như Mật khẩu cơ sở dữ liệu, Số cổng, v.v.
- sql_queries.py: Tất cả các truy vấn cơ sở dữ liệu thường được sử dụng để trích xuất và tải dữ liệu ở định dạng Chuỗi phải có sẵn.
- etl.py: Kết nối với cơ sở dữ liệu và thực hiện các truy vấn cần thiết bằng cách thực hiện tất cả các thủ tục cần thiết.
- chính.py: Chịu trách nhiệm quản lý luồng hoạt động và thực hiện các hoạt động thiết yếu theo một thứ tự cụ thể.
Trong phần này của sql_queries.py, đây là nơi chúng ta sẽ lưu trữ tất cả các truy vấn sql để trích xuất từ cơ sở dữ liệu nguồn và nhập vào cơ sở dữ liệu đích (kho dữ liệu)
Thiết lập thông tin xác thực và biến cơ sở dữ liệu
Trong biến.py, tạo một biến để ghi tên của cơ sở.
datawarehouse_name = 'your_datawarehouse_name'
Định cấu hình tất cả các chuỗi và thông tin đăng nhập cơ sở dữ liệu nguồn và đích của bạn trong db_credentials.py như được hiển thị bên dưới. Lưu cấu hình dưới dạng danh sách để chúng tôi có thể lặp lại nó bất cứ khi nào cần thông qua nhiều cơ sở dữ liệu sau này.
từ các biến nhập datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (db đích, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', ' cơ sở dữ liệu': '{}'.format(datawarehouse_name), 'user': 'your_db_username', 'password': 'your_db_password', 'autocommit': True, } # sql-server (db nguồn) sqlserver_db_config = [ { 'Trusted_Connection ': 'yes', 'driver': '{SQL Server}', 'server': 'your_sql_server', 'database': 'db1', 'user': 'your_db_username', 'password': 'your_db_password', ' autocommit': True, } ] # mysql (db nguồn) mysql_db_config = [ { 'user': 'your_user_1', 'password': 'your_password_1', 'host': 'db_connection_string_1', 'database': 'db_1', } , { 'người dùng': 'your_user_2', 'password': 'your_password_2', 'host': 'db_connection_string_2', 'database': 'db_2', }, ] # firebird (db nguồn) fdb_db_config = [ { 'dsn' : "/your/path/to/source.db", 'user': "your_username", 'password': "your_password", } ]
Truy vấn SQL
Trong phần này của sql_queries.py, đây là nơi chúng tôi sẽ lưu trữ tất cả các truy vấn sql để trích xuất từ cơ sở dữ liệu nguồn và nhập vào cơ sở dữ liệu đích (kho dữ liệu) của chúng tôi.
Chúng tôi phải triển khai nhiều cú pháp khác nhau cho mọi cơ sở dữ liệu vì chúng tôi đang làm việc với nhiều nền tảng dữ liệu. Chúng tôi có thể làm điều này bằng cách tách các truy vấn dựa trên loại cơ sở dữ liệu.
# truy vấn ví dụ, sẽ khác nhau trên các nền tảng db khác nhau firebird_extract = (''' CHỌN fbd_column_1, fbd_column_2, fbd_column_3 TỪ fbd_table; ''') firebird_insert = (''' CHÈN VÀO bảng (cột_1, cột_2, cột_3) GIÁ TRỊ (?, ?, ?) ''') firebird_extract_2 = (''' CHỌN fbd_column_1, fbd_column_2, fbd_column_3 TỪ fbd_table_2; ''') firebird_insert_2 = (''' CHÈN VÀO bảng_2 (cột_1, cột_2, cột_3) GIÁ TRỊ (?, ?, ? ) ''') sqlserver_extract = (''' CHỌN sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 TỪ sqlserver_table ''') sqlserver_insert = (''' CHÈN VÀO bảng (cột_1, cột_2, cột_3) GIÁ TRỊ (?, ?, ?) ''' ) mysql_extract = (''' CHỌN mysql_column_1, mysql_column_2, mysql_column_3 TỪ mysql_table ''') mysql_insert = (''' CHÈN VÀO bảng (cột_1, cột_2, cột_3) GIÁ TRỊ (?, ?, ?) ''') # xuất truy vấn lớp SqlQuery: def __init__(self, extract_query, load_query) Truy vấn (sqlserver_extract , sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # lưu dưới dạng danh sách để lặp lại fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
Trích xuất tải chuyển đổi
Để thiết lập ETL bằng Python cho các nguồn dữ liệu nêu trên, bạn sẽ cần các mô-đun sau:
# mô-đun python nhập mysql.connector nhập pyodbc nhập fdb # biến từ biến nhập datawarehouse_name
Chúng ta có thể sử dụng hai kỹ thuật trong việc này: etl() và etl_ process().
etl_ process() là quy trình thiết lập kết nối nguồn cơ sở dữ liệu và gọi phương thức etl() dựa trên nền tảng cơ sở dữ liệu.
Và trong phương thức thứ hai là phương thức etl(), nó chạy truy vấn trích xuất trước, sau đó lưu trữ dữ liệu SQL trong dữ liệu biến và chèn nó vào cơ sở dữ liệu được nhắm mục tiêu, là kho dữ liệu của chúng tôi. Việc chuyển đổi dữ liệu có thể được thực hiện bằng cách thay đổi biến dữ liệu của kiểu tuple.
def etl(query, source_cnx, target_cnx): # trích xuất dữ liệu từ nguồn db source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # tải dữ liệu vào kho db if dữ liệu: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('dữ liệu được tải vào kho db') target_cursor.close() other : print('dữ liệu trống') def etl_process(queries, target_cnx, source_db_config, db_platform): # thiết lập kết nối db nguồn if db_platform == 'mysql': source_cnx = mysql.connector.connect(**source_db_config) elif db_platform == 'sqlserver': source_cnx = pyodbc.connect(**source_db_config) elif db_platform == 'firebird': source_cnx = fdb.connect(**source_db_config) khác: trả về 'Lỗi! nền tảng db không được nhận dạng' # lặp qua các truy vấn sql cho truy vấn trong các truy vấn: etl(query, source_cnx, target_cnx) # đóng kết nối db nguồn source_cnx.close()
Kết hợp mọi thứ lại với nhau
Bây giờ, trong bước tiếp theo, Chúng tôi có thể lặp lại tất cả thông tin đăng nhập trong main.py và thực thi etl cho tất cả các cơ sở dữ liệu.
Để làm được điều đó, chúng ta phải Nhập tất cả các biến và phương thức cần thiết:
# biến từ db_credentials nhập datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config từ sql_queries nhập fbd_queries, sqlserver_queries, mysql_queries từ biến nhập * # phương thức từ etl nhập etl_ process
Mã trong tệp này chịu trách nhiệm lặp lại thông tin đăng nhập để kết nối với cơ sở dữ liệu và thực hiện các thao tác ETL bằng Python cần thiết.
def main(): print('starting etl') # thiết lập kết nối cho cơ sở dữ liệu đích (máy chủ sql) target_cnx = pyodbc.connect(**datawarehouse_db_config) # lặp thông qua thông tin đăng nhập # mysql cho cấu hình trong mysql_db_config: thử: print("đang tải db: " + config['database']) etl_ process(mysql_queries, target_cnx, config, 'mysql') ngoại trừ Ngoại lệ là lỗi: print("etl cho {} có lỗi".format(config['database'])) print ('thông báo lỗi: {}'.format(error)) continue # sql-server for config in sqlserver_db_config: try: print("loading db: " + config['database']) etl_ process(sqlserver_queries, target_cnx, config, ' sqlserver') ngoại trừ Ngoại lệ là lỗi: print("etl for {} has error".format(config['database'])) print('error message: {}'.format(error)) continue # firebird for config in fbd_db_config: thử: print("đang tải db: " + config['database']) etl_ process(fbd_queries, target_cnx, config, 'firebird')ngoại trừ Ngoại lệ là lỗi: print("etl cho {} có lỗi".format(config ['cơ sở dữ liệu'])) print('thông báo lỗi: {}'.format(error)) continue target_cnx.close() if __name__ == "__main__": main()
Trong thiết bị đầu cuối của bạn, hãy nhập python main.py và bạn vừa tạo một ETL bằng tập lệnh python thuần túy.
Công cụ ETL
Có một số công cụ Kho dữ liệu trên thị trường. Dưới đây là một số ví dụ nổi tiếng nhất:
1. MarkLogic:
MarkLogic là một hệ thống kho dữ liệu sử dụng một loạt các khả năng kinh doanh để giúp việc tích hợp dữ liệu trở nên dễ dàng và nhanh chóng hơn. Nó có thể truy vấn nhiều loại dữ liệu, chẳng hạn như tài liệu, mối quan hệ và siêu dữ liệu.
https://www.marklogic.com/product/getting-started/
2. Nhà tiên tri:
Oracle là cơ sở dữ liệu phổ biến nhất trong ngành. Nó cung cấp rất nhiều giải pháp Kho dữ liệu cho cả dịch vụ tại chỗ và đám mây. Nó giúp mang lại trải nghiệm tốt hơn cho khách hàng bằng cách tăng hiệu quả hoạt động.
https://www.oracle.com/index.html
3. Dịch chuyển đỏ của Amazon:
Redshift là một giải pháp lưu trữ dữ liệu từ Amazon. Đó là một giải pháp đơn giản và hiệu quả về chi phí để phân tích các loại dữ liệu khác nhau bằng SQL tiêu chuẩn và các công cụ kinh doanh thông minh hiện có. Nó cũng cho phép thực hiện các truy vấn phức tạp trên hàng petabyte dữ liệu có cấu trúc.
https://aws.amazon.com/redshift/?nc2=h_m1
Kết luận
Bài viết này giúp bạn hiểu sâu hơn về ETL là gì, cũng như hướng dẫn từng bước về cách thiết lập ETL của bạn trong Python. Nó cũng cung cấp cho bạn một danh sách các công cụ tốt nhất mà hầu hết các tổ chức hiện nay sử dụng để xây dựng đường dẫn dữ liệu ETL của họ.
Mặt khác, hầu hết các tổ chức ngày nay đều có một lượng dữ liệu khổng lồ với cấu trúc rất năng động. Tạo một quy trình ETL từ đầu cho dữ liệu đó là một quy trình khó khăn vì các tổ chức sẽ phải sử dụng một lượng lớn tài nguyên để tạo quy trình này và sau đó đảm bảo rằng nó có thể theo kịp khối lượng dữ liệu cao và các thay đổi của Lược đồ.
Giới thiệu về Tác giả
Prashant Sharma
Hiện tại, tôi đang theo học bằng Cử nhân Công nghệ (B.Tech) của Học viện Công nghệ Vellore. Tôi rất hào hứng với lập trình và các ứng dụng thực tế của nó bao gồm phát triển phần mềm, học máy, Học sâu và khoa học dữ liệu.
Tôi hy vọng bạn thích bài viết. Nếu bạn muốn kết nối với tôi thì bạn có thể kết nối trên:
hoặc đối với bất kỳ nghi ngờ nào khác, bạn có thể gửi thư với tôi cũng vậy
Sản phẩm liên quan
- '
- "
- Tài khoản
- hoạt động
- Tất cả
- đàn bà gan dạ
- phân tích
- các ứng dụng
- KHU VỰC
- bài viết
- Tự động
- sao lưu
- thúc đẩy
- xây dựng
- Xây dựng
- kinh doanh
- kinh doanh thông minh
- cuộc gọi
- Séc
- cleveland
- đám mây
- dịch vụ điện toán đám mây
- mã
- Giao tiếp
- công ty
- liên quan
- tiếp tục
- Tạo
- Credentials
- dữ liệu
- tích hợp dữ liệu
- khoa học dữ liệu
- kho dữ liệu
- Kho dữ liệu
- Cơ sở dữ liệu
- cơ sở dữ liệu
- học kĩ càng
- phát triển
- phát triển
- Phát triển
- Thiết bị (Devices)
- kích thước
- tài liệu
- trình điều khiển
- hiệu quả
- vv
- thực hiện
- giám đốc điều hành
- Kinh nghiệm
- khai thác
- Chất chiết xuất
- Không
- Tên
- dòng chảy
- định dạng
- tươi
- Full
- hướng dẫn
- phần cứng
- tại đây
- Cao
- lịch sử
- Độ đáng tin của
- Hướng dẫn
- HTTPS
- xác định
- Va chạm
- nhập khẩu
- Bao gồm
- thông tin
- hội nhập
- Sự thông minh
- các vấn đề
- IT
- Key
- lớn
- LEARN
- học tập
- Dòng
- Danh sách
- tải
- học máy
- quản lý
- bản đồ
- thị trường
- Phương tiện truyền thông
- Phổ biến nhất
- di chuyển
- tên
- thông báo
- số
- Cung cấp
- hoạt động
- các hệ điều hành
- Hoạt động
- oracle
- gọi món
- tổ chức
- Nền tảng khác
- Đối tác
- Mật khẩu
- hiệu suất
- nền tảng
- Nền tảng
- Phổ biến
- Sản xuất
- Sản lượng
- Lập trình
- Khóa Học
- dự án
- kéo
- Python
- Nguyên
- thời gian thực
- Thực tế
- hồ sơ
- phục hồi
- Mối quan hệ
- Báo cáo
- Thông tin
- phản ứng
- doanh thu
- Khoa học
- DỊCH VỤ
- định
- ngắn
- Đơn giản
- So
- Phần mềm
- phát triển phần mềm
- Giải pháp
- SQL
- Traineeship
- Tiểu bang
- hàng
- cửa hàng
- hệ thống
- hệ thống
- Mục tiêu
- công nghệ cao
- kỹ thuật
- Công nghệ
- Nguồn
- thời gian
- Chuyển đổi
- hướng dẫn
- Cập nhật
- giá trị
- nhà cung cấp
- khối lượng
- Kho
- Kho bãi
- hàng tuần
- Công việc
- công trinh