BigQueryでPARTITION BYなどを使った際、「allotted memory～」のエラーが起きる原因と理解について

Question

BigQueryで、巨大データに対して「PARTITION BY」のWINDOW関数を使うと以下のようなエラーが返ってくるかと思います。

Error: Resources exceeded during query execution: The query could not be executed in the allotted memory. Sort operator used for PARTITION BY used too much memory..

上記の理解について、２点質問です。

●テーブル内のデータ(=レコード)には「並び順」という概念がない。
その為「PARTITION BY」のように、全レコードの並び順を操作する処理を走らせると、
上記エラーが返るという理解で良いか。

●分散処理させる（例えば、テーブル内の大きな塊を最初にELSEやWHERE句などで外す）ことで、
本件は回避できるものか。

どうぞ、よろしくお願いします。

【追記1】

WITH T1 as (
	SELECT
		カラム1,
		カラム2,
		カラム3
	FROM
		テーブル1(2,000万レコード)

	UNION ALL

	SELECT
		カラム1,
		カラム2,
		カラム3
	FROM
		テーブル2(5,000万レコード)
)

SELECT
    ROW_NUMBER() OVER (PARTITION BY カラム1 ORDER BY カラム2, カラム3) as CNT
FROM T1

▼結果

Error: Resources exceeded during query execution: The query could not be executed in the allotted memory. Sort operator used for PARTITION BY used too much memory..

【追記2】
現在、下記サイトを参考にしております。
引き続き、よろしくお願いします。

「大きなデータを取得する」
https://tech.starttoday-tech.com/entry/bigquery_data_reduction#%E5%A4%A7%E3%81%8D%E3%81%AA%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B

Comments

[sazi]

どんなSQLなのか追記して下さい

[SatoshiMaruyama]

SQLを追記いたしました。どうぞよろしくお願いします。

Answer 1

パーティション内の連番を取りたいということでしょうか。
リンク先(大きなデータを取得する)にもあるように、扱うデータを区切るしか無さそうですので、先ずは、パーティションで指定している単位を条件にして、order byの対象になる件数を減らすしか無い気がします。

Answer 2

allotted
↓
allocated

だとは思いますが。

その為「PARTITION BY」のように、全レコードの並び順を操作する処理を走らせると、上記エラーが返るという理解で良いか。

メモリが足りないと言っていますから、PARTITION BY を削って良いならた削ったSQLで試されては？

●分散処理させる（例えば、テーブル内の大きな塊を最初にELSEやWHERE句などで外す）ことで、本件は回避できるものか。

条件で対象データを絞り込めるならリソースは少なくなりますが、実際に使えるメモリ、テーブルの全レコードの件数、絞り込んだ時の件数、SQLとそのSQLを実行できるだけのTABLE定義(そのまま提示できなければテーブル名やカラム名を差支えないものに置換)を提示すると適切なコメントが付き易いです。

Comments

[SatoshiMaruyama]

ありがとうございます。SQLを追記しました。求めている結果は、SQL記述の通り「PARTITION BY カラム1の中で」という制約がかかります。すみませんが引き続きよろしくお願いします。

[Orlofsky]

件数の多いテーブルをUNION ALLして、WHEREでデータを絞り込むようなこともしていないので、かなり厳しいです。
SELECTを複数に分けてそれぞれWHEREで条件を変えることで対応できないか検討されては？

[yuukive]

allottedでも割当という意味があるので合ってると思います。
(BQのエラーメッセージ) `The query could not be executed in the allotted memory.`

[Orlofsky]

合っていましたか。間違ったコメントをしてすみません。

Answer 3

皆様
ご回答ありがとうございました。

やはり、大きいデータをUNIONして、WHERE句の絞り込み無しでは厳しかったです。

主旨・用途は異なりますが、WHERE句でデータを落とさず、レコード数を減らす方法として、
以下のように、GROUP BYしたユニークID毎に、複数あるレコードの当該カラムを
カンマ区切り等の配列関数(ARRAY_AGG、STRING_AGG)で集約させました。
また、「ARRAY_AGG関数」については、MODIFY_DATEでソートした最新データを正として
取得する処理にしました。結果、効果的でした。

SELECT
    ID,
    ARRAY_AGG ( カラムA ORDER BY MODIFY_DATE DESC )[OFFSET(0)] as カラムA(最新版のみ取得),
    STRING_AGG( カラムA ORDER BY MODIFY_DATE DESC )            as カラムA(カンマ区切り 全て取得)
FROM (
    SELECT DISTINCT
        ID,
        カラムA
        MODIFY_DATE
    FROM テーブル
)
GROUP BY ID