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 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 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}");
}
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 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 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);
}
public void insertIssues(List <Issue> issues)
{
var table = client.GetTable(datasetName, tableName);
foreach (var issue in issues)
{
var json = JsonConvert.SerializeObject(issue, new ItemStateConverter(typeof(StringEnum <ItemState>)));
Console.WriteLine(json);
}
}
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);
}
// [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,
new QueryOptions {
DestinationTable = destination
});
// Wait for the job to complete.
job.GetQueryResults();
}
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);
}
// [END sync_query]
// [START sync_query_legacy_sql]
public BigQueryResults LegacySqlSyncQuery(string projectId, string datasetId,
string tableId, string query, double timeoutMs, BigQueryClient client)
{
var table = client.GetTable(projectId, datasetId, tableId);
BigQueryJob job = client.CreateQueryJob(query,
new QueryOptions {
UseLegacySql = true
});
// Get the query result, waiting for the timespan specified in milliseconds.
BigQueryResults result = client.GetQueryResults(job.Reference.JobId,
new GetQueryResultsOptions {
Timeout = TimeSpan.FromMilliseconds(timeoutMs)
});
return(result);
}
// [END sync_query_legacy_sql]
// [START async_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,
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));
}
//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);
}
}
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 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);
}
// [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,
new CreateQueryJobOptions { DestinationTable = destination });
// Wait for the job to complete.
job.PollQueryUntilCompleted();
}
// [END sync_query_legacy_sql]
// [START async_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,
new CreateQueryJobOptions { 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 sync_query]
// [START sync_query_legacy_sql]
public BigQueryResults LegacySqlSyncQuery(string projectId, string datasetId,
string tableId, string query, double timeoutMs, BigQueryClient client)
{
var table = client.GetTable(projectId, datasetId, tableId);
BigQueryJob job = client.CreateQueryJob(query,
new CreateQueryJobOptions { UseLegacySql = true });
// Get the query result, waiting for the timespan specified in milliseconds.
BigQueryResults result = client.GetQueryResults(job.Reference.JobId,
new GetQueryResultsOptions { Timeout = TimeSpan.FromMilliseconds(timeoutMs) });
return result;
}
public void TestLegacySqlAsyncQuery()
{
string projectId = "bigquery-public-data";
string datasetId = "samples";
string tableId = "shakespeare";
var table = _client.GetTable(projectId, datasetId, tableId);
string query = $"SELECT TOP(corpus, 42) as title, COUNT(*) as unique_words FROM [{table.FullyQualifiedId}]";
BigQueryResults results = LegacySqlAsyncQuery(
projectId, datasetId, tableId, query, _client);
Assert.True(results.Count() > 0);
}
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);
}
public void TestSyncQuery()
{
string projectId = "bigquery-public-data";
string datasetId = "samples";
string tableId = "shakespeare";
var table = _client.GetTable(projectId, datasetId, tableId);
string query = $@"SELECT corpus AS title, COUNT(*) AS unique_words FROM {table}
GROUP BY title ORDER BY unique_words DESC LIMIT 42";
BigQueryResults results = SyncQuery(projectId, datasetId, tableId, query, 10000, _client);
Assert.True(results.Count() > 0);
}
public string GetTableFullyQualifiedId(string datasetId, string tableId)
{
return(_bigQuery.GetTable(_projectId, datasetId, tableId).FullyQualifiedId);
}
