主流 AP 数据库数据迁移至 OceanBase 最佳实践

INSERT INTO FILES(
    "path" = "s3://mybucket/unload/data1",
    "format" = "parquet",
    "compression" = "uncompressed",
    "target_max_file_size" = "1024", -- 单位：字节（示例为 1KB）
    "aws.s3.access_key" = "xxxxxxxxxx",
    "aws.s3.secret_key" = "yyyyyyyyyy",
    "aws.s3.region" = "us-west-2"
)
SELECT * FROM sales_records;

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest3 (
    A STRUCT<var1:STRING, var2:INT>
)
STORED AS PARQUET
LOCATION 'oss://testBucketName/osstest/Parquet';

INSERT INTO adb_external_demo.osstest3 SELECT * FROM t1;

INSERT /*+ direct(true, 0) enable_parallel_dml parallel(32) */ INTO t1
SELECT * FROM FILES(
  location = 's3://data/?host=xxx&access_id=xxx&access_key=xxx',
  format = (TYPE = 'PARQUET'),
  pattern = 'datafiles$'
);

LOAD DATA /*+ direct(true,0) parallel(2) */
FROM 's3://data/?host=xxx&access_id=xxx&access_key=xxx'
INTO TABLE t1
FORMAT (type = 'PARQUET');

INSERT INTO t1
SELECT * FROM files(
  LOCATION = 'hdfs://${namenode_host}:${namenode_port}/user?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/path/to/hdfs.keytab&krb5conf=/path/to/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy',
  FORMAT = (
    TYPE = 'CSV',
    FIELD_DELIMITER = ',',
    FIELD_OPTIONALLY_ENCLOSED_BY = '\''
  ),
  PATTERN = 'test_tbl1.csv'
);

INSERT INTO t1
SELECT * FROM SOURCE (
    TYPE = 'ODPS',
    ACCESSID = 'your_id',
    ACCESSKEY = 'your_key',
    ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
    PROJECT_NAME = 'my_project',
    TABLE_NAME = 'my_table'
);

-- 导入指定分区（追加）
INSERT /*+ direct(true, 0) enable_parallel_dml parallel(32) */ INTO t1 PARTITION(p0, p1)
SELECT * FROM external_table;

-- 覆盖指定分区
INSERT /*+ direct(true, 0) enable_parallel_dml parallel(32) */ OVERWRITE t1 PARTITION(p0, p1)
SELECT * FROM external_table;