public void TestImportFromCloudStorage()
{
string datasetId = "datasetForTestImportFromCloudStorage";
string newTableId = "tableForTestImportFromCloudStorage";
string jsonGcsSampleFile = "sample.json";
string gcsFolder = "test";
string gcsUploadTestWord = "exampleJsonFromGCS";
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}";
BigQueryResults results = AsyncQuery(_projectId, datasetId, newTableId,
query, _client);
// Get first row and confirm it contains the expected value.
var row = results.GetRows().First();
Assert.Equal(gcsUploadTestWord, row["title"]);
DeleteTable(datasetId, newTableId, _client);
DeleteDataset(datasetId, _client);
}
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.GetRows().Count() > 0);
}
public void TestLegacySqlSyncQuery()
{
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 = LegacySqlSyncQuery(
projectId, datasetId, tableId, query, 10000, _client);
Assert.True(results.GetRows().Count() > 0);
}
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}";
BigQueryResults results = AsyncQuery(_projectId, datasetId, newTableId, query, _client);
var row = results.GetRows().First();
Assert.Equal(gcsUploadTestWord, row["title"]);
DeleteTable(datasetId, newTableId, _client);
DeleteDataset(datasetId, _client);
}
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.GetRows().Last();
Assert.Equal(uploadTestWordValue, row["unique_words"]);
DeleteTable(datasetId, newTableId, _client);
DeleteDataset(datasetId, _client);
}
protected override void ProcessRecord()
{
// Set Project for the lazy instantiation of a BQ Client object.
Project = InputObject.ProjectId;
var options = new GetQueryResultsOptions {
Timeout = new TimeSpan(0, 0, (Timeout < 10) ? 10 : Timeout)
};
try
{
BigQueryResults result = Client.GetQueryResults(InputObject, options);
if (result == null)
{
throw new Exception("Server response came back as null.");
}
WriteObject(result.GetRows(), true);
}
catch (Exception ex)
{
ThrowTerminatingError(new ErrorRecord(ex, "Failed to receive results.",
ErrorCategory.InvalidOperation, this));
}
}
public static void Main(string[] args)
{
//format should be YYMM-comments.json"
Stack <string> dbDates = new Stack <string>(new List <string>()
{
"16_07",
"16_08",
"16_09",
"16_10",
"16_11",
"16_12",
"17_01",
"17_02",
"17_03",
"17_04",
"17_05",
"17_06",
});
string dbDate = "17_06";
string subreddit = "Seattle";
string dataset = "posts";
//string dataset = "comments";
BigQueryClient client = BigQueryClient.Create("aaaa-153204");
ProjectReference pr = client.GetProjectReference("fh-bigquery");
DatasetReference dr = new DatasetReference()
{
DatasetId = $"reddit_{dataset}", ProjectId = pr.ProjectId
};
JsonSerializerSettings serializerSettings = new JsonSerializerSettings()
{
NullValueHandling = NullValueHandling.Include
};
while (dbDates.Count > 0)
{
dbDate = dbDates.Pop();
string fileName = $"{dbDate.Replace("_", "")}-{dataset}.json";
string query = $@"SELECT * FROM `fh-bigquery.reddit_{dataset}.20{dbDate}` WHERE subreddit = '{subreddit}'";
BigQueryResults result =
client.ExecuteQuery
//(@"SELECT count(1) FROM `fh-bigquery.reddit_comments.2016_11` WHERE subreddit = 'SeattleWA'",
(query,
new ExecuteQueryOptions()
{
DefaultDataset = new DatasetReference()
{
DatasetId = $"reddit_{dataset}",
ProjectId = "fh-bigquery"
}
});
//, new ExecuteQueryOptions() { UseQueryCache = true, DefaultDataset = new DatasetReference() { ProjectId = "fh-bigquery", DatasetId = "reddit_comments" } });
LinkedList <string> rows = new LinkedList <string>();
while (!result.Completed)
{
Console.WriteLine("Polling for completed query");
result = result.PollUntilCompleted();
}
foreach (BigQueryRow row in result.GetRows(new Google.Api.Gax.PollSettings(
Google.Api.Gax.Expiration.None, new TimeSpan(0, 0, 15))))
{
if (dataset == "comments")
{
object rowObj = new
{
body = row["body"],
score_hidden = (bool?)row["score_hidden"],
archived = (bool?)row["archived"],
name = row["name"],
author = row["author"],
author_flair_text = row["author_flair_text"],
downs = (long?)row["downs"],
created_utc = (long?)row["created_utc"],
subreddit_id = row["subreddit_id"],
link_id = row["link_id"],
parent_id = row["parent_id"],
score = (long?)row["score"],
retrieved_on = (long?)row["retrieved_on"],
controversiality = (long?)row["controversiality"],
gilded = (long?)row["gilded"],
id = row["id"],
subreddit = row["subreddit"],
ups = (long?)row["ups"],
distinguished = row["distinguished"],
author_flair_css_class = row["author_flair_css_class"]
};
rows.AddLast(
JsonConvert.SerializeObject(rowObj,
Formatting.None,
serializerSettings
));
}
else
{
object rowPostObj = new
{
created_utc = (long?)row["created_utc"],
subreddit = row["subreddit"],
author = row["author"],
domain = row["domain"],
url = row["url"],
num_comments = (long?)row["num_comments"],
score = (long?)row["score"],
ups = (long?)row["ups"],
downs = (long?)row["downs"],
title = row["title"],
selftext = row["selftext"],
saved = (bool?)row["saved"],
id = row["id"],
from_kind = row["from_kind"],
gilded = (long?)row["gilded"],
from = row["from"],
stickied = (bool?)row["stickied"],
retrieved_on = (long?)row["retrieved_on"],
over_18 = (bool?)row["over_18"],
thumbnail = row["thumbnail"],
subreddit_id = row["subreddit_id"],
hide_score = (bool?)row["hide_score"],
link_flair_css_class = row["link_flair_css_class"],
author_flair_css_class = row["author_flair_css_class"],
archived = (bool?)row["archived"],
is_self = (bool?)row["is_self"],
from_id = row["from_id"],
permalink = row["permalink"],
name = row["name"],
author_flair_text = row["author_flair_text"],
quarantine = (bool?)row["quarantine"],
link_flair_text = row["link_flair_text"],
distinguished = row["distinguished"]
};
rows.AddLast(
JsonConvert.SerializeObject(rowPostObj,
Formatting.None,
serializerSettings
));
}
// string.Join(",", row.RawRow.F.Select(x => x.V != null ? x.V.ToString(): "null"))
//);
}
System.IO.File.WriteAllLines(@"D:\dev\data\" + dataset + "\\" + subreddit + "\\" + fileName, rows);
}
Console.WriteLine("Complete");
Console.ReadLine();
}