kudu sql 实操

impala操作kudu

建表

• hash分区

CREATE TABLE xiniu.my_first_table
(
  id BIGINT,
  name STRING,
  PRIMARY KEY(id)
)
PARTITION BY HASH PARTITIONS 16
STORED AS KUDU;

• hash分区且指定副本数

CREATE TABLE xiniu.replicastable1
(
  id BIGINT,
  name STRING,
  PRIMARY KEY(id)
)
PARTITION BY HASH PARTITIONS 16
STORED AS KUDU
TBLPROPERTIES ('kudu.num_tablet_replicas' = '1')
;

默认副本数为3，查看副本数"kudu cluster ksck worker-2"

• create as select

-- impala创建源表
create table xiniu.old_table (
    ts timestamp,
    name string,
    value string
);

-- create as old_table
create table xiniu.new_table
primary key (ts,name)
partition by hash (name) partitions 5
stored as kudu
as
select ts,name,value from old_table;

• range分区

create table xiniu.range_table(
    id bigint,
    name string,
    primary key (id)
)
partition by range (id)
(
    partition value = 1,
    partition value = 100,
    partition value = 200,
    partition value = 300,
    partition value = 400,
    partition value = 500
)
stored as kudu
tblproperties ('kudu.num_tablet_replicas' = '1');

• 组合分区

-- 组合主键必须放在表字段的最前面
-- 分组键必须为主键
create table xiniu.combin_partition_table(
    id bigint,
    age int,
    name string,
    primary key (id,age)
)
partition by hash (id) partitions 5,
range (age)
(
    partition VALUES < 10,
    partition 10 <= VALUES < 20,
    partition 20 <= VALUES < 30,
    partition 30 <= VALUES < 40,
    partition 40 <= VALUES < 60
)
stored as kudu
tblproperties ('kudu.num_tablet_replicas' = '3')
;

• 增加分区

alter table xiniu.combin_partition_table add range partition 60 < values < 100;

• 删除分区

alter table xiniu.combin_partition_table drop range partition value = 60;

插入数据

-- 单条插入
INSERT INTO my_first_table VALUES (99, "sarah");
-- 多条插入
INSERT INTO my_first_table VALUES (1, "john"), (2, "jane"), (3, "jim");
-- upsert 如果主键存在则修改，如果不存在则新增
UPSERT INTO my_first_table VALUES (99, "zoe");
UPSERT INTO my_first_table VALUES (88, "zoe");

修改数据

-- update语法仅限于在impala中操作kudu表
UPDATE xiniu.my_first_table SET name="bob" where id = 3;

删除数据

-- delete语法仅限于在impala中操作kudu表
DELETE FROM xiniu.my_first_table WHERE id < 3;

修改表名

ALTER TABLE xiniu.my_first_table RENAME TO xiniu.my_new_table;

impala关联kudu表

create EXTERNAL table xiniu.testbyapimapping 
stored as kudu
TBLPROPERTIES (
  'kudu.table_name' = 'xiniu.testbyapi'
);