export data from bigquery to cloud storage- php client library - there is one extra empty new line in the cloud storage file

Question

I followed this sample

https://cloud.google.com/bigquery/docs/exporting-data

public function exportDailyRecordsToCloudStorage($date, $tableId)
{
    $validTableIds = ['table1', 'table2'];

    if (!in_array($tableId, $validTableIds))
    {
        die("Wrong TableId");
    }

    $date = date("Ymd", date(strtotime($date)));
    $datasetId = $date;
    $dataset = $this->bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);

    // load the storage object
    $storage = $this->storage;

    $bucketName = 'mybucket';
    $objectName = "daily_records/{$tableId}_" . $date;
    $destinationObject = $storage->bucket($bucketName)->object($objectName);

    // create the import job
    $format = 'NEWLINE_DELIMITED_JSON';

    $options = ['jobConfig' => ['destinationFormat' => $format]];
    $job = $table->export($destinationObject, $options);

    // poll the job until it is complete
    $backoff = new ExponentialBackoff(10);
    $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);
        }
    });

    // 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 exported successfully' . PHP_EOL);
    }

I have 37670 rows in my table1, and the cloud storage file has 37671 lines.

And I have 388065 my table2, and the cloud storage file has 388066 lines.

The last line in both cloud storage files is empty line.

Is this a Google BigQuery feature improvement request? or I did something wrong in my codes above?

I'm from bigquery and I created a ticked for this issue. But in the future, you could also use BigQuery Issue Tracker to report such kind of issues. — xuejian

Nhan Nguyen Nhan Nguyen · Accepted Answer · 2017-05-08T19:15:28

What you described seems like an unexpected outcome. The output file should generally has the same number of lines as the source table.

Your PHP code looks fine and shouldn't be the cause of the issue.

I'm trying reproduce it but unable to. Could you double-check if the last empty line is somehow added by another tool like a text editor or something? How are you counting the lines of the resulting output.

If you have ruled that out and are sure the newline is indeed added by BigQuery export feature, please consider opening a bug using the BigQuery Issue Tracker as suggested by xuejian and include your job ID so that we can investigate further.

export data from bigquery to cloud storage- php client library - there is one extra empty new line in the cloud storage file

1 Answers