public void ShouldBuildWithName() { // Arrange ITableSchemaBuilder builder = new TableSchemaBuilder(); // Act TableSchema schema = builder.WithName("name").Build(); // Assert schema.name.ShouldBe("name"); }
// [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.Int64 } }.Build(); // Create the table if it doesn't exist. BigQueryTable table = dataset.GetOrCreateTable(tableId, schema); }
public void ShouldBuildWithKeyField() { // Arrange ITableSchemaBuilder builder = new TableSchemaBuilder(); // Act TableSchema schema = builder.WithKeyField("index").Build(); // Assert FieldSchema field = schema.field.First(); field.is_primary_key.ShouldBe(true); field.type.ShouldBe("id"); field.name.ShouldBe("index"); }
public void ShouldBuildWithField() { // Arrange ITableSchemaBuilder builder = new TableSchemaBuilder(); // Act TableSchema schema = builder.WithField("name", true, 123).Build(); // Assert FieldSchema field = schema.Field.First(); field.IsPrimaryKey.ShouldBe(null); field.Required.ShouldBe(true); field.Name.ShouldBe("name"); field.DefaultValue.ShouldBe("123"); }
public void ShouldBuildWithFieldsFromRecord() { // Arrange ITableSchemaBuilder builder = new TableSchemaBuilder(); // Act TableSchema schema = builder.WithFieldsFrom<Record>().Build(); // Assert schema.field.Count.ShouldBe(4); schema.field.Single(x => x.name == "id").type.ShouldBe("id"); schema.field.Single(x => x.name == "name").type.ShouldBe("string"); schema.field.Single(x => x.name == "age").type.ShouldBe("integer"); schema.field.Single(x => x.name == "active").type.ShouldBe("boolean"); }
public void ShouldBuildWithField() { // Arrange ITableSchemaBuilder builder = new TableSchemaBuilder(); // Act TableSchema schema = builder.WithField("name", true, 123).Build(); // Assert FieldSchema field = schema.field.First(); field.is_primary_key.ShouldBe(null); field.required.ShouldBe(true); field.name.ShouldBe("name"); field.default_value.ShouldBe("123"); }
public static TableSchemaBuilder GetSchemaBuilder(Type schemaType, string indent = "") { var schemaBuilder = new TableSchemaBuilder(); foreach (var propertyItem in schemaType.GetProperties()) { if (IgnoreProperty(propertyItem.GetCustomAttribute <BigQuerySchemaAttribute>())) { continue; } if (IsEnumerable(propertyItem.PropertyType)) { var elementType = GetElementsType(propertyItem); if (IsClass(elementType)) { WritePropertyMessage(propertyItem, indent, "IEnumerableClass"); var propertySchemaBuilder = GetSchemaBuilder(propertyItem.PropertyType.GetGenericArguments().FirstOrDefault(), indent.Insert(0, _spaces)); schemaBuilder.Add(NewTableFieldSchema(propertyItem.Name, "RECORD", BigQueryFieldMode.Repeated.ToString(), propertySchemaBuilder.Build())); } else { WritePropertyMessage(propertyItem, indent, "IEnumerableType"); schemaBuilder.Add(NewTableFieldSchema(propertyItem.Name, elementType.ToBigQueryDbType(), BigQueryFieldMode.Repeated.ToString())); } } else if (IsClass(propertyItem.PropertyType)) { WritePropertyMessage(propertyItem, indent, "Class"); var propertySchemaBuilder = GetSchemaBuilder(propertyItem.PropertyType, indent.Insert(0, _spaces)); schemaBuilder.Add(NewTableFieldSchema(propertyItem.Name, "RECORD", BigQueryFieldMode.Nullable.ToString(), propertySchemaBuilder.Build())); } else { WritePropertyMessage(propertyItem, indent, "Type"); schemaBuilder.Add(NewTableFieldSchema(propertyItem.Name, propertyItem.PropertyType.ToBigQueryDbType(), BigQueryFieldMode.Nullable.ToString())); } } return(schemaBuilder); }
// Creates a new empty table in Google BigQuery public void CreateBigQueryTable(string projectName, string dataSetName, string tableName) { if (_DataTypeMap == null) { throw new Exception("DataTypeMap can not be null. Call UploadTableToStorage() or MapColumnTypes() method first"); } BigqueryClient client = BigqueryClient.Create(projectName, _GoogleAPICredential); // Build the schema with Google's schema object and our DataTypeMap TableSchemaBuilder sBuilder = new TableSchemaBuilder(); for (int i = 0; i < _DataTypeMap.Keys.Count; i++) { sBuilder.Add(_DataTypeMap[i].ColumnName, _DataTypeMap[i].BQColumnType); } // Create the dataset if it doesn't exist. BigqueryDataset dataset = client.GetOrCreateDataset(dataSetName); BigqueryTable table = dataset.GetOrCreateTable(tableName, sBuilder.Build()); }
public void AddNested() { var builder = new TableSchemaBuilder { { "outer", new TableSchemaBuilder { { "inner", BigQueryDbType.String } }, BigQueryFieldMode.Required, "Outer field" } }; var schema = builder.Build(); var field = schema.Fields[0]; Assert.Equal("outer", field.Name); Assert.Equal("RECORD", field.Type); Assert.Equal("REQUIRED", field.Mode); Assert.Equal("Outer field", field.Description); var nestedField = field.Fields[0]; Assert.Equal("inner", nestedField.Name); Assert.Equal("STRING", nestedField.Type); Assert.Equal("NULLABLE", nestedField.Mode); }
/// <summary> /// Imports data from a CSV source /// </summary> /// <param name="datasetId">Dataset identifier.</param> /// <param name="tableId">Table identifier.</param> /// <param name="client">A BigQuery client.</param> public static void LoadTableFromCSV(string datasetId, string tableId, BigQueryClient client) { // [START bigquery_load_table_gcs_csv] 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 jobOptions = new CreateLoadJobOptions() { // The source format defaults to CSV; line below is optional. SourceFormat = FileFormat.Csv, SkipLeadingRows = 1 }; var loadJob = client.CreateLoadJob(gcsURI, dataset.GetTableReference(tableId), schema, jobOptions); loadJob.PollUntilCompleted(); // [END bigquery_load_table_gcs_csv] }
public void CreateTable() { string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = Guid.NewGuid().ToString().Replace("-", "_"); // Snippet: CreateTable(string,string,*,*) BigqueryClient client = BigqueryClient.Create(projectId); TableSchema schema = new TableSchemaBuilder { { "from_player", BigqueryDbType.String }, { "to_player", BigqueryDbType.String }, { "message", BigqueryDbType.String } }.Build(); BigqueryTable table = client.CreateTable(datasetId, tableId, schema); // Now populate the table with data... // End snippet var tables = client.ListTables(datasetId); var ids = tables.Select(ds => ds.Reference.TableId).ToList(); Assert.Contains(tableId, ids); }
static void LoadDataToBigQuery() { var gcsUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"; var client = BigQueryClient.Create(PROJECT_ID); var dataset = client.GetOrCreateDataset("us_states_dataset"); var schema = new TableSchemaBuilder { { "name", BigQueryDbType.String }, { "post_abbr", BigQueryDbType.String } }.Build(); var jobOptions = new CreateLoadJobOptions { SourceFormat = FileFormat.NewlineDelimitedJson }; var table = dataset.GetTableReference("us_states_table"); var loadJob = client.CreateLoadJob(gcsUri, table, schema, jobOptions); loadJob.PollUntilCompleted(); loadJob.ThrowOnAnyError(); Console.WriteLine("Json file loaded to BigQuery"); }
public void UploadCsv_PartitionedByField() { var client = BigQueryClient.Create(_fixture.ProjectId); string tableId = _fixture.CreateTableId(); var tableReference = client.GetTableReference(_fixture.DatasetId, _fixture.CreateTableId()); 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)); TableSchema schema = new TableSchemaBuilder { { "Name", BigQueryDbType.String }, { "GameStarted", BigQueryDbType.Timestamp }, { "Score", BigQueryDbType.Int64 }, }.Build(); var options = new UploadCsvOptions { SkipLeadingRows = 1, TimePartitioning = TimePartition.CreateDailyPartitioning(expiration: null, "GameStarted") }; var job = client.UploadCsv(_fixture.DatasetId, tableId, schema, new MemoryStream(bytes), options); var result = job.PollUntilCompleted(); Assert.Null(result.Status.ErrorResult); var table = client.GetTable(_fixture.DatasetId, tableId); Assert.Equal("GameStarted", table.Resource.TimePartitioning.Field); }
public void UploadAvro() { var client = BigQueryClient.Create(_fixture.ProjectId); var tableId = _fixture.CreateTableId(); var tableReference = client.GetTableReference(_fixture.DatasetId, tableId); var schema = new TableSchemaBuilder { { "re", BigQueryDbType.Int64 }, { "im", BigQueryDbType.Int64 } }.Build(); var typeInfo = typeof(UploadTest).GetTypeInfo(); string resourceName = typeInfo.Namespace + ".one_complex.avro"; using (var stream = typeInfo.Assembly.GetManifestResourceStream(resourceName)) { var job = client.UploadAvro(tableReference, schema, stream); job.PollUntilCompleted(); } var rows = client.GetTable(tableReference).ListRows().ToList(); Assert.Equal(1, rows.Count); Assert.Equal(100, (long)rows[0]["re"]); Assert.Equal(200, (long)rows[0]["im"]); }
private string CreateGameDataset() { string id = CreateDataset(); BigqueryDataset game = Client.GetDataset(id); var historySchema = new TableSchemaBuilder { { "player", BigqueryDbType.String }, { "score", BigqueryDbType.Integer }, { "level", BigqueryDbType.Integer }, { "game_started", BigqueryDbType.Timestamp } }.Build(); var historyTable = game.CreateTable(HistoryTableId, historySchema); historyTable.Insert( CreateHistoryRow("Tim", 503, 1, "2015-05-03T23:01:05"), CreateHistoryRow("Nadia", 450, 1, "2013-05-06T10:05:07"), CreateHistoryRow("Nadia", 1320, 2, "2013-06-01T15:02:07"), CreateHistoryRow("Ben", 300, 1, "2014-01-30T12:53:35"), CreateHistoryRow("Tim", 5310, 3, "2014-06-28T10:32:15"), CreateHistoryRow("Tim", 2000, 2, "2014-07-01T08:12:25"), CreateHistoryRow("Nadia", 8310, 5, "2015-03-20T14:55:10") ); return(id); }
public void Add_InvalidName() { var builder = new TableSchemaBuilder(); Assert.Throws <ArgumentException>(() => builder.Add("123", BigQueryDbType.Int64)); }
[InlineData("bc\u00e9")] // e acute public void ValidateFieldName_Invalid(string name) { Assert.Throws <ArgumentException>(() => TableSchemaBuilder.ValidateFieldName(name, "field")); }
static void Main(string[] args) { // -------------------------------------------- Setup GCP Credentials -------------------------------------------- // Replace with path to wherever Auth<name>.json file is on your local machine string pathToCreds = "/Users/devin/Documents/GitHub/FrankFund/Credentials/AuthDevin.json"; // Once set you can make calls to the GCP API System.Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", pathToCreds); // -------------------------------------------- BigQuery Query Example -------------------------------------------- // Relevant Docs: https://cloud.google.com/bigquery/docs/samples/bigquery-query#bigquery_query-csharp var projectID = "frankfund"; // GCP project id is always lowercase var datasetID = "FrankFund"; var tableName = "Accounts"; var client = BigQueryClient.Create(projectID); var table = client.GetTable(datasetID, tableName); var query = $"SELECT * FROM {projectID}.{datasetID}.{tableName} ORDER BY AccountID DESC"; // Table selection is always in the form: projectID.datasetID.tableName var queryResults = client.ExecuteQuery(query, parameters: null); Console.WriteLine("Executing query: " + query); /* * Console Output: * Executing query: SELECT * FROM frankfund.FrankFund.Accounts ORDER BY AccountID DESC * * Query Results: * 4, KennethTran, [email protected] * 3, RachelPai, [email protected] * 2, DevinSuy, [email protected] * 1, AutumnNguyen, [email protected] */ Console.WriteLine("\nQuery Results:"); foreach (BigQueryRow row in queryResults) { Console.WriteLine($" {row["AccountID"]}, {row["AccountUsername"]}, {row["EmailAddress"]}"); // Iterate over result set and access current row with row["columnName"] } // -------------------------------------------- BigQuery Insert Data From .json Stored In Cloud Storage Example -------------------------------------------- // Relevant Docs: https://cloud.google.com/bigquery/docs/samples/bigquery-load-table-gcs-json projectID = "frankfund"; // GCP project id is always lowercase datasetID = "FrankFund"; tableName = "Accounts"; var schema = new TableSchemaBuilder { { "AccountID", BigQueryDbType.String }, { "AccountUsername", BigQueryDbType.String }, { "EmailAddress", BigQueryDbType.String }, { "Password", BigQueryDbType.String }, { "FacebookID", BigQueryDbType.Numeric }, { "GoogleID", BigQueryDbType.Numeric } }.Build(); var jobOptions = new CreateLoadJobOptions { SourceFormat = FileFormat.NewlineDelimitedJson // NOTE: GCP only accepts single line per object for .json format }; client = BigQueryClient.Create(projectID); var dataset = client.GetDataset(datasetID); var tableRef = dataset.GetTableReference(tableName); var cloudStorageURI = "gs://frankfund_sandbox/exampleAccountInsert.json"; // Cloud Storage URI format: gs://<bucket_name>/<file_path_inside_bucket> var loadJob = client.CreateLoadJob(cloudStorageURI, tableRef, schema, jobOptions); try{ loadJob.PollUntilCompleted(); Console.WriteLine("Json file loaded to BigQuery"); } catch (Google.GoogleApiException e) { Console.WriteLine(e.Message); } // -------------------------------------------- Cloud Storage File Retrieval Example -------------------------------------------- // Relevant Docs: // https://googleapis.github.io/google-cloud-dotnet/docs/Google.Cloud.Storage.V1/index.html // https://docs.microsoft.com/en-us/dotnet/api/system.io.filestream?view=net-5.0 // Useful reference for future: https://medium.com/net-core/using-google-cloud-storage-in-asp-net-core-74f9c5ee55f5 projectID = "frankfund"; var bucketName = "frankfund_sandbox"; var fileName = "team_members.txt"; var storageClient = StorageClient.Create(); // Download file from cloud storage bucket to local using (var stream = File.OpenWrite(fileName)){ storageClient.DownloadObject(bucketName, fileName, stream); } /* Console Output From Downloaded File: * Team Frank: Autumn Nguyen, Devin Suy, Kenneth Tran, Rachel Pai */ foreach (string line in File.ReadAllLines(fileName)) { Console.WriteLine(line); } }
public void ValidateFieldName_InvalidTooLong() { // Can't embed this name into an InlineData easily... Assert.Throws <ArgumentException>(() => TableSchemaBuilder.ValidateFieldName(new string('a', 129), "field")); }
public void Scalars() { var schema = new TableSchemaBuilder { { "integer", BigQueryDbType.Int64 }, { "bool", BigQueryDbType.Bool }, { "bytes", BigQueryDbType.Bytes }, { "float", BigQueryDbType.Float64 }, { "string", BigQueryDbType.String }, { "timestamp", BigQueryDbType.Timestamp }, { "date", BigQueryDbType.Date }, { "dateTime", BigQueryDbType.DateTime }, { "time", BigQueryDbType.Time }, { "numeric", BigQueryDbType.Numeric }, { "geography", BigQueryDbType.Geography }, { "struct", new TableSchemaBuilder { { "x", BigQueryDbType.Int64 }, { "y", BigQueryDbType.String } } } }.Build(); var rawRow = new TableRow { F = new[] { new TableCell { V = "10" }, new TableCell { V = "true" }, new TableCell { V = "AQI=" }, // 1, 2 new TableCell { V = "2.5" }, new TableCell { V = "text" }, new TableCell { V = "1477566580.5" }, // 2016-10-27T11:09:40.500Z new TableCell { V = "2017-08-09" }, new TableCell { V = "2017-08-09T12:34:56.123" }, new TableCell { V = "12:34:56.123" }, new TableCell { V = "1234567890123456789012345678.123456789" }, new TableCell { V = "POINT(1 2)" }, new TableCell { V = new JObject { ["f"] = new JArray { new JObject { ["v"] = "100" }, new JObject { ["v"] = "xyz" } } } } } }; var row = new BigQueryRow(rawRow, schema); Assert.Equal(10, (long)row["integer"]); Assert.Equal(true, (bool)row["bool"]); Assert.Equal(new byte[] { 1, 2 }, (byte[])row["bytes"]); Assert.Equal(2.5d, (double)row["float"]); Assert.Equal("text", (string)row["string"]); Assert.Equal(new DateTime(2016, 10, 27, 11, 9, 40, 500, DateTimeKind.Utc), (DateTime)row["timestamp"]); Assert.Equal(new DateTime(2017, 8, 9, 0, 0, 0, DateTimeKind.Utc), (DateTime)row["date"]); Assert.Equal(new DateTime(2017, 8, 9, 12, 34, 56, 123, DateTimeKind.Utc), (DateTime)row["dateTime"]); Assert.Equal(new TimeSpan(0, 12, 34, 56, 123), (TimeSpan)row["time"]); Assert.Equal(BigQueryNumeric.Parse("1234567890123456789012345678.123456789"), (BigQueryNumeric)row["numeric"]); Assert.Equal(BigQueryGeography.Parse("POINT(1 2)"), (BigQueryGeography)row["geography"]); Assert.Equal(new Dictionary <string, object> { { "x", 100L }, { "y", "xyz" } }, (Dictionary <string, object>)row["struct"]); }
public void Arrays() { var schema = new TableSchemaBuilder { { "integer", BigQueryDbType.Int64, BigQueryFieldMode.Repeated }, { "bool", BigQueryDbType.Bool, BigQueryFieldMode.Repeated }, { "bytes", BigQueryDbType.Bytes, BigQueryFieldMode.Repeated }, { "float", BigQueryDbType.Float64, BigQueryFieldMode.Repeated }, { "string", BigQueryDbType.String, BigQueryFieldMode.Repeated }, { "timestamp", BigQueryDbType.Timestamp, BigQueryFieldMode.Repeated }, { "date", BigQueryDbType.Date, BigQueryFieldMode.Repeated }, { "dateTime", BigQueryDbType.DateTime, BigQueryFieldMode.Repeated }, { "time", BigQueryDbType.Time, BigQueryFieldMode.Repeated }, { "numeric", BigQueryDbType.Numeric, BigQueryFieldMode.Repeated }, { "geography", BigQueryDbType.Geography, BigQueryFieldMode.Repeated }, { "struct", new TableSchemaBuilder { { "x", BigQueryDbType.Int64 }, { "y", BigQueryDbType.String } }, BigQueryFieldMode.Repeated } }.Build(); var rawRow = new TableRow { F = new[] { new TableCell { V = CreateArray("10", "20") }, new TableCell { V = CreateArray("true", "false") }, new TableCell { V = CreateArray("AQI=", "AQM=") }, // [1, 2], [1, 3] new TableCell { V = CreateArray("2.5", "3.5") }, new TableCell { V = CreateArray("text", "more text") }, new TableCell { V = CreateArray("1477566580.5", "1477566581.5") }, // 2016-10-27T11:09:40.500Z, 2016-10-27T11:09:41.500Z new TableCell { V = CreateArray("2017-08-09", "2017-08-10") }, new TableCell { V = CreateArray("2017-08-09T12:34:56.123", "2017-08-09T12:34:57.123") }, new TableCell { V = CreateArray("12:34:56.123", "12:34:57.123") }, new TableCell { V = CreateArray("1234567890123456789012345678.123456789", "0.000000001") }, new TableCell { V = CreateArray("POINT(1 3)", "POINT(2 4)") }, new TableCell { V = new JArray { new JObject { ["v"] = new JObject { ["f"] = new JArray { new JObject { ["v"] = "100" }, new JObject { ["v"] = "xyz" } } } }, new JObject { ["v"] = new JObject { ["f"] = new JArray { new JObject { ["v"] = "200" }, new JObject { ["v"] = "abc" } } } } } } } }; var row = new BigQueryRow(rawRow, schema); Assert.Equal(new[] { 10L, 20L }, (long[])row["integer"]); Assert.Equal(new[] { true, false }, (bool[])row["bool"]); Assert.Equal(new[] { new byte[] { 1, 2 }, new byte[] { 1, 3 } }, (byte[][])row["bytes"]); Assert.Equal(new[] { 2.5d, 3.5d }, (double[])row["float"]); Assert.Equal(new[] { "text", "more text" }, (string[])row["string"]); Assert.Equal(new[] { new DateTime(2016, 10, 27, 11, 9, 40, 500, DateTimeKind.Utc), new DateTime(2016, 10, 27, 11, 9, 41, 500, DateTimeKind.Utc) }, (DateTime[])row["timestamp"]); Assert.Equal(new[] { new DateTime(2017, 8, 9, 0, 0, 0, DateTimeKind.Utc), new DateTime(2017, 8, 10, 0, 0, 0, DateTimeKind.Utc) }, (DateTime[])row["date"]); Assert.Equal(new[] { new DateTime(2017, 8, 9, 12, 34, 56, 123, DateTimeKind.Utc), new DateTime(2017, 8, 9, 12, 34, 57, 123, DateTimeKind.Utc) }, (DateTime[])row["dateTime"]); Assert.Equal(new[] { new TimeSpan(0, 12, 34, 56, 123), new TimeSpan(0, 12, 34, 57, 123) }, (TimeSpan[])row["time"]); Assert.Equal(new[] { BigQueryNumeric.Parse("1234567890123456789012345678.123456789"), BigQueryNumeric.Parse("0.000000001") }, (BigQueryNumeric[])row["numeric"]); Assert.Equal(new[] { BigQueryGeography.Parse("POINT(1 3)"), BigQueryGeography.Parse("POINT(2 4)") }, (BigQueryGeography[])row["geography"]); Assert.Equal(new[] { new Dictionary <string, object> { { "x", 100L }, { "y", "xyz" } }, new Dictionary <string, object> { { "x", 200L }, { "y", "abc" } } }, (Dictionary <string, object>[])row["struct"]); }
private static TableSchema CreateTestTableSchema() { ITableSchemaBuilder builder = new TableSchemaBuilder(); return builder.WithName("staff").WithFieldsFrom<StaffRecord>().WithKeyField("uid").Build(); }
private static TableSchema CreateTestTableSchema() { ITableSchemaBuilder builder = new TableSchemaBuilder(); return(builder.WithName("staff").WithFieldsFrom <StaffRecord>().WithKeyField("uid").Build()); }
public void ValidateFieldName_Valid() { TableSchemaBuilder.ValidateFieldName("abcABC_10", "field"); TableSchemaBuilder.ValidateFieldName("_", "field"); TableSchemaBuilder.ValidateFieldName(new string('a', 128), "field"); }
public void ValidateFieldName_Short() => TableSchemaBuilder.ValidateFieldName(ShortFieldName, "param");
public void ValidateFieldName_Long() => TableSchemaBuilder.ValidateFieldName(LongFieldName, "param");