阿里云大数据计算服务MaxCompute(原ODPS)
概要
使用场景
海量数据,实时性要求不高,不具备数据库的特征,如事务、主键约束、索引等
保留字
sql语句的关键字
分区表
创建表的时候创建分区列,提高sql效率
类型转换
显式转换和隐式转换
- 显式转换:cast 将一种数据类型的值转化为另一种类型的值的行为 cast( string as int)
- 隐式转换: MaxCompute利用上下文环境自动转换类型
1. sql内建函数,输入参数
2. case when
3. 分区列仅支持string、比今天类型不支持其他任意类型的隐式类型转换
4. union all 要求数据类型,列个数,列名称必须完全一样
DDL语句
表操作
- 创建表
普通方法,销售例子: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 表名 ;只复制表结构,不复制数据 - 删除表/重命名表
drop table [if exists ] table_name
alter table table_name rename to new_table_name - 修改表的注释/修改表的生命周期属性/禁止表的生命周期/修改表的修改时间
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,把他设定为当前时间 - 清空非分区表里的数据/备份表的数据
truncate table table_name
alter table my_log partition(ds =‘20140101’) archive;//压缩存储空间50%获取raid file,当机器损坏则恢复数据时间比较长
视图操作
- 创建视图
举例 create view if not exists sale_detail_view() comment ‘’ as select * from sale_detail; - 删除视图
drop view [if exists] view_name - 重命名视图
alter view view_name rename to new_view_name
列和分区操作
- 添加分区、删除分区
alter table sale_detail add if not exists partition (sale_data="",region="");
alter table sale_detail drop partition(sale_data="",region="") - 添加列、修改列名、修改列、分区注释、修改分区的修改时间、修改分区值
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语句
-
insert 语句(加入table关键字)
insert into:追加数据
insert overwrite:覆盖数据- 插入时使用select,对应关系是列的顺序不是表和列的关系
- 分区列不能出现在select 列表中
多路输出multiinsert(多条插入语句) - 单个sql了里面可以有128路输出,超过128报语法错误
- 在一个multi insert 中,对于分区表,同一个目标分区不可以出现多次;对于非分区表,改表不能出现多次(防止出现相同的列)
- 对于同一张分区表的不同分区不能同时有insert overwrite 和insert into 操作,否则报错返回。
输出到动态分区:使用select 提供分区的值 - 目前,在动态分区功能的sql中,单个进程最多只能输出512个动态分区,否则引发运行时的异常;
- 动态分区不能超过2000个
- 动态分区不可以有null,否则会引发异常
- 如果目标表有多级分区,在运行insert 语句时允许指定部分分区为静态,但是静态分区必须是高级分区
-
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 一起
子查询 - 子查询必须要有别名
-
union 语句
- 只允许128路union all
- 其他和sql一样
-
join 语句
- 和sql一样
- mapjoin hit
-
explain 语句