MaxCompute SQL语句

By | 2021年4月23日
0 Comment

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

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

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

除UUID函数外，如何设置MaxCompute表的主键，实现唯一性索引？

MaxCompute应用于海量数据的批量计算场景，不支持设置表的主键或者唯一性索引。

如果源表没有分区字段，是否可以增加或更改分区？

MaxCompute不支持在源表上直接增加或修改分区字段，分区字段一旦创建就无法修改。您可以重新创建一张分区表，使用动态分区SQL将源表数据导入至新分区表，详情请参见插入或覆写动态分区数据（DYNAMIC PARTITION）。

如何更新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缓存多张小表？

您可以在MAPJOIN中填写表的别名。

示例为鸢尾花的数据，表名为iris，表结构如下。

+——————————————————————————————————————————+

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

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

+——————————————————————————————————————————+

执行命令示例如下。

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中，可以添加列，但不可以删除列。

如何添加列？

添加列的命令示例如下。

ALTER TABLE table_name ADD COLUMNS (col_name1 type1, col_name2 type2…);

如果表中已经存在一部分数据，则新添加列的值为NULL。

如何删除列？

MaxCompute不支持删除表的列。如果您有删除列的需求，可以通过如下方法实现：

  1. 创建一张新表，命令示例如下。 
    CREATE TABLE new_table_name AS SELECT c1,c2,c3 FROM table_name;
  2. 删除旧的表，并重命名新表，命令示例如下。 
    ALTER TABLE new_table_name RENAME TO table_name;

如何查看MaxCompute的数据量？

查看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操作执行成功则更新数据，INSERT操作执行失败则回滚数据。

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

执行INSERT INTO或INSERT 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中的表可以分区，分区表有分区列。您可以通过分区列创建分区。

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

如何向MaxCompute表中插入数据？

向MaxCompute表中数据的示例如下：

  1. 执行如下命令创建一个有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

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
  • 问题原因：日期格式有误。mm和MM都表示月份，分钟需要使用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类型的字段，系统会对此字段进行隐式类型转换。MaxCompute仅支持yyyy-mm-dd hh:mi:ss类型的字符串隐式转换。如果字符串中的数据不是此格式，就会报错。
  • 解决方法：建议您使用TO_DATE函数把STRING类型的数据转换为DATETIME类型的数据。如果字符串的类型和TO_DATE要求的不一样，请使用UDF进行解析。

在执行MaxCompute 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成一张临时表作为输入表，减少输入表的个数。

在执行MaxCompute SQL过程中，报错输出表的分区过多，如何处理？

  • 问题现象：在执行MaxCompute SQL过程中，报错如下。 
    FAILED: ODPS-0123031:Partition exception - a single instance cannot output data to more than 10000 partitions
  • 问题原因：虽然单个MaxCompute表允许有6万个分区，但是单个作业涉及的输出表分区数量只允许有10000个。出现这个错误，通常是因为分区字段设置有误，例如根据ID字段分区造成分区过多。
  • 解决方法：一般作业输出动态分区数达到几千已经很大，超过10000可能存在业务逻辑或SQL语法问题。如无逻辑或语法问题，建议修改分区表的分区字段，或将业务逻辑拆分为多个作业，避免出现该错误。

在执行MaxCompute SQL过程中，报错Repeated key in GROUP BY，如何处理？

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

在执行MaxCompute SQL过程中，报错ODPS-0010000，如何处理？

  • 问题现象：在执行MaxCompute 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的字段作为分区字段，导致分区数量过多。
  • 解决方法：
    • 如果未写分区条件，补上分区条件即可。
    • 如果分区列不合适，导致分区数量太多，请考虑更改分区列。

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

在执行MaxCompute SQL过程中，报错ODPS-0130089，如何处理？

  • 问题现象：在执行MaxCompute SQL调用UDF时，报错如下。 
    FAILED:ODPS-0130089 Invalid UDF reference - class not ‘xxx’ found for function
  • 问题原因：未找到您定义的类。
  • 解决方法：
    • 请在代码中核对路径名和类名是否正确，例如大小写是否正确。
    • 使用解压工具解压JAR包，查看类文件是否存在。函数使用的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类型的数据报错，如何处理？

