本文为您介绍SQL语句的常见问题。

如何查看MaxCompute表的最近访问时间?

您可以在DataWorks数据地图中查询表,进入表详情页面查看技术信息,获得表的最近访问时间。技术信息

除了使用UUID()函数,如何给MaxCompute表设置自增主键?

MaxCompute的应用场景是针对海量数据的批量计算,目前不支持在数据表里设置主键或者唯一性索引。

如果一开始并没有分区字段,是否可以增加或更改分区?

不支持在源表上直接增加或修改分区字段,分区字段一旦创建就不能修改。但可以重新创建一张分区表,使用动态分区SQL把源表数据导入新分区表中。

如何更新MaxCompute表或分区中的数据?

MaxCompute不支持直接对表数据进行更新(UPDATE)操作。

您需要把源分区或源表中的数据导入到新分区或新表中,在导入过程中执行相应的更新逻辑。新分区或新表可以与源分区或源表相同,即就地更新。

如何删除MaxCompute表或分区中的数据?

MaxCompute不支持直接对表数据进行删除(DELETE)操作。您可以通过如下方法进行删除:
  • 通过删除(DROP)表,达到删除数据的目的。
  • 若是非分区表,可以通过TRUNCATE TABLE table_name;语句清空表数据,也可以用INSERT OVERWRITE实现类似的功能。
    • 示例一:若想删除TableA表里的Col=1的数据,语句如下。
      INSERT OVERWRITE TABLE TableA SELECT a,b,c.... FROM TableA WHERE Col <> 1;
    • 示例二:执行如下语句删除全部数据。
      INSERT OVERWRITE TABLE TableA SELECT a,b,c.... FROM TableA WHERE 1=2;
  • 若是分区表,可以通过ALTER TABLE table_name DROP IF EXISTS PARTITION(分区名=‘具体分区值’)语句,只需删除对应的分区即可删除对应的数据。
    假设表testtable的分区列为ds,执行如下语句删除ds='20170520'的分区。
    ALTER TABLE testtable DROP IF EXISTS PARTITION (ds='20170520');
  • 通过INSERT和WHERE条件,把需要的数据导入到另一张新分区或新表中,INSERT语句支持源表和目的表是同一张表。
    insert overwrite table sale_detail select * from sale_detail where name=“mengyonghui”;

在MaxCompute中,一张表的分区的数量是否越多越好?

在MaxCompute中,一张表最多允许有60000个分区,同时每个分区的容量没有上限。但是分区数量过多,会导致统计和分析过程非常不方便。

MaxCompute限制单个作业中最多不能超过一定数量的Instance,而作业中的Instance数量和输入的数据量以及分区数量密切相关的,所以您需要根据业务需要,选择合适的分区策略。

如何用MAPJOIN缓存多张小表?

如果SQL运行中需要用MAPJOIN缓存多张表,那么您可以在MAPJOIN的括号中写表的别名。

此示例为鸢尾花的数据,表名为iris,表结构如下所示。
+——————————————————————————————————————————+

| Field           | Type       | Label | Comment                                     |
+——————————————————————————————————————————+

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

+——————————————————————————————————————————+
				
SQL语句如下所示。
select 
  /*+ mapjoin(b,c) */
  a.,
  b.cnt as cnt_category,
  c.cnt as cnt_all
from iris a
join
(
  select count() as cnt,category from iris group by category
) b
on a.category = b.category
join 
(
  select count(*) as cnt from iris
) c;              

MaxCompute中,是否可以添加或删除列?

可以添加列,但不可以删除列。

如何添加列?

如果表中已经有了一部分数据,则新添加列的值为NULL。

添加列的语法,如下所示。
ALTER TABLE table_name ADD COLUMNS (col_name1 type1, col_name2 type2…)               

如何删除列?

MaxCompute目前不支持删除表的列。如果您有删除表中列的需求,可以使用如下方法实现:
  1. 通过SQL语句创建一张新表,如下所示。
    CREATE TABLE new_table_name as SELECT c1,c2,c3 FROM table_name;
  2. 删除原来的表,重命名新表,如下所示。
    ALTER TABLE new_table_name RENAME TO table_name;

如何查看MaxCompute数据量?

查看数据量包含查看数据条数和查看占用的物理空间的大小。
  • 针对全表,查看物理空间,您可以使用desc命令,如下所示。查看物理空间
  • 如果要查看表的数据条数,需要使用SQL语句。例如select count() as cnt from iris;
  • 对于分区表,如果要查看单个分区的数据情况,请参考下图(其中area=’N’,pdate=’1976’是partition_table2表的一个分区)。查看分区表

    如果使用SQL语句则要用WHERE来过滤分区,例如select count() as cnt from partition_table2 where area=’N’and pdate=’1976’;

