public void LoadTableGcsOrcTruncate(
string projectId = "your-project-id",
string datasetId = "your_dataset_id",
string tableId = "your_table_id"
)
{
BigQueryClient client = BigQueryClient.Create(projectId);
var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.orc";
var dataset = client.GetDataset(datasetId);
TableReference destinationTableRef = dataset.GetTableReference(
tableId: "us_states");
// Create job configuration
var jobOptions = new CreateLoadJobOptions()
{
SourceFormat = FileFormat.Orc,
WriteDisposition = WriteDisposition.WriteTruncate
};
// Create and run job
var loadJob = client.CreateLoadJob(
sourceUri: gcsURI,
destination: destinationTableRef,
// Pass null as the schema because the schema is inferred when
// loading Orc data
schema: null, options: jobOptions);
loadJob.PollUntilCompleted(); // Waits for the job to complete.
// Display the number of rows uploaded
BigQueryTable table = client.GetTable(destinationTableRef);
Console.WriteLine(
$"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
}
public void LoadTableGcsJson(
string projectId = "your-project-id",
string datasetId = "your_dataset_id"
)
{
BigQueryClient client = BigQueryClient.Create(projectId);
var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
var dataset = client.GetDataset(datasetId);
var schema = new TableSchemaBuilder {
{ "name", BigQueryDbType.String },
{ "post_abbr", BigQueryDbType.String }
}.Build();
TableReference destinationTableRef = dataset.GetTableReference(
tableId: "us_states");
// Create job configuration
var jobOptions = new CreateLoadJobOptions()
{
SourceFormat = FileFormat.NewlineDelimitedJson
};
// Create and run job
BigQueryJob loadJob = client.CreateLoadJob(
sourceUri: gcsURI, destination: destinationTableRef,
schema: schema, options: jobOptions);
loadJob.PollUntilCompleted(); // Waits for the job to complete.
// Display the number of rows uploaded
BigQueryTable table = client.GetTable(destinationTableRef);
Console.WriteLine(
$"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
}
public void LoadTableGcsCsv(
string projectId = "your-project-id",
string datasetId = "your_dataset_id"
)
{
BigQueryClient client = BigQueryClient.Create(projectId);
var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
var dataset = client.GetDataset(datasetId);
var schema = new TableSchemaBuilder {
{ "name", BigQueryDbType.String },
{ "post_abbr", BigQueryDbType.String }
}.Build();
var destinationTableRef = dataset.GetTableReference(
tableId: "us_states");
// Create job configuration
var jobOptions = new CreateLoadJobOptions()
{
// The source format defaults to CSV; line below is optional.
SourceFormat = FileFormat.Csv,
SkipLeadingRows = 1
};
// Create and run job
var loadJob = client.CreateLoadJob(
sourceUri: gcsURI, destination: destinationTableRef,
schema: schema, options: jobOptions);
loadJob = loadJob.PollUntilCompleted().ThrowOnAnyError(); // Waits for the job to complete.
// Display the number of rows uploaded
BigQueryTable table = client.GetTable(destinationTableRef);
Console.WriteLine(
$"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
}
// [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);
}
// [END bigquery_create_dataset]
// [START bigquery_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 BigQueryTable CreateTable(
string projectId = "your-project-id",
string datasetId = "your_dataset_id"
)
{
BigQueryClient client = BigQueryClient.Create(projectId);
var dataset = client.GetDataset(datasetId);
// Create schema for new table.
var schema = new TableSchemaBuilder
{
{ "full_name", BigQueryDbType.String },
{ "age", BigQueryDbType.Int64 }
}.Build();
// Create the table
return(dataset.CreateTable(tableId: "your_table_id", schema: schema));
}
/// <summary>
/// Imports data from an Orc source
/// </summary>
/// <param name="datasetId">Dataset identifier.</param>
/// <param name="tableId">Table identifier.</param>
/// <param name="client">A BigQuery client.</param>
public static void LoadTableFromOrc(string datasetId,
string tableId, BigQueryClient client)
{
// [START bigquery_load_table_gcs_orc]
var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.orc";
var dataset = client.GetDataset(datasetId);
var jobOptions = new CreateLoadJobOptions()
{
SourceFormat = FileFormat.Orc
};
// Pass null as the schema because the schema is inferred when
// loading Orc data
var loadJob = client.CreateLoadJob(gcsURI, dataset.GetTableReference(tableId),
null, jobOptions);
loadJob.PollUntilCompleted();
// [END bigquery_load_table_gcs_orc]
}
public BigQueryMessageRespository(ILogger <BigQueryMessageRespository> logger)
{
// Get projectId fron config
string googleCredentialsText = File.ReadAllText(Environment.GetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS"));
JObject googleCredentials = JObject.Parse(googleCredentialsText);
string projectId = googleCredentials["project_id"].ToString();
// Get Dataset Name and Table Name
string datasetName = Environment.GetEnvironmentVariable("DATASET_NAME");
string tableName = Environment.GetEnvironmentVariable("MESSAGE_TABLE_NAME");
// Get Table and Client
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryDataset dataset = client.GetDataset(datasetName);
_logger = logger;
_table = dataset.GetTable(tableName);
_logger.LogInformation("GCP Information set. projectId: {projectId} datasetName: {datasetName},tableName:{tableName}, ", projectId, datasetName, tableName);
}
/// <summary>
/// Loads the table from a JSON source.
/// </summary>
/// <param name="client">BigQuery client</param>
/// <param name="datasetId"></param>
/// <param name="tableId"></param>
public static void LoadTableFromJSON(BigQueryClient client,
string datasetId, string tableId)
{
// [START bigquery_load_table_gcs_json]
var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
var dataset = client.GetDataset(datasetId);
var schema = new TableSchemaBuilder {
{ "name", BigQueryDbType.String },
{ "post_abbr", BigQueryDbType.String }
}.Build();
var jobOptions = new CreateLoadJobOptions()
{
SourceFormat = FileFormat.NewlineDelimitedJson
};
var loadJob = client.CreateLoadJob(gcsURI, dataset.GetTableReference(tableId),
schema, jobOptions);
loadJob.PollUntilCompleted();
// [END bigquery_load_table_gcs_json]
}
/// <summary>
/// Imports data from a CSV source
/// </summary>
/// <param name="datasetId">Dataset identifier.</param>
/// <param name="tableId">Table identifier.</param>
/// <param name="client">A BigQuery client.</param>
public static void LoadTableFromCSV(string datasetId,
string tableId, BigQueryClient client)
{
// [START bigquery_load_table_gcs_csv]
var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
var dataset = client.GetDataset(datasetId);
var schema = new TableSchemaBuilder {
{ "name", BigQueryDbType.String },
{ "post_abbr", BigQueryDbType.String }
}.Build();
var jobOptions = new CreateLoadJobOptions()
{
// The source format defaults to CSV; line below is optional.
SourceFormat = FileFormat.Csv,
SkipLeadingRows = 1
};
var loadJob = client.CreateLoadJob(gcsURI, dataset.GetTableReference(tableId),
schema, jobOptions);
loadJob.PollUntilCompleted();
// [END bigquery_load_table_gcs_csv]
}
public void TestQuickStartConsoleApp()
{
string output;
string filePath = "..\\..\\..\\Quickstart\\bin\\Debug\\QuickStart.exe";
string expectedOutputFirstWord = "Dataset";
string expectedOutputLastWord = "created.";
string sampleDatasetUsedInQuickStart = "my_new_dataset";
output = GetConsoleAppOutput(filePath).Trim();
var outputParts = output.Split(new[] { ' ' });
Assert.Equal(outputParts.First(), expectedOutputFirstWord);
Assert.Equal(outputParts.Last(), expectedOutputLastWord);
//Delete QuickStart testing Dataset if it exists
var dataset = _client.GetDataset(sampleDatasetUsedInQuickStart);
if (dataset != null)
{
DeleteDataset(sampleDatasetUsedInQuickStart, _client);
}
;
}
