public List <string> GetPopularTags()
{
const string format = "yyyyMMdd";
const string tablePrefix = "analytics_198930456.events_";
const string todayTablePrefix = "analytics_198930456.events_intraday_";
var todayDate = DateTime.Today;
string todayTable = todayTablePrefix + todayDate.ToString(format);
string d2Table = tablePrefix + todayDate.AddDays(-1).ToString(format);
string d3Table = tablePrefix + todayDate.AddDays(-2).ToString(format);
CreateTableIfNotExist(todayTable);
CreateTableIfNotExist(d2Table);
CreateTableIfNotExist(d3Table);
string query = @"
SELECT event_name, COUNT(event_name) AS tag_count FROM ( " +
"SELECT event_name FROM `" + todayTable + "`, UNNEST(event_params) AS p1 WHERE p1.key = 'TAG' " +
"UNION ALL SELECT event_name FROM `" + d2Table + "`, UNNEST(event_params) AS p2 WHERE p2.key = 'TAG' " +
"UNION ALL SELECT event_name FROM `" + d3Table + "`, UNNEST(event_params) AS p3 WHERE p3.key = 'TAG')" +
"GROUP BY event_name " +
"ORDER BY tag_count DESC " +
"LIMIT 5";
BigQueryJob job = client.CreateQueryJob(
sql: query,
parameters: null,
options: new QueryOptions {
UseQueryCache = false
});
job.PollUntilCompleted();
List <string> tags = new List <string>();
foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
{
var tag = $"{row["event_name"]}";
tags.Add(tag);
}
return(tags);
}
public BigQueryResults GetBigQueryResults(
BigQueryClient client,
BigQueryJob job
)
{
job.PollUntilCompleted();
BigQueryResults results = client.GetQueryResults(job.Reference);
return(results);
}
public void WriteResults(
string query
)
{
BigQueryClient client = CreateClient();
BigQueryJob job = CreateQueryJob(client, query);
job.PollUntilCompleted();
foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
{
Console.WriteLine($"{row["ClubId"]},{row["ClubName"]}");
}
}
// [END sync_query_legacy_sql]
// [START async_query]
public BigQueryResults AsyncQuery(string projectId, string datasetId, string tableId,
string query, BigQueryClient client)
{
var table = client.GetTable(projectId, datasetId, tableId);
BigQueryJob job = client.CreateQueryJob(query,
new QueryOptions {
UseQueryCache = false
});
// Wait for the job to complete.
job.PollUntilCompleted();
// Then we can fetch the results, either via the job or by accessing
// the destination table.
return(client.GetQueryResults(job.Reference.JobId));
}
// [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);
}
public static List <CHITIETTRAM> Update(string matram, string mo, string da)
{
List <CHITIETTRAM> re = new List <CHITIETTRAM>();
// Add file json.
using (BigQueryClient client = BigQueryClient.Create("phantantai", GoogleCredential.FromFile(@"C:\Users\ykdn1\OneDrive\Máy tính\oracle18.5\oracleteam3\oraclenhom3\oraclenhom3\App_Data\phantantai-c3450caeb9b5.json")))
{
string query = $@"
SELECT stn as MATRAM,da as DA,mo as MO,year as YEAR ,temp as NHIETDO,slp as APSUAT,wdsp as TOCDOGIO,max as TMAX,min as TMIN,prcp as LUONGMUA
FROM `bigquery-public-data.noaa_gsod.gsod2019`
where stn = '{matram}' and mo = '{mo}' and da = '{da}'" ;
BigQueryJob job = client.CreateQueryJob(
sql: query,
parameters: null,
options: new QueryOptions {
UseQueryCache = false
});
// Wait for the job to complete.
job.PollUntilCompleted();
CHITIETTRAM chitie = new CHITIETTRAM();
foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
{
chitie.MATRAM = int.Parse($"{row["MATRAM"]}");
chitie.MO = byte.Parse($"{row["MO"]}");
chitie.DA = short.Parse($"{row["DA"]}");
chitie.YEAR = short.Parse($"{row["YEAR"]}");
var nd = float.Parse($"{row["NHIETDO"]}");
chitie.NHIETDO = (byte)nd;
var tmp = float.Parse($"{row["APSUAT"]}");
chitie.APSUAT = tmp < 900 ? (short)tmp : (short)0;
tmp = float.Parse($"{row["TOCDOGIO"]}");
chitie.TOCDOGIO = tmp < 900 ? (short)tmp : (short)0;
tmp = float.Parse($"{row["TMAX"]}");
chitie.TMAX = tmp < 900 ? (short)tmp : (short)0;
tmp = float.Parse($"{row["TMIN"]}");
chitie.TMIN = tmp < 900 ? (short)tmp : (short)0;
tmp = float.Parse($"{row["LUONGMUA"]}");
chitie.LUONGMUA = tmp < 900 ? (short)tmp : (short)0;
re.Add(chitie);
}
return(re);
}
}
public void Query(
string projectId = "your-project-id"
)
{
BigQueryClient client = BigQueryClient.Create(projectId);
string query = @"
SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013`
WHERE state = 'TX'
LIMIT 100";
BigQueryJob job = client.CreateQueryJob(
sql: query,
parameters: null,
options: new QueryOptions {
UseQueryCache = false
});
// Wait for the job to complete.
job = job.PollUntilCompleted().ThrowOnAnyError();
// Display the results
foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
{
Console.WriteLine($"{row["name"]}");
}
}
// [END sync_query_legacy_sql]
// [START async_query]
public BigQueryResults AsyncQuery(string projectId, string datasetId, string tableId,
string query, BigQueryClient client)
{
var table = client.GetTable(projectId, datasetId, tableId);
BigQueryJob job = client.CreateQueryJob(query,
new CreateQueryJobOptions { UseQueryCache = false });
// Wait for the job to complete.
job.PollUntilCompleted();
// Then we can fetch the results, either via the job or by accessing
// the destination table.
return client.GetQueryResults(job.Reference.JobId);
}
// [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 CreateQueryJobOptions { 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;
}