INSERT语句执行过程中出现错误,会损坏原有数据吗?

不会损坏原有数据。MaxCompute满足原子性,INSERT成功更新或者失败回滚。

在MaxCompute SQL执行过程中,报错Table xx has n columns, but query has m columns如何处理?

MaxCompute SQL使用INSERT INTO或OVERWRITE插入数据时,需要保证SELECT查询出来的字段和插入的表的字段匹配,匹配内容包括顺序、字段类型,总的字段数量。目前MaxCompute不支持插入表的指定字段,其他字段为NULL或者其他默认值的情况,您可以在SELECT的时候设置成NULL,例如SELECT ‘a’,NULL FROM XX

执行MaxCompute SQL的过程中,报错ODPS-0130071,如何处理?

  • 问题现象:执行MaxCompute SQL的过程中,出现类似如下报错。
    FAILED: ODPS-0130071:Semantic analysis exception - Both left and right aliases encountered in JOIN : line 3:3 ‘xx’: . I f you really want to perform this join, try mapjoin                   
  • 问题原因:
    • SQL关联条件ON中包含非等值连接,例如table1.c1>table2.c3
    • SQL中的Join条件的某一边的数据来自两张表,例如table1.col1 = concat(table1.col2,table2.col3)
  • 解决方法:
    • 修改SQL语句。
    • 如果其中有一张表比较小,您可以使用MapJoin方法。

如何更新和删除数据?

MaxCompute不支持直接对表数据删除(DELETE)和更新(UPDATE)的语法。
  • 更新(UPDATE)数据

    只能把源分区或表数据导入到新分区或表(新分区或表可以与源分区或表相同),在导入过程中执行相应的更新逻辑。对于非分区列,只支持重命名和新建列,不支持对列的删除。

    MaxCompute不支持UPDATE语句,建议您把更新后的结果筛选出来,然后用INSERT OVERWRITE的方法写回原表。
    insert overwrite table t1 select a1,case when id=1 then 'a' else 'b' end as a2 from t1;
  • 删除(DELETE)的数据
    • 可以通过删除(DROP)表达到数据删除目的。
    • 非分区表可以通过TRUNCATE TABLE table_name;语句清空表数据。
    • 分区表可以通过ALTER TABLE table_name DROP IF EXISTS PARTITION(分区名=‘具体分区值’)删除分区达到删除整个分区数据的目的。
      ALTER TABLE TABLE_NAME DROP [IF EXISTS] PARTITION partition_spec;
    • 通过INSERT和WHERE条件把需要的数据导入到另一张新分区或表中或就地更新,INSERT语句支持源表和目的表是同一张表。示例如下。
      • 删除A表里的col=1的数据。
        insert overwrite table tableA select * from tableA where col<>1;
      • 删除全部数据。
        insert overwrite table tableA select * from tableA where 1=2;
      • 删除A表某分区里id=123的数据。
        insert overwrite table yunfengdhTopic2 partition (ds='20160802') select id, name, rq, addtional from yunfengdhTopic2 where id<>123 and ds='20160802';
      • 删除分区全部数据。
        insert overwrite table yunfengdhTopic2 partition (ds='20160730') select id, name, rq, addtional from yunfengdhTopic2 where 1=2 and ds='20160730';

MaxCompute中分区和分区列的区别是什么?

MaxCompute里可以对表做分区,分区表会有分区列。用户可以针对分区列创建分区。

例如分区ds=20150101,此处ds是一个分区列,而ds=20150101是一个分区。

如何往MaxCompute表插入数据?

  1. 创建一个表,例如DUAL表。执行下述语句可以生成一张有1条记录的DUAL表。
    create table dual(cnt bigint);
    insert into table dual select count(*) as cnt from dual;
  2. 执行下述语句,即可向MaxCompute表中插入记录。
    insert into table xxxx select 1,2,3 from dual;

MaxCompute SQL中,使用NOT IN后面接子查询,子查询返回的结果是上万级别的数据量,但当IN和NOT IN后面的子查询返回的是分区时,返回的数量上限为1000。在NOT IN必须使用的情况下,该如何实现此查询?

您可以使用left outer join命令查询。
select * from a where a.ds not in (select ds from b);
改成如下语句。
select a.* from a left outer join (select distinct ds from b) bb on a.ds=bb.ds where bb.ds is null;              

如何处理单字段大于8 MB的限制?

目前由于存储机制限制,MaxCompute表中单个字段的最大长度不能超过8 MB。对于超过8 MB的字段,建议您拆分成多个字段。具体的拆分逻辑您可以根据业务特性设计,保证每个字段不超过8 MB即可。

