البرنامج التعليمي: استخراج وتحويل وتحميل البيانات باستخدام الاستعلام التفاعلي في Azure HDInsight

في هذا البرنامج التعليمي، يمكن تحميل ملف البيانات غير المنسقة للقيم المفصولة بفواصل من بيانات إصدار التقييم. استيراده إلى تخزين كتلة HDInsight ثم تحويل البيانات باستخدام الاستعلام التفاعلي في Azure HDInsight. بمجرد تحويل البيانات، يمكن تحميل تلك البيانات إلى قاعدة بيانات في Azure SQL Database باستخدام Apache Sqoop .

يغطي هذا البرنامج التعليمي المهام التالية:

• تنزيل نموذج بيانات الرحلة
• رفع البيانات إلى كتلة HDInsight
• تحويل البيانات باستخدام الاستعلام التفاعلي
• إنشاء جدول في قاعدة بيانات في Azure SQL Database
• استخدام Sqoop لتصدير البيانات إلى قاعدة بيانات في Azure SQL Database

المتطلبات الأساسية

• كتلة استعلام التفاعلي على HDInsight. يرجى الرجوع إلى إنشاء كُتل Apache Hadoop باستخدام مدخل Azure ثم تحديدالاستعلام التفاعلي لـ نوع الكتلة.

• قاعدة بيانات في Azure SQL Database. يمكن استخدام قاعدة البيانات كمخزن بيانات وجهة. في حالة عدم امتلاك قاعدة بيانات في Azure SQL Database، يرجى الرجوع إلى إنشاء قاعدة بيانات في Azure SQL Database في مدخل Azure .

• عميل SSH. لمزيد من المعلومات، يرجى الرجوع إلى الاتصال بـ HDInsight (Apache Hadoop) باستخدام SSH .

تحميل بيانات الرحلة

1. تصفح للوصول إلى إدارة الأبحاث والتكنولوجيا المبتكرة، مكتب إحصاءات النقل .

2. في الصفحة، إلغاء تحديد كافة الحقول، ثم تحديد القيم التالية:

   الاسم	القيمة
   سنة التصفية	2019
   فترة التصفية	يناير
   الحقول	Year, FlightDate, Reporting_Airline, DOT_ID_Reporting_Airline, Flight_Number_Reporting_Airline, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay.

3. اختر ⁧⁩تنزيل⁧⁩. يتم تنزيل ملف .zip مع حقول البيانات التي حددتها.

رفع البيانات إلى كتلة HDInsight

هناك العديد من الطرق لتحميل البيانات إلى التخزين المقترن بكتلة HDInsight. في هذا القسم، يمكن استخدام scp لتحميل البيانات. للتعرف على طرق أخرى لتحميل البيانات، يرجى الرجوع إلى تحميل البيانات إلى HDInsight .

1. رفع الملف .zip إلى عقدة رأس نظام كتلة HDInsight. تحرير الأمر أدناه عن طريق استبدال FILENAME باسم ملف .zip و CLUSTERNAME باسم كتلة HDInsight. ثم افتح موجه الأوامر، وقم بتعيين دليل العمل الخاص بك إلى موقع الملف، ثم أدخل الأمر:

   scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
   

   تحديد نعم أو لا للمتابعة إذا طلب ذلك. النص غير مرئي في الإطار أثناء الكتابة.

2. بعد انتهاء التحميل، الاتصال بالكتلة باستخدام SSH. تحرير الأمر أدناه عن طريق استبدال CLUSTERNAME باسم كتلة HDInsight. ثم إدخال الأمر التالي:

   ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
   

3. إعداد متغير البيئة بمجرد تأسيس اتصال SSH. استبدال FILE_NAME و SQL_SERVERNAME و SQL_DATABASE و SQL_USER و SQL_PASWORD بالقيم المناسبة. ثم إدخال الأمر:

   export FILENAME=FILE_NAME
   export SQLSERVERNAME=SQL_SERVERNAME
   export DATABASE=SQL_DATABASE
   export SQLUSER=SQL_USER
   export SQLPASWORD='SQL_PASWORD'
   

4. فك ضغط ملف .zip بإدخال الأمر أدناه:

   unzip $FILENAME.zip
   

5. +إنشاء دليل على تخزين HDInsight ثم نسخ ملف .csv إلى الدليل بإدخال الأمر أدناه:

   hdfs dfs -mkdir -p /tutorials/flightdelays/data
   hdfs dfs -put $FILENAME.csv /tutorials/flightdelays/data/
   

تحويل البيانات باستخدام استعلام الخلية

