Specific BigQuery output table does not work with BatchPredictionJob

[allengour]

Per the docs at https://googleapis.dev/python/aiplatform/latest/aiplatform/services.html?#google.cloud.aiplatform.Model.batch_predict and https://googleapis.dev/python/aiplatform/latest/aiplatform.html?#google.cloud.aiplatform.BatchPredictionJob.create

bigquery_destinationprefix – Optional[str] = None The BigQuery URI to a project or table, up to 2000 characters long. When only the project is specified, the Dataset and Table is created. When the full table reference is specified, the Dataset must exist and table must not exist. Accepted forms: bq://projectId or bq://projectId.bqDatasetId or bq://projectId.bqDatasetId.bqTableId. If no Dataset is specified, a new one is created with the name prediction_ where the table name is made BigQuery-dataset-name compatible (for example, most special characters become underscores), and timestamp is in YYYY_MM_DDThh_mm_ss_sssZ “based on ISO-8601” format. In the dataset two tables will be created, predictions, and errors. If the Model has both instance and prediction schemata defined then the tables have columns as follows: The predictions table contains instances for which the prediction succeeded, it has columns as per a concatenation of the Model’s instance and prediction schemata. The errors table contains rows for which the prediction has failed, it has instance columns, as per the instance schema, followed by a single “errors” column, which as values has google.rpc.Status <Status>__ represented as a STRUCT, and containing only code and message.

A prefix uri like bq://projectId.bqDatasetId.bqTableId is an acceptable input to bigquery_destination_prefix. However, when that is passed in, I receive:

InvalidArgument: 400 Table name is not allowed in Bigquery output uri.

The same error can be observed in the UI, even though UI suggests tableId is allowed and passes validation.

Note that the documentation does not mention allowing a user-specified output table in bigquery_destination_prefix here:

• https://cloud.google.com/python/docs/reference/aiplatform/latest/google.cloud.aiplatform.Model#google_cloud_aiplatform_Model_batch_predict
• https://cloud.google.com/python/docs/reference/aiplatform/latest/google.cloud.aiplatform.BatchPredictionJob#google_cloud_aiplatform_BatchPredictionJob_create

Code example

import google.cloud.aiplatform as aip
model = aip.Model(
    model_name='projects/123/locations/us-central1/models/123',
)
model.batch_predict(
    instances_format='bigquery',
    bigquery_source='bq://redactedProject.redactedDataset.redactedSourceTable',
    bigquery_destination_prefix='bq://redactedProject.redactedDataset.redactedSpecificTable',
)

or

import google.cloud.aiplatform as aip
aip.BatchPredictionJob.create(
    job_display_name='try_specifying_output_table',
    model_name='projects/123/locations/us-central1/models/123',
    instances_format='bigquery',
    predictions_format='bigquery',
    bigquery_source='bq://redactedProject.redactedDataset.redactedSourceTable',
    bigquery_destination_prefix='bq://redactedProject.redactedDataset.redactedSpecificTable',
)

Steps to reproduce

Model type: AutoMLForecasting

On UI:

1. 
2. 

Stack trace

~/.local/lib/python3.7/site-packages/google/cloud/aiplatform/models.py in batch_predict(self, job_display_name, gcs_source, bigquery_source, instances_format, gcs_destination_prefix, bigquery_destination_prefix, predictions_format, model_parameters, machine_type, accelerator_type, accelerator_count, starting_replica_count, max_replica_count, generate_explanation, explanation_metadata, explanation_parameters, labels, credentials, encryption_spec_key_name, sync, create_request_timeout, batch_size)
   3622             encryption_spec_key_name=encryption_spec_key_name,
   3623             sync=sync,
-> 3624             create_request_timeout=create_request_timeout,
   3625         )
   3626 

~/.local/lib/python3.7/site-packages/google/cloud/aiplatform/jobs.py in create(cls, job_display_name, model_name, instances_format, predictions_format, gcs_source, bigquery_source, gcs_destination_prefix, bigquery_destination_prefix, model_parameters, machine_type, accelerator_type, accelerator_count, starting_replica_count, max_replica_count, generate_explanation, explanation_metadata, explanation_parameters, labels, project, location, credentials, encryption_spec_key_name, sync, create_request_timeout, batch_size)
    701             generate_explanation=generate_explanation,
    702             sync=sync,
