/// <summary>Snippet for AnalyzeDataSourceRisk</summary> public void AnalyzeDataSourceRisk() { // Snippet: AnalyzeDataSourceRisk(PrivacyMetric,BigQueryTable,CallSettings) // Create client DlpServiceClient dlpServiceClient = DlpServiceClient.Create(); // Initialize request argument(s) PrivacyMetric privacyMetric = new PrivacyMetric(); BigQueryTable sourceTable = new BigQueryTable(); // Make the request Operation <RiskAnalysisOperationResult, RiskAnalysisOperationMetadata> response = dlpServiceClient.AnalyzeDataSourceRisk(privacyMetric, sourceTable); // Poll until the returned long-running operation is complete Operation <RiskAnalysisOperationResult, RiskAnalysisOperationMetadata> completedResponse = response.PollUntilCompleted(); // Retrieve the operation result RiskAnalysisOperationResult result = completedResponse.Result; // Or get the name of the operation string operationName = response.Name; // This name can be stored, then the long-running operation retrieved later by name Operation <RiskAnalysisOperationResult, RiskAnalysisOperationMetadata> retrievedResponse = dlpServiceClient.PollOnceAnalyzeDataSourceRisk(operationName); // Check if the retrieved long-running operation has completed if (retrievedResponse.IsCompleted) { // If it has completed, then access the result RiskAnalysisOperationResult retrievedResult = retrievedResponse.Result; } // End snippet }
public Task InsertRowsAsync(IEnumerable <GoogleBigQueryRow> rows, CancellationToken cancellationToken) { if (rows != null && rows.Count() > 0) { return(GetTableAsync(cancellationToken) .ContinueWith((tableTask) => { BigQueryTable table = tableTask.Result; var bigQueryRows = rows.Select(c => BigQueryInsertRowService.GetBigQueryInsertRow(c, dictionaryOfProperties)).ToArray(); return table.InsertRowsAsync(bigQueryRows, new InsertOptions() { AllowUnknownFields = false }, cancellationToken) .ContinueWith((insertRowsTask) => { if (insertRowsTask.IsFaulted) { throw insertRowsTask.Exception.InnerExceptions.First(); } }); }, cancellationToken).Unwrap()); } return(Task.CompletedTask); }
public void LoadTableGcsCsv( string projectId = "your-project-id", string datasetId = "your_dataset_id" ) { BigQueryClient client = BigQueryClient.Create(projectId); var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.csv"; var dataset = client.GetDataset(datasetId); var schema = new TableSchemaBuilder { { "name", BigQueryDbType.String }, { "post_abbr", BigQueryDbType.String } }.Build(); var destinationTableRef = dataset.GetTableReference( tableId: "us_states"); // Create job configuration var jobOptions = new CreateLoadJobOptions() { // The source format defaults to CSV; line below is optional. SourceFormat = FileFormat.Csv, SkipLeadingRows = 1 }; // Create and run job var loadJob = client.CreateLoadJob( sourceUri: gcsURI, destination: destinationTableRef, schema: schema, options: jobOptions); loadJob = loadJob.PollUntilCompleted().ThrowOnAnyError(); // Waits for the job to complete. // Display the number of rows uploaded BigQueryTable table = client.GetTable(destinationTableRef); Console.WriteLine( $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}"); }
public void LoadFromFile( string projectId = "your-project-id", string datasetId = "your_dataset_id", string tableId = "your_table_id", string filePath = "path/to/file.csv" ) { BigQueryClient client = BigQueryClient.Create(projectId); // Create job configuration var uploadCsvOptions = new UploadCsvOptions() { SkipLeadingRows = 1, // Skips the file headers Autodetect = true }; using (FileStream stream = File.Open(filePath, FileMode.Open)) { // Create and run job // Note that there are methods available for formats other than CSV BigQueryJob job = client.UploadCsv( datasetId, tableId, null, stream, uploadCsvOptions); job = job.PollUntilCompleted().ThrowOnAnyError(); // Waits for the job to complete. // Display the number of rows uploaded BigQueryTable table = client.GetTable(datasetId, tableId); Console.WriteLine( $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}"); } }
public void CopyTable( string projectId = "your-project-id", string destinationDatasetId = "your_dataset_id" ) { BigQueryClient client = BigQueryClient.Create(projectId); TableReference sourceTableRef = new TableReference() { TableId = "shakespeare", DatasetId = "samples", ProjectId = "bigquery-public-data" }; TableReference destinationTableRef = client.GetTableReference( destinationDatasetId, "destination_table"); BigQueryJob job = client.CreateCopyJob( sourceTableRef, destinationTableRef) .PollUntilCompleted() // Wait for the job to complete. .ThrowOnAnyError(); // Retrieve destination table BigQueryTable destinationTable = client.GetTable(destinationTableRef); Console.WriteLine( $"Copied {destinationTable.Resource.NumRows} rows from table " + $"{sourceTableRef.DatasetId}.{sourceTableRef.TableId} " + $"to {destinationTable.FullyQualifiedId}." ); }
public string CreateTempEmptyTable(string datasetId) { string tableId = TestUtil.RandomName(); BigQueryTable table = _client.CreateTable(datasetId, tableId, null); return(tableId); }
public void Patch() { string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = _fixture.GenerateTableId(); BigQueryClient.Create(projectId).CreateTable(datasetId, tableId, new TableSchema()); // Snippet: Patch(Table, bool, *) BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable dataset = client.GetTable(datasetId, tableId); // There's no ETag in this Table. The matchETag parameter in the method call // determines whether the ETag in the original resource is propagated into the // patch. Table patch = new Table { FriendlyName = "Patched table" }; BigQueryTable updated = dataset.Patch(patch, matchETag: true); Console.WriteLine($"Patched table friendly name: {updated.Resource.FriendlyName}"); // End snippet Assert.Equal("Patched table", updated.Resource.FriendlyName); }
public void LoadTableGcsOrcTruncate( string projectId = "your-project-id", string datasetId = "your_dataset_id", string tableId = "your_table_id" ) { BigQueryClient client = BigQueryClient.Create(projectId); var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.orc"; var dataset = client.GetDataset(datasetId); TableReference destinationTableRef = dataset.GetTableReference( tableId: "us_states"); // Create job configuration var jobOptions = new CreateLoadJobOptions() { SourceFormat = FileFormat.Orc, WriteDisposition = WriteDisposition.WriteTruncate }; // Create and run job var loadJob = client.CreateLoadJob( sourceUri: gcsURI, destination: destinationTableRef, // Pass null as the schema because the schema is inferred when // loading Orc data schema: null, options: jobOptions); loadJob.PollUntilCompleted(); // Waits for the job to complete. // Display the number of rows uploaded BigQueryTable table = client.GetTable(destinationTableRef); Console.WriteLine( $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}"); }
public void LoadTableGcsJson( string projectId = "your-project-id", string datasetId = "your_dataset_id" ) { BigQueryClient client = BigQueryClient.Create(projectId); var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json"; var dataset = client.GetDataset(datasetId); var schema = new TableSchemaBuilder { { "name", BigQueryDbType.String }, { "post_abbr", BigQueryDbType.String } }.Build(); TableReference destinationTableRef = dataset.GetTableReference( tableId: "us_states"); // Create job configuration var jobOptions = new CreateLoadJobOptions() { SourceFormat = FileFormat.NewlineDelimitedJson }; // Create and run job BigQueryJob loadJob = client.CreateLoadJob( sourceUri: gcsURI, destination: destinationTableRef, schema: schema, options: jobOptions); loadJob.PollUntilCompleted(); // Waits for the job to complete. // Display the number of rows uploaded BigQueryTable table = client.GetTable(destinationTableRef); Console.WriteLine( $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}"); }
/// <summary> /// Polls until this table contains at least the given number of rows. This should be used carefully, only where we know the table won't be huge. /// </summary> /// <remarks> /// <para> /// This method is designed to make integration tests more reliable: after an insert, data fetching (with queries or ListRows) /// may not see the new data. /// </para> /// <para> /// We've tried using the data in BigQueryTable.Resource.NumRows and BigQueryTable.Resource.StreamingBuffer, but they're not as reliable as /// just reading the rows. /// </para> /// </remarks> /// <param name="expectedRows">The number of rows expected.</param> /// <param name="pollSettings">The poll settings to use, or null to use the defaults (poll once every 2 seconds, 30 second timeout)</param> /// <returns>The actual number of rows in the table.</returns> /// <exception cref="TimeoutException">The timeout specified in the poll settings elapsed before the streaming buffer became empty.</exception> public static int PollUntilRowCountIsAtLeast(this BigQueryTable table, int expectedRows, PollSettings pollSettings = null) => Polling.PollRepeatedly( ignoredDeadline => table.ListRows().Count(), count => count >= expectedRows, SystemClock.Instance, SystemScheduler.Instance, pollSettings ?? s_defaultStreamingBufferPollSettings, CancellationToken.None);
public void TestCreateTable() { var snippet = new BigQueryCreateTable(); string datasetId = CreateTempDataset(); BigQueryTable table = snippet.CreateTable(_projectId, datasetId); Assert.Equal("your_table_id", table.Resource.TableReference.TableId); }
public void ListJobs_FilterByLabels() { string bucketName = _fixture.StorageBucketName; string objectName = _fixture.GenerateStorageObjectName(); string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = _fixture.HistoryTableId; // Snippet: Labels IDictionary <string, string> labels = new Dictionary <string, string>() { { "label-key", "label-value" } }; BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(projectId, datasetId, tableId); string destinationUri = $"gs://{bucketName}/{objectName}"; // Just a couple examples of jobs marked with labels: // (These jobs will most certainly be created somewhere else.) // Running a query on a given table. BigQueryJob oneLabeledJob = client.CreateQueryJob( $"SELECT * FROM {table}", null, new QueryOptions { Labels = labels }); // Extracting data from a table to GCS. BigQueryJob anotherLabeledJob = client.CreateExtractJob( projectId, datasetId, tableId, destinationUri, new CreateExtractJobOptions { Labels = labels }); // Find jobs marked with a certain label. KeyValuePair <string, string> labelToBeFound = labels.First(); // Specify full projection to make sure that // label information, if it exists, is returned for listed jobs. ListJobsOptions options = new ListJobsOptions { Projection = ProjectionEnum.Full }; List <BigQueryJob> jobs = client .ListJobs(options) .Where(job => job.Resource.Configuration.Labels?.Contains(labelToBeFound) ?? false) .Take(2) .ToList(); foreach (BigQueryJob job in jobs) { Console.WriteLine(job.Reference.JobId); } // End snippet // This test added two jobs with such labels, other tests might have // added more. Assert.True(jobs.Count >= 2); }
// SELECT COUNT(MFACTOR), MFACTOR FROM `dataflix-public-datasets.traffic_safety.factor` GROUP BY MFACTOR public BigQueryResults getAccidentsFactors() { BigQueryTable table = client.GetTable(PROJECT_ID, DATASET_ID, FACTOR_TABLE_ID); var sql = $"SELECT MFACTOR AS factor, COUNT(MFACTOR) AS accidents_count FROM {table} WHERE MFACTOR NOT IN ('None', 'Not Reported', 'Unknown') GROUP BY MFACTOR"; var results = client.ExecuteQuery(sql, parameters: null); return(results); }
internal void InsertAndWait(BigQueryTable table, Action insertAction, int expectedRowCountChange) { var countBefore = table.ListRows().Count(); var expectedCount = countBefore + expectedRowCountChange; insertAction(); // Wait until there are *at least* enough rows int actualCount = table.PollUntilRowCountIsAtLeast(expectedCount); // Now check it's *exactly* the right number of rows. Assert.Equal(expectedCount, actualCount); }
public BigQueryResults getAccidentsByStateAndYear(string year) { string queryYear = year != null? $"WHERE YEAR={year}" : ""; BigQueryTable table = client.GetTable(PROJECT_ID, DATASET_ID, ACCIDENTS_TABLE_ID); var sql = $"SELECT STATE AS state, COUNT(STATE) AS accidents_count FROM {table} {queryYear} GROUP BY STATE ORDER BY COUNT(STATE) DESC"; var results = client.ExecuteQuery(sql, parameters: null); return(results); }
// [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); }
private static string GetBigQueryType(BigQueryClient client, string datasetName, string tableName, string key) { BigQueryTable table = client.GetTable(datasetName, tableName); foreach (var field in table.Schema.Fields) { if (field.Name == key) { return(field.Type); } } return(null); }
public async Task <bool> selectExceptUsers() { BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); string sql = $"SELECT * EXCEPT (SecondNumber) FROM {table}"; BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null); /*foreach (BigQueryRow row in results) * { * Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}"); * }*/ Console.WriteLine(results.ToList().Count); return(true); }
//BigQuery implementation public BigQueryResults BigQueryResults(string aDatasetId, string aTableName, string aQueryString) { try { BigQueryTable table = bigQuery.GetTable(projectId, aDatasetId, aTableName); //string sql = $"SELECT COUNT(*) FROM {table}"; string sql = aQueryString.Replace("TABLE", $"{table}"); BigQueryResults results = bigQuery.ExecuteQuery(sql); return(results); } catch (Exception aExeption) { Console.WriteLine(aExeption.ToString()); return(null); } }
private async Task <BigQueryTable> GetTable() { var client = await bigQueryContextClientResolver.GetClient(projectId, credsPath); var dataset = client.GetDataset(projectId, datasetId); var schema = BigQueryContextTableSchemaBuilder.BuildSchema <T>(); BigQueryTable table = null; await tableAccessLocker.WaitAsync(); try { var partitionProperty = typeof(T).GetProperties() .SingleOrDefault(x => x.GetCustomAttribute <BigQueryPartitionAttribute>() != null); var tableDeclaration = new Table { Schema = schema }; if (partitionProperty != null) { tableDeclaration.TimePartitioning = new TimePartitioning { Field = SnakeCaseConverter.ConvertToSnakeCase(typeof(T).GetProperties() .Single(x => x.GetCustomAttribute <BigQueryPartitionAttribute>() != null).Name) }; } var tables = await dataset.ListTablesAsync().ReadPageAsync(10000); if (tables.Any(x => x.Reference.TableId == tableName)) { table = await dataset.GetOrCreateTableAsync(tableName, tableDeclaration); } else { table = await dataset.CreateTableAsync(tableName, tableDeclaration); } } finally { tableAccessLocker.Release(); } return(table); }
public BigQueryMessageRespository(ILogger <BigQueryMessageRespository> logger) { // Get projectId fron config string googleCredentialsText = File.ReadAllText(Environment.GetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS")); JObject googleCredentials = JObject.Parse(googleCredentialsText); string projectId = googleCredentials["project_id"].ToString(); // Get Dataset Name and Table Name string datasetName = Environment.GetEnvironmentVariable("DATASET_NAME"); string tableName = Environment.GetEnvironmentVariable("MESSAGE_TABLE_NAME"); // Get Table and Client BigQueryClient client = BigQueryClient.Create(projectId); BigQueryDataset dataset = client.GetDataset(datasetName); _logger = logger; _table = dataset.GetTable(tableName); _logger.LogInformation("GCP Information set. projectId: {projectId} datasetName: {datasetName},tableName:{tableName}, ", projectId, datasetName, tableName); }
public async Task <bool> selectNotEqualUsers() { BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); string sql = $"SELECT FullName, Country ,CreatedAt" + $"FROM {table}" + "WHERE Country != 'American Samoa'" + "ORDER BY Country ASC; "; BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null); /*foreach (BigQueryRow row in results) * { * Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}"); * }*/ Console.WriteLine(results.ToList().Count); return(true); }
public async Task <bool> selectReplaceUsers() { BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); string sql = $"SELECT * REPLACE (FirstNumber / @divider AS FirstNumber) FROM {table}"; BigQueryParameter[] parameters = new[] { new BigQueryParameter("divider", BigQueryDbType.Int64, 2) }; BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters); /*foreach (BigQueryRow row in results) * { * Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}"); * }*/ Console.WriteLine(results.ToList().Count); return(true); }
public IActionResult InsertBQData(string username, string userplan, string customerId) { try { string projectId = "task8-imagerecognition-281418"; DateTime now = DateTime.Now; Console.WriteLine("email: " + username); Console.WriteLine("plan: " + userplan); BigQueryClient client = BigQueryClient.Create(projectId); // Create the dataset if it doesn't exist. BigQueryDataset dataset = client.GetOrCreateDataset("mydata"); // Create the table if it doesn't exist. BigQueryTable table = dataset.GetOrCreateTable("customer", new TableSchemaBuilder { { "email", BigQueryDbType.String }, { "subscriptionStarted", BigQueryDbType.Timestamp }, { "planType", BigQueryDbType.String } //BigQueryDbType.Int64 }.Build()); //Insert data into table table.InsertRow(new BigQueryInsertRow { { "email", username }, { "subscriptionStarted", DateTimeOffset.UtcNow.ToUnixTimeSeconds() }, { "planType", userplan } }); Console.WriteLine("Inserted: " + username + " successfully"); return(Redirect("/Home/Main/" + username + "/" + userplan + "/" + customerId + "?Msg=Success")); }// End of try catch (Exception ex) { Console.WriteLine("Error: " + ex.Message); return(Redirect("/Home/Main/" + username + "/" + userplan + "/" + customerId + "?Msg=Failed")); } }// End of insertBQData
public void Update() { string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = _fixture.GenerateTableId(); BigQueryClient.Create(projectId).CreateTable(datasetId, tableId, new TableSchema()); // Snippet: Update(Table, *) BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); // This example modifies the in-memory resource in the BigQueryDataset, // and then applies that change in the server. Alternatively, pass a Dataset // into the Update method. table.Resource.FriendlyName = "Updated table"; BigQueryTable updated = table.Update(); Console.WriteLine($"Updated table friendly name: {updated.Resource.FriendlyName}"); // End snippet Assert.Equal("Updated table", updated.Resource.FriendlyName); }
public async Task <bool> selectQualifyUsers() { BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); string sql = $"SELECT FullName, Country, CreatedAt, AvgFirstNumber, MinFirstNumber, TotalFirstNumber" + "FROM(" + "SELECT FullName, Country, CreatedAt," + "AVG(FirstNumber) OVER(PARTITION BY Country) AS AvgFirstNumber," + "MIN(FirstNumber) OVER(PARTITION BY Country) AS MinFirstNumber," + "SUM(FirstNumber) OVER(PARTITION BY Country) AS TotalFirstNumber" + $"FROM {table})" + "WHERE AvgFirstNumber > 500;"; BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null); /*foreach (BigQueryRow row in results) * { * Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}"); * }*/ Console.WriteLine(results.ToList().Count); return(true); }
public async Task ListRowsAsync() { string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = _fixture.HistoryTableId; // Snippet: ListRowsAsync BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); PagedAsyncEnumerable <TableDataList, BigQueryRow> result = table.ListRowsAsync(); await result.ForEachAsync(row => { DateTime timestamp = (DateTime)row["game_started"]; long level = (long)row["level"]; long score = (long)row["score"]; string player = (string)row["player"]; Console.WriteLine($"{player}: {level}/{score} ({timestamp:yyyy-MM-dd HH:mm:ss})"); }); // End snippet // We set up 7 results in the fixture. Other tests may add more. Assert.True(await result.CountAsync() >= 7); }
public Task InsertRowsAsync(DateTime date, IEnumerable <GoogleBigQueryRow> rows, CancellationToken cancellationToken) { if (rows != null && rows.Count() > 0) { int dateDiff = (date - DateTime.UtcNow.Date).Days; if (dateDiff >= -31 && dateDiff <= 16) { var bigQueryRows = rows.Select(c => BigQueryInsertRowService.GetBigQueryInsertRow(c, dictionaryOfProperties)); return(GetTableAsync(date, cancellationToken) .ContinueWith((tableTask) => { BigQueryTable table = tableTask.Result; return table.InsertRowsAsync(bigQueryRows, new InsertOptions() { AllowUnknownFields = true }, cancellationToken) .ContinueWith((insertRowsTask) => { if (insertRowsTask.IsFaulted) { throw insertRowsTask.Exception.InnerExceptions.First(); } }); }, cancellationToken).Unwrap()); } else { throw new ArgumentOutOfRangeException("BigQuery streamming API don't allow to write data in DAY partioned tabled outside 31 days in the past and 16 days in the future."); } } return(Task.CompletedTask); }
public async Task Initialize() { this.Client = await BigQueryClient.CreateAsync(ProjectId); this.Table = await this.Client.GetTableAsync(DatasetId, TableId); }
public ADataBase(string nomeDaTabela) { this.client = BigQueryClient.Create("hackathon-04", GoogleCredential.FromFile(@"C:\Users\wesley.olivier\Desktop\hackathon\hackathon-04-0c90ce17edf7.json")); this.table = client.GetTable("hackathon-04", "dadosBrutos", nomeDaTabela); }