Data Transfer – Google BigQuery to Google Cloud Storage

There is a variety of ways of getting data out of BigQuery using the API including streaming the results into memory and creating a file for download, however if you need to download a large amount of data then this will initially need to be exported to Google Cloud Storage before the resulting files can be downloaded.

In my case this data was to be loaded into a SQL database so I exported it as a CSV but it can also be exported as JSON or AVRO depending on the structure of your results.

The way to export BigQuery tables to first run a query against them and to then export these query results to Cloud Storage. The complete code for this post is here.

RunBigQueryAsync

public static async Task<BigQueryResults> RunBigQueryAsync(BigQueryClient bigQueryClient, string query)
{
	BigQueryJob bigQueryJob = await bigQueryClient.CreateQueryJobAsync(
		sql: query,
		parameters: null
		);

	await bigQueryJob.PollUntilCompletedAsync();

	return await bigQueryClient.GetQueryResultsAsync(bigQueryJob.Reference);
}

ExportBigQueryTableToStorageAsync

public static async Task ExportBigQueryTableToStorageAsync(BigQueryClient bigQueryClient, string destinationUri, BigQueryResults results)
{
	CreateExtractJobOptions jobOptions = new CreateExtractJobOptions()
	{
		DestinationFormat = FileFormat.Csv,
		Compression = CompressionType.Gzip
	};

	BigQueryJob job = bigQueryClient.CreateExtractJob(
		projectId: results.TableReference.ProjectId,
		datasetId: results.TableReference.DatasetId,
		tableId: results.TableReference.TableId,
		destinationUri: destinationUri,
		options: jobOptions
	);

	await job.PollUntilCompletedAsync();
}

Usage

GoogleCredential googleCredential = GoogleCredential.FromFile(_googleCredentialPath);
BigQueryClient bigQueryClient = await BigQueryClient.CreateAsync(_bigQueryProjectId, googleCredential);
string query = $"select * from `{_bigQueryProjectId}.{_bigQueryDataSetId}.{_bigQueryTableId}`";

BigQueryResults results = await RunBigQueryAsync(bigQueryClient, query);

await ExportBigQueryTableToStorageAsync(bigQueryClient, _cloudStorageDestinationUri, results);