使用Cloud联邦查询训练BigQueryML

Question

我正在尝试使用联邦Cloud查询来训练(稍后使用) BigQueryML模型。

这样做是没有问题的：

SELECT
   ML.NGRAMS(REGEXP_EXTRACT_ALL(LOWER(translated_title), '[a-z]+'), [1, 3]) as title_ngrams,
   ML.NGRAMS(REGEXP_EXTRACT_ALL(LOWER(translated_intro), '[a-z]+'), [1, 3]) as intro_ngrams,
   source_id,
   category_id,
   language,
   actual_interesting_level
FROM 
   EXTERNAL_QUERY("projects/***/locations/europe-west1/connections/conn", "SELECT * FROM articles WHERE actual_interesting_level IS NOT NULL;");

这个用于训练BigQueryML模型的查询没有：

CREATE OR REPLACE MODEL `***.news.news-classifier` OPTIONS(model_type='linear_reg', input_label_cols=['actual_interesting_level']) AS
SELECT
    ML.NGRAMS(REGEXP_EXTRACT_ALL(LOWER(translated_title), '[a-z]+'), [1, 3]) as title_ngrams,
    ML.NGRAMS(REGEXP_EXTRACT_ALL(LOWER(translated_intro), '[a-z]+'), [1, 3]) as intro_ngrams,
    source_id,
    category_id,
    language,
    actual_interesting_level
  FROM 
    EXTERNAL_QUERY("projects/***/locations/europe-west1/connections/conn", "SELECT * FROM articles WHERE actual_interesting_level IS NOT NULL;");

但却给了我

Invalid table-valued function EXTERNAL_QUERY External database credentials not found for connection projects/***/locations/europe-west1/connections/conn

Cloud实例和dataset位于同一个位置。

联邦查询在BigQueryML中不受支持吗(我是否需要导入数据而不是使用联邦查询)？还是需要在某个地方启用权限，以使联邦查询通过BigQueryML工作？

Answer 1

请参阅下面的限制。

创建模型语句中使用的

查询语句不能包含EXTERNAL_QUERY。如果要使用EXTERNAL_QUERY，则将查询结果具体化，然后在新创建的表中使用created语句。

https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create#limitations