Terapkan Transformasi SQL

Artikel
05/06/2019

Penting

Dukungan untuk Studio Azure Machine Learning (klasik) akan berakhir pada 31 Agustus 2024. Sebaiknya Anda transisi ke Azure Machine Learning sebelum tanggal tersebut.

Mulai 1 Desember 2021, Anda tidak akan dapat membuat sumber daya Studio Azure Machine Learning (klasik) baru. Hingga 31 Agustus 2024, Anda dapat terus menggunakan sumber daya Pembelajaran Mesin Studio (klasik) yang ada.

Lihat informasi tentang memindahkan proyek pembelajaran mesin dari ML Studio (klasik) ke Azure Machine Learning.
Mer informasjon tentang Azure Machine Learning.

ML Dokumentasi Studio (klasik) sedang berhenti dan mungkin tidak diperbarui di masa mendatang.

Menjalankan kueri SQLite pada himpunan data input untuk mengubah data

Kategori: Transformasi / Manipulasi Data

Catatan

Berlaku untuk: Pembelajaran Mesin Studio (klasik) saja

Modul drag-and-drop serupa tersedia di Azure Machine Learning desainer.

Ringkasan Modul

Artikel ini menjelaskan cara menggunakan modul Terapkan transformasi SQL di Pembelajaran Mesin Studio (klasik), untuk menentukan kueri SQL pada himpunan data input atau himpunan data.

SQL berguna ketika Anda perlu memodifikasi data Anda dengan cara yang kompleks, atau mempertahankan data untuk digunakan di lingkungan lain. Misalnya, menggunakan modul Terapkan transformasi SQL, Anda dapat:

Membuat tabel hasil dan menyimpan himpunan data di database portabel.
Menjalankan transformasi kustom pada jenis data, atau membuat agregrat.
Menjalankan pernyataan kueri SQL untuk memfilter atau mengubah data dan menampilkan hasil kueri sebagai tabel data.

Penting

Mesin SQL yang digunakan dalam modul ini adalah SQLite. Jika Anda tidak terbiasa dengan sintaks SQLite, pastikan untuk membaca bagian sintaks dan penggunaan artikel ini sebagai contoh.

Apa itu SQLite?

SQLite adalah sistem manajemen database relasional domain publik yang terkandung dalam pustaka pemrograman C. SQLite adalah pilihan populer sebagai database tertanam untuk penyimpanan lokal di browser web.

SQLite awalnya dirancang pada tahun 2000 untuk Angkatan Laut AS, untuk mendukung transaksi tanpa server. Ini adalah mesin database mandiri yang tidak memiliki sistem manajemen dan karenanya tidak memerlukan konfigurasi atau administrasi.

Cara mengonfigurasi modul Terapkan Transformasi SQL

Modul ini dapat menggunakan hingga tiga himpunan data sebagai input. Saat mereferensikan himpunan data yang tersambung ke setiap port input, Anda harus menggunakan nama t1, t2, dan t3. Nomor tabel menunjukkan indeks port input.

Parameter yang tersisa adalah kueri SQL, yang menggunakan sintaksis SQLite. Modul ini mendukung semua pernyataan standar sintaksis SQLite. Untuk daftar pernyataan yang tidak didukung, lihat bagian Catatan Teknis.

Sintaks umum dan penggunaan

Saat memasukkan beberapa baris di kotak teks Skrip SQL, gunakan titik koma untuk mengakhiri setiap pernyataan. Jika tidak, pemisah baris akan diubah menjadi spasi.

Misalnya, pernyataan berikut setara:
```
SELECT   
*   
from   
t1;  
```
```
SELECT * from t1;  
```
Anda dapat menambahkan komentar dengan menggunakan baik -- di awal setiap baris, atau dengan melampirkan teks menggunakan /* */.

Misalnya, pernyataan ini valid:
```
SELECT * from t1  
/*WHERE ItemID BETWEEN 1 AND 100*/;  
```
Jika nama kolom menduplikasi nama kata kunci yang dicadangkan, penyorotan sintaks diterapkan ke teks di dalam kotak teks SQL Skrip. Untuk menghindari kebingungan, Anda harus melampirkan nama kolom dengan tanda kurung siku (untuk mengikuti konvensi Transact-SQL) atau backticks atau tanda kutip ganda (konvensi SQL ANSI).

Misalnya, dalam kueri berikut pada himpunan data Donor Darah, Waktu adalah nama kolom yang valid tetapi juga merupakan kata kunci yang dicadangkan.
```
SELECT Recency, Frequency, Monetary, Time, Class  
FROM t1  
WHERE Time between 3 and 20;  
```
Jika Anda menjalankan kueri apa adanya, kueri mungkin mengembalikan hasil yang benar, tetapi tergantung pada himpunan data, kueri mungkin mengembalikan kesalahan. Berikut adalah beberapa contoh cara menghindari masalah ini:
```
-- Transact-SQL  
SELECT [Recency], [Frequency], [Monetary], [Time], [Class]  
FROM t1  
WHERE [Time] between 3 and 20;  
-- ANSI SQL  
SELECT "Recency", "Frequency", "Monetary", "Time", "Class"  
FROM t1  
WHERE `Time` between 3 and 20;  
```
Catatan

Penyorotan sintaks tetap ada pada kata kunci bahkan setelah diapit dalam tanda kutip atau tanda kurung.
SQLite adalah case insensitive, kecuali untuk beberapa perintah yang memiliki varian case-sensitive dengan arti yang berbeda (GLOB vs glob).

Pernyataan SELECT

Dalam pernyataan tersebut SELECT , nama kolom yang menyertakan spasi atau karakter lain yang dilarang dalam pengidentifikasi harus terlampir dalam tanda kutip ganda, tanda kurung siku, atau karakter backtick (').

Misalnya, kueri ini mereferensikan himpunan data Two-Class Iris , t1tetapi satu nama kolom berisi karakter yang dilarang, sehingga nama kolom terlampir dalam tanda kutip.

SELECT class, "sepal-length" FROM t1;

Anda dapat menambahkan WHERE klausa untuk memfilter nilai dalam himpunan data.

SELECT class, "sepal-length" FROM t1 WHERE "sepal-length" >5.0;

Sintaks SQLite tidak mendukung TOP kata kunci, yang digunakan dalam Transact-SQL. Sebagai gantinya, Anda dapat menggunakan LIMIT kata kunci, atau FETCH pernyataan.

Misalnya, bandingkan kueri ini pada himpunan data Penyewaan Sepeda.

-- unsupported in SQLite  
SELECT  TOP 100 [dteday] FROM t1 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100   
SELECT  [dteday] FROM t1 LIMIT 100 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100. Note that FETCH is on a new line.  
SELECT  [dteday] FROM t1 - ;  
FETCH FIRST 100 rows ONLY;  
ORDER BY [dteday] DESC;

Gabungan

Contoh berikut menggunakan himpunan data Peringkat Restoran pada port input yang sesuai dengan t1, dan himpunan data Fitur Restoran pada port input yang sesuai t2dengan .

Pernyataan berikut bergabung dengan dua tabel untuk membuat dataset yang menggabungkan fitur restoran yang ditentukan dengan peringkat rata-rata untuk setiap restoran.

SELECT DISTINCT(t2.placeid),    
t2.name, t2.city, t2.state, t2.price, t2.alcohol,  
AVG(rating)  AS 'AvgRating'   
FROM t1   
JOIN t2  
ON t1.placeID = t2.placeID  
GROUP BY t2.placeid;

Fungsi agregat

Bagian ini memberikan contoh dasar dari beberapa fungsi agregat SQL umum, menggunakan SQLite.

Fungsi agregat yang saat ini didukung adalah: AVG, COUNT, MAX, MIN, SUM, TOTAL.

Kueri berikut mengembalikan himpunan data yang berisi ID restoran, bersama dengan peringkat rata-rata untuk restoran.

SELECT DISTINCT placeid,  
AVG(rating) AS ‘AvgRating’,  
FROM t1  
GROUP BY placeid

Bekerja dengan string

SQLite mendukung operator pipa ganda untuk menggabungkan string.

Pernyataan berikut membuat kolom baru dengan menggabungkan dua kolom teks.

SELECT placeID, name,   
(city || '-' || state) AS 'Target Region',   
FROM t1

Peringatan

Operator penggabungan string Transact-SQL tidak didukung: + (String Concatenation). Misalnya, ekspresi ('city + '-' + state) AS 'Target Region'dalam kueri contoh akan mengembalikan 0 untuk semua nilai.

Namun, meskipun operator tidak didukung untuk tipe data ini, tidak ada kesalahan yang muncul dalam Pembelajaran Mesin. Pastikan untuk memverifikasi hasil Apply SQL Transformation sebelum menggunakan himpunan data yang dihasilkan dalam percobaan.

COALESCE dan CASE

COALESCE mengevaluasi beberapa argumen, secara berurutan, dan mengembalikan nilai ekspresi pertama yang tidak dievaluasi ke NULL.

Misalnya, kueri pada himpunan data Steel Annealing Multi-Class mengembalikan bendera non-null pertama dari daftar kolom yang diasumsikan memiliki nilai yang saling eksklusif. Jika tidak ada bendera yang ditemukan, string "tidak ada" dikembalikan.

SELECT classes, family, [product-type],  
COALESCE(bt,bc,bf,[bw/me],bl, "none") AS TemperType  
FROM t1;

Pernyataan ini CASE berguna untuk menguji nilai dan mengembalikan nilai baru berdasarkan hasil yang dievaluasi. SQLite mendukung sintaks berikut untuk CASE pernyataan:

KASUS KETIKA [kondisi] LALU [ekspresi] ELSE [ekspresi] BERAKHIR
KASUS [ekspresi] KETIKA [nilai] LALU [ekspresi] ELSE [ekspresi] AKHIR

Misalnya, Anda sebelumnya telah menggunakan modul Konversi ke Nilai Indikator untuk membuat kolom fitur yang ditetapkan yang berisi nilai true-false. Kueri berikut menciutkan nilai dalam beberapa kolom fitur menjadi satu kolom multi-nilai.

SELECT userID, [smoker-0], [smoker-1],  
CASE  
WHEN [smoker-0]= '1' THEN 'smoker'   
WHEN [smoker-1]= '1' THEN 'nonsmoker'   
ELSE 'unknown'  
END AS newLabel  
FROM t1;

Contoh

Untuk contoh bagaimana modul ini dapat digunakan dalam eksperimen pembelajaran mesin, lihat sampel ini di Galeri AI Azure:

Terapkan Transformasi SQL: Menggunakan kumpulan data Peringkat Restoran, Fitur Restoran, dan Pelanggan Restoran untuk mengilustrasikan gabungan sederhana, pilih pernyataan, dan fungsi agregat.

Catatan teknis

Bagian ini berisi detail implementasi, tips, dan jawaban terkait pertanyaan yang sering diajukan.

Input selalu diperlukan pada port 1.
Jika himpunan data input memiliki nama kolom, kolom dalam himpunan data output akan menggunakan nama kolom dari himpunan data input.

Jika himpunan data input tidak memiliki nama kolom, nama kolom dalam tabel secara otomatis dibuat dengan menggunakan konvensi penamaan berikut: T1COL1, T1COL2, T1COL3, dan sebagainya, di mana angka menunjukkan indeks setiap kolom dalam himpunan data input.
Untuk pengidentifikasi kolom yang berisi spasi atau karakter khusus lain, selalu sertakan pengidentifikasi kolom dalam tanda kurung siku atau tanda kutip ganda saat merujuk ke kolom dalam klausul SELECT atau WHERE.

Pernyataan yang tidak didukung

Meskipun mendukung berbagai standar ANSI SQL, SQLite tidak menyertakan banyak fitur yang didukung oleh sistem database hubungan komersial. Untuk informasi selengkapnya, lihat SQL yang Dapat Dipahami SQLite. Selain itu, perhatikan batasan berikut saat membuat pernyataan SQL:

SQLite menggunakan pengetikan nilai yang dinamis, bukan menetapkan jenis ke kolom seperti pada sebagian besar sistem database hubungan. SQLite diketik dengan sederhana, dan memungkinkan konversi jenis implisit.
LEFT OUTER JOIN diimplementasikan, tetapi bukan RIGHT OUTER JOIN atau FULL OUTER JOIN.
Anda dapat menggunakan pernyataan RENAME TABLE dan ADD COLUMN dengan perintah ALTER TABLE, tetapi klausul lain tidak didiukung, termasuk DROP COLUMN, ALTER COLUMN, dan ADD CONSTRAINT.
Anda dapat membuat VIEW dalam SQLite, tetapi setelah itu view bersifat baca-saja. Anda tidak dapat menjalankan pernyataan DELETE, INSERT, atau UPDATE pada view. Namun, Anda dapat membuat pemicu yang diaktifkan dalam upaya DELETE, INSERT, atau UPDATE pada view dan menjalankan operasi lain dalam isi pemicu.

Selain menyediakan daftar fungsi yang tidak didukung di situs resmi SQLite, wiki ini juga menyediakan daftar fitur lain yang tidak didukung: SQLite - SQL yang Tidak Didukung

Input yang diharapkan

Nama	Jenis	Deskripsi
Tabel1	Tabel Data	Input himpunan data1
Tabel2	Tabel Data	Input himpunan data2
Tabel3	Tabel Data	Input himpunan data3

Parameter modul

Nama	Rentang	Jenis	Default	Deskripsi
Skrip Kueri SQL	apa pun	StreamReader		pernyataan kueri SQL

Output

Nama	Jenis	Deskripsi
Kumpulan data hasil	Tabel Data	Himpunan data output

Pengecualian

Pengecualian	Deskripsi
Kesalahan 0001	Pengecualian terjadi jika satu atau beberapa kolom tertentu dari himpunan data tidak dapat ditemukan.
Kesalahan 0003	Pengecualian terjadi jika satu atau beberapa himpunan data input null atau kosong.
Kesalahan 0069	SQL kesalahan logika atau database yang hilang

Untuk daftar kesalahan khusus untuk modul Studio (klasik), lihat Pembelajaran Mesin Kode kesalahan.

Untuk daftar pengecualian API, lihat Pembelajaran Mesin Kode Kesalahan REST API.

Lihat juga

Manipulasi
Transformasi Data
Daftar Modul A-Z

Bagikan melalui