Read and write data in C-Store tables with Spark SQL-AnalyticDB-Alibaba Cloud

Anda dapat menggunakan Spark SQL di kluster AnalyticDB for MySQL untuk membaca dari dan menulis ke tabel C-Store yang menggunakan engine XUANWU, baik dalam mode real-time maupun batch.

Prasyarat

Kluster AnalyticDB for MySQL Edisi Perusahaan, Edisi Dasar, atau Edisi Data Lakehouse telah dibuat.
Catatan
- Kluster Edisi Data Lakehouse harus memiliki lebih dari 0 ACU sumber daya penyimpanan yang dipesan.
- Kluster Edisi Perusahaan harus memiliki lebih dari 0 ACU sumber daya yang dipesan.
- Kluster Edisi Dasar harus memiliki lebih dari 0 ACU sumber daya yang dipesan.
Kelompok sumber daya pekerjaan telah dibuat untuk kluster AnalyticDB for MySQL Edisi Perusahaan, Edisi Dasar, atau Edisi Data Lakehouse.
Akun database telah dibuat untuk kluster AnalyticDB for MySQL Edisi Perusahaan, Edisi Dasar, atau Edisi Data Lakehouse.

Langkah 1: Buka pengembangan data

Masuk ke Konsol AnalyticDB for MySQL. Di pojok kiri atas konsol, pilih wilayah. Di panel navigasi sebelah kiri, klik Clusters. Temukan kluster yang ingin Anda kelola, lalu klik ID kluster tersebut.
Di panel navigasi sebelah kiri, pilih Job Development > SQL Development.
Pada tab SQLConsole, pilih engine Spark dan kelompok sumber daya pekerjaan.

Langkah 2: Buat database dan tabel C-Store

Catatan

Anda dapat menjalankan pernyataan SQL berikut dalam mode pemrosesan batch atau mode interaktif. Untuk informasi selengkapnya, lihat Mode eksekusi Spark SQL.

Jalankan pernyataan berikut untuk membuat database:
```
CREATE DATABASE spark_create_adb_db_test;
```

Jalankan pernyataan berikut untuk membuat tabel C-Store. Untuk informasi selengkapnya mengenai sintaks pembuatan tabel, lihat Buat tabel internal menggunakan Spark SQL.

CREATE TABLE spark_create_adb_db_test.test_adb_tbl (
  id int,
  name string COMMENT 'test_name',
  age int
) using adb TBLPROPERTIES('primaryKey'='id,age',
                'distributeType'='HASH', 
                'distributeColumns'='id', 
                'partitionType'='value', 
                'partitionColumn'='age',   
                'partitionCount'='120',
                'storagePolicy'='COLD'   
               );

Langkah 3: Baca dan tulis tabel C-Store

Catatan

Anda dapat menjalankan pernyataan SQL berikut dalam mode pemrosesan batch atau mode interaktif. Untuk informasi selengkapnya, lihat Mode eksekusi Spark SQL.
Saat Anda menjalankan pernyataan Spark SQL, sistem hanya mengembalikan pesan yang menunjukkan apakah eksekusi berhasil atau gagal. Tidak ada data yang dikembalikan. Lihat data tabel di Logs pada tab Application List di halaman Spark Jar Development. Untuk informasi selengkapnya, lihat Lihat informasi aplikasi Spark.

Untuk operasi dalam mode batch (INSERT OVERWRITE), Anda hanya perlu memilih engine Spark dan kelompok sumber daya pekerjaan. Untuk operasi dalam mode real-time (INSERT INTO), Anda harus menggunakan perintah SET untuk menentukan kelompok sumber daya interaktif guna menjalankan pernyataan SQL.

Mode batch (INSERT OVERWRITE)

Catatan penggunaan

Perhatikan poin-poin berikut saat membaca dan menulis data dalam mode batch:

