/// <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);
}