Membuka Antarmuka SQL

Lapisan pemodelan semantik LookML Looker memungkinkan analis data menentukan dimensi, agregat, penghitungan, dan hubungan data dalam database SQL. Model LookML menyediakan integrasi Git dan kemampuan penggunaan kembali kode. Model LookML yang terstruktur dengan baik memungkinkan pengguna melakukan eksplorasi dan pelaporan data layanan mandiri mereka sendiri.

Model LookML adalah dasar dari data apa pun yang diminta dari Looker, baik permintaan tersebut berasal dari antarmuka Jelajah Looker di UI Looker, visualisasi tersemat di portal perusahaan Anda atau aplikasi pihak ketiga lainnya, atau aplikasi kustom yang dikembangkan dengan API Looker. Antarmuka SQL Terbuka memberikan akses ke model LookML ke aplikasi pihak ketiga yang mendukung Java Database Connectivity (JDBC). Aplikasi dapat terhubung ke model LookML seolah-olah itu adalah database, sehingga pengguna dapat memanfaatkan semua pekerjaan yang dilakukan oleh analis data mereka dalam model LookML, sambil menggunakan alat apa pun yang paling mereka sukai.

Cara Antarmuka SQL Terbuka menampilkan elemen project LookML

Untuk memahami cara Antarmuka SQL Terbuka menampilkan elemen project LookML, Anda harus memahami struktur project LookML.

Project LookML adalah kumpulan file yang mendeskripsikan objek, koneksi database, dan elemen antarmuka pengguna yang digunakan untuk menjalankan kueri SQL di Looker (lihat Istilah dan konsep LookML untuk mengetahui informasi selengkapnya). Konsep project LookML berikut terkait dengan Antarmuka SQL Terbuka:

  • Model LookML menentukan koneksi database dan satu atau beberapa Jelajah. Antarmuka SQL Terbuka menampilkan model sebagai skema database.
  • Jelajahi adalah pengelompokan logis dari satu atau beberapa tampilan dan hubungan join di antara tampilan tersebut. Antarmuka SQL Terbuka menampilkan Jelajah sebagai tabel database.
  • Tampilan menentukan kumpulan kolom (dimensi dan ukuran). Tampilan umumnya didasarkan pada tabel di database Anda atau tabel turunan. Tampilan dapat berisi kolom dari tabel database yang mendasarinya serta dimensi atau ukuran kustom yang mungkin diperlukan pengguna akhir Anda. Antarmuka Open SQL menampilkan kombinasi nama tampilan dan nama kolom sebagai nama kolom database. Misalnya, dimensi id di tampilan order_items ditampilkan oleh Open SQL Interface sebagai kolom database yang disebut order_items.id.

Jelajah Looker dapat menentukan hubungan join antara beberapa tampilan. Karena satu tampilan mungkin memiliki kolom dengan nama yang sama dengan kolom dalam tampilan lain, Open SQL Interface menyertakan nama tampilan dan nama kolom saat mereferensikan kolom. Oleh karena itu, gunakan format ini untuk mereferensikan nama kolom saat mengirim kueri ke Antarmuka SQL Terbuka:

`<view_name>.<field_name>`

Misalnya, jika ada Jelajah bernama order_items yang menggabungkan tampilan bernama customer dengan tampilan bernama product dan kedua tampilan ini memiliki dimensi id, Anda akan merujuk ke dua kolom id sebagai `customer.id` dan `product.id`. Untuk menggunakan nama yang sepenuhnya memenuhi syarat dengan nama Jelajah juga, Anda akan merujuk ke kedua kolom sebagai `order_items`.`customer.id` dan `order_items`.`product.id`. (Lihat Menggunakan tanda petik terbalik di sekitar ID database untuk mengetahui informasi tentang tempat meletakkan tanda petik terbalik saat merujuk ke ID database.)

Menyiapkan Antarmuka SQL Terbuka

