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

Artikel
03/09/2023

Dalam tutorial ini, Anda melakukan operasi ETL: mengekstrak, mengubah, dan memuat data. Anda mengambil file data CSV mentah, mengimpornya ke kluster Azure HDInsight, mengubahnya dengan Apache Hive, dan memuatnya ke Azure SQL Database dengan Apache Sqoop.

Dalam tutorial ini, Anda akan mempelajari cara:

Mengekstrak dan mengunggah data ke kluster HDInsight.
Mengubah data dengan menggunakan Apache Hive.
Memuat data ke Azure SQL Database dengan menggunakan Sqoop.

Jika Anda tidak memiliki langganan Azure, buat akun gratis sebelum memulai.

Prasyarat

Akun penyimpanan yang memiliki namespace hierarkis (Azure Data Lake Storage Gen2) yang dikonfigurasi untuk HDInsight

Menggunakan Azure Data Lake Storage Gen2 dengan kluster Azure HDInsight.
Kluster Hadoop berbasis Linux di HDInsight

Lihat Mulai cepat: Mulai menggunakan Apache Hadoop dan Apache Hive di Azure HDInsight menggunakan portal Microsoft Azure.
Azure SQL Database

Anda menggunakan Azure SQL Database sebagai penyimpanan data tujuan. Jika Anda tidak memiliki database di Database SQL, lihat Membuat database di Azure SQL Database di portal Microsoft Azure.
Azure CLI

Jika Anda belum menginstal Azure CLI, lihat Menginstal Azure CLI.
Klien Secure Shell (SSH)

Untuk informasi selengkapnya, lihat Menyambungkan ke HDInsight (Hadoop) dengan menggunakan SSH.

Unduh, ekstrak lalu unggah data

Di bagian ini, Anda mengunduh sampel data penerbangan. Kemudian, Anda mengunggah data tersebut ke kluster HDInsight Anda lalu menyalin data tersebut ke akun Data Lake Storage Gen2 Anda.

Unduh file On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip file. File ini berisi data penerbangan.
Buka prompt perintah dan gunakan perintah Secure Copy (Scp) berikut untuk mengunggah file .zip ke node kepala kluster HDInsight:
```
scp On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net:
```
- <ssh-user-name> Ganti tempat penampung dengan nama pengguna SSH untuk kluster HDInsight.
- Ganti placeholder <cluster-name> dengan nama kluster HDInsight.
Jika Anda menggunakan kata sandi untuk mengautentikasi nama pengguna SSH, Anda akan dimintai kata sandi.

Jika Anda menggunakan kunci umum, Anda mungkin perlu menggunakan parameter -i dan menentukan jalur ke kunci privat yang cocok. Contohnya:scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:
Setelah pengunggahan selesai, sambungkan ke kluster dengan menggunakan SSH. Pada prompt perintah, masukkan perintah berikut:
```
ssh <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net
```
Gunakan perintah berikut untuk membuka zip .zip file:
```
unzip <file-name>.zip
```
Perintah mengekstrak file .csv.
Gunakan perintah berikut untuk membuat kontainer Data Lake Storage Gen2.
```
hadoop fs -D "fs.azure.createRemoteFileSystemDuringInitialization=true" -ls abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/
```
Ganti <container-name> placeholder dengan nama yang ingin Anda berikan kepada kontainer Anda.

Ganti placeholder <storage-account-name> dengan nama akun penyimpanan.

Gunakan perintah berikut untuk membuat direktori.

hdfs dfs -mkdir -p abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data

Gunakan perintah berikut untuk menyalin .csv file ke direktori:

hdfs dfs -put "On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2016_1.csv" abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data/

Gunakan tanda kutip di sekitar nama file jika nama file berisi spasi atau karakter khusus.

Mengubah data

Di bagian ini, Anda menggunakan Beeline untuk menjalankan pekerjaan Apache Hive.

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

Ubah teks berikut dengan mengganti <container-name> tempat penampung dan <storage-account-name> dengan kontainer dan nama akun penyimpanan Anda. Kemudian salin dan tempel teks ke konsol nano dengan menggunakan menekan tombol SHIFT bersama dengan tombol pilih kanan mouse.

  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 'abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/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
  LOCATION 'abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/processed'
  AS
  SELECT YEAR AS year,
     FL_DATE AS FlightDate, 
     substring(UNIQUE_CARRIER, 2, length(UNIQUE_CARRIER) -1) AS IATA_CODE_Reporting_Airline,
     substring(CARRIER, 2, length(CARRIER) -1) AS Reporting_Airline, 
     substring(FL_NUM, 2, length(FL_NUM) -1) AS Flight_Number_Reporting_Airline,
     ORIGIN_AIRPORT_ID AS OriginAirportID,
     substring(ORIGIN, 2, length(ORIGIN) -1) AS OriginAirportSeqID,
     substring(ORIGIN_CITY_NAME, 2) AS OriginCityName,
     substring(ORIGIN_STATE_ABR, 2, length(ORIGIN_STATE_ABR) -1)  AS OriginState,
     DEST_AIRPORT_ID AS DestAirportID,
     substring(DEST, 2, length(DEST) -1) AS DestAirportSeqID,
     substring(DEST_CITY_NAME,2) AS DestCityName,
     substring(DEST_STATE_ABR, 2, length(DEST_STATE_ABR) -1) AS DestState,
     DEP_DELAY_NEW AS DepDelay,
     ARR_DELAY_NEW AS ArrDelay,
     CARRIER_DELAY AS CarrierDelay,
     WEATHER_DELAY AS WeatherDelay,
     NAS_DELAY AS NASDelay,
     SECURITY_DELAY AS SecurityDelay,
     LATE_AIRCRAFT_DELAY AS LateAircraftDelay
  FROM delays_raw;