--> 703             create_request_timeout=create_request_timeout,
    704         )
    705 

~/.local/lib/python3.7/site-packages/google/cloud/aiplatform/base.py in wrapper(*args, **kwargs)
    791                 if self:
    792                     VertexAiResourceNounWithFutureManager.wait(self)
--> 793                 return method(*args, **kwargs)
    794 
    795             # callbacks to call within the Future (in same Thread)

~/.local/lib/python3.7/site-packages/google/cloud/aiplatform/jobs.py in _create(cls, empty_batch_prediction_job, model_or_model_name, gca_batch_prediction_job, generate_explanation, sync, create_request_timeout)
    763             parent=parent,
    764             batch_prediction_job=gca_batch_prediction_job,
--> 765             timeout=create_request_timeout,
    766         )
    767 

~/.local/lib/python3.7/site-packages/google/cloud/aiplatform_v1/services/job_service/client.py in create_batch_prediction_job(self, request, parent, batch_prediction_job, retry, timeout, metadata)
   2414             retry=retry,
   2415             timeout=timeout,
-> 2416             metadata=metadata,
   2417         )
   2418 

/opt/conda/lib/python3.7/site-packages/google/api_core/gapic_v1/method.py in __call__(self, timeout, retry, *args, **kwargs)
    152             kwargs["metadata"] = metadata
    153 
--> 154         return wrapped_func(*args, **kwargs)
    155 
    156 

/opt/conda/lib/python3.7/site-packages/google/api_core/grpc_helpers.py in error_remapped_callable(*args, **kwargs)
     57             return callable_(*args, **kwargs)
     58         except grpc.RpcError as exc:
---> 59             raise exceptions.from_grpc_error(exc) from exc
     60 
     61     return error_remapped_callable

InvalidArgument: 400 Table name is not allowed in Bigquery output uri.

Environment details

• OS type and version: Local and Managed Workbench instances
• Python version: 3.7
• pip version: 22.1.1
• google-cloud-aiplatform version: 1.16.

[allengour]

@TheMichaelHu :D

[MALPICAHJ1010]

Aparently, the feature doesn't allow a specific table as it creates the table after the Batches Prediction. Using projectID.datasetID works around the error.

[nayaknishant]

Hi @allengour, thanks for the detailed issue. Our recommendation is to follow the guidance of the docstring:

When only the project is specified, the Dataset and Table is created. 
When the full table reference is specified, the Dataset must exist and table must not exist.

and the error message: InvalidArgument: 400 Table name is not allowed in Bigquery output uri.

I agree that bq://projectId.bqDatasetId.bqTableId shouldn't be an accepted format and that it can be confusing. The docstring has been edited in https://github.com/googleapis/python-aiplatform/commit/794cedd0e9e34544d6c8d5bf6892ef2c28eaaf5d.

[TheMichaelHu]

FYI Custom Models can accept destination tables during BP, however models trained using AutoML products cannot, the most granular they accept is destination dataset. I have filed a feature request to remove this restriction, but that is how it is right now.

[gfcornejo]

It appears the workaround has been ruined, now Vertex forces you to select a table, so AutoML batch predictions are essentially impossible to export into BigQuery atm

[TheMichaelHu]

Hi @gfcornejo thanks for pointing this out. I'm also seeing this behavior in the UI, which is definitely not intentional. Are you seeing an issue exporting to BQ via the Vertex SDK as well?

[gfcornejo]

I haven’t tried the SDK.

After the 10th refresh, the issue went away on its own!

On Fri, Nov 10, 2023 at 12:50 PM Michael Hu @.***> wrote:

Hi @gfcornejo https://github.com/gfcornejo thanks for pointing this out. I'm also seeing this behavior in the UI, which is definitely not intentional. Are you seeing an issue exporting to BQ via the Vertex SDK as well?

— Reply to this email directly, view it on GitHub https://github.com/googleapis/python-aiplatform/issues/1615#issuecomment-1806267090, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADVFDSUPVWCOAUU6L3ZHDHLYDZZQRAVCNFSM57U3QSSKU5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TCOBQGYZDMNZQHEYA . You are receiving this because you were mentioned.Message ID: @.***>