互換性のないSQL文の書き換え - MaxCompute - Alibaba Cloud ドキュメントセンター

このトピックでは、MaxCompute V2.0と互換性のないSQL文を変更する方法について説明します。

背景情報

MaxCompute V2.0は、オープンソースのエコシステムを完全に採用し、より多くのプログラミング言語と機能をサポートし、より高いパフォーマンスを提供します。また、構文をより厳密に検査します。その結果、より厳密でない構文を使用し、以前のバージョンで正常に実行された一部のステートメントではエラーが返される場合があります。

MaxCompute V2.0へのスムーズなカナリアアップグレードを有効にするために、MaxComputeフレームワークはロールバックをサポートしています。 MaxCompute V2.0がジョブの実行に失敗した場合、MaxCompute V1.0は代わりにジョブを実行します。ロールバックにより、ジョブのレイテンシが増加します。ジョブを送信する前に、set odps.sql.planner.mo de=lot; を設定して、ロールバック機能を手動で無効にすることを推奨します。これにより、MaxComputeロールバックポリシーの変更による影響を防ぎます。

MaxComputeチームは、オンラインロールバック条件に基づいて、必要なSQL文を電子メールまたはDingTalkで実行できないジョブの所有者に通知します。ジョブの所有者は、できるだけ早い機会にジョブのSQLステートメントを変更する必要があります。そうしないと、ジョブが失敗する可能性があります。

グループ. by.with.star

このステートメントは、select * …group by… ステートメントに相当します。

MaxCompute V2.0では、ソーステーブルのすべての列をGROUP BY句に含める必要があります。それ以外の場合は、エラーが返されます。
以前のバージョンのMaxComputeでは、ソーステーブルのすべての列がgroup by句に含まれていなくても、select * from GROUP BY keyがサポートされています。

例

シナリオ1: GROUP BYキーにすべての列が含まれるわけではありません。

無効な構文:
```
select * from t group by key;
```

エラーメッセージ：

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - column reference t.value should appear in GROUP BY key

有効な構文:
```
select distinct key from t;
```

シナリオ2: GROUP BYキーにはすべての列が含まれます。
- 次の構文は使用しないことを推奨します。
```
select * from t group by key, value; -- t has columns key and value
```
- 上記の構文でMaxCompute V2.0でエラーが発生しない場合でも、次の構文を使用することを推奨します。
```
select distinct key, value from t;
```

bad.escape

エスケープシーケンスが無効です。

MaxComputeは、文字列リテラルで、0から127の範囲の各ASCII文字をバックスラッシュ (\) の後に3つの8進数の形式で記述する必要があると定義しています。たとえば、0は \001と記述され、1は \002と記述されます。ただし、\01と \0001は \001として処理されます。

この方法は、新しいユーザーを混乱させます。たとえば、"\0001" は "\000" + "1" として処理できません。他のシステムからMaxComputeにデータを移行するユーザーの場合、無効なデータが生成される可能性があります。

説明番号が \000に追加されている場合 (\0001〜 \0009の範囲の番号や \00001の番号など) 、エラーが返される場合があります。

MaxCompute V2.0は、この問題を処理するためにスクリプトのシーケンスを修正します。

無効な構文:

select split(key, "\01"), value like "\0001" from t;

エラーメッセージ：

FAILED: ODPS-0130161:[1,19] Parse exception - unexpected escape sequence: 01
ODPS-0130161:[1,38] Parse exception - unexpected escape sequence: 0001

有効な構文:

select split(key, "\001"), value like "\001" from t;

column.repeated.in.creation

CREATE TABLEステートメントの実行時に重複する列名が検出された場合、MaxCompute V2.0はエラーを返します。

例

無効な構文:

create table t (a BIGINT, b BIGINT, a BIGINT);

エラーメッセージ：

FAILED: ODPS-0130071:[1,37] Semantic analysis exception - column repeated in creation: a

有効な構文:
```
create table t (a BIGINT, b BIGINT);
```

string.join.double

