ODPS开发笔记

数据定义语言(DDL SQL)

#【建表】
CREATE TABLE [IF NOT EXISTS] <table_name>
[(<col_name> <data_type> [comment <col_comment>],...)]
[comment <table_comment>]
[partitioned by (<col_name> <data_type> [comment <col_comment>],...)]
[lifecycle <days>]
[as <select_statement>]

【IF NOT EXISTS的功能】
在创建表时，在table_name不存在的情况下，如果不指定if not exists选项则返回出错；
若指定此选项，则无论是否存在同名表，即使原表结构与要创建的目标表结构不一致，均返回成功。已存在的同名表的元信息不会被改动。

#【快速建表】
CREATE TABLE <table_name> AS SELECT <column_list> FROM <table_name> WHERE ...;
CREATE TABLE <table_name> LIKE <table_name>;

【AS和LIKE的区别】
数据：AS可以带入数据，可以依赖多张表；LIKE只能复制单张表的表结构，不能带入数据；
属性：AS不能带入LEFCYCLE、分区键信息、注释等；LIKE不能带入LEFCYCLE、但是可以带入分区键和注释信息。

ODPS视图操作

#【创建视图】
CREATE VIEW if not exists <view_name>(<col1,col2,...>)
COMMENT <comment>
AS <select_statement>
#【删除视图】
DROP VIEW [if exists] <view_name>;
#【重命名视图】
ALTER VIEW <view_name> rename to <new_view_name>

数据操作语言(DML SQL)

#【删表】
DROP TABLE [if exists] <table_name>;
#【改表名】
ALTER TABLE <table_name> rename to <new_table_name>;
#【添加分区】
ALTER TABLE <table_name> ADD [IF NOT EXISTS] PARTITION <partition_spec>
【partition_spec】(partition_col1 = partition_col_value1,partition_col2 = partition_col_value2,...)
#【删除分区】
ALTER TABLE <table_name> DROP PARTITION[IF EXISTS] <partition_spec>
【partition_spec】(partition_col1 = partition_col_value1,partition_col2 = partition_col_value2,...)
#【添加列】
ALTER TABLE <table_name> ADD COLUMNS(<col_name1> <type1>,<col_name2> <type2>,...)
#【修改列名】
ALTER TABLE <table_name> CHANGE COLUMN <old_col_name> rename to <new_col_name>;
#【修改表注释】
ALTER TABLE <table_name> SET COMMENT 'tb1 comment';
#【修改列/分区注释】
ALTER TABLE <table_name> CHANGE COLUMN <col_name> COMMENT 'comment'
#【更新】
INSERT overwrite INTO TABLE <table_name> [partition (partcol1 = val1,partcol2 = val2,...)]
<select_statement>
FROM <from_statement>;

动态分区

在insert overwrite到一张分区表时，可以在语句中指定分区到值。也可以用另一种更加灵活到方式，在分区中指定一个分区列名但不给出值。相应的，在SELECT子句中的对应列来提供分区的值。

注意：
在分布式环境下，单个进程最多只能输出512个动态分区。
任意动态分区SQL不可以生成超过2000个动态分区。
动态分区的分区值不可以为NULL。
如果目标表有多级分区，在运行insert语句时允许指定部分分区为静态，但是静态分区必须是高级分区。

#【插入动态分区】
INSERT INTO TABLE <table_name> partition(partcol1)
SELECT col1,col2,...,to_char(sale_date,'yyyymmdd') AS partcol1 FROM <table_name2>

多路输出(MULTIINSERT)

ODPS SQL支持在一个语句中插入不同的结果表或者分区。

【多路输出】
FROM <from_statement>
INSERT overwrite/into table <table_name1> [partition (<partcol1=bal1,partcol2=val2,...>)]
<select_statement1>
INSERT overwrite/into table <table_name2> [partition (<partcol1=bal1,partcol2=val2,...>)]
<select_statement2>

注意：
单个SQL最多可以写128路输出。
对于分区表，同一个目标分区不可以出现多次
对于未分区表，该表不能作为目标表出现多次
对于同一张分区表的不同分区，不能同时有inert overwrite和insert into操作。

数据查询语言(SELECT)

#【查询】
SELECT [all | distinct] <select_expr>,<select_expr>,...
FROM <table_reference>
[WHERE <where_condition>]
[GROUP BY <col_list>]
[ORDER BY <order_condition>]
[DISTRIBUTE BY <distribute_condition> [SORT BY <sort_condition>]]
[LIMIT <number>]

