BigQuery ML : syntaxe
Instructions et fonctions de BigQuery ML, du CREATE MODEL aux fonctions AI.*. Le choix du type de modèle, la tarification et les pièges sont dans BigQuery ML.
Entraîner un modèle
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Créer ou remplacer un modèle | CREATE OR REPLACE MODEL `PROJECT_ID.DATASET.MODELE` OPTIONS (MODEL_TYPE = 'LOGISTIC_REG', INPUT_LABEL_COLS = ['resilie']) AS SELECT * FROM DATASET.TABLE |
Toutes les colonnes du SELECT autres que l'étiquette servent de variables ; aussi CREATE MODEL IF NOT EXISTS |
| Régression linéaire | MODEL_TYPE = 'LINEAR_REG' |
Poids lisibles par ML.WEIGHTS |
| Classification binaire ou multiclasse | MODEL_TYPE = 'LOGISTIC_REG', AUTO_CLASS_WEIGHTS = TRUE |
Pondère les classes rares |
| Arbres boostés (XGBoost) | MODEL_TYPE = 'BOOSTED_TREE_CLASSIFIER' ou 'BOOSTED_TREE_REGRESSOR', MAX_TREE_DEPTH = 6, SUBSAMPLE = 0.8 |
Aussi RANDOM_FOREST_*, DNN_* ; entraînés sur Agent Platform |
| AutoML tabulaire | MODEL_TYPE = 'AUTOML_CLASSIFIER' ou 'AUTOML_REGRESSOR', BUDGET_HOURS = 1.0 |
Recherche automatique du modèle, dans la limite du budget d'heures |
| Segmentation | MODEL_TYPE = 'KMEANS', NUM_CLUSTERS = 5 |
Sans étiquette ; ML.PREDICT renvoie centroid_id, ML.CENTROIDS les centres |
| Recommandation | MODEL_TYPE = 'MATRIX_FACTORIZATION', FEEDBACK_TYPE = 'IMPLICIT', USER_COL = 'client', ITEM_COL = 'produit', RATING_COL = 'nb_vues' |
EXPLICIT pour des notes ; prédiction par ML.RECOMMEND ; réservation obligatoire |
| Série temporelle | MODEL_TYPE = 'ARIMA_PLUS', TIME_SERIES_TIMESTAMP_COL = 'jour', TIME_SERIES_DATA_COL = 'ventes', TIME_SERIES_ID_COL = 'magasin', HORIZON = 90, HOLIDAY_REGION = 'FR' |
Une série par valeur de TIME_SERIES_ID_COL ; ARIMA_PLUS_XREG ajoute des covariables |
| Découpage entraînement / évaluation | DATA_SPLIT_METHOD = 'RANDOM', DATA_SPLIT_EVAL_FRACTION = 0.2 |
Défaut AUTO_SPLIT : tout en entraînement sous 500 lignes, 20 % d'évaluation jusqu'à 50 000, puis 10 000 lignes |
| Découpage chronologique | DATA_SPLIT_METHOD = 'SEQ', DATA_SPLIT_COL = 'jour', DATA_SPLIT_EVAL_FRACTION = 0.2 |
Les lignes les plus récentes servent à l'évaluation |
| Découpage fourni par une colonne | DATA_SPLIT_METHOD = 'CUSTOM', DATA_SPLIT_COL = 'is_eval' |
Colonne BOOL : TRUE ou NULL en évaluation |
| Réglage d'hyperparamètres | NUM_TRIALS = 20, MAX_PARALLEL_TRIALS = 4, L2_REG = HPARAM_RANGE(0, 10) |
Essais comparés par ML.TRIAL_INFO |
| Prétraitement enregistré avec le modèle | CREATE MODEL ... TRANSFORM (ML.STANDARD_SCALER(age) OVER () AS age_n, ML.QUANTILE_BUCKETIZE(panier, 10) OVER () AS panier_q, * EXCEPT (age, panier)) OPTIONS (...) AS SELECT ... |
Réappliqué tel quel par ML.EVALUATE et ML.PREDICT |
| Enregistrer dans Model Registry | OPTIONS (..., MODEL_REGISTRY = 'VERTEX_AI', VERTEX_AI_MODEL_ID = 'churn') |
Des modèles BigQuery qui partagent le même VERTEX_AI_MODEL_ID deviennent des versions d'un même modèle ; un CREATE OR REPLACE remplace sa version |
| Enregistrer un modèle existant | ALTER MODEL `PROJECT_ID.DATASET.MODELE` SET OPTIONS (vertex_ai_model_id = 'churn') |
Même effet après coup ; l'identifiant ne se change plus ensuite |
Évaluer, prédire, expliquer
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Métriques sur les données d'évaluation | SELECT * FROM ML.EVALUATE(MODEL `PROJECT_ID.DATASET.MODELE`) |
Précision, rappel, F1, roc_auc, log_loss en classification ; erreurs et R² en régression |
| Métriques sur un autre jeu | ML.EVALUATE(MODEL `...`, (SELECT * FROM DATASET.TEST), STRUCT(0.6 AS threshold)) |
Seuil de décision personnalisé en classification binaire |
| Matrice de confusion, courbe ROC | ML.CONFUSION_MATRIX(MODEL `...`), ML.ROC_CURVE(MODEL `...`) |
Pour les classifieurs |
| Suivre l'entraînement | SELECT * FROM ML.TRAINING_INFO(MODEL `...`) |
Perte par itération, durée |
| Prédire | SELECT * FROM ML.PREDICT(MODEL `PROJECT_ID.DATASET.MODELE`, (SELECT * FROM DATASET.NOUVEAUX)) |
Colonnes d'entrée, plus predicted_<étiquette> et, en classification, predicted_<étiquette>_probs |
| Prédire avec un seuil | ML.PREDICT(MODEL `...`, TABLE DATASET.NOUVEAUX, STRUCT(0.7 AS threshold)) |
Défaut 0,5 en classification binaire |
| Expliquer chaque prédiction | ML.EXPLAIN_PREDICT(MODEL `...`, (SELECT ...), STRUCT(3 AS top_k_features)) |
Contribution des variables les plus influentes par ligne (5 par défaut) |
| Importance globale (explicabilité) | SELECT * FROM ML.GLOBAL_EXPLAIN(MODEL `...`) |
Exige ENABLE_GLOBAL_EXPLAIN = TRUE à l'entraînement |
| Importance des variables d'un arbre | SELECT * FROM ML.FEATURE_IMPORTANCE(MODEL `...`) |
Arbres boostés et forêts aléatoires : importance_weight, importance_gain, importance_cover par variable |
| Recommander | SELECT * FROM ML.RECOMMEND(MODEL `...`, (SELECT client FROM DATASET.CIBLES)) |
Note prédite pour chaque couple utilisateur-article |
| Prévoir | SELECT * FROM ML.FORECAST(MODEL `...`, STRUCT(30 AS horizon, 0.9 AS confidence_level)) |
Valeur prévue et intervalle de confiance ; horizon 3 par défaut |
| Détecter des anomalies dans une série | ML.DETECT_ANOMALIES(MODEL `...`, STRUCT(0.95 AS anomaly_prob_threshold)) |
Colonne is_anomaly ; sans table, porte sur l'historique d'entraînement (DECOMPOSE_TIME_SERIES laissé à TRUE) |
| Détecter des anomalies (k-means, PCA, autoencodeur) | ML.DETECT_ANOMALIES(MODEL `...`, STRUCT(0.02 AS contamination), TABLE DATASET.NOUVEAUX) |
contamination : part d'anomalies attendue dans les données d'entraînement |
| Prévoir sans entraîner | SELECT * FROM AI.FORECAST(TABLE DATASET.SERIES, data_col => 'ventes', timestamp_col => 'jour', id_cols => ['magasin'], horizon => 30) |
TimesFM 2.5 par défaut ; aussi AI.DETECT_ANOMALIES |
| Exporter le modèle | EXPORT MODEL `PROJECT_ID.DATASET.MODELE` OPTIONS (URI = 'gs://BUCKET/modeles/churn/') |
TensorFlow SavedModel, ou Booster XGBoost pour les arbres ; TRIAL_ID pour un essai précis |
Modèles distants et IA générative
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Modèle distant sur Gemini | CREATE OR REPLACE MODEL `PROJECT_ID.DATASET.gemini` REMOTE WITH CONNECTION DEFAULT OPTIONS (ENDPOINT = 'gemini-3.5-flash') |
Le compte de service de la connexion doit avoir Agent Platform User (roles/aiplatform.user) |
| Modèle distant sur un modèle d'embedding | ... REMOTE WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID` OPTIONS (ENDPOINT = 'text-embedding-005') |
Entrée de AI.GENERATE_EMBEDDING |
| Modèle ouvert déployé par BigQuery | ... REMOTE WITH CONNECTION DEFAULT OPTIONS (HUGGING_FACE_MODEL_ID = 'ORG/MODELE') |
Ou MODEL_GARDEN_MODEL_NAME ; ENDPOINT_IDLE_TTL libère l'endpoint inactif |
| Modèle distant sur une API ML | ... OPTIONS (REMOTE_SERVICE_TYPE = 'CLOUD_AI_TRANSLATE_V3') |
Aussi CLOUD_AI_VISION_V1, CLOUD_AI_NATURAL_LANGUAGE_V1, CLOUD_AI_DOCUMENT_V1, CLOUD_AI_SPEECH_TO_TEXT_V2 |
| Générer du texte ligne par ligne | SELECT AI.GENERATE(CONCAT('Résume : ', texte), endpoint => 'gemini-3.5-flash').result FROM DATASET.AVIS |
STRUCT avec result, full_response et status ; sans endpoint, BigQuery choisit un Gemini stable ; sans connexion, identifiants de l'utilisateur |
| Sortie structurée | AI.GENERATE(('Extrais produit et motif : ', texte), output_schema => 'produit STRING, motif STRING, urgent BOOL') |
result remplacé par les champs typés ; le prompt peut être un STRUCT de chaînes et d'ObjectRef |
| Régler le modèle | model_params => JSON '{"generation_config": {"temperature": 0, "thinking_config": {"thinking_budget": 0}}}' |
Corps generateContent sauf contents ; limite les jetons de réflexion facturés |
| Générer sur une table via un modèle distant | SELECT * FROM AI.GENERATE_TEXT(MODEL `PROJECT_ID.DATASET.gemini`, (SELECT texte AS prompt FROM DATASET.AVIS), STRUCT(256 AS max_output_tokens, 0.2 AS temperature)) |
La colonne d'entrée doit s'appeler prompt ; sortie result, statistics, status |
| Extraire une table typée | AI.GENERATE_TABLE(MODEL `...`, (SELECT ... AS prompt ...), STRUCT('nom STRING, age INT64' AS output_schema)) |
Une colonne par champ du schéma ; modèles Gemini seulement |
| Filtrer en langage naturel | SELECT * FROM DATASET.AVIS WHERE AI.IF(('Cet avis signale un défaut de livraison : ', texte)) |
BOOL ; NULL en cas d'erreur de ligne |
| Classer dans des catégories | AI.CLASSIFY(texte, categories => ['livraison', 'facturation', 'qualité', 'autre']) |
STRING ; output_mode => 'multi' rend un ARRAY<STRING> |
| Noter | AI.SCORE(('Note de 1 à 10 la satisfaction du client : ', texte)) |
FLOAT64, à trier ou filtrer |
| Résumer un groupe de lignes | SELECT produit, AI.AGG(texte, 'Résume les plaintes récurrentes') FROM DATASET.AVIS GROUP BY produit |
Agrégat : un texte par groupe |
| Faire échouer au-delà d'un taux d'erreurs | AI.IF(..., max_error_ratio => 0.05) |
Par défaut 1,0 : les lignes en erreur rendent NULL sans faire échouer la requête |
| Embedding d'une valeur | SELECT AI.EMBED(texte, endpoint => 'text-embedding-005').result FROM DATASET.DOCS |
ARRAY<FLOAT64> ; task_type => 'RETRIEVAL_DOCUMENT' ou 'RETRIEVAL_QUERY' |
| Embeddings d'une table | SELECT * FROM AI.GENERATE_EMBEDDING(MODEL `PROJECT_ID.DATASET.emb`, (SELECT id, texte AS content FROM DATASET.DOCS), STRUCT(256 AS output_dimensionality)) |
La colonne d'entrée doit s'appeler content ; sortie embedding, statistics, status |
Recherche vectorielle
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Rechercher les voisins d'un lot de requêtes | SELECT query.id, base.id, distance FROM VECTOR_SEARCH(TABLE DATASET.DOCS_EMB, 'embedding', (SELECT id, embedding FROM DATASET.QUESTIONS), top_k => 5, distance_type => 'COSINE') |
Colonnes query, base (des STRUCT) et distance ; top_k 10 par défaut, distance EUCLIDEAN par défaut |
| Préfiltrer la base | VECTOR_SEARCH((SELECT * FROM DATASET.DOCS_EMB WHERE langue = 'fr'), 'embedding', ...) |
Seuls SELECT, FROM et WHERE sont admis dans la requête de base |
| Créer un index vectoriel | CREATE VECTOR INDEX idx ON DATASET.DOCS_EMB(embedding) STORING (langue) OPTIONS (index_type = 'IVF', distance_type = 'COSINE', ivf_options = '{"num_lists": 1000}') |
Recherche approchée ; TREE_AH (ScaNN) en alternative ; un index par table ; colonnes stockées pour préfiltrer |
| Régler le compromis rappel / vitesse | options => '{"fraction_lists_to_search": 0.1}' |
Plus de listes lues : meilleur rappel, plus lent |
| Forcer la recherche exacte | options => '{"use_brute_force": true}' |
Ignore l'index |
| Rechercher sur une colonne à embeddings autonomes | AI.SEARCH(TABLE DATASET.DOCS, 'texte', 'délai de remboursement', mode => 'HYBRID', top_k => 5) |
Colonne source (et non la colonne vectorielle) d'une table à embeddings générés par AI.EMBED ; mode vaut AUTO par défaut, hybride si l'index vectoriel a des colonnes lexicales |
| Supprimer l'index | DROP VECTOR INDEX idx ON DATASET.DOCS_EMB |
— |
Les fonctions génératives s'appliquent à chaque ligne : tester sur un
LIMIT 10, puis estimer le volume avecAI.COUNT_TOKENSavant de lancer la requête sur la table entière. Une ligne en erreur (quota, filtre de sécurité) rendNULLou unstatusnon vide sans faire échouer la requête.
Contexte : BigQuery ML, RAG, Entraîner et servir un modèle, MLOps, BigQuery : DDL.