هناك العديد من الطرق لتشغيل وظيفة الخلية على كتلة HDInsight. في هذا القسم، يجب استخدام الخط المباشر لتشغيل وظيفة الخلية. للحصول على معلومات حول الطرق الأخرى لتشغيل مهمة الخلية، يرجى الرجوع إلى استخدام Apache Hive على HDInsight .

كجزء من مهمة الخلية، يلزم استيراد البيانات من ملف .csv إلى جدول خلية يسمى التأخيرات.

1. من موجه SSH الموجود بالفعل كتلة HDInsight، استخدام الأمر التالي لإنشاء وتحرير ملف جديد باسم flightdelays.hql:

   nano flightdelays.hql
   

2. استخدام النص التالي كمحتويات هذا الملف:

   DROP TABLE delays_raw;
   -- Creates an external table over the csv file
   CREATE EXTERNAL TABLE delays_raw (
       YEAR string,
       FL_DATE string,
       UNIQUE_CARRIER string,
       CARRIER string,
       FL_NUM string,
       ORIGIN_AIRPORT_ID string,
       ORIGIN string,
       ORIGIN_CITY_NAME string,
       ORIGIN_CITY_NAME_TEMP string,
       ORIGIN_STATE_ABR string,
       DEST_AIRPORT_ID string,
       DEST string,
       DEST_CITY_NAME string,
       DEST_CITY_NAME_TEMP string,
       DEST_STATE_ABR string,
       DEP_DELAY_NEW float,
       ARR_DELAY_NEW float,
       CARRIER_DELAY float,
       WEATHER_DELAY float,
       NAS_DELAY float,
       SECURITY_DELAY float,
       LATE_AIRCRAFT_DELAY float)
   -- The following lines describe the format and location of the file
   ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n'
   STORED AS TEXTFILE
   LOCATION '/tutorials/flightdelays/data';
   
   -- Drop the delays table if it exists
   DROP TABLE delays;
   -- Create the delays table and populate it with data
   -- pulled in from the CSV file (via the external table defined previously)
   CREATE TABLE delays AS
   SELECT YEAR AS year,
       FL_DATE AS flight_date,
       substring(UNIQUE_CARRIER, 2, length(UNIQUE_CARRIER) -1) AS unique_carrier,
       substring(CARRIER, 2, length(CARRIER) -1) AS carrier,
       substring(FL_NUM, 2, length(FL_NUM) -1) AS flight_num,
       ORIGIN_AIRPORT_ID AS origin_airport_id,
       substring(ORIGIN, 2, length(ORIGIN) -1) AS origin_airport_code,
       substring(ORIGIN_CITY_NAME, 2) AS origin_city_name,
       substring(ORIGIN_STATE_ABR, 2, length(ORIGIN_STATE_ABR) -1)  AS origin_state_abr,
       DEST_AIRPORT_ID AS dest_airport_id,
       substring(DEST, 2, length(DEST) -1) AS dest_airport_code,
       substring(DEST_CITY_NAME,2) AS dest_city_name,
       substring(DEST_STATE_ABR, 2, length(DEST_STATE_ABR) -1) AS dest_state_abr,
       DEP_DELAY_NEW AS dep_delay_new,
       ARR_DELAY_NEW AS arr_delay_new,
       CARRIER_DELAY AS carrier_delay,
       WEATHER_DELAY AS weather_delay,
       NAS_DELAY AS nas_delay,
       SECURITY_DELAY AS security_delay,
       LATE_AIRCRAFT_DELAY AS late_aircraft_delay
   FROM delays_raw;
   

3. لحفظ الملف، الضغط على Ctrl + X، ثم y، ثم إدخال.

4. لبدء خلية وتشغيل ملف flightdelays.hql، يجب استخدام الأمر التالي:

   beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql
   

5. بعد انتهاء تشغيل البرنامج النصي flightdelays.hql، استخدم الأمر التالي لفتح جلسة Beeline تفاعلية:

   beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
   