Simpan file dengan mengetik CTRL+X lalu ketik Y saat diminta.
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(OriginCityName, '''', ''),
  avg(WeatherDelay)
FROM delays
WHERE WeatherDelay IS NOT NULL
GROUP BY OriginCityName;
```
Kueri ini mengambil daftar kota yang mengalami keterlambatan cuaca, bersama dengan waktu keterlambatan rata-rata, dan menyimpannya ke abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/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

Anda memerlukan nama server dari SQL Database untuk operasi ini. Selesaikan langkah-langkah ini untuk menemukan nama server Anda.

Buka portal Microsoft Azure.
Pilih Database SQL.
Filter pada nama database yang Anda pilih untuk digunakan. Nama server tercantum di kolom Nama server.
Filter pada nama database yang ingin Anda gunakan. Nama server tercantum di kolom Nama server.

Ada banyak cara untuk menyambungkan ke SQL Database dan membuat tabel. Langkah-langkah berikut menggunakan FreeTDS dari kluster HDInsight.
Untuk memasang FreeTDS, gunakan perintah berikut dari koneksi SSH ke kluster:
```
sudo apt-get --assume-yes install freetds-dev freetds-bin
```
Setelah instalasi selesai, gunakan perintah berikut untuk menyambungkan ke SQL Database.
```
TDSVER=8.0 tsql -H '<server-name>.database.windows.net' -U '<admin-login>' -p 1433 -D '<database-name>'
```
- Ganti placeholder <server-name> dengan nama server SQL logis.
- <admin-login> Ganti tempat penampung dengan nama pengguna admin untuk SQL Database.
- Ganti placeholder <database-name> dengan nama database
Saat diminta, masukkan kata sandi untuk nama pengguna admin SQL Database.

Anda 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 sqooptest
1>
```

Di perintah 1>, masukkan pernyataan berikut:

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

Ketika pernyataan GO dimasukkan, pernyataan sebelumnya dievaluasi.

Kueri membuat tabel bernama penundaan yang memiliki indeks terkluster.

Gunakan kueri berikut untuk memverifikasi bahwa tabel dibuat:

SELECT * FROM information_schema.tables
GO

Outputnya 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 dan memuat data

Di bagian sebelumnya, Anda menyalin data yang diubah di lokasi abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Di bagian ini, Anda menggunakan Sqoop untuk mengekspor data dari abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output ke tabel yang Anda buat di Azure SQL Database.

Gunakan perintah berikut untuk memverifikasi bahwa Sqoop dapat melihat database SQL Anda:
```
sqoop list-databases --connect jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433 --username <ADMIN_LOGIN> --password <ADMIN_PASSWORD>
```
Perintah mengembalikan daftar database, termasuk database tempat Anda membuat tabel penundaan.

Gunakan perintah berikut untuk mengekspor data dari tabel hivesampletable ke tabel penundaan:

sqoop export --connect 'jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433;database=<DATABASE_NAME>' --username <ADMIN_LOGIN> --password <ADMIN_PASSWORD> --table 'delays' --export-dir 'abfs://<container-name>@.dfs.core.windows.net/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1

Sqoop tersambung ke database yang berisi tabel penundaan, dan mengekspor data dari direktori /tutorials/flightdelays/output ke tabel penundaan.

Setelah perintah sqoop selesai, gunakan utilitas tsql untuk menyambungkan ke database:

TDSVER=8.0 tsql -H <SERVER_NAME>.database.windows.net -U <ADMIN_LOGIN> -P <ADMIN_PASSWORD> -p 1433 -D <DATABASE_NAME>

Gunakan pernyataan berikut untuk memverifikasi bahwa data diekspor 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.
Masukkan exit untuk keluar dari utilitas tsql.

Membersihkan sumber daya

Semua sumber daya yang digunakan dalam tutorial ini sudah ada sebelumnya. Pembersihan tidak perlu dilakukan.

Langkah berikutnya

Untuk mempelajari cara lain untuk menangani data di HDInsight, lihat artikel berikut ini:

Menggunakan Azure Data Lake Storage Gen2 dengan kluster Azure HDInsight

Bagikan melalui