Bekerja dengan parameter kueri

Halaman ini menjelaskan cara bekerja dengan parameter kueri di editor Azure Databricks SQL.

Parameter kueri memungkinkan Anda membuat kueri Anda lebih dinamis dan fleksibel dengan menyisipkan nilai variabel saat runtime. Alih-alih mengkodekan nilai tertentu secara permanen ke dalam kueri, Anda dapat menentukan parameter untuk memfilter data atau memodifikasi output berdasarkan input pengguna. Pendekatan ini meningkatkan penggunaan kembali kueri, meningkatkan keamanan dengan mencegah injeksi SQL, dan memungkinkan penanganan skenario data yang lebih efisien.

Sintaks penanda parameter bernama

Penanda parameter bernama adalah variabel tempat penampung yang ditik. Gunakan sintaks ini untuk menulis kueri di bagian UI Azure Databricks berikut:

• Editor SQL (baru dan warisan)
• Notebooks
• Editor himpunan data dasbor AI/BI
• Ruang Genie AI/BI

Catatan

Editor himpunan data dasbor AI/BI dan editor SQL baru mendukung jenis data berikut dengan sintaks parameter bernama: parameter numerik, tanggal, dan tanggal dan waktu. Di semua antarmuka pengguna lainnya, hanya parameter jenis string yang didukung dengan menggunakan sintaks bernama parameter. Untuk menggunakan jenis parameter lain di luar editor ini, gunakan parameter kumis.

Sisipkan parameter ke dalam kueri SQL Anda dengan mengetik titik dua diikuti dengan nama parameter, seperti :parameter_name. Saat Anda menyertakan penanda parameter bernama dalam kueri, widget muncul di UI. Anda dapat menggunakan widget untuk mengedit jenis dan nama parameter.

Menambahkan penanda parameter bernama ke kueri

Contoh ini menambahkan penanda parameter ke kueri berikut:

SELECT
  trip_distance,
  fare_amount
FROM
  samples.nyctaxi.trips
WHERE
  fare_amount < 5

Kueri ini mengembalikan himpunan data yang hanya menyertakan jumlah tarif yang berada di bawah lima dolar. Gunakan langkah-langkah berikut untuk mengedit kueri untuk menggunakan parameter alih-alih nilai yang dikodekan secara permanen (5).

1. Hapus angka 5 dari kueri.
2. Ketik titik dua (:) diikuti dengan string fare_parameter. Baris terakhir kueri yang diperbarui harus mengatakan fare_amount < :fare_parameter.
3. Klik di dekat widget parameter. Dialog memperlihatkan bidang berikut:
   • Kata kunci: Kata kunci yang mewakili parameter dalam kueri. Anda tidak dapat mengedit bidang ini. Untuk mengubah kata kunci, edit penanda di kueri SQL.
   • Judul: Judul yang muncul di atas widget. Secara default, judulnya sama dengan kata kunci.
   • Tipe: Jenis yang didukung adalah Teks, Angka, Daftar Dropdown, Tanggal, Tanggal dan Waktu, serta Tanggal dan Waktu (dengan Detik). Standarnya adalah Teks.
4. Dalam dialog, ubah Tipe menjadi Angka.
5. Masukkan angka ke widget parameter dan klik Terapkan perubahan.
6. Klik Simpan untuk menyimpan kueri.

Contoh sintaks parameter bernama

Contoh berikut menunjukkan beberapa kasus penggunaan umum untuk parameter.

Sisipkan tanggal

Contoh berikut menyertakan parameter Tanggal yang membatasi hasil kueri ke rekaman setelah tanggal tertentu.

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  sum(o_totalprice) AS `Total Price`
FROM
  samples.tpch.orders
WHERE
  o_orderdate > :date_param
GROUP BY
  1,
  2

Sisipkan angka

Contoh berikut mencakup parameter Angka yang membatasi hasil ke rekaman di mana bidang lebih besar dari nilai parameter yang disediakan.

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  o_totalprice AS Price
FROM
  samples.tpch.orders
