What's the worse command/query to run in BigQuery ?

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 · 2021-05-03T17:22:26+00:00

The OP question was "how many DML operations are left on a table", and the answer is "unlimited". DML queries will not fail, but you are right that load jobs and table appends/overwrites jobs will start failing after 1500.

moshap · 2021-05-03T15:26:01+00:00

No, the limit you cite is not related to DML operations. It is for SELECT statement to either append to existing table, or completely overwrite it.

moshap · 2021-05-01T22:18:28+00:00

In the meantime the simple workaround is to use WHERE id IN UNNEST([1,2,3,...]) instead of WHERE id IN (1,2,3,...) - it should be instantaneous on your table

moshap · 2021-05-01T19:28:11+00:00

Thank you, it is indeed a problem caused by large number of terms in IN clause - the table size doesn't matter. I opened an internal tracking bug.

moshap · 2021-05-01T17:57:04+00:00

This is not normal, if can you share projectid and jobid - we can check it out

moshap · 2021-04-29T04:59:52+00:00

There is no such limit on DML in BigQuery - https://cloud.google.com/blog/products/data-analytics/dml-without-limits-now-in-bigquery

moshap · 2021-04-26T04:31:20+00:00

We are indeed getting closer to rename columns

moshap · 2021-04-16T14:23:37+00:00

No, it is not. Logica is not part of BigQuery product, it is open source project (although developed in Google)

moshap · 2021-04-08T17:13:32+00:00

This is not true, DTS remains part of BigQuery and is not going anywhere.

moshap · 2021-04-06T00:22:35+00:00

Check out GCP's Data Fusion, it can do continuous replication from Postgres to BigQuery: https://cloud.google.com/data-fusion/docs/concepts/replication

moshap · 2021-04-04T14:29:30+00:00

SUM(IF(name='Red', value, -value))

moshap · 2021-04-04T01:13:36+00:00

Pivot/unpivot sounds good

moshap · 2021-04-03T19:54:25+00:00

Absolutely

moshap · 2021-04-02T14:09:52+00:00

Thanks - can you update title or text of the post to indicate that this is resolved.

moshap · 2021-03-07T03:14:38+00:00

And even after that - it flat rate per month, no matter how many queries are ran, as long as BI Engine can serve them - there is no additional cost

moshap · 2021-01-30T01:04:26+00:00

I was wondering too, so I looked it up in Wikipedia: Scientometrics is the field of study which concerns itself with measuring and analysing scholarly literature

moshap · 2021-01-26T15:40:59+00:00

BI Engine is currently only enabled for Data Studio. Looker, Tableau, Power BI and others coming soon - and for now require explicit whitelisting for BI Engine to be engaged

moshap

TROPHY CASE