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

Makale
05/13/2021
Okumak için 6 dakika

Bu öğreticide, genel kullanıma açık uçuş verilerinin ham CSV veri dosyasını indirirler. Bunu HDInsight küme depolamasına içeri aktarın ve ardından Azure HDInsight 'ta etkileşimli sorgu kullanarak verileri dönüştürün. Veriler dönüştürüldükten sonra, bu verileri Apache Sqoopkullanarak Azure SQL veritabanı 'nda 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
Etkileşimli sorgu 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 etkileşimli bir sorgu kümesi. Bkz. Azure Portal kullanarak Apache Hadoop kümeleri oluşturma ve küme türü için etkileşimli sorgu seçme.
Azure SQL veritabanı 'nda bir veritabanı. Veritabanını hedef veri deposu olarak kullanırsınız. Azure SQL veritabanında bir 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

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.

Sayfasında, 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`.

İndir'i seçin. Seçtiğiniz veri alanlarını içeren bir .zip dosyası alırsınız.

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.

. Zip dosyasını HDInsight kümesi baş düğümüne yükleyin. FILENAME. Zip dosyasının adıyla ve HDInsight kümesinin adıyla değiştirerek aşağıdaki komutu düzenleyin CLUSTERNAME . Sonra bir komut istemi açın, çalışma dizininizi dosya konumu olarak ayarlayın ve ardından komutunu girin.
```
scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
```
İstenirse devam etmek için Evet veya Hayır girin. Metin, yazarken pencerede görünmez.
Karşıya yükleme tamamlandıktan sonra SSH kullanarak kümeye bağlanın. Aşağıdaki komutu, CLUSTERNAME HDInsight kümesinin adıyla değiştirerek düzenleyin. Ardından aşağıdaki komutu girin:
```
ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
```
Bir SSH bağlantısı kurulduktan sonra ortam değişkenini ayarlayın. ,,, FILE_NAME SQL_SERVERNAME SQL_DATABASE SQL_USER Ve SQL_PASWORD değerlerini uygun değerlerle değiştirin. Sonra şu komutu girin:
```
export FILENAME=FILE_NAME
export SQLSERVERNAME=SQL_SERVERNAME
export DATABASE=SQL_DATABASE
export SQLUSER=SQL_USER
export SQLPASWORD='SQL_PASWORD'
```
Aşağıdaki komutu girerek. zip dosyasını ayıklayın:
```
unzip $FILENAME.zip
```
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şini çalıştırmak için Beeline kullanırsınız. Hive işi çalıştırmanın diğer yöntemleri hakkında daha fazla bilgi için bkz. HDInsight üzerinde Apache Hive kullanma.

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

HDInsight kümesi için zaten sahip olduğunuz SSH isteminden, flightgecikmeleri. HQL adlı yeni bir dosya oluşturmak ve düzenlemek için aşağıdaki komutu kullanın:
```
nano flightdelays.hql
```

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;

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

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
```

Yükleme tamamlandıktan sonra, SQL veritabanına 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>

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 ifade, bir kümelenmiş dizine sahip gecikmeler 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

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

Apache Sqoop kullanarak verileri SQL veritabanı 'na 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 'yi kullanırsınız.

Sqoop 'nin SQL veritabanınızı aşağıdaki komutu girerek görebildiğini doğrulayın:
```
sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
```
Bu komut, daha önce tabloyu oluşturduğunuz veritabanı da dahil olmak üzere veritabanlarının bir listesini döndürür delays .

/tutorials/flightdelays/output delays Aşağıdaki komutu girerek verileri 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 veritabanına bağlanır delays ve /tutorials/flightdelays/output dizinden verileri tabloya aktarır delays .

Sqoop komutu bittikten 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 Storage 'da depolanır, bu sayede bir kümeyi kullanımda olmadığında güvenle silebilirsiniz. Ayrıca, kullanımda olmasa bile bir HDInsight kümesi için de ücretlendirilirsiniz. Kümenin ücretleri depolama ücretinden çok daha fazla olduğundan, kullanımda olmadıkları zaman kümeleri silmek ekonomik bir anlam sağlar.

Bir 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üme depolamasına içeri aktardınız ve sonra Azure HDInsight 'ta etkileşimli sorgu kullanarak verileri dönüştürürsünüz. Apache Hive ambar Bağlayıcısı hakkında bilgi edinmek için sonraki öğreticiye ilerleyin.

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