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 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 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"]); }
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 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); }
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 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); }
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 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 GetTable_FromIds() { var client = BigqueryClient.Create(PublicDatasetsProject); var table = client.GetTable(PublicDatasetsDataset, "wikipedia"); Assert.Equal("bigquery-public-data.samples.wikipedia", table.FullyQualifiedId); }
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 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 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 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 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 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)); }
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"])); }
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] }
public void EmptyQueryResults_GetResultSet() { var client = BigqueryClient.Create(_fixture.ProjectId); var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId); // Deliberately overfetch var queryJob = client.CreateQueryJob($"SELECT * FROM {table} WHERE score < 0") .PollQueryUntilCompleted(new GetQueryResultsOptions { PageSize = 100 }); var resultSet = queryJob.GetResultSet(10); Assert.Empty(resultSet.Rows); }
public void Insert_BadData_IgnoreBadData() { var client = BigqueryClient.Create(_fixture.ProjectId); var dataset = client.GetDataset(_fixture.DatasetId); var table = dataset.GetTable(_fixture.HighScoreTableId); var row = new InsertRow { { "noSuchField", 10 } }; table.Insert(row, new InsertOptions { AllowUnknownFields = true }); }
public void MultiRequestQueryStreaming() { var client = BigqueryClient.Create(_fixture.ProjectId); var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId); var queryResults = client.ExecuteQuery($"SELECT * FROM {table}", new ExecuteQueryOptions { PageSize = 1 }) .PollUntilCompleted() .GetRows() .ToList(); Assert.True(queryResults.Count >= 2); }
public void CopyTable() { // TODO: Make this simpler in the wrapper var projectId = _fixture.ProjectId; var datasetId = _fixture.GameDatasetId; var historyTableId = _fixture.HistoryTableId; var destinationTableId = Guid.NewGuid().ToString().Replace('-', '_'); if (!WaitForStreamingBufferToEmpty(historyTableId)) { Console.WriteLine("Streaming buffer not empty after 30 seconds; not performing export"); return; } // Sample: CopyTable BigqueryClient client = BigqueryClient.Create(projectId); Job job = client.Service.Jobs.Insert(new Job { Configuration = new JobConfiguration { Copy = new JobConfigurationTableCopy { DestinationTable = client.GetTableReference(datasetId, destinationTableId), SourceTable = client.GetTableReference(datasetId, historyTableId) } } }, projectId).Execute(); // Wait until the copy has finished. client.PollJob(job.JobReference); // Now list its rows BigqueryResult result = client.ListRows(datasetId, destinationTableId); 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 sample var originalRows = client.ListRows(datasetId, historyTableId).Rows.Count(); var copiedRows = result.Rows.Count(); Assert.Equal(originalRows, copiedRows); }