Bangun dan sebarkan model menggunakan Azure Synapse Analytics
Dalam tutorial ini, kami memandu Anda dalam membangun dan menyebarkan model pembelajaran mesin menggunakan Azure Synapse Analytics untuk himpunan data yang tersedia untuk umum - himpunan data Perjalanan Taksi NYC. Model klasifikasi biner yang dibangun memprediksi apakah tip dibayar untuk perjalanan atau tidak. Model termasuk klasifikasi multikelas (apakah ada tip atau tidak) dan regresi (distribusi untuk jumlah tip yang diberikan).
Prosedur ini mengikuti alur kerja Proses Ilmu Data Tim (TDSP). Kami memperlihatkan cara menyiapkan lingkungan ilmu data, cara memuat data ke Azure Synapse Analytics, dan cara menggunakan Azure Synapse Analytics atau Jupyter Notebook untuk menjelajahi data dan fitur teknisi ahli untuk dijadikan model. Lalu, kami menunjukkan cara membuat dan menyebarkan model dengan Azure Machine Learning.
Kumpulan data Perjalanan Taksi NYC
Data Perjalanan Taksi NYC terdiri dari file CSV terkompresi yang berukuran sekitar 20 GB (~48 GB tidak terkompresi), data lebih dari 173 juta perjalanan individu dan tarif yang dibayarkan untuk setiap perjalanan. Setiap data perjalanan meliputi lokasi dan waktu penjemputan serta pengantaran, surat izin taksi yang dianonimkan, dan nomor medali (ID unik taksi). Data ini mencakup semua perjalanan pada tahun 2013 dan disediakan dalam dua himpunan data berikut untuk setiap bulan:
- File trip_data.csv berisi detail perjalanan, seperti jumlah penumpang, titik penjemputan dan pengantaran, durasi perjalanan, dan jarak tempuh perjalanan. Berikut beberapa contoh rekaman:
medallion,hack_license,vendor_id,rate_code,store_and_fwd_flag,pickup_datetime,dropoff_datetime,passenger_count,trip_time_in_secs,trip_distance,pickup_longitude,pickup_latitude,dropoff_longitude,dropoff_latitude
89D227B655E5C82AECF13C3F540D4CF4,BA96DE419E711691B9445D6A6307C170,CMT,1,N,2013-01-01 15:11:48,2013-01-01 15:18:10,4,382,1.00,-73.978165,40.757977,-73.989838,40.751171
0BD7C8F5BA12B88E0B67BED28BEA73D8,9FD8F69F0804BDB5549F40E9DA1BE472,CMT,1,N,2013-01-06 00:18:35,2013-01-06 00:22:54,1,259,1.50,-74.006683,40.731781,-73.994499,40.75066
0BD7C8F5BA12B88E0B67BED28BEA73D8,9FD8F69F0804BDB5549F40E9DA1BE472,CMT,1,N,2013-01-05 18:49:41,2013-01-05 18:54:23,1,282,1.10,-74.004707,40.73777,-74.009834,40.726002
DFD2202EE08F7A8DC9A57B02ACB81FE2,51EE87E3205C985EF8431D850C786310,CMT,1,N,2013-01-07 23:54:15,2013-01-07 23:58:20,2,244,.70,-73.974602,40.759945,-73.984734,40.759388
DFD2202EE08F7A8DC9A57B02ACB81FE2,51EE87E3205C985EF8431D850C786310,CMT,1,N,2013-01-07 23:25:03,2013-01-07 23:34:24,1,560,2.10,-73.97625,40.748528,-74.002586,40.747868
- File trip_fare.csv berisi detail tarif yang dibayarkan untuk setiap perjalanan, seperti jenis pembayaran, jumlah tarif, biaya tambahan dan pajak, uang tip dan tarif tol, serta total jumlah uang yang dibayarkan. Berikut beberapa contoh rekaman:
medallion, hack_license, vendor_id, pickup_datetime, payment_type, fare_amount, surcharge, mta_tax, tip_amount, tolls_amount, total_amount
89D227B655E5C82AECF13C3F540D4CF4,BA96DE419E711691B9445D6A6307C170,CMT,2013-01-01 15:11:48,CSH,6.5,0,0.5,0,0,7
0BD7C8F5BA12B88E0B67BED28BEA73D8,9FD8F69F0804BDB5549F40E9DA1BE472,CMT,2013-01-06 00:18:35,CSH,6,0.5,0.5,0,0,7
0BD7C8F5BA12B88E0B67BED28BEA73D8,9FD8F69F0804BDB5549F40E9DA1BE472,CMT,2013-01-05 18:49:41,CSH,5.5,1,0.5,0,0,7
DFD2202EE08F7A8DC9A57B02ACB81FE2,51EE87E3205C985EF8431D850C786310,CMT,2013-01-07 23:54:15,CSH,5,0.5,0.5,0,0,6
DFD2202EE08F7A8DC9A57B02ACB81FE2,51EE87E3205C985EF8431D850C786310,CMT,2013-01-07 23:25:03,CSH,9.5,0.5,0.5,0,0,10.5
Kunci unik yang digunakan untuk menggabungkan trip_data dan trip_fare terdiri dari tiga bidang berikut:
- medallion,
- hack_license dan
- pickup_datetime.
Menangani tiga jenis tugas prediksi
Kami merumuskan tiga masalah prediksi berdasarkan tip_amount untuk menggambarkan tiga jenis tugas pemodelan:
- Klasifikasi biner: Untuk memprediksi apakah tip dibayarkan atau tidak untuk sebuah perjalanan, yaitu, tip_amount yang lebih besar dari $0 adalah contoh positif, sedangkan tip_amount dari $0 adalah contoh negatif.
- Klasifikasi multikelas: Untuk memprediksi rentang tip yang diberikan. Kami membagi tip_amount menjadi lima bin atau kelas:
Class 0 : tip_amount = $0
Class 1 : tip_amount > $0 and tip_amount <= $5
Class 2 : tip_amount > $5 and tip_amount <= $10
Class 3 : tip_amount > $10 and tip_amount <= $20
Class 4 : tip_amount > $20
- Tugas regresi: Untuk memprediksi jumlah tip yang diberikan untuk perjalanan.
Menyiapkan lingkungan ilmu data Azure untuk analitik tingkat lanjut
Untuk menyiapkan lingkungan Azure Data Science Anda, ikuti langkah berikut.
Buat akun penyimpanan blob Azure Anda sendiri
Saat Anda memprovisikan penyimpanan blob Azure Anda sendiri, pilih lokasi geografis untuk penyimpanan blob Azure Anda di atau sedekat mungkin dengan US Tengah Selatan, yang merupakan tempat data Taksi NYC disimpan. Data akan disalin menggunakan AzCopy dari kontainer penyimpanan blob umum ke kontainer di akun penyimpanan Anda sendiri. Semakin dekat penyimpanan blob Azure Anda dengan US Tengah Selatan, semakin cepat tugas ini (Langkah 4) akan selesai.
Untuk membuat akun Azure Storage Anda sendiri, ikuti langkah yang diuraikan di Tentang akun Azure Storage. Pastikan Anda membuat catatan tentang nilai untuk mengikuti info masuk akun penyimpanan karena catatan tersebut akan diperlukan nanti dalam panduan ini.
- Name Akun Penyimpanan
- Kunci Akun Penyimpanan
- Nama Kontainer (tempat penyimpanan yang Anda inginkan di penyimpanan blob Azure)
Provisikan instans Azure Synapse Analytics Anda. Ikuti dokumentasi di Buat dan kueri Analitik Azure Synapse di portal Microsoft Azure untuk memprovisikan instans Azure Synapse Analytics. Pastikan Anda membuat notasi pada info masuk Azure Synapse Analytics berikut yang akan digunakan di langkah selanjutnya.
- Nama Server: <Nama server>.database.windows.net
- Nama SQLDW (Database)
- Nama pengguna
- Kata sandi
Pasang Visual Studio dan SQL Server Data Tools. Untuk mengetahui petunjuknya, lihat Memulai dengan Visual Studio 2019 untuk Azure Synapse Analytics.
Sambungkan ke Azure Synapse Analytics dengan Visual Studio. Untuk petunjuk, lihat langkah 1 & 2 di Menyambungkan ke SQL Analytics di Azure Synapse Analytics.
Catatan
Jalankan kueri SQL berikut pada database yang Anda buat di Azure Synapse Analytics (alih-alih kueri yang disediakan di langkah 3 topik sambungkan) untuk membuat kunci utama.
BEGIN TRY
--Try to create the master key
CREATE MASTER KEY
END TRY
BEGIN CATCH
--If the master key exists, do nothing
END CATCH;
Buat ruang kerja Azure Machine Learning di bawah langganan Azure Anda. Untuk mengetahui petunjuknya, lihat Membuat ruang kerja Azure Machine Learning.
Memuat data ke Azure Synapse Analytics
Buka jendela perintah Windows PowerShell. Jalankan perintah PowerShell berikut untuk mengunduh contoh file skrip SQL yang kami bagikan kepada Anda di GitHub ke direktori lokal yang Anda tentukan dengan parameter -DestDir. Anda dapat mengubah nilai parameter -DestDir ke direktori lokal apa pun. Jika -DestDir tidak ada, oarameter akan dibuat oleh skrip PowerShell.
Catatan
Anda mungkin harus Jalankan sebagai Administrator saat menjalankan skrip PowerShell berikut jika direktori DestDir Anda memerlukan hak istimewa Administrator untuk membuat atau menulisnya.
$source = "https://raw.githubusercontent.com/Azure/Azure-MachineLearning-DataScience/master/Misc/SQLDW/Download_Scripts_SQLDW_Walkthrough.ps1"
$ps1_dest = "$pwd\Download_Scripts_SQLDW_Walkthrough.ps1"
$wc = New-Object System.Net.WebClient
$wc.DownloadFile($source, $ps1_dest)
.\Download_Scripts_SQLDW_Walkthrough.ps1 –DestDir 'C:\tempSQLDW'
Setelah berhasil dieksekusi, direktori kerja Anda saat ini berubah menjadi -DestDir. Anda nanti akan melihat layar seperti yang di bawah ini:
Di -DestDir Anda, jalankan skrip PowerShell berikut dalam mode administrator:
./SQLDW_Data_Import.ps1
Saat skrip PowerShell berjalan untuk pertama kalinya, Anda akan diminta untuk memasukkan informasi dari Azure Synapse Analytics dan akun penyimpanan blob Azure Anda. Ketika skrip PowerShell ini selesai berjalan untuk pertama kalinya, info masuk yang Anda masukkan akan ditulis ke file konfigurasi SQLDW.conf di direktori kerja saat ini. Jalannya file skrip PowerShell ini pada masa mendatang memiliki opsi untuk membaca semua parameter yang diperlukan dari file konfigurasi ini. Jika Anda perlu mengubah beberapa parameter, Anda dapat memilih untuk memasukkan parameter pada layar setelah diminta dengan menghapus file konfigurasi ini dan memasukkan nilai parameter seperti yang diminta atau untuk mengubah nilai parameter dengan mengedit file SQLDW.conf di direktori -DestDir Anda.
Catatan
Untuk menghindari pertentangan nama skema dengan yang sudah ada di Azure Azure Synapse Analytics, saat membaca parameter langsung dari file SQLDW.conf, angka acak 3 digit ditambahkan ke nama skema dari file SQLDW.conf sebagai nama skema default untuk setiap eksekusi. Skrip PowerShell dapat meminta nama skema kepada Anda: nama dapat ditentukan berdasarkan kebijaksanaan pengguna.
File skrip PowerShell ini menyelesaikan tugas berikut ini:
Mengunduh dan memasang AzCopy, jika AzCopy belum dipasang
$AzCopy_path = SearchAzCopy if ($AzCopy_path -eq $null){ Write-Host "AzCopy.exe is not found in C:\Program Files*. Now, start installing AzCopy..." -ForegroundColor "Yellow" InstallAzCopy $AzCopy_path = SearchAzCopy } $env_path = $env:Path for ($i=0; $i -lt $AzCopy_path.count; $i++){ if ($AzCopy_path.count -eq 1){ $AzCopy_path_i = $AzCopy_path } else { $AzCopy_path_i = $AzCopy_path[$i] } if ($env_path -notlike '*' +$AzCopy_path_i+'*'){ Write-Host $AzCopy_path_i 'not in system path, add it...' [Environment]::SetEnvironmentVariable("Path", "$AzCopy_path_i;$env_path", "Machine") $env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") $env_path = $env:Path }Menyalin data ke akun penyimpanan blob pribadi Anda dari blob mum dengan AzCopy
Write-Host "AzCopy is copying data from public blob to yo storage account. It may take a while..." -ForegroundColor "Yellow" $start_time = Get-Date AzCopy.exe /Source:$Source /Dest:$DestURL /DestKey:$StorageAccountKey /S $end_time = Get-Date $time_span = $end_time - $start_time $total_seconds = [math]::Round($time_span.TotalSeconds,2) Write-Host "AzCopy finished copying data. Please check your storage account to verify." -ForegroundColor "Yellow" Write-Host "This step (copying data from public blob to your storage account) takes $total_seconds seconds." -ForegroundColor "Green"Memuat data menggunakan Polybase (dengan mengeksekusi LoadDataToSQLDW.sql) ke Azure Synapse Analytics dari akun penyimpanan blob pribadi Anda dengan perintah berikut.
Buat grup skema
EXEC (''CREATE SCHEMA {schemaname};'');Buat info masuk yang dicakup database
CREATE DATABASE SCOPED CREDENTIAL {KeyAlias} WITH IDENTITY = ''asbkey'' , Secret = ''{StorageAccountKey}''Buat sumber data eksternal untuk blob Azure Storage
CREATE EXTERNAL DATA SOURCE {nyctaxi_trip_storage} WITH ( TYPE = HADOOP, LOCATION =''wasbs://{ContainerName}@{StorageAccountName}.blob.core.windows.net'', CREDENTIAL = {KeyAlias} ) ; CREATE EXTERNAL DATA SOURCE {nyctaxi_fare_storage} WITH ( TYPE = HADOOP, LOCATION =''wasbs://{ContainerName}@{StorageAccountName}.blob.core.windows.net'', CREDENTIAL = {KeyAlias} ) ;Buat format file eksternal untuk file csv. Data tidak dikompresi dan bidang dipisahkan dengan karakter pipa.
CREATE EXTERNAL FILE FORMAT {csv_file_format} WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR ='','', USE_TYPE_DEFAULT = TRUE ) ) ;Buat tarif eksternal dan tabel perjalanan untuk himpunan data taksi NYC di penyimpanan blob Azure.
CREATE EXTERNAL TABLE {external_nyctaxi_fare} ( medallion varchar(50) not null, hack_license varchar(50) not null, vendor_id char(3), pickup_datetime datetime not null, payment_type char(3), fare_amount float, surcharge float, mta_tax float, tip_amount float, tolls_amount float, total_amount float ) with ( LOCATION = ''/nyctaxifare/'', DATA_SOURCE = {nyctaxi_fare_storage}, FILE_FORMAT = {csv_file_format}, REJECT_TYPE = VALUE, REJECT_VALUE = 12 ) CREATE EXTERNAL TABLE {external_nyctaxi_trip} ( medallion varchar(50) not null, hack_license varchar(50) not null, vendor_id char(3), rate_code char(3), store_and_fwd_flag char(3), pickup_datetime datetime not null, dropoff_datetime datetime, passenger_count int, trip_time_in_secs bigint, trip_distance float, pickup_longitude varchar(30), pickup_latitude varchar(30), dropoff_longitude varchar(30), dropoff_latitude varchar(30) ) with ( LOCATION = ''/nyctaxitrip/'', DATA_SOURCE = {nyctaxi_trip_storage}, FILE_FORMAT = {csv_file_format}, REJECT_TYPE = VALUE, REJECT_VALUE = 12 )Muat data dari tabel eksternal di penyimpanan blob Azure ke Azure Synapse Analytics
CREATE TABLE {schemaname}.{nyctaxi_fare} WITH ( CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH(medallion) ) AS SELECT * FROM {external_nyctaxi_fare} ; CREATE TABLE {schemaname}.{nyctaxi_trip} WITH ( CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH(medallion) ) AS SELECT * FROM {external_nyctaxi_trip} ;Buat contoh tabel data (NYCTaxi_Sample) dan sisipkan data ke dalamnya agar tidak memilih kueri SQL pada tabel perjalanan dan tarif. (Beberapa langkah dalam panduan ini perlu menggunakan tabel sampel ini.)
CREATE TABLE {schemaname}.{nyctaxi_sample} WITH ( CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH(medallion) ) AS ( SELECT t.*, f.payment_type, f.fare_amount, f.surcharge, f.mta_tax, f.tolls_amount, f.total_amount, f.tip_amount, tipped = CASE WHEN (tip_amount > 0) THEN 1 ELSE 0 END, tip_class = CASE WHEN (tip_amount = 0) THEN 0 WHEN (tip_amount > 0 AND tip_amount <= 5) THEN 1 WHEN (tip_amount > 5 AND tip_amount <= 10) THEN 2 WHEN (tip_amount > 10 AND tip_amount <= 20) THEN 3 ELSE 4 END FROM {schemaname}.{nyctaxi_trip} t, {schemaname}.{nyctaxi_fare} f WHERE datepart("mi",t.pickup_datetime) = 1 AND t.medallion = f.medallion AND t.hack_license = f.hack_license AND t.pickup_datetime = f.pickup_datetime AND pickup_longitude <> ''0'' AND dropoff_longitude <> ''0'' ) ;
Lokasi geografis akun penyimpanan Anda memengaruhi waktu pemuatan.
Catatan
Bergantung pada lokasi geografis akun penyimpanan blob pribadi Anda, proses menyalin data dari blob umum ke akun penyimpanan pribadi Anda dapat memakan waktu sekitar 15 menit, atau bahkan lebih lama, dan proses pemuatan data dari akun penyimpanan Anda ke Azure Azure Synapse Analytics Anda dapat memakan waktu 20 menit atau lebih lama.
Anda harus memutuskan apa yang harus dilakukan jika Anda memiliki sumber duplikat dan file tujuan.
Catatan
Jika file .csv yang akan disalin dari penyimpanan blob umum ke akun penyimpanan blob pribadi Anda sudah ada di akun penyimpanan blob pribadi Anda, AzCopy akan bertanya apakah Anda ingin menimpanya atau tidak. Jika Anda tidak ingin menimpa file, masukkan n ketika diminta. Jika Anda ingin menimpa semuanya, masukkan a ketika diminta. Anda juga dapat memasukkan y untuk menimpa file .csv satu per satu.
Anda dapat menggunakan data Anda sendiri. Jika data berada di komputer lokal Anda di aplikasi kehidupan nyata, Anda masih dapat menggunakan AzCopy untuk mengunggah data lokal ke penyimpanan b;pb Azure pribadi Anda. Anda hanya perlu mengubah lokasi Sumber, $Source = "http://getgoing.blob.core.windows.net/public/nyctaxidataset", di perintah AzCopy file skrip PowerShell ke direktori lokal yang berisi data Anda.
Tip
Jika data sudah berada di penyimpanan blob Azure pribadi Anda di aplikasi kehidupan nyata, Anda dapat melewati langkah AzCopy di skrip PowerShell dan langsung mengunggah data ke Azure Azure Synapse Analytics. Ini akan memerlukan pengeditan skrip tambahan untuk menyesuaikannya dengan format data Anda.
Skrip PowerShell ini juga menyertakan informasi Azure Synapse Analytics ke dalam file contoh eksplorasi data SQLDW_Explorations.sql, SQLDW_Explorations.ipynb, dan SQLDW_Explorations_Scripts.py sehingga ketiga file ini siap untuk segera diuji coba setelah skrip PowerShell selesai.
Setelah eksekusi berhasil, Anda akan melihat layar seperti di bawah ini:
Eksplorasi data dan rekayasa fitur di Azure Synapse Analytics
Di bagian ini, kami melakukan eksplorasi data dan pembuatan fitur dengan menjalankan kueri SQL terhadap Azure Synapse Analytics secara langsung menggunakan Alat Data Visual Studio. Semua kueri SQL yang digunakan di bagian ini dapat ditemukan dalam contoh skrip bernama SQLDW_Explorations.sql. File ini telah diunduh ke direktori lokal Anda oleh skrip PowerShell. Anda juga dapat mengambilnya dari GitHub. Tetapi file di GitHub tidak memiliki informasi Azure Synapse Analytics.
Sambungkan ke Azure Synapse Analytics menggunakan Visual Studio dengan nama masuk dan kata sandi Azure Synapse Analytics dan buka SQL Object Explorer untuk mengonfirmasi bahwa database dan tabel telah diimpor. Ambil file SQLDW_Explorations.sql.
Catatan
Untuk membuka editor kueri Parallel Data Warehouse (PDW), gunakan perintah Kueri Baru saat PDW Anda dipilih di SQL Object Explorer. Editor kueri SQL standar tidak didukung oleh PDW.
Berikut adalah tipe tugas eksplorasi data dan pembuatan fitur yang dijalankan di bagian ini:
- Menjelajahi distribusi data di beberapa bidang pada periode waktu yang bervariasi.
- Menyelidiki kualitas data bidang bujur dan lintang.
- Buat label klasifikasi biner dan multikelas berdasarkan tip_amount.
- Membuat fitur dan menghitung/membandingkan jarak tempuh perjalanan.
- Menggabungkan dua tabel dan mengekstrak sampel acak yang akan digunakan untuk membuat model.
Verifikasi impor data
Kueri ini memberikan verifikasi cepat pada sejumlah baris dan kolom di tabel yang diisi sebelumnya menggunakan impor massal paralel Polybase,
-- Laporkan jumlah baris dalam tabel <nyctaxi_trip> tanpa pemindaian tabel
SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_trip>')
-- Laporkan jumlah kolom dalam tabel <nyctaxi_trip>
SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '<nyctaxi_trip>' AND table_schema = '<schemaname>'
Output: Anda akan mendapatkan 173.179.759 baris dan 14 kolom.
Eksplorasi: Distribusi perjalanan menurut medallion
Contoh kueri ini mengidentifikasi medali (nomor taksi) yang menyelesaikan lebih dari 100 perjalanan dalam periode waktu tertentu. Kueri akan memperoleh keuntungan dari akses tabel berpartisi karena kueri dikondisikan oleh skema partisi pickup_datetime. Mengkueri himpunan data lengkap juga akan memanfaatkan tabel berpartisi dan/atau pemindaian indeks.
SELECT medallion, COUNT(*)
FROM <schemaname>.<nyctaxi_fare>
WHERE pickup_datetime BETWEEN '20130101' AND '20130331'
GROUP BY medallion
HAVING COUNT(*) > 100
Output: Kueri akan menampilkan tabel dengan baris yang menentukan 13.369 medali (taksi) dan jumlah perjalanan yang diselesaikan pada tahun 2013. Kolom terakhir berisi perhitungan jumlah perjalanan yang diselesaikan.
Eksplorasi: Distribusi perjalanan menurut medalilion dan hack_license
Contoh ini mengidentifikasi medali (nomor taksi) dan nomor hack_license (sopir) yang menyelesaikan lebih dari 100 perjalanan dalam periode waktu tertentu.
SELECT medallion, hack_license, COUNT(*)
FROM <schemaname>.<nyctaxi_fare>
WHERE pickup_datetime BETWEEN '20130101' AND '20130131'
GROUP BY medallion, hack_license
HAVING COUNT(*) > 100
Outpuit: Kueri akan menampilkan tabel dengan 13.369 baris yang menentukan 13.369 ID sopir/mobil yang telah menyelesaikan lebih dari 100 perjalanan pada tahun 2013. Kolom terakhir berisi perhitungan jumlah perjalanan yang diselesaikan.
Penilaian kualitas data: Memverifikasi data dengan garis bujur dan/atau garis lintang yang salah
Contoh ini menyelidiki apakah salah satu bidang garis bujur dan/atau garis lintang yang berisi nilai tidak valid (derajat radian harus antara -90 dan 90), atau memiliki koordinat (0, 0).
SELECT COUNT(*) FROM <schemaname>.<nyctaxi_trip>
WHERE pickup_datetime BETWEEN '20130101' AND '20130331'
AND (CAST(pickup_longitude AS float) NOT BETWEEN -90 AND 90
OR CAST(pickup_latitude AS float) NOT BETWEEN -90 AND 90
OR CAST(dropoff_longitude AS float) NOT BETWEEN -90 AND 90
OR CAST(dropoff_latitude AS float) NOT BETWEEN -90 AND 90
OR (pickup_longitude = '0' AND pickup_latitude = '0')
OR (dropoff_longitude = '0' AND dropoff_latitude = '0'))
Output: Kueri menampilkan 837.467 perjalanan yang memiliki bidang garis bujur dan/atau garis lintang yang tidak valid.
Eksplorasi: Distribusi perjalanan dengan tip vs. tanpa tip
Dalam contoh ini, jumlah perjalanan dengan tip vs. angka tanpa tip dalam periode waktu tertentu (atau dalam himpunan data lengkap jika mencakup setahun penuh seperti yang diatur di sini). Distribusi ini mencerminkan distribusi label biner yang nantinya digunakan untuk pemodelan klasifikasi biner.
SELECT tipped, COUNT(*) AS tip_freq FROM (
SELECT CASE WHEN (tip_amount > 0) THEN 1 ELSE 0 END AS tipped, tip_amount
FROM <schemaname>.<nyctaxi_fare>
WHERE pickup_datetime BETWEEN '20130101' AND '20131231') tc
GROUP BY tipped
Ouput: Kueri akan menampilkan frekuensi tip berikut untuk tahun 2013: 90.447.622 memberi tip dan 82.264.709 tidak memberi tip.
Eksplorasi: Distribusi kelas/rentang tip
Contoh ini menghitung distribusi rentang tip dalam periode waktu tertentu (atau di himpunan data lengkap jika mencakup setahun penuh). Distribusi kelas label ini nantinya akan digunakan untuk pemodelan klasifikasi multikelas.
SELECT tip_class, COUNT(*) AS tip_freq FROM (
SELECT CASE
WHEN (tip_amount = 0) THEN 0
WHEN (tip_amount > 0 AND tip_amount <= 5) THEN 1
WHEN (tip_amount > 5 AND tip_amount <= 10) THEN 2
WHEN (tip_amount > 10 AND tip_amount <= 20) THEN 3
ELSE 4
END AS tip_class
FROM <schemaname>.<nyctaxi_fare>
WHERE pickup_datetime BETWEEN '20130101' AND '20131231') tc
GROUP BY tip_class
Output:
| tip_class | tip_freq |
|---|---|
| 1 | 82230915 |
| 2 | 6198803 |
| 3 | 1932223 |
| 0 | 82264625 |
| 4 | 85765 |
Eksplorasi: Menghitung dan membandingkan jarak tempuh perjalanan
Contoh ini mengonversi garis bujur dan garis lintang penjemputan serta pengantaran ke titik geografi SQL, menghitung jarak tempuh perjalanan menggunakan perbedaan titik geografi SQL, dan menampilkan sampel acak dari hasil untuk perbandingan. Contoh membatasi hasil hanya untuk koordinat yang valid menggunakan kueri penilaian kualitas data yang dijelaskan sebelumnya.
/****** Object: UserDefinedFunction [dbo].[fnCalculateDistance] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type IN ('FN', 'IF') AND name = 'fnCalculateDistance')
DROP FUNCTION fnCalculateDistance
GO
-- User-defined function to calculate the direct distance in mile between two geographical coordinates.
CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
RETURNS float
AS
BEGIN
DECLARE @distance decimal(28, 10)
-- Convert to radians
SET @Lat1 = @Lat1 / 57.2958
SET @Long1 = @Long1 / 57.2958
SET @Lat2 = @Lat2 / 57.2958
SET @Long2 = @Long2 / 57.2958
-- Calculate distance
SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
--Convert to miles
IF @distance <> 0
BEGIN
SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
END
RETURN @distance
END
GO
SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude,
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) AS DirectDistance
FROM <schemaname>.<nyctaxi_trip>
WHERE datepart("mi",pickup_datetime)=1
AND CAST(pickup_latitude AS float) BETWEEN -90 AND 90
AND CAST(dropoff_latitude AS float) BETWEEN -90 AND 90
AND pickup_longitude != '0' AND dropoff_longitude != '0'
Rekayasa fitur menggunakan fungsi SQL
Terkadang fungsi SQL dapat menjadi pilihan yang efisien untuk rekayasa fitur. Dalam panduan ini, kami mendefinisikan fungsi SQL untuk menghitung jarak langsung antara lokasi penjemputan dan pengantaran. Anda dapat menjalankan skrip SQL berikut di Alat Data Visual Studio.
Berikut adalah skrip SQL yang mendefinisikan fungsi jarak.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type IN ('FN', 'IF') AND name = 'fnCalculateDistance')
DROP FUNCTION fnCalculateDistance
GO
-- User-defined function calculate the direct distance between two geographical coordinates.
CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
RETURNS float
AS
BEGIN
DECLARE @distance decimal(28, 10)
-- Convert to radians
SET @Lat1 = @Lat1 / 57.2958
SET @Long1 = @Long1 / 57.2958
SET @Lat2 = @Lat2 / 57.2958
SET @Long2 = @Long2 / 57.2958
-- Calculate distance
SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
--Convert to miles
IF @distance <> 0
BEGIN
SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
END
RETURN @distance
END
GO
Berikut adalah contoh untuk memanggil fungsi ini untuk menghasilkan fitur dalam kueri SQL Anda:
-- Contoh kueri untuk memanggil fungsi untuk membuat fitur
SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) AS DirectDistance FROM <schemaname>.<nyctaxi_trip> WHERE datepart("mi",pickup_datetime)=1 AND CAST(pickup_latitude AS float) BETWEEN -90 AND 90 AND CAST(dropoff_latitude AS float) BETWEEN -90 AND 90 AND pickup_longitude != '0' AND dropoff_longitude != '0'
Output: Kueri ini membuat tabel (dengan 2.803.538 baris) dengan garis lintang dan garis bujur penjemputan dan pengantaran serta jarak langsung yang sesuai dalam hitungan mil. Berikut adalah hasil untuk tiga baris pertama:
| (Nomor baris) | pickup_latitude | pickup_longitude | dropoff_latitude | dropoff_longitude | DirectDistance |
|---|---|---|---|---|---|
| 1 | 40.731804 | -74.001083 | 40.736622 | -73.988953 | .7169601222 |
| 2 | 40.715794 | -74,010635 | 40.725338 | -74.00399 | .7448343721 |
| 3 | 40.761456 | -73.999886 | 40.766544 | -73.988228 | 0.7037227967 |
Menyiapkan data untuk pembuatan model
Kueri berikut menggabungkan tabel nyctaxi_trip dan nyctaxi_fare, dan menghasilkan label klasifikasi biner tipped, label klasifikasi multi-kelas tip_class, dan mengekstrak sampel dari himpunan data lengkap yang digabungkan. Pengambilan sampel dilakukan dengan mengambil subset perjalanan berdasarkan waktu penjemputan. Kueri SQL ini dapat disalin, kemudian ditempelkan untuk konsumsi data langsung dari instans SQL Database di Azure. Kueri mengecualikan data dengan koordinat (0, 0) yang salah.
SELECT t.*, f.payment_type, f.fare_amount, f.surcharge, f.mta_tax, f.tolls_amount, f.total_amount, f.tip_amount,
CASE WHEN (tip_amount > 0) THEN 1 ELSE 0 END AS tipped,
CASE WHEN (tip_amount = 0) THEN 0
WHEN (tip_amount > 0 AND tip_amount <= 5) THEN 1
WHEN (tip_amount > 5 AND tip_amount <= 10) THEN 2
WHEN (tip_amount > 10 AND tip_amount <= 20) THEN 3
ELSE 4
END AS tip_class
FROM <schemaname>.<nyctaxi_trip> t, <schemaname>.<nyctaxi_fare> f
WHERE datepart("mi",t.pickup_datetime) = 1
AND t.medallion = f.medallion
AND t.hack_license = f.hack_license
AND t.pickup_datetime = f.pickup_datetime
AND pickup_longitude != '0' AND dropoff_longitude != '0'
Saat sudah siap untuk melanjutkan ke Azure Machine Learning, Anda dapat:
- Menyimpan kueri SQL akhir untuk mengekstrak dan mengambil sampel data serta menyalin-tempel kueri tersebut secara langsung ke dalam notebook di Azure Machine Learning, atau
- Mempertahankan data sampel dan rekayasa yang rencananya Anda gunakan untuk pembuatan model dalam tabel Azure Synapse Analytics baru dan mengakses tabel tersebut melalui datastore di Azure Machine Learning.
Eksplorasi data dan rekayasa fitur di notebook
Di bagian ini, kami akan melakukan eksplorasi data dan pembuatan fitur menggunakan kueri Python dan SQL terhadap Azure Synapse Analytics yang dibuat sebelumnya. Notebook sampel yang bernama SQLDW_Explorations.ipynb dan file skrip Python SQLDW_Explorations_Scripts.py telah diunduh ke direktori lokal Anda. Keduanya juga tersedia di GitHub. Kedua file ini identik dalam skrip Python. File skrip Python ini diberikan kepada Anda jika Anda ingin menggunakan Python tanpa notebook. Kedua sampel file Python ini dirancang berdasarkan Python 2.7.
Informasi Azure Synapse Analytics yang diperlukan dalam sampel; Jupyter Notebook dan file skrip Python yang diunduh ke komputer lokal Anda sebelumnya telah disertakan oleh skrip PowerShell. File dapat dieksekusi tanpa pengubahan.
Jika Anda telah menyiapkan ruang kerja Azure Machine Learning, Anda dapat langsung mengunggah sampel Notebook ke area AzureML Notebooks. Terkait petunjuk mengenai pengunggahan notebook, lihat Menjalankan Jupyter Notebooks di ruang kerja Anda
Catatan: Untuk menjalankan sampel Notebook Jupyter atau file skrip Python, diperlukan paket Python berikut.
- pandas
- numpy
- matplotlib
- pyodbc
- PyTables
Saat membangun solusi analitik tingkat lanjut di Azure Machine Learning dengan data yang besar, berikut adalah urutan yang disarankan:
- Baca sampel data yang kecil ke bingkai data dalam memori.
- Lakukan beberapa visualisasi dan eksplorasi menggunakan data yang diambil sampelnya.
- Lakukan eksperimen dengan rekayasa fitur menggunakan data yang diambil sampelnya.
- Untuk eksplorasi data yang lebih besar, manipulasi data, dan rekayasa fitur, gunakan Python untuk menerbitkan Kueri SQL langsung terhadap Azure Synapse Analytics.
- Tentukan ukuran sampel yang cocok untuk pembuatan model Azure Machine Learning.
Berikut beberapa contoh dari eksplorasi data, visualisasi data, dan rekayasa fitur. Eksplorasi data lainnya dapat ditemukan di sampel Jupyter Notebook dan sampel file skrip Python.
Menginisialisasi info masuk database
Lakukan inisialisasi pengaturan koneksi database dalam variabel berikut:
SERVER_NAME=<server name>
DATABASE_NAME=<database name>
USERID=<user name>
PASSWORD=<password>
DB_DRIVER = <database driver>
Membuat koneksi database
Berikut adalah string koneksi yang membuat koneksi ke database.
CONNECTION_STRING = 'DRIVER={'+DRIVER+'};SERVER='+SERVER_NAME+';DATABASE='+DATABASE_NAME+';UID='+USERID+';PWD='+PASSWORD
conn = pyodbc.connect(CONNECTION_STRING)
Melaporkan jumlah baris dan kolom dalam tabel <nyctaxi_trip>
nrows = pd.read_sql('''
SELECT SUM(rows) FROM sys.partitions
WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_trip>')
''', conn)
print 'Total number of rows = %d' % nrows.iloc[0,0]
ncols = pd.read_sql('''
SELECT COUNT(*) FROM information_schema.columns
WHERE table_name = ('<nyctaxi_trip>') AND table_schema = ('<schemaname>')
''', conn)
print 'Total number of columns = %d' % ncols.iloc[0,0]
- Total jumlah baris = 173179759
- Total jumlah kolom = 14
Melaporkan jumlah baris dan kolom dalam tabel <nyctaxi_fare>
nrows = pd.read_sql('''
SELECT SUM(rows) FROM sys.partitions
WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_fare>')
''', conn)
print 'Total number of rows = %d' % nrows.iloc[0,0]
ncols = pd.read_sql('''
SELECT COUNT(*) FROM information_schema.columns
WHERE table_name = ('<nyctaxi_fare>') AND table_schema = ('<schemaname>')
''', conn)
print 'Total number of columns = %d' % ncols.iloc[0,0]
- Total jumlah baris = 173179759
- Total jumlah kolom = 11
Baca sampel data yang kecil dari Database Azure Synapse Analytics
t0 = time.time()
query = '''
SELECT TOP 10000 t.*, f.payment_type, f.fare_amount, f.surcharge, f.mta_tax,
f.tolls_amount, f.total_amount, f.tip_amount
FROM <schemaname>.<nyctaxi_trip> t, <schemaname>.<nyctaxi_fare> f
WHERE datepart("mi",t.pickup_datetime) = 1
AND t.medallion = f.medallion
AND t.hack_license = f.hack_license
AND t.pickup_datetime = f.pickup_datetime
'''
df1 = pd.read_sql(query, conn)
t1 = time.time()
print 'Time to read the sample table is %f seconds' % (t1-t0)
print 'Number of rows and columns retrieved = (%d, %d)' % (df1.shape[0], df1.shape[1])
Waktu yang diperlukan untuk mengambil sampel tabel adalah 14,096495 detik. Jumlah baris dan kolom yang diambil = (1000, 21).
Statistik deskriptif
Kini Anda sudah siap untuk mengeksplorasi data sampel. Kami mulai dengan melihat beberapa statistik deskriptif untuk trip_distance (atau bidang lain yang Anda pilih untuk ditentukan).
df1['trip_distance'].describe()
Visualisasi: Contoh diagram kotak
Selanjutnya, mari kita lihat diagram kotak pada jarak perjalanan untuk memvisualisasikan kuantil.
df1.boxplot(column='trip_distance',return_type='dict')
Visualisasi: Contoh diagram distribusi
Plot yang memvisualisasikan distribusi dan histogram untuk jarak perjalanan yang diambil sampelnya.
fig = plt.figure()
ax1 = fig.add_subplot(1,2,1)
ax2 = fig.add_subplot(1,2,2)
df1['trip_distance'].plot(ax=ax1,kind='kde', style='b-')
df1['trip_distance'].hist(ax=ax2, bins=100, color='k')
Visualisasi: Diagram batang dan garis
Dalam contoh ini, kita mengelompokkan jarak perjalanan ke dalam lima bin dan memvisualisasikan hasil pengelompokkannya.
trip_dist_bins = [0, 1, 2, 4, 10, 1000]
df1['trip_distance']
trip_dist_bin_id = pd.cut(df1['trip_distance'], trip_dist_bins)
trip_dist_bin_id
Kita dapat membuat diagram untuk distribusi bin di atas dengan jenis diagram batang atau garis dengan:
pd.Series(trip_dist_bin_id).value_counts().plot(kind='bar')
dan
pd.Series(trip_dist_bin_id).value_counts().plot(kind='line')
Visualisasi: Contoh diagram tebar
Kami menunjukkan diagram tebar antara trip_time_in_secs dan trip_distance untuk melihat apakah ada korelasi
plt.scatter(df1['trip_time_in_secs'], df1['trip_distance'])
Demikian pula kami dapat memeriksa hubungan antara rate_code dan trip_distance.
plt.scatter(df1['passenger_count'], df1['trip_distance'])
Eksplorasi data pada data sampel menggunakan kueri SQL di Jupyter Notebook
Pada bagian ini, kita akan mengeksplorasi distribusi data menggunakan data sampel yang dipertahankan di tabel baru yang telah dibuat sebelumnya. Eksplorasi serupa dapat dilakukan menggunakan tabel asli.
Eksplorasi: Melaporkan jumlah baris dan kolom dalam tabel sampel
nrows = pd.read_sql('''SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_sample>')''', conn)
print 'Number of rows in sample = %d' % nrows.iloc[0,0]
ncols = pd.read_sql('''SELECT count(*) FROM information_schema.columns WHERE table_name = ('<nyctaxi_sample>') AND table_schema = '<schemaname>'''', conn)
print 'Number of columns in sample = %d' % ncols.iloc[0,0]
Eksplorasi: Distribusi dengan tip/tanpa tip
query = '''
SELECT tipped, count(*) AS tip_freq
FROM <schemaname>.<nyctaxi_sample>
GROUP BY tipped
'''
pd.read_sql(query, conn)
Eksplorasi: Distribusi kelas tip
query = '''
SELECT tip_class, count(*) AS tip_freq
FROM <schemaname>.<nyctaxi_sample>
GROUP BY tip_class
'''
tip_class_dist = pd.read_sql(query, conn)
Eksplorasi: Membuat diagram distribusi tip menurut kelas
tip_class_dist['tip_freq'].plot(kind='bar')
Eksplorasi: Distribusi harian perjalanan
query = '''
SELECT CONVERT(date, dropoff_datetime) AS date, COUNT(*) AS c
FROM <schemaname>.<nyctaxi_sample>
GROUP BY CONVERT(date, dropoff_datetime)
'''
pd.read_sql(query,conn)
Eksplorasi: Distribusi perjalanan per medali
query = '''
SELECT medallion,count(*) AS c
FROM <schemaname>.<nyctaxi_sample>
GROUP BY medallion
'''
pd.read_sql(query,conn)
Eksplorasi: Distribusi perjalanan menurut medali dan surat izin taksi
query = '''select medallion, hack_license,count(*) from <schemaname>.<nyctaxi_sample> group by medallion, hack_license'''
pd.read_sql(query,conn)
Eksplorasi: Distribusi waktu perjalanan
query = '''select trip_time_in_secs, count(*) from <schemaname>.<nyctaxi_sample> group by trip_time_in_secs order by count(*) desc'''
pd.read_sql(query,conn)
Eksplorasi: Distribusi jarak perjalanan
query = '''select floor(trip_distance/5)*5 as tripbin, count(*) from <schemaname>.<nyctaxi_sample> group by floor(trip_distance/5)*5 order by count(*) desc'''
pd.read_sql(query,conn)
Eksplorasi: Distribusi jenis pembayaran
query = '''select payment_type,count(*) from <schemaname>.<nyctaxi_sample> group by payment_type'''
pd.read_sql(query,conn)
Memverifikasi bentuk akhir tabel yang ditampilkan
query = '''SELECT TOP 100 * FROM <schemaname>.<nyctaxi_sample>'''
pd.read_sql(query,conn)
Membuat model di Azure Machine Learning
Kini kita sudah siap untuk melanjutkan ke tahap pembuatan model dan penyebaran model di Azure Machine Learning. Data siap digunakan untuk masalah prediksi apa pun yang telah teridentifikasi sebelumnya, yaitu:
- Klasifikasi biner: Untuk memprediksi apakah tip diberikan atau tidak.
- Klasifikasi multikelas: Untuk memprediksi rentang tip yang diberikan, sesuai dengan kelas yang ditentukan sebelumnya.
- Tugas regresi: Untuk memprediksi jumlah tip yang dibayarkan untuk perjalanan.
Untuk memulai latihan pemodelan, masuk ke ruang kerja Azure Machine Learning Anda. Jika Anda belum membuat ruang kerja pembelajaran mesin, lihat Membuat ruang kerja.
- Untuk mulai menggunakan Azure Machine Learning, lihat Apa yang dimaksud dengan Azure Machine Learning?
- Masuk ke portal Microsoft Azure.
- Beranda Pembelajaran Mesin menyediakan banyak informasi, video, tutorial, tautan ke Referensi Modul, dan sumber daya lain. Untuk informasi selengkapnya tentang Azure Machine Learning, lihat Pusat Dokumentasi Azure Machine Learning.
Eksperimen pelatihan umum terdiri dari langkah-langkah berikut:
- Buat sumber daya penulisan (notebook atau desainer, misalnya).
- Masukkan data ke Azure Machine Learning.
- Lakuka prapemrosesan, transformasi, dan manipulasi pada data sesuai keperluan.
- Buat fitur sesuai keperluan.
- Pisahkan data ke dalam himpunan data pelatihan/validasi/pengujian (atau buat himpunan data untuk tiap-tiap data).
- Pilih satu atau beberapa algoritme pembelajaran mesin bergantung pada masalah pembelajaran yang akan diselesaikan. Misalnya klasifikasi biner, klasifikasi multikelas, regresi.
- Latih satu atau beberapa model menggunakan himpunan data pelatihan.
- Berikan skor pada himpunan data validasi menggunakan model terlatih.
- Evaluasi model untuk menghitung metrik yang relevan pada masalah pembelajaran.
- Sesuaikan model dan pilih model terbaik untuk disebarkan.
Dalam latihan ini, kita telah mengeksplorasi dan merekayasa data di Azure Synapse Analytics, dan menentukan ukuran sampel untuk diserap di Azure Machine Learning. Berikut adalah prosedur untuk membangun satu atau beberapa model prediksi:
- Masukkan data ke Azure Machine Learning. Untuk detailnya, lihat Opsi penyerapan data untuk alur kerja Azure Machine Learning
- Sambungkan ke Synapse Analytics. Untuk detailnya, lihat Menautkan ruang kerja Azure Synapse Analytics dan Azure Machine Learning dan memasang kumpulan Apache Spark
Penting
Pada contoh ekstraksi data pemodelan dan pengambilan sampel kueri di bagian sebelumnya, semua label untuk tiga latihan pemodelan disertakan dalam kueri. Langkah penting (wajib) di setiap latihan pemodelan adalah mengecualikan label yang tidak perlu untuk dua masalah lainnya, dan kebocoran target lainnya. Misalnya, saat menggunakan klasifikasi biner, gunakan label tipped dan kecualikan bidang tip_class, tip_amount, dan total_amount. Yang terakhir adalah kebocoran target karena label menyatakan tip telah diberikan.
Menyebarkan model di Azure Machine Learning
Saat model Anda siap, Anda dapat dengan mudah menyebarkannya sebagai layanan web langsung dari model yang disimpan dalam eksekusi eksperimen. Untuk detail tentang opsi penyebaran, lihat Menyebarkan model pembelajaran mesin ke Azure
Ringkasan
Untuk merekap apa yang telah kita jalani dalam tutorial ini, Anda telah menciptakan lingkungan ilmu data Azure, bekerja dengan kumpulan data publik yang besar, membawanya melalui Proses Ilmu Data Tim, sepanjang jalan dari akuisisi data hingga pelatihan model, dan kemudian ke penyebaran layanan web Azure Machine Learning.
Informasi lisensi
Panduan sampel ini beserta skrip dan notebook dibagikan oleh Microsoft di bawah lisensi MIT. Periksa file LICENSE.txt di direktori kode sampel di GitHub untuk detail selengkapnya.