Cara membuat, mengubah, dan menggunakan kamus eksternal di Alibaba Cloud ClickHouse - ApsaraDB for ClickHouse

Jika data bisnis Anda berisi data konstan atau dapat dipisahkan menjadi tabel fakta dan tabel dimensi, gunakan kamus alih-alih tabel dimensi untuk menghindari operasi JOIN dan meningkatkan kinerja kueri. Buat, ubah, dan gunakan kamus eksternal di Alibaba Cloud ClickHouse.

Prasyarat

Sumber data dan kluster target harus berada di wilayah dan VPC yang sama serta memiliki daftar putih alamat IP yang saling mengizinkan. Jika tidak, selesaikan terlebih dahulu masalah konektivitas jaringan. Cara menyelesaikan masalah konektivitas jaringan antara kluster target dan sumber data.

Catatan

Jalankan SELECT * FROM system.clusters; untuk melihat alamat IP kluster Alibaba Cloud ClickHouse Anda.

Untuk mempelajari cara mengonfigurasi daftar putih alamat IP untuk ClickHouse, lihat Konfigurasi Daftar Putih Alamat IP.

Catatan penggunaan

Anda tidak dapat menggunakan Konsol untuk mengelola kamus pada kluster Alibaba Cloud ClickHouse yang menjalankan versi 21.8 atau lebih baru. Sebagai gantinya, kelola kamus tersebut dengan pernyataan SQL seperti CREATE DICTIONARY.

Buat konfigurasi kamus

Masuk ke Konsol ApsaraDB for ClickHouse.
Di pojok kiri atas halaman, pilih wilayah tempat kluster target berada.
Pada halaman Clusters, klik tab Clusters of Community-compatible Edition, lalu klik ID kluster target Anda.
Di panel navigasi sebelah kiri, klik Dictionary Management.
Di pojok kanan atas halaman, klik Add Dictionary Configuration.

Pada kotak dialog Add Dictionary Configuration, konfigurasikan parameter-parameter berikut.

Tabel berikut mencantumkan parameter utama kamus. Referensi lengkap parameter: Dictionaries.

Parameter	Deskripsi
<name>	Nama kamus kustom. Harus unik secara global.
<source>	Sumber data untuk kamus. Sumber yang didukung: MySQL ClickHouse
<lifetime>	Frekuensi pembaruan data kamus, dalam satuan detik.
<layout>	Tata letak dalam memori untuk kamus. Tersedia tujuh jenis: Kunci numerik flat hashed range_hashed cache Kunci komposit complex_key_hashed complex_key_cache ip_trie
<structure>	Struktur data kamus. Catatan Anda hanya dapat menambahkan satu node dalam satu waktu.

Setelah mengonfigurasi parameter, klik OK. Kamus akan dibuat.

Setelah pembuatan, Anda dapat melihat, mengubah, atau menghapus kamus dari kolom Actions.

DDL Kamus

Kluster yang menjalankan versi 20.8 atau lebih baru mendukung DDL kamus untuk membuat kamus eksternal secara langsung. Periksa versi kluster Anda di halaman Informasi Kluster di Konsol.

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 membuat kamus dari berbagai sumber data.

Sumber: Alibaba Cloud ClickHouse

Persiapkan 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);

Sumber adalah Alibaba Cloud ClickHouse lainnya

Persiapkan 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);

ApsaraDB RDS for MySQL

Persiapkan data di instans ApsaraDB RDS for 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);

Kueri kustom

Persiapkan 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);

Gunakan kamus

Kueri metadata kamus

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

Kueri data kamus

Gunakan dictGet untuk mengambil data dari kamus. Referensi sintaksis lengkap: dokumentasi resmi ClickHouse.

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