由于复杂结构的超大字段在数据开发和分析中会严重影响计算性能,因此建议根据数仓建设规范来设计您的数据架构,避免超大字段:
  • 具有复杂结构的原始数据,作为ODS层,最好以压缩的方式归档。
  • 定时(例如每天)对ODS层的增量数据做数据清洗,复杂字段拆分为多个简单字段,然后存储在CDM层的表中,便于数据的统计分析。

MaxCompute表如何设置自增长列?

MaxCompute目前不支持自增长序列功能,如果有这样的需求,且数据量比较小,建议使用ROW_NUMBER来实现。

如何查看某一天MaxCompute上执行的所有SQL?

请参见MaxCompute如何在客户端上查看一个任务的历史信息?

使用COALESCE函数只要超过一个expression,就会报错ODPS-0130071,如何处理?

  • 问题现象:使用COALESCE函数只要超过一个expression,就会报错,报错信息如下。
    FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 8:9 "$.table"                    
    报错的SQL如下。
    SELECT 
    md5(concat(aid,bid)) as id
    ,aid
    , bid
    , sum(amountdue) as amountdue
    , coalesce(
    sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
    decode(get_json_object(extended_x, '$.table'), NULL, 0, 1)
    ) as tableparty
    , decode(sum(headcount),null,0,sum(headcount) ) as headcount
    , 'a' as pt
    FROM e_orders
    where pt='20170425'
    group by aid, bid;                      
  • 问题原因:GROUP BY后面缺少分组字段,因此报错。
  • 解决方法:如下表达式的返回值实际上是字段,需要把整个表达式写在GROUP BY后面。
    coalesce(
    sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
    decode(get_json_object(extended_x, '$.table'), NULL, 0, 1)
    ) as tableparty
    , decode(sum(headcount),null,0,sum(headcount) ) as headcount                   

如何设置MaxCompute表的主键,实现唯一性约束?

MaxCompute的应用场景是针对海量数据的批量计算,目前不支持在数据表里设置主键或者唯一性索引。

DOUBLE类型数据精度问题

  • 问题现象:数据量特别大的情况下,对DOUBLE类型的数据执行类似SUM的操作,真实结果和预期结果有所偏差。
  • 问题原因:因为数据精度造成的结果偏差。DOUBLE类型是8字节双精度的浮点数。
  • 解决方法:目前您可以考虑先用STRING类型存放数据,然后编写UDF处理数据从而实现任意精度的计算。

执行TO_DATE函数报错没有分钟部分是什么原因?

  • 问题现象:执行SQL语句to_date(‘2016-07-18 18:18:18’, ‘yyyy-MM-dd HH:mm:ss’)时,报错如下。
    FAILED: ODPS-0121095:Invalid arguments - format string has second part, but doesn’t have minute part : yyyy-MM-dd HH:mm:ss                   
  • 问题原因:日期格式写错。mmMM都是表示月份,表示分钟需要使用mi

隐式类型转换时报错ODPS-0121035,如何处理?

  • 问题现象:执行SQL时报错如下。
    FAILED: ODPS-0121035:Illegal implicit type cast - in function to_char, in function cast, string datetime’s format must be yyyy-mm-dd hh:mi:ss, input string is xxx。        
  • 问题原因:如果您调用一个需要传入DATETIME类型参数的函数,却传入一个STRING类型的字段,系统会对此字段进行隐式类型转换。目前仅支持yyyy-mm-dd hh:mi:ss类型的字符串隐式转换。如果字符串中的数据不是此格式,就会抛出这个错误。
  • 解决方法:建议您使用TO_DATE函数把STRING类型的数据转成DATETIME类型的数据。如果字符串的类型和TO_DATE要求的不一样,请使用UDF进行解析。

SQL语句中对DOUBLE类型的数据进行等值比较,为什么结果不符合预期?

由于MaxCompute中DOUBLE类型的数值存在一定的精度差,因此不建议直接使用等号(=)对两个DOUBLE类型的数据进行比较。

请对两个DOUBLE类型数据相减,然后取绝对值,当绝对值足够小的时候,认为两个DOUBLE类型的数据数值相等。

执行MaxCompute SQL时,报错输入表过多,如何处理?

  • 问题现象:执行MaxCompute SQL时,报错如下。
    FAILED: ODPS-0123065:Join exception - Maximum 16 join inputs allowed
  • 问题原因:MaxCompute SQL最多支持6张小表的MAPJOIN,并且连续JOIN的表不能超过16张。
  • 解决方法:把部分小表JOIN成一张临时表作为输入表,减少输入表的个数。