注意：where子句，如果在in/not in 条件后加子查询，子查询只能返回一列值，且返回值的数量不能超过1000条记录。

MAPJOIN HINT

使用情景：一个大表和一个或多个小表做join。
基本原理：将用户指定的小表全部加载到执行join操作的程序内存中，从而加快join的执行数据。

注意事项：
left outer join的左表必须是大表。
right outer join的右表必须是大表。
inner join的左表右表均可以作为大表。
full outer join不能使用mapjoin。
支持小表为子查询。
引用小表或者子查询时，需要使用别名。
可以使用不等值连接或者使用or连接多个条件。
目前最多支持指定6张小表。
所有小表占用的内存总和不得超过2G。

#【MAPJOIN HINT】的使用场景
SELECT /*+mapjoin(a)*/a.total_price,b.total_price
FROM shop a join sale_detail b on a.total_price < b.total_price
or a.total_price + b.total_price < 500;

#【MAPJOIN HINT】在一般join可以解决的SQL里，也可以使用MAPJOIN去提高性能。
SELECT /*+mapjoin(a)*/a.shop_name,b.customer_id,b.total_price
FROM shop a join sale_detail b on a.shop_name = b.shop_name;

联合(UNION ALL)

将两个或者多个SELECT操作返回的数据集联合成一个数据集

<select_state> union all <select_state>

注意：
ODPS支持UNION ALL而不支持UNION，也就是说不支持记录去重。
UNION ALL不支持顶级的两个查询结果合并，要改写成一个子查询的形式。
UNION ALL操作对应的各个子查询的列个数、名称和类型必须一致。
一般情况下，最多允许128路union all，超过此限制报语法错误。

窗口函数

window_func() over (partition by col1,[col2...][asc|desc][,col2[asc|desc]...]] windowing_clause)

把数据按照一定条件分成多组称为开窗，每个组称为一个窗口，partirion by部分用来指定开窗的列。
分区列的值相同的行被视为在同一个窗口内order by用来指定数据在一个窗口内如何排序。
windowing_clause部分可以用rows指定开窗方式，有两种方式：

【第一种】表示窗口范围是从前或后X行到前或后y行。
rows between x <preceding|following> and y <preceding|following>
【第二种】窗口范围是从前或后第x行到当前行。
rows x <preceding|following>

注意：
窗口函数只能出现在SELECT子句中。
窗口函数中不要嵌套使用窗口函数和聚合函数。
不可以和同级别的聚合函数一起使用。
一个ODPS SQL语句中，可以使用至多5个窗口函数。
Partition开窗时，同一窗口内最多包含1亿行数据。
用rows开窗时，x、y必须为大于等于0的整数常量，限度范围0～10000，值0代表当前行。
必须指定order by才可以用rows方式指定窗口范围。
并非所有的窗口函数都可以用rows指定开窗方式，支持这种用法的窗口函数有avg、count、max、min、stddev和sum。

SELECT max(id) OVER (partition by name order by id desc),name,id from t_test;
【平滑数据案例】
SELECT pid,days,cnt,avg(cnt) over (partition by pid order by days rows between 3 preceding 
and 2 following) from t time windows

命令行工具(CLT)

【查看Project下的所有表】
SQL>ls TABLES;
【正则表达式显示前缀为page的表】
SQL>SHOW TABLES 'page.*';
【查看page_view表下的所有分区】
SQL>LIST PARTITIONS page_view;
【查看表page_view的结构信息】
SQL>DESCRIBE TABLE page_view;
SQL>DESC table page_view;
【查看表page_view的分区PARTITION(dt='20111217',country='US')的元信息】
SQL>DESC PARTITION page_view(dt='20111217',country='US')

pythonSDK导出ODPS数据的Python脚本DEMO

from odps import ODPS
o = ODPS(<accesskeyId>,<accessId>,<project_name>)
import pandas as pd
from odps.df import DataFrame

sql = '''create table ... as ...'''

a = o.execute_sql(sql)
t = o.get_table('tmp_geo').to_df()
t_local = t.to_pandas()
t_local.to_csv('~/documents/用户位置信息.csv',index=None, encoding = 'utf8')
o.execute_sql('drop table tmp_geo')


t_local.head()
​```