Öğretici: Azure HDInsight'ta Interactive Query kullanarak verileri ayıklama, dönüştürme ve yükleme

Bu öğreticide, genel kullanıma açık uçuş verilerinin ham CSV veri dosyasını indirirsiniz. HdInsight küme depolama alanına aktarın ve ardından Azure HDInsight'ta Interactive Query kullanarak verileri dönüştürün. Veriler dönüştürüldükten sonra, Apache Sqoop kullanarak bu verileri Azure SQL Veritabanındaki bir veritabanına yüklersiniz.

Bu öğretici aşağıdaki görevleri kapsar:

• Örnek uçuş verilerini indirme
• Verileri bir HDInsight kümesine yükleme
• Interactive Query kullanarak verileri dönüştürme
• Azure SQL Veritabanında bir veritabanında tablo oluşturma
• Azure SQL Veritabanındaki bir veritabanına veri aktarmak için Sqoop kullanma

Önkoşullar

• HDInsight üzerinde bir Interactive Query kümesi. Bkz. Azure portal kullanarak Apache Hadoop kümeleri oluşturma ve Küme türü için Interactive Query seçme.

• Azure SQL Veritabanı'ndaki bir veritabanı. Veritabanını hedef veri deposu olarak kullanırsınız. Azure SQL Veritabanında veritabanınız yoksa bkz. Azure portal Azure SQL Veritabanında veritabanı oluşturma.

• Bir SSH istemcisi. Daha fazla bilgi için bkz. SSH kullanarak HDInsight'a (Apache Hadoop) bağlanma.

Uçuş verilerini indirme

1. Research and Innovative Technology Administration, Bureau of Transportation Statistics (Araştırma ve Yenilikçi Teknolojiler İdaresi, Ulaşım İstatistikleri Bürosu) sayfasına göz atın.

2. Sayfada tüm alanları temizleyin ve ardından aşağıdaki değerleri seçin:

   Name	Değer
   Yıl Filtresi	2019
   Dönem Filtresi	Ocak
   Alanlar	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. İndir'i seçin. Seçtiğiniz veri alanlarını içeren bir .zip dosyası indirilir.

Verileri bir HDInsight kümesine yükleme

Bir HDInsight kümesiyle ilişkili depolama birimine veri yüklemenin birçok yolu vardır. Bu bölümde, verileri karşıya yüklemek için scp kullanacaksınız. Verileri karşıya yüklemenin diğer yollarını öğrenmek için bkz. Verileri HDInsight'a yükleme.

1. .zip dosyasını HDInsight kümesi baş düğümüne yükleyin. öğesini .zip dosyasının adıyla ve CLUSTERNAME HDInsight kümesinin adıyla değiştirerek FILENAME aşağıdaki komutu düzenleyin. Ardından bir komut istemi açın, çalışma dizininizi dosya konumuna ayarlayın ve komutu girin:

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

   İstenirse devam etmek için evet veya hayır yazın. Siz yazarken metin pencerede görünmez.

2. Karşıya yükleme tamamlandıktan sonra SSH kullanarak kümeye bağlanın. öğesini HDInsight kümesinin adıyla değiştirerek CLUSTERNAME aşağıdaki komutu düzenleyin. Ardından aşağıdaki komutu girin:

   ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
   

3. Bir SSH bağlantısı kurulduktan sonra ortam değişkenini ayarlayın. , , SQL_SERVERNAME, SQL_DATABASE, SQL_USERve SQL_PASWORD değerlerini uygun değerlerle değiştirinFILE_NAME. Ardından şu komutu girin:

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

4. Aşağıdaki komutu girerek .zip dosyasının sıkıştırmasını açın:

   unzip $FILENAME.zip
   

5. HDInsight depolamada bir dizin oluşturun ve ardından aşağıdaki komutu girerek .csv dosyasını dizine kopyalayın:

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

Hive sorgusu kullanarak veri dönüştürme

Bir HDInsight kümesi üzerinde Hive işi çalıştırmanın çok sayıda yolu vardır. Bu bölümde, Bir Hive işi çalıştırmak için Beeline kullanacaksınız. Hive işini çalıştırmanın diğer yöntemleri hakkında bilgi için bkz. HDInsight'ta Apache Hive kullanma.

Hive işinin bir parçası olarak, verileri .csv dosyasından Delays adlı bir Hive tablosuna aktarın.

1. HDInsight kümesi için zaten sahip olduğunuz SSH isteminde aşağıdaki komutu kullanarak flightdelays.hql adlı yeni bir dosya oluşturun ve düzenleyin:

   nano flightdelays.hql
   

2. Bu dosyanın içeriği olarak aşağıdaki metni kullanın:

   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. Dosyayı kaydetmek için Ctrl + X tuşlarına basın, ardından y tuşuna basın ve ardından enter tuşuna basın.