6. عندما تتلقى مطالبة jdbc:hive2://localhost:10001/>، استخدم الاستعلام التالي لاسترداد البيانات من بيانات تأخير الرحلة المستوردة:

   INSERT OVERWRITE DIRECTORY '/tutorials/flightdelays/output'
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
   SELECT regexp_replace(origin_city_name, '''', ''),
       avg(weather_delay)
   FROM delays
   WHERE weather_delay IS NOT NULL
   GROUP BY origin_city_name;
   

   يسترد هذا الاستعلام قائمة بالمدن التي تعرضت لتأخيرات بسبب الطقس، جنبًا إلى جنب مع متوسط وقت التأخير، ويحفظها في /tutorials/flightdelays/output. لاحقاً، يقرأ Sqoop البيانات من هذا الموقع ويصدرها إلى قاعدة بيانات azure SQL.

7. للخروج من Beeline، إدخال !quit عند المطالبة.

إنشاء جدول قاعدة بيانات SQL

هناك العديد من الطرق للاتصال بقاعدة بيانات SQL وإنشاء جدول. تستخدم الخطوات التالية FreeTDS من كتلة HDInsight.

1. لتثبيت FreeTDS، استخدم الأمر التالي من اتصال SSH المفتوح إلى الكتلة:

   sudo apt-get --assume-yes install freetds-dev freetds-bin
   

2. بعد انتهاء التثبيت، استخدم الأمر التالي للاتصال بقاعدة بيانات SQL:

   TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
   

   تظهر مخرجات مشابهة للنص التالي:

   locale is "en_US.UTF-8"
   locale charset is "UTF-8"
   using default charset "UTF-8"
   Default database being set to <yourdatabase>
   1>
   

3. في موجه 1>، إدخال الأسطر التالية:

   CREATE TABLE [dbo].[delays](
   [origin_city_name] [nvarchar](50) NOT NULL,
   [weather_delay] float,
   CONSTRAINT [PK_delays] PRIMARY KEY CLUSTERED
   ([origin_city_name] ASC))
   GO
   

   عند إدخال العبارة GO، تُقيم العبارات السابقة. تقوم هذه العبارة بإنشاء جدول يسمى التأخيرات، باستخدام فهرس عنقودي.

   استخدام الاستعلام التالي للتحقق من إنشاء الجدول:

   SELECT * FROM information_schema.tables
   GO
   

   الإخراج مشابه للنص التالي:

   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
   databaseName       dbo             delays        BASE TABLE
   

4. إدخال exit في موجه 1> للخروج من الأداة المساعدة tsql.

تصدير البيانات إلى قاعدة بيانات SQL باستخدام Apache Sqoop

في الأقسام السابقة، نُسخت البيانات المحولة في /tutorials/flightdelays/output. في هذا القسم، استخدام Sqoop لتصدير البيانات من /tutorials/flightdelays/output إلى الجدول الذي أُنشئ في قاعدة بيانات Azure SQL.

1. التحقق من أن Sqoop يمكن أن ترى قاعدة البيانات SQL الخاصة بك عن طريق إدخال الأمر أدناه:

   sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
   

   يقوم هذا الأمر بإرجاع قائمة بقواعد البيانات، بما في ذلك قاعدة البيانات التي أُنشئ فيها جدول delays سابقًا.

2. تصدير البيانات من /tutorials/flightdelays/output إلى جدول delays عن طريق إدخال الأمر أدناه:

   sqoop export --connect "jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433;database=$DATABASE" --username $SQLUSER --password $SQLPASWORD --table 'delays' --export-dir '/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1
   

   يتصل Sqoop بقاعدة البيانات التي تحتوي على الجدول delays، ويصدر البيانات من الدليل /tutorials/flightdelays/output إلى الجدول delays.

3. بعد انتهاء الأمر sqoop استخدم الأداة المساعدة tsql للاتصال بقاعدة البيانات عن طريق إدخال الأمر أدناه:

   TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
   

   استخدام العبارات التالية للتحقق من أن البيانات صُدرت إلى جدول التأخيرات:

   SELECT * FROM delays
   GO
   

   يجب أن تظهر قائمة البيانات في الجدول. يشمل الجدول اسم المدينة ومتوسط وقت تأخير الرحلة لتلك المدينة.

   اكتب exit للخروج من الأداة المساعدة tsql.

تنظيف الموارد

بعد إكمال البرنامج التعليمي، قد يلزم حذف الكتلة. باستخدام HDInsight، يتم تخزين بياناتك الخاصة في Azure Storage بحيث يمكنك حذف نظام المجموعة بأمان عندما لا يكون قيد الاستخدام. كما يتم تحصيل رسوم منك مقابل مجموعة HDInsight، حتى عندما لا تكون قيد الاستخدام. نظرًا لأن رسوم مجموعة أكبر بعدة مرات من رسوم التخزين، فمن المنطقي من الناحية الاقتصادية حذف المجموعات عندما لا تكون قيد الاستخدام.

لحذف المجموعة يرجى الرجوع إلى ⁧⁩ حذف مجموعة HDInsight باستخدام المستعرض، أو PowerShell، أو Azure CLI ⁧⁩.

الخطوات التالية

في هذا البرنامج التعليمي، ينبغي أخذ ملف بيانات CSV غير المنسق، واستيراده إلى تخزين كتلة HDInsight، ومن ثم تحويل البيانات باستخدام الاستعلام التفاعلي في Azure HDInsight. الانتقال إلى البرنامج التعليمي التالي لمعرفة المزيد عن موصل مستودع Apache Hive.

دمج Apache Spark وApache Hive مع Hive Warehouse Connector