MaxCompute 2.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 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语句查询有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语句长度没有直接换算关系，暂时无法预估。
    • 由于分区量过大导致执行计划超过限制。
    • 由于小文件比较多导致SQL运行失败。
  • 解决方法：
    • 对于过长的SQL语句，建议拆分成多次运行，避免触发长度限制。
    • 如果分区过大，需要调整分区个数，详情请参见分区。
    • 如果是由于小文件较多导致，请参见优化诊断。

MySQL支持的SUBSTRING_INDEX函数在MaxCompute中支持吗？

支持，详情请参见SUBSTRING_INDEX。

插入动态分区报错，如何处理？

  • 问题现象：执行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客户端支持并行下载吗？

支持并行下载。

在并行下载时，请注意本地服务器配置、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 table_name WHERE xxx;

  • 如果只运行一个SQL语句，您可以使用MaxCompute SQL中的sqltext方法，命令示例如下。 
    ./odpscmd -e "SELECT FROM DUAL;"

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

    此功能可以配合crontab命令定时执行SQL，建议您使用DataWorks的周期任务功能，详情请参见查看周期任务。

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

因为数据量太大并且有倾斜，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表。

在执行MaxCompute SQL时使用动态分区，将GMT格式化作为分区字段，产生大量分区和记录数，一直没有运行完成，是什么原因？

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

MaxCompute能否像MySQL一样灵活使用用户变量（即MySQL的@变量名）？

不支持参数化的SQL。

REGEXP_COUNT函数的参数pattern是否支持嵌入查询语句？

不支持，您可以改写为支持的语法，例如JOIN。

在执行MaxCompute 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'

MaxCompute没有提供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导出数据。

SQLTask查询数据和DownloadSession在使用及功能上，有什么不同？

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

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

如何下载超过一万行的数据？

如果您使用MaxCompute客户端，可以先把SQL结果插入到一张表中，然后使用Tunnel下载表中的数据。详情请参见导出SQL运行结果的方法总结和使用说明。

如何查看SQL执行费用？

您可以使用COST SQL命令查询费用，详情请参见计费方式。

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

支持，例如SELECT * FROM user_info WHERE address RLIKE '[0-9]{9}';，表示查找9位数字组成的ID。

在执行MaxCompute SQL过程中，报错ODPS-0121145 ，什么原因？

报错信息如下。

--报错1。
ODPS-0121145:Data overflow - param convert to Double result is nan, input param is NaN
--报错2。
ODPS-0121145:Data overflow - Div result is inf, two params are 19.000000 and 0.000000

报错1产生原因：原始数据中有空值。

报错2产生原因：数据溢出，超出数据类型的值域范围。需要确认数据类型是否正确。

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

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

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

使用ROUND函数对DOUBLE类型数据四舍五入，为何结果存在偏差？

  • 问题现象：使用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查看作业运行信息。

优化SQL作业，详情请参见计算优化最佳实践。

MaxCompute的时间类型字段是否可以不带时分秒？

时间类型字段使用DATE数据类型。使用该数据类型时，您需要打开MaxCompute 2.0数据类型开关。详情请参见2.0数据类型版本。

MaxCompute的表有无索引？

没有索引，Hash Clustering可以提供类似数据库中Cluster index的效果，详情请参见表操作。

如何快速查看项目空间下哪些表是分区表？

执行如下命令查看项目空间下的分区表信息。

SELECT table_name FROM information_schema.columns WHERE is_partition_key = true GROUP BY table_name;

如何将开发环境的表数据同步至生产环境的表中？

执行如下命令。

INSERT INTO project.table SELECT * FROM project_dev.table;

如果没有生产环境中表的读写权限，需要完成账号授权，详情请参见
授权。

查询分区表的WHERE条件是add_months(‘yyyy-mm-dd’,x)，报错is full scan with all partitions，please specify partition predicates，如何处理？

您可以通过EXPLAIN命令查看SQL中的分区剪裁是否生效。详情请参见分区剪裁合理性评估。

通过JDBC方式访问MaxCompute可以向MaxCompute中插入数据吗？

您可以通过INSERT操作插入数据，详情请参见使用说明。

如何对表的分区数据做LEFT JOIN操作？

详情请参见JOIN。

使用GROUP BY分组查询100亿条数据会不会影响性能？GROUP BY对数据量有没有限制？

无影响。无限制。GROUP BY分组查询详情请参见SELECT语法。

MaxCompute SQL支持WITH AS语句吗？

支持，MaxCompute支持SQL标准的CTE，提高SQL语句的可读性与执行效率。详情请参见COMMON TABLE EXPRESSION（CTE）。

是否可以在DataWorks执行set命令打开2.0数据类型开关？

可以。使用2.0数据类型时，您可以在DataWorks的临时查询模式或ODPS SQL节点下执行set odps.sql.type.system.odps2=true;（Session级别）或setproject odps.sql.type.system.odps2=true;（Project级别）命令。

如何解决DECIMAL数据类型精度溢出问题？

执行set odps.sql.decimal.odps2=true;命令，打开2.0数据类型开关。

如何判断一个字段是否为空？

您可以通过MaxCompute SQL的运算符判断字段是否为空，详情请参见运算符。

因误操作删除的表可以恢复吗？

不可以。在MaxCompute客户端（odpscmd）和IntelliJ IDEA中删除表为不可逆操作。请谨慎操作。

如何查看已执行的所有SQL作业？

您可以通过Information Schema服务的TASKS_HISTORY视图查看已执行的所有SQL作业。详情请参见Information Schema概述。

MaxCompute SQL如何实现多行注释？

您可以使用快捷键Ctrl+/实现多行注释，详情请参见编辑器快捷键列表。

查询数据时，报错Semanticanalysisexception-XXXtypeisnotenabled incurrentmode，如何处理？

执行set odps.sql.decimal.odps2=true;命令，打开2.0数据类型开关。

是否可以通过DataWorks的Shell节点调取MaxCompute SQL？

不可以。Shell节点仅支持标准Shell语法，不支持交互性语法。如果作业较多，您可以使用ODPS SQL节点执行作业，详情请参见创建ODPS SQL节点。

MaxCompute支持修改表字段的数据类型吗？

不支持。只能添加字段列，生产环境中的表不允许删除表字段、修改表字段和分区字段。如果必须修改，请删除表之后重新建表。您也可以创建外部表，删除并重建表后，可以重新加载数据。数据类型详情请参见数据类型版本说明。

除使用UDF外，如何合并两个没有任何关联关系的表？

您可以通过UNION ALL运算完成纵向合并。横向合并可以通过ROW_NUMBER函数实现，两个表都新加一个ID列，进行ID关联，然后取两个张表的字段。

执行SELECT * FROM XXX ORDER BY XXX;命令报错，如何处理？

ORDER BY必须与LIMIT共同使用。详情请参见SELECT语法。

如何将一行数据拆分为多行数据？

结合使用Lateral View和表生成函数（例如Split和Explode），将一行数据拆成多行数据，并对拆分后的数据进行聚合。详情请参见Lateral View（行转列）。

执行MaxCompute SQL过程中，报错Parse exception – invalid token ‘cost’，如何处理？

使用Java SDK中的SQLCostTask接口查询单条SQL费用。接口使用方式请参见SQLCostTask。

如何删除生产环境的表？

在MaxCompute客户端（odpscmd）或DataWorks的数据开发（DataStudio）中执行如下命令删除生产环境的表。

DROP TABLE project_name.table_name;

如何连接相同字段？

