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}");
}
/// <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.CreateDataset(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]
}
/// <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.CreateDataset(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]
}
public void PropertiesSetOnRequest()
{
var options = new CreateLoadJobOptions
{
AllowJaggedRows = true,
AllowQuotedNewlines = true,
Autodetect = false,
CreateDisposition = CreateDisposition.CreateIfNeeded,
FieldDelimiter = "/",
IgnoreUnknownValues = true,
MaxBadRecords = 100,
NullMarker = "null",
ProjectionFields = new[] { "foo", "bar" },
Quote = "q",
SkipLeadingRows = 10,
SourceFormat = FileFormat.DatastoreBackup,
TimePartitioning = TimePartition.CreateDailyPartitioning(expiration: null),
WriteDisposition = WriteDisposition.WriteAppend,
DestinationEncryptionConfiguration = new EncryptionConfiguration {
KmsKeyName = "projects/1/locations/us/keyRings/1/cryptoKeys/1"
},
DestinationSchemaUpdateOptions = SchemaUpdateOption.AllowFieldAddition | SchemaUpdateOption.AllowFieldRelaxation,
UseAvroLogicalTypes = true
};
JobConfigurationLoad load = new JobConfigurationLoad();
options.ModifyRequest(load);
Assert.Equal(true, load.AllowJaggedRows);
Assert.Equal(true, load.AllowQuotedNewlines);
Assert.Equal(false, load.Autodetect);
Assert.Equal("CREATE_IF_NEEDED", load.CreateDisposition);
Assert.Equal("/", load.FieldDelimiter);
Assert.Equal(true, load.IgnoreUnknownValues);
Assert.Equal(100, load.MaxBadRecords);
Assert.Equal("null", load.NullMarker);
Assert.Equal(new[] { "foo", "bar" }, load.ProjectionFields);
Assert.Equal("q", load.Quote);
Assert.Equal(10, load.SkipLeadingRows);
Assert.Equal("DATASTORE_BACKUP", load.SourceFormat);
Assert.Equal("WRITE_APPEND", load.WriteDisposition);
Assert.Equal("DAY", load.TimePartitioning.Type);
Assert.Null(load.TimePartitioning.ExpirationMs);
Assert.Equal("projects/1/locations/us/keyRings/1/cryptoKeys/1", load.DestinationEncryptionConfiguration.KmsKeyName);
Assert.Contains("ALLOW_FIELD_ADDITION", load.SchemaUpdateOptions);
Assert.Contains("ALLOW_FIELD_RELAXATION", load.SchemaUpdateOptions);
Assert.True(load.UseAvroLogicalTypes);
}
/// <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 void CreateLoadJob_Parquet()
{
string sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.parquet";
var client = BigQueryClient.Create(_fixture.ProjectId);
var tableId = _fixture.CreateTableId();
var tableRef = client.GetTableReference(_fixture.DatasetId, tableId);
var options = new CreateLoadJobOptions {
SourceFormat = FileFormat.Parquet
};
var job = client.CreateLoadJob(sourceUri, tableRef, schema: null, options: options);
job.PollUntilCompleted().ThrowOnAnyError();
var table = client.GetTable(tableRef);
var rows = table.ListRows().ToList();
Assert.Equal(50, rows.Count);
}
public void PropertiesSetOnRequest()
{
var options = new CreateLoadJobOptions
{
AllowJaggedRows = true,
AllowQuotedNewlines = true,
Autodetect = false,
CreateDisposition = CreateDisposition.CreateIfNeeded,
FieldDelimiter = "/",
IgnoreUnknownValues = true,
MaxBadRecords = 100,
NullMarker = "null",
ProjectionFields = new[] { "foo", "bar" },
Quote = "q",
SkipLeadingRows = 10,
SourceFormat = FileFormat.DatastoreBackup,
TimePartitioning = TimePartition.CreateDailyPartitioning(expiration: null),
WriteDisposition = WriteDisposition.WriteAppend
};
JobConfigurationLoad load = new JobConfigurationLoad();
options.ModifyRequest(load);
Assert.Equal(true, load.AllowJaggedRows);
Assert.Equal(true, load.AllowQuotedNewlines);
Assert.Equal(false, load.Autodetect);
Assert.Equal("CREATE_IF_NEEDED", load.CreateDisposition);
Assert.Equal("/", load.FieldDelimiter);
Assert.Equal(true, load.IgnoreUnknownValues);
Assert.Equal(100, load.MaxBadRecords);
Assert.Equal("null", load.NullMarker);
Assert.Equal(new[] { "foo", "bar" }, load.ProjectionFields);
Assert.Equal("q", load.Quote);
Assert.Equal(10, load.SkipLeadingRows);
Assert.Equal("DATASTORE_BACKUP", load.SourceFormat);
Assert.Equal("WRITE_APPEND", load.WriteDisposition);
Assert.Equal("DAY", load.TimePartitioning.Type);
Assert.Null(load.TimePartitioning.ExpirationMs);
}
static void LoadDataToBigQuery()
{
var gcsUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
var client = BigQueryClient.Create(PROJECT_ID);
var dataset = client.GetOrCreateDataset("us_states_dataset");
var schema = new TableSchemaBuilder
{
{ "name", BigQueryDbType.String },
{ "post_abbr", BigQueryDbType.String }
}.Build();
var jobOptions = new CreateLoadJobOptions
{
SourceFormat = FileFormat.NewlineDelimitedJson
};
var table = dataset.GetTableReference("us_states_table");
var loadJob = client.CreateLoadJob(gcsUri, table, schema, jobOptions);
loadJob.PollUntilCompleted();
loadJob.ThrowOnAnyError();
Console.WriteLine("Json file loaded to BigQuery");
}
static void Main(string[] args)
{
// -------------------------------------------- Setup GCP Credentials --------------------------------------------
// Replace with path to wherever Auth<name>.json file is on your local machine
string pathToCreds = "/Users/devin/Documents/GitHub/FrankFund/Credentials/AuthDevin.json";
// Once set you can make calls to the GCP API
System.Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", pathToCreds);
// -------------------------------------------- BigQuery Query Example --------------------------------------------
// Relevant Docs: https://cloud.google.com/bigquery/docs/samples/bigquery-query#bigquery_query-csharp
var projectID = "frankfund"; // GCP project id is always lowercase
var datasetID = "FrankFund";
var tableName = "Accounts";
var client = BigQueryClient.Create(projectID);
var table = client.GetTable(datasetID, tableName);
var query = $"SELECT * FROM {projectID}.{datasetID}.{tableName} ORDER BY AccountID DESC"; // Table selection is always in the form: projectID.datasetID.tableName
var queryResults = client.ExecuteQuery(query, parameters: null);
Console.WriteLine("Executing query: " + query);
/*
* Console Output:
* Executing query: SELECT * FROM frankfund.FrankFund.Accounts ORDER BY AccountID DESC
*
* Query Results:
* 4, KennethTran, [email protected]
* 3, RachelPai, [email protected]
* 2, DevinSuy, [email protected]
* 1, AutumnNguyen, [email protected]
*/
Console.WriteLine("\nQuery Results:");
foreach (BigQueryRow row in queryResults)
{
Console.WriteLine($" {row["AccountID"]}, {row["AccountUsername"]}, {row["EmailAddress"]}"); // Iterate over result set and access current row with row["columnName"]
}
// -------------------------------------------- BigQuery Insert Data From .json Stored In Cloud Storage Example --------------------------------------------
// Relevant Docs: https://cloud.google.com/bigquery/docs/samples/bigquery-load-table-gcs-json
projectID = "frankfund"; // GCP project id is always lowercase
datasetID = "FrankFund";
tableName = "Accounts";
var schema = new TableSchemaBuilder {
{ "AccountID", BigQueryDbType.String },
{ "AccountUsername", BigQueryDbType.String },
{ "EmailAddress", BigQueryDbType.String },
{ "Password", BigQueryDbType.String },
{ "FacebookID", BigQueryDbType.Numeric },
{ "GoogleID", BigQueryDbType.Numeric }
}.Build();
var jobOptions = new CreateLoadJobOptions {
SourceFormat = FileFormat.NewlineDelimitedJson // NOTE: GCP only accepts single line per object for .json format
};
client = BigQueryClient.Create(projectID);
var dataset = client.GetDataset(datasetID);
var tableRef = dataset.GetTableReference(tableName);
var cloudStorageURI = "gs://frankfund_sandbox/exampleAccountInsert.json"; // Cloud Storage URI format: gs://<bucket_name>/<file_path_inside_bucket>
var loadJob = client.CreateLoadJob(cloudStorageURI, tableRef, schema, jobOptions);
try{
loadJob.PollUntilCompleted();
Console.WriteLine("Json file loaded to BigQuery");
}
catch (Google.GoogleApiException e) {
Console.WriteLine(e.Message);
}
// -------------------------------------------- Cloud Storage File Retrieval Example --------------------------------------------
// Relevant Docs:
// https://googleapis.github.io/google-cloud-dotnet/docs/Google.Cloud.Storage.V1/index.html
// https://docs.microsoft.com/en-us/dotnet/api/system.io.filestream?view=net-5.0
// Useful reference for future: https://medium.com/net-core/using-google-cloud-storage-in-asp-net-core-74f9c5ee55f5
projectID = "frankfund";
var bucketName = "frankfund_sandbox";
var fileName = "team_members.txt";
var storageClient = StorageClient.Create();
// Download file from cloud storage bucket to local
using (var stream = File.OpenWrite(fileName)){
storageClient.DownloadObject(bucketName, fileName, stream);
}
/* Console Output From Downloaded File:
* Team Frank: Autumn Nguyen, Devin Suy, Kenneth Tran, Rachel Pai
*/
foreach (string line in File.ReadAllLines(fileName))
{
Console.WriteLine(line);
}
}
