Tutorial: Mengekstrak, mengubah, dan memuat data dengan menggunakan Interactive Query di Azure HDInsight

Artikel
05/10/2024

Dalam tutorial ini, Anda mengunduh file data CSV mentah dari data penerbangan yang tersedia untuk umum. Impor ke penyimpanan kluster HDInsight, lalu ubah data menggunakan Interactive Query di Azure HDInsight. Setelah data diubah, Anda memuat data tersebut ke dalam database di Azure SQL Database menggunakan Apache Sqoop.

Tutorial ini mencakup tugas-tugas berikut:

Mengunduh sampel data penerbangan
Mengunggah data ke kluster HDInsight
Mengubah data menggunakan Interactive Query
Membuat tabel dalam database di Azure SQL Database
Menggunakan Sqoop untuk mengekspor data ke database di Azure SQL Database

Prasyarat

Kluster Interactive Query di HDInsight. Lihat Membuat kluster Apache Hadoop menggunakan portal Microsoft Azure dan pilih Interactive Query untuk Jenis kluster.
Database di Azure SQL Database. Anda menggunakan database sebagai penyimpanan data tujuan. Jika Anda tidak memiliki database di Azure SQL Database, lihat Membuat database di Azure SQL Database di portal Microsoft Azure.
Klien SSH. Untuk informasi selengkapnya, lihat Menyambungkan ke HDInsight (Apache Hadoop) menggunakan SSH.

Mengunduh data penerbangan

Telusuri Administrasi Penelitian dan Teknologi Inovatif, Biro Statistik Transportasi.

Di halaman tersebut, bersihkan semua bidang, lalu pilih nilai berikut ini:

Nama	Nilai
Tahun Filter	2019
Periode Filter	Januari
Bidang	`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`.

Pilih Unduh. File .zip diunduh dengan bidang data yang Anda pilih.

Mengunggah data ke kluster HDInsight

Ada banyak cara untuk mengunggah data ke penyimpanan yang terkait dengan kluster HDInsight. Di bagian ini, Anda menggunakan scp untuk mengunggah data. Untuk mempelajari cara lain mengunggah data, lihat Mengunggah data ke HDInsight.

Unggah file .zip ke head node kluster HDInsight. Edit perintah di bawah ini dengan mengganti FILENAME dengan nama file .zip, dan CLUSTERNAME dengan nama kluster HDInsight. Kemudian buka perintah, atur direktori kerja Anda ke lokasi file, lalu masukkan perintah:
```
scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
```
Masukkan ya atau tidak untuk melanjutkan jika diminta. Teks tidak terlihat di jendela saat Anda mengetik.
Setelah pengunggahan selesai, sambungkan ke kluster dengan menggunakan SSH. Edit perintah di bawah ini dengan mengganti CLUSTERNAME dengan nama kluster HDInsight. Masukkan perintah berikut:
```
ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
```
Siapkan variabel lingkungan setelah koneksi SSH dibuat. Ganti FILE_NAME, SQL_SERVERNAME, SQL_DATABASE, SQL_USER, dan SQL_PASWORD dengan nilai yang sesuai. Kemudian masukkan perintah:
```
export FILENAME=FILE_NAME
export SQLSERVERNAME=SQL_SERVERNAME
export DATABASE=SQL_DATABASE
export SQLUSER=SQL_USER
export SQLPASWORD='SQL_PASWORD'
```
Unzip file .zip dengan memasukkan perintah di bawah ini:
```
unzip $FILENAME.zip
```
Buat direktori pada penyimpanan HDInsight, lalu salin file .csv ke direktori dengan memasukkan perintah di bawah ini:
```
hdfs dfs -mkdir -p /tutorials/flightdelays/data
hdfs dfs -put $FILENAME.csv /tutorials/flightdelays/data/
```

Mengubah data menggunakan kueri Apache Hive

Ada banyak cara untuk menjalankan pekerjaan Apache Hive pada kluster HDInsight. Di bagian ini, Anda menggunakan Beeline untuk menjalankan pekerjaan Apache Hive. Untuk informasi tentang metode lain untuk menjalankan pekerjaan Apache Hive, lihat Menggunakan Apache Hive di HDInsight.

Sebagai bagian dari pekerjaan Apache Hive, Anda mengimpor data dari file .csv ke dalam tabel Apache Hive bernama Penundaan.

Dari perintah SSH yang sudah Anda miliki untuk kluster HDInsight, gunakan perintah berikut untuk membuat dan mengedit file baru bernama flightdelays.hql:
```
nano flightdelays.hql
```

Gunakan teks berikut sebagai konten file:

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;

