阿里odps基于mysql

转载

IT独行侠客 2024-07-07 22:14:37

文章标签 阿里odps基于mysql 分区表数据 sql 文章分类 MySQL 数据库

阿里云大数据计算服务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 语句时允许指定部分分区为静态，但是静态分区必须是高级分区