Google BigQuery ML创建模型时预估2.4GB却处理100GB的原因咨询

Let’s break down why your CREATE MODEL operation might be chewing through 100GB instead of the expected 2.4GB, along with practical checks and fixes:

1. AutoML’s Trial-and-Error is Likely the Culprit #

If you used an AutoML model type (like AUTOML_CLASSIFIER or AUTOML_REGRESSOR), this is almost certainly the reason. BigQuery’s AutoML automatically tests dozens of model architectures, feature combinations, and hyperparameters to find the best fit. Each trial processes your full dataset (or a large subset) again—so 40+ trials on your 2.4GB table would easily add up to 100GB of processed bytes.

2. Cross-Validation & Feature Expansion #

Even with standard model types (like logistic regression), BigQuery ML can expand your data during training:

• Cross-validation: The default setting for many models uses 5-10 folds, which means your data is split into subsets and processed multiple times. While 10-fold CV would only multiply your data by 10, if you have nested/repeated fields, flattening those can explode the row count and total bytes.
• Feature transformations: One-hot encoding for categorical columns, embedding generation, or other built-in transforms create additional columns that increase the total data size processed.

3. Intermediate Processing Overhead #

BigQuery ML creates hidden intermediate tables during training—think split training/validation sets, transformed feature datasets, etc. These aren’t visible in your dataset, but every byte read from or written to these tables counts toward your processed bytes. For complex models, these intermediate steps can add up quickly.

4. Storage vs. Processed Bytes Difference #

Your table’s 2.4GB is its compressed storage size, but BigQuery processes data in uncompressed columnar format. While compression ratios can vary, a 40x jump (2.4GB to 100GB) is way beyond typical compression—so this is probably not the main issue here.

How to Verify & Fix This #

• Check training details: Run this query to see exactly what’s driving the processing:

  SELECT * FROM ML.TRAINING_INFO(MODEL `your-project.your-dataset.your-model`)
  

  Look for columns like num_trials (for AutoML) or num_folds (for cross-validation) to confirm the source of extra processing.
• Tame AutoML or cross-validation: If you don’t need full AutoML exploration, switch to a standard model type. For standard models, disable cross-validation or reduce the number of folds:

  CREATE MODEL `your-model`
  OPTIONS(
    MODEL_TYPE='LOGISTIC_REG',
    NUM_FOLDS=1, -- Disable cross-validation
    MAX_ITERATIONS=50
  )
  AS SELECT * FROM your-table;
  

• Test raw data expansion: Run a query that flattens all nested/repeated fields in your table and check its bytes processed. This will tell you if data flattening is contributing to the increase:

  -- Replace nested fields with explicit expansion if needed
  SELECT * EXCEPT(nested_field), nested_field.* FROM your-table;
  

内容的提问来源于stack exchange，提问作者Firenze