执行SQL语句时,报错输出表的分区过多,如何处理?

  • 问题现象:执行SQL时报错如下。
    FAILED: ODPS-0123031:Partition exception - a single instance cannot output data to more than 2048 partitions                   
  • 问题原因:虽然目前单个MaxCompute表允许有6万个分区,但是单个任务涉及的输出表分区数量只允许有2048个。通常出现这个错误,是因为分区字段设置有问题,例如根据ID字段做的分区造成分区过多。
  • 解决方法:修改分区表的分区字段,以避免出现这种问题。

执行SQL报错Repeated key in GROUP BY,如何处理?

  • 问题现象:执行SQL时,报错如下。
    FAILED: ODPS-0130071:Semantic analysis exception - Repeated key in GROUP BY。
  • 问题原因:SELECT DISTINCT后面不能跟常量。
  • 解决方法:将SQL拆分为两层,内层处理没有常量的DISTINCT逻辑,外层加入常量数据。

执行SQL报错ODPS-0010000,如何处理?

  • 问题现象:执行SQL报错如下。
    FAILED: ODPS-0010000:System internal error - OTS filtering exception - Ots read range partitions exceeds the specified limit:10000:  tableName:xxxx , please check hive conf key                     
  • 问题原因:目前MaxCompute单张表支持6万个分区,但是一次查询里最多只支持涉及1万个分区。此报错常见的原因如下:
    • 分区未写分区条件。
    • 使用类似用户ID的字段作为分区字段,导致分区数量特别多。
  • 解决方法:
    • 如果是未写分区条件,补上分区条件即可。
    • 如果是分区列不合适导致分区数量太多,请考虑更改分区列。

    如果问题还未解决,请提工单

执行SQL报错ODPS-0130089,如何处理?

  • 问题现象:执行SQL调用UDF时,报错如下。
    FAILED:ODPS-0130089 Invalid UDF reference - class not ‘xxx’ found for function                    
  • 问题原因:出现上述报错,是因为未找到您定义的类。
  • 解决方法:
    • 请在代码里核对路径名和类名是否正确,例如大小写是否正确。
    • 使用解压工具解压JAR包,查看里面的class文件是否存在。函数使用的JAR包名称,可以通过在客户端中执行list functions;获取。

外关联后发现数据条数比原表多,是什么原因?

  • 问题现象:执行如下MaxCompute SQL语句。
    Select count(*) from table1 a left outer join table2 b on a.id = b.id;

    执行完毕后,查询返回结果的条数大于table1的数据条数。

  • 问题原因:上述的SQL是table1通过id字段和table2的id字段做左外关联。所以会出现以下几种情况:
    • 如果是table2表中找不到关联数据,table1也会返回一条数据。
    • 如果table1找不到但是table2能找到,则不返回结果。
    • 如果table1和table2都能找到数据,这个关联逻辑和普通的内关联一样。如果同样的id字段在table2中能搜到数据的话,那返回结果就是table1和table2的笛卡尔积。

    示例

    table1的数据如下所示。
    id values
    1 a
    1 b
    2 c
    table2的数据如下所示。
    id values
    1 A
    1 B
    3 D
    则执行select * from t1 left outer join t2 on t1.id = t2.id;的结果,如下所示。
    id1 values1 id2 values2
    1 b 1 B
    1 b 1 A
    1 a 1 B
    1 a 1 A
    2 c NULL NULL
    由此可见:
    • id=1的数据两边都有,于是执行了笛卡尔积,返回4条。
    • id=2的数据只有table1有,于是返回了1条数据。
    • id=3的数据只有table2有,table1里没数据,于是不返回。
  • 解决方法:首先确认出现数据条数增加是否是因为table2的数据导致。
    Select id,count() as cnt from table2 group by id having cnt>1 limit 10;
    此处加了limit 10是担心如果table2中的数据条数很多,会刷屏。如果只是确认问题的话,看前几条数据验证即可。如果是在重复的情况下不希望执行笛卡尔积,希望有类似SQL里IN的功能,可以改写SQL为如下语句。
    Select * from table1 a left outer join (select distinct id from table2) b on a.id = b.id;