WHERE
  o_totalprice > :num_param

Sisipkan nama bidang

Dalam contoh berikut, field_param digunakan dengan IDENTIFIER fungsi untuk memberikan nilai ambang untuk kueri saat runtime. Nilai parameter harus berupa nama kolom dari tabel yang digunakan dalam kueri.

SELECT
  *
FROM
  samples.tpch.orders
WHERE
  IDENTIFIER(:field_param) < 10000

Sisipkan objek database

Contoh berikut membuat tiga parameter: catalog, schema, dan table.

SELECT
  *
FROM
  IDENTIFIER(:catalog || '.' || :schema || '.' || :table)

Lihat klausa IDENTIFIER.

Menggabungkan beberapa parameter

Anda dapat menyertakan parameter dalam fungsi SQL lainnya. Contoh ini memungkinkan penampil untuk memilih judul karyawan dan ID angka. Kueri menggunakan format_string fungsi untuk menggabungkan dua string dan memfilter pada baris yang cocok. Silakan lihat fungsi format_string.

SELECT
  o_orderkey,
  o_clerk
FROM
  samples.tpch.orders
WHERE
  o_clerk LIKE format_string('%s%s', :title, :emp_number)

Bekerja dengan string JSON

Anda dapat menggunakan parameter untuk mengekstrak atribut dari string JSON. Contoh berikut menggunakan from_json fungsi untuk mengonversi string JSON menjadi nilai struct. Mengganti string a sebagai nilai untuk parameter (param) mengembalikan atribut 1.

SELECT
  from_json('{"a": 1}', 'map<string, int>') [:param]

Membuat interval

Jenis ini INTERVAL mewakili rentang waktu dan memungkinkan Anda melakukan aritmatika dan operasi berbasis waktu. Contoh berikut menggunakan fungsi CAST untuk mentransmisikan parameter sebagai jenis interval. Nilai yang INTERVAL dihasilkan dapat digunakan untuk perhitungan berbasis waktu atau pemfilteran dalam kueri.

Lihat INTERVAL jenis untuk detail dan sintaks lengkap.

SELECT CAST(:param AS INTERVAL MINUTE)

Menambahkan rentang tanggal

Contoh berikut menunjukkan cara memfilter rekaman dalam rentang tanggal. Parameter tanggal dapat menggunakan .min atribut dan .max untuk menentukan rentang. Anda juga dapat menspesifikasikan rentang menggunakan dua parameter tanggal yang berbeda.

Menggunakan atribut min dan maks

1. Pilih nama parameter. Gunakan .min untuk mengakses nilai rentang minimum dan .max untuk mengakses nilai rentang maksimum, seperti dalam kueri berikut:

   SELECT * FROM samples.nyctaxi.trips
   WHERE tpep_pickup_datetime
   BETWEEN :date_range.min AND :date_range.max
   

2. Atur jenis parameter ke Date.

3. Atur jenis Widget ke Rentang.

Menggunakan dua parameter tanggal

Contoh berikut menggunakan dua parameter, start_date dan end_date, untuk mengatur rentang tanggal.

SELECT * FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime
BETWEEN :start_date AND :end_date

Parameterisasi rollup menurut hari, bulan, atau tahun

Contoh berikut menggabungkan data perjalanan taksi pada tingkat granularitas berparameter. Fungsi ini DATE_TRUNCtpep_pickup_datetime memotong nilai berdasarkan :date_granularity nilai parameter, seperti DAY, , MONTHatau YEAR. Tanggal terpotong diberi alias sebagai date_rollup dan digunakan dalam GROUP BY klausa.

SELECT DATE_TRUNC(:date_granularity, tpep_pickup_datetime) AS
  date_rollup,
  COUNT(*) AS total_trips
FROM samples.nyctaxi.trips
GROUP BY date_rollup

Menggunakan beberapa nilai dalam satu kueri

