Google BigQuery: Upload ExecuteQuery result to Google Cloud Storage in json format

Question

I am importing bigquery table which contains repeated records, I need to upload those records to google cloud storage, How can i convert the result to json format so that i can store it in google cloud?

Below is my code:

  BigQueryClient Client = BigQueryClient.Create("test", _googleCredential)

   string query = @"select * from `projecttest.TestDataset.CostData`";

 result = Client.ExecuteQuery(query, parameters: null, queryOptions: new QueryOptions { UseLegacySql = false });

Answer 1

As I see you do this for whole table - you should rather use Export job with JSON format

Answer 2

You can use the Exporting Table Data BigQuery functionality that can be implemented to export your tables data from BigQuery to GCS in several formats, such as JSON, CSV and Avro export formats.

I suggest you to take a look on the Exporting data stored in BigQuery official documentation that contains some useful examples to submit an extract job via API, Web UI, Command-line and Client Libraries in case you use a specific programming language as shown in the following C# code sample:

public void ExportJsonToGcs(
    string datasetId, string tableId, string bucketName, string fileName,
    BigQueryClient client)
{
    StorageClient gcsClient = StorageClient.Create();
    string contentType = "application/json";
    // Get Table and append results into StringBuilder.
    PagedEnumerable<TableDataList, BigQueryRow> result = client.ListRows(datasetId, tableId);
    StringBuilder sb = new StringBuilder();
    foreach (var row in result)
    {
        sb.Append($"{{\"title\" : \"{row["title"]}\", \"unique_words\":\"{row["unique_words"]}\"}}{Environment.NewLine}");
    }
    // Save stream to Google Cloud Storage.
    using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(sb.ToString())))
    {
        var obj = gcsClient.UploadObject(bucketName, fileName, contentType, stream);
    }
}