// [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 BigQueryTest()
{
// [START create_bigquery_client]
// By default, the Google.Bigquery.V2 library client will authenticate
// using the service account file (created in the Google Developers
// Console) specified by the GOOGLE_APPLICATION_CREDENTIALS
// environment variable. If you are running on
// a Google Compute Engine VM, authentication is completely
// automatic.
_projectId = Environment.GetEnvironmentVariable("GOOGLE_PROJECT_ID");
_client = BigQueryClient.Create(_projectId);
// [END create_bigquery_client]
}
// [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;
}
private Task <BigQueryTable> GetTableAsync(DateTime date, CancellationToken cancellationToken)
{
BigQueryClient client = GetBiqQueryClient();
return(client.GetTableAsync(googleBigQueryAttribute.DatasetId, $"{googleBigQueryAttribute.TableId}${date:yyyyMMdd}", null, cancellationToken));
}
/// <summary>
///
/// </summary>
/// <param name="etlSettings"></param>
/// <returns></returns>
public static async Task <List <string> > TransferBigQueryResultByDate(this EtlSettings etlSettings, AWSAthenaAPI awsAthenaAPI)
{
var result = new List <string>();
var awsS3Api = etlSettings.CreateTargetS3API();
var ga = etlSettings.GoogleAnalyticsQuerySource;
Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", $"{AppContext.BaseDirectory}/{ga.GoogleAnalyticsSettingFile}");
BigQueryClient client = BigQueryClient.Create(ga.GoogleAnalyticsProjectId);
string sql = ga.BigQuerySQL;
string dateQueryKey = DateTime.Now.AddDays(-ga.DaysAgo).ToString(ga.DateFormat);
string dateKey = DateTime.Now.AddDays(-ga.DaysAgo).ToString("yyyyMMdd");
sql = sql.Replace("{date}", dateKey);
var job = await client.CreateQueryJobAsync(sql, new List <BigQueryParameter>());
BigQueryResults results = null;
results = await client.GetQueryResultsAsync(job.Reference, new GetQueryResultsOptions()
{
StartIndex = 0,
PageSize = 20000,
});
var enumerator = results.GetEnumerator();
List <BigQueryRow> rows = new List <BigQueryRow>();
int parquetIndex = 0;
var targetS3 = etlSettings.CreateTargetS3API();
while (enumerator.MoveNext())
{
rows.Add(enumerator.Current);
if (rows.Count >= etlSettings.NumberOfItemsPerParquet)
{
var s3key = etlSettings.MakeTargetS3Key(dateKey, "", false, parquetIndex);
await targetS3.WriteResultRowsToS3Bucket(rows, results, etlSettings, s3key);
result.Add($"s3://{etlSettings.TargetS3BucketName}/{s3key}");
parquetIndex += 1;
}
}
// write what ever left less than 200000
if (rows.Count > 0)
{
var s3key = etlSettings.MakeTargetS3Key(dateKey, "", false, parquetIndex);
await targetS3.WriteResultRowsToS3Bucket(rows, results, etlSettings, s3key);
result.Add($"s3://{etlSettings.TargetS3BucketName}/{s3key}");
parquetIndex += 1;
}
{
// load partition to athena table
await awsAthenaAPI.LoadPartition(
$"`{etlSettings.AthenaDatabaseName}`.`{etlSettings.AthenaTableName}`",
$"`{etlSettings.DatePartitionKey}` = '{dateKey}'",
$"s3://{etlSettings.TargetS3BucketName}/{etlSettings.TargetS3Prefix}/{dateKey}/");
}
return(result);
}
public DamageHistoryRepository(BigQueryClient client, string datasetId)
: base(client, datasetId)
{
}
// [END list_projects]
// [START list_rows]
public int ListRows(
string projectId, string datasetId, string tableId, int numberOfRows,
BigQueryClient client)
{
int recordCount = 0;
var result = client.ListRows(projectId, datasetId, tableId, null,
new ListRowsOptions { PageSize = numberOfRows });
foreach (var row in result.Take(numberOfRows))
{
Console.WriteLine($"{row["word"]}: {row["corpus"]}");
recordCount++;
}
return recordCount;
}
public void PopulateTable(
string query, string datasetId, string newTableId, BigQueryClient client)
{
var destination = client.GetTableReference(datasetId, newTableId);
BigQueryJob job = client.CreateQueryJob(query,
new CreateQueryJobOptions { DestinationTable = destination });
// Wait for the job to complete.
job.PollQueryUntilCompleted();
}
public BigQueryInsertResultsTests()
{
_client = new DerivedBigQueryClient();
}
private BigQueryTable GetTable()
{
var client = BigQueryClient.Create(_fixture.ProjectId);
return(client.GetTable(_fixture.DatasetId, _fixture.ExhaustiveTypesTableId));
}
public LoginRepository(BigQueryClient client, string datasetId)
: base(client, datasetId)
{
}
private void Form1_Load(object sender, EventArgs e)
{
//---------------- GLOBALS ----------------
//Set GOOGLE_APPLICATION_CREDENTIALS env variable so that GoogleCredential.GetApplicationDefault() works
Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", @"..\..\Credentials\key.json");
string PROJECT_ID = "bqconnectordemo";
//Create authenticated client
var credential = GoogleCredential.GetApplicationDefault();
var client = BigQueryClient.Create(PROJECT_ID, credential);
//Set datasetId and tableName variables to identify the dataset and table to interact with (querying or streaming)
string datasetId = "demo";
string tableName = "BitcoinBlockchainMetrics";
//----------------------------------------------
//---------------- QUERY IMPORT ----------------
//Query a table
var table = client.GetTable(PROJECT_ID, datasetId, tableName);
var sql =
$"SELECT * FROM {table} WHERE DATE(DateTime) = '2018-09-17'";
//Execute Query
var results = client.ExecuteQuery(sql, parameters: null);
//Create and populate DataTable
DataTable dt = CreateDataTable(results);
foreach (var row in results)
{
PopulateDataTable(dt, row);
}
//Populate DataGridView
dataGridView1.DataSource = dt;
dataGridView1.Refresh();
//--------------------------------------------------
//---------------- STREAMING EXPORT ----------------
List <BigQueryInsertRow> bQIRows = new List <BigQueryInsertRow>();
string[] columnNames = dt.Columns
.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
var count = 0; //DEMO ONLY!!!
//Build list of rows to stream (bQIRows)
foreach (DataRow dr in dt.Rows)
{
if (count > 10)
{
break;
}
bQIRows.Add(PopulateBigQueryInsertRow(dr, columnNames, client, datasetId, tableName));
count++; //DEMO ONLY!!!
}
//Stream bQIRows up to BigQuery
client.InsertRows(datasetId, tableName, bQIRows);
//---------------------------------------------------
//---------------- MISC EXAMPLE CODE ----------------
//Set query options
//var queryOptions = new QueryOptions
//{
// UseQueryCache = false
//};
//var results = client.ExecuteQuery(sql, parameters: null, queryOptions: queryOptions);
//Console.WriteLine(results.SafeTotalRows);
//var job = client.GetJob(results.JobReference);
//var stats = job.Statistics;
//Console.WriteLine("-----------");
//Console.WriteLine($"Creation time: {stats.CreationTime}");
//Console.WriteLine($"End time: {stats.EndTime}");
//Console.WriteLine($"Total bytes processed: {stats.TotalBytesProcessed}");
}
private static BigQueryInsertRow PopulateBigQueryInsertRow(DataRow dr, string[] cNames, BigQueryClient client, string datasetId, string tableName)
{
var bQIRow = new BigQueryInsertRow();
string type;
foreach (var key in cNames)
{
type = GetBigQueryType(client, datasetId, tableName, key);
if (type == "TIMESTAMP")
{
string value = DateTime.Parse((string)dr[key]).ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss");
bQIRow.Add(key, value);
}
else
{
bQIRow.Add(key, dr[key]);
}
}
return(bQIRow);
}
public UnitMasterRepository(BigQueryClient client, string datasetId, MasterData masterData)
: base(client, datasetId, "unit_master", masterData)
{
}
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);
}
}
// [END async_query]
// [START import_from_file]
public void UploadJsonFromFile(string projectId, string datasetId, string tableId,
string fileName, BigQueryClient client)
{
using (FileStream stream = File.Open(fileName, FileMode.Open))
{
// This example uploads data to an existing table. If the upload will create a new table
// or if the schema in the JSON isn't identical to the schema in the table,
// create a schema to pass into the call instead of passing in a null value.
BigQueryJob job = client.UploadJson(datasetId, tableId, null, stream);
// Use the job to find out when the data has finished being inserted into the table,
// report errors etc.
// Wait for the job to complete.
job.PollUntilCompleted();
}
}
// [END stream_row]
// [START export_to_cloud_storage]
public void ExportJsonToGcs(
string datasetId, string tableId, string bucketName, string fileName,
BigQueryClient client)
{
StorageClient gcsClient = StorageClient.Create();
string contentType = "application/json";
// Get Table and append results into StringBuilder.
PagedEnumerable<TableDataList, BigQueryRow> result = client.ListRows(datasetId, tableId);
StringBuilder sb = new StringBuilder();
foreach (var row in result)
{
sb.Append($"{{\"title\" : \"{row["title"]}\", \"unique_words\":\"{row["unique_words"]}\"}}{Environment.NewLine}");
}
// Save stream to Google Cloud Storage.
using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(sb.ToString())))
{
var obj = gcsClient.UploadObject(bucketName, fileName, contentType, stream);
}
}
public void Constructor_NullParameters_Fail(
BigQueryClient client, InsertOptions options, IReadOnlyList <BigQueryInsertRow> rows, TableDataInsertAllResponse response) =>
Assert.Throws <ArgumentNullException>(() => new BigQueryInsertResults(client, options, rows, response));
// [START list_datasets]
public List<BigQueryDataset> ListDatasets(BigQueryClient client)
{
var datasets = client.ListDatasets().ToList();
return datasets;
}
public Erc721UsageQuery(BigQueryClient client)
{
_client = client;
}
public MatchUseUnitHistoryRepository(BigQueryClient client, string datasetId)
: base(client, datasetId)
{
}
public MatchExecutionHistoryRepository(BigQueryClient client, string datasetId)
: base(client, datasetId)
{
}
// [START delete_table]
public void DeleteTable(string datasetId, string tableId, BigQueryClient client)
{
client.DeleteTable(_projectId, datasetId, tableId);
}
/// <summary>
/// Handle the intent.
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public override async Task <string> HandleAsync(ConvRequest req)
{
// Extract the DialogFlow date, without the time, that has been requested
// Format is "yyyy-mm-dd"
var date = req.Parameters["date"];
date = date.Substring(0, Math.Min(10, date.Length));
// Create the BigQuery client with default credentials
var bigQueryClient = await BigQueryClient.CreateAsync(Program.AppSettings.GoogleCloudSettings.ProjectId);
// Build the parameterized SQL query
var table = bigQueryClient.GetTable("bigquery-public-data", "hacker_news", "full");
var sql = $"SELECT title, url \nFROM {table} \n" +
"WHERE STARTS_WITH(CAST(timestamp AS STRING), @date) AND type=\"story\" \n" +
"ORDER BY score DESC \n" +
"LIMIT 10";
// Create the BigQuery parameters
var parameters = new[]
{
new BigQueryParameter("date", BigQueryDbType.String, date)
};
// Show SQL query in browser
ShowQuery(sql, parameters);
// Time the BigQuery execution with a StopWatch
var stopwatch = new Stopwatch();
stopwatch.Start();
// Execute BigQuery SQL query. This can take time
var result = await bigQueryClient.ExecuteQueryAsync(sql, parameters);
// Query finished, stop the StopWatch
stopwatch.Stop();
// Get a job reference, for statistics
var job = await bigQueryClient.GetJobAsync(result.JobReference);
// Get result list, and check that there are some results
var resultList = result.ToList();
if (resultList.Count == 0)
{
return(DialogflowApp.Tell("Sorry, there is no data for that date."));
}
// Time and data statistics
long processedMb = job.Statistics.TotalBytesProcessed.Value / (1024 * 1024);
double secs = stopwatch.Elapsed.TotalSeconds;
var titles = resultList.Select(x => x["title"].ToString()).ToList();
// Show SQL query and query results in browser
ShowQuery(sql, parameters, (processedMb, secs, titles));
// Send spoken response to DialogFlow
return(DialogflowApp.Tell($"Scanned {processedMb} mega-bytes in {secs:0.0} seconds. " +
$"The top title on hacker news was titled: {titles.First()}"));
}
public TokenUsageQuery(BigQueryClient client)
{
_client = client;
}
public SetOpenTypeRepository(BigQueryClient client, string datasetId)
: base(client, datasetId)
{
}
public static async Task GetBigQueryResultSampleByDate(this EtlSettings etlSettings, int lines)
{
var awsS3Api = etlSettings.CreateTargetS3API();
var ga = etlSettings.GoogleAnalyticsQuerySource;
Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", $"{AppContext.BaseDirectory}/{ga.GoogleAnalyticsSettingFile}");
BigQueryClient client = BigQueryClient.Create(ga.GoogleAnalyticsProjectId);
string sql = ga.BigQuerySQL;
string dateQueryKey = DateTime.Now.AddDays(-ga.DaysAgo).ToString(ga.DateFormat);
// make sure the query is limited by 20
sql = sql.Replace("{date}", dateQueryKey) + $"\nlimit {lines}";
var job = await client.CreateQueryJobAsync(sql, new List <BigQueryParameter>());
BigQueryResults results = null;
results = await client.GetQueryResultsAsync(job.Reference, new GetQueryResultsOptions()
{
StartIndex = 0,
PageSize = 20000,
});
var enumerator = results.GetEnumerator();
List <BigQueryRow> rows = new List <BigQueryRow>();
while (enumerator.MoveNext())
{
rows.Add(enumerator.Current);
}
// map schema to athena types
etlSettings.Mappings = results.ToFieldMappings();
var sample = new DataSample()
{
Rows = new List <DataRow>()
};
// convert big query data to sample data
foreach (var row in rows)
{
sample.Rows.Add(new DataRow()
{
Items = row.RawRow.F.Select(item =>
{
if (item.V == null)
{
return("");
}
else if (item.V.GetType() == typeof(DateTime))
{
return(((DateTime)item.V).ToString("o"));
}
else if (item.V.GetType() == typeof(byte[]))
{
return(Convert.ToBase64String((byte[])item.V));
}
else
{
return(item.V.ToString());
}
}).ToList()
});
}
etlSettings.Sample = sample;
}
public PingResultsRepository(BigQueryClient client, string datasetId)
: base(client, datasetId)
{
}
private Task <BigQueryTable> GetTableAsync(CancellationToken cancellationToken)
{
BigQueryClient client = GetBiqQueryClient();
return(client.GetTableAsync(googleBigQueryAttribute.DatasetId, googleBigQueryAttribute.TableId, null, cancellationToken));
}
public BqCmdlet()
{
_service = new Lazy <BigqueryService>(() => new BigqueryService(GetBaseClientServiceInitializer()));
_client = new Lazy <BigQueryClient>(() => BigQueryClient.Create(Project));
}
// [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);
}
public static object Inspect(
string projectId,
string minLikelihood,
int maxFindings,
bool includeQuote,
IEnumerable <FieldId> identifyingFields,
IEnumerable <InfoType> infoTypes,
IEnumerable <CustomInfoType> customInfoTypes,
string datasetId,
string tableId)
{
var inspectJob = new InspectJobConfig
{
StorageConfig = new StorageConfig
{
BigQueryOptions = new BigQueryOptions
{
TableReference = new Google.Cloud.Dlp.V2.BigQueryTable
{
ProjectId = projectId,
DatasetId = datasetId,
TableId = tableId,
},
IdentifyingFields =
{
identifyingFields
}
},
TimespanConfig = new StorageConfig.Types.TimespanConfig
{
StartTime = Timestamp.FromDateTime(System.DateTime.UtcNow.AddYears(-1)),
EndTime = Timestamp.FromDateTime(System.DateTime.UtcNow)
}
},
InspectConfig = new InspectConfig
{
InfoTypes = { infoTypes },
CustomInfoTypes = { customInfoTypes },
Limits = new FindingLimits
{
MaxFindingsPerRequest = maxFindings
},
ExcludeInfoTypes = false,
IncludeQuote = includeQuote,
MinLikelihood = (Likelihood)System.Enum.Parse(typeof(Likelihood), minLikelihood)
},
Actions =
{
new Google.Cloud.Dlp.V2.Action
{
// Save results in BigQuery Table
SaveFindings = new Google.Cloud.Dlp.V2.Action.Types.SaveFindings
{
OutputConfig = new OutputStorageConfig
{
Table = new Google.Cloud.Dlp.V2.BigQueryTable
{
ProjectId = projectId,
DatasetId = datasetId,
TableId = tableId
}
}
},
}
}
};
// Issue Create Dlp Job Request
var client = DlpServiceClient.Create();
var request = new CreateDlpJobRequest
{
InspectJob = inspectJob,
ParentAsProjectName = new ProjectName(projectId),
};
// We need created job name
var dlpJob = client.CreateDlpJob(request);
var jobName = dlpJob.Name;
// Make sure the job finishes before inspecting the results.
// Alternatively, we can inspect results opportunistically, but
// for testing purposes, we want consistent outcome
var finishedJob = EnsureJobFinishes(projectId, jobName);
var bigQueryClient = BigQueryClient.Create(projectId);
var table = bigQueryClient.GetTable(datasetId, tableId);
// Return only first page of 10 rows
Console.WriteLine("DLP v2 Results:");
var firstPage = table.ListRows(new ListRowsOptions {
StartIndex = 0, PageSize = 10
});
foreach (var item in firstPage)
{
Console.WriteLine($"\t {item[""]}");
}
return(finishedJob);
}
// [END import_from_file]
// [START stream_row]
public void UploadJson(string datasetId, string tableId, BigQueryClient client)
{
// Note that there's a single line per JSON object. This is not a JSON array.
IEnumerable<string> jsonRows = new string[]
{
"{ 'title': 'exampleJsonFromStream', 'unique_words': 1}",
"{ 'title': 'moreExampleJsonFromStream', 'unique_words': 1}",
//add more rows here...
}.Select(row => row.Replace('\'', '"')); // Simple way of representing C# in JSON to avoid escaping " everywhere.
// Normally we'd be uploading from a file or similar. Any readable stream can be used.
var stream = new MemoryStream(Encoding.UTF8.GetBytes(string.Join("\n", jsonRows)));
// This example uploads data to an existing table. If the upload will create a new table
// or if the schema in the JSON isn't identical to the schema in the table,
// create a schema to pass into the call instead of passing in a null value.
BigQueryJob job = client.UploadJson(datasetId, tableId, null, stream);
// Use the job to find out when the data has finished being inserted into the table,
// report errors etc.
// Wait for the job to complete.
job.PollUntilCompleted();
}
private static BigQueryClient CreateBigQueryClient(string projectId, string serviceKeyFile) =>
string.IsNullOrEmpty(serviceKeyFile)
? BigQueryClient.Create(projectId)
: BigQueryClient.Create(projectId, GoogleCredential.FromFile(serviceKeyFile));
// [END export_to_cloud_storage]
public void ExportCsvToGcs(
string datasetId, string tableId, string bucketName, string fileName, BigQueryClient client)
{
StorageClient gcsClient = StorageClient.Create();
string contentType = "text/csv";
// Get Table and input results into StringBuilder.
var result = client.ListRows(datasetId, tableId);
StringBuilder sb = new StringBuilder();
// Create header row.
sb.Append($"title, unique_words,{Environment.NewLine}");
foreach (var row in result)
{
sb.Append($"{row["title"]}, {row["unique_words"]},{Environment.NewLine}");
}
// Save stream to Google Cloud Storage.
using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(sb.ToString())))
{
var obj = gcsClient.UploadObject(bucketName, fileName, contentType, stream);
}
}
// [START create_dataset]
public void CreateDataset(string datasetId, BigQueryClient client)
{
var dataset = client.GetOrCreateDataset(datasetId);
}
// [START copy_table]
public void CopyTable(
string datasetId, string tableIdToBeCopied, string newTableId, BigQueryClient client)
{
var table = client.GetTable(datasetId, tableIdToBeCopied);
string query = $"SELECT * FROM {table}";
var destination = client.GetTableReference(datasetId, newTableId);
BigQueryJob job = client.CreateQueryJob(query,
new CreateQueryJobOptions { DestinationTable = destination });
// Wait for the job to complete.
job.PollQueryUntilCompleted();
}
// [END create_table]
public void DeleteDataset(string datasetId, BigQueryClient client)
{
_retryDeleteBusy.Eventually(() => client.DeleteDataset(datasetId));
}
// [END list_datasets]
// [START list_projects]
public List<CloudProject> ListProjects(BigQueryClient client)
{
var projects = client.ListProjects().ToList();
return projects;
}
// [START delete_table]
public void DeleteTable(string datasetId, string tableId, BigQueryClient client)
{
client.DeleteTable(_projectId, datasetId, tableId);
}
// [END list_rows]
// [START browse_table]
public int TableDataList(
string datasetId, string tableId, int pageSize, BigQueryClient client)
{
int recordCount = 0;
var result = client.ListRows(datasetId, tableId, null,
new ListRowsOptions { PageSize = pageSize });
// If there are more rows than were returned in the first page of results,
// iterating over the rows will lazily evaluate the results each time,
// making further requests as necessary.
foreach (var row in result)
{
Console.WriteLine($"{row["title"]}: {row["unique_words"]}");
recordCount++;
}
return recordCount;
}
// [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,
parameters: null,
options: 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);
}
// [START create_dataset]
public void CreateDataset(string datasetId, BigQueryClient client)
{
var dataset = client.GetOrCreateDataset(datasetId);
}
// [START list_datasets]
public List <BigQueryDataset> ListDatasets(BigQueryClient client)
{
var datasets = client.ListDatasets().ToList();
return(datasets);
}
// [END create_table]
public void DeleteDataset(string datasetId, BigQueryClient client)
{
_retryDeleteBusy.Eventually(() => client.DeleteDataset(datasetId));
}
// [END list_datasets]
// [START list_projects]
public List <CloudProject> ListProjects(BigQueryClient client)
{
var projects = client.ListProjects().ToList();
return(projects);
}
// [END delete_table]
// [START import_file_from_gcs]
public void ImportDataFromCloudStorage(string projectId, string datasetId,
string tableId, BigQueryClient client, string fileName, string folder = null)
{
StorageClient gcsClient = StorageClient.Create();
using (var stream = new MemoryStream())
{
// Set Cloud Storage Bucket name. This uses a bucket named the same as the project.
string bucket = projectId;
// If folder is passed in, add it to Cloud Storage File Path using "/" character
string filePath = string.IsNullOrEmpty(folder) ? fileName : folder + "/" + fileName;
// Download Google Cloud Storage object into stream
gcsClient.DownloadObject(projectId, filePath, stream);
// This example uploads data to an existing table. If the upload will create a new table
// or if the schema in the JSON isn't identical to the schema in the table,
// create a schema to pass into the call instead of passing in a null value.
BigQueryJob job = client.UploadJson(datasetId, tableId, null, stream);
// Use the job to find out when the data has finished being inserted into the table,
// report errors etc.
// Wait for the job to complete.
job.PollUntilCompleted();
}
}
/// <summary>
/// Handle the intent.
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public override async Task <string> HandleAsync(ConvRequest req)
{
var errorMessage = ExtractAndValidateParameters(req, out string temp, out string year, out string countryCode2,
out string countryName, out string fipsCountry);
if (errorMessage != null)
{
return(DialogflowApp.Tell(errorMessage));
}
var bigQueryClient = await BigQueryClient.CreateAsync(Program.AppSettings.GoogleCloudSettings.ProjectId);
// Build the parameterized SQL query
var tableGsod = bigQueryClient.GetTable("bigquery-public-data", "noaa_gsod", $"gsod*");
var tableStations = bigQueryClient.GetTable("bigquery-public-data", "noaa_gsod", "stations");
var sql = $"SELECT (gsod.temp - 32)*5/8 AS celcius, stations.name AS name, gsod.year AS y, gsod.mo AS m, gsod.da AS d \n" +
$"FROM {tableGsod} AS gsod \n" +
$"JOIN {tableStations} AS stations ON gsod.stn = stations.usaf AND gsod.wban = stations.wban \n" +
$"WHERE stations.country=@fipsCountry and gsod.year=@year \n" +
$"ORDER BY gsod.temp{(temp == "hottest" ? " DESC" : "")} \n" +
"limit 10";
// Create the BigQuery parameters
var parameters = new[]
{
new BigQueryParameter("fipsCountry", BigQueryDbType.String, fipsCountry),
new BigQueryParameter("year", BigQueryDbType.String, year)
};
// Show SQL query in browser
ShowQuery(sql, parameters);
// Time the BigQuery execution with a StopWatch
var stopwatch = new Stopwatch();
stopwatch.Start();
// Execute BigQuery SQL query. This can take time
var result = await bigQueryClient.ExecuteQueryAsync(sql, parameters);
// Query finished, stop the StopWatch
stopwatch.Stop();
// Get a job reference, for statistics
var job = await bigQueryClient.GetJobAsync(result.JobReference);
// Get result list, and check that there are some results
var resultList = result.ToList();
if (resultList.Count == 0)
{
return(DialogflowApp.Tell($"Sorry, there is no data for country '{countryName}'"));
}
// Time and data statistics
long processedMb = job.Statistics.TotalBytesProcessed.Value / (1024 * 1024);
double secs = stopwatch.Elapsed.TotalSeconds;
var temperatures = resultList
.Select(x => $"{(double)x["celcius"]:0.0}°C at {x["name"]} on {x["y"]}-{x["m"]}-{x["d"]}")
.ToList();
// Show SQL query and query results in browser
ShowQuery(sql, parameters, (processedMb, secs, temperatures));
// Send spoken response to DialogFlow
var top = resultList[0];
return(DialogflowApp.Tell($"Scanned {processedMb} mega-bytes in {secs:0.0} seconds. " +
$"The {temp} temperature in {countryName} in the year {year} was " +
$"{(double)top["celcius"]:0.0} degrees celcius, at the {top["name"]} monitoring station."));
}
