Tutorial: Mengekstrak, mengubah, dan memuat data dengan menggunakan Azure HDInsight
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
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 prompt1>
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: