1. 集群规划
– 节点数：6节点（3 Zone * 2节点）
– 配置：64核 256G SSD
– 副本数：3副本
– 网络：万兆以太网

2. 租户创建
obclient> CREATE RESOURCE UNIT dw_unit
MEMORY_SIZE = ‘100G’,
MAX_CPU = 32,
MIN_CPU = 16,
IOPS = 50000,
DISK_SIZE = ‘5T’;

obclient> CREATE RESOURCE POOL dw_pool
UNIT = ‘dw_unit’,
UNIT_NUM = 2,
ZONE_LIST = (‘zone1′,’zone2′,’zone3’);

obclient> CREATE TENANT data_warehouse
PRIMARY_ZONE = ‘zone1;zone2,zone3’,
RESOURCE_POOL_LIST = (‘dw_pool’)
SET ob_tcp_invited_nodes = ‘%’;更多视频教程www.fgedu.net.cn。

3. 参数优化
obclient> ALTER SYSTEM SET memory_limit = ‘200G’;
obclient> ALTER SYSTEM SET __data_mem_limit = ‘150G’;
obclient> ALTER SYSTEM SET parallel_servers_target = 128;
obclient> ALTER SYSTEM SET _ob_enable_parallel_dml = TRUE;

4. 表空间创建
obclient> CREATE TABLESPACE fgedu_ods_ts
DATAFILE ‘/ob/fgdata/fgedu_ods_ts.dbf’ SIZE 1T AUTOEXTEND ON;

obclient> CREATE TABLESPACE fgedu_dwd_ts
DATAFILE ‘/ob/fgdata/fgedu_dwd_ts.dbf’ SIZE 2T AUTOEXTEND ON;

1. OMS配置
$ cat oms_dw_config.json
{
“source”: {
“type”: “MySQL”,
“host”: “192.168.1.50”,
“port”: 3306,
“user”: “etl_user”,
“password”: “etl123”
},
“target”: {
“type”: “OceanBase”,
“host”: “192.168.1.100”,
“port”: 3306,
“user”: “etl_user@data_warehouse”,
“password”: “etl123”
},
“migration”: {
“type”: “FULL_INCR”,
“tables”: [“orders”, “products”, “customers”],
“parallel”: 16，更多学习教程公众号风哥教程itpux_com。
}
}

2. DataX配置
$ cat datax_job.json
{
“job”: {
“content”: [{
“reader”: {
“name”: “mysqlreader”,
“parameter”: {
“username”: “etl_user”,
“password”: “etl123”,
“column”: [“*”],
“connection”: [{
“jdbcUrl”: [“jdbc:mysql://192.168.1.50:3306/source_db”],
“table”: [“orders”]
}]
}
},
“writer”: {
“name”: “oceanbasev10writer”,
“parameter”: {
“username”: “etl_user@data_warehouse”,
“password”: “etl123”,
“column”: [“*”],
“connection”: [{
“jdbcUrl”: “jdbc:oceanbase://192.168.1.100:3306/target_db”,
“table”: [“ods_orders”]
}]，from DB视频:www.itpux.com。
}
}
}]
}
}

3. 调度配置
#!/bin/bash
# etl_schedule.sh
# from:www.itpux.com.qq113257174.wx:itpux-com

# 全量抽取
python datax.py datax_full_job.json

# 增量抽取
python datax.py datax_incr_job.json

# 数据转换
obclient -e ”
INSERT INTO dwd_orders
SELECT
order_id,
customer_id,
product_id,
order_amount,
order_status,
DATE(create_time) as order_date
FROM ods_orders
WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY);
”

# 数据汇总
obclient -e ”
INSERT INTO dws_daily_sales
SELECT
order_date,
product_id,
SUM(order_amount) as daily_amount,
COUNT(*) as order_count
FROM dwd_orders
GROUP BY order_date, product_id;
”

1. 分区优化
obclient> CREATE TABLE dwd_orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT,
product_id BIGINT,
order_amount DECIMAL(18,4),
order_date DATE,
INDEX idx_customer (customer_id),
INDEX idx_product (product_id)
) PARTITION BY RANGE COLUMNS(order_date)
(
PARTITION p202401 VALUES LESS THAN (‘2024-02-01’),
PARTITION p202402 VALUES LESS THAN (‘2024-03-01’),
PARTITION p202403 VALUES LESS THAN (‘2024-04-01’)
);

2. 并行查询
obclient> ALTER SESSION SET PARALLEL_DEGREE_POLICY = ‘AUTO’;
obclient> ALTER SESSION SET PARALLEL_DEGREE_LIMIT = 32;

3. 物化视图
obclient> CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH COMPLETE ON DEMAND
AS
SELECT
order_date,
product_id,
SUM(order_amount) as total_amount,
COUNT(*) as order_count
FROM dwd_orders
GROUP BY order_date, product_id;

4. 统计信息
obclient> CALL DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘dwd_orders’);
obclient> CALL DBMS_STATS.GATHER_SCHEMA_STATS(‘fgedu’);