❄️

External Table で Vectorized Scanner を使いつつ SELECT * したいときに EXCLUDE 句が便利

2022/11/17に公開

SQL

Database

Snowflake

tech

前提知識

Snowflake の External Table では、Parquet ファイルの場合、Vectorized Scanner (ベクトル化スキャナ) という通常のスキャナよりも高速で特別なスキャナを利用することができ、TPC-DS 10 TB で取ったベンチマークではスキャン速度 8 倍、クエリ実行時間 1/2 という結果が記録されています。

The External Tables GA comes with a new vectorized scanner for parquet files, which is eight times faster than the previous, non-vectorized parquet scanner. The new vectorized scanner is designed to take advantage of parquet’s columnar file format. We did benchmarking of Snowflake with the previous parquet scanner and the vectorized scanner on TPC DS 10TB data set and the 102 queries that come with it. Tests demonstrated an 8X scan performance improvement and a 2X query performance improvement over the non-vectorized parquet scanner.

しかし Vectorized Scanner は参照するカラムが特定の条件を満たしていないと使用できません。

さらに、 "value:<パス>::<データ型>" （または同等の GET / GET_PATH、 : 関数）形式のクエリは、ベクトル化されたスキャナーを利用します。 "value" または単に "value:<パス>" 形式のクエリは、ベクトル化されていないスキャナーを使用して処理されます。

External Table のカラム定義は通常 value:<パス>::<データ型> という形式で定義されるので、SELECT * でも Vectorized Scanner が使用できそうに見えます。

…が実は External Table に SELECT * すると VALUE という Parquet の 1 レコード全体を格納するカラムが自動的についてきてしまうので、上記の制約にひっかかってしまい、 Vectorized Scanner が使用できない仕様となっています。

SELECT * は常にVALUE列を返します。この列では、すべての通常または半構造化データがバリアント行にキャストされます。

つまり、「全カラムを読みたい」けど「Vectorized Scanner も使ってほしい」というときには、SELECT と FROM の間にわざわざ全カラム名を列挙してあげる必要があることになります。

でも、カラム数多すぎて毎回全部のカラム名書くのめんどくさいんだけど……というときに SELECT * EXCLUDE が便利、というのが今回の記事で伝えたいことです。

検証

下記の記事と同じ方法で External Table を作成します。

copy into @ext/scanner-test/data
from snowflake_sample_data.tpcds_sf10tcl.catalog_returns
file_format = (type = parquet);

create or replace external table scanner_test (
    CR_RETURNED_DATE_SK string(6) as (value:"_COL_0"::string), 
	CR_RETURNED_TIME_SK NUMBER(38, 0) as (value:"_COL_1"::number), 
	CR_ITEM_SK NUMBER(38, 0) as (value:"_COL_2"::number), 
	CR_REFUNDED_CUSTOMER_SK NUMBER(38, 0) as (value:"_COL_3"::number), 
	CR_REFUNDED_CDEMO_SK NUMBER(38, 0) as (value:"_COL_4"::number), 
	CR_REFUNDED_HDEMO_SK NUMBER(38, 0) as (value:"_COL_5"::number), 
	CR_REFUNDED_ADDR_SK NUMBER(38, 0) as (value:"_COL_6"::number), 
	CR_RETURNING_CUSTOMER_SK NUMBER(38, 0) as (value:"_COL_7"::number), 
	CR_RETURNING_CDEMO_SK NUMBER(38, 0) as (value:"_COL_8"::number), 
	CR_RETURNING_HDEMO_SK NUMBER(38, 0) as (value:"_COL_9"::number), 
	CR_RETURNING_ADDR_SK NUMBER(38, 0) as (value:"_COL_10"::number), 
	CR_CALL_CENTER_SK NUMBER(38, 0) as (value:"_COL_11"::number), 
	CR_CATALOG_PAGE_SK NUMBER(38, 0) as (value:"_COL_12"::number), 
	CR_SHIP_MODE_SK NUMBER(38, 0) as (value:"_COL_13"::number), 
	CR_WAREHOUSE_SK NUMBER(38, 0) as (value:"_COL_14"::number), 
	CR_REASON_SK NUMBER(38, 0) as (value:"_COL_15"::number), 
	CR_ORDER_NUMBER NUMBER(38, 0) as (value:"_COL_16"::number), 
	CR_RETURN_QUANTITY NUMBER(38, 0) as (value:"_COL_17"::number), 
	CR_RETURN_AMOUNT NUMBER(7, 2) as (value:"_COL_18"::number), 
	CR_RETURN_TAX NUMBER(7, 2) as (value:"_COL_19"::number), 
	CR_RETURN_AMT_INC_TAX NUMBER(7, 2) as (value:"_COL_20"::number), 
	CR_FEE NUMBER(7, 2) as (value:"_COL_21"::number), 
	CR_RETURN_SHIP_COST NUMBER(7, 2) as (value:"_COL_22"::number), 
	CR_REFUNDED_CASH NUMBER(7, 2) as (value:"_COL_23"::number), 
	CR_REVERSED_CHARGE NUMBER(7, 2) as (value:"_COL_24"::number), 
	CR_STORE_CREDIT NUMBER(7, 2) as (value:"_COL_25"::number), 
	CR_NET_LOSS NUMBER(7, 2) as (value:"_COL_26"::number)
) 
location=@ext/scanner-test/
file_format = (type = parquet)
;

