参加《21天晋级大数据实战营》——DAY15让数据产生价值--数据仓库服务实验小结

1、创建数据仓库集群

2、下载客户端连接集群

需先安装jdk8或以上

3、导入交通卡口数据并进行分析

代码参考片段1：
create database traffic encoding 'utf8' template template0;

代码参考片段2：
create schema traffic_data;
set current_schema= traffic_data;
drop table if exists GCJL;
CREATE TABLE GCJL
(
        kkbh   VARCHAR(20),
        hphm   VARCHAR(20),
        gcsj   DATE ,
        cplx   VARCHAR(8),
        cllx   VARCHAR(8),
        csys   VARCHAR(8)
)
with (orientation = column, COMPRESSION=MIDDLE)
distribute by hash(hphm);

代码参考片段3：
create schema tpchobs;
set current_schema = 'tpchobs';
drop FOREIGN table if exists GCJL_OBS;
CREATE FOREIGN TABLE GCJL_OBS
(
        like traffic_data.GCJL
)
SERVER gsmpp_server
OPTIONS (
        encoding 'utf8',
        location 'obs://dws-demo-cn-south-1/traffic-data/gcxx',
        format 'text',
        delimiter ',',
        access_key 'your ak',
        secret_access_key 'your sk',
        chunksize '64',
        IGNORE_EXTRA_DATA 'on'
);

注：将上面的桶名，AK/SK换成实际对应的即可

北京：dws-demo

上海：dws-demo-cn-east-2

广州：dws-demo-cn-south-1

参考代码片段4：
insert into traffic_data.GCJL select * from tpchobs.GCJL_OBS;


参考代码片段5：
set current_schema= traffic_data;
Select count(*) from traffic_data.gcjl;

参考代码片段6：
set current_schema= traffic_data;
select hphm, kkbh, gcsj
from traffic_data.gcjl
where hphm = '粤D38641'
and gcsj between '2016-01-06' and '2016-01-07'
order by gcsj desc;

参考代码片段7：
set current_schema= traffic_data;
select hphm, kkbh, gcsj
from traffic_data.gcjl
where hphm like '粤A23F%'
and kkbh in('508', '1125', '2120')
and gcsj between '2016-01-01' and '2016-01-07'  
order by hphm,gcsj desc;