מה זה ETL?
ETL הוא תהליך המחלץ נתונים ממספר מערכות מקור, משנה אותם (באמצעות חישובים, שרשורים וכן הלאה), ואז מכניס אותם למערכת Data Warehouse. ETL ראשי תיבות של Extract, Transform, and Load.
קל להאמין שבניית מחסן נתונים היא פשוטה כמו שליפת נתונים ממקורות רבים והזנתם למסד נתונים של מחסן נתונים. זה רחוק מלהיות המקרה, ונדרש הליך ETL מסובך. תהליך ה-ETL, שהוא מורכב מבחינה טכנית, כולל השתתפות פעילה של מגוון בעלי עניין, לרבות מפתחים, אנליסטים, בודקים ומנהלים בכירים.
כדי לשמר את ערכו ככלי קבלת החלטות, מערכת מחסני הנתונים חייבת להתפתח בסנכרון עם ההתפתחויות העסקיות. ETL הוא תהליך קבוע (יומי, שבועי, חודשי) של מערכת מחסן נתונים שחייבת להיות זריזה, אוטומטית ומתועדת כראוי.
איך ETL עובד?
כאן נלמד כיצד תהליך ה-ETL עובד שלב אחר שלב:
שלב 1) חילוץ
נתונים מופקים ממערכת המקור וממוקמים באזור ההיערכות במהלך החילוץ. אם נדרשות טרנספורמציות כלשהן, הן מבוצעות באזור ההיערכות כך שביצועי מערכת המקור לא ייפגעו. החזרה לאחור תהיה קשה אם נתונים פגומים מועברים ישירות מהמקור למסד הנתונים של מחסן הנתונים. לפני העברת נתונים שחולצו למחסן הנתונים, ניתן לאמת אותם באזור ההיערכות.
מחסני נתונים יכולים לשלב מערכות עם חומרה שונה, מערכות ניהול מסדי נתונים, מערכות הפעלה ופרוטוקולי תקשורת. מחסני נתונים חייבים לשלב מערכות עם DBMS, חומרה, מערכות הפעלה ופרוטוקולי תקשורת שונים. המקורות עשויים לכלול תוכניות מדור קודם כגון מיינפריים, יישומים מותאמים אישית, התקני נקודת מגע כגון כספומטים ומתגי שיחות, קבצי טקסט, גיליונות אלקטרוניים, ERP, נתונים מספקים ושותפים וכן הלאה.
לפיכך, לפני חילוץ נתונים וטעינתם פיזית, נדרשת מפת נתונים לוגית. הקשר בין מקורות ונתוני יעד מוצג במפת נתונים זו.
שלוש שיטות חילוץ נתונים:
- חילוץ חלקי - אם מערכת המקור מתריעה לך כאשר רשומה משתנה, זו הדרך הפשוטה ביותר להשיג את הנתונים.
- חילוץ חלקי (ללא הודעת עדכון) - לא כל המערכות יכולות לספק הודעה כאשר מתרחש עדכון; אבל, הם יכולים לציין לרשומות ששונו ולספק חילוץ של רשומות אלה.
- תמצית מלאה – מערכות מסוימות אינן מסוגלות לקבוע אילו נתונים שונו בכלל. בתרחיש זה, הדרך היחידה להוציא את הנתונים מהמערכת היא לבצע חילוץ מלא. גישה זו דורשת גיבוי של התמצית הקודמת ב-
אותו פורמט בהישג יד כדי לזהות את השינויים שבוצעו.
ללא קשר לשיטה שאומצה, לחילוץ לא אמורה להיות השפעה על הביצועים או זמן התגובה של מערכות המקור. אלו הם מאגרי ייצור בזמן אמת. כל האטה או נעילה עשויה להשפיע על השורה התחתונה של החברה.
שלב 2) טרנספורמציה
הנתונים שאוחזרו משרת המקור הם גולמיים ואינם שמישים במצבם המקורי. כתוצאה מכך, יש לנקות אותו, למפות אותו ולשנות אותו. במציאות, זהו שלב המפתח שבו תהליך ה-ETL מוסיף ערך והופך נתונים על מנת לייצר דוחות BI משמעותיים.
זהו מושג מפתח ETL שבו אתה מיישם אוסף של פונקציות על נתונים שחולצו. מהלך ישיר or לעבור דרך דאטa הוא סוג הנתונים שאינו דורש כל טרנספורמציה.
אתה יכול לבצע פעולות מותאמות אישית על נתונים במהלך שלב השינוי. לדוגמה, נניח שהלקוח רוצה הכנסה מסכום מכירות שאינה קיימת במסד הנתונים. או אם השם הפרטי והמשפחה בטבלה נמצאים בעמודות נפרדות. לפני הטעינה, ניתן לשרשר אותם.
להלן כמה דוגמאות לבעיות שלמות נתונים:
- איותים שונים של אותו אדם, כגון פרשנט, פרשנט וכו'.
- ישנן דרכים רבות לייצוג שם חברה, כגון Google, Google Inc.
- נעשה שימוש בשמות שונים, כמו קליבלנד וקליבלנד.
- ייתכן שמספרי חשבונות מרובים מיוצרים על ידי יישומים שונים עבור אותו לקוח.
- חלק מהקבצים הדרושים לנתונים נותרים ריקים.
שלב 3) טעינה
השלב האחרון בתהליך ה-ETL הוא טעינת נתונים למסד הנתונים של מחסן הנתונים היעד. נפח גדול של נתונים נטען בפרק זמן קצר יחסית במחסן נתונים טיפוסי. כתוצאה מכך, יש לבצע אופטימיזציה של תהליך הטעינה לביצועים.
במקרה של כשל בטעינה, יש להפעיל נהלי שחזור כך שהפעולות יוכלו להתחיל מחדש מנקודת הכשל מבלי לפגוע בשלמות הנתונים. מנהלי מחסני נתונים חייבים לפקח, להמשיך ולעצור עומסים על סמך ביצועי השרת.
סוגי טעינה:
- עומס ראשוני - ממלא את כל
טבלאות מחסן הנתונים - עומס מצטבר - יישום מתמשך
שינויים לפי הצורך על בסיס קבוע - רענון מלא - ניקוי התוכן
של טבלה אחת או יותר וטעינה מחדש של נתונים חדשים
טען אימות
- בדוק שנתוני שדה המפתח אינם חסרים או חסרים.
- יש לבדוק תצוגות דוגמנות המבוססות על טבלאות יעד.
- בחן את הערכים המשולבים3 ואת המדדים המחושבים.
- בדיקות נתונים בטבלאות הממדים וההיסטוריה.
- בדוק את דוחות ה-BI בטבלת העובדות והממדים הטעונות.
הגדרת ETL באמצעות PythonScript
כתוצאה מכך, עליך לבצע Extract Transform Load (ETL) בסיסי ממספר מסדי נתונים למחסן נתונים כדי לבצע צבירת נתונים עבור בינה עסקית. ישנן מספר חבילות ETL זמינות שלדעתך היו מוגזמות עבור מקרה השימוש הבסיסי שלך.
אני אראה לך כיצד לחלץ נתונים מ-MySQL, SQL-server ו-firebird במאמר זה. באמצעות Python 3.6, הפוך את הנתונים וטען אותם לשרת SQL (מחסן נתונים).
קודם כל, עלינו ליצור ספרייה עבור הפרויקט שלנו:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
כדי להגדיר ETL באמצעות Python, תצטרך ליצור את הקבצים הבאים בספריית הפרויקט שלך.
- db_credentials.py: צריך לכלול את כל המידע הדרוש כדי להתחבר לכל מסדי הנתונים. כגון סיסמת מסד נתונים, מספר יציאה וכו'.
- sql_queries.py: כל שאילתות מסד הנתונים הנפוצות לחילוץ וטעינת נתונים בפורמט מחרוזת צריכות להיות זמינות.
- etl.py: התחבר למסד הנתונים וערוך את השאילתות הדרושות על ידי ביצוע כל ההליכים הדרושים.
- main.py: אחראי על ניהול זרימת הפעולות וביצוע הפעולות החיוניות לפי סדר מוגדר.
בחלק זה של sql_queries.py, זה המקום שבו אנו הולכים לאחסן את כל שאילתות ה-sql שלנו לחילוץ ממסדי נתונים מקור ויבוא למסד הנתונים היעד שלנו (מחסן נתונים)
הגדר אישורים ומשתנים של מסד נתונים
ב-variables.py, צור משתנה כדי להקליט את שם הבסיס.
datawarehouse_name = 'שם_מאגר הנתונים_שלך'
הגדר את כל המחרוזות והאישורים של חיבור המקור והיעד של מסד הנתונים שלך ב-db_credentials.py כפי שמוצג להלן. שמור את התצורה כרשימה כדי שנוכל לחזור עליה בכל עת שיידרש דרך מסדי נתונים רבים מאוחר יותר.
מהמשתנים ייבוא datawarehouse_name datawarehouse_name = 'שם_מאגר הנתונים_שלך' # sql-server (דב יעד, מחסן נתונים) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', 'database': '{}'.format(datawarehouse_name), 'user': 'your_db_username ', 'password': 'your_db_password', 'autocommit': נכון, } # sql-server (מקור db) sqlserver_db_config = [ { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'your_sql_server', 'database': 'db1', 'user': 'your_db_username', 'password' : 'your_db_password', 'autocommit': נכון, } ] # mysql (מקור db) mysql_db_config = [ { 'user': 'your_user_1', 'password': 'your_password_1', 'host': 'db_connection_string_1', 'database': 'db_1', }, { 'user': 'your_user_2', 'password' : 'your_password_2', 'host': 'db_connection_string_2', 'database': 'db_2', }, ] # ציפור אש (מקור db) fdb_db_config = [ { 'dsn': "/your/path/to/source.db", 'user': "your_username", 'password': "your_sword", } ]
שאילתות SQL
בחלק זה של sql_queries.py, זה המקום שבו אנו הולכים לאחסן את כל שאילתות ה-sql שלנו לחילוץ ממסדי נתונים מקור ויבוא למסד הנתונים היעד שלנו (מחסן נתונים).
עלינו ליישם תחבירים שונים עבור כל מסד נתונים מכיוון שאנו עובדים עם מספר פלטפורמות נתונים. נוכל לעשות זאת על ידי הפרדת השאילתות לפי סוג מסד הנתונים.
# שאילתות לדוגמה, יהיו שונות בפלטפורמות db שונות firebird_extract = (''' בחר fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table; ''') firebird_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') firebird_extract_2 = (''' בחר fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_2 (column_1, column_2, column_3) VALUES (?, ?, ?) ''') sqlserver_extract = (''' בחר sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 FROM sqlserver_table ''') sqlserver_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') mysql_extract = (''' בחר mysql_column_1, mysql_column_2, mysql_column_3 מ-mysql_table ''') mysql_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') # שאילתות ייצוא class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # צור מופעים עבור המחלקה SqlQuery fbd_query = SqlQuery(firebird_extract, firebird_insert) fbd_query_2 = SqlQuery(firebird_extract_2, firebird_insert_2) sqlserver_query = SqlQuery(sqlserver_extract, sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # אחסן כרשימה לאיטרציה fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
חילוץ עומס טרנספורמציה
כדי להגדיר ETL באמצעות Python עבור מקורות הנתונים שהוזכרו לעיל, תזדקק למודולים הבאים:
# מודולי פיתון ייבוא mysql.connector ייבוא pyodbc ייבוא fdb # משתנים ממשתנים ייבוא datawarehouse_name
אנו יכולים להשתמש בשתי טכניקות בכך: etl() ו-etl_process().
etl_process() הוא ההליך ליצירת חיבור מקור מסד נתונים וקריאה לשיטת etl() המבוססת על פלטפורמת מסד הנתונים.
ובשיטה השנייה שהיא שיטת etl() היא מריץ תחילה את שאילתת החילוץ, ואז מאחסן את נתוני ה-SQL בנתונים המשתנים ומכניס אותם למסד הנתונים הממוקד, שהוא מחסן הנתונים שלנו. ניתן לבצע טרנספורמציה של נתונים על ידי שינוי משתנה הנתונים של הסוג tuple.
def etl(query, source_cnx, target_cnx): # לחלץ נתונים ממקור db source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # טען נתונים ל-db במחסן אם data: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('נתונים נטענו למחסן db') target_cursor.close() else : print('נתונים ריקים') def etl_process(queries, target_cnx, source_db_config, db_platform): # create source db connection if db_platform == 'mysql': source_cnx = mysql.connector.connect(**source_db_config) elif db_platform == 'sqlserver' =connect pyodcnx. (**source_db_config) elif db_platform == 'firebird': source_cnx = fdb.connect(**source_db_config) else: return 'Error! פלטפורמת db לא מזוהה' # לולאה דרך שאילתות sql עבור שאילתה בשאילתות: etl(query, source_cnx, target_cnx) # סגור את חיבור המקור db source_cnx.close()
לחבר הכל ביחד
כעת, בשלב הבא, אנו יכולים לעבור בלולאה על כל האישורים ב-main.py ולהפעיל את ה-etl עבור כל מסדי הנתונים.
לשם כך עלינו לייבא את כל המשתנים והשיטות הנדרשים:
# משתנים מ-db_credentials ייבוא datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config מ-sql_queries ייבוא fbd_queries, sqlserver_queries, mysql_queries מיבוא משתנים etl_procesl * # etl_methods from variables import
הקוד בקובץ זה אחראי על איטרציה על אישורים על מנת להתחבר למסד הנתונים ולבצע את ה-ETL הדרושות באמצעות פעולות Python.
def main(): print('starting etl') # יצירת חיבור למסד הנתונים היעד (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # לולאת אישורים # mysql עבור config ב-mysql_db_config: try: print("loading db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql') למעט חריגה כשגיאה: print("etl for {} has error".format(config['database'])) print ('הודעת שגיאה: {}'.format(error)) המשך # sql-server עבור config ב-sqlserver_db_config: try: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') למעט חריגה כשגיאה: print("etl for {} has error".format(config['database'])) print('error message: {}'.format(error)) continue # firebird for config in fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') למעט חריגה כשגיאה: print("etl for {} has error".format(config ['database'])) print('הודעת שגיאה: {}'.format(error)) continue target_cnx.close() if __name__ == "__main__": main()
בטרמינל שלך, הקלד python main.py וזה עתה יצרת ETL באמצעות סקריפט python טהור.
ETL כלים
ישנם מספר כלים לאחסון נתונים בשוק. הנה כמה מהדוגמאות המפורסמות ביותר:
1. MarkLogic:
MarkLogic היא מערכת אחסון נתונים המשתמשת במגוון של יכולות עסקיות כדי להפוך את שילוב הנתונים לקלה ומהירה יותר. הוא יכול לבצע שאילתות בסוגים רבים של נתונים, כגון מסמכים, קשרים ומטא נתונים.
https://www.marklogic.com/product/getting-started/
2. אורקל:
אורקל הוא מסד הנתונים הפופולרי ביותר בתעשייה. היא מציעה מגוון עצום של פתרונות Data Warehouse הן עבור שירותי מקומיים והן עבור שירותי ענן. זה עוזר לחוויות לקוח טובות יותר על ידי הגברת היעילות התפעולית.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift הוא פתרון אחסון נתונים מבית אמזון. זהו פתרון פשוט וחסכוני לניתוח סוגים שונים של נתונים עם SQL סטנדרטי וכלי בינה עסקית קיימים. זה גם מאפשר ביצוע של שאילתות מורכבות על פטה-בייט של נתונים מובנים.
https://aws.amazon.com/redshift/?nc2=h_m1
סיכום
מאמר זה נתן לך הבנה עמוקה של מה זה ETL, כמו גם הדרכה שלב אחר שלב כיצד להגדיר את ה-ETL שלך ב- Python. זה גם נתן לך רשימה של הכלים הטובים ביותר שרוב הארגונים בימינו משתמשים בהם כדי לבנות את צינורות הנתונים ETL שלהם.
לרוב הארגונים בימינו, לעומת זאת, יש כמות עצומה של נתונים עם מבנה דינמי ביותר. יצירת צינור ETL מאפס עבור נתונים כאלה היא הליך קשה מכיוון שארגונים יצטרכו להשתמש במספר רב של משאבים על מנת ליצור צינור זה ולאחר מכן להבטיח שהוא יוכל לעמוד בקצב נפח הנתונים הגבוה ושינויי סכימה.
על המחבר
פראשנט שארמה
נכון לעכשיו, אני לומדת לתואר ראשון בטכנולוגיה (B.Tech) מהמכון הטכנולוגי של Vellore. אני מאוד נלהב מהתכנות ומהיישומים האמיתיים שלו, כולל פיתוח תוכנה, למידת מכונה, למידה עמוקה ומדעי נתונים.
אני מקווה שתאהבו את המאמר. אם אתה רוצה להתחבר איתי אתה יכול להתחבר ב:
או לכל ספק אחר, אתה יכול לשלוח מייל גם לי
מוצרים מקושרים
- '
- "
- חֶשְׁבּוֹן
- פעיל
- תעשיות
- אמזון בעברית
- ניתוח
- יישומים
- AREA
- מאמר
- אוטומטי
- גיבוי
- חיזוק
- לִבנוֹת
- בִּניָן
- עסקים
- מודיעין עסקי
- שיחה
- בדיקות
- קליבלנד
- ענן
- שירותי ענן
- קוד
- תקשורת
- חברה
- הקשר
- להמשיך
- יוצרים
- אישורים
- נתונים
- שילוב נתונים
- מדע נתונים
- מחסן נתונים
- מחסני נתונים
- מסד נתונים
- מאגרי מידע
- למידה עמוקה
- לפתח
- מפתחים
- צעצועי התפתחות
- התקנים
- מֵמַד
- מסמכים
- נהג
- יְעִילוּת
- וכו '
- הוצאת להורג
- כעובדים בכירים
- חוויות
- הוֹצָאָה
- תמציות
- כשלון
- ראשון
- תזרים
- פוּרמָט
- טרי
- מלא
- מדריך
- חומרה
- כאן
- גָבוֹהַ
- היסטוריה
- איך
- איך
- HTTPS
- לזהות
- פְּגִיעָה
- יבוא
- כולל
- מידע
- השתלבות
- מוֹדִיעִין
- בעיות
- IT
- מפתח
- גָדוֹל
- לִלמוֹד
- למידה
- קו
- לינקדין
- רשימה
- לִטעוֹן
- למידת מכונה
- ניהול
- מַפָּה
- שוק
- מדיה
- הכי פופולארי
- המהלך
- שמות
- הודעה
- מספרים
- המיוחדות שלנו
- פועל
- מערכות הפעלה
- תפעול
- אורקל
- להזמין
- ארגונים
- אחר
- שותפים
- סיסמה
- ביצועים
- פלטפורמה
- פלטפורמות
- פופולרי
- מיוצר
- הפקה
- תכנות
- תוכניות
- פּרוֹיֶקט
- מושך
- פיתון
- חי
- זמן אמת
- מציאות
- רשום
- התאוששות
- מערכות יחסים
- דוחות לדוגמא
- משאבים
- תגובה
- הכנסה
- מדע
- שירותים
- סט
- קצר
- פָּשׁוּט
- So
- תוכנה
- פיתוח תוכנה
- פתרונות
- SQL
- התמחות
- מדינה
- חנות
- חנויות
- מערכת
- מערכות
- יעד
- טק
- טכניקות
- טכנולוגיה
- המקור
- זמן
- טרנספורמציה
- הדרכה
- עדכון
- ערך
- ספקים
- כֶּרֶך
- מחסן
- אחסון
- שבועי
- תיק עבודות
- עובד