ETL چیست؟
ETL فرآیندی است که دادهها را از سیستمهای چند منبع استخراج میکند، آنها را تغییر میدهد (از طریق محاسبات، الحاقات و غیره)، و سپس آن را در سیستم انبار داده قرار میدهد. ETL مخفف Extract، Transform و 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-server (انبار داده) بارگذاری کنید.
اول از همه، ما باید یک دایرکتوری برای پروژه خود ایجاد کنیم:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
برای راه اندازی ETL با استفاده از پایتون، باید فایل های زیر را در فهرست پروژه خود ایجاد کنید.
- db_credentials.py: باید تمام اطلاعات مورد نیاز برای اتصال به تمام پایگاه های داده را داشته باشد. مانند رمز پایگاه داده، شماره پورت و غیره.
- sql_queries.py: همه پرس و جوهای پایگاه داده رایج برای استخراج و بارگذاری داده ها در قالب String باید در دسترس باشند.
- etl.py: به پایگاه داده متصل شوید و با انجام تمام مراحل لازم، پرس و جوهای مورد نیاز را انجام دهید.
- main.py: مسئولیت مدیریت جریان عملیات و اجرای عملیات ضروری به ترتیب مشخص.
در این بخش از sql_queries.py، این مکانی است که ما می خواهیم تمام پرس و جوهای sql خود را برای استخراج از پایگاه داده منبع و وارد کردن به پایگاه داده هدف خود (انبار داده) ذخیره کنیم.
راه اندازی اعتبار پایگاه داده و متغیرها
در variables.py یک متغیر برای ثبت نام thebase ایجاد کنید.
datawarehouse_name = 'your_datawarehouse_name'
تمام رشته ها و اعتبارنامه های اتصال پایگاه داده منبع و مقصد خود را در db_credentials.py مطابق شکل زیر پیکربندی کنید. پیکربندی را به عنوان یک لیست ذخیره کنید تا بتوانیم بعداً هر زمان که لازم شد آن را در بسیاری از پایگاه های داده تکرار کنیم.
از متغیرها import datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (db target, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server_house': 'dataware's پایگاه داده': '{}'.format(datawarehouse_name)، 'user': 'your_db_username'، 'password': 'your_db_password'، 'autocommit': True، } # sql-server (db source) sqlserver_db_config = [{onnectionT' ': 'yes'، 'driver': '{SQL Server}'، 'server': 'your_sql_server'، 'پایگاه داده': 'db1'، 'user': 'your_db_username'، 'password': 'your_db_password'، ' autocommit': True, } ] # 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', }, ] # firebird (منبع db) fdb_db_config = [ { 'dsn' : "/your/path/to/source.db", 'user': "your_username", 'password': "your_password"، } ]
پرس و جوهای SQL
در این بخش از sql_queries.py، این مکانی است که ما می خواهیم تمام پرس و جوهای sql خود را برای استخراج از پایگاه داده منبع و وارد کردن به پایگاه داده هدف خود (انبار داده) ذخیره کنیم.
ما باید برای هر پایگاه داده سینتکس های مختلفی را پیاده سازی کنیم زیرا با پلتفرم های داده های متعدد کار می کنیم. ما می توانیم این کار را با جداسازی کوئری ها بر اساس نوع پایگاه داده انجام دهیم.
# پرس و جوهای مثال، در پلتفرم های مختلف db متفاوت خواهد بود firebird_extract = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table; ''') firebird_insert = (''' INSERT INTO جدول (column_1, column_VA, column) ?, ?) ''') firebird_extract_2 = (''' SELECT fbd_column_3, fbd_column_2, fbd_column_1 FROM fbd_table_2; ''') firebird_insert_3 = (''' INSERT INTO table_2 (column_2, column, VA, ?L? ) ''') sqlserver_extract = (''' SELECT sqlserver_column_2, sqlserver_column_1, sqlserver_column_2 FROM sqlserver_table ''') sqlserver_insert = (''' INSERT INTO, LU__,3 ستون VA,1 ستون ? '''' ) mysql_extract = (''' SELECT mysql_column_2, mysql_column_3, mysql_column_1 FROM mysql_table ''') mysql_insert = (''' INSERT INTO جدول (ستون_2، ستون_3، ستون_1)، صادرات #VA 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_query,firebird_query =firebird_query,firebird_query,firbird_query,firebird_insert,firbird_query) _insert_2) sqlserver_query = SqlQuery(sqlserver_extract , sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # ذخیره بهعنوان فهرستی برای تکرار fbd_queries = [fbdquery, fbd_query_3] sqlserver_queries = [sqlserver_query_query] mys
استخراج بار تبدیل
برای راهاندازی ETL با استفاده از پایتون برای منابع دادهای که در بالا ذکر شد، به ماژولهای زیر نیاز دارید:
# ماژول پایتون import mysql.connector import pyodbc import fdb # متغیرها از متغیرها import datawarehouse_name
ما می توانیم در این مورد از دو تکنیک استفاده کنیم: etl() و etl_process().
etl_process() رویه ای برای ایجاد یک اتصال منبع پایگاه داده و فراخوانی متد etl() بر اساس پلت فرم پایگاه داده است.
و در روش دوم که متد etl() است، ابتدا کوئری استخراج را اجرا می کند، سپس داده های SQL را در داده های متغیر ذخیره می کند و در پایگاه داده هدف قرار می دهد که انبار داده ما است. تبدیل داده ها ممکن است با تغییر متغیر داده نوع تاپل انجام شود.
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): # برقراری اتصال منبع db if db_platform == 'mysql': source_cnx = mysql.connector.connect(**source_db_db_config =platif) 'sqlserver': source_cnx = pyodbc.connect(**source_db_config) elif db_platform == 'firebird': source_cnx = fdb.connect(**source_db_config) else: بازگشت 'خطا! پلتفرم 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 from variables importlcess
کد موجود در این فایل وظیفه تکرار روی اعتبارنامه ها به منظور اتصال به پایگاه داده و اجرای ETL لازم با استفاده از عملیات پایتون را بر عهده دارد.
def main(): print('starting etl') # ایجاد اتصال برای پایگاه داده هدف (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # حلقه از طریق اعتبارنامه # mysql برای پیکربندی در mysql_db_config: try: print("loading db: " + config['پایگاه داده']) etl_process(mysql_queries, target_cnx, config, 'mysql') به جز Exception as error: print("etl for {} has error".format(config['database'])) print ('پیام خطا: {}'.format(خطا)) ادامه # sql-server برای پیکربندی در sqlserver_db_config: try: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') به جز Exception به عنوان خطا: print("etl for {} has error".format(config['database'])) print('error message: {}'.format(error)) continue # firebird برای پیکربندی در fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') به جز استثنا به عنوان خطا: print("etl for {} has error".format(config ['پایگاه داده'])) print('پیام خطا: {}'.format(error)) continue target_cnx.close() if __name__ == "__main__": main()
در ترمینال خود، python main.py را تایپ کنید و به تازگی یک ETL با استفاده از یک اسکریپت پایتون خالص ایجاد کرده اید.
ابزارهای ETL
چندین ابزار ذخیره سازی داده در بازار وجود دارد. در اینجا برخی از معروف ترین نمونه ها آورده شده است:
1. MarkLogic:
MarkLogic یک سیستم ذخیرهسازی داده است که از مجموعهای از قابلیتهای تجاری استفاده میکند تا یکپارچهسازی دادهها را آسانتر و سریعتر کند. میتواند انواع مختلفی از دادهها، مانند اسناد، روابط، و ابردادهها را پرس و جو کند.
https://www.marklogic.com/product/getting-started/
2. اوراکل:
اوراکل محبوب ترین پایگاه داده صنعت است. طیف گسترده ای از راه حل های Data Warehouse را برای خدمات داخلی و ابری ارائه می دهد. با افزایش کارایی عملیاتی، به تجربه بهتر مشتری کمک می کند.
https://www.oracle.com/index.html
3. آمازون RedShift:
Redshift یک راه حل انبار داده از آمازون است. این یک راه حل ساده و مقرون به صرفه برای تجزیه و تحلیل انواع داده ها با SQL استاندارد و ابزارهای هوش تجاری موجود است. همچنین اجرای پرس و جوهای پیچیده را بر روی پتابایت داده های ساختاریافته امکان پذیر می کند.
https://aws.amazon.com/redshift/?nc2=h_m1
نتیجه
این مقاله به شما درک عمیقی از چیستی ETL و همچنین یک آموزش گام به گام در مورد نحوه تنظیم ETL خود در پایتون داد. همچنین فهرستی از بهترین ابزارهایی را که امروزه بیشتر سازمانها برای ایجاد خطوط لوله داده ETL خود استفاده میکنند، در اختیار شما قرار میدهد.
از سوی دیگر، اکثر سازمان های امروزی دارای حجم عظیمی از داده ها با ساختار بسیار پویا هستند. ایجاد خط لوله ETL از ابتدا برای چنین دادههایی یک روش سخت است زیرا سازمانها باید از تعداد زیادی منابع برای ایجاد این خط لوله استفاده کنند و سپس اطمینان حاصل کنند که میتواند با حجم بالای داده و تغییرات طرحواره همراه باشد.
درباره نویسنده
پرشانت شارما
در حال حاضر، من لیسانس فناوری (B.Tech) خود را از موسسه فناوری Vellore دنبال می کنم. من در مورد برنامه نویسی و کاربردهای واقعی آن از جمله توسعه نرم افزار، یادگیری ماشین، یادگیری عمیق و علم داده بسیار مشتاق هستم.
امیدوارم مقاله را دوست داشته باشید. اگر می خواهید با من ارتباط برقرار کنید، می توانید از طریق:
یا برای هر شک دیگری، می توانید یک ایمیل بفرست به من نیز
مربوط
- '
- "
- حساب
- فعال
- معرفی
- آمازون
- علم تجزیه و تحلیل
- برنامه های کاربردی
- محدوده
- مقاله
- خودکار
- پشتیبان گیری
- تقویت
- ساختن
- بنا
- کسب و کار
- هوش تجاری
- صدا
- چک
- Cleveland
- ابر
- خدمات ابر
- رمز
- ارتباط
- شرکت
- ارتباط
- ادامه دادن
- ایجاد
- مجوزها و اعتبارات
- داده ها
- یکپارچه سازی داده ها
- علم اطلاعات
- انبار داده
- انبارهای داده
- پایگاه داده
- پایگاه های داده
- یادگیری عمیق
- توسعه
- توسعه دهندگان
- پروژه
- دستگاه ها
- بعد
- اسناد و مدارک
- راننده
- بهره وری
- و غیره
- اعدام
- مدیران
- تجارب
- استخراج
- عصاره ها
- شکست
- نام خانوادگی
- جریان
- قالب
- تازه
- کامل
- گوگل
- راهنمایی
- سخت افزار
- اینجا کلیک نمایید
- زیاد
- تاریخ
- چگونه
- چگونه
- HTTPS
- شناسایی
- تأثیر
- واردات
- از جمله
- اطلاعات
- ادغام
- اطلاعات
- مسائل
- IT
- کلید
- بزرگ
- یاد گرفتن
- یادگیری
- لاین
- لینک
- فهرست
- بار
- فراگیری ماشین
- مدیریت
- نقشه
- بازار
- رسانه ها
- محبوبترین
- حرکت
- نام
- اخطار
- تعداد
- پیشنهادات
- عملیاتی
- سیستم های عامل
- عملیات
- وحی
- سفارش
- سازمان های
- دیگر
- شرکای
- کلمه عبور
- کارایی
- سکو
- سیستم عامل
- محبوب
- ساخته
- تولید
- برنامه نويسي
- برنامه ها
- پروژه
- کشیدن
- پــایتــون
- خام
- زمان واقعی
- واقعیت
- سوابق
- بهبود
- روابط
- گزارش ها
- منابع
- پاسخ
- درامد
- علم
- خدمات
- تنظیم
- کوتاه
- ساده
- So
- نرم افزار
- توسعه نرم افزار
- مزایا
- SQL
- صحنه
- دولت
- opbevare
- پرده
- سیستم
- سیستم های
- هدف
- فن آوری
- تکنیک
- پیشرفته
- منبع
- زمان
- دگرگونی
- آموزش
- بروزرسانی
- ارزش
- فروشندگان
- حجم
- انبار کالا
- انبارداری
- هفتگی
- مهاجرت کاری
- با این نسخهها کار