[230523]Data Clean Room Topics

[ustato]

(BigQuery) SQL pushdown is generally available (GA).

概要

SQL プッシュダウン は最適化手法の一つです。これは、BigQuery がフィルタリングなどのオペレーションを自身で実行する代わりに、外部データソース（例: Cloud SQL または Cloud Spanner データベース）に委任するときに行われます。

何がGood？

BigQuery に戻す必要があるデータの量が少なくなるため、クエリの総実行時間が短縮されます。また、処理されるデータが少なくなるため、コストを大幅に削減できます。

BT Group のデジタル部門がクエリを見直したところ、上記のようなクエリを BigQuery 連携クエリが実行するのに 10 分かかっていたことがわかりました。今では、プッシュダウンのおかげで 26 秒しかかかりません。

Usage

現在、SQL プッシュダウンは SELECT * FROM T という形式のクエリにのみ適用されます。

Cloud SQL - プッシュダウンなし

SELECT *
FROM  operations_table

Cloud SQL - プッシュダウンあり

SELECT
 "a",
 "b",
 "c"
FROM (
 SELECT *
 FROM  operations_table) t
WHERE
 (("a" = $1)
   AND (NOT "b" IN ($2,
       $3))
   AND ("c" = $4))

[ustato]

(Snowflake)New SQL Functions

概要

新しい関数が追加された

関数	説明
ARRAY_GENERATE_RANGE	指定された範囲内の整数値の ARRAY を返します (例: [2, 3, 4])。
ARRAY_REMOVE	ソース ARRAY を指定すると、指定された値の要素が削除された ARRAY を返します。
ARRAY_REMOVE_AT	ソース ARRAY を指定すると、指定された位置にある要素が削除された ARRAY を返します。

[ustato]

(Snowflake)Geospatial Functions for Shape Transformation and Orientation — General Availability

概要

新しい地理空間関数が追加された

関数	説明
ST_BUFFER	入力 GEOMETRY オブジェクトから指定された距離内の点を含む MultiPolygon を表す GEOMETRY オブジェクトを返します。返されたオブジェクトは、事実上、入力オブジェクトの周囲の「バッファ」を表します。
ST_SIMPLIFY	ラインまたはポリゴンを表す入力 GEOMETRY オブジェクトを指定すると、オブジェクトのより単純な近似を返します。この関数は選択された頂点を識別して削除し、頂点の数が減った同様のオブジェクトが生成されます。
ST_AZIMUTH	GEOMETRY オブジェクトである 2 つの Points を指定すると、その 2 つの Point によって形成される線分の方位角 (ラジアン単位) を返します。
ST_MAKEPOLYGONORIENTED	穴のない多角形を表す GEOGRAPHY オブジェクトを構築します。この関数は、指定された LineString を外側のループとして使用します。この関数はループの向きを修正しようとしないため、大きな図形の向きを反転するST_MAKEPOLYGON 、 ST_POLYGONとは対照的に、地球の半分以上にまたがる多角形の作成が可能になります。

何がGood?

MySQL等で使われていた地理空間関数が使い回せるようになってきた感があります。

Usage

下記はあくまでMySQLで行う例です。

点Pから200m内の点を抽出する

デカルト座標で生成した円ポリゴンを、強引に SRID 4326 の数値であるかのように読み替えたものに含まれる、テーブル上の点(sp)を抽出するSQLです。 ST_Within() を使っています。

SELECT id, ST_AsText(sp) p ,
       ST_Distance_Sphere(ST_GeomFromText('POINT(35.685 139.762)', 4326), sp) d 
  FROM sp1
WHERE
  ST_Within(
     sp,
     ST_SRID(
       ST_Buffer(
         ST_GeomFromText('POINT(139.762 35.685)'),
         200 * 180.0 / PI() / 6378137.0
       ), 4326)
)
ORDER BY d;

+--------+----------------------------------------------+--------------------+
| id     | p                                            | d                  |
+--------+----------------------------------------------+--------------------+
| 131966 | POINT(35.68480000000051 139.76239999999962)  |  42.42302952190574 |
| 131965 | POINT(35.68480000000051 139.76159999999962)  |  42.42302958257237 |
:              :
| 130964 | POINT(35.6834000000005 139.76239999999962)   | 181.54307375208234 |
| 130963 | POINT(35.6834000000005 139.76159999999962)   | 181.54307376625917 |
+--------+----------------------------------------------+--------------------+
18 rows in set (0.00 sec)

https://sakaik.hateblo.jp/entry/20201216/mysql_gis_select_points_in_the_circle

[ustato]

(Snowflake)Support for Specifying How to Handle Invalid Geospatial Shapes — General Availability

概要

デフォルトでは、地理空間変換関数を使用して、サポートされている入力形式のデータをGEOGRAPHY または GEOMETRY オブジェクトに変換すると 、関数は形状を検証し、形状が無効な場合は形状を修復しようとします。形状を修復できない場合、関数は GEOGRAPHY または GEOMETRY オブジェクトを作成しません。

この機能を使用すると、検証および修復プロセスをより詳細に制御できるようになります。あなたはできる：

• これらの変換関数で無効な形状の GEOGRAPHY オブジェクトと GEOMETRY オブジェクトを作成できるようにします。
• GEOGRAPHY または GEOMETRY オブジェクトの形状が無効かどうかを確認します。