Hacker News Reader

Comment by orf | Hacker News Reader

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?

View on HN

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)

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)

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.

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.

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.

Reply on news.ycombinator.com