public void ExecuteQuery()
{
var projectId = _fixture.ProjectId;
var datasetId = _fixture.GameDatasetId;
var historyTableId = _fixture.HistoryTableId;
// Snippet: ExecuteQuery
BigqueryClient client = BigqueryClient.Create(projectId);
BigqueryTable table = client.GetTable(datasetId, historyTableId);
BigqueryResult result = client.ExecuteQuery(
$@"SELECT player, MAX(score) AS score
FROM {table}
GROUP BY player
ORDER BY score DESC");
foreach (var row in result.Rows)
{
Console.WriteLine($"{row["player"]}: {row["score"]}");
}
// End snippet
var players = result.Rows.Select(r => (string)r["player"]).ToList();
Assert.Contains("Ben", players);
Assert.Contains("Nadia", players);
Assert.Contains("Tim", players);
}
// [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 BigqueryQueryJob 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 BigqueryQueryJob 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.
BigqueryQueryJob result = client.GetQueryResults(job.Reference.JobId,
new GetQueryResultsOptions {
Timeout = TimeSpan.FromMilliseconds(timeoutMs)
});
return(result);
}
public void CreateQueryJob()
{
var projectId = _fixture.ProjectId;
var datasetId = _fixture.GameDatasetId;
var historyTableId = _fixture.HistoryTableId;
var queryTableId = Guid.NewGuid().ToString().Replace('-', '_');
// Snippet: CreateQueryJob(*,*)
BigqueryClient client = BigqueryClient.Create(projectId);
BigqueryTable table = client.GetTable(datasetId, historyTableId);
TableReference destination = client.GetTableReference(datasetId, queryTableId);
// If the destination table is not specified, the results will be stored in
// a temporary table.
BigqueryJob job = client.CreateQueryJob(
$@"SELECT player, MAX(score) AS score
FROM {table}
GROUP BY player
ORDER BY score DESC",
new CreateQueryJobOptions {
DestinationTable = destination
});
// Wait for the job to complete.
job.Poll();
// Then we can fetch the results, either via the job or by accessing
// the destination table.
BigqueryResult result = client.GetQueryResults(job.Reference);
foreach (var row in result.Rows)
{
Console.WriteLine($"{row["player"]}: {row["score"]}");
}
// End snippet
var players = result.Rows.Select(r => (string)r["player"]).ToList();
Assert.Contains("Ben", players);
Assert.Contains("Nadia", players);
Assert.Contains("Tim", players);
}
public void ListRows()
{
string projectId = _fixture.ProjectId;
string datasetId = _fixture.GameDatasetId;
string tableId = _fixture.HistoryTableId;
// Snippet: ListRows
BigqueryClient client = BigqueryClient.Create(projectId);
BigqueryTable table = client.GetTable(datasetId, tableId);
BigqueryResult result = table.ListRows();
foreach (BigqueryResult.Row row in result.Rows)
{
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(result.Rows.Count() >= 7);
}
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);
IPagedAsyncEnumerable <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.Count() >= 7);
}
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";
BigqueryQueryJob results = SyncQuery(projectId, datasetId, tableId, query, 10000, _client);
Assert.True(results.GetRows().Count() > 0);
}
public void TestSyncQuery()
{
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}";
BigqueryResult results = SyncQuery(projectId, datasetId, tableId, query, 10000, _client);
Assert.True(results.Rows.Count() > 0);
}