通过Cloud Datalab加入Google BigQuery

I am trying to do JOIN on two columns from two different tables (one of them is a view) in Google BigQuery. I have tried this numerous ways, but have received this error the most consistently:

我试图在Google BigQuery中的两个不同的表（其中一个是视图）中的两列上进行JOIN。我尝试过这么多种方法，但是最一致地收到了这个错误：

invalidQuery: 2.1 - 0.0: JOIN cannot be applied directly to a table union or to a table wildcard function. Consider wrapping the table union or table wildcard function in a subquery (e.g., SELECT *).

Here is my SQL (legacy) query:

这是我的SQL（遗留）查询：

SELECT 
  blp_today.beta_key,
  blp_today.px_last,
  blp_today.eqy_weighted_avg_px,
  blp_today.created_date,
  blp_today.security_ticker,
  ciq_company_stg.ticker,
  ciq_company_stg.ciq
FROM 
  [fcm-dw:acquisition_bloomberg.blp_today],
  [fcm-dw:acquisition_ciq] 
JOIN
  blp_today.security_ticker AS ticker
ON
  blp_today.security_ticker = ciq_company_stg.ticker
LIMIT 1000

Any help would be much appreciated.

任何帮助将非常感激。

2 个解决方案

#1

I think you either want something like this:

我想你要么想要这样的东西：

SELECT * FROM(SELECT 
  beta_key,
  px_last,
  eqy_weighted_avg_px,
  created_date,
  security_ticker,
FROM 
  [fcm-dw:acquisition_bloomberg.blp_today],
  [fcm-dw:acquisition_ciq] ) as  a
JOIN
  blp_today.security_ticker AS ticker
ON
  a.security_ticker = ciq_company_stg.ticker
LIMIT 1000

//edit: I kind of missed earlier that the table that you are joining (after your join statement) does not actually seem to be a table. Are you trying to join or to union these two tables: [fcm-dw:acquisition_bloomberg.blp_today] and [fcm-dw:acquisition_ciq] ? And is the latter even a table? Your code seems to indicate that there is another table named: [fcm-dw:acquisition_ciq.ciq_company_stg]?

//编辑：我之前有点想念你加入的表（在你的join语句之后）实际上似乎不是一个表。你是想加入还是联合这两个表：[fcm-dw：acquisition_bloomberg.blp_today]和[fcm-dw：acquisition_ciq]？而后者甚至是一张桌子？你的代码似乎表明还有另一个名为：[fcm-dw：acquisition_ciq.ciq_company_stg]的表？

#2

First wrap your union into a sub select then join the result

首先将您的联合包装到子选择中然后加入结果

select ...
FROM 
(select * from 
  [fcm-dw:acquisition_bloomberg.blp_today],
  [fcm-dw:acquisition_ciq] ) t
JOIN
  blp_today.security_ticker AS ticker

#1