private void CreateHighScoreExtendedTable(BigQueryDataset dataset)
{
var table = dataset.CreateTable(HighScoreExtendedTableId, new TableSchemaBuilder
{
{ "player", BigQueryDbType.String },
{ "gameStarted", BigQueryDbType.Timestamp },
{ "score", BigQueryDbType.Int64 },
{ "gameFinished", BigQueryDbType.Timestamp }
}.Build());
var rows = new[]
{
new BigQueryInsertRow {
{ "player", "Bob" },
{ "score", 75 },
{ "gameStarted", new DateTime(2003, 1, 1, 0, 0, 0, DateTimeKind.Utc) },
{ "gameFinished", new DateTime(2003, 1, 1, 1, 0, 0, DateTimeKind.Utc) }
},
new BigQueryInsertRow {
{ "player", "Angela" },
{ "score", 105 },
{ "gameStarted", new DateTime(2004, 1, 1, 0, 0, 0, DateTimeKind.Utc) },
{ "gameFinished", new DateTime(2004, 1, 1, 3, 0, 0, DateTimeKind.Utc) }
}
};
InsertAndWait(table, () => table.InsertRows(rows), 2);
}
private void CreateHighScoreTable(BigQueryDataset dataset)
{
var table = dataset.CreateTable(HighScoreTableId, new TableSchemaBuilder
{
{ "player", BigQueryDbType.String },
{ "gameStarted", BigQueryDbType.Timestamp },
{ "score", BigQueryDbType.Int64 }
}.Build());
table.Insert(new[]
{
new BigQueryInsertRow {
{ "player", "Bob" },
{ "score", 85 },
{ "gameStarted", new DateTime(2000, 1, 1, 0, 0, 0, DateTimeKind.Utc) }
},
new BigQueryInsertRow {
{ "player", "Angela" },
{ "score", 95 },
{ "gameStarted", new DateTime(2002, 1, 1, 0, 0, 0, DateTimeKind.Utc) }
},
new BigQueryInsertRow {
{ "player", null }, // Unnamed player...
{ "score", 1 },
{ "gameStarted", new DateTime(2001, 1, 1, 0, 0, 0, DateTimeKind.Utc) }
}
});
}
private void CreateComplexTypesTable(BigQueryDataset dataset)
{
dataset.CreateTable(ComplexTypesTableId, new TableSchemaBuilder
{
{ "guid", BigQueryDbType.String },
{ "tags", BigQueryDbType.String, BigQueryFieldMode.Repeated },
{ "position", new TableSchemaBuilder
{
{ "x", BigQueryDbType.Int64 },
{ "y", BigQueryDbType.Int64 }
}
},
{ "job", new TableSchemaBuilder
{
{ "company", BigQueryDbType.String },
{ "roles", BigQueryDbType.String, BigQueryFieldMode.Repeated }
}
},
{ "names", new TableSchemaBuilder
{
{ "first", BigQueryDbType.String },
{ "last", BigQueryDbType.String }
},
BigQueryFieldMode.Repeated
}
}.Build());
}
private string CreateGameDataset()
{
string id = CreateDataset();
BigQueryDataset game = Client.GetDataset(id);
var historySchema = new TableSchemaBuilder
{
{ "player", BigQueryDbType.String },
{ "score", BigQueryDbType.Int64 },
{ "level", BigQueryDbType.Int64 },
{ "game_started", BigQueryDbType.Timestamp }
}.Build();
var historyTable = game.CreateTable(HistoryTableId, historySchema);
var historyTableWithInserts = game.CreateTable(HistoryTableWithInsertsId, historySchema);
string[] csvRows =
{
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")
};
MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(string.Join("\n", csvRows)));
historyTable.UploadCsv(stream).PollUntilCompleted().ThrowOnAnyError();
stream.Position = 0;
historyTableWithInserts.UploadCsv(stream).PollUntilCompleted().ThrowOnAnyError();
return(id);
}
public void TestCreateDataset()
{
var snippet = new BigQueryCreateDataset();
BigQueryDataset dataset = snippet.CreateDataset(_projectId);
Assert.Equal("US", dataset.Resource.Location);
dataset.Delete();
}
private static bool IsTestDataset(BigQueryDataset dataset)
{
var id = dataset.Reference.DatasetId;
return(id.StartsWith("test_") ||
id.StartsWith("snippets_") ||
id.StartsWith("testlabels_") ||
id.StartsWith("testml_"));
}
public frmMain()
{
InitializeComponent();
//Creates a connection to the database
client = BigQueryClient.Create("booming-pride-278623");
// Create the dataset if it doesn't exist.
dataset = client.GetOrCreateDataset("TestBQ");
}
public void FullyQualifiedId()
{
var reference = new DatasetReference {
ProjectId = "my-project", DatasetId = "my-dataset"
};
var dataset = new BigQueryDataset(new Mock <BigQueryClient>().Object, new Dataset {
DatasetReference = reference
});
Assert.Equal("my-project:my-dataset", dataset.FullyQualifiedId);
}
public string CreateTempDataset()
{
string datasetId = TestUtil.RandomName();
BigQueryDataset tempDataset = _client.CreateDataset(
datasetId, new Dataset {
Location = "US"
});
_tempDatasets.Add(tempDataset);
return(datasetId);
}
internal void CreateRoutine(string routineId, BigQueryDataset dataset)
{
Routine routine = new Routine
{
DefinitionBody = "SELECT 1;",
};
routine.SetRoutineLanguage(RoutineLanguage.Sql);
routine.SetRoutineType(RoutineType.StoredProcedure);
dataset.CreateRoutine(routineId, routine);
}
static void Main(string[] args)
{
// Your Google Cloud Platform project ID
string projectId = "YOUR_PROJECT_ID";
// Instantiates a client
BigQueryClient client = BigQueryClient.Create(projectId);
// The id for the new dataset
string datasetId = "my_new_dataset";
// Creates the dataset
BigQueryDataset dataset = client.CreateDataset(datasetId);
Console.WriteLine($"Dataset {dataset.FullyQualifiedId} created.");
}
private void CreateExhaustiveTypesTable(BigQueryDataset dataset)
{
// Record containing a single string, a repeated string and a nested record
TableSchema recordSchema = new TableSchemaBuilder
{
{ "single_string", BigQueryDbType.String },
{ "repeated_string", BigQueryDbType.String, BigQueryFieldMode.Repeated },
{ "nested_record",
new TableSchemaBuilder {
{ "a", BigQueryDbType.Int64 },
{ "b", BigQueryDbType.Int64 },
} }
}.Build();
var table = dataset.CreateTable(ExhaustiveTypesTableId, new TableSchemaBuilder
{
// Single fields
{ "single_string", BigQueryDbType.String },
{ "single_bool", BigQueryDbType.Bool },
{ "single_bytes", BigQueryDbType.Bytes },
{ "single_date", BigQueryDbType.Date },
{ "single_datetime", BigQueryDbType.DateTime },
{ "single_time", BigQueryDbType.Time },
{ "single_timestamp", BigQueryDbType.Timestamp },
{ "single_int64", BigQueryDbType.Int64 },
{ "single_float64", BigQueryDbType.Float64 },
{ "single_numeric", BigQueryDbType.Numeric },
{ "single_geography", BigQueryDbType.Geography },
{ "single_record", recordSchema },
// Repeated fields
{ "array_string", BigQueryDbType.String, BigQueryFieldMode.Repeated },
{ "array_bool", BigQueryDbType.Bool, BigQueryFieldMode.Repeated },
{ "array_bytes", BigQueryDbType.Bytes, BigQueryFieldMode.Repeated },
{ "array_date", BigQueryDbType.Date, BigQueryFieldMode.Repeated },
{ "array_datetime", BigQueryDbType.DateTime, BigQueryFieldMode.Repeated },
{ "array_time", BigQueryDbType.Time, BigQueryFieldMode.Repeated },
{ "array_timestamp", BigQueryDbType.Timestamp, BigQueryFieldMode.Repeated },
{ "array_int64", BigQueryDbType.Int64, BigQueryFieldMode.Repeated },
{ "array_float64", BigQueryDbType.Float64, BigQueryFieldMode.Repeated },
{ "array_numeric", BigQueryDbType.Numeric, BigQueryFieldMode.Repeated },
{ "array_geography", BigQueryDbType.Geography, BigQueryFieldMode.Repeated },
{ "array_record", recordSchema, BigQueryFieldMode.Repeated },
}.Build());
InsertAndWait(table, () => table.InsertRow(ExhaustiveTypesTest.GetSampleRow()), 1);
}
public BigQueryMessageRespository(ILogger <BigQueryMessageRespository> logger)
{
// Get projectId fron config
string googleCredentialsText = File.ReadAllText(Environment.GetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS"));
JObject googleCredentials = JObject.Parse(googleCredentialsText);
string projectId = googleCredentials["project_id"].ToString();
// Get Dataset Name and Table Name
string datasetName = Environment.GetEnvironmentVariable("DATASET_NAME");
string tableName = Environment.GetEnvironmentVariable("MESSAGE_TABLE_NAME");
// Get Table and Client
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryDataset dataset = client.GetDataset(datasetName);
_logger = logger;
_table = dataset.GetTable(tableName);
_logger.LogInformation("GCP Information set. projectId: {projectId} datasetName: {datasetName},tableName:{tableName}, ", projectId, datasetName, tableName);
}
private void CreatePeopleTable(BigQueryDataset dataset)
{
// Sample schema taken from
// https://cloud.google.com/bigquery/docs/data
// Data is loaded in UploadTest.
var table = dataset.CreateTable(PeopleTableId, new TableSchemaBuilder
{
{ "name", BigQueryDbType.String },
{ "fullName", BigQueryDbType.String, BigQueryFieldMode.Required },
{ "age", BigQueryDbType.Int64 },
{ "gender", BigQueryDbType.String },
{ "phoneNumber", new TableSchemaBuilder
{
{ "areaCode", BigQueryDbType.Int64 },
{ "number", BigQueryDbType.Int64 }
}
},
{ "children", new TableSchemaBuilder
{
{ "name", BigQueryDbType.String },
{ "gender", BigQueryDbType.String },
{ "age", BigQueryDbType.Int64 },
},
BigQueryFieldMode.Repeated
},
{ "citiesLived", new TableSchemaBuilder
{
{ "place", BigQueryDbType.String },
{ "yearsLived", BigQueryDbType.Int64, BigQueryFieldMode.Repeated },
},
BigQueryFieldMode.Repeated
}
}.Build());
List<string> jsonRows = LoadTextResource("personsData.json");
var job = table.UploadJson(jsonRows);
var result = job.PollUntilCompleted();
var errors = result.Status.ErrorResult;
if (errors != null)
{
throw new Exception("Errors uploading JSON: " + errors);
}
}
public IActionResult InsertBQData(string username, string userplan, string customerId)
{
try
{
string projectId = "task8-imagerecognition-281418";
DateTime now = DateTime.Now;
Console.WriteLine("email: " + username);
Console.WriteLine("plan: " + userplan);
BigQueryClient client = BigQueryClient.Create(projectId);
// Create the dataset if it doesn't exist.
BigQueryDataset dataset = client.GetOrCreateDataset("mydata");
// Create the table if it doesn't exist.
BigQueryTable table = dataset.GetOrCreateTable("customer", new TableSchemaBuilder
{
{ "email", BigQueryDbType.String },
{ "subscriptionStarted", BigQueryDbType.Timestamp },
{ "planType", BigQueryDbType.String } //BigQueryDbType.Int64
}.Build());
//Insert data into table
table.InsertRow(new BigQueryInsertRow
{
{ "email", username },
{ "subscriptionStarted", DateTimeOffset.UtcNow.ToUnixTimeSeconds() },
{ "planType", userplan }
});
Console.WriteLine("Inserted: " + username + " successfully");
return(Redirect("/Home/Main/" + username + "/" + userplan + "/" + customerId + "?Msg=Success"));
}// End of try
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
return(Redirect("/Home/Main/" + username + "/" + userplan + "/" + customerId + "?Msg=Failed"));
}
}// End of insertBQData
private string CreateGameDataset()
{
string id = CreateDataset();
BigQueryDataset game = Client.GetDataset(id);
var historySchema = new TableSchemaBuilder
{
{ "player", BigQueryDbType.String },
{ "score", BigQueryDbType.Int64 },
{ "level", BigQueryDbType.Int64 },
{ "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);
}
private void CriarDataSet(string nomeDataSet)
{
_dataSet = _cliente.GetOrCreateDataset(nomeDataSet);
}
private bool BuildTable()
{
var tableName = ComputerName == "CH-J7TMTZ1" ? "CCChinaTable" : "CCTable";
#if DEBUG
tableName = "CCChinaTable";
#endif
try {
_client = BigQueryClient.Create("booming-pride-278623");
_dataset = _client.GetOrCreateDataset("CCDataset");
_table = _dataset.GetOrCreateTable(tableName, new TableSchemaBuilder
{
{ "CycleNumber", BigQueryDbType.Int64 },
{ "LogTime_Timestamp", BigQueryDbType.Timestamp },
{ "TotalTest_Hours", BigQueryDbType.Float64 },
{ "MinutesIntoCycle", BigQueryDbType.Float64 },
{ "CurrentBiasIsOn", BigQueryDbType.String },
{ "SampleName", BigQueryDbType.String },
{ "Current_Amps", BigQueryDbType.Float64 },
{ "Voltage_Volts", BigQueryDbType.Float64 },
{ "NumCells", BigQueryDbType.Int64 },
{ "CellVoc_Volts", BigQueryDbType.Float64 },
{ "TempSensorNumber", BigQueryDbType.Int64 },
{ "SetCurrent_Amps", BigQueryDbType.Float64 },
{ "EstimatedRs_mOhms", BigQueryDbType.Float64 },
{ "Temp1_C", BigQueryDbType.Float64 },
{ "Temp2_C", BigQueryDbType.Float64 },
{ "Temp3_C", BigQueryDbType.Float64 },
{ "Temp4_C", BigQueryDbType.Float64 },
{ "Temp5_C", BigQueryDbType.Float64 },
{ "Temp6_C", BigQueryDbType.Float64 },
{ "Temp7_C", BigQueryDbType.Float64 },
{ "Temp8_C", BigQueryDbType.Float64 },
{ "Temp9_C", BigQueryDbType.Float64 },
{ "Temp10_C", BigQueryDbType.Float64 },
{ "Temp11_C", BigQueryDbType.Float64 },
{ "Temp12_C", BigQueryDbType.Float64 },
{ "Temp13_C", BigQueryDbType.Float64 },
{ "Temp14_C", BigQueryDbType.Float64 },
{ "Temp15_C", BigQueryDbType.Float64 },
{ "Temp16_C", BigQueryDbType.Float64 },
{ "SmokeLevel1_Volts", BigQueryDbType.Float64 },
{ "SmokeLevel2_Volts", BigQueryDbType.Float64 },
{ "SmokeLevel3_Volts", BigQueryDbType.Float64 },
{ "SmokeLevel4_Volts", BigQueryDbType.Float64 },
{ "SmokeLevel5_Volts", BigQueryDbType.Float64 },
{ "SmokeLevel6_Volts", BigQueryDbType.Float64 },
{ "SmokeLevel7_Volts", BigQueryDbType.Float64 },
{ "SmokeLevel8_Volts", BigQueryDbType.Float64 },
{ "SmokeVoltage1_Volts", BigQueryDbType.Float64 },
{ "SmokeVoltage2_Volts", BigQueryDbType.Float64 },
{ "SmokeVoltage3_Volts", BigQueryDbType.Float64 },
{ "SmokeVoltage4_Volts", BigQueryDbType.Float64 },
{ "SmokeVoltage5_Volts", BigQueryDbType.Float64 },
{ "SmokeVoltage6_Volts", BigQueryDbType.Float64 },
{ "SmokeVoltage7_Volts", BigQueryDbType.Float64 },
{ "SmokeVoltage8_Volts", BigQueryDbType.Float64 },
}.Build());
return(true);
}
catch {
return(false);
}
}
