Import Internal Tables
This page explains how to synchronize external data into Aloudata CAN. Aloudata CAN currently supports StarRocks and Doris as compute engines.
The example below imports data from PostgreSQL into StarRocks by using SMT and DataX. For more import methods, see the StarRocks loading documentation.
1. Prerequisites
- Create a source table in PostgreSQL and prepare sample data.
- Prepare a StarRocks environment.
- Download DataX from DataX.
- Download SMT from SMT.
Note
StarRocks Migration Tool, or SMT, is a data migration tool provided by StarRocks. See SMT for details. This page uses SMT to quickly create tables in StarRocks.
DataX is an offline data synchronization tool widely used by Alibaba Group. See DataX for details. This page uses DataX to synchronize data from PostgreSQL to StarRocks.
2. Steps
Prepare PostgreSQL Source Table Data
Create a table named user in PostgreSQL. Example DDL:
CREATE TABLE `user` (
`user_id` int(11) NULL COMMENT "",
`user_name` varchar(50) NULL COMMENT "",
`sex` varchar(50) NULL COMMENT "",
`age` int(11) NULL COMMENT "",
`city` varchar(50) NULL COMMENT ""
)
Insert sample data into the table:
INSERT INTO `user` (`user_id`, `user_name`, `sex`, `age`, `city`) VALUES
(1, 'Alice', 'Female', 28, 'New York'),
(2, 'Bob', 'Male', 34, 'Los Angeles'),
(3, 'Charlie', 'Male', 22, 'Chicago'),
(4, 'Diana', 'Female', 29, 'Houston'),
(5, 'Eve', 'Female', 35, 'Phoenix');
Generate StarRocks DDL With SMT
Update the SMT configuration file:
[db]
host = 192.168.1.1
port = 5432
user = xxx
password = xxx
type = pgsql
[other]
# number of backends in StarRocks
be_num = 3
# `decimal_v3` is supported since StarRocks-1.18.1
use_decimal_v3 = false
# directory to save the converted DDL SQL
output_dir = ./result
[table-rule.1]
# pattern to match databases for setting properties
database = db1
# pattern to match tables for setting properties
table = user
# pattern to match schemas for setting properties
schema = public
############################################
### flink sink configurations
### DO NOT set `connector`, `table-name`, `database-name`, they are auto-generated
############################################
flink.starrocks.jdbc-url=jdbc:mysql://192.168.1.1:9030
flink.starrocks.load-url= 192.168.1.1:8030
flink.starrocks.username=root
flink.starrocks.password=
flink.starrocks.sink.max-retries=10
flink.starrocks.sink.buffer-flush.interval-ms=15000
flink.starrocks.sink.properties.format=json
flink.starrocks.sink.properties.strip_outer_array=true
Run starrocks-migrate-tool. The generated table creation statements are saved in the result directory.
$ ./starrocks-migrate-tool
$ ls result
flink-create.1.sql smt.tar.gz starrocks-create.all.sql
flink-create.all.sql starrocks-create.1.sql
Create the StarRocks table structure:
Prepare The DataX Import Script
Create a DataX import configuration file named config.json.
{
"job": {
"setting": {
"speed": {
"channel": 3
}
},
"content": [
{
"reader": {
"name": "postgresqlreader",
"parameter": {
"username": "root",
"password": "password",
"connection": [
{
"table": [
"user"
],
"jdbcUrl": [
"jdbc:mysql://127.0.0.1:3306/db1"
]
}
],
"column": [
"*"
]
}
},
"writer": {
"name": "starrockswriter",
"parameter": {
"username": "root",
"password": "password",
"column": ["*"],
"preSql": [],
"postSql": [],
"connection": [
{
"table": ["user"],
"jdbcUrl": "jdbc:mysql://10.15.1.171:9030/"
}
],
"loadUrl": ["10.15.1.171:8030"],
"loadProps": {}
}
}
}
]
}
}