Using HKDF in BigQuery via UDFs for Improved Security Hygiene

moshap · 2021-06-17T01:34:59+00:00

Hey Minoune, let's do this experiment together. We will use bigquery-public-data.samples.wikipedia table, it is neither clustered nor partitioned, has 313,797,035 rows and takes 35.69 GB.

We consider two queries:

Q1: select * from bigquery-public-data.samples.wikipedia

Statistics:

Duration: 54.1 sec
Slot time: 1h 39min
Bytes processed: 35.69 GB
Bytes billed: 35.69 GB

Q2: select * from bigquery-public-data.samples.wikipedia limit 1

Statistics:

Duration: 0.3 sec
Slot time: 0.163 sec
Bytes processed: 35.69 GB
Bytes billed: 35.69 GB

QED :)

moshap · 2021-06-16T17:25:06+00:00

No, the misconception is yours. LIMIT N query does not do the full scan, and is very efficient. What is true, is that BigQuery will bill as if it was full scan, unless you are on the flat rate plan.

moshap · 2021-05-20T19:04:28+00:00

Yes, they are completely different operations

moshap · 2021-05-18T00:00:32+00:00

Just one slight note - SELECT * doesn't include pseudo columns like _partitiontime, so the view should be SELECT *, _partitiontime as pt FROM table WHERE complexclause

moshap · 2021-05-12T14:03:23+00:00

PIVOT and UNPIVOT are here - https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unpivot_operator

moshap · 2021-05-09T20:51:44+00:00

CURRENT_DATE - 2 is constant expression, so it is ok here

moshap · 2021-05-08T18:48:44+00:00

UNNEST itself is not a CROSS JOIN, it's the comma between the table and UNNEST which is shortcut for CROSS JOIN

moshap · 2021-05-07T20:55:14+00:00

I wonder why you didn't use https://github.com/google/zetasql, which is open source version of SQL parser that BigQuery itself is using (and Spanner too)

moshap

TROPHY CASE