可以使用WM_CONCAT函数连接相同字段，详情请参见WM_CONCAT。

如何修改表的Hash Clustering属性？

增加表的Hash Clustering属性：ALTER TABLE table_name [CLUSTERED BY (col_name [, col_name, ...]) [SORTED BY (col_name [ASC | DESC] [, col_name [ASC | DESC] ...])] INTO number_of_buckets BUCKETS];。

去除表的Hash Clustering属性：ALTER TABLE table_name NOT CLUSTERED;。

如何查看指定的表是否存在？

可以使用函数TABLE_EXISTS查看指定的表是否存在，详情请参见TABLE_EXISTS。

如何查看指定的分区是否存在？

可以使用函数PARTITION_EXISTS查看指定的分区是否存在，详情请参见PARTITION_EXISTS。

获取项目空间下的所有表名称报错，如何处理？

使用MaxCompute的元数据服务，详情请参见Information Schema概述。

如何升级表的数据类型？

在SQL语句前增加set odps.sql.type.system.odps2=true;命令，与SQL语句一并提交。

新创建的项目空间不支持数据类型自动隐式转换，如何处理？

确认是否开启MaxCompute 2.0，关闭MaxCompute 2.0之后可进行隐式转换。详情请参见数据类型转换。

是否支持将非分区表修改为分区表？

非分区表不支持更改为分区表，也不支持增加分区列。您可以重新创建一张分区表，详情请参见表操作。

如何创建视图？

根据创建视图语法编写SQL语句即可，更多创建视图语法信息，请参见视图操作。

如何关闭查询加速模式？

在SQL语句前添加set odps.mcqa.disable=true;命令，与SQL语句一起提交执行，即可关闭查询加速模式。

在执行MaxCompute SQL过程中，报错ODPS-0121096，什么原因？

  • 问题现象：执行MaxCompute SQL的过程中，出现类似如下报错。 
    Failed to run ddltask - Modify DDL meta encounter exception : ODPS-0121096:MetaStore transaction conflict - Reached maximum retry times because of OTSStorageTxnLockKeyFail(Inner exception: Transaction timeout because cannot acquire exclusive lock.)
  • 问题原因：MaxCompute对正在操作的表没有锁机制，该错误是由元数据产生竞争导致。您需要检查是否存在同时多次对表或表分区执行读写操作的情况。
  • 解决方法：在MaxCompute还没有锁机制的情况下，不要同时对一张表或表分区执行多次读写操作。

在客户端的odps_config.ini文件中设置use_instance_tunnel=false，instance_tunnel_max_record=10，为什么Select还是能输出很多记录？

需要修改use_instance_tunnel=false为use_instance_tunnel=true，才能通过instance_tunnel_max_record控制输出记录数。

在MaxCompute SQL中使用JOIN时，分区裁剪条件放在ON中分区裁剪会生效，还是放在WHERE中才会生效？

如果分区剪裁条件置于WHERE语句中，分区剪裁会生效。如果分区剪裁条件置于ON语句中，从表的分区剪裁会生效，主表的分区剪裁不会生效即会全表扫描。更多分区裁剪信息，请参见分区剪裁合理性评估。

如何查询某个用户创建的表？

只查询某个用户创建的表可以使用元数据视图TABLES，通过owner_name字段过滤。更多TABLES信息，请参见TABLES。

如何用正则表达式判断是否为中文？

命令示例如下：

select '汉字' rlike '[\\x{4e00}-\\x{9fa5}]+';

如何判断给定的表是否存在？

您可以使用TABLE_EXISTS函数，更多信息，请参见TABLE_EXISTS。

如何收集一个月内访问MaxCompute的用户信息？

您可以使用审计日志功能来查看。MaxCompute会完整地记录用户的各项操作行为，并通过阿里云ActionTrail服务将用户行为日志实时推送至ActionTrail。更多信息，请参见审计日志。

请关注公众号获取更多资料

发表评论

电子邮件地址不会被公开。 必填项已用*标注