<commit_before>﻿/*
 * Copyright (c) 2017 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

using System;
// [START bigquery_client_default_credentials]
using Google.Cloud.BigQuery.V2;
// [END bigquery_client_default_credentials]
using Google.Cloud.Storage.V1;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xunit;
using System.IO;
using System.Diagnostics;
using Google.Apis.Bigquery.v2.Data;
using Google.Api.Gax;

namespace GoogleCloudSamples
{
    public class BigQueryTest : IDisposable
    {
        private readonly string _projectId;
        private readonly BigQueryClient _client;
        // Create list of tuples to collect tableName and datasetName for test tables to be deleted.
        private readonly List<Tuple<string, string>> _tablesToDelete = new List<Tuple<string, string>>();
        private readonly List<string> _datasetsToDelete = new List<string>();
        private readonly List<string> _filesToDelete = new List<string>();
        private static readonly Random s_random = new Random();
        // Generate random suffix for creating unique resource names.
        private static string RandomSuffix()
        {
            return s_random.Next(1000000).ToString();
        }

        public void Dispose()
        {
            try
            {
                // Delete all tables created from running the tests.
                foreach (Tuple<string, string> resource in _tablesToDelete)
                {
                    DeleteTable(resource.Item1, resource.Item2, _client);
                }
            }
            catch (Google.GoogleApiException ex) when
                (ex != null
                    && ex.HttpStatusCode == System.Net.HttpStatusCode.BadRequest)
            { }
            try
            {
                // Delete all datasets created from running the tests.
                foreach (string dataset in _datasetsToDelete)
                {
                    DeleteDataset(dataset, _client);
                }
            }
            catch (Google.GoogleApiException ex) when
                (ex != null
                    && ex.HttpStatusCode == System.Net.HttpStatusCode.BadRequest)
            { }
            try
            {
                // Delete all cloud storage files created from running the tests.
                foreach (string fileName in _filesToDelete)
                {
                    DeleteFileFromGcs(_projectId, fileName);
                }
            }
            catch (Google.GoogleApiException ex) when
                (ex != null
                    && ex.HttpStatusCode == System.Net.HttpStatusCode.BadRequest)
            { }
        }

        private readonly RetryRobot _retryDeleteBusy = new RetryRobot
        {
            ShouldRetry = (Exception e) =>
            {
                // Retry when we see:
                // Dataset yada:yada is still in use [400]
                var apiException = e as Google.GoogleApiException;
                return apiException != null &&
                    apiException.HttpStatusCode == System.Net.HttpStatusCode.BadRequest;
            }
        };

        public BigQueryTest()
        {
            // [START bigquery_client_default_credentials]
            // By default, the Google.Bigquery.V2 library client will authenticate 
            // using the service account file (created in the Google Developers 
            // Console) specified by the GOOGLE_APPLICATION_CREDENTIALS 
            // environment variable. If you are running on
            // a Google Compute Engine VM, authentication is completely 
            // automatic.
            _projectId = Environment.GetEnvironmentVariable("GOOGLE_PROJECT_ID");
            _client = BigQueryClient.Create(_projectId);
            // [END bigquery_client_default_credentials]
        }

        // [START bigquery_create_dataset]
        public void CreateDataset(string datasetId, BigQueryClient client)
        {
            var dataset = client.GetOrCreateDataset(datasetId);
        }
        // [END bigquery_create_dataset]

        // [START bigquery_create_table]
        public void CreateTable(string datasetId, string tableId, BigQueryClient client)
        {
            var dataset = client.GetDataset(datasetId);
            // Create schema for new table.
            var schema = new TableSchemaBuilder
            {
                { "title", BigQueryDbType.String },
                { "unique_words", BigQueryDbType.Int64 }
            }.Build();
            // Create the table if it doesn't exist.
            BigQueryTable table = dataset.GetOrCreateTable(tableId, schema);
        }
        // [END bigquery_create_table]

        public void DeleteDataset(string datasetId, BigQueryClient client)
        {
            _retryDeleteBusy.Eventually(() => client.DeleteDataset(datasetId));
        }

        // [START bigquery_delete_table]
        public void DeleteTable(string datasetId, string tableId, BigQueryClient client)
        {
            client.DeleteTable(_projectId, datasetId, tableId);
        }
        // [END bigquery_delete_table]

        // [START import_file_from_gcs]
        public void ImportDataFromCloudStorage(string projectId, string datasetId,
            string tableId, BigQueryClient client, string fileName, string folder = null)
        {
            StorageClient gcsClient = StorageClient.Create();

            using (var stream = new MemoryStream())
            {
                // Set Cloud Storage Bucket name. This uses a bucket named the same as the project.
                string bucket = projectId;
                // If folder is passed in, add it to Cloud Storage File Path using "/" character
                string filePath = string.IsNullOrEmpty(folder) ? fileName : folder + "/" + fileName;
                // Download Google Cloud Storage object into stream
                gcsClient.DownloadObject(projectId, filePath, stream);

                // This example uploads data to an existing table. If the upload will create a new table
                // or if the schema in the JSON isn't identical to the schema in the table,
                // create a schema to pass into the call instead of passing in a null value.
                BigQueryJob job = client.UploadJson(datasetId, tableId, null, stream);
                // Use the job to find out when the data has finished being inserted into the table,
                // report errors etc.

                // Wait for the job to complete.
                job.PollUntilCompleted();
            }
        }
        // [END import_file_from_gcs]

        // [START bigquery_query_legacy]
        public BigQueryResults LegacySqlAsyncQuery(string projectId, string datasetId,
            string tableId, string query, BigQueryClient client)
        {
            var table = client.GetTable(projectId, datasetId, tableId);
            BigQueryJob job = client.CreateQueryJob(query,
                parameters: null,
                options: new QueryOptions { UseLegacySql = true });

            // Wait for the job to complete.
            job.PollUntilCompleted();

            // Then we can fetch the results, either via the job or by accessing
            // the destination table.
            return client.GetQueryResults(job.Reference.JobId);
        }
        // [END bigquery_query_legacy]

        // [START bigquery_query]
        public BigQueryResults AsyncQuery(string projectId, string datasetId, string tableId,
            string query, BigQueryClient client)
        {
            var table = client.GetTable(projectId, datasetId, tableId);
            BigQueryJob job = client.CreateQueryJob(query,
                parameters: null,
                options: new QueryOptions { UseQueryCache = false });

            // Wait for the job to complete.
            job.PollUntilCompleted();

            // Then we can fetch the results, either via the job or by accessing
            // the destination table.
            return client.GetQueryResults(job.Reference.JobId);
        }
        // [END bigquery_query]


        // [START bigquery_load_from_file]
        public void UploadJsonFromFile(string projectId, string datasetId, string tableId,
            string fileName, BigQueryClient client)
        {
            using (FileStream stream = File.Open(fileName, FileMode.Open))
            {
                // This example uploads data to an existing table. If the upload will create a new table
                // or if the schema in the JSON isn't identical to the schema in the table,
                // create a schema to pass into the call instead of passing in a null value.
                BigQueryJob job = client.UploadJson(datasetId, tableId, null, stream);
                // Use the job to find out when the data has finished being inserted into the table,
                // report errors etc.

                // Wait for the job to complete.
                job.PollUntilCompleted();
            }
        }
        // [END bigquery_load_from_file]

        // [START bigquery_table_insert_rows]
        public void UploadJsonStreaming(string datasetId, string tableId,
            BigQueryClient client)
        {
            // The insert ID is optional, but can avoid duplicate data
            // when retrying inserts.
            BigQueryInsertRow row1 = new BigQueryInsertRow("row1")
            {
                { "title", "exampleJsonFromStream" },
                { "unique_words", 1 }
            };
            BigQueryInsertRow row2 = new BigQueryInsertRow("row2")
            {
                { "title", "moreExampleJsonFromStream" },
                { "unique_words", 1 }
            };
            client.InsertRows(datasetId, tableId, row1, row2);
        }
        // [END bigquery_table_insert_rows]

        // [START bigquery_extract_table]
        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);
            }
        }
        // [END bigquery_extract_table]

        public void ExportCsvToGcs(
            string datasetId, string tableId, string bucketName, string fileName, BigQueryClient client)
        {
            StorageClient gcsClient = StorageClient.Create();
            string contentType = "text/csv";
            // Get Table and input results into StringBuilder.
            var result = client.ListRows(datasetId, tableId);
            StringBuilder sb = new StringBuilder();
            // Create header row.
            sb.Append($"title, unique_words,{Environment.NewLine}");
            foreach (var row in result)
            {
                sb.Append($"{row["title"]}, {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);
            }
        }

        public void PopulateTable(
            string query, string datasetId, string newTableId, BigQueryClient client)
        {
            var destination = client.GetTableReference(datasetId, newTableId);
            BigQueryJob job = client.CreateQueryJob(query,
                parameters: null,
                options: new QueryOptions { DestinationTable = destination });
            // Wait for the job to complete.
            job.GetQueryResults();
        }

        // [START copy_table]
        public void CopyTable(
            string datasetId, string tableIdToBeCopied, string newTableId, BigQueryClient client)
        {
            var table = client.GetTable(datasetId, tableIdToBeCopied);
            string query = $"SELECT * FROM {table}";
            var destination = client.GetTableReference(datasetId, newTableId);
            BigQueryJob job = client.CreateQueryJob(query,
                parameters: null,
                options: new QueryOptions { DestinationTable = destination });
            // Wait for the job to complete.
            job.GetQueryResults();
        }
        // [END copy_table]

        public string GetFileNameFromCloudStorage(string bucket, string fileName)
        {
            StorageClient gcsClient = StorageClient.Create();
            var file = gcsClient.GetObject(bucket, fileName);
            return file.Name;
        }

        public void DeleteFileFromGcs(string bucket, string fileName)
        {
            StorageClient gcsClient = StorageClient.Create();
            gcsClient.DeleteObject(bucket, fileName);
        }

        // [START bigquery_list_datasets]
        public List<BigQueryDataset> ListDatasets(BigQueryClient client)
        {
            var datasets = client.ListDatasets().ToList();
            return datasets;
        }<commit_msg>fixes remaining region tags<commit_after>﻿/*
 * Copyright (c) 2017 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

using System;
// [START bigquery_client_default_credentials]
using Google.Cloud.BigQuery.V2;
// [END bigquery_client_default_credentials]
using Google.Cloud.Storage.V1;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xunit;
using System.IO;
using System.Diagnostics;
using Google.Apis.Bigquery.v2.Data;
using Google.Api.Gax;

namespace GoogleCloudSamples
{
    public class BigQueryTest : IDisposable
    {
        private readonly string _projectId;
        private readonly BigQueryClient _client;
        // Create list of tuples to collect tableName and datasetName for test tables to be deleted.
        private readonly List<Tuple<string, string>> _tablesToDelete = new List<Tuple<string, string>>();
        private readonly List<string> _datasetsToDelete = new List<string>();
        private readonly List<string> _filesToDelete = new List<string>();
        private static readonly Random s_random = new Random();
        // Generate random suffix for creating unique resource names.
        private static string RandomSuffix()
        {
            return s_random.Next(1000000).ToString();
        }

        public void Dispose()
        {
            try
            {
                // Delete all tables created from running the tests.
                foreach (Tuple<string, string> resource in _tablesToDelete)
                {
                    DeleteTable(resource.Item1, resource.Item2, _client);
                }
            }
            catch (Google.GoogleApiException ex) when
                (ex != null
                    && ex.HttpStatusCode == System.Net.HttpStatusCode.BadRequest)
            { }
            try
            {
                // Delete all datasets created from running the tests.
                foreach (string dataset in _datasetsToDelete)
                {
                    DeleteDataset(dataset, _client);
                }
            }
            catch (Google.GoogleApiException ex) when
                (ex != null
                    && ex.HttpStatusCode == System.Net.HttpStatusCode.BadRequest)
            { }
            try
            {
                // Delete all cloud storage files created from running the tests.
                foreach (string fileName in _filesToDelete)
                {
                    DeleteFileFromGcs(_projectId, fileName);
                }
            }
            catch (Google.GoogleApiException ex) when
                (ex != null
                    && ex.HttpStatusCode == System.Net.HttpStatusCode.BadRequest)
            { }
        }

        private readonly RetryRobot _retryDeleteBusy = new RetryRobot
        {
            ShouldRetry = (Exception e) =>
            {
                // Retry when we see:
                // Dataset yada:yada is still in use [400]
                var apiException = e as Google.GoogleApiException;
                return apiException != null &&
                    apiException.HttpStatusCode == System.Net.HttpStatusCode.BadRequest;
            }
        };

        public BigQueryTest()
        {
            // [START bigquery_client_default_credentials]
            // By default, the Google.Bigquery.V2 library client will authenticate 
            // using the service account file (created in the Google Developers 
            // Console) specified by the GOOGLE_APPLICATION_CREDENTIALS 
            // environment variable. If you are running on
            // a Google Compute Engine VM, authentication is completely 
            // automatic.
            _projectId = Environment.GetEnvironmentVariable("GOOGLE_PROJECT_ID");
            _client = BigQueryClient.Create(_projectId);
            // [END bigquery_client_default_credentials]
        }

        // [START bigquery_create_dataset]
        public void CreateDataset(string datasetId, BigQueryClient client)
        {
            var dataset = client.GetOrCreateDataset(datasetId);
        }
        // [END bigquery_create_dataset]

        // [START bigquery_create_table]
        public void CreateTable(string datasetId, string tableId, BigQueryClient client)
        {
            var dataset = client.GetDataset(datasetId);
            // Create schema for new table.
            var schema = new TableSchemaBuilder
            {
                { "title", BigQueryDbType.String },
                { "unique_words", BigQueryDbType.Int64 }
            }.Build();
            // Create the table if it doesn't exist.
            BigQueryTable table = dataset.GetOrCreateTable(tableId, schema);
        }
        // [END bigquery_create_table]

        public void DeleteDataset(string datasetId, BigQueryClient client)
        {
            _retryDeleteBusy.Eventually(() => client.DeleteDataset(datasetId));
        }

        // [START bigquery_delete_table]
        public void DeleteTable(string datasetId, string tableId, BigQueryClient client)
        {
            client.DeleteTable(_projectId, datasetId, tableId);
        }
        // [END bigquery_delete_table]

        // [START bigquery_load_table_gcs_csv]
        public void ImportDataFromCloudStorage(string projectId, string datasetId,
            string tableId, BigQueryClient client, string fileName, string folder = null)
        {
            StorageClient gcsClient = StorageClient.Create();

            using (var stream = new MemoryStream())
            {
                // Set Cloud Storage Bucket name. This uses a bucket named the same as the project.
                string bucket = projectId;
                // If folder is passed in, add it to Cloud Storage File Path using "/" character
                string filePath = string.IsNullOrEmpty(folder) ? fileName : folder + "/" + fileName;
                // Download Google Cloud Storage object into stream
                gcsClient.DownloadObject(projectId, filePath, stream);

                // This example uploads data to an existing table. If the upload will create a new table
                // or if the schema in the JSON isn't identical to the schema in the table,
                // create a schema to pass into the call instead of passing in a null value.
                BigQueryJob job = client.UploadJson(datasetId, tableId, null, stream);
                // Use the job to find out when the data has finished being inserted into the table,
                // report errors etc.

                // Wait for the job to complete.
                job.PollUntilCompleted();
            }
        }
        // [END bigquery_load_table_gcs_csv]

        // [START bigquery_query_legacy]
        public BigQueryResults LegacySqlAsyncQuery(string projectId, string datasetId,
            string tableId, string query, BigQueryClient client)
        {
            var table = client.GetTable(projectId, datasetId, tableId);
            BigQueryJob job = client.CreateQueryJob(query,
                parameters: null,
                options: new QueryOptions { UseLegacySql = true });

            // Wait for the job to complete.
            job.PollUntilCompleted();

            // Then we can fetch the results, either via the job or by accessing
            // the destination table.
            return client.GetQueryResults(job.Reference.JobId);
        }
        // [END bigquery_query_legacy]

        // [START bigquery_query]
        public BigQueryResults AsyncQuery(string projectId, string datasetId, string tableId,
            string query, BigQueryClient client)
        {
            var table = client.GetTable(projectId, datasetId, tableId);
            BigQueryJob job = client.CreateQueryJob(query,
                parameters: null,
                options: new QueryOptions { UseQueryCache = false });

            // Wait for the job to complete.
            job.PollUntilCompleted();

            // Then we can fetch the results, either via the job or by accessing
            // the destination table.
            return client.GetQueryResults(job.Reference.JobId);
        }
        // [END bigquery_query]


        // [START bigquery_load_from_file]
        public void UploadJsonFromFile(string projectId, string datasetId, string tableId,
            string fileName, BigQueryClient client)
        {
            using (FileStream stream = File.Open(fileName, FileMode.Open))
            {
                // This example uploads data to an existing table. If the upload will create a new table
                // or if the schema in the JSON isn't identical to the schema in the table,
                // create a schema to pass into the call instead of passing in a null value.
                BigQueryJob job = client.UploadJson(datasetId, tableId, null, stream);
                // Use the job to find out when the data has finished being inserted into the table,
                // report errors etc.

                // Wait for the job to complete.
                job.PollUntilCompleted();
            }
        }
        // [END bigquery_load_from_file]

        // [START bigquery_table_insert_rows]
        public void UploadJsonStreaming(string datasetId, string tableId,
            BigQueryClient client)
        {
            // The insert ID is optional, but can avoid duplicate data
            // when retrying inserts.
            BigQueryInsertRow row1 = new BigQueryInsertRow("row1")
            {
                { "title", "exampleJsonFromStream" },
                { "unique_words", 1 }
            };
            BigQueryInsertRow row2 = new BigQueryInsertRow("row2")
            {
                { "title", "moreExampleJsonFromStream" },
                { "unique_words", 1 }
            };
            client.InsertRows(datasetId, tableId, row1, row2);
        }
        // [END bigquery_table_insert_rows]

        // [START bigquery_extract_table]
        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);
            }
        }
        // [END bigquery_extract_table]

        public void ExportCsvToGcs(
            string datasetId, string tableId, string bucketName, string fileName, BigQueryClient client)
        {
            StorageClient gcsClient = StorageClient.Create();
            string contentType = "text/csv";
            // Get Table and input results into StringBuilder.
            var result = client.ListRows(datasetId, tableId);
            StringBuilder sb = new StringBuilder();
            // Create header row.
            sb.Append($"title, unique_words,{Environment.NewLine}");
            foreach (var row in result)
            {
                sb.Append($"{row["title"]}, {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);
            }
        }

        public void PopulateTable(
            string query, string datasetId, string newTableId, BigQueryClient client)
        {
            var destination = client.GetTableReference(datasetId, newTableId);
            BigQueryJob job = client.CreateQueryJob(query,
                parameters: null,
                options: new QueryOptions { DestinationTable = destination });
            // Wait for the job to complete.
            job.GetQueryResults();
        }

        // [START bigquery_copy_table]
        public void CopyTable(
            string datasetId, string tableIdToBeCopied, string newTableId, BigQueryClient client)
        {
            var table = client.GetTable(datasetId, tableIdToBeCopied);
            string query = $"SELECT * FROM {table}";
            var destination = client.GetTableReference(datasetId, newTableId);
            BigQueryJob job = client.CreateQueryJob(query,
                parameters: null,
                options: new QueryOptions { DestinationTable = destination });
            // Wait for the job to complete.
            job.GetQueryResults();
        }
        // [END bigquery_copy_table]

        public string GetFileNameFromCloudStorage(string bucket, string fileName)
        {
            StorageClient gcsClient = StorageClient.Create();
            var file = gcsClient.GetObject(bucket, fileName);
            return file.Name;
        }

        public void DeleteFileFromGcs(string bucket, string fileName)
        {
            StorageClient gcsClient = StorageClient.Create();
            gcsClient.DeleteObject(bucket, fileName);
        }

        // [START bigquery_list_datasets]
        public List<BigQueryDataset> ListDatasets(BigQueryClient client)
        {
            var datasets = client.ListDatasets().ToList();
            return datasets;
        }
{'fraction_non_alphanumeric': 0.07747912134184855, 'fraction_numerical': 0.002526493087234192, 'mean_word_length': 1.8804325854052961, 'pattern_counts': {'":': 2, '<': 25, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4727206', 'n_tokens_mistral': 6939, 'n_tokens_neox': 6751, 'n_words': 2131}