I want to retrieve the file content of a file at a given commit in Googles BigQuery Github Dataset ("github_repos").
Hence I would expect something like SELECT content FROM sample_contents WHERE commit_id = abc (just as one example. In the future it should be a join). But sadly I am not able to find a link i.e. an attribute which is shared between the commits-table and the contents-table.
How can I join the tables commits and contents?
You need to use the table file to join these two tables commits and contents.
You can see this example:
with example as (
SELECT a.id as idFiles,b.id as idContents,a.repo_name as idContents_1,c.repo_name as idCommit_1
FROM `files` a
join `contents` b on a.id=b.id
join `commits` c on a.repo_name=ARRAY_TO_STRING(c.repo_name, "")
where a.repo_name='conda-forge/feedstocks' and a.id='c798dafa5d536d96203f762db8fd11cbde8f3139'
)
select *
from example limit 10
As you can see in this image, the table files uses the field id to join the table contents because it uses the same id field. The table files uses the field repo_name to join the table commit. You must use the table files to join the tables commit and contents. You can’t join these two tables directly (commit and contents).
