CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
Pilven Matthieu
Pilven Matthieu

Reputation: 77

Joining tables with incompatible types

I'm trying to join two tables using this command :

SELECT * FROM bigquery-public-data.github_repos.files INNER JOIN bigquery-public-data.github_repos.commits USING (repo_name)

but there are incompatible types on either side of the join: STRING and ARRAY< STRING> Is there a way to go through this ?

Thank you !

Upvotes: 0

Views: 1135

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

You want to join a 2 billion row table with a 200 million row one. This won't end up well, unless you define restrictions on what you want to get out of this.

As for the technical problems of this query: The error says you are trying to JOIN a single value with an array of values. You need to UNNEST() that array.

This would work syntactically:

SELECT *
FROM `bigquery-public-data.github_repos.files` a
INNER JOIN (
  SELECT * EXCEPT(repo_name) 
  FROM `bigquery-public-data.github_repos.commits`
  , UNNEST(repo_name) repo
) b
ON a.repo_name=b.repo

But if you go for it, it will use all your free monthly quota (1TB of data scanned) for no good purpose, as far as I can tell.

Upvotes: 2

Related Questions