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); }