阿里云大数据计算服务MaxCompute（远ODPS）

阿里云大数据计算服务MaxCompute（原ODPS）

概要

使用场景

海量数据，实时性要求不高，不具备数据库的特征，如事务、主键约束、索引等

保留字

sql语句的关键字

分区表

创建表的时候创建分区列，提高sql效率

类型转换

显式转换和隐式转换

1. 显式转换：cast 将一种数据类型的值转化为另一种类型的值的行为 cast( string as int)
2. 隐式转换: MaxCompute利用上下文环境自动转换类型
   1. sql内建函数，输入参数
   2. case when
   3. 分区列仅支持string、比今天类型不支持其他任意类型的隐式类型转换
   4. union all 要求数据类型，列个数，列名称必须完全一样

DDL语句

表操作

1. 创建表
   普通方法，销售例子：create table if not exists sale_detail (shop_name string, customer_id string, total_price double )
   partitioned by (sale_date string, region string)//partitioned by为设置分区列仅仅支持string、bigint，源表分区只能为6级，分区个数按照具体的project配置，默认为60 000个
   select方法：create table sale_detail_ctasl as select * from sale_detail ;//从一个表复制到另一个表（表结构+数据），但是不复制分区属性
   常量 select 方法：create table … as select [列名]，[常量] as [列名] ， from othertable
   like select 方法：create table like 表名 ；只复制表结构，不复制数据
2. 删除表/重命名表
   drop table [if exists ] table_name
   alter table table_name rename to new_table_name
3. 修改表的注释/修改表的生命周期属性/禁止表的生命周期/修改表的修改时间
   alter table sale_detail set comment ‘’;
   alter table table_name set lifecycle days ;//如果表是非分区表，自最后一次数据修改开始计算，经过days天后数据仍未改动，则自动被回收，如果是分区表，则根据分区LastDataModifiedTime 判断该分区是否该被回收
   alert table table_name [partition_spec] disable lifecycle;
   alter table table_name touch;//提供touch操作修改表的LastDataModifiedTime，把他设定为当前时间
4. 清空非分区表里的数据/备份表的数据
   truncate table table_name
   alter table my_log partition(ds =‘20140101’) archive;//压缩存储空间50%获取raid file，当机器损坏则恢复数据时间比较长

视图操作

1. 创建视图
   举例 create view if not exists sale_detail_view() comment ‘’ as select * from sale_detail;
2. 删除视图
   drop view [if exists] view_name
3. 重命名视图
   alter view view_name rename to new_view_name

列和分区操作

1. 添加分区、删除分区
   alter table sale_detail add if not exists partition (sale_data="",region="");
   alter table sale_detail drop partition(sale_data="",region="")
2. 添加列、修改列名、修改列、分区注释、修改分区的修改时间、修改分区值
   alter table table_name add columns(col_nam1,type1,col_name2,type2)
   alter table table_name change column old_column rename to new_col_name;
   alter table table_name change column col_name comment ‘comment’
   alter table table_name touch;

DML语句

1. insert 语句(加入table关键字)
   insert into:追加数据
   insert overwrite:覆盖数据

   • 插入时使用select，对应关系是列的顺序不是表和列的关系
   • 分区列不能出现在select 列表中
     多路输出multiinsert（多条插入语句）
   • 单个sql了里面可以有128路输出，超过128报语法错误
   • 在一个multi insert 中，对于分区表，同一个目标分区不可以出现多次；对于非分区表，改表不能出现多次（防止出现相同的列）
   • 对于同一张分区表的不同分区不能同时有insert overwrite 和insert into 操作，否则报错返回。
     输出到动态分区：使用select 提供分区的值
   • 目前，在动态分区功能的sql中，单个进程最多只能输出512个动态分区，否则引发运行时的异常；
   • 动态分区不能超过2000个
   • 动态分区不可以有null，否则会引发异常
   • 如果目标表有多级分区，在运行insert 语句时允许指定部分分区为静态，但是静态分区必须是高级分区

2. select 语句：
   group by

   • 用group by 的key 可以是输入的列名
   • 可以是输入表的列构成的表达式，*但是不能是表的别名
   • 没有聚合函数的列必须在group by中
   • 规则1优先于2
     order by
   • order by 必须和limit共用
   • order by 必须加列的别名
     distribute by 和sort by
   • distribute by 对数据按照某几列的值做hash分片，必须使用select 的输出列的别名
   • distribute by 和sort by 必须在一起
   • order by 和group by 不能和 distribute by 和sort by 一起
     子查询
   • 子查询必须要有别名

3. union 语句

   • 只允许128路union all
   • 其他和sql一样

4. join 语句

   • 和sql一样
   • mapjoin hit

5. explain 语句