odps创建周期任务及字符串与日期函数用法

odps简介

PB/EB级数据的离线存储(存储资源)及逻辑处理(计算资源)，集群可用性全托管。

MaxCompute(odps)是适用于数据分析场景的企业级SaaS（Software as a Service）模式云数据仓库，以Serverless架构提供快速、全托管的在线数据仓库服务，消除了传统数据平台在资源扩展性和弹性方面的限制，最小化用户运维投入，使您可以经济并高效地分析处理海量数据。

随着数据收集手段不断丰富，行业数据大量积累，数据规模已增长到了传统软件行业无法承载的海量数据（TB、PB、EB）级别。MaxCompute提供离线和流式数据的接入，支持大规模数据计算及查询加速能力，为您提供面向多种计算场景的数据仓库解决方案及分析建模服务。MaxCompute还为您提供完善的数据导入方案以及多种经典的分布式计算模型，您可以不必关心分布式计算和维护细节，便可轻松完成大数据分析。

MaxCompute适用于100 GB以上规模的存储及计算需求，最大可达EB级别，并且MaxCompute已经在阿里巴巴集团内部得到大规模应用。MaxCompute适用于大型互联网企业的数据仓库和BI分析、网站的日志分析、电子商务网站的交易分析、用户特征和兴趣挖掘等。

• odps 架构如下，更多详情，参见官网
• https://help.aliyun.com/document_detail/27800.html
  
  

创建周期任务(分区表)

周期任务，每天或每小时运行任务，将数据存储到天级或小时分区内。

CREATE TABLE IF NOT EXISTS student_bill_1d
(
	student_id    STRING COMMIT 'student_id '
   ,product_id    STRING COMMIT '消费产品id'
   ,bill_date    STRING COMMIT '结算日期(yyyy-mm-dd)'
   ,month        STRING COMMIT '周期月份yyyymm'  
   ,cost         STRING COMMIT '结算金额'
)
COMMIT '学生消费产品账单天级'
PARTITIONED BY
(
	ds 		    STRING  COMMIT '按天分区(yyyymmdd)'
)

LIFECYCLE  3650     
;

ALTER TABLE student_bill_1d DROP iF EXISTS PARTITION (ds = '${bizdate}')
;

INSERT OVERWRITE TABLE student_bill_1d PARTITION (ds = '${bizdate}')

SELECT 
	student_id
	,product_code
	,SPLIT_PART(bill_date, ' ', 1, 1) as bill_date     -- 原 bill_date 字段精度到秒 2222-02-02 02:02:02
	,month
	,SUM(cost) as cost
FROM student.student_bill_base
WHERE ds=MAX_PT("student.student_bill_base")
GROUP BY
	student_id
	,product_code
	,bill_date
	,month
;

函数用法

1、查询的结果作为输入查询

odps将已查询的结果作为参数查询，只需将 查询语句 字符化，即可。

SELECT TO_CHAR('2222-02-22', 'yyyy-mm-dd');      -- 2222-02-22

SELECT TO_CHAR(TO_DATE('2222-02-22','yyyy-mm-dd'), 'yyyymm');   -- 222202

SELECT TO_CHAR ( TO_DATE (( 
				SELECT DISTINCT month 
				FROM db_test.student 
				WHERE ds = MAX_PT ( "db_test.student" )
				ORDER BY month DESC
				limit 1
				), 'yyyymm'), 'yyyymm');   
-- 222202

2、日期函数

1）获取当前时间

select GETDATE();    -- 2202-12-12 12:12:12

2）日期转字符串

select TO_CHAR(GETDATE(), "yyyymmdd");       -- 22021212

3）计算 指定日期段内的天数

select DATEDIFF("2023-01-01", "2022-01-01");      -- 365

4）获取日期的下一天

set odps.sql.type.system.odps2=true;
select date_add(date '2022-12-12', 1);     --  2022-12-13 

5）获取日期的前一天

set odps.sql.type.system.odps2=true;
select date_add(date '2022-12-12', -1);     --  2022-12-11

3、字符串函数

1）字符串切片

select SPLIT_PART('2222-12-12 12:12:12', ' ', 1, 1);     -- '2222-12-12'

2）字符串拼接

select CONCAT('2222-12-12', ' '.'00:00:00');      -- 2222-12-12 00:00:00

3）字符串替换

select REPLACE('2222-12-12', '-', '');            -- 20221212

4）字符串左补0

-- 字符串 指定长度，不够的话 左补 0
select lpad("1216", "5", "0");        ---   01216
select rpad("1216", "5", "0");        ---   12160

5）字符串右补0

-- 字符串 指定长度，不够的话 右补 0
select rpad("1216", "5", "0");        ---   12160

6）keyvalue取值

-- 拿到 指定 key 的 val值
select KEYVALUE("k1:v1;k2:v2;k3:v3", ";", ":", "k1");     -- v1

7）字符串转日期

select TO_DATE('2022-12-12', 'yyyy-mm-dd');    -- 2022-12-12 

select TO_DATE('2022-11-11 00:00:00', 'yyyy-mm-dd hh:mi:ss');  -- 2022-11-11 00:00:00

8）字符串大小写

-- 转小写
select TOLOWER('QWER');      -- qwer 

-- 转大写
select TOUPPER('qwer');      -- QWER

4、获取当月的天数

• 假如每天产一条记录，基于此，获取每条记录数所在的每月的天数是多少？

-- 假如每天产一条记录，基于此，获取每条记录数所在的每月的天数是多少？
-- 假数据如下：
select '2023-05-25 00:00:00' as bill_date. '202305' as month
union all
select '2023-05-24 00:00:00' as bill_date. '202305' as month
select '2023-05-23 00:00:00' as bill_date. '202305' as month
select '2023-05-22 00:00:00' as bill_date. '202305' as month
select '2023-05-21 00:00:00' as bill_date. '202305' as month
... ...
select '2023-05-01 00:00:00' as bill_date. '202305' as month

select month, 
	 count(split_part(split_part(bill_date, " ",1, 1), 1,2) ) as days
from my_table_name
where partition = "20230525"
group by  month
;
	

更多函数：https://help.aliyun.com/document_detail/48973.html