Untuk menyimpan file, tekan Ctrl + X, lalu y, lalu masukkan.
Untuk memulai Apache Hive dan menjalankan file flightdelays.hql, gunakan perintah berikut:
```
beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql
```
Setelah skrip flightdelays.hql selesai dijalankan, gunakan perintah berikut untuk membuka sesi Beeline interaktif:
```
beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
```
Saat Anda menerima perintah jdbc:hive2://localhost:10001/>, gunakan kueri berikut untuk mengambil data dari data keterlambatan flight yang diimpor:
```
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;
```
Kueri ini mengambil daftar kota yang mengalami keterlambatan cuaca, bersama dengan waktu keterlambatan rata-rata, dan menyimpannya ke /tutorials/flightdelays/output. Kemudian, Sqoop membaca data dari lokasi ini dan mengekspornya ke Azure SQL Database.
Untuk keluar dari Beeline, masukkan !quit di perintah.

Membuat tabel database SQL

Ada banyak cara untuk menyambungkan ke SQL Database dan membuat tabel. Langkah-langkah berikut menggunakan FreeTDS dari kluster HDInsight.

Untuk menginstal FreeTDS, gunakan perintah berikut dari koneksi SSH terbuka ke kluster:
```
sudo apt-get --assume-yes install freetds-dev freetds-bin
```

Setelah penginstalan selesai, gunakan perintah berikut untuk menyambungkan ke SQL Database:

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

Anda akan menerima output yang mirip dengan teks berikut:

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

Di perintah 1>, masukkan baris berikut:

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

Ketika pernyataan GO dimasukkan, pernyataan sebelumnya dievaluasi. Pernyataan ini membuat tabel bernama penundaan, dengan indeks berkluster.

Gunakan kueri berikut untuk memverifikasi bahwa tabel telah dibuat:

SELECT * FROM information_schema.tables
GO

Output yang dihasilkan mirip dengan teks berikut:

TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
databaseName       dbo             delays        BASE TABLE

Masukkan exit pada prompt 1> untuk keluar dari utilitas tsql.

Mengekspor data ke SQL Database menggunakan Apache Sqoop

Di bagian sebelumnya, Anda menyalin data yang diubah di /tutorials/flightdelays/output. Di bagian ini, Anda menggunakan Sqoop untuk mengekspor data dari /tutorials/flightdelays/output ke tabel yang Anda buat di Azure SQL Database.

Verifikasi bahwa Sqoop dapat melihat database SQL Anda dengan memasukkan perintah di bawah ini:
```
sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
```
Perintah ini mengembalikan daftar database, termasuk database tempat Anda membuat tabel delays sebelumnya.

Ekspor data dari /tutorials/flightdelays/output ke tabel delays dengan memasukkan perintah di bawah ini:

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 tersambung ke database yang berisi tabel delays, dan mengekspor data dari direktori /tutorials/flightdelays/output ke tabel delays.

Setelah perintah sqoop selesai, gunakan utilitas tsql untuk menyambungkan ke database dengan memasukkan perintah di bawah ini:
```
TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
```
Gunakan pernyataan berikut untuk memverifikasi bahwa data telah diekspor sebelumnya ke tabel penundaan:
```
SELECT * FROM delays
GO
```
Anda akan melihat daftar data dalam tabel. Tabel ini mencakup nama kota dan waktu keterlambatan penerbangan rata-rata untuk kota tersebut.

Ketik exit untuk keluar dari utilitas tsql.

Membersihkan sumber daya

Setelah Anda menyelesaikan tutorial, Anda dapat menghapus kluster. Dengan HDInsight, data Anda disimpan di Azure Storage, sehingga Anda dapat menghapus kluster dengan aman saat tidak digunakan. Anda juga dikenakan biaya untuk kluster HDInsight, bahkan saat tidak digunakan. Karena biaya untuk kluster berkali-kali lebih banyak daripada biaya untuk penyimpanan, masuk akal secara ekonomis untuk menghapus kluster saat tidak digunakan.

Untuk menghapus kluster, lihat Hapus kluster HDInsight menggunakan browser, PowerShell, atau Azure CLI Anda.

Langkah berikutnya

Dalam tutorial ini, Anda mengambil file data CSV mentah, mengimpornya menjadi penyimpanan kluster HDInsight, dan kemudian mengubah data menggunakan Interactive Query di Azure HDInsight. Lanjutkan ke tutorial berikutnya untuk mempelajari tentang Apache Hive Warehouse Connector.

Mengintegrasikan Apache Spark dan Apache Hive dengan Hive Warehouse Connector

Bagikan melalui