Contoh berikut menggunakan fungsi ARRAY_CONTAINS untuk memfilter daftar nilai. Fungsi TRANSFORM, dan SPLIT memungkinkan beberapa nilai yang dipisahkan koma untuk diteruskan sebagai parameter string.

Nilai :list_parameter mengambil daftar nilai yang dipisahkan koma. Fungsi SPLIT mengurai daftar tersebut, memisahkan nilai yang dipisahkan koma menjadi array. Fungsi mengubah TRANSFORM setiap elemen dalam array dengan menghapus spasi kosong apa pun. Fungsi ARRAY_CONTAINS memeriksa apakah nilai dropoff_zip dari tabel trips terkandung dalam array nilai yang diteruskan sebagai list_parameter.

SELECT * FROM samples.nyctaxi.trips WHERE
  array_contains(
    TRANSFORM(SPLIT(:list_parameter, ','), s -> TRIM(s)),
    dropoff_zip
  )

Catatan

Contoh ini berfungsi untuk nilai string. Untuk mengubah kueri untuk tipe data lain, seperti daftar bilangan bulat, bungkus operasi TRANSFORM dengan operasi CAST untuk mengonversi nilai string menjadi jenis data yang diinginkan.

Perubahan sintaks

Tabel berikut menunjukkan kasus penggunaan umum untuk parameter, sintaks 'mustache' asli dari Databricks SQL, dan sintaks yang setara menggunakan sintaks penanda parameter bernama.

