CODEWITHSUNDEEP
google-bigquery
Ben
Ben

Reputation: 1581

Train BigQueryML with a Cloud SQL federated query

I'm trying to train (and later use) a BigQueryML model using a federated Cloud SQL query.

This works without issues:

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;");

This same query used to train a BigQueryML model doesn't:

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;");

but instead gives me

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

The Cloud SQL instance and the dataset are in the same location.

Are federated queries not supported in BigQueryML (and do I need to import the data rather than use federated queries)? Or do I need to enable permissions somewhere to make this federated query work through BigQueryML?

Upvotes: 0

Views: 123

Answers (1)

Jaytiger
Jaytiger

Reputation: 12234

Refer to the limitations below.

Query statements used in the CREATE MODEL statement cannot contain EXTERNAL_QUERY. If you want to use EXTERNAL_QUERY, then materialize the query result and then use the CREATE MODEL statement with the newly created table.

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

Upvotes: 1

Related Questions