删除分区报错ODPS-0130161,如何解决?

  • 问题现象:执行如下MaxCompute SQL语句。
    alter table pol_self_overall_2016_part drop partition;                  
    返回如下报错。
    FAILED: ODPS-0130161:Parse exception - line 1:44 mismatched input '<EOF>' expecting ( near 'partition' in alter table statement                        
  • 问题原因:MaxCompute分区表删除分区不支持批量删除,需要逐个分区删除,每次需要指定具体分区。
  • 解决方法:删除分区时,指定具体的分区。按照如下语法格式修改原SQL语句。
    ALTER TABLE TABLE_NAME DROP [IF EXISTS] PARTITION partition_spec;
        partition_spec:
            : (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)                    

非分区表中有部分数据重复,由于表数据量比较大,全部重新导入代价较大,如何删除表中的重复数据呢?

如果每一列都一样,可以对所有列执行GROUP BY。例如,非分区表table1有三列c1,c2和c3,可以执行如下语句。
INSERT OVERWRITE TABLE table1 SELECT c1, c2, c3 FROM table1 GROUP BY c1, c2, c3;                
说明 建议您在执行此操作前,做好数据备份工作并根据数据量评估此方式的代价是否比重新导入的代价低。

向MaxCompute表中插入FLOAT类型的数据报错,需要如何解决?

MaxCompute2.0支持的基本数据类型请参见数据类型版本说明。其中FLOAT数据类型没有常量定义,若要插入该类型数据,可以使用CAST函数将数据进行类型转换。例如cast(5.1 as float)将字符串‘5.1’转为FLOAT类型5.1。

目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY)时,需要用set命令开启,该命令有两种方式:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:执行set命令setproject odps.sql.type.system.odps2=true;将Project级别的新数据类型打开。此命令需要项目Owner执行。

相同数据运行INSERT SELECT语句的结果和运行SELECT的结果为什么不一致?

  • 问题现象:对相同的STRING类型字段分别执行SQL语句,出现小数位不统一的现象。使用SELECT语句保留2位小数,使用INSERT SELECT语句,结果显示多个小数位。
  • 问题原因:对于INSERT SELECT操作而言,原始字段类型是STRING,在隐式转换到目标类型DECIMAL的过程中,先转换为DOUBLE,然后在DOUBLE类型数据的基础上做了ROUND。由于DOUBLE类型本身是不精确的,虽然做了ROUND,但是依然可能显示多个小数位。
  • 解决方法:此类情况在处理时,建议使用显示转换,例如下面语句直接加上CASE显示转换为DECIMAL类型。
    CASE WHEN pcm.abc IS NULL THEN 0 
                        ELSE ROUND(cast(pcm.abc as decimal) ,2) 
                    END abc                        

补数据的时候,应该选择INSERT INTO插入数据,结果选成INSERT OVERWRITE导致原库里的30 GB数据被清理,可以恢复吗?

INSERT OVERWRITE相当于执行了先删除后插入操作,不能恢复,需要重新插入数据。

已经指定了分区条件,为何还是会提示禁止全表扫描?

  • 问题现象:在两个项目里执行如下同一段代码,一个项目中成功,一个项目中失败。
    select t.stat_date 
    from fddev.tmp_001 t  
    left outer join (select '20180830' as ds from fddev.dual ) t1 
    on t.ds = 20180830
    group by t.stat_date;    
    失败报错如下。
    Table(fddev,tmp_001) is full scan with all partisions,please specify partition predicates.
  • 问题原因:在进行SELECT操作时,如果需要指定分区请使用WHERE子句。使用ON属于非标准用法。

    执行成功的项目设置了允许非标准SQL的行为,即执行了set odps.sql.outerjoin.supports.filters=true命令,此配置会把ON里的条件转换成过滤。该项设置可用于兼容HIVE语法,但不符合SQL标准。

  • 解决方法:建议将分区过滤条件放在WHERE子句里。

运行SQL语句查询表dxlus中数据,dxlus表中数据为1万条,查询一直处于Job Quening状态,无法执行,原因是什么?

请排查任务运行状态,可能有个任务运行完了所有的作业,请先中止此任务。

