StarRocks——分区表创建和数据导入

1.创建分区表

CREATE TABLE tbl1 (

ID VARCHAR ( 30 ) NOT NULL,

ROWKEY VARCHAR ( 100 ) NOT NULL,

.......

PASS_TIME DATETIME NOT NULL

) DUPLICATE KEY ( ID ) partition BY RANGE ( PASS_TIME ) ( ) DISTRIBUTED BY HASH ( ID ) BUCKETS 5 PROPERTIES (

"dynamic_partition.enable" = "true",

"dynamic_partition.time_unit" = "DAY",

"dynamic_partition.start" = "-2147483648",

"dynamic_partition.end" = "3",

"dynamic_partition.prefix" = "P",

"dynamic_partition.buckets" = "10",

"replication_num" = "2"

);

2.查看分区情况以及每个分区对应的时间区间

show partitions from tbl1;

2.创建历史分区

动态分区，是说会随着时间推移自动创建后续的分区，例如今天是21号，你设置动态分区是自动创建后面三天的，那么会把22、23、24三天的分区创建。并不是数据过来后，自动创建数据对应的分区。

创建历史分区，可以先关掉动态分区属性，然后修改表的分区

参考：第2.4章：StarRocks表设计--分区分桶与副本数_流木的博客-CSDN博客

ALTER TABLE tbl1 SET("dynamic_partition.enable"="false");

批量添加分区

PARTITION BY RANGE (PASS_TIME) (

    START ("2021-10-01") END ("2021-10-03") EVERY (INTERVAL 1 day)

);

增加一个指定上下界的分区p20210312：

ALTER TABLE tbl1 ADD PARTITION p20210312 VALUES [("2021-03-12"), ("2021-03-13")); 

左闭右开

PS：不创建历史分区导入数据报错：

Reason: there are 4096 rows couldn't find a partition.

3.DataX导入数据的job.json文件

{

    "job": {

        "setting": {

            "speed": {

                "channel": 5

            }

        },

        "content": [

            {

                "reader": {

                    "name": "oraclereader",

                    "parameter": {

                        "username": "${r_username}",

                        "password": "${r_password}",

                        "column": [

                            "ID",

                            "ROWKEY",

                            ......

                            "PASS_TIME"

                        ],

                        "connection": [

                            {

                                "table": [

                                    "tbl1"

                                ],

                                "jdbcUrl": [

                                    "jdbc:oracle:thin:@//${r_ip}:${r_port}/${r_dbname}"

                                ]

                            }

                        ]

                    }

                },

                "writer": {

                    "name": "starrockswriter",

                    "parameter": {

                        "username": "${w_username}",

                        "password": "",

                        "database": "${w_dbname}",

                        "table": "tbl1",

                        "column": [

                            "ID",

                            "ROWKEY",

                            ......

                            "PASS_TIME"

                           

                        ],

                        "preSql": [

                            "truncate  table ${w_dbname}.tbl1"

                        ],

                        "postSql": [ ],

                        "jdbcUrl": "jdbc:mysql://${w_ip}:${w_port}/",

                        "loadUrl": [

                            "beip:8030",

                            "beip:8030"

                        ],

                        "loadProps": {

                            "column_separator": "\\x01",

                            "row_delimiter": "\\x02"

                        }

                    }

                }

            }

        ]

    }

}

PS：需要指定一下分隔符

报错

Reason: column count mismatch,expect=58 real=6. src line:

Reason: column count mismatch,expect=58 real=53. src line: