public void TestImportDataFromJSON()
{
var datasetId = $"datasetForLoadJson{DateTime.Now.Ticks}";
var newTableID = $"tableForTestImportDataFromJSON{RandomSuffix()}";
_tablesToDelete.Add(new Tuple <string, string>(datasetId, newTableID));
_datasetsToDelete.Add(datasetId);
// JSON file below has 50 items in it.
string expectedFirstRowName = "Alabama";
GoogleCloudSamples.BiqQuerySnippets.LoadTableFromJSON(_client, datasetId, newTableID);
// Run query to get table data.
var newTable = _client.GetTable(datasetId, newTableID);
string query = $"SELECT name, post_abbr FROM {newTable}" +
$"ORDER BY name, post_abbr";
BigQueryResults results = AsyncQuery(_projectId, datasetId, newTableID,
query, _client);
var row = results.First();
// Check results.
Assert.Equal(expectedFirstRowName, row["name"]);
Assert.True(results.Count() == 50);
}
public IReadOnlyCollection <T> MapFromResults <T>(BigQueryResults results)
{
var models = new List <T>();
foreach (var row in results)
{
var instance = Activator.CreateInstance <T>();
foreach (var field in results.Schema.Fields)
{
var property = typeof(T).GetProperties().SingleOrDefault(x => x.Name == field.Name);
if (property == null)
{
throw new InvalidOperationException($"Unable to find a model property for the response field '{field.Name}'");
}
property.SetValue(instance, row[field.Name]);
}
models.Add(instance);
}
return(models);
}
public void TestImportFromCloudStorage()
{
string datasetId = "datasetForTestImportFromCloudStorage";
string newTableId = "tableForTestImportFromCloudStorage";
string jsonGcsSampleFile = "sample.json";
string gcsFolder = "test";
string gcsUploadTestWord = "exampleJsonFromGCS";
_tablesToDelete.Add(new Tuple <string, string>(datasetId, newTableId));
_datasetsToDelete.Add(datasetId);
CreateDataset(datasetId, _client);
CreateTable(datasetId, newTableId, _client);
// Import data.
ImportDataFromCloudStorage(_projectId, datasetId, newTableId, _client,
jsonGcsSampleFile, gcsFolder);
// Run query to get table data.
var newTable = _client.GetTable(datasetId, newTableId);
string query = $"SELECT title, unique_words FROM {newTable} " +
"ORDER BY unique_words, title";
BigQueryResults results = AsyncQuery(_projectId, datasetId, newTableId,
query, _client);
// Get first row and confirm it contains the expected value.
var row = results.First();
Assert.Equal(gcsUploadTestWord, row["title"]);
}
public List <Event> GetEvents()
{
List <Event> events = new List <Event>();
BigQueryResults result = Results[0];
foreach (BigQueryRow row in result)
{
Event e = new Event();
e.eventid = Convert.ToInt32(row["eventid"]);
e.description = row["description"].ToString();
e.clubid = Convert.ToInt32(row["clubid"]);
e.studioid = Convert.ToInt32(row["studioid"]);
e.personnelid = Convert.ToInt32(row["personnelid"]);
e.date = row["date"].ToString();
e.time = row["time"].ToString();
e.nonmember = Convert.ToBoolean(row["nonmember"]);
e.canbook = Convert.ToBoolean(row["canbook"]);
e.memberamount = Convert.ToDouble(row["memberamount"]);
e.nonmemberamount = Convert.ToDouble(row["nonmemberamount"]);
e.attendance = Convert.ToInt32(row["attendance"]);
events.Add(e);
}
return(events);
}
public List <ClubDetails> GetClubDetails()
{
List <ClubDetails> clubDetails = new List <ClubDetails>();
BigQueryResults result = Results[0];
foreach (BigQueryRow row in result)
{
ClubDetails details = new ClubDetails
{
clubId = Convert.ToInt32(row["clubid"]),
clubName = row["clubname"].ToString(),
csiId = Convert.ToInt32(row["csiid"]),
gpId = Convert.ToInt32(row["gpid"]),
location = row["location"].ToString(),
zip = row["zip"].ToString(),
RitaID = Convert.ToInt64(row["RitaID"]),
MerchantID = Convert.ToInt64(row["MerchantId"]),
language = Convert.ToInt32(row["ExtraLanguage"]),
timeZone = Convert.ToInt32(row["TimeZone"]),
isActive = Convert.ToBoolean(row["isActive"]),
};
clubDetails.Add(details);
}
return(clubDetails);
}
public BigQueryResults GetBigQueryResults(
BigQueryClient client,
BigQueryJob job
)
{
job.PollUntilCompleted();
BigQueryResults results = client.GetQueryResults(job.Reference);
return(results);
}
private static DataTable CreateDataTable(BigQueryResults res)
{
DataTable dt = new DataTable();
foreach (var field in res.Schema.Fields)
{
dt.Columns.Add(field.Name);
}
return(dt);
}
static async Task MainAsync(string[] args)
{
GoogleCredential googleCredential = GoogleCredential.FromFile(_googleCredentialPath);
BigQueryClient bigQueryClient = await BigQueryClient.CreateAsync(_bigQueryProjectId, googleCredential);
string query = $"select * from `{_bigQueryProjectId}.{_bigQueryDataSetId}.{_bigQueryTableId}`";
BigQueryResults results = await RunBigQueryAsync(bigQueryClient, query);
await ExportBigQueryTableToStorageAsync(bigQueryClient, _cloudStorageDestinationUri, results);
}
public void TestLegacySqlAsyncQuery()
{
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.FullyQualifiedId}]";
BigQueryResults results = LegacySqlAsyncQuery(
projectId, datasetId, tableId, query, _client);
Assert.True(results.Count() > 0);
}
public void TestAsyncQuery()
{
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";
BigQueryResults results = AsyncQuery(projectId, datasetId, tableId, query, _client);
Assert.True(results.Count() > 0);
}
public async Task <bool> selectExceptUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT * EXCEPT (SecondNumber) FROM {table}";
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
private async void CreateExportQuery(SENSOR_DATA_TYPE type)
{
try
{
var credential = GoogleCredential.FromFile(" path to credentials");
BigQueryClient client = BigQueryClient.Create(_projectId, credential);
string table_name = "", destination_uri = " ";
switch (type)
{
case SENSOR_DATA_TYPE.SOIL_MOISTURE:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_Soil_Moisture`";
destination_uri = "gs://bq-query-export-data/sm-data-*.csv";
break;
case SENSOR_DATA_TYPE.LIGHT:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_light_resistance`";
destination_uri = "gs://bq-query-export-data/lr-data-*.csv";
break;
case SENSOR_DATA_TYPE.TEMPERATURE:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_temperature`";
destination_uri = "gs://bq-query-export-data/tmp-data-*.csv";
break;
case SENSOR_DATA_TYPE.HUMIDITY:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_humidity`";
destination_uri = "gs://bq-query-export-data/hm-data-*.csv";
break;
default:
break;
}
string sql = string.Format(@"EXPORT DATA OPTIONS(uri='{0}', format='CSV', overwrite=true)
AS SELECT * FROM {1} WHERE TIME_DIFF(time_received, CURRENT_TIME('UTC-5'),HOUR) < 12
LIMIT 70", destination_uri, table_name);
BigQueryParameter[] parameters = null;
BigQueryResults _ = await client.ExecuteQueryAsync(sql, parameters);
}
catch (Exception ex)
{
_logger.LogError(@" \n --------------\n
Somthing went wrong in {0}. \n Message: {1} \n \n Stack trace: {3} \n Inner Exception {4}", ex.Source, ex.Message, ex.StackTrace, ex.StackTrace);
}
}
private int NumberOfActiveWorker(string aTable)
{
string datasetId = "customer";
string sql = $"SELECT * FROM TABLE";
//demonage
//elmage
//goldenage
//parklane
//sapphire
//willblowage
BigQueryResults results = this.adater.BigQueryResults(datasetId, aTable, sql);
//Results
int rows = Int32.Parse(results.TotalRows.ToString());
return(rows);
}
// [END sync_query]
// [START sync_query_legacy_sql]
public BigQueryResults 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 QueryOptions {
UseLegacySql = true
});
// Get the query result, waiting for the timespan specified in milliseconds.
BigQueryResults result = client.GetQueryResults(job.Reference.JobId,
new GetQueryResultsOptions {
Timeout = TimeSpan.FromMilliseconds(timeoutMs)
});
return(result);
}
//Method to send query and get response and then assemble the response.
public ArrayList GetData()
{
ArrayList tempArray = new ArrayList();
try
{
//tempArray.Add(System.IO.Directory.GetCurrentDirectory());
//var credentials = GoogleCredential.FromFile("./Assets/Scripts/Additional/authKey.json");
//var credentials = GoogleCredential.FromFile("/Additional/authkey.json");
var credentials = GoogleCredential.FromJson("Not Today");
BigQueryClient ourClient = BigQueryClient.Create(projectCode, credentials);
String query = this.FormQuery();
Debug.Log(query);
BigQueryResults results = ourClient.ExecuteQuery(query, parameters: null);
ArrayList stateData = new ArrayList();
StateData currentState = new StateData();
foreach (BigQueryRow row in results)
{
String date = Convert.ToString(row["date"]);
String country = Convert.ToString(row["country_code"]);
String state = Convert.ToString(row["subregion1_name"]);
String coordinates = Convert.ToString(row["location_geometry"]);
int confirmedCases = Convert.ToInt32(row["cumulative_confirmed"]);
int deceased = Convert.ToInt32(row["cumulative_deceased"]);
if (currentState.IsValidInsertion(state))
{
currentState.AddRow(country, state, coordinates, date, confirmedCases, deceased);
}
else
{
stateData.Add(currentState);
currentState = new StateData();
currentState.AddRow(country, state, coordinates, date, confirmedCases, deceased);
getDataSize++;
}
}
Debug.Log("Made it to data");
return(stateData);
}catch (Exception e)
{
tempArray.Add(e.ToString());
return(tempArray);
}
}
//BigQuery implementation
public BigQueryResults BigQueryResults(string aDatasetId, string aTableName, string aQueryString)
{
try
{
BigQueryTable table = bigQuery.GetTable(projectId, aDatasetId, aTableName);
//string sql = $"SELECT COUNT(*) FROM {table}";
string sql = aQueryString.Replace("TABLE", $"{table}");
BigQueryResults results = bigQuery.ExecuteQuery(sql);
return(results);
}
catch (Exception aExeption)
{
Console.WriteLine(aExeption.ToString());
return(null);
}
}
public List <FactorsReturedObject> getAccidnetsFactors()
{
BigQueryResults results = googleService.getAccidentsFactors();
List <FactorsReturedObject> resultDataList = new List <FactorsReturedObject>();
foreach (var row in results)
{
String factor = (String)row["factor"];
Int64 accidents = (Int64)row["accidents_count"];
resultDataList.Add(new FactorsReturedObject {
x = factor, value = accidents
});
}
return(resultDataList);
}
public List <GoogleQueryReturnedObject> getMapData(string year)
{
BigQueryResults results = googleService.getAccidentsByStateAndYear(year);
List <GoogleQueryReturnedObject> resultDataList = new List <GoogleQueryReturnedObject>();
foreach (var row in results)
{
String state = (String)row["state"];
Int64 accidents = (Int64)row["accidents_count"];
resultDataList.Add(new GoogleQueryReturnedObject {
id = usaStates[state], value = accidents, stateName = state
});
}
return(resultDataList);
}
public List <ActivityType> GetActivities()
{
List <ActivityType> activityTypes = new List <ActivityType>();
BigQueryResults results = Results[0];
foreach (BigQueryRow row in results)
{
ActivityType type = new ActivityType
{
activityTypeId = Convert.ToInt32(row["activityTypeId"]),
activityType = row["activityType"].ToString()
};
activityTypes.Add(type);
}
return(activityTypes);
}
public async Task <bool> selectReplaceUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT * REPLACE (FirstNumber / @divider AS FirstNumber) FROM {table}";
BigQueryParameter[] parameters = new[]
{
new BigQueryParameter("divider", BigQueryDbType.Int64, 2)
};
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
public async Task <bool> selectNotEqualUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT FullName, Country ,CreatedAt" +
$"FROM {table}" +
"WHERE Country != 'American Samoa'" +
"ORDER BY Country ASC; ";
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
public List <Schedule> GetClasses()
{
List <Schedule> classList = new List <Schedule>();
BigQueryResults result = Results[0];
foreach (BigQueryRow row in result)
{
Schedule classes = new Schedule
{
classId = Convert.ToInt32(row["classid"]),
clubId = Convert.ToInt32(row["clubid"]),
name = row["name"].ToString(),
shortDescription = row["shortDescription"].ToString(),
personnelId = (long[])(row["personnelid"]),
personnelName = (String[])row["personnelName"],
startDateTime = Convert.ToDateTime(row["startDateTime"]),
endDateTime = Convert.ToDateTime(row["endDateTime"]),
activityCode = row["activityCode"].ToString(),
activityTypeId = Convert.ToInt32(row["activityTypeId"]),
conceptId = Convert.ToInt32(row["conceptId"]),
conceptName = row["conceptName"].ToString(),
booked = Convert.ToInt32(row["booked"]),
sessionBeginDate = Convert.ToDateTime(row["sessionBeginDate"]),
sessionEndDate = Convert.ToDateTime(row["sessionEndDate"]),
memberStatus = row["memberStatus"].ToString(),
isPaid = Convert.ToBoolean(row["isPaid"].ToString()),
attendingCapacity = Convert.ToInt32(row["attendingCapacity"]),
scheduleGUID = row["scheduleGUID"].ToString(),
resourceId = (long[])row["resourceId"],
classTypeId = Convert.ToInt32(row["ClassTypeId"]),
familyFlag = Convert.ToBoolean(row["FamilyFlag"]),
isDropIn = Convert.ToBoolean(row["isDropIn"]),
intensity = row["Intensity"].ToString()
};
classList.Add(classes);
}
return(classList);
}
public void TestImportDataFromStream()
{
string datasetId = "datasetForTestImportDataFromStream";
string newTableId = "tableForTestImportDataFromStream";
string gcsUploadTestWord = "exampleJsonFromStream";
CreateDataset(datasetId, _client);
CreateTable(datasetId, newTableId, _client);
// Import data.
UploadJson(datasetId, newTableId, _client);
// Query table to get first row and confirm it contains the expected value.
var newTable = _client.GetTable(datasetId, newTableId);
string query = $"SELECT title, unique_words FROM {newTable} ORDER BY title";
BigQueryResults results = AsyncQuery(_projectId, datasetId, newTableId, query, _client);
var row = results.First();
Assert.Equal(gcsUploadTestWord, row["title"]);
DeleteTable(datasetId, newTableId, _client);
DeleteDataset(datasetId, _client);
}
public List <Resource> GetResources()
{
List <Resource> resource = new List <Resource>();
BigQueryResults result = Results[0];
foreach (BigQueryRow row in result)
{
Resource res = new Resource
{
resourceId = Convert.ToInt32(row["ResourceId"]),
resourceName = row["ResourceName"].ToString(),
clubId = Convert.ToInt32(row["ClubId"]),
isActive = Convert.ToBoolean(row["isActive"]),
};
resource.Add(res);
}
return(resource);
}
public List <Concept> GetConcepts()
{
List <Concept> ConceptsList = new List <Concept>();
BigQueryResults result = Results[0];
foreach (BigQueryRow row in result)
{
Concept Concept = new Concept
{
conceptid = Convert.ToInt32(row["conceptid"]),
conceptname = row["conceptname"].ToString(),
clubid = Convert.ToInt32(row["clubid"]),
isactive = Convert.ToBoolean(row["isActive"])
};
ConceptsList.Add(Concept);
}
return(ConceptsList);
}
public List <ClassTypes> GetClassTypes()
{
List <ClassTypes> classTypes = new List <ClassTypes>();
BigQueryResults res = Results[0];
foreach (BigQueryRow row in res)
{
ClassTypes types = new ClassTypes
{
id = Convert.ToInt32(row["id"]),
classType = row["name"].ToString(),
conceptId = Convert.ToInt32(row["conceptId"]),
className = (row["className"]).ToString(),
description = row["description"].ToString(),
};
classTypes.Add(types);
}
return(classTypes);
}
public void TestImportDataFromFile()
{
string datasetId = "datasetForTestImportDataFromFile";
string newTableId = "tableForTestImportDataFromFile";
string uploadTestWord = "additionalExampleJsonFromFile";
long uploadTestWordValue = 9814072356;
string filePath = "..\\..\\..\\test\\data\\sample.json";
CreateDataset(datasetId, _client);
CreateTable(datasetId, newTableId, _client);
// Import data.
UploadJsonFromFile(_projectId, datasetId, newTableId, filePath, _client);
// Query table to get first row and confirm it contains the expected value
var newTable = _client.GetTable(datasetId, newTableId);
string query = $"SELECT title, unique_words FROM {newTable} WHERE title = '{uploadTestWord}'";
BigQueryResults results = AsyncQuery(_projectId, datasetId, newTableId, query, _client);
var row = results.Last();
Assert.Equal(uploadTestWordValue, row["unique_words"]);
DeleteTable(datasetId, newTableId, _client);
DeleteDataset(datasetId, _client);
}
public void TestImportFromCloudStorageOrc()
{
var datasetId = $"datasetForLoadOrc{DateTime.Now.Ticks}";
var newTableID = $"tableForTestImportDataFromOrc{RandomSuffix()}";
_tablesToDelete.Add(new Tuple <string, string>(datasetId, newTableID));
_datasetsToDelete.Add(datasetId);
CreateDataset(datasetId, _client);
// Test parameters.
string expectedFirstRowName = "Alabama";
// Import data.
GoogleCloudSamples.BiqQuerySnippets.LoadTableFromOrc(datasetId, newTableID, _client);
// Run query to get table data.
var newTable = _client.GetTable(datasetId, newTableID);
string query = $"SELECT name, post_abbr FROM {newTable}" +
$"ORDER BY name, post_abbr";
BigQueryResults results = AsyncQuery(_projectId, datasetId, newTableID,
query, _client);
var row = results.First();
// Check results.
Assert.Equal(expectedFirstRowName, row["name"]);
Assert.True(results.Count() == 50);
// Test the truncate sample
GoogleCloudSamples.BiqQuerySnippets.LoadTableFromOrcTruncate(datasetId, newTableID, _client);
BigQueryResults newResults = AsyncQuery(_projectId, datasetId, newTableID,
query, _client);
row = newResults.First();
// Check results.
Assert.Equal(expectedFirstRowName, row["name"]);
Assert.True(newResults.Count() == 50);
}
public List <Personnel> GetPersonnel()
{
List <Personnel> employeeList = new List <Personnel>();
BigQueryResults result = Results[0];
foreach (BigQueryRow row in result)
{
Personnel employee = new Personnel
{
personnelId = Convert.ToInt32(row["employeeid"]),
name = row["employeename"].ToString(),
clubId = Convert.ToInt32(row["clubid"]),
conceptId = (long[])row["Concepts"],
personnelTypeId = Convert.ToInt32(row["jobtitleid"]),
personnelType = row["jobtitle"].ToString()
};
employeeList.Add(employee);
}
return(employeeList);
}
public List <Personnel> GetPersonnel()
{
List <Personnel> employeeList = new List <Personnel>();
BigQueryResults result = Results[0];
foreach (BigQueryRow row in result)
{
Personnel employee = new Personnel();
employee.personnelid = Convert.ToInt32(row["employeeid"]);
employee.name = row["employeename"].ToString();
employee.clubid = Convert.ToInt32(row["clubid"]);
employee.studioid = (long[])row["Studios"];
employee.personneltypeid = Convert.ToInt32(row["jobtitleid"]);
employee.personneltype = row["jobtitle"].ToString();
employeeList.Add(employee);
}
return(employeeList);
}