查询SQL语句报错ValidateJsonSize error,如何解决?

  • 问题现象:执行包含200个Union All的SQL语句select count(1) as co from client_table union all ...出现如下报错。
    FAILED: build/release64/task/fuxiWrapper.cpp(344): ExceptionBase: Submit fuxi Job failed, {
        "ErrCode": "RPC_FAILED_REPLY",
        "ErrMsg": "exception: ExceptionBase:build/release64/fuxi/fuximaster/fuxi_master.cpp(1018): ExceptionBase: StartAppFail: ExceptionBase:build/release64/fuxi/fuximaster/app_master_mgr.cpp(706): ExceptionBase: ValidateJsonSize error: the size of compressed plan is larger than 1024KB\nStack      
  • 问题原因:
    • SQL语句转化成执行计划后,超过了底层架构限制的1024 KB导致SQL执行报错。执行计划的长度与SQL语句长度没有直接换算关系,暂时无法预估。
    • 由于分区量过大导致Plan Size超过限制。
    • 由于小文件比较多导致SQL运行失败。
  • 解决方法:
    1. 对于过长的SQL语句,建议拆分成多次运行,避免触发长度限制。
    2. 如果分区过大,需要调整分区个数,详情请参见分区
    3. 如果是由于小文件较多导致,请参见优化诊断

MYSQL支持的SUBSTRING_INDEX函数在MaxCompute中支持吗?

支持,详情请参见字符串函数

插入动态分区报错,是什么原因?

  • 问题现象:执行MaxCompute SQL插入动态分区时,报错如下。
    FAILED: ODPS-0123031:Partition exception - invalid dynamic partition value: province=上海
  • 问题原因:使用了非法的动态分区。动态分区是根据指定字段进行分区,目前不支持特殊字符和中文动态分区字段。
    插入动态分区时,有以下几种情况容易返回异常:
    • 在分布式环境下执行动态分区功能的SQL时,单个进程最多只能输出512个动态分区,否则会返回异常。
    • 任意动态分区SQL不允许生成超过2000个动态分区,否则会返回异常。
    • 动态生成的分区值不允许为NULL,否则会返回异常。
    • 如果目标表有多级分区,在运行INSERT语句时,允许指定部分分区为静态,但是静态分区必须是高级分区,否则会返回异常。

执行MaxCompute SQL时,报错Expression not in GROUP BY key是什么原因?

  • 问题现象:执行MaxCompute SQL时,报错如下。
    FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 1:xx ‘xxx’
  • 问题原因:在GROUP BY子句中,SELECT查询的列,必须是GROUP BY中的列或者是聚合函数(例如SUM或COUNT等)加工过的列。不支持直接引用非GROUP BY的列。详情请参见SELECT语法介绍

MaxCompute客户端使用-e参数执行SQL语句是否有长度限制?

有,SQL语句长度不支持超过2 MB。

MaxCompute客户端支持并行下载表吗?

MaxCompute客户端支持并行下载。

在并行下载时请注意本地服务器配置、CPU、网络带宽或服务器负载等情况,以免影响并行下载功能。

在MaxCompute客户端执行SQL可以使用自己创建的ECS调度资源吗?

如果执行SQL语句使用的是公共资源,可能会出现等待的情况。添加自己的调度资源请参见新增自定义数据集成资源组

MaxCompute中,单表中可以存放的最大列数是多少?

目前MaxCompute单表可以存放的最大的列数为1200列。如果您的列数超过限制,可以参考如下方式处理:
  • 对数据进行降维,缩减到1200列以内。
  • 修改数据的保存方式,修改成诸如设备证书、稀疏或稠密矩阵的保存方式。

MaxCompute中,查询出来的数据是根据什么排序的?

MaxCompute中表的读取是无序的。如果您没有进行自定义设置,查询获取的结果也是无序的。

如果您对数据的顺序有要求,需要对数据进行排序。例如,在SQL中,需要加上order by xx limit n对数据进行排序。

如果您需要对数据进行全排序,只需要把Limit后面的n设置为数据总条数+1即可。

说明 海量数据的全排序,对性能的影响非常大,而且很容易造成内存溢出等问题,需要尽量避免。

MaxCompute如何非交互式运行MaxCompute SQL?

在操作系统中,您可以通过Shell进行非交互式运行MaxCompute SQL:
  • 使用odps -f filename的方式,读取SQL文件并处理。
    说明 如果运行SQL,Filename文件的第一行是SQL表示已经进入SQL模式,如下所示。
    sql
    
      select from tablename where xxx;
    							
  • 如果只运行一个SQL语句,您可以使用MaxCompute SQL中的sqltext方法,如下所示。
    ./odpscmd -e "select  from dual;"             

    您可以通过odps -help获得更多的信息。

    此功能可以配合crontab命令定时执行SQL,建议您使用DataWorks的周期任务功能。

使用MaxCompute SQL自定义函数查询时,为什么提示内存不够?

出现上述报错,是因为数据量太大并且有倾斜,任务超出默认设置的内存。

执行set odps.sql.udf.joiner.jvm.memory=xxxx;命令增大内存。

MaxCompute与关系型数据库有什么区别?

  • MaxCompute适合海量存储和大数据分析,不适合在线服务。
  • MaxCompute SQL的语法是ANSI SQL92的一个子集,并有自己的扩展,与Oracle或MySQL等很类似。
  • MaxCompute表不支持主键、索引和字段约束。
  • MaxCompute表不支持UPDATE语句。
  • MaxCompute表不支持DELETE语句,只能DROP整个分区或表。在MaxCompute中创建表时,不允许指定字段默认值。
  • SELECT语句输出屏显的数据行数是受限制的,最大为10000条。不能通过SELECT下载数据,这不同于ODBC或JDBC的ResultSet方式。
  • 导出数据时,在MaxCompute中需要通过Tunnel命令、Dship工具或MaxCompute Tunnel SDK完成。

MaxCompute支持虚拟表吗?例如MYSQL中的DUAL表。

暂时不支持虚拟表,但是您可以手动创建DUAL表。

在项目空间中运行SQL语句时使用动态分区,将GMT格式化作为分区字段。产生大量分区和记录数,一直没有运行完成,是什么原因?

动态分区涉及的分区比较多,所以数据分发花费时间较多。

MaxCompute能否像MySQL一样灵活使用用户变量(即MySQL的@变量名)?

目前不支持参数化的SQL。

regexp_count(string source, string pattern[, bigint start_position])的第二个参数pattern是否支持嵌入查询语句?

不支持,您可以把语句改写为目前支持的语法,例如JOIN。

执行SQL语句报错Semantic analysis exception是什么原因?

SQL语句如下。
SELECT a.id as id > , IFNULL(concat('phs\xxx', a.insy, '\xxxb\xxx', IFNULL()))
报错信息如下。
Semantic analysis exception - Invalid function : line 1:41 'ifnull'

没有提供ifnull函数造成报错,需要用case when表达式或者coalesce命令。具体的SQL调试请参见其他函数

SQL能将MaxCompute的配置转移到另外一个阿里云账号上吗?

通过Package的方法可以实现,请参见MaxCompute多团队协同数据开发项目管理最佳实践

MaxCompute SQL设置过滤执行条件后为什么还会报错提示输入的数据超过100 GB?

先过滤分区,再取数据。取数据后,再过滤其他非分区字段。输入表的大小是根据取数据的步骤(分区过滤后,其他字段过滤前)的大小来计算的。

如何处理外部表执行SQL慢的问题?

  • OSS外部表中的GZ压缩文件读取慢
    • 问题现象:用户创建了一个OSS外部表,数据源为OSS中的GZ压缩文件,大小为200 GB。在读取数据过程中执行缓慢。
    • 解决方法:此类情况可能是由于Map端执行计算的Mapper数量过少,所以SQL处理慢。
      • 对于结构化数据,您可以设置以下参数调整单个Mapper读取数据量的大小,加速SQL执行。
        set odps.sql.mapper.split.size=256; #调整每个Mapper读取table数据的大小,单位是MB。       
      • 对于非结构化数据,您需要查看OSS外部表路径下的OSS文件是否只有1个。如果只有1个,由于压缩方式下的非结构化数据不支持拆分,所以只能生产1个Mapper,导致处理速度较慢。这种情况下,建议您在OSS对应的外部表路径下,将OSS大文件拆分为小文件,从而增加读取外部表生成的Mapper数量,提升读取速度。
  • 使用SDK搜索MaxCompute外部表数据速度慢
    • 问题描述:使用SDK搜索MaxCompute外部表数据速度慢。
    • 解决方法:外部表当前仅支持全量搜索,所以较慢,这种情况下建议您改用MaxCompute内部表。
  • 查询外部表Tablestore数据慢
    • 问题现象:查询外部表Tablestore的数据慢,同样的业务数据,1个实时写入Tablestore,1个定时写入MaxCompute,两个表结构和数据量一样。查询MaxCompute内部表耗时远小于查询Tablestore外部表。
    • 解决方法:这种情况可能是对1份数据进行了多次计算,导致速度慢。相比每次从Tablestore远程读取数据,更高效快速的方法是先一次性把需要的数据导入到MaxCompute内部,成为MaxCompute内部表,再进行查询。

SQL语句支持一次添加多个分区吗?

不支持,需要分多次逐个添加。

设置表的生命周期为3天,每个表的分区的存储量很大,如何清理分区表旧数据?

设置了生命周期的表超过设定时间没有修改会自动回收。

通过desc table_name partition(pt_spec)命令查看旧的分区修改时间是否在生命周期内修改过。通过desc nginx_log命令查看生命周期时间,MaxComptue每天17:00点进行回收,DataWorks上的数据显示是有延迟的,一般会延迟一天。

如何能提高查询效率?分区设置能调整吗?

当利用分区字段对表进行分区时,新增分区、更新分区内数据和读取分区数据均不需要做全表扫描,可以提高处理效率。详情请参见表操作MaxCompute的分区配置和使用

是否能将RDS中的表一次性导入到MaxCompute中?如果成功导入为什么物理存储显示是0?

物理存储显示并不是实时同步的,通常次日可以看到。您可以在DataWorks中使用SQL查看表数据是否正常同步。

对于查询的结果,目前提供了复制和下载功能,是否有哪些设置可以把这两个功能关闭掉,或者针对某些账号关闭掉?

在工作空间配置中关闭能下载Select结果开关。关于工作空间配置的详细信息,请参见工作空间配置

使用SQLTask进行SQL查询时,若查询结果条数大于限制的1000条,该如何获取所有数据?

您可以将SQL查询的结果集写到一张表中,然后用Tunnel将表中的数据全部下载下来。详情请参见导出SQL运行结果的方法总结

SQLTask中,按照下述方法返回结果集的数据量是否有限制?如果有限制,最大的返回结果集大小是多少?

Instance instance = SQLTask.run(odps, "sql语句");
instance.waitForSuccess();
List<Record> records = SQLTask.getResult(instance);              
有限制,您可以最多调整到5000。如果数据比较大,建议您把数据用Tunnel SDK导出来。

SQL Task查询数据和DownloadSession在使用及功能上,有什么不同?

SQLTask运行SQL并返回结果,返回条数有限制,默认是1000条。

DownloadSession下载某个存在的表里的数据,结果条数无限制。

在MaxCompute里运行SQL命令,怎样能下载超过一万行的数据?

如果您使用的是MaxCompute客户端,可以先把SQL结果插入到一张表中,然后使用Tunnel命令下载这个表中的数据,Tunnle命令的使用请参见Tunnel命令使用说明导出SQL运行结果的方法总结

MaxCompute如何查看SQL执行的费用?

您可以使用cost sql命令查询费用,详情请参见计费方式

MaxCompute SQL语句中LIKE模糊查询的WHERE条件是否支持正则表达式?

支持,例如select * from user_info where address RLIKE '[0-9]{9}' ;,这条语句用来表示查找9位数字组成的ID。

SQL执行报错ODPS-0121145,是什么原因?

详细的报错信息如下。
ODPS-0121145:Data overflow - param convert to Double result is nan, input param is NaN

原始数据中有空值。

多路输出的情况下,能否在REDUCE函数中获取到每一个Label输出表的表结构?

result1.getColumns()方法可以获取表的字段信息。

最新版本的SDK信息请参见ODPS SDK Core API,代码需要您自行调试。

用SQL对DOUBLE类型数据执行ROUND函数四舍五入,为何结果会存在偏差?

  • 问题现象:使用ROUND函数对DOUBLE类型的数据进行四舍五入,发现4.515四舍五入结果为4.51。
    SELECT round(4.515, 2),round(125.315, 2) from dual;                   
  • 问题原因:DOUBLE类型是8字节双精度浮点数,存在一定的精度差。在本例中,4.515的DOUBLE类型表示结果为4.514999999... ,因此四舍五入时被计算为4.51。

如果只同步100条数据,在过滤条件WHERE处应该怎样写Limit?

Limit暂时不支持在过滤语句中使用。可以先在数据库中使用SQL筛选出100条数据,再执行同步操作。

对表A执行GROUP BY生成表B,表B比原来表A的行数少了,但是在物理存储量上表B却是表A的10倍,是什么原因造成的?

数据在MaxCompute上是列式存储,并有压缩的,如果数据的同一列的前后的数据的内容是相似的,压缩比会比较高。 当odps.sql.groupby.skewindata=true打开时,用SQL写入数据,这种情况下数据比较分散,压缩比较小。如果希望数据有比较好的压缩比,可以在使用SQL写入数据时进行局部排序,计费标准请参见计费方式

如果一个表下面有很多分区,需要清空这个表所有的分区,应该如何操做?

删除分区语法如下,需要一个个删除分区。如果要删除大量分区,建议重建一个新表。
ALTER TABLE TABLE_NAME DROP [IF EXISTS] PARTITION partition_spec;               

MaxCompute客户端SQL语句执行成功,为什么会打印出异常信息?

  • 问题现象:在使用MaxCompute客户端执行MaxCompute SQL语句时,在SQL执行成功的情况下,打印出来如下错误信息。
    com.aliyun.openservices.odps.console.ODPSConsoleException                  
  • 问题原因:本地计算机开启了网络代理软件。
  • 解决方法:退出或者关闭您的网络代理软件。

当目标表的字段类型为varchar(10),插入数据溢出时会报错吗?

varchar(10)数据类型的字段插入数据时,数据长度溢出时截断并不报错。

在DataWorks里运行需要传参的SQL语句时报错,是什么原因?

在开发环境运行需要传参的SQL语句,需要单击高级运行

MaxCompute与标准SQL的主要区别是什么?如何解决?

MaxCompute与标准SQL的主要区别及解决方法,详情请参见与标准SQL的主要区别及解决方法

MaxCompute是否支持order by field nulls last语法?

MaxCompute暂时不支持此语法。MaxCompute对语法的支持请参见与其他SQL语法的差异

SQL任务运行过慢,如何优化?

SQL任务可以通过Logview进行定位,定位方法请参见使用Logview查看Job信息

优化SQL任务,详情请参见计算优化最佳实践