Spark SQL mendukung pembacaan (SELECT) dari dan penulisan (INSERT) ke tabel partisi C-Store, tetapi tidak mendukung tabel non-partisi. Untuk informasi selengkapnya tentang cara membuat tabel partisi, lihat CREATE TABLE.
Anda tidak dapat menggunakan Spark SQL untuk memperbarui (UPDATE) atau menghapus (DELETE) data dari tabel C-Store, baik tabel tersebut dipartisi maupun tidak.
Saat melakukan kueri terhadap hot data, Anda harus terlebih dahulu mengubah item konfigurasi ELASTIC_ENABLE_HOT_PARTITION_HAS_HDD_REPLICA, lalu secara manual memicu pekerjaan BUILD. Anda hanya dapat menjalankan pernyataan kueri SQL setelah pekerjaan BUILD selesai. Jika tidak, pernyataan SQL akan gagal.
1. Gunakan engine XIHE untuk menjalankan pernyataan SQL berikut guna mengubah item konfigurasi:
```
SET adb_config ELASTIC_ENABLE_HOT_PARTITION_HAS_HDD_REPLICA=true;
```
2. Picu secara manual pekerjaan BUILD.
  - Jalankan pekerjaan BUILD pada partisi tertentu:
    Penting
    Anda hanya dapat menjalankan pekerjaan BUILD pada partisi tertentu untuk kluster versi V3.1.6.0 atau yang lebih baru.
    Untuk melihat dan memperbarui versi minor, buka bagian Configuration Information pada halaman Cluster Information di Konsol AnalyticDB for MySQL.
    BUILD TABLE <table_name> force partitions='partition1,partition2';
  - Jalankan pekerjaan BUILD pada seluruh tabel:
    Penting
    Fitur ini dinonaktifkan secara default karena proses rebuild indeks untuk semua partisi memakan waktu lama. Kami menyarankan Anda menjalankan pekerjaan BUILD hanya pada partisi tertentu. Jika ingin mengaktifkan fitur ini, evaluasi risiko sistem secara hati-hati, lalu kirim tiket.
    BUILD TABLE <table_name> force = true;
3. Setelah memicu pekerjaan BUILD, jalankan pernyataan berikut untuk memeriksa status pekerjaan tersebut:
```
SELECT table_name, schema_name, status FROM INFORMATION_SCHEMA.KEPLER_META_BUILD_TASK ORDER BY create_time DESC LIMIT 10;
```

Tulis data ke tabel C-Store.
AnalyticDB for MySQL mendukung penulisan data ke tabel C-Store secara offline menggunakan pernyataan INSERT OVERWRITE. Anda dapat memilih salah satu metode berikut untuk menulis data ke tabel C-Store:
- Metode 1: Tulis ke partisi statis
```
INSERT OVERWRITE spark_create_adb_db_test.test_adb_tbl partition(age=10) VALUES (1, 'bom');
```
- Metode 2: Tulis ke partisi dinamis
```
INSERT OVERWRITE spark_create_adb_db_test.test_adb_tbl partition (age) VALUES (1, 'bom', 10);
```

Baca data dari tabel C-Store.

SELECT * FROM spark_create_adb_db_test.test_adb_tbl;

Mode real-time (INSERT INTO)

AnalyticDB for MySQL memungkinkan Anda menggunakan pernyataan INSERT INTO untuk menulis data ke tabel C-Store dalam mode real-time. Anda dapat membaca dari dan menulis ke tabel menggunakan metode JDBC, metode View, atau metode Catalog.

Metode JDBC

-- Akun database, password, dan titik akhir internal kluster.
conf spark.adb.username=user;
conf spark.adb.password=password;
conf spark.adb.endpoint=amv-bp1a74zh****.ads.aliyuncs.com:3306;
-- Aktifkan akses berbasis ENI.
SET spark.adb.eni.enabled=true;
SET spark.adb.eni.vswitchId=vsw-bp12ldm83z4zu9k4d****;
SET spark.adb.eni.securityGroupId=sg-bp1cdm3acizrgq6x****;
-- Gunakan mode JDBC.
SET spark.adb.useJdbc = true;
-- Tetapkan kelompok sumber daya interaktif untuk eksekusi SQL.
SET spark.adb.resourceGroup=user_default;
-- Tulis data.
INSERT INTO spark_create_adb_db_test.test_adb_tbl VALUES (1, 'adb', 20);
-- Baca data.
SELECT * FROM spark_create_adb_db_test.test_adb_tbl;

Tabel berikut menjelaskan parameter-parameter tersebut.

Parameter	Deskripsi
spark.adb.username	Akun database untuk kluster.
spark.adb.password	Password untuk akun database.
spark.adb.endpoint	Titik akhir internal dan port kluster. Formatnya adalah `amv-bp1a74zh****.ads.aliyuncs.com:3306`.
spark.adb.eni.enabled	Menentukan apakah akses berbasis ENI diaktifkan. Untuk mengakses data, atur parameter `spark.adb.eni.enabled` ke true.
spark.adb.eni.vswitchId	ID VSwitch tempat kluster berada.
spark.adb.eni.securityGroupId	ID Security Group untuk kluster. Anda dapat memilih Security Group yang sudah ada atau membuat yang baru. Penting Security Group harus berada dalam VPC yang sama dengan kluster Edisi Perusahaan, Edisi Dasar, atau Edisi Data Lakehouse.
spark.adb.useJdbc	Menentukan apakah metode JDBC digunakan untuk mengakses data. Nilai yang valid: true: Ya. false (nilai default): Tidak. Atur parameter ini ke true untuk menulis data dalam mode real-time.
spark.adb.resourceGroup	Kelompok sumber daya interaktif yang digunakan untuk menjalankan pernyataan SQL. Catatan Saat Anda membuat kluster Edisi Perusahaan, Edisi Dasar, atau Edisi Data Lakehouse, kelompok sumber daya interaktif bernama `user_default` dibuat secara otomatis. Anda dapat menggunakan kelompok sumber daya ini atau membuat kelompok sumber daya interaktif baru. Untuk informasi selengkapnya tentang cara membuat kelompok sumber daya, lihat Buat dan kelola kelompok sumber daya.

View method

-- Aktifkan akses berbasis ENI.
SET spark.adb.eni.enabled=true;
SET spark.adb.eni.vswitchId=vsw-bp1sxxsodv28ey5dl****;   
SET spark.adb.eni.securityGroupId=sg-bp19mr685pmg4ihc****;    
-- Buat view.
CREATE TEMPORARY VIEW table_tmp
USING org.apache.spark.sql.jdbc
OPTIONS (
  url 'jdbc:mysql://amv-bp1a74zh****.ads.aliyuncs.com:3306/spark_create_adb_db_test?useServerPrepStmts=false&rewriteBatchedStatements=true',     
  dbtable 'spark_create_adb_db_test.test_adb_tbl',    
  user 'user',       
  password 'password****'      
);
-- Tulis data.
INSERT INTO table_tmp VALUES (1, 'adb', 20);
-- Kueri data.
SELECT * FROM table_tmp;

Tabel berikut menjelaskan parameter-parameter tersebut.

Parameter	Deskripsi
spark.adb.eni.enabled	Menentukan apakah akses berbasis ENI diaktifkan. Untuk mengakses data, atur parameter `spark.adb.eni.enabled` ke true.
spark.adb.eni.vswitchId	ID VSwitch tempat kluster berada.
spark.adb.eni.securityGroupId	ID Security Group untuk kluster. Anda dapat memilih Security Group yang sudah ada atau membuat yang baru. Penting Security Group harus berada dalam VPC yang sama dengan kluster Edisi Perusahaan, Edisi Dasar, atau Edisi Data Lakehouse.
table_tmp	Nama view. Dalam topik ini, `table_tmp` digunakan sebagai contoh.
USING org.apache.spark.sql.jdbc	Nilai parameter ini tetap `USING org.apache.spark.sql.jdbc`.
url	String koneksi JDBC kluster. Format: `jdbc:mysql://<endpoint:port>/<db_name>?useServerPrepStmts=false&rewriteBatchedStatements=true`, dengan: `endpoint`: titik akhir internal kluster. port: nomor port kluster. Nilainya tetap 3306. `db_name`: nama database di kluster. useServerPrepStmts=false&rewriteBatchedStatements=true: Konfigurasi ini wajib untuk penulisan batch. Ini meningkatkan performa penulisan dan mengurangi beban pada kluster. Contoh: `jdbc:mysql://amv-bp1a74zh****.ads.aliyuncs.com:3306/spark_create_adb_db_test?useServerPrepStmts=false&rewriteBatchedStatements=true`.
dbtable	Nama tabel di kluster. Formatnya adalah `db_name.table_name`. Dalam topik ini, `spark_create_adb_db_test.test_adb_tbl` digunakan sebagai contoh.
user	Akun database untuk kluster.
password	Password untuk akun database.

Metode Katalog

SET spark.adb.eni.enabled=true;
SET spark.adb.eni.vswitchId=<vsw-bp1d14ddiw46fkgu1****>;
SET spark.adb.eni.securityGroupId=<sg-bp19varsa8j0hyb****>;
SET spark.sql.catalog.jdbc=org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTableCatalog;
SET spark.sql.catalog.jdbc.url=<jdbc:mysql://amv-bp1a74zh****.ads.aliyuncs.com:3306/?useServerPrepStmts=false&rewriteBatchedStatements=true>;
SET spark.sql.catalog.jdbc.user=<user>;
SET spark.sql.catalog.jdbc.password=<password>;
use jdbc;
SELECT * FROM spark_create_adb_db_test.test_adb_tbl;

Tabel berikut menjelaskan parameter-parameter tersebut.

Parameter	Deskripsi
spark.sql.catalog.jdbc	Metode yang digunakan Spark SQL untuk mengonfigurasi sumber data. Nilai parameter ini tetap `org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTableCatalog`. Catatan Pada parameter `spark.sql.catalog.catalog_name`, bagian `catalog_name` dapat dikustomisasi. Dalam topik ini, `jdbc` digunakan sebagai contoh.
spark.sql.catalog.jdbc.url	URL JDBC untuk kluster dalam format `jdbc:mysql://<endpoint:port>/?useServerPrepStmts=false&rewriteBatchedStatements=true`, dengan: `endpoint`: Alamat koneksi internal kluster. `port`: Nomor port kluster. Nilainya tetap 3306. `useServerPrepStmts=false&rewriteBatchedStatements=true`: Pengaturan wajib untuk penulisan batch data ke kluster, yang meningkatkan performa penulisan dan mengurangi beban pada kluster. Contoh: `jdbc:mysql://amv-bp1a74zh****.ads.aliyuncs.com:3306/?useServerPrepStmts=false&rewriteBatchedStatements=true`.
spark.sql.catalog.jdbc.user	Akun database untuk kluster.
spark.sql.catalog.jdbc.password	Password untuk akun database.

Parameter Spark

Saat menggunakan Spark SQL untuk membaca data dari atau menulis data ke tabel C-Store, Anda dapat mengonfigurasi parameter berikut:

Parameter	Deskripsi	Default
spark.adb.write.batchSize	Jumlah catatan data yang ditulis dalam satu batch. Nilainya harus berupa bilangan bulat positif. Catatan Parameter ini hanya berlaku saat Anda menulis data dalam mode real-time.	600
spark.adb.write.arrow.maxMemoryBufferSize	Ukuran buffer memori maksimum untuk operasi penulisan. Nilainya harus berupa bilangan bulat positif. Satuan: MB. Catatan Parameter ini hanya berlaku saat Anda menulis data dalam mode batch.	1024
spark.adb.write.arrow.maxRecordSizePerBatch	Jumlah maksimum catatan yang ditulis dalam satu batch. Nilainya harus berupa bilangan bulat positif. Catatan Parameter ini hanya berlaku saat Anda menulis data dalam mode batch.	500