STRING型の値をDOUBLE型の値と結合する必要があります。

MaxComputeの初期バージョンでは、STRING型とDOUBLE型の値はBIGINT型に変換されます。これは精度損失を引き起こす。例えば、JOIN条件における1.1=「1」は等しいと考えられる。
MaxCompute V2.0では、MaxCompute V2.0はHiveと互換性があるため、STRING型とDOUBLE型の値はDOUBLE型に変換されます。

例

推奨されない構文:

select * from t1 join t2 on t1.double_value = t2.string_value;

警告情報:

WARNING:[1,48]  implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress

推奨構文:

select * from t1 join t2 on t.double_value = cast(t2.string_value as double);

window.ref.prev.window.alias

ウィンドウ関数は、同じレベルのSELECT句内の他のウィンドウ関数のエイリアスを参照します。

例

t1にrnが存在しないと仮定する。無効な構文:

select row_number() over (partition by c1 order by c1) rn,
row_number() over (partition by c1 order by rn) rn2
from t1;

エラーメッセージ：

FAILED: ODPS-0130071:[2,45] Semantic analysis exception - column rn cannot be resolved

有効な構文:

select row_number() over (partition by c1 order by rn) rn2
from
(select c1, row_number() over (partition by c1 order by c1) rn
from t1
) tmp;

select.invalid.token.after.star

SELECT句を使用すると、アスタリスク (*) を使用してテーブルのすべての列を選択できます。ただし、アスタリスクが1つの列しか指定していない場合でも、アスタリスクの後にエイリアスを付けることはできません。新しいエディターは、同様の構文のエラーを返します。

例

無効な構文:
```
select * as alias from table_test;
```

エラーメッセージ：

FAILED: ODPS-0130161:[1,10] Parse exception - invalid token 'as'

有効な構文:
```
select * from table_test;
```

agg.having.ref.prev.agg.alias

HAVINGが存在する場合、SELECT句は集計関数エイリアスを参照できます。

例

無効な構文:

select count(c1) cnt,
sum(c1) / cnt avg
from t1
group by c2
having cnt > 1;

エラーメッセージ：
```
FAILED: ODPS-0130071:[2,11] Semantic analysis exception - column cnt cannot be resolved
ODPS-0130071:[2,11] Semantic analysis exception - column reference cnt should appear in GROUP BY key
```
sとcntはソーステーブルt1に存在しません。ただし、HAVINGが存在するため、MaxComputeの初期バージョンではエラーは返されません。 MaxCompute V2.0では、エラーメッセージ列cannot be resolveが返されます。

有効な構文:

select cnt, s, s/cnt avg
from
(
select count(c1) cnt,
sum(c1) s
from t1
group by c2
having count(c1) > 1
) tmp;

order.by.no.limit

MaxComputeでは、データレコードの数を制限するために、ORDER BY句の後にLIMIT句を追加する必要があります。 ORDER BYは、すべてのデータレコードをソートするために使用されます。 ORDER BYの後にLIMIT句がない場合、実行パフォーマンスは低くなります。

例

無効な構文:

select * from (select *
from (select cast(login_user_cnt as int) as uv, '3' as shuzi
from test_login_cnt where type = 'device' and type_name = 'mobile') v
order by v.uv desc) v
order by v.shuzi limit 20;

エラーメッセージ：

FAILED: ODPS-0130071:[4,1] Semantic analysis exception - ORDER BY must be used with a LIMIT clause

サブクエリのorder by v.uv descにLIMIT句を追加します。

MaxCompute V1.0では、ビューチェックは厳密ではありません。たとえば、ビューは、LIMIT句のチェックを必要としないプロジェクトで作成されます。 odps.sql.validate.orderby.limit=falseは、プロジェクトがLIMIT句のチェックを必要としないことを示します。

create view table_view as select id from table_view order by id;

次のステートメントを実行して、ビューにアクセスします。

select * from table_view;

MaxCompute V1.0はエラーを返しませんが、MaxCompute V2.0は次のエラーを返します。

