SQL
MaxCompute SQL 语法、报错、使用等相关问题
1.MaxCompute非分区表支持直接转换为分区表吗？
暂时不支持直接转换，只能重新建表，插入分区数据。
2.MaxCompute中给表中指定位置加字段的 sql 怎么写呢？
3.如何把开发表中的数据同步到生产表中？
用 insert 语句，指明生产环境project name。参考文档。
4.怎么查看表是否倾斜呢（没有设置 clustered by）？
数据倾斜产生的根本原因是少数 Worker 处理的数据量远远超过其他 Worker 处理的数据量，因此少数 Worker 的运行时长远远超过其他 Worker 的平均运行时长，导致整个任务运行时间超长，造成任务延迟。 数据倾斜优化可以参考文档。
5.并发写入 MaxCompute 表不同分区，会不会发生冲突？
不同分区是不会发生冲突的，同时写入同一分区会报错。
6.在 MaxCompute 中，如何用正则判断是否为中文，写法是 select '我' rlike '^[\u4e00-\u9fa5]$'，返回的结果是 false，应该怎么解决？
正确写法： select '我' rlike '[\\x{4e00}-\\x{9fa5}]+';
7.MaxComputeSQL报错：FAILED: ODPS-0121145:Data overflow -Div result is inf, two params are 19.000000 and 0.000000
此报错为数据溢出，超出数据类型的值域范围。确认一下数据类型是否正确。div 操作了 19/0，这个运算是非法的结果。
8.MaxCompute SQL 中，使用 JOIN 时，分区裁剪条件放在 ON 中分区裁剪会生效，还是分区裁剪条件放在 WHERE 中才会生效？
在 SQL 语句中使用JOIN 进行关联时：
- 如果分区剪裁条件放在 WHERE 子句中，则分区剪裁会生效。
- 如果分区剪裁条件放在 ON 子句中，从表的分区剪裁会生效，主表则不会生效。参考官方文档。
9.MaxCompute 不支持 cross join，那如何实现共现矩阵呢？
MaxCompute 目前不支持 cross join，在 MAPJOIN 中，可以通过不写 ON 语句而通过 MAPJOIN ON 1 = 1 的形式，实现笛卡尔乘积的计算。参考下这个文档。
10.SQL 报错：Failed to run ddltask - Modify DDL meta encounter exception : ODPS-0121096:MetaStore transaction conflict-Reac-hed maximum retry times because of OTSStorageTxnLockKeyF-ail(Innerexception:Transaction timeout because cannot acqui-re exclusive lock.
目前 MaxCompute 对正在操作的表没有锁机制。这个错误是由元数据产生竞争导致，检查下是不是同时多次对表或者表分区执行读写操作。建议在 MaxCompute 还没有锁机制的情况下，不要同时对一个表执行操作。
11. MaxCompute 怎么创建视图?
直接进行 DDL 操作就可以。可以参考文档。
12.MaxCompute 中,怎么关闭“查询加速模式”？
SQL 前加这条 flag 一起执行。set odps.mcqa.disable=true;
13. MaxCompute 中，CAST(substr(sdk_channel,1,1) AS BIGINT) = 1 或 CAST(t.is_client AS BIGINT) = 1 报错： Illegal type cast - in function cast, value 'n' cannot be casted from String to Bigint
substr 是对字符串截取 （），该函数里面的 两个参数 是 下标和截取个数，代码截的是 string 类型，外面的 cast 函数是对类型进行转换，如果里面截出来的，
例如：abc，根本无法转换成 bigint，如果截出来是数字 123 是可以的 转成 bigint 的。 例如：
select cast(substr("1234",1,1) as BIGINT );
14. 字段为空字符串的怎么在 SQL 里面过滤掉？
uid is not null and uid <> ''或者可以使用 uid!='';
15.请问 MaxCompute 表字段 string 类型的数据长度大于 8MB 怎么解决？
有两个方法：1、拆分字段。2、使用独享资源组，再提工单绑定可以解决大于 8MB 问题。
16.MaxCompute SQL task 能执行语法检查吗？
可以执行一下 explian，参考官方文档。
17.MaxCompute 中，按照分区查询数据的语法是什么？
select *from table where dt='1';可以参考文档。
18.MaxCompute 可以指定列进行 insert overwrite 吗？
insert overwrite不支持指定插入列的功能，可以用 insert into 或者设计一张拉链表。可以参考这篇文章。
19.MaxCompute 中执行 SQL 报错 ODPS-0130071:[0,0] Semantic analy-sis exception - physical plan generation failed: java.lang.RuntimeException:com.aliyun.odps.lot.cbo.FailFastException:instancecount exceeds limit 99999.
MaxCompute中会限制单个作业中最多不能超过 10 万个 instance，而作业中的 instance 是和用户输入的数据量和 SQL 的结构密切相关的。setodps.sql.mapper.split.size=4096; 在正式的sql 前面加上这句，设定一个 map 的最大数据输入量，单位 M,默认 256M。用户可以通过控制这个变量，从而达到对 map 端输入的控制。
20.MaxCompute 中，同一时间操作同一张表的同一分区，会产生数据翻倍的情况吗？
不会，MaxCompute 在更新元数据的时候不允许同时更新元数据。
21.MaxCompute SQL 去重 ID 怎么做？
可以使用 group 或者 distinct 去重。
22.MaxCompute SQL 报错 Invalid arguments - format string has se-cond part, but doesn't have minute part : yyyy-mm-dd hh:mm:ss
可以做一下类型转换to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3')=2018-10-30 15:13:12
23.在 MaxCompute sql 计算比例时，分母为 0 时怎么处理，有相应的函数吗？
使用 case when 或者decode 内建函数解决。可参考官方文档。
24.MaxCompute 有无一些类似数据库的内置函数，可以将 3 条记录变成 1 条，之后逗号隔开。
25.MaxCompute 是否有函数能把日期转换成周几？
26.MaxCompute 时间类型字段能不能不带时分秒？
可以使用 date 数据类型。开启MaxCompute2.0 可以使用 date 类型。 具体可参考文档。
27.MaxCompute 的表有无索引？
没有索引，不过 Hash Clustering 可以提供类似数据库里 cluster index 的效果。 具体可参考文档。
28.请问删除表的所有数据 sql 怎么写？
可以通过删除（DROP）表达到数据删除目的。
非分区表可以通过 TRUNCATE TABLE table_name;语句清空表数据。
分区表可以通过 ALTER TABLE table_name DROP IF EXISTS PARTITION（分区名=‘具体分区值’)删除分区达到删除整个分区数据的目的。
29.请问有什么办法快速查看项目空间下哪些表是分区表？
select table_name from information_schema.columns where is_partition_key = true group by table_name;
30.查询一个分区表 where 条件是 add_months('2020-06-01',-1)，报错：is full scan with all partitions, please specify partition predicat-es.怎么解决呢？
可以通过explain 命令查看 SQL 中的分区剪裁是否生效。 具体可参考文档。
31.使用 jdbc 方式访问MaxCompute 可以向 MaxComptue 中插入数据吗？
32.select a.*,b.ce from a left join b on a.cente_id = b.id WHERE
a.pt='20200518' and b.pt='20200518' 选择 a 和 b 表的分区 20200518的数据，做 left join 不行吗？
可以改成这样：
select a1.*,b1.ce from （select * from a where pt='20200518'）a1 left join
（select * from b where pt='20200518'）b1 on a1.cente_id = b1.id
33.在MaxCompute 中，100 亿条数据，使用 Group by 分组查询会不会影响性能？使用 Group by 对数据量有没有限制？
无影响，无限制。具体 Group by 分组查询使用规则可参考 MaxCompute SELECT 语法格式及使用
SELECT 语法执行嵌套查询、排序操作、分组查询等操作的注意事项。
34.MaxCompute SQL 支持 with as 语句吗？
35.MaxCompute 2.0 里的数据类型使用需要 set 设置，是否可以在
DataWorks 里面运行？
使用数据类型系统时，需要进行设置：set odps.sql.type.system.odps2=true;或 setproject odps.s
ql.type.system.odps2=true;，语句是可以在 DataWorks 新建表的 DDL 模式下执行操作或者 odps
SQL 节点运行。
36.MaxCompute Sql 中如何判断一个字段是否为空？
IS NULL。MaxCompute Sql 中包含关系运算符、算数运算符、位运算符、逻辑运算符。可参考官方文档。
37.不小心 drop 删除表可以恢复吗？
MaxCompute 提供的备份恢复功能可以帮助您恢复表数据，更多备份恢复信息，请参见备份与恢复。
38.在哪里可以看到所有执行的 SQL？
通过Information_Schema 元数据的 TASKS_HISTORY 明细来查看 14 天历史记录，元数据服务Information_Schema 已经全面开放，大家可以使用此服务查询项目内关键对象的元数据信息，在元数据之外，也提供了包括作业运行、数据上下传使用历史的行为数据。目前默认的 information_ schema 信息只保留 15 天，如果需要长期使用，需要手工转存。 具体可参考官方文档操作。
39.MaxCompute SQL 注释如何多行注释？
多行注释为 Ctrl + / 官方文档中有详细代码快捷键和 DataStudio 快捷键整理。
40.MaxCompute 中如何把表的 A 列中，包含“123456”or “678910”全部查询出来？
select * from tablename whert cloumn rlike'.*(123456|45678).*'
41.查询数据时报：Semanticanalysisexception-INTtypeisnotenabled incurrentmode，int 类型为什么不能用呢？
使用 int 类型需要打开 set odps.sql.type.system.odps2=true; 默认支持的是bigint，如果没有特别
的需求的话，只用 bigint 就可以了。
42.DataStudio 中是否可以通过 shell 节点调取 MaxCompute sql 语句？
不可以的，Shell 节点支持标准 Shell 语法，不支持交互性语法。如果任务较多，可以使用 ODPS SQL 节点来完成任务的执行。关于 DataStudio 的其他介绍请参考官方文档。
43.MaxCompute 支持修改表字段的数据类型吗？不支持，只能添加字段列，表不允许删除字段、修改字段及分区字段，如果必须修改，请删除之后重新建表，可以将表建立成外部表，在表删除重建以后，能将数据重新加载回来。 数据类型请参考官方文档。
44.除了 UDF 函数的方式外，有没有别的办法将两个没有任何关联关系的表合并成一张表呢？
可以纵向合并使用 union all，横向合并的话可以借助 row number，两张表都新加一个新的 ID 列，进行 ID 关联，然后取两张表的字段。
45.MaxCompute中可以增加或更改分区吗？可以对表数据进行删除和更改操作吗？
MaxCompute 中不可以在源表上直接增加/更改分区信息，分区一旦创建就不能更改。建议再建一张新分区表，使用动态分区 SQL 把源表数据导入到新分区表。同时 MaxCompute 不支持直接对普通表数据进行更新（UPDATE）操作也不支持直接对普通表数据进行删除（DELETE）操作，可参考文档。
46.MaxCompute SQL 中，使用 not in 后面接子查询，子查询返回的结果是上万级别的数据量，但语句中的子查询返回的col1 的个数超过 1000个时，系统会报错为 records returned from subquery exceed-ed limit of 1000。应该怎么去实现子查询限制 1000 呢？
可以使用 left outer join 实现来替代子查询实现。 子查询：SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx); join 查询：SELECT a.* FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1)
47.select '尺码' rlike '[\u4e00-\u9fa5]+';匹配汉字正则表达式怎么写?
可以这样写：select '汉字' rlike '[\x{4e00}-\x{9fa5}]+'
48.中，select * from sale_detail order by region; 报错：
Semantic analysis exception-ORDER BY must be used with a LIMET clause
ORDER BY 必须与 limit 共同使用。ORDER BY 没有与 limit 共同使用时，报错返回，可参考文档。
49. 在 MaxCompute 中，一张表的分区的数量是否越多越好？
在 MaxCompute 中，一张表最多允许有 60000 个分区，同时每个分区的容量没有上限。但是分区数量过多，会导致统计和分析过程非常不方便。 MaxCompute 也会限制单个作业中最多不能超过一定数量的 instance，而作业中的 instance 和您输入的数据量和分区数量密切相关的。所以您需要根据业务需要，选择合适的分区策略。
50.如果一开始并没有分区字段，是否可以增加或更改分区？
您不可以在源表上直接增加或更改分区键，分区键一旦创建就不能更改。但您可以重新创建一张分区表，使用动态分区 SQL 把源表数据导入到新分区表，可参考文档。
51.在 MaxCompute 中，需要将一行转化为多行的时候，我们该如何解决？
Lateral View 和 split，explode 等UDTF 一起使用，它能够将一行数据拆成多行数据，并在此基础上对拆分后的数据进行聚合，可参考文档。
52.在 MaxCompute 中，INSERT 语句执行过程中出现错误，会损坏原有数据吗？
不会损坏原有数据。MaxCompute满足原子性(Atomicity），INSERT 要么成功更新，要么失败回滚。
53.包年包月空间，运行 SQL 语句查询表数据，表中数据为 1 万条，查询一直处于Job Quening...状态，无法执行，原因是什么？
请排查任务运行状态，可能有个任务运行占用了资源，请先中止或者等待此任务。可以在 MaxCompute 客户端中运行 Show Instances/Show P 命令来查看实例信息。
54.使用 MaxCompute SQLTask 执行 cost sql sql 命令时报错ODPS-0130161:[1,1] Parse exception - invalid token 'cost'怎么解决呢？
需要使用 Java SDK 中的 SQLCostTask 接口来实现单条 SQL 费用的查询。接口使用方式可以参考文档。
55.MaxCompute 中如何删除生产环境的表？
可以在 MaxCompute 客户端(odpscmd)或 DataStudio 中使用 drop table project_name.table_name; 删除生产环境的表。
56.MaxCompute 中，是否可以添加或删除列？
可以添加列，但不可以删除列。如果有删除列的需求，可以新建表，再重命名表。
57.在 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。
58.中使用什么方法可以实现相同字段连接？
MaxCompute 可以使用WM_CONCAT 函数来实现相同字段连接，具体函数说明可参考文档。
59.MaxCompute 中，如何修改表的 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;
分区表修改写法：ALTER TABLE table_name [partitions (ds='xxx')] NOT CLUSTERED;
60.MaxCompute 中，如何查看指定的表或者分区是否存在？
可使用使用函数TABLE_EXISTS，查询指定的表是否存在。 使用函数 PARTITION_EXISTS，查询指
定的分区是否存在。 具体函数说明可参考文档。
61.SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema = ?使用sql获取 指定空间下的表信息 提示failed:
ODPS-0130013:Authorization exception - Authorization Failed [4002], You don't exist in project information_schema. 请问该如何授权呢？
需要授权，可以参考一下MaxCompute 的元数据服务，点击链接参考官方文档。
62.执行 INSERT INTO 或 INSERT OVERWRITE 操作时，提示字段不匹配，如何解决？
执行INSERT INTO 或 INSERT OVERWRITE 操作插入数据时，需要保证 SELECT 得到的字段和目标表的字段匹配，匹配内容包括顺序、字段类型和总的字段数量。MaxCompute不支持插入表的指定字段，其他字段为 NULL 或者其他默认值时，您可以在 SELECT 时设置为 NULL，例如select 'a' ,null from XX;。
>>快来点击免费下载《阿里云MaxCompute百问百答》了解更多详情！<<