4. Hive’ı başlatmak ve flightdelays.hql dosyasını çalıştırmak için aşağıdaki komutu kullanın:

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

5. flightdelays.hql betiği çalışmayı tamamladıktan sonra aşağıdaki komutu kullanarak etkileşimli bir Beeline oturumu açın:

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

6. jdbc:hive2://localhost:10001/> istemini aldığınızda, içeri aktarılan uçuş gecikme verilerini almak için aşağıdaki sorguyu kullanın:

   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;
   

   Bu sorgu, hava durumundan kaynaklanan gecikmeler yaşayan şehirlerin bir listesini, ortalama gecikme süresi ile birlikte alır ve /tutorials/flightdelays/output konumuna kaydeder. Daha sonra, Sqoop bu konumdaki verileri okur ve Azure SQL Veritabanına aktarır.

7. Beeline’dan çıkmak için isteme !quit girin.

SQL veritabanı tablosu oluşturma

SQL Veritabanına bağlanıp tablo oluşturmanın çok sayıda yolu vardır. Aşağıdaki adımlarda HDInsight kümesinden FreeTDS kullanılır.

1. FreeTDS'yi yüklemek için kümeye açık SSH bağlantısından aşağıdaki komutu kullanın:

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

2. Yükleme tamamlandıktan sonra SQL Veritabanı bağlanmak için aşağıdaki komutu kullanın:

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

   Aşağıdakine benzer bir çıktı alırsınız:

   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> isteminde aşağıdaki satırları girin:

   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 deyimi girildiğinde önceki deyimler değerlendirilir. Bu deyim, kümelenmiş dizine sahip delays adlı bir tablo oluşturur.

   Tablonun oluşturulduğunu doğrulamak için aşağıdaki sorguyu kullanın:

   SELECT * FROM information_schema.tables
   GO
   

   Çıktı aşağıdaki metne benzer:

   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
   databaseName       dbo             delays        BASE TABLE
   

4. Tsql yardımcı programından çıkış yapmak için 1> istemine exit girin.

Apache Sqoop kullanarak verileri SQL Veritabanı dışarı aktarma

Önceki bölümlerde, /tutorials/flightdelays/output konumunda dönüştürülen verileri kopyaladınız. Bu bölümde, verileri /tutorials/flightdelays/output Azure SQL Veritabanında oluşturduğunuz tabloya aktarmak için Sqoop kullanacaksınız.

1. Aşağıdaki komutu girerek Sqoop'un SQL veritabanınızı görebildiğini doğrulayın:

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

   Bu komut, tabloyu daha önce oluşturduğunuz veritabanı da dahil olmak üzere veritabanlarının delays listesini döndürür.

2. Aşağıdaki komutu girerek verileri /tutorials/flightdelays/outputdelays tablosundan tabloya aktarın:

   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, tabloyu içeren delays veritabanına bağlanır ve verileri dizinden /tutorials/flightdelays/output tabloya delays aktarır.

3. sqoop komutu tamamlandıktan sonra, aşağıdaki komutu girerek veritabanına bağlanmak için tsql yardımcı programını kullanın:

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

   Verilerin delays tablosuna aktarıldığını doğrulamak için aşağıdaki ifadeleri kullanın:

   SELECT * FROM delays
   GO
   

   Tabloda verilerin listesini görürsünüz. Tablo, şehir adını ve bu şehre ait ortalama uçuş gecikme süresini içerir.

   Tsql yardımcı programından çıkmak için exit yazın.

Kaynakları temizleme

Öğreticiyi tamamladıktan sonra kümeyi silmek isteyebilirsiniz. HDInsight ile verileriniz Azure Depolama'da depolanır, bu nedenle kullanımda olmayan bir kümeyi güvenle silebilirsiniz. Kullanımda olmasa bile HDInsight kümesi için de ücretlendirilirsiniz. Küme ücretleri depolama ücretinden çok daha fazla olduğundan, kullanımda olmayan kümeleri silmek ekonomik bir anlam ifade eder.

Kümeyi silmek için bkz. Tarayıcınızı, PowerShell'i veya Azure CLI'yı kullanarak HDInsight kümesini silme.

Sonraki adımlar

Bu öğreticide ham bir CSV veri dosyası aldınız, bir HDInsight kümesi depolama alanına aktarıp Azure HDInsight'ta Interactive Query kullanarak verileri dönüştürdüniz. Apache Hive Ambarı Bağlayıcısı hakkında bilgi edinmek için sonraki öğreticiye ilerleyin.

Apache Spark ve Apache Hive'ı Hive Ambar Bağlayıcısı ile tümleştirme