FAILED: ODPS-0130071:[1,15] Semantic analysis exception - while resolving view xdj.xdj_view_limit - ORDER BY must be used with a LIMIT clause

generated.column.name.multi.window

自動的に生成されたエイリアスが使用されます。

MaxComputeの初期バージョンでは、SELECTステートメントの各式に対してエイリアスが自動的に生成されます。エイリアスはMaxComputeクライアントに表示されます。ただし、MaxComputeの初期バージョンでは、エイリアス生成ルールが正しいか、変更されないことを保証するものではありません。自動生成されたエイリアスは使用しないことを推奨します。

MaxCompute V2.0は、自動的に生成されたエイリアスの使用を警告します。ただし、MaxCompute V2.0では、悪影響を回避するために自動的に生成されるエイリアスの使用は禁止されていません。

場合によっては、MaxComputeのさまざまなバージョンでエイリアス生成ルールに既知の変更が加えられます。一部のオンラインジョブは、自動的に生成されたエイリアスに依存します。これらのジョブは、MaxComputeのアップグレードまたはロールバック中に失敗する可能性があります。これらの問題が発生した場合は、クエリを変更し、列のエイリアスを明示的に指定します。

例

推奨されない構文:

select _c0 from (select count(*) from table_name) t;

推奨構文:

select c from (select count(*) c from table_name) t;

non.boolean.filter

非BOOLEANフィルタ条件が使用される。

MaxComputeでは、BOOLEAN型と他のデータ型間の暗黙的な変換を禁止します。ただし、MaxComputeの初期バージョンでは、BIGINTフィルター条件を使用できる場合があります。 MaxCompute V2.0は、BIGINTフィルター条件の使用を禁止します。スクリプトのフィルタ条件がBIGINTの場合は、できるだけ早く変更してください。例：

無効な構文:

select id, count(*) from table_name group by id having id;

エラーメッセージ：

FAILED: ODPS-0130071:[1,50] Semantic analysis exception - expect a BOOLEAN expression

有効な構文:

select id, count(*) from table_name group by id having id <> 0;

post.select.ambiguous

ORDER BY、CLUSTER BY、DISTRIBUTE BY、およびSORT BY句は、競合する名前を持つ列を参照します。

MaxComputeの初期バージョンでは、SELECT句の最後の列が操作オブジェクトとして自動的に選択されます。ただし、この場合、MaxCompute V2.0はエラーを報告します。できるだけ早い機会にクエリを変更します。例：

無効な構文:

select a, b as a from t order by a limit 10;

エラーメッセージ：

FAILED: ODPS-0130071:[1,34] Semantic analysis exception - a is ambiguous, can be both t.a or null.a

有効な構文:

select a as c, b as a from t order by a limit 10;

この変更は、競合する列名を持つが同じ構文を持つステートメントをカバーします。あいまいさは発生しませんが、システムはこれらのステートメントに対して警告するエラーを返します。関連するステートメントを変更することを推奨します。

duplicated.partition.column

同じ名前のパーティションがクエリで指定されます。

MaxComputeの初期バージョンでは、同じ名前の2つのパーティションキーが指定されている場合、エラーは返されません。後者のパーティションキーは、前者のパーティションを上書きする。これは混乱を引き起こします。この場合、MaxCompute V2.0はエラーを返します。例：

無効な構文1:

insert overwrite table partition (ds = '1', ds = '2')select ... ;

ds = '1' は実行中は無視される。

有効な構文:

insert overwrite table partition (ds = '2')select ... ;

無効な構文2:

create table t (a bigint, ds string) partitioned by (ds string);

有効な構文:

create table t (a bigint) partitioned by (ds string);

order.by.col. あいまい

ORDER BY句は、SELECT句内の重複するエイリアスを参照します。

無効な構文:

select id, id
from table_test 
order by id;

有効な構文:

select id, id id2
from table_name 
order by id;

ORDER BY句が参照できるようにする前に、重複するエイリアスを削除します。

in.subquery.without.result