まずは、この External Table を SELECT * してみると、4XLARGE の仮想ウェアハウスでだいたい 126 秒かかります。

select * from scanner_test;
-- 2 min 6 sec

次に全部のカラムを列挙してみると、半分以下の 58 秒程度で完了します。

select CR_RETURNED_DATE_SK, CR_RETURNED_TIME_SK, CR_ITEM_SK, CR_REFUNDED_CUSTOMER_SK, CR_REFUNDED_CDEMO_SK, CR_REFUNDED_HDEMO_SK, CR_REFUNDED_ADDR_SK, CR_RETURNING_CUSTOMER_SK, CR_RETURNING_CDEMO_SK, CR_RETURNING_HDEMO_SK, CR_RETURNING_ADDR_SK, CR_CALL_CENTER_SK, CR_CATALOG_PAGE_SK, CR_SHIP_MODE_SK, CR_WAREHOUSE_SK, CR_REASON_SK, CR_ORDER_NUMBER, CR_RETURN_QUANTITY, CR_RETURN_AMOUNT, CR_RETURN_TAX, CR_RETURN_AMT_INC_TAX, CR_FEE, CR_RETURN_SHIP_COST, CR_REFUNDED_CASH, CR_REVERSED_CHARGE, CR_STORE_CREDIT, CR_NET_LOSS
from scanner_test;
-- 58.02 sec

最初に貼った記事の「クエリパフォーマンスが 2 倍」みたいな話が再現できましたが、毎回このカラム数を列挙するのはめんどくさいし可読性低いし、現にこの記事を書くにあたって上のクエリを準備するのも大変めんどくさかったです。

この問題は長年解決することができなかったのですが、とうとう Snowflake が SELECT * EXCLUDE という最高の文法をサポートするようになり、「全カラムから特定のカラムだけを取り除いて参照する」ということがかなりシンプルに書けるようになりました。

EXCLUDE <col_name>
EXCLUDE (<col_name>, <col_name>, ...)
When you select all columns (SELECT *), specifies the columns that should be excluded from the results.

というわけで、この SELECT * EXCLUDE を使って Vectorized Scanner を使うにあたっての障害になっている VALUE カラムを取り除いてあげると…

select * exclude (value) from scanner_test;
-- 1 min 3 sec

無事 Vectorized Scanner が使用されて、カラム名を全部並べたときと同じぐらいのパフォーマンスになりました。

まとめ

SELECT * EXCLUDE は神。

前提知識

検証

まとめ

Discussion