Not being able to export a Big Query table

Question

I am trying to export a Big Query table into cloud storage using NEWLINE_DELIMITED_JSON as destination format but I keep on receiving the following error: Error running job: Operation cannot be performed on a nested schema.

I know Big Query allows to export nested tables if you choose the json format (as it is stated both on the documentation and on this question), so I don't understand how come I am getting this error... I am using the PHP SDK and this is the code I've been using:

extract_table($projectId, $datasetId, $tableId, $bucketName, $objectName);


function extract_table($projectId, $datasetId, $tableId, $bucketName, $objectName, $format = 'NEWLINE_DELIMITED_JSON'){
    $bigQuery = new BigQueryClient(['projectId' => $projectId,]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);
    // load the storage object
    $storage = new StorageClient([
      'projectId' => $projectId,
    ]);
    $destinationObject = $storage->bucket($bucketName)->object($objectName);
    // create the extract job
    $options = ['destinationFormat' => $format];
    $extractConfig = $table->extract($destinationObject, $options);

    $job = $table->runJob($extractConfig);
    // poll the job until it is complete
    $backoff = new ExponentialBackoff(10);
    try {
       $backoff->execute(function () use ($job) {
           print('Waiting for job to complete' . PHP_EOL);
           $job->reload();
           if (!$job->isComplete()) {
               throw new Exception('Job has not yet completed', 500);
           }
        });
    } catch (Exception $e) {
    }
    // check if the job has errors
    if (isset($job->info()['status']['errorResult'])) {
        $error = $job->info()['status']['errorResult']['message'];
        printf('Error running job: %s' . PHP_EOL, $error);
    } else {
        print('Data extracted successfully' . PHP_EOL);
    }
}

dsesto dsesto · Accepted Answer · 2018-05-28T15:13:30

Your issue is that you are using the wrong options field configuration. Following the PHP documentation on the Table.extract method, you will see that the options field in the extract method must be a configuration object such as the one presented in the BigQuery documentation.

You are defining your options object as:

$options = ['destinationFormat' => $format];

Instead, you should use the format:

$options = ['configuration' => ['extract' => ['destinationFormat' => $format]]];

This is because the destinationFormat field is nested under configuration and extract.

Not being able to export a Big Query table

1 Answers