サブクエリのcolxが結果を返さない場合、colxはソーステーブルに存在しません。

無効な構文:

select * from table_name
where not_exist_col in (select id from table_name limit 0);

エラーメッセージ：

FAILED: ODPS-0130071:[2,7] Semantic analysis exception - column not_exist_col cannot be resolved

ctas.if.not.exists

ターゲットテーブルの構文が無効です。

ターゲットテーブルが存在する場合、MaxComputeの初期バージョンは構文をチェックしません。ただし、MaxCompute V2.0では構文がチェックされます。その結果、多数のエラーが返される可能性があります。例：

無効な構文:

create table if not exists table_name
as
select * from not_exist_table;

エラーメッセージ：

FAILED: ODPS-0130131:[1,50] Table not found - table meta_dev.not_exist_table cannot be resolved

worker.restart.instance.timeout

MaxComputeの初期バージョンでは、UDFがレコードを生成するたびに、Apsara分散ファイルシステムで書き込み操作がトリガーされ、ハートビートパケットがジョブスケジューラに送信されます。 UDFが10分間レコードを生成しない場合、次のエラーが返されます。

FAILED: ODPS-0123144: Fuxi job failed - WorkerRestart errCode:252,errMsg:kInstanceMonitorTimeout, usually caused by bad udf performance.

MaxCompute V2.0のランタイムフレームワークは、一度に列の複数の行を処理するベクトル化をサポートしています。これは実行をより効率的にする。複数のレコードが一度に処理され、特定の期間内にハートビートパケットがジョブスケジューラに送信されない場合、ベクトル化により通常のステートメントがタイムアウトする可能性があります。 2つの出力レコードの間隔は10分を超えることはできません。

タイムアウトエラーが発生した場合は、まずUDFのパフォーマンスを確認することをお勧めします。各レコードを処理するには数秒かかる。 UDFを最適化できない場合は、この問題を処理するためにbatch.rowcountを手動で設定できます。 batch.rowcountのデフォルト値は1024です。

set odps.sql.executionengine.batch.rowcount=16;

divide.nan.or.overflow

MaxComputeの初期バージョンでは、分割定数の折りたたみはサポートされていません。

次のコードは、MaxComputeの初期バージョンの物理実行計画を示しています。

explain
select if(false, 0/0, 1.0)
from table_name;
in task M1_Stg1:
    Data source: meta_dev.table_name
    TS: alias: table_name
      SEL: If(False, Divide(UDFToDouble(0), UDFToDouble(0)), 1.0)
        FS: output: None

IFおよびDIVIDE機能は保持される。実行中、IFの最初のパラメーターはFalseに設定され、DIVIDEの式は評価されません。ゼロ除算エラーは発生しません。

ただし、MaxCompute V2.0は分割定数折りたたみをサポートしています。その結果、エラーが返されます。例：

無効な構文:

select IF(FALSE, 0/0, 1.0)
from table_name;

エラーメッセージ：

FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result NaN, two params are 0.000000 and 0.000000

オーバーフローエラーも発生する可能性があります。例：

無効な構文:

select if(false, 1/0, 1.0)
from table_name;

エラーメッセージ：

FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result overflow, two params are 1.000000 and 0.000000

有効な構文:

/0を削除し、有効な定数を使用することを推奨します。

同様の問題は、CASE WHENのコンスタントフォールディング (例えば、CASE WHEN TRUE THEN 0 ELSE 0/0) において生じる。 MaxCompute V2.0での定数フォールディング中に、すべての部分式が評価され、ゼロ除算エラーが発生します。

CASE WHENは、より複雑な最適化シナリオを含み得る。例：

select case when key = 0 then 0 else 1/key end
from (
select 0 as key from src
union all
select key from src) r;

オプティマイザは除算操作をサブクエリにプッシュダウンします。次のコードは、同様の変換を示しています。

M (
select case when 0 = 0 then 0 else 1/0 end c1 from src
UNION ALL
select case when key = 0 then 0 else 1/key end c1 from src) r;

