创建普通ORC表

任务

创建普通ORC表，并验证ORC表的事务性。

步骤_创建数据库和ORC表

create table orc_table(key int, value string) stored as orc;

注意！！ORC表不支持单条语句insert，只有torc才支持，但是可以insert into select xx from XXX；
比如下方示例，用户向ORC表中插入数据报错

insert into orc_table values(1,'test');

创建ORC事务表（TORC）

任务

创建ORC事务表，并进行事务操作。

步骤

① 设置开启事务

set transaction.type=inceptor;

② 设置PLSQL编译器不检查语义

set plsql.compile.dml.check.semantic=false;

③ 创建ORC事务表

drop table if exists atomicity_table;

create table atomicity_table(key int, value string) 
clustered by(key) into 8 buckets 
stored as orc tblproperties('transactional'='true');

④ 可以输入指令查看是否创建成功

⑤ 向ORC事务表中插入数据

insert into atomicity_table values(1,'src1'); 
insert into atomicity_table values(2,'src2');

⑥ 查看数据是否写入成功

select * from atomicity_table;

⑦ 更新ORC事务表数据

update atomicity_table set value = 'src3' where key = 1;

⑧ 查看数据是否更新成功

select * from atomicity_table;

创建ORC分区表

任务

创建单值分区表user_acc_level，表包含字段为name，分区字段为acc_level。

步骤

① 创建单值分区表，分区键=acc_level

CREATE TABLE user_acc_level (name STRING)
PARTITIONED BY (acc_level STRING);

创建ORC分区分桶表

任务

创建范围分区分桶表，并存储为ORC格式。

步骤

① 创建范围分区分桶表，分区键=sj，分桶键=mbbh

create table hq_ais_history_data_orc_bucket (
cbm string,
csx int,
cwjqd int,
dzdwzz int,
gjmc string,
hh string,
hs double,
hwlx int,
hx double,
hxzt int,
imobm string,
mbbh string,
mdd string,
txzt int,
xxlx int,
xxly int,
yjddsj string,
zdjss double,
zxl int,
lat double,
lon double,
mbsj int
)

partitioned by range (sj string) (
partition values less than ("2014-11-04 23:59:59"),
partition values less than ("2014-11-05 23:59:59"),
partition values less than ("2014-11-06 23:59:59"),
partition values less than ("2014-11-07 23:59:59"),
partition values less than ("2014-11-08 23:59:59"),
partition values less than ("2014-11-09 23:59:59"),
partition values less than ("2014-11-10 23:59:59"),
partition values less than ("2014-11-11 23:59:59"),
partition values less than ("2015-08-05 23:59:59")
)
clustered by (mbbh) into 23 buckets
stored as orc;