ODPS 之SQL_odps sql

1.基本概念

1）ODPS：Open Data Processing Service， 简称ODPS；是由阿里云自主研发，提供针对TB/PB级数据、实时性要求不高的分布式处理能力，应用于数据分析、挖掘、商业智能等领域；阿里巴巴的离线数据业务都运行在ODPS上；
2）御膳房：基于ODPS的一个应用模型；基于公共云计算的数据管理、计算和交换平台，为政府机构、企业、科研机构、第三方软件服务商等客户，提供大数据管理、开发和生产计算的能力，同时让客户间能交换数据，解决数据管理、应用、流通的场景需求，帮助客户实现商业价值；

3）ODPS SQL：与Hive SQL语法基本一致；适用于海量数据(TB级别)，实时性要求不高的场合，它的每个作业的准备，提交等阶段要花费较长时间， 因此要求每秒处理几千至数万笔事务的业务是不能用ODPS SQL完成的；ODPS SQL采用的是类似于SQL的语法，可以看作是标准SQL的子集，但不能因此简单的把ODPS SQL等价成一个数据库，它在很多方面并不具备数据库的特征；

2. DDL

（1）create table

--创建者  : xx
--创建日期: 2018-05-10
--修改日期:         
--修改人  :             
--修改内容: 
--*******************************************************************************
 
--创建无分区表
CREATE TABLE IF NOT EXISTS dws_test_live_stream_duration_1d
(
    stat_date                    STRING   COMMENT 'stat_date',
    live_uuid                    STRING   COMMENT 'live_uuid',
    utdid                        STRING   COMMENT 'utdid',
    total_duration_s             double   COMMENT '观看时长',
    pv                           bigint   COMMENT 'pv'
)
comment '直播访问时长轻度汇总表'
LIFECYCLE 93;

 

ODPS Table 表前缀含义：

ODS(Operation Data Store):把操作系统数据几乎无处理的放进数据仓库系统【保留原始数据，一般永久存储】

CDM(Common Data Layer):存放事实表，维表，dws轻度汇总表；有ODS清洗产出

DWD(Data Warehouse Detail):存放明细数据，一般是大宽表，星型模型

DWS(Data Warehouse Summary):存放轻度汇总数据，一般是一部分维度，有DWD层加工而来

ADS(Application Data Service):存放数据产品个性化的指标数据，有DWD和DWS以及DIM计算产出

这次说下表命名结尾的含义：

di实际对应的dwd table ，日增表，即每天都是一天一天的快照数据

df:实际对应的dwd table ，即每天都是包含截止当前全部的数据

1d实际对应的dws/ads ，指的是1天内的数据统计

7d/1w:实际对应的dws/ads ，指的是7天内的数据统计

1m:实际对应的dws/ads ，指的是一个月内的数据统计

nd:实际对应的dws/ads ，指的该表内是多天内的数据统计，如最近1天，7天等多天的指标都在

hi:小时增量表，每个分区代表一个小时的数据

1h:小时统计表，每个分区是一个小时内的统计

hh:小时累计表，当天截止到当前小时的全部数据

 2）alter table

-- 添加一列 age
ALTER TABLE students_info ADD COLUMNS (age BIGINT); 
 
-- 表为test_data ，分区为dt
INSERT OVERWRITE  TABLE test_data PARTITION(dt=${bdp.system.bizdate}) 
 
--删除整张表
ALTER TABLE table_name DROP [IF EXISTS] partition_spec;  
 
--删除指定分区
ALTER TABLE test_data  DROP IF EXISTS partition(dt="20190710"); --删除分区
 
添加列
ALTER TABLE test_table_name ADD COLUMNS (col_name1 type1, col_name2 type2...)
eg: alter table dwd_wl_log_ut_spm_obj_vst_di      add columns (client_code string comment 'client_code');
 
修改列名
ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;
 
修改列、分区注释
ALTER TABLE table_name CHANGE COLUMN col_name COMMENT 'comment';
 
修改表的生命周期属性
ALTER TABLE table_name SET LIFECYCLE days;
eg：ALTER TABLE dwd_wl_log_ut_spm_di          SET LIFECYCLE 750;
 
删除表
DROP TABLE [IF EXISTS] table_name;
误删除表；drop table会在回收站保存三天，如果发现误删表请立即联系管理员同学还可以恢复
删除视图
drop view [if exists] view_name;
 
重命名表
ALTER TABLE table_name RENAME TO new_table_name;
 
 
重命名视图
alter view view_name rename to new_view_name;
 
添加分区：
alter table sale_detail add if not exists partition (sale_date='201312', region='hangzhou');
 
删除表分区
ALTER TABLE test_table drop if exists partition(dt= '${bizdate}'); 
eg:
alter table test_table drop if exists partition(dt = '${bizdate}'); 
alter table s_user_track_outer_hour drop if exists partition(ds = '20170905',hh='01',product='baba'); 
 
查看表分区：
show partitions test_table
 
清空非分区表的数据
TRUNCATE TABLE test_table;