エラーメッセージ：

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.ArithmeticException: DIVIDE func result overflow, two params are 1.000000 and 0.000000

UNION ALLの最初の句の定数折りたたみに対してエラーが返されます。 SQL文のCASE WHENをサブクエリに移動し、不要なCASE WHEN文と /0を削除することを推奨します。

select c1 end
from (
select 0 c1 end from src
union all
select case when key = 0 then 0 else 1/key end) r;

small.table. excess. mem.limit

MaxComputeの初期バージョンは、多方向結合最適化をサポートしています。同じ結合キーを持つ複数のJOIN操作は、この例ではJ4_1_2_3_Stg1など、同じFuxiタスクで実行するためにマージされます。

explain
select t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;

次のコードは、MaxComputeの初期バージョンの物理実行計画を示しています。

In Job job0:
root Tasks: M1_Stg1, M2_Stg1, M3_Stg1
J4_1_2_3_Stg1 depends on: M1_Stg1, M2_Stg1, M3_Stg1

In Task M1_Stg1:
    Data source: meta_dev.t1

In Task M2_Stg1:
    Data source: meta_dev.t2

In Task M3_Stg1:
    Data source: meta_dev.t3

In Task J4_1_2_3_Stg1:
    JOIN: t1 INNER JOIN unknown INNER JOIN unknown
        SEL: t1._col0, t1._col1, t1._col2
            FS: output: None

MAPJOINヒントが追加された場合、MaxComputeの初期バージョンの物理実行プランは変更されません。 MaxComputeの初期バージョンでは、多方向結合最適化が優先的に使用され、ユーザー定義のMAPJOINヒントは無視できます。

explain
select /* +mapjoin(t1) */ t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;

MaxComputeの初期バージョンの前述の物理実行プランが適用されます。

MaxCompute V2.0のオプティマイザは、ユーザー定義のMAPJOINヒントを優先的に使用します。この例では、t1が大きなテーブルの場合、次のようなエラーが返されます。

FAILED: ODPS-0010000:System internal error - SQL Runtime Internal Error: Hash Join Cursor HashJoin_REL… small table exceeds, memory limit(MB) 640, fixed memory used …, variable memory used …

この場合、MAPJOINが不要な場合は、MAPJOINヒントを削除することを推奨します。

sigkill.oom

sigkill.oomにはsmall.table.exceeds.mem.limitと同じ問題があります。 MAPJOINヒントを指定し、小さなテーブルのサイズが大きい場合、MaxComputeの初期バージョンで複数の結合を使用して複数のJOIN文を最適化することができます。その結果、ステートメントはMaxComputeの初期バージョンで正常に実行されます。ただし、MaxCompute V2.0では、小さなテーブルがサイズ制限を超えないように、odps.sql.mapjoin.memory.maxを使用するユーザーもいます。各MaxComputeワーカーにはメモリ制限があります。小さなテーブルのサイズが大きい場合、メモリ制限を超えたため、MaxComputeワーカーが終了する可能性があります。この場合、次のようなエラーが返されます。

Fuxi job failed - WorkerRestart errCode:9,errMsg:SigKill(OOM), usually caused by OOM(out of memory).

MAPJOINヒントを削除し、マルチウェイ結合を使用することを推奨します。

wm_concat.first.argument.const

集計関数に記載されているWM_CONCAT関数に基づいて、WM_CONCATの最初のパラメーターは定数である必要があります。ただし、MaxComputeの初期バージョンには厳密なチェック標準がありません。たとえば、ソーステーブルにデータがない場合、WM_CONCATの最初のパラメーターがColumnReferenceであってもエラーは返されません。

Function declaration:
string wm_concat(string separator, string str)
Parameters:
separator: the delimiter, which is a constant of the STRING type. Delimiters of other types or non-constant delimiters result in exceptions.

MaxCompute V2.0は、計画段階でパラメーターの有効性をチェックします。 WM_CONCATの最初のパラメーターが定数でない場合、エラーが返されます。例：

無効な構文:

select wm_concat(value, ',') FROM src group by value;

