// 创建数据库并导入数据
1. create database {database_name};
2. use {database_name};
3. create table docs(line string);
4. load data inpath '/training/{student_name}/inceptor_data/wordcount' into table docs;
//创建结果表
create table wc(word string, totalword int);
// wordcount统计
from (select explode(split(line, ' ')) as word from docs) w
insert into table wc
select word, count(1) as totalword
group by word
order by word;
// 查看分析结果
select * from wc;
// 创建外表
create external table ext_table(rowkey string, num int, country int, rd string) row format delimited fields terminated by ',' location '/images/inceptor_data';
//创建ORC事务表
// 设置开启事务
1. set transaction.type=inceptor;
// 设置PLSQL编译器不检查语义
2. set plsql.compile.dml.check.semantic=false;
create table atomicity_table(key int, value string) clustered by(key) into 8 buckets stored as orc tblproperties('transactional'='true');
// 创建单值分区表
CREATE TABLE user_acc_level (name STRING)
PARTITIonED BY (acc_level STRING)
//创建ORC分区分桶表
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;