Untuk menggunakan Open SQL Interface, lakukan langkah-langkah berikut:

  1. Verifikasi bahwa persyaratan terpenuhi.
  2. Download file driver JDBC Open SQL Interface.

Bagian berikut menjelaskan langkah-langkah ini.

Persyaratan

Komponen berikut diperlukan untuk menggunakan Open SQL Interface:

Mendownload driver JDBC Open SQL Interface

Driver JDBC Antarmuka SQL Terbuka Looker disebut avatica-<release_number>-looker.jar. Download versi terbaru dari GitHub di https://github.com/looker-open-source/calcite-avatica/releases.

Driver JDBC mengharapkan format URL berikut:

jdbc:looker:url=https://Looker instance URL

Contoh:

jdbc:looker:url=https://myInstance.cloud.looker.com

Class driver JDBC adalah:

org.apache.calcite.avatica.remote.looker.LookerDriver

Mengautentikasi ke Antarmuka SQL Terbuka

Antarmuka Open SQL mendukung tiga metode untuk autentikasi:

OAuth

Klien JDBC yang mendukung OAuth dapat dikonfigurasi untuk menggunakan server OAuth instance Looker. Ikuti langkah-langkah untuk mengonfigurasi autentikasi OAuth:

  1. Gunakan ekstensi API Explorer untuk mendaftarkan klien OAuth JDBC dengan instance Looker Anda sehingga instance Looker dapat mengenali permintaan OAuth. Lihat Mendaftarkan aplikasi klien OAuth untuk mengetahui petunjuknya.
  2. Login ke Looker dengan OAuth untuk meminta token akses. Lihat Melakukan login pengguna menggunakan OAuth untuk mengetahui contohnya.
  3. Gunakan objek Properti untuk meneruskan kredensial OAuth saat membuka koneksi JDBC ke Open SQL Interface.

Berikut adalah contoh yang menggunakan DriverManager#getConnection(<String>, <Properties>`):

String access_token = getAccessToken() //uses the Looker OAuth flow to get a token
String URL = "jdbc:looker:url=https://myInstance.cloud.looker.com"
Properties info = new Properties( );
info.put("token", access_token);
Connection conn = DriverManager.getConnection(URL, info);

Membuat token akses menggunakan kunci API

Daripada menggunakan alur OAuth standar untuk membuat token akses, Anda dapat mengikuti langkah-langkah berikut untuk menggunakan Looker API guna membuat token akses yang dapat diteruskan ke driver JDBC Open SQL Interface:

  1. Buat kunci API untuk pengguna Looker Anda seperti yang dijelaskan di halaman Setelan admin - Pengguna.

  2. Gunakan endpoint login API untuk instance Looker Anda. Respons menyertakan token akses dalam format Authorization: token <access_token>. Berikut adalah contoh perintah curl yang dapat Anda gunakan untuk membuat permintaan ini:

      curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\

  3. Teruskan nilai <access_token> respons sebagai token di objek Properties untuk meneruskan kredensial OAuth saat membuka koneksi JDBC ke Open SQL Interface.

Kunci API

Anda juga dapat menggunakan kunci API untuk mengautentikasi sebagai pengganti nama pengguna dan sandi. Kunci API dianggap kurang aman daripada OAuth dan mungkin hanya tersedia selama pratinjau Antarmuka SQL Terbuka. Lihat Kunci API untuk mengetahui informasi tentang cara membuat kunci API untuk instance Looker Anda.

Gunakan bagian Client ID dari kunci Looker API sebagai nama pengguna. Gunakan bagian Rahasia Klien untuk sandi.

Menjalankan kueri dengan Open SQL Interface

Perhatikan panduan berikut saat menjalankan kueri dengan Antarmuka SQL Terbuka:

Batasan LookML

Perhatikan hal-hal berikut saat Anda mengirim kueri ke Antarmuka SQL Terbuka:

Batasan SQL

Perhatikan batasan SQL berikut saat mengirim kueri ke Antarmuka SQL Terbuka:

Menggunakan tanda kutip tunggal terbalik di sekitar ID database

Saat mengirim kueri ke Antarmuka SQL Terbuka, gunakan tanda kutip terbalik di sekitar ID skema, tabel, dan kolom. Berikut cara menentukan elemen database menggunakan tanda petik terbalik dengan istilah Looker:

  • schema: `<model_name>`
  • table: `<explore_name>`

  • kolom: `<view_name>.<field_name>`

Berikut adalah contoh format pernyataan SELECT yang menggunakan elemen ini:

SELECT `view.field`
  FROM `model`.`explore`
  LIMIT 10;

Menentukan ukuran LookML dengan AGGREGATE()

Tabel database biasanya hanya berisi dimensi, data yang menjelaskan satu atribut tentang baris dalam tabel. Namun, project LookML dapat menentukan dimensi dan ukuran. Pengukuran adalah agregasi data di beberapa baris, seperti SUM, AVG, MIN, atau MAX. (Jenis ukuran lainnya juga didukung, lihat halaman Jenis ukuran untuk mengetahui daftar lengkap jenis ukuran LookML yang didukung.)

Dengan Antarmuka SQL Terbuka, Anda harus menetapkan ukuran LookML yang disertakan dalam kueri dengan menggabungkan ukuran (termasuk tanda petik terbalik) dalam fungsi khusus AGGREGATE(). Misalnya, gunakan ini untuk menentukan ukuran count dari tampilan orders:

AGGREGATE(`orders.count`)

Anda harus menggabungkan pengukuran LookML dalam fungsi AGGREGATE(), baik pengukuran tersebut berada dalam klausa SELECT, klausa HAVING, maupun klausa ORDER BY.

Jika tidak yakin apakah kolom merupakan ukuran LookML, Anda dapat menggunakan metode DatabaseMetaData.getColumns untuk mengakses metadata project LookML. Kolom IS_GENERATEDCOLUMN akan menunjukkan YES untuk ukuran LookML, dan NO untuk dimensi LookML. Lihat bagian Mengakses metadata database untuk mengetahui informasi selengkapnya.

Contoh

Berikut adalah contoh kueri yang menggunakan dimensi dan ukuran. Kueri ini mengambil dimensi negara bagian dan kota dari tampilan pelanggan dan ukuran jumlah total dari tampilan pesanan. Kedua tampilan ini digabungkan ke dalam Eksplorasi pesanan dalam model e-commerce. Untuk kota yang memiliki lebih dari 10 pesanan, respons kueri ini menampilkan 5 kota teratas berdasarkan jumlah pesanan:

SELECT `customers.state`, `customers.city`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Menentukan kolom dan parameter khusus filter dengan JSON_OBJECT

Antarmuka SQL Terbuka mendukung parameter dan kolom khusus filter.

Saat menjalankan kueri dengan Open SQL Interface, Anda dapat menerapkan parameter dan kolom khusus filter ke kueri dengan menyertakan panggilan konstruktor JSON_OBJECT dengan format berikut:

JSON_OBJECT(
    '<view>.<parameter name>', '<parameter value>',
    '<view>.<filter name>', '<Looker filter expression>'
)

Objek JSON dapat berisi nol atau beberapa pasangan nilai kunci filter dan nol atau beberapa pasangan nilai kunci parameter.

  • Kunci dalam konstruktor JSON_OBJECT harus berupa nama kolom atau parameter khusus filter.
  • Untuk kolom khusus filter, nilai untuk setiap kunci harus berupa ekspresi filter string Looker.
  • Untuk parameter, nilai untuk setiap kunci harus berupa nilai biasa yang ditentukan dalam definisi parameter.

Lihat bagian berikut untuk mengetahui contoh penggunaan parameter dan kolom khusus filter dengan Open SQL Interface.

Contoh parameter

Sebagai contoh penggunaan parameter dengan Antarmuka SQL Terbuka, jika tampilan customers memiliki parameter yang ditentukan di Looker sebagai berikut:

parameter: segment {
  type: string
  allowed_value: {
    label: "Small (less than 500)"
    value: "small_customers"
  }
  allowed_value: {
    label: "Larger (greater than 10,000)"
    value: "large_customers"
  }
  allowed_value: {
    label: "Medium customers (Between 500 and 10,000)"
    value: "medium_customers"
  }
}

Anda dapat mengirim kueri ini ke Open SQL Interface untuk menerapkan nilai parameter segment dari medium_customers ke kueri:

SELECT `customers.segment_size`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'customers.segment', 'medium_customers'
))
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Antarmuka SQL Terbuka akan meneruskan nilai parameter ini ke kueri di Looker, dan Looker akan menerapkan nilai medium_customers ke kolom apa pun di Jelajahi yang dikonfigurasi untuk menggunakan parameter segment. Lihat dokumentasi parameter untuk mengetahui informasi tentang cara kerja parameter di Looker.

Contoh kolom khusus filter

Anda dapat menggunakan kolom filter dengan Antarmuka SQL Terbuka. Misalnya, jika tampilan products memiliki dimensi dan kolom khusus filter yang ditentukan di Looker sebagai berikut:

filter: brand_select {
  type: string
  }

dimension: brand_comparitor {
  sql:
    CASE
      WHEN {% condition brand_select %} ${products.brand_name} {% endcondition %}
      THEN ${products.brand_name}
      ELSE "All Other Brands"
    END ;;
    }

Anda dapat menggunakan filter brand_select dengan Antarmuka SQL Terbuka dengan mengirimkan kueri seperti berikut:

SELECT `products.brand_comparator`, `products.number_of_brands`,
  AGGREGATE(`products.total_revenue`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'products.brand_select', '%Santa Cruz%'
))
GROUP BY `products.brand_comparator`
ORDER BY 3 DESC LIMIT 5;

Antarmuka SQL Terbuka akan menerapkan ekspresi filter string Looker %Santa Cruz% ke kueri di Looker. Lihat dokumentasi filter untuk mengetahui informasi tentang cara kerja kolom khusus filter di Looker.

Mengakses metadata database

Antarmuka SQL Terbuka mendukung subset antarmuka DatabaseMetaData JDBC standar, yang digunakan untuk mendapatkan informasi tentang database yang mendasarinya. Anda dapat menggunakan metode antarmuka DatabaseMetaData berikut untuk mendapatkan informasi tentang model LookML:

DatabaseMetadata.getSchemas

Tabel berikut menjelaskan hubungan model LookML dengan struktur database standar dalam respons metode antarmuka DatabaseMetadata.getSchemas.

Kolom respons getSchemas Deskripsi
TABLE_SCHEM Nama model LookML
TABLE_CATALOG (null)

DatabaseMetadata.getTables

Tabel berikut menjelaskan hubungan model LookML dengan struktur database dalam respons metode antarmuka DatabaseMetaData.getTables. Responsnya mencakup metadata JDBC standar serta metadata khusus Looker:

Kolom respons getTables Deskripsi
Metadata standar JDBC
TABLE_CAT (null)
TABLE_SCHEM Nama model LookML
TABLE_NAME Nama Jelajah LookML
TABLE_TYPE Selalu menampilkan nilai TABLE_TYPE
Metadata khusus Looker
DESCRIPTION Jelajahi deskripsi
LABEL Jelajahi label
TAGS Jelajahi tag

DatabaseMetadata.getColumns

Tabel berikut menjelaskan hubungan model LookML dengan struktur database dalam respons metode antarmuka DatabaseMetaData.getColumns. Responsnya mencakup metadata JDBC standar serta metadata khusus Looker:

Kolom respons getColumns Deskripsi
Metadata standar JDBC
TABLE_CAT (null)
TABLE_SCHEM Nama model LookML
TABLE_NAME Nama Jelajah LookML
COLUMN_NAME Nama kolom LookML dalam format `<view_name>.<field_name>`. Contoh, `orders.amount`.
DATA_TYPE Kode java.sql.Types kolom. Misalnya, kolom yesno Looker adalah kode jenis SQL 16 (BOOLEAN).
ORDINAL_POSITION Ordo kolom berbasis 1 dalam Jelajah (menggabungkan dimensi dan ukuran secara alfabetis menurut nama tampilan, lalu nama kolom)
IS_NULLABLE Selalu menampilkan nilai YES
IS_GENERATEDCOLUMN YES untuk ukuran, NO untuk dimensi
Metadata khusus Looker
DIMENSION_GROUP Nama grup dimensi jika kolom merupakan bagian dari grup dimensi. Jika kolom bukan bagian dari grup dimensi, kolom ini akan bernilai null.
DRILL_FIELDS Daftar kolom penelusuran yang ditetapkan untuk dimensi atau ukuran, jika ada
FIELD_ALIAS Alias untuk kolom, jika ada
FIELD_CATEGORY Apakah kolom tersebut berupa dimension atau measure
FIELD_DESCRIPTION Deskripsi kolom
FIELD_GROUP_VARIANT Jika kolom ditampilkan di bagian label grup kolom, FIELD_GROUP_VARIANT akan menentukan nama kolom yang lebih singkat yang ditampilkan di bagian label grup.
FIELD_LABEL Kolom label
FIELD_NAME Nama dimensi atau ukuran
HIDDEN Apakah kolom disembunyikan dari pemilih kolom di Jelajah (TRUE), atau apakah kolom terlihat di pemilih kolom di Jelajah (FALSE).
LOOKER_TYPE Jenis kolom LookML untuk dimensi atau ukuran
REQUIRES_REFRESH_ON_SORT Apakah kueri SQL harus dimuat ulang untuk mengurutkan ulang nilai kolom (TRUE), atau apakah nilai kolom dapat diurutkan ulang tanpa perlu memuat ulang kueri SQL (FALSE).
SORTABLE Apakah kolom dapat diurutkan (TRUE) atau tidak dapat diurutkan (FALSE)
TAGS Tag kolom
USE_STRICT_VALUE_FORMAT Apakah kolom menggunakan format nilai ketat (TRUE) atau tidak (FALSE)
VALUE_FORMAT String Format nilai untuk kolom
VIEW_LABEL Label tampilan untuk kolom
VIEW_NAME Nama tampilan tempat kolom ditentukan dalam project LookML

Mengidentifikasi kueri Antarmuka SQL Terbuka di UI Looker

Admin Looker dapat menggunakan UI Looker untuk mengidentifikasi kueri yang berasal dari Antarmuka SQL Terbuka:

  • Di halaman admin Kueri, kueri dari Antarmuka SQL Terbuka memiliki nilai Sumber "Antarmuka SQL". Nilai Pengguna akan menampilkan nama pengguna Looker yang menjalankan kueri. Anda dapat mengklik tombol Detail untuk kueri guna menampilkan informasi tambahan tentang kueri tersebut. Di dialog Details, Anda dapat mengklik SQL Interface query untuk melihat kueri SQL yang dikirim ke Looker dari Open SQL Interface.

  • Di Jelajahi Histori Aktivitas Sistem, kueri dari Antarmuka SQL Terbuka memiliki nilai Sumber "sql_interface". Nilai Email Pengguna akan menampilkan alamat email pengguna Looker yang menjalankan kueri. Anda dapat langsung membuka Jelajah Histori yang difilter berdasarkan "sql_interface" dengan menyisipkan alamat instance Looker Anda ke awal URL ini:

    https://Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=%22sql_interface%22

Repositori untuk dependensi pihak ketiga

Link berikut memberikan akses ke repositori yang dihosting Google untuk dependensi pihak ketiga yang digunakan oleh driver JDBC Looker:

https://third-party-mirror.googlesource.com/looker_sql_interface/+/refs/heads/master/third_party/