public void LoadFromFile( string projectId = "your-project-id", string datasetId = "your_dataset_id", string tableId = "your_table_id", string filePath = "path/to/file.csv" ) { BigQueryClient client = BigQueryClient.Create(projectId); // Create job configuration var uploadCsvOptions = new UploadCsvOptions() { SkipLeadingRows = 1, // Skips the file headers Autodetect = true }; using (FileStream stream = File.Open(filePath, FileMode.Open)) { // Create and run job // Note that there are methods available for formats other than CSV BigQueryJob job = client.UploadCsv( datasetId, tableId, null, stream, uploadCsvOptions); job = job.PollUntilCompleted().ThrowOnAnyError(); // Waits for the job to complete. // Display the number of rows uploaded BigQueryTable table = client.GetTable(datasetId, tableId); 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}"); }
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 CopyTable( string projectId = "your-project-id", string destinationDatasetId = "your_dataset_id" ) { BigQueryClient client = BigQueryClient.Create(projectId); TableReference sourceTableRef = new TableReference() { TableId = "shakespeare", DatasetId = "samples", ProjectId = "bigquery-public-data" }; TableReference destinationTableRef = client.GetTableReference( destinationDatasetId, "destination_table"); BigQueryJob job = client.CreateCopyJob( sourceTableRef, destinationTableRef) .PollUntilCompleted() // Wait for the job to complete. .ThrowOnAnyError(); // Retrieve destination table BigQueryTable destinationTable = client.GetTable(destinationTableRef); Console.WriteLine( $"Copied {destinationTable.Resource.NumRows} rows from table " + $"{sourceTableRef.DatasetId}.{sourceTableRef.TableId} " + $"to {destinationTable.FullyQualifiedId}." ); }
public void Patch() { string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = _fixture.GenerateTableId(); BigQueryClient.Create(projectId).CreateTable(datasetId, tableId, new TableSchema()); // Snippet: Patch(Table, bool, *) BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable dataset = client.GetTable(datasetId, tableId); // There's no ETag in this Table. The matchETag parameter in the method call // determines whether the ETag in the original resource is propagated into the // patch. Table patch = new Table { FriendlyName = "Patched table" }; BigQueryTable updated = dataset.Patch(patch, matchETag: true); Console.WriteLine($"Patched table friendly name: {updated.Resource.FriendlyName}"); // End snippet Assert.Equal("Patched table", updated.Resource.FriendlyName); }
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 ListJobs_FilterByLabels() { string bucketName = _fixture.StorageBucketName; string objectName = _fixture.GenerateStorageObjectName(); string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = _fixture.HistoryTableId; // Snippet: Labels IDictionary <string, string> labels = new Dictionary <string, string>() { { "label-key", "label-value" } }; BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(projectId, datasetId, tableId); string destinationUri = $"gs://{bucketName}/{objectName}"; // Just a couple examples of jobs marked with labels: // (These jobs will most certainly be created somewhere else.) // Running a query on a given table. BigQueryJob oneLabeledJob = client.CreateQueryJob( $"SELECT * FROM {table}", null, new QueryOptions { Labels = labels }); // Extracting data from a table to GCS. BigQueryJob anotherLabeledJob = client.CreateExtractJob( projectId, datasetId, tableId, destinationUri, new CreateExtractJobOptions { Labels = labels }); // Find jobs marked with a certain label. KeyValuePair <string, string> labelToBeFound = labels.First(); // Specify full projection to make sure that // label information, if it exists, is returned for listed jobs. ListJobsOptions options = new ListJobsOptions { Projection = ProjectionEnum.Full }; List <BigQueryJob> jobs = client .ListJobs(options) .Where(job => job.Resource.Configuration.Labels?.Contains(labelToBeFound) ?? false) .Take(2) .ToList(); foreach (BigQueryJob job in jobs) { Console.WriteLine(job.Reference.JobId); } // End snippet // This test added two jobs with such labels, other tests might have // added more. Assert.True(jobs.Count >= 2); }
public void insertIssues(List <Issue> issues) { var table = client.GetTable(datasetName, tableName); foreach (var issue in issues) { var json = JsonConvert.SerializeObject(issue, new ItemStateConverter(typeof(StringEnum <ItemState>))); Console.WriteLine(json); } }
private static string GetBigQueryType(BigQueryClient client, string datasetName, string tableName, string key) { BigQueryTable table = client.GetTable(datasetName, tableName); foreach (var field in table.Schema.Fields) { if (field.Name == key) { return(field.Type); } } return(null); }
// [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 QueryOptions { DestinationTable = destination }); // Wait for the job to complete. job.GetQueryResults(); }
public async Task <bool> selectExceptUsers() { BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); string sql = $"SELECT * EXCEPT (SecondNumber) FROM {table}"; BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null); /*foreach (BigQueryRow row in results) * { * Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}"); * }*/ Console.WriteLine(results.ToList().Count); return(true); }
// [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); }
// [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)); }
//BigQuery implementation public BigQueryResults BigQueryResults(string aDatasetId, string aTableName, string aQueryString) { try { BigQueryTable table = bigQuery.GetTable(projectId, aDatasetId, aTableName); //string sql = $"SELECT COUNT(*) FROM {table}"; string sql = aQueryString.Replace("TABLE", $"{table}"); BigQueryResults results = bigQuery.ExecuteQuery(sql); return(results); } catch (Exception aExeption) { Console.WriteLine(aExeption.ToString()); return(null); } }
public async Task <bool> selectNotEqualUsers() { BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); string sql = $"SELECT FullName, Country ,CreatedAt" + $"FROM {table}" + "WHERE Country != 'American Samoa'" + "ORDER BY Country ASC; "; BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null); /*foreach (BigQueryRow row in results) * { * Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}"); * }*/ Console.WriteLine(results.ToList().Count); return(true); }
public async Task <bool> selectReplaceUsers() { BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); string sql = $"SELECT * REPLACE (FirstNumber / @divider AS FirstNumber) FROM {table}"; BigQueryParameter[] parameters = new[] { new BigQueryParameter("divider", BigQueryDbType.Int64, 2) }; BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters); /*foreach (BigQueryRow row in results) * { * Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}"); * }*/ Console.WriteLine(results.ToList().Count); return(true); }
public void Update() { string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = _fixture.GenerateTableId(); BigQueryClient.Create(projectId).CreateTable(datasetId, tableId, new TableSchema()); // Snippet: Update(Table, *) BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); // This example modifies the in-memory resource in the BigQueryDataset, // and then applies that change in the server. Alternatively, pass a Dataset // into the Update method. table.Resource.FriendlyName = "Updated table"; BigQueryTable updated = table.Update(); Console.WriteLine($"Updated table friendly name: {updated.Resource.FriendlyName}"); // End snippet Assert.Equal("Updated table", updated.Resource.FriendlyName); }
public async Task <bool> selectQualifyUsers() { BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); string sql = $"SELECT FullName, Country, CreatedAt, AvgFirstNumber, MinFirstNumber, TotalFirstNumber" + "FROM(" + "SELECT FullName, Country, CreatedAt," + "AVG(FirstNumber) OVER(PARTITION BY Country) AS AvgFirstNumber," + "MIN(FirstNumber) OVER(PARTITION BY Country) AS MinFirstNumber," + "SUM(FirstNumber) OVER(PARTITION BY Country) AS TotalFirstNumber" + $"FROM {table})" + "WHERE AvgFirstNumber > 500;"; BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null); /*foreach (BigQueryRow row in results) * { * Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}"); * }*/ Console.WriteLine(results.ToList().Count); return(true); }
public async Task ListRowsAsync() { string projectId = _fixture.ProjectId; string datasetId = _fixture.GameDatasetId; string tableId = _fixture.HistoryTableId; // Snippet: ListRowsAsync BigQueryClient client = BigQueryClient.Create(projectId); BigQueryTable table = client.GetTable(datasetId, tableId); PagedAsyncEnumerable <TableDataList, BigQueryRow> result = table.ListRowsAsync(); await result.ForEachAsync(row => { DateTime timestamp = (DateTime)row["game_started"]; long level = (long)row["level"]; long score = (long)row["score"]; string player = (string)row["player"]; Console.WriteLine($"{player}: {level}/{score} ({timestamp:yyyy-MM-dd HH:mm:ss})"); }); // End snippet // We set up 7 results in the fixture. Other tests may add more. Assert.True(await result.CountAsync() >= 7); }
// [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 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; }
public void TestLegacySqlAsyncQuery() { 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 = LegacySqlAsyncQuery( projectId, datasetId, tableId, query, _client); Assert.True(results.Count() > 0); }
public ADataBase(string nomeDaTabela) { this.client = BigQueryClient.Create("hackathon-04", GoogleCredential.FromFile(@"C:\Users\wesley.olivier\Desktop\hackathon\hackathon-04-0c90ce17edf7.json")); this.table = client.GetTable("hackathon-04", "dadosBrutos", nomeDaTabela); }
public void TestSyncQuery() { 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 = SyncQuery(projectId, datasetId, tableId, query, 10000, _client); Assert.True(results.Count() > 0); }
public string GetTableFullyQualifiedId(string datasetId, string tableId) { return(_bigQuery.GetTable(_projectId, datasetId, tableId).FullyQualifiedId); }