tencent cloud

CREATE TABLE IF NOT EXISTS example_db.expamle_tbl
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1",
    "in_memory"="true"
);

ALTER TABLE example_db.my_table set ("in_memory" = "true");

ALTER TABLE example_db.my_table ADD [TEMPORARY] PARTITION p1 VALUES LESS THAN ("2020-02-01") ("in_memory" = "true", "replication_num" = "1")

ALTER TABLE example_db.my_table MODIFY PARTITION (p1, p2, p4) SET("in_memory"="true"); 

ALTER TABLE example_db.my_table set ("in_memory" = "false");

ALTER TABLE example_db.my_table MODIFY PARTITION (p1, p2, p4) SET("in_memory"="false"); 

[root@9 data]# tail test.sql 
select * from supplier where s_suppkey=1;
...
select * from supplier where s_suppkey=999991;
select * from supplier where s_suppkey=999992;
select * from supplier where s_suppkey=999993;
select * from supplier where s_suppkey=999994;
select * from supplier where s_suppkey=999995;
select * from supplier where s_suppkey=999996;
select * from supplier where s_suppkey=999997;
select * from supplier where s_suppkey=999998;
select * from supplier where s_suppkey=999999;
[root@9 data]# tail test2.sql 
select * from supplier1 where s_suppkey=1;
...
select * from supplier1 where s_suppkey=999991;
select * from supplier1 where s_suppkey=999992;
select * from supplier1 where s_suppkey=999993;
select * from supplier1 where s_suppkey=999994;
select * from supplier1 where s_suppkey=999995;
select * from supplier1 where s_suppkey=999996;
select * from supplier1 where s_suppkey=999997;
select * from supplier1 where s_suppkey=999998;
select * from supplier1 where s_suppkey=999999;

mysqlslap -h127.0.0.1 -uadmin -P9030 -pxxxxx --iterations=1 --concurrency=500 --number-of-queries=1000000 --create-schema=tpch_100_d --query="/data/test1.sql" --delimiter=";";