Kasus penggunaan parameter	Sintaks parameter kumis	Sintaks penanda parameter bernama
Muat hanya data sebelum tanggal yang ditentukan	WHERE date_field < '{{date_param}}' Anda harus menyertakan tanda kutip di sekitar parameter tanggal dan tanda kurung kurawal.	WHERE date_field < :date_param
Muat hanya data yang kurang dari nilai numerik tertentu	WHERE price < {{max_price}}	WHERE price < :max_price
Membandingkan dua string	WHERE region = {{region_param}}	WHERE region = :region_param
Tentukan tabel yang digunakan dalam kueri	SELECT * FROM {{table_name}}	SELECT * FROM IDENTIFIER(:table) Saat pengguna memasukkan parameter ini, mereka harus menggunakan namespace tiga tingkat lengkap untuk mengidentifikasi tabel.
Tentukan katalog, skema, dan tabel yang digunakan dalam kueri secara independen	SELECT * FROM {{catalog}}.{{schema}}.{{table}}	SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Menggunakan parameter sebagai templat dalam string yang lebih panjang dan diformat	"({{area_code}}) {{phone_number}}" Nilai parameter secara otomatis digabungkan sebagai string.	format_string("(%d)%d, :area_code, :p hone_number) Lihat Menggabungkan beberapa parameter untuk contoh lengkap.
Membuat interval	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Sintaks parameter Mustache

Penting

Bagian berikut ini berlaku untuk sintaks kueri yang bisa Anda gunakan hanya di editor SQL. Itu berarti jika Anda menyalin dan menempelkan kueri menggunakan sintaksis ini ke antarmuka Azure Databricks lainnya, seperti editor himpunan data buku catatan atau dasbor AI/BI, kueri harus disesuaikan secara manual untuk menggunakan penanda parameter bernama sebelum berjalan tanpa kesalahan.

Di editor SQL, string apa pun antara kurung kurawal {{ }} ganda diperlakukan sebagai parameter kueri. Widget muncul di atas panel hasil tempat Anda mengatur nilai parameter. Meskipun Azure Databricks umumnya merekomendasikan penggunaan penanda parameter bernama, beberapa fungsionalitas hanya didukung menggunakan sintaks parameter kumis.

Gunakan sintaks parameter kumis untuk fungsionalitas berikut:

• Filter dasbor warisan
• Parameter dropdown dengan banyak nilai
• parameter drop-down berbasis kueri

Menambahkan parameter kumis

1. Ketik Cmd + I. Parameter disisipkan pada tanda sisipan teks dan dialog Tambahkan Parameter muncul.
   • Kata kunci: Kata kunci yang mewakili parameter dalam kueri.
   • Judul: Judul yang muncul di atas widget. Secara default, judulnya sama dengan kata kunci.
   • Tipe: Jenis yang didukung adalah teks, angka, tanggal, tanggal dan waktu, tanggal dan waktu (dengan detik), daftar dropdown, dan daftar dropdown berbasis kueri. Standarnya adalah Teks.
2. Masukkan kata kunci, secara opsional ambil alih judul, dan pilih jenis parameter.
3. Klik Tambahkan Parameter.
4. Di widget parameter, atur nilai parameter.
5. Klik Terapkan perubahan.
6. Klik Simpan.

Atau, ketik kurung kurawal {{ }} ganda dan klik ikon roda gigi di dekat widget parameter untuk mengedit pengaturan.

Untuk menjalankan kembali kueri dengan nilai parameter yang berbeda, masukkan nilai dalam widget dan klik Terapkan Perubahan.

Mengedit parameter kueri

Untuk mengedit parameter, klik ikon roda gigi di sebelah widget parameter. Untuk mencegah pengguna yang tidak memiliki kueri mengubah parameter, klik Perlihatkan Hasil Saja. Dialog <Keyword> parameter muncul.

Menghapus parameter kueri

Untuk menghapus parameter kueri, hapus parameter dari kueri Anda. Widget parameter menghilang, dan Anda dapat menulis ulang kueri Anda menggunakan nilai statis.

Mengubah urutan parameter

Untuk mengubah urutan parameter ditampilkan, Anda dapat mengklik dan menyeret setiap parameter ke posisi yang diinginkan.

Jenis parameter kueri

• Teks
• Angka
• Daftar Dropdown
• Daftar DropdownQuery-Based
• Tanggal dan Waktu

Teks

Mengambil string sebagai input. Tanda kutip garis miring terbalik, tunggal, dan ganda lolos, dan Azure Databricks menambahkan tanda kutip ke parameter ini. Misalnya, string seperti mr's Li"s diubah menjadi 'mr\'s Li\"s' Contoh penggunaan ini dapat berupa

SELECT * FROM users WHERE name={{ text_param }}

Angka

Mengambil nomor sebagai inputnya. Contoh penggunaan ini bisa jadi

SELECT * FROM users WHERE age={{ number_param }}

Daftar Menurun

Untuk membatasi cakupan kemungkinan nilai parameter saat menjalankan kueri, gunakan jenis parameter Daftar Dropdown. Contohnya adalah SELECT * FROM users WHERE name='{{ dropdown_param }}'. Saat dipilih dari panel pengaturan parameter, kotak teks muncul di mana Anda memasukkan nilai yang diizinkan, setiap nilai dipisahkan oleh baris baru. Menu tarik-turun adalah parameter teks. Untuk menggunakan tanggal atau tanggal dan waktu di Daftar Dropdown Anda, masukkan dalam format yang diperlukan sumber data Anda. String tidak lolos. Anda dapat memilih antara dropdown nilai tunggal atau multinilai.

• Nilai tunggal: Tanda kutip tunggal di sekitar parameter diperlukan.
• Multinilai : Alihkan opsi Izinkan beberapa nilai. Di menu tarik-turun Kutipan, pilih apakah akan membiarkan parameter seperti yang dimasukkan (tanpa tanda kutip) atau membungkus parameter dengan tanda kutip tunggal atau ganda. Anda tidak perlu menambahkan tanda kutip di sekitar parameter jika Anda memilih tanda kutip.

Ubah klausa Anda WHERE untuk menggunakan IN kata kunci dalam kueri Anda.

SELECT ...
FROM   ...
WHERE field IN ( {{ Multi Select Parameter }} )

Widget multi-pilihan parameter memungkinkan Anda meneruskan beberapa nilai ke database. Jika Anda memilih opsi Tanda Kutip Ganda untuk parameter Kutipan , kueri Anda mencerminkan format berikut:

Daftar Dropdown Query-Based

Mengambil hasil kueri sebagai inputnya. Ini memiliki perilaku yang sama dengan parameter Daftar Dropdown . Anda harus menyimpan kueri dari daftar dropdown Databricks SQL agar dapat digunakan sebagai input dalam kueri lainnya.

1. Klik daftar menurun berbasis kueri di bawah Tipe di panel pengaturan.
2. Klik bidang Kueri dan pilih kueri. Jika kueri target Anda mengembalikan sejumlah besar rekaman, performanya akan menurung.

Jika kueri target Anda mengembalikan lebih dari satu kolom, Databricks SQL menggunakan pertama. Jika kueri target Anda mengembalikan kolom name dan value, Databricks SQL mengisi widget pemilihan parameter dengan kolom name tetapi menjalankan kueri dengan valueterkait .

Misalnya, kueri berikut mengembalikan data dalam tabel.

SELECT user_uuid AS 'value', username AS 'name'
FROM users

nilai	nama
1001	Yohanes Smith
1002	Jane Doe
1003	Tabel Bobby

Saat Azure Databricks menjalankan kueri, nilai yang diteruskan ke database adalah 1001, 1002, atau 1003.

Tanggal dan Waktu

Azure Databricks memiliki beberapa opsi untuk membuat parameter nilai tanggal dan tanda waktu, termasuk opsi untuk menyederhanakan parameterisasi rentang waktu. Pilih dari tiga opsi presisi yang bervariasi:

Opsi	Presisi	Jenis
Tanggal	hari	DATE
Tanggal dan Waktu	menit	TIMESTAMP
Tanggal dan Waktu (dengan detik)	detik	TIMESTAMP

Saat memilih opsi parameter rentang , Anda membuat dua parameter yang ditunjuk oleh akhiran .start dan .end. Semua opsi meneruskan parameter ke kueri Anda sebagai literal string; Azure Databricks mengharuskan Anda membungkus nilai tanggal dan waktu dalam tanda kutip tunggal ('). Contohnya:

-- Date parameter
SELECT *
FROM usage_logs
WHERE date = '{{ date_param }}'

-- Date and Time Range parameter
SELECT *
FROM usage_logs
WHERE modified_time > '{{ date_range.start }}' and modified_time < '{{ date_range.end }}'

Parameter tanggal menggunakan antarmuka pemilihan kalender dan default ke tanggal dan waktu saat ini.

Catatan

Parameter Rentang Tanggal hanya mengembalikan hasil yang benar untuk kolom jenis DATE. Untuk kolom TIMESTAMP, gunakan salah satu opsi Tanggal dan Rentang Waktu.

Nilai tanggal dan rentang tanggal dinamis

Saat Anda menambahkan parameter tanggal atau rentang tanggal ke kueri, widget pilihan menampilkan ikon petir biru. Klik untuk menampilkan nilai dinamis seperti today, yesterday, this week, last week, last month, atau last year. Nilai-nilai ini diperbarui secara dinamis.

Penting

Tanggal dinamis dan rentang tanggal tidak kompatibel dengan kueri terjadwal.

Menggunakan parameter kueri pada dashboard

Secara opsional, kueri dapat menggunakan parameter atau nilai statis. Saat visualisasi berdasarkan kueri berparameter ditambahkan ke dasbor, visualisasi dapat dikonfigurasi untuk menggunakan:

• Parameter widget

  Parameter widget khusus untuk visualisasi tunggal di dasbor, muncul di panel visualisasi, dan nilai parameter yang ditentukan hanya berlaku untuk kueri yang mendasar visualisasi.

• Parameter dasbor

  Parameter dasbor dapat berlaku untuk beberapa visualisasi. Saat Anda menambahkan visualisasi berdasarkan kueri berparameter ke dasbor, parameter akan ditambahkan sebagai parameter dasbor secara default. Parameter dasbor dikonfigurasi untuk satu atau beberapa visualisasi di dasbor dan muncul di bagian atas dasbor. Nilai parameter yang ditentukan untuk parameter dasbor berlaku untuk visualisasi yang menggunakan kembali parameter dasbor tertentu. Dasbor dapat memiliki beberapa parameter, yang masing-masing dapat berlaku untuk beberapa visualisasi dan bukan yang lain.

• Nilai statis

  Nilai statis digunakan sebagai pengganti parameter yang merespons perubahan. Nilai statis memungkinkan Anda untuk membuat kode keras nilai sebagai pengganti parameter. Mereka membuat parameter "menghilang" dari dasbor atau widget tempat sebelumnya muncul.

Saat Anda menambahkan visualisasi yang berisi kueri berparameter, Anda bisa memilih judul dan sumber untuk parameter dalam kueri visualisasi dengan mengklik ikon pensil yang sesuai. Anda juga dapat memilih kata kunci dan nilai default. Lihat Properti parameter.

Setelah menambahkan visualisasi ke dasbor, akses antarmuka pemetaan parameter dengan mengklik menu kebab di kanan atas widget dasbor lalu klik Ubah pengaturan widget.

Properti parameter

• Judul: Nama tampilan yang muncul di samping pemilih nilai di dasbor Anda. Ini default ke parameter Kata Kunci. Untuk mengeditnya, klik ikon pensil . Judul tidak ditampilkan untuk parameter dasbor statis karena pemilih nilai disembunyikan. Jika Anda memilih nilai statis sebagaiSumber Nilai , bidang Judul berwarna abu-abu.

• Kata kunci: Literal string untuk parameter ini dalam kueri yang mendasarinya. Ini berguna untuk penelusuran kesalahan jika dasbor Anda tidak mengembalikan hasil yang diharapkan.

• Nilai Default: Nilai yang digunakan jika tidak ada nilai lain yang ditentukan. Untuk mengubah ini dari layar kueri, jalankan kueri dengan nilai parameter yang Anda inginkan dan klik tombol Simpan .

• Sumber Nilai: Sumber nilai parameter. Klik ikon pensil untuk memilih sumber.

  • Parameter dasbor baru: Buat parameter tingkat dasbor baru. Ini memungkinkan Anda menetapkan nilai parameter di satu tempat di dasbor Anda dan memetakannya ke beberapa visualisasi.
  • Parameter dasbor yang ada: Petakan parameter ke parameter dasbor yang ada. Anda harus menentukan parameter dasbor yang sudah ada sebelumnya.
  • Parameter widget: Menampilkan pemilih nilai di dalam widget dasbor Anda. Ini berguna untuk parameter satu kali yang tidak dibagikan di antara widget.
  • Nilai statis: Pilih nilai statis untuk widget, terlepas dari nilai yang digunakan pada widget lain. Nilai parameter yang dipetakan secara statis tidak menampilkan pemilih nilai di mana saja di dasbor, yang lebih ringkas. Ini memungkinkan Anda memanfaatkan fleksibilitas parameter kueri tanpa mengacaukan antarmuka pengguna di dasbor ketika parameter tertentu tidak diharapkan sering berubah.

  

Tanya Jawab Umum (FAQ)

• Dapatkah saya menggunakan kembali parameter yang sama beberapa kali dalam satu kueri?
• Bisakah saya menggunakan beberapa parameter dalam satu kueri?

Bisakah saya menggunakan kembali parameter yang sama beberapa kali dalam satu kueri?

Ya. Gunakan identifikasi yang sama dalam kurung kurawal. Contoh ini menggunakan parameter {{org_id}} dua kali.

SELECT {{org_id}}, count(0)
FROM queries
WHERE org_id = {{org_id}}

Bisakah saya menggunakan beberapa parameter dalam satu kueri?

Ya. Gunakan nama unik untuk setiap parameter. Contoh ini menggunakan dua parameter: {{org_id}} dan {{start_date}}.

SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'