エラーメッセージ：

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: com.aliyun.odps.lot.cbo.validator.AggregateCallValidator$AggregateCallValidationException: Invalid argument type - The first argument of WM_CONCAT must be constant string.

pt.implicit.convertion.failed

srcptは、2つのパーティションを持つパーティションテーブルです。

create table srcpt(key STRING, value STRING) partitioned by (pt STRING);
alter table srcpt add partition (pt='pt1');
alter table srcpt add partition (pt='pt2');

上記のSQL文では、STRING型のpt列のINT型の定数は、比較のためにDOUBLE型の定数に変換されます。プロジェクトにodps.sql.udf.strict.mo de=trueが設定されていても、MaxComputeの初期バージョンではエラーが返されず、すべてのpt列が除外されます。ただし、MaxCompute V2.0ではエラーが返されます。例：

無効な構文:

select key from srcpt where pt in (1, 2);

エラーメッセージ：

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.NumberFormatException: ODPS-0123091:Illegal type cast - In function cast, value 'pt1' cannot be casted from String to Double.

STRING定数とINT定数のパーティションキー列の値を比較しないことをお勧めします。このような比較が必要な場合は、INT定数をSTRING型に変換します。

e.select.alias having.us

SQL仕様では、GROUP BY句とHAVING句がSELECT句の前にあると定義されています。したがって、SELECT句によって生成された列エイリアスは、HAVING句では使用できません。

例

無効な構文:

select id id2 from table_name group by id having id2 > 0;

エラーメッセージ：

FAILED: ODPS-0130071:[1,44] Semantic analysis exception - column id2 cannot be resolvedODPS-0130071:[1,44] Semantic analysis exception - column reference id2 should appear in GROUP BY key

id2はSELECT句によって生成される列エイリアスであり、HAVING句では使用できません。

dynamic.pt.to.static

MaxCompute V2.0では、動的パーティションはオプティマイザによって静的パーティションに変換できます。

例

insert overwrite table srcpt partition(pt) select id, 'pt1' from table_name;

上記のステートメントは、次のステートメントに変換されます。

insert overwrite table srcpt partition(pt='pt1') select id from table_name;

'${bizdate}' など、指定されたパーティション値が無効な場合、MaxCompute V2.0は構文チェック中にエラーを返します。詳細については、「パーティション」をご参照ください。

無効な構文:

insert overwrite table srcpt partition(pt) select id, '${bizdate}' from table_name limit 0;

エラーメッセージ：

FAILED: ODPS-0130071:[1,24] Semantic analysis exception - wrong columns count 2 in data source, requires 3 columns (includes dynamic partitions if any)

MaxComputeの初期バージョンでは、LIMIT 0のためにSQL文から結果が返されず、動的パーティションは作成されません。その結果、エラーは返されません。

lot.not.in.subquery

inサブクエリでのNULL値の処理。

標準のSQL In操作では、値リストにNULL値が含まれている場合、戻り値はNULLまたはtrueになりますが、falseになることはありません。たとえば、(null, 1,2, 3) の1はtrueを返し、(null, 2,3) の1はNULLを返し、(null, 1,2, 3) のnullはNULLを返します。同様に、NOT IN操作の場合、値リストにNULL値が含まれている場合、戻り値はfalseまたはNULLになりますが、trueにはなりません。

MaxCompute V2.0は、標準の実行ルールを使用してNULL値を処理します。この問題の通知を受け取った場合は、in操作のサブクエリにNULL値があるかどうか、および関連する実行が期待どおりかどうかを確認します。関連する実行が期待に合わない場合は、クエリを変更します。

例

```
select * from t where c not in (select accepted from c_list);
```
受け入れられた列にNULL値が含まれていない場合は、この問題を無視します。 accepted列にNULL値が含まれている場合、c_not in (select accepted from c_list) はMaxComputeの初期バージョンでtrueを返し、MaxCompute V2.0ではNULLを返します。

有効な構文:

select * from t where c not in (select accepted from c_list where accepted is not null)