Impor data dari OSS ke Alibaba Cloud ClickHouse menggunakan mesin tabel dan fungsi tabel - ApsaraDB for ClickHouse

Impor data dari Object Storage Service (OSS) ke Alibaba Cloud ClickHouse menggunakan mesin tabel atau fungsi tabel untuk mengkueri, menganalisis, dan memproses log.

Prasyarat

Aktifkan Object Storage Service (OSS). Untuk informasi selengkapnya, lihat Activate OSS.
Buat bucket di wilayah yang sama dengan Alibaba Cloud ClickHouse. Untuk petunjuk cara membuat bucket, lihat Create a bucket.
Akun yang digunakan untuk mengakses OSS harus memiliki izin baca pada objek di dalam bucket. Untuk informasi selengkapnya, lihat Overview.

Persiapan data

Simpan data uji berikut sebagai test.csv dan unggah ke OSS. Pemisah kolom default adalah koma (,). Untuk petunjuk pengunggahan, lihat Upload files.

1,yang,32,shanghai,http://example1.com
2,wang,22,beijing,http://example2.com
3,xiao,23,shenzhen,http://example3.com
4,jess,45,hangzhou,http://example4.com
5,jack,14,shanghai,http://example5.com
6,tomy,25,hangzhou,http://example6.com
7,lucy,45,shanghai,http://example7.com
8,tengyin,26,shanghai,http://example8.com
9,wangli,27,shenzhen,http://example9.com
10,xiaohua,37,shanghai,http://example10.com

Langkah-langkah

Sambungkan ke kluster Alibaba Cloud ClickHouse. Untuk informasi selengkapnya, lihat Connect to a cluster.
Buat tabel lokal bernama oss_test_tbl_local.
Penting
- Skema tabel Alibaba Cloud ClickHouse harus sesuai dengan skema tabel eksternal OSS dan format data di OSS. Untuk mencegah kegagalan penguraian dan gangguan kluster, tangani bidang null dengan hati-hati.
- Pilih pernyataan CREATE TABLE yang sesuai berdasarkan konfigurasi replika kluster Anda. Anda dapat melihat konfigurasi replika di halaman Cluster Information pada bagian Cluster Properties di Konsol.
- Anda dapat memilih mesin tabel berdasarkan kebutuhan bisnis Anda. Untuk informasi selengkapnya, lihat Table engines.
Edisi satu replika
```
CREATE TABLE oss_test_tbl_local ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = MergeTree()
ORDER BY id;
```
Edisi dua replika
```
CREATE TABLE oss_test_tbl_local ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
ORDER BY id;
```

(Opsional) Buat tabel terdistribusi bernama oss_test_tbl_distributed.

Catatan

Buat tabel terdistribusi jika Anda ingin mendistribusikan data ke semua tabel lokal di kluster.

CREATE TABLE oss_test_tbl_distributed ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = Distributed(default, default, oss_test_tbl_local, rand());

Impor data dari OSS ke Alibaba Cloud ClickHouse.

Alibaba Cloud ClickHouse mendukung dua metode untuk mengimpor data dari OSS: mesin tabel dan fungsi tabel.

Penting

Pilih pernyataan yang sesuai berdasarkan versi kluster Anda. Anda dapat melihat versinya di halaman Cluster Information pada bagian Cluster Properties di Konsol.

Metode 1: Gunakan mesin tabel

Buat tabel eksternal OSS bernama oss_test_tbl.

Sintaksis untuk versi sebelum 22.8

CREATE TABLE <table_name> [ON CLUSTER cluster]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>');

Sintaksis untuk versi 22.8 dan seterusnya

CREATE TABLE <table_name> [ON CLUSTER cluster]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('https://<BucketName>.<oss-endpoint>/<file-name>', '<access-key-id>', '<access-key-secret>', '<file-format-name>');

Parameter:

Parameter	Deskripsi
table_name	Nama tabel.
ON CLUSTER	Membuat tabel lokal di setiap node. Atur parameter ini menjadi `ON CLUSTER default`.
col_name1,col_name2	Nama kolom.
col_type1,col_type2	Tipe data kolom. Penting Skema tabel eksternal OSS harus sesuai dengan data di OSS.
BucketName	Nama bucket.
oss-endpoint	Titik akhir yang digunakan untuk mengakses OSS. Untuk informasi selengkapnya, lihat Regions and endpoints. Penting Pastikan bucket dan kluster Alibaba Cloud ClickHouse Anda berada di wilayah yang sama, dan akses OSS melalui Virtual Private Cloud (VPC).
file-name	Nama file.
access-key-id	ID AccessKey yang digunakan untuk mengakses data di OSS. Untuk informasi cara mendapatkan ID AccessKey, lihat Obtain an AccessKey pair.
access-key-secret	Rahasia AccessKey yang digunakan untuk mengakses data di OSS. Untuk informasi cara mendapatkan Rahasia AccessKey, lihat Obtain an AccessKey pair.
oss-file-path	Jalur penyimpanan file. Jalurnya biasanya dalam format `oss://<bucket-name>/<path-to-file>`. Catatan Parameter `oss-file-path` mendukung pencocokan kabur dengan menggunakan wildcard. Untuk informasi selengkapnya, lihat Wildcard matching for OSS storage paths.
file-format-name	Format file. Topik ini menggunakan CSV sebagai contoh.

Contoh pernyataan:

Contoh untuk versi sebelum 22.8

CREATE TABLE oss_test_tbl on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://testBucketName/test.csv', 'CSV');

Contoh untuk versi 22.8 dan seterusnya

CREATE TABLE oss_test_tbl on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'STS.****************', 'STS.****************','CSV')

Impor data dari tabel eksternal OSS oss_test_tbl ke tabel terdistribusi oss_test_tbl_distributed.

Catatan
Jika Anda hanya perlu mengimpor data ke tabel lokal, ganti nama tabel terdistribusi dengan nama tabel lokal dalam pernyataan INSERT.
```
INSERT INTO oss_test_tbl_distributed SELECT * FROM oss_test_tbl;
```
Jika file CSV Anda menggunakan pemisah selain koma (,), gunakan pengaturan format_csv_delimiter dalam pernyataan INSERT untuk menentukannya. Misalnya, jika file menggunakan tanda pipa vertikal (|) sebagai pemisah, jalankan pernyataan berikut:
```
INSERT INTO oss_test_tbl_distributed SELECT * FROM oss_test_tbl settings format_csv_delimiter='|';
```

Metode 2: Gunakan fungsi tabel

Sintaksis untuk versi sebelum 22.8

INSERT INTO <table_name> SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<col_name> <col_type>(,...)');

Sintaksis untuk versi 22.8 dan seterusnya

INSERT INTO <table_name> SELECT * FROM oss('https://<BucketName>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

Untuk informasi selengkapnya tentang parameter, lihat Parameter descriptions.

Contoh pernyataan:

Contoh untuk versi sebelum 22.8

INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://testBucketName/test.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

Contoh untuk versi 22.8 dan seterusnya

INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'STS.****************', 'STS.****************', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

Jika file CSV Anda menggunakan pemisah selain koma (,), gunakan pengaturan format_csv_delimiter dalam pernyataan INSERT untuk menentukannya. Misalnya, jika file menggunakan tanda pipa vertikal (|) sebagai pemisah, jalankan pernyataan berikut:

INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>',  '<col_name> <col_type>(,...)') settings format_csv_delimiter='|';

Kueri tabel terdistribusi oss_test_tbl_distributed untuk memverifikasi impor data.

SELECT * FROM oss_test_tbl_distributed;

Hasil yang diharapkan:

┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
│  1 │  yang     │   32  │  shanghai  │  http://example1.com  │
│  2 │  wang     │   22  │  beijing   │  http://example2.com  │
│  3 │  xiao     │   23  │  shenzhen  │  http://example3.com  │
│  4 │  jess     │   45  │  hangzhou  │  http://example4.com  │
│  5 │  jack     │   14  │  shanghai  │  http://example5.com  │
│  6 │  tomy     │   25  │  hangzhou  │  http://example6.com  │
│  7 │  lucy     │   45  │  shanghai  │  http://example7.com  │
│  8 │  tengyin  │   26  │  shanghai  │  http://example8.com  │
│  9 │  wangli   │   27  │  shenzhen  │  http://example9.com  │
│ 10 │  xiaohua  │   37  │  shanghai  │  http://example10.com │
└────┴───────────┴───────┴────────────┴───────────────────────┘

Pencocokan wildcard untuk jalur penyimpanan OSS

Untuk menyederhanakan analisis beberapa file kecil yang menggunakan konvensi penamaan yang sama, parameter oss-file-path mendukung wildcard berikut:

*: Mencocokkan nama file atau direktori apa pun. Misalnya, /dir/* mencocokkan semua file di direktori /dir.
{x,y,z}: Mencocokkan nilai apa pun yang tercantum dalam tanda kurung kurawal. Misalnya, file_{x,y,z} mencocokkan file_x, file_y, atau file_z.
{num1..num2}: Mencocokkan nilai apa pun dalam rentang yang diperluas dari num1 hingga num2. Misalnya, file_{1..3} mencocokkan file_1, file_2, dan file_3.
?: Mencocokkan satu karakter apa pun. Misalnya, file_? mencocokkan file_a, file_b, file_c, dan seterusnya.

Contoh

File yang diunggah menggunakan struktur direktori berikut.

oss://testBucketName/
               doc-data/
                    oss-import/
                        small_files/
                            access_log_csv_1.txt
                            access_log_csv_2.txt
                            access_log_csv_3.txt

Contoh:

oss://testBucketName/doc-data/oss-import/small_files/*
oss://testBucketName/doc-data/oss-import/small_files/access*
oss://testBucketName/doc-data/oss-import/small_files/access_log_csv_{1,2,3}.txt
oss://testBucketName/doc-data/oss-import/*/access_log_csv_{1,2,3}.txt
oss://testBucketName/doc-data/oss-import/*/*
oss://testBucketName/doc-data/oss-import/*/access_log_csv_{1..3}.txt
oss://testBucketName/doc-data/oss-import/*/access_log_csv_?.txt

Prasyarat

Persiapan data

Langkah-langkah

Edisi satu replika

Edisi dua replika

Metode 1: Gunakan mesin tabel

Sintaksis untuk versi sebelum 22.8

Sintaksis untuk versi 22.8 dan seterusnya

Contoh untuk versi sebelum 22.8

Contoh untuk versi 22.8 dan seterusnya

Metode 2: Gunakan fungsi tabel

Sintaksis untuk versi sebelum 22.8

Sintaksis untuk versi 22.8 dan seterusnya

Contoh untuk versi sebelum 22.8

Contoh untuk versi 22.8 dan seterusnya

Pencocokan wildcard untuk jalur penyimpanan OSS