public void Insert()
{
string projectId = _fixture.ProjectId;
string datasetId = _fixture.GameDatasetId;
string tableId = _fixture.HistoryTableId;
BigqueryTable table = BigqueryClient.Create(projectId).GetTable(datasetId, tableId);
int rowsBefore = table.ListRows().Rows.Count();
// Snippet: Insert(string,string,*)
BigqueryClient client = BigqueryClient.Create(projectId);
// The insert ID is optional, but can avoid duplicate data
// when retrying inserts.
InsertRow row1 = new InsertRow("row1")
{
{ "player", "Jane" },
{ "level", 3 },
{ "score", 3600 },
{ "game_started", DateTime.UtcNow }
};
InsertRow row2 = new InsertRow("row2")
{
{ "player", "Jeff" },
{ "level", 2 },
{ "score", 2000 },
{ "game_started", DateTime.UtcNow }
};
client.Insert(datasetId, tableId, row1, row2);
// End snippet
int rowsAfter = table.ListRows().Rows.Count();
Assert.Equal(rowsBefore + 2, rowsAfter);
}
// [END 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();
}
}
public void GetTable_FromIds()
{
var client = BigqueryClient.Create(PublicDatasetsProject);
var table = client.GetTable(PublicDatasetsDataset, "wikipedia");
Assert.Equal("bigquery-public-data.samples.wikipedia", table.FullyQualifiedId);
}
public void Insert_RepeatedField()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.ComplexTypesTableId);
var guid = Guid.NewGuid().ToString();
var row = new InsertRow
{
["guid"] = guid,
// The null element will be ignored here (at the server side)
["tags"] = new[] { "a", null, "b" }
};
table.Insert(row);
// We know the format of Guid.ToString() is harmless. More care needed for arbitrary strings, of course!
var queryResults = client.ExecuteQuery($"SELECT guid, tags FROM {table} WHERE guid='{guid}' ORDER BY TAGS").Rows
.Select(r => new { Guid = (string)r["guid"], Tag = (string)r["tags"] })
.ToList();
var expectedResults = new[]
{
new { Guid = guid, Tag = "a" },
new { Guid = guid, Tag = "b" }
};
Assert.Equal(expectedResults, queryResults);
}
// Import CSV file from Google Storage bucket into existing Google BigQuery data table
private void ImportFileFromStorageToBigQuery(BigqueryClient client, string projectName,
string bucketName, string fileName, string dataSetName, string tableName)
{
StorageClient gcsClient = StorageClient.Create(_GoogleAPICredential);
using (var stream = new MemoryStream())
{
gcsClient.DownloadObject(bucketName, fileName, stream);
// This uploads data to an existing table. If the upload will create a new table
// or if the schema in the CSV 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 = null;
try
{
job = client.UploadCsv(dataSetName, tableName, null, stream);
}
catch (Exception e)
{
string m = e.Message;
}
// 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.
try
{
job.Poll();
} catch (Exception e) {
string m = e.Message;
}
}
}
public void Insert_RecordField()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.ComplexTypesTableId);
var guid = Guid.NewGuid().ToString();
var row = new InsertRow
{
["guid"] = guid,
["position"] = new InsertRow {
["x"] = 10L, ["y"] = 20L
}
};
table.Insert(row);
// We know the format of Guid.ToString() is harmless. More care needed for arbitrary strings, of course!
var queryResults = client.ExecuteQuery($"SELECT guid, position.x, position.y FROM {table} WHERE guid='{guid}'").Rows
.Select(r => new { Guid = (string)r["guid"], X = (long)r["position_x"], Y = (long)r["position_y"] })
.ToList();
var expectedResults = new[]
{
new { Guid = guid, X = 10L, Y = 20L }
};
Assert.Equal(expectedResults, queryResults);
}
public void Insert_RepeatedRecordField()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.ComplexTypesTableId);
var guid = Guid.NewGuid().ToString();
var row = new InsertRow
{
["guid"] = guid,
["names"] = new[] {
new InsertRow {
["first"] = "a", ["last"] = "b"
},
new InsertRow {
["first"] = "x", ["last"] = "y"
}
}
};
table.Insert(row);
var command = new BigqueryCommand($"SELECT guid, name.first, name.last FROM {table}, UNNEST(names) AS name WHERE guid=@guid ORDER BY name.first")
{
Parameters = { { "guid", BigqueryParameterType.String, guid } }
};
var queryResults = WaitForRows(client, command)
.Select(r => new { Guid = (string)r["guid"], FirstName = (string)r["first"], LastName = (string)r["last"] })
.ToList();
var expectedResults = new[]
{
new { Guid = guid, FirstName = "a", LastName = "b" },
new { Guid = guid, FirstName = "x", LastName = "y" }
};
Assert.Equal(expectedResults, queryResults);
}
public void AsynchronousPermanentQuery()
{
// We create the client using our user, but then access a dataset in a public data
// project. We can't run a query "as" the public data project.
var projectId = _fixture.ProjectId;
var client = BigqueryClient.Create(projectId);
var table = client.GetTable(PublicDatasetsProject, PublicDatasetsDataset, ShakespeareTable);
var userDataset = client.GetDataset(_fixture.DatasetId);
var sql = $"SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words FROM {table}";
var destinationTable = userDataset.GetTableReference(_fixture.CreateTableId());
var job = client.CreateQueryJob(sql, new CreateQueryJobOptions {
DestinationTable = destinationTable
});
var rows = job.GetQueryResults().Rows.ToList();
Assert.Equal(10, rows.Count);
Assert.Equal("hamlet", (string)rows[0][0]);
Assert.Equal(5318, (long)rows[0][1]);
// Read the table again later - synchronously this time
table = client.GetTable(destinationTable);
rows = client.ExecuteQuery($"SELECT * FROM {table}").Rows.ToList();
Assert.Equal(10, rows.Count);
Assert.Equal("hamlet", (string)rows[0][0]);
Assert.Equal(5318, (long)rows[0][1]);
}
public void Insert_RecordField()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.ComplexTypesTableId);
var guid = Guid.NewGuid().ToString();
var row = new InsertRow
{
["guid"] = guid,
["position"] = new InsertRow {
["x"] = 10L, ["y"] = 20L
}
};
table.Insert(row);
var command = new BigqueryCommand($"SELECT guid, position.x, position.y FROM {table} WHERE guid=@guid")
{
Parameters = { { "guid", BigqueryParameterType.String, guid } }
};
var queryResults = WaitForRows(client, command)
.Select(r => new { Guid = (string)r["guid"], X = (long)r["x"], Y = (long)r["y"] })
.ToList();
var expectedResults = new[]
{
new { Guid = guid, X = 10L, Y = 20L }
};
Assert.Equal(expectedResults, queryResults);
}
public void Insert_RepeatedField()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.ComplexTypesTableId);
var guid = Guid.NewGuid().ToString();
var row = new InsertRow
{
["guid"] = guid,
// The null element will be ignored here (at the server side)
["tags"] = new[] { "a", null, "b" }
};
table.Insert(row);
var command = new BigqueryCommand($"SELECT guid, tag FROM {table}, UNNEST(tags) AS tag WHERE guid=@guid ORDER BY tag")
{
Parameters = { { "guid", BigqueryParameterType.String, guid } }
};
var queryResults = WaitForRows(client, command)
.Select(r => new { Guid = (string)r["guid"], Tag = (string)r["tag"] })
.ToList();
var expectedResults = new[]
{
new { Guid = guid, Tag = "a" },
new { Guid = guid, Tag = "b" }
};
Assert.Equal(expectedResults, queryResults);
}
public void UploadCsv()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
string[] csvRows =
{
"Name,GameStarted,Score",
"Ben,2014-08-19T12:41:35.220Z,85",
"Lucy,2014-08-20T12:41:35.220Z,130",
"Rohit,2014-08-21T12:41:35.220Z,90"
};
var bytes = Encoding.UTF8.GetBytes(string.Join("\n", csvRows));
var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
var beforeRowCount = table.ListRows().Count();
var job = table.UploadCsv(new MemoryStream(bytes), new UploadCsvOptions {
SkipLeadingRows = 1
});
var result = job.PollUntilCompleted();
Assert.Null(result.Status.ErrorResult);
var afterRows = table.ListRows().ToList();
Assert.Equal(beforeRowCount + 3, afterRows.Count);
var ben = afterRows.Single(row => (string)row["player"] == "Ben");
Assert.Equal(85, (long)ben["score"]);
Assert.Equal(new DateTime(2014, 8, 19, 12, 41, 35, 220, DateTimeKind.Utc), (DateTime)ben["gameStarted"]);
}
public void NullParameter()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
var parameter = new BigqueryParameter("player", BigqueryParameterType.String, "Angela");
var command = new BigqueryCommand($"SELECT score FROM {table} WHERE player=@player")
{
Parameters = { parameter }
};
var resultSet = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(5);
Assert.Equal(1, resultSet.Rows.Count);
Assert.Equal(95, (long)resultSet.Rows[0]["score"]);
// SQL rules: nothing equals null
parameter.Value = null;
resultSet = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(5);
Assert.Equal(0, resultSet.Rows.Count);
// But we should be able to find the null value this way.
command.Sql = $"SELECT score FROM {table} WHERE player=@player OR (player IS NULL AND @player IS NULL)";
resultSet = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(5);
Assert.Equal(1, resultSet.Rows.Count);
Assert.Equal(1, (long)resultSet.Rows[0]["score"]);
}
private static int Main(string[] args)
{
if (args.Length != 1)
{
Console.Error.WriteLine("Specify the project ID as the only command line argument");
return(1);
}
string projectId = args[0];
var client = BigqueryClient.Create(projectId);
var datasets = client.ListDatasets().ToList();
foreach (var dataset in datasets.Where(IsTestDataset))
{
var id = dataset.Reference.DatasetId;
try
{
dataset.Delete(new DeleteDatasetOptions {
DeleteContents = true
});
Console.WriteLine($"Deleted {id}");
}
catch (GoogleApiException e)
{
Console.WriteLine($"Failed to delete {id}: {e.Message}");
}
}
return(0);
}
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);
}
public void InsertionOverview()
{
string projectId = _fixture.ProjectId;
// Sample: InsertOverview
var client = BigqueryClient.Create(projectId);
// Create the dataset if it doesn't exist.
var dataset = client.GetOrCreateDataset("mydata");
// Create the table if it doesn't exist.
var table = dataset.GetOrCreateTable("scores", new TableSchemaBuilder
{
{ "player", BigqueryDbType.String },
{ "gameStarted", BigqueryDbType.Timestamp },
{ "score", BigqueryDbType.Integer }
}.Build());
// Insert a single row. There are many other ways of inserting
// data into a table.
table.Insert(new InsertRow
{
{ "player", "Bob" },
{ "score", 85 },
{ "gameStarted", new DateTime(2000, 1, 14, 10, 30, 0, DateTimeKind.Utc) }
});
// End sample
}
public void UploadJson()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
// We use ' instead of " in the JSON to make it easier to write the string literals, then fix it up.
var jsonRows = new[]
{
"{ 'player': 'UploadJsonTest1', 'score': 90, 'GameStarted': '2015-01-01T00:00:00.000Z' }",
"{ 'player': 'UploadJsonTest2', 'score': 100, 'GameStarted': '2014-01-01T01:00:00.000Z' }"
}.Select(x => x.Replace('\'', '"'));
var bytes = Encoding.UTF8.GetBytes(string.Join("\n", jsonRows));
var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
var beforeRowCount = table.ListRows().Count();
var job = table.UploadJson(new MemoryStream(bytes));
var result = job.PollUntilCompleted();
Assert.Null(result.Status.ErrorResult);
var afterRows = table.ListRows().ToList();
Assert.Equal(beforeRowCount + 2, afterRows.Count);
var sql = $"SELECT player, score FROM {table} WHERE STARTS_WITH(player, 'UploadJsonTest') ORDER BY player";
var rows = client.ExecuteQuery(sql).GetRows().ToList();
Assert.Equal(2, rows.Count);
Assert.Equal("UploadJsonTest1", (string)rows[0]["player"]);
Assert.Equal("UploadJsonTest2", (string)rows[1]["player"]);
Assert.Equal(90L, (long)rows[0]["score"]);
Assert.Equal(100L, (long)rows[1]["score"]);
}
public void ExportCsv()
{
// TODO: Make this simpler in the wrapper
var projectId = _fixture.ProjectId;
var datasetId = _fixture.GameDatasetId;
var historyTableId = _fixture.HistoryTableId;
string bucket = "bigquerysnippets-" + Guid.NewGuid().ToString().ToLowerInvariant();
string objectName = "table.csv";
if (!WaitForStreamingBufferToEmpty(historyTableId))
{
Console.WriteLine("Streaming buffer not empty after 30 seconds; not performing export");
return;
}
// Sample: ExportCsv
BigqueryClient client = BigqueryClient.Create(projectId);
// Create a storage bucket; in normal use it's likely that one would exist already.
StorageClient storageClient = StorageClient.Create();
storageClient.CreateBucket(projectId, bucket);
string destinationUri = $"gs://{bucket}/{objectName}";
Job job = client.Service.Jobs.Insert(new Job
{
Configuration = new JobConfiguration
{
Extract = new JobConfigurationExtract
{
DestinationFormat = "CSV",
DestinationUris = new[] { destinationUri },
SourceTable = client.GetTableReference(datasetId, historyTableId)
}
}
}, projectId).Execute();
// Wait until the export has finished.
var result = client.PollJob(job.JobReference);
// If there are any errors, display them *then* fail.
if (result.Status.ErrorResult != null)
{
foreach (var error in result.Status.Errors)
{
Console.WriteLine(error.Message);
}
}
Assert.Null(result.Status.ErrorResult);
MemoryStream stream = new MemoryStream();
storageClient.DownloadObject(bucket, objectName, stream);
Console.WriteLine(Encoding.UTF8.GetString(stream.ToArray()));
// End sample
storageClient.DeleteObject(bucket, objectName);
storageClient.DeleteBucket(bucket);
}
private BigqueryRow GetSingleRow(BigqueryCommand command)
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var results = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(10);
Assert.Equal(1, results.Rows.Count);
return(results.Rows[0]);
}
public void GetTable_ViaDataset()
{
var client = BigqueryClient.Create(PublicDatasetsProject);
var dataset = client.GetDataset(PublicDatasetsDataset);
var table = dataset.GetTable("wikipedia");
Assert.Equal("bigquery-public-data:samples.wikipedia", table.FullyQualifiedId);
}
private void CreateData()
{
var client = BigqueryClient.Create(ProjectId);
var dataset = client.CreateDataset(DatasetId);
CreateHighScoreTable(dataset);
CreatePeopleTable(dataset);
CreateComplexTypesTable(dataset);
}
public void EmptyTable()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var schema = new TableSchemaBuilder {
{ "name", BigqueryDbType.String }
}.Build();
var table = client.CreateTable(_fixture.DatasetId, _fixture.CreateTableId(), schema);
var rows = table.ListRows().ToList();
Assert.Empty(rows);
}
public BigquerySnippetFixture()
{
ProjectId = Environment.GetEnvironmentVariable(ProjectEnvironmentVariable);
if (string.IsNullOrEmpty(ProjectId))
{
throw new InvalidOperationException(
$"Please set the {ProjectEnvironmentVariable} environment variable before running tests");
}
Client = BigqueryClient.Create(ProjectId);
GameDatasetId = CreateGameDataset();
}
public void IntegerArrayParameter()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var command = new BigqueryCommand("SELECT value FROM UNNEST([0, 1, 2, 3, 4]) AS value WHERE value IN UNNEST(@p)")
{
Parameters = { { "p", BigqueryParameterType.Array, new[] { 1, 3, 5 } } }
};
var results = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(10);
Assert.Equal(new[] { 1L, 3L }, results.Rows.Select(r => (long)r["value"]));
}
public void EmptyQueryResults_CreateQueryJob()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
var queryResults = client.CreateQueryJob($"SELECT * FROM {table} WHERE score < 0")
.PollQueryUntilCompleted()
.GetRows()
.ToList();
Assert.Empty(queryResults);
}
public void Insert_BadData()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.HighScoreTableId);
var row = new InsertRow {
{ "noSuchField", 10 }
};
Assert.Throws <GoogleApiException>(() => table.Insert(row));
}
// [END create_dataset]
// [START 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.Integer }
}.Build();
// Create the table if it doesn't exist.
BigqueryTable table = dataset.GetOrCreateTable(tableId, schema);
}
public void PopulateTable(
string query, string datasetId, string newTableId, BigqueryClient client)
{
var destination = client.GetTableReference(datasetId, newTableId);
BigqueryJob job = client.CreateQueryJob(query,
new CreateQueryJobOptions {
DestinationTable = destination
});
// Wait for the job to complete.
job.PollQueryUntilCompleted();
}
private bool WaitForStreamingBufferToEmpty(string tableId)
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var table = client.GetTable(_fixture.GameDatasetId, tableId);
for (int i = 0; i < 3 && table.Resource.StreamingBuffer != null; i++)
{
Thread.Sleep(TimeSpan.FromSeconds(10));
table = client.GetTable(table.Reference);
}
return(table.Resource.StreamingBuffer == null);
}
public void UploadCsv()
{
string projectId = _fixture.ProjectId;
string datasetId = _fixture.GameDatasetId;
string tableId = _fixture.HistoryTableId;
BigqueryTable table = BigqueryClient.Create(projectId).GetTable(datasetId, tableId);
int rowsBefore = table.ListRows().Rows.Count();
// Snippet: UploadCsv(*,*,*,*,*)
BigqueryClient client = BigqueryClient.Create(projectId);
string[] csvRows =
{
"player,score,level,game_started",
"Tim,5000,3,2014-08-19T12:41:35.220Z",
"Holly,6000,4,2014-08-03T08:45:35.123Z",
"Jane,2402,1,2015-01-20T10:13:35.059Z"
};
// Normally we'd be uploading from a file or similar. Any readable stream can be used.
var stream = new MemoryStream(Encoding.UTF8.GetBytes(string.Join("\n", csvRows)));
// This example uploads data to an existing table. If the upload will create a new table
// or if the schema in the CSV isn't identical to the schema in the table (for example if the
// columns are in a different order), create a schema to pass into the call.
TableSchema schema = null;
BigqueryJob job = client.UploadCsv(datasetId, tableId, schema, stream,
// Our sample data has a header row, so we need to skip it.
new UploadCsvOptions {
SkipLeadingRows = 1
});
// Use the job to find out when the data has finished being inserted into the table,
// report errors etc.
// End snippet
var result = job.Poll();
// If there are any errors, display them *then* fail.
if (result.Status.ErrorResult != null)
{
foreach (var error in result.Status.Errors)
{
Console.WriteLine(error.Message);
}
}
Assert.Null(result.Status.ErrorResult);
int rowsAfter = table.ListRows().Rows.Count();
Assert.Equal(rowsBefore + 3, rowsAfter);
}
public BigqueryTest()
{
// [START create_bigquery_client]
// 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 create_bigquery_client]
}
