3 ms·

In their query they do: FROM (SELECT id,content FROM (SELECT id,content FROM [bigquery-public-data:github_repos.contents] WHERE NOT

by orf 10y ago

In their query they do: FROM (SELECT id,content FROM (SELECT id,content FROM [bigquery-public-data:github_repos.contents] WHERE NOT binary) WHERE content CONTAINS 'commons-collections<') Why the subquery? Why not WHERE NOT binary AND content CONTAINS...? is this a bigquery thing?

deleted 10y ago
[deleted]
fhoffa 10y ago
Good catch. It seems like an artifact of working and editing a query until they got what they wanted - certainly not a BigQuery restriction. (like this https://www.youtube.com/watch?v=cO1a1Ek-HD0 https://www.youtube.com/watch?v=cO1a1Ek-HD0) Note that the published query scans 2.25 TB of data. While impressive, for a better workflow and cost management I would split it into a 2 step process: - First extract all the files I'm interested in to a separate table (all pom.xmls?). - Then run whatever analysis you want over those files.
vgt 10y ago
This appears to be "legacy SQL" in BigQuery, which did not have query optimization - entirely rule-based query planning. The query is a little inefficient indeed. BigQuery has since released ANSI 2011 "standard SQL", which would does have an optimizer and would push predicates down). (work on GCP and worked on BQ until recently)
contravariant 10y ago
Maybe they want to make sure Binary data is filtered out first? Not exactly sure how rigid the evaluation order is in SQL or BigQuery.
havermeyer 10y ago
It should work fine either way. Using standard SQL in BigQuery, though, you can do: #standardSQL SELECT pop, repo_name, path FROM ( SELECT id, repo_name, path FROM `bigquery-public-data.github_repos.files` AS files WHERE path LIKE '%pom.xml' AND EXISTS ( SELECT 1 FROM `bigquery-public-data.github_repos.contents` WHERE NOT binary AND content LIKE '%commons-collections<%' AND content LIKE '%>3.2.1<%' AND id = files.id ) ) JOIN ( SELECT difference.new_sha1 AS id, ARRAY_LENGTH(repo_name) AS pop FROM `bigquery-public-data.github_repos.commits` CROSS JOIN UNNEST(difference) AS difference ) USING (id) ORDER BY pop DESC; Better yet, it runs faster than the legacy SQL query :) As a disclosure, I work on the project to support standard SQL in BigQuery.

by orf 10y ago

deleted 10y ago

[deleted]

fhoffa 10y ago

Good catch. It seems like an artifact of working and editing a query until they got what they wanted - certainly not a BigQuery restriction. (like this https://www.youtube.com/watch?v=cO1a1Ek-HD0 https://www.youtube.com/watch?v=cO1a1Ek-HD0) Note that the published query scans 2.25 TB of data. While impressive, for a better workflow and cost management I would split it into a 2 step process: - First extract all the files I'm interested in to a separate table (all pom.xmls?). - Then run whatever analysis you want over those files.

vgt 10y ago

This appears to be "legacy SQL" in BigQuery, which did not have query optimization - entirely rule-based query planning. The query is a little inefficient indeed. BigQuery has since released ANSI 2011 "standard SQL", which would does have an optimizer and would push predicates down). (work on GCP and worked on BQ until recently)

contravariant 10y ago

Maybe they want to make sure Binary data is filtered out first? Not exactly sure how rigid the evaluation order is in SQL or BigQuery.

havermeyer 10y ago

It should work fine either way. Using standard SQL in BigQuery, though, you can do: #standardSQL SELECT pop, repo_name, path FROM ( SELECT id, repo_name, path FROM `bigquery-public-data.github_repos.files` AS files WHERE path LIKE '%pom.xml' AND EXISTS ( SELECT 1 FROM `bigquery-public-data.github_repos.contents` WHERE NOT binary AND content LIKE '%commons-collections<%' AND content LIKE '%>3.2.1<%' AND id = files.id ) ) JOIN ( SELECT difference.new_sha1 AS id, ARRAY_LENGTH(repo_name) AS pop FROM `bigquery-public-data.github_repos.commits` CROSS JOIN UNNEST(difference) AS difference ) USING (id) ORDER BY pop DESC; Better yet, it runs faster than the legacy SQL query :) As a disclosure, I work on the project to support standard SQL in BigQuery.