maxcomputer的分区表相关操作

– 查看xsxw(学生行为表定义)
desc xsxw;

– 查看xsxw1(学生行为表定义)
desc xsxw1;
– 上面的两张表是通过ddl语句创建的分区表
– 创建ddl语句如下
– CREATE TABLE IF NOT EXISTS xsxw1(
– xwsj STRING COMMENT ‘行为时间’,
– xh STRING COMMENT ‘学号’,
– xwdd STRING COMMENT ‘行为地点’,
– xwdm STRING COMMENT ‘行为代码’,
– bzsm STRING COMMENT ‘备注说明’)
– comment ‘学生行为信息’
– PARTITIONED BY (
– xwrq STRING COMMENT ‘行为信息’,
– yxbh STRING COMMENT ‘院系编号’);
– show CREATE table xsxw1; 查询结果如下
– CREATE TABLE IF NOT EXISTS default_project_31710a2.xsxw1(
– xwsj STRING COMMENT ‘行为时间’,
– xh STRING COMMENT ‘学号’,
– xwdd STRING COMMENT ‘行为地点’,
– xwdm STRING COMMENT ‘行为代码’,
– bzsm STRING COMMENT ‘备注说明’)
– PARTITIONED BY (
– xwrq STRING COMMENT ‘行为信息’,
– yxbh STRING COMMENT ‘院系编号’)
– STORED AS ALIORC TBLPROPERTIES (‘comment’=‘学生行为信息’);

– 添加分区
ALTER TABLE xsxw add if not EXISTS PARTITION (xwrq=20170717,yxbh=‘SMGL’);
ALTER TABLE xsxw add if not EXISTS PARTITION (xwrq=20170718,yxbh=‘JGXY’);

– 列出表的已有分区
show PARTITIONS xsxw;

– 查看单个分区的信息
DESC xsxw PARTITION (xwrq=20170717,yxbh=‘SMGL’);

–添加分区(xwrq=‘20170716’,yxbh=‘JSJXY’)
ALTER TABLE xsxw ADD IF NOT EXISTS
PARTITION(xwrq=20170716,yxbh=‘JGXY’);

–插入数据到(xwrq=‘20170716’,yxbh=‘JSJXY’)分区
insert into xsxw
PARTITION(xwrq=20170716,yxbh=‘JGXY’）
VALUES(‘20211224’,‘20211106’,‘潍坊’,‘3701222’,‘学生信息’);

–读数据
read xsxw;

– 添加分区(xwrq=‘20170716’,yxbh=‘JXJXY’)
alter table xsxw add if not exists partition(xwrq=‘20170716’,yxbh=‘JXJXY’);
– 插入数据到分区(xwrq=‘20170716’,yxbh=‘JXJXY’)
insert into table xsxw partition (xwrq=‘20170716’,yxbh=‘JXJXY’)
values(‘晚上’,‘50701207’,‘河北工程技术高等专科学校’,‘11121666666’,‘请假’)
–读数据 表中有多个分区时查询应指定表的分区
select * from xsxw where xwrq=‘20170716’;

– 导入本地文件到xsxw 分区(xwrq=‘20170716’,yxbh=‘JGXY’)851条数据再查询
SELECT * from xsxw
where xwrq='20170718’and yxbh=‘JGXY’;

– 查看分区创建修改时间的信息
desc xsxw PARTITION(xwrq=‘20170716’,yxbh=‘JGXY’)