Cara membuat, memodifikasi, dan menggunakan kamus di ClickHouse - ApsaraDB for ClickHouse

Dalam pengembangan gudang data dan analitik data, jika data bisnis Anda berisi konstanta atau dapat dibagi menjadi tabel fakta dan dimensi, Anda dapat menggunakan kamus untuk menggantikan tabel dimensi. Metode ini menghindari penggunaan pernyataan JOIN dan meningkatkan efisiensi kueri. Topik ini menjelaskan cara membuat, memodifikasi, dan menggunakan kamus dalam ApsaraDB untuk ClickHouse.

Prasyarat

Sumber data dan tujuan harus berada di wilayah dan VPC yang sama, serta telah menambahkan alamat IP masing-masing ke daftar putih mereka. Jika kondisi ini tidak terpenuhi, Anda harus terlebih dahulu menyelesaikan masalah konektivitas jaringan. Untuk informasi lebih lanjut, lihat Cara menyelesaikan masalah konektivitas jaringan antara kluster tujuan dan sumber data.

Catatan

Untuk memeriksa alamat IP dari kluster ApsaraDB untuk ClickHouse, jalankan SELECT * FROM system.clusters;.

Untuk informasi lebih lanjut tentang cara mengonfigurasi daftar putih untuk ClickHouse, lihat Atur daftar putih.

Catatan

Anda tidak dapat mengelola kamus untuk kluster ApsaraDB untuk ClickHouse versi 21.8 atau lebih baru di Konsol. Sebagai gantinya, Anda dapat menggunakan pernyataan SQL untuk melihat dan membuat kamus. Untuk informasi lebih lanjut, lihat CREATE DICTIONARY.

Buat konfigurasi kamus

Masuk ke Konsol ApsaraDB untuk ClickHouse.
Di bilah navigasi atas, pilih wilayah tempat kluster yang ingin Anda kelola ditempatkan.
Pada halaman Clusters, pada tab Community Edition Instances, klik ID kluster target.
Di panel navigasi kiri, klik Dictionary Management untuk membuka halaman Manajemen Kamus.
Di sudut kanan atas, klik Add Dictionary Configuration.

Dalam kotak dialog Add Dictionary Configuration, edit templat dan tentukan isi kamus.

Topik ini hanya menjelaskan parameter utama untuk konfigurasi kamus. Untuk informasi lebih lanjut, lihat Kamus.

Parameter	Deskripsi
<name>	Nama kustom untuk kamus. Nama tersebut harus unik secara global.
<source>	Sumber data dari kamus. ApsaraDB untuk ClickHouse mendukung sumber data berikut: MySQL ClickHouse
<lifetime>	Frekuensi pembaruan untuk data dalam kamus. Unit: detik.
<layout>	Tata letak kamus mendefinisikan bagaimana data disimpan di memori. Kamus ApsaraDB untuk ClickHouse mendukung tujuh tata letak berikut: Kunci numerik flat hashed range_hashed cache Kunci komposit complex_key_hashed complex_key_cache ip_trie
<structure>	Struktur data dari kamus. Catatan Anda hanya dapat menambahkan satu node pada satu waktu.

Klik OK untuk membuat kamus data.
Setelah kamus dibuat, Anda dapat melihat, menghapus, atau memodifikasinya di kolom Tindakan.

Buat kamus dengan kueri DDL

Mulai dari versi 20.8, kluster ApsaraDB untuk ClickHouse mendukung Bahasa Definisi Data (DDL) untuk kamus. Anda dapat membuat kamus baru langsung menggunakan pernyataan DDL. Untuk memeriksa versi kluster Anda, masuk ke Konsol ApsaraDB untuk ClickHouse dan buka halaman Informasi Kluster.

CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
(
    key1 type1  [DEFAULT|EXPRESSION expr1] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID],
    key2 type2  [DEFAULT|EXPRESSION expr2] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID],
    attr1 type2 [DEFAULT|EXPRESSION expr3],
    attr2 type2 [DEFAULT|EXPRESSION expr4]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN]))
LAYOUT(LAYOUT_NAME([param_name param_value]))
LIFETIME([MIN val1] MAX val2)

Contoh berikut menunjukkan cara membuat kamus dari sumber yang berbeda.

Dari instans ApsaraDB untuk ClickHouse saat ini

Siapkan data.

Buat tabel sumber.

CREATE TABLE default.cities (id UInt64, city_name String)ENGINE = Memory;

Impor data uji.

INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');

Buat kamus.

CREATE DICTIONARY default.city_dict (
 id UInt64,
 city_name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE( USER 'username' PASSWORD 'password' TABLE 'cities' DB 'default' ))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360);

Dari instans ApsaraDB untuk ClickHouse lainnya

Siapkan data.

Buat tabel sumber.

CREATE TABLE default.cities ( id UInt64, city_name String)ENGINE = Memory;

Impor data uji.

INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');

Buat kamus.

CREATE DICTIONARY default.city_dict (
 id UInt64,
 city_name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 HOST 'cc-xxx.clickhouse.ads.aliyuncs.com'
 PORT 3306
 USER 'username'
 PASSWORD 'password'
 DB 'default'
 TABLE 'cities'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360);

Dari instans RDS MySQL

Siapkan data di instans RDS untuk MySQL.

Buat database dan tabel sumber.

CREATE DATABASE testdb;
CREATE TABLE testdb.cities ( id INT PRIMARY KEY, city_name VARCHAR(255));

Impor data uji.

INSERT INTO testdb.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');

Buat kamus.

CREATE DICTIONARY default.city_dict (
 id UInt64,
 city_name String
)
PRIMARY KEY id
SOURCE(MYSQL(
 HOST 'rm-xxx.mysql.rds.aliyuncs.com'
 PORT 3306
 USER 'username'
 PASSWORD 'password'
 DB 'testdb'
 TABLE 'cities'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360);

Tabel kamus dengan pernyataan kueri

Siapkan data.

Buat tabel sumber.

CREATE TABLE default.cities ( id UInt64, city_name String)ENGINE = Memory;

Impor data uji.

INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');

Buat kamus.

CREATE DICTIONARY default.my_dict
(
 id UInt64,
 city_name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
USER 'username' 
PASSWORD 'password' 
DB 'default' 
QUERY 'SELECT id, city_name FROM default.cities where id<2'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

Menggunakan kamus

Daftar Metadata Kamus

SELECT
    name,
    type,
    key,
    attribute.names,
    attribute.types,
    bytes_allocated,
    element_count,
    source
FROM system.dictionaries;

Ambil Data dari Kamus

Anda dapat menggunakan fungsi dictGet untuk mengambil data dari kamus. Untuk informasi lebih lanjut tentang fungsi dictGet, lihat dokumentasi resmi ClickHouse.

dictGet(<dict_name>, <attr_name>, <id_expr>)
dictGetOrDefault(<dict_name>, <attr_name>, <id_expr>, <default_value_expr>)