// Request creation
private JobsResource.InsertRequest CreateInsertQueryJobRequest(JobConfigurationQuery query, QueryOptions options)
{
options?.ModifyRequest(query);
return(CreateInsertJobRequest(new JobConfiguration {
Query = query, DryRun = options?.DryRun
}, options));
}
public void PropertiesSetOnRequest()
{
var options = new CreateQueryJobOptions
{
AllowLargeResults = true,
CreateDisposition = CreateDisposition.CreateNever,
DefaultDataset = new DatasetReference {
ProjectId = "a", DatasetId = "b"
},
DestinationTable = new TableReference {
ProjectId = "a", DatasetId = "b", TableId = "c"
},
FlattenResults = false,
MaximumBillingTier = 10,
Priority = QueryPriority.Batch,
UseQueryCache = false,
WriteDisposition = WriteDisposition.WriteIfEmpty
};
JobConfigurationQuery query = new JobConfigurationQuery();
options.ModifyRequest(query);
Assert.Equal(true, query.AllowLargeResults);
Assert.Equal("CREATE_NEVER", query.CreateDisposition);
Assert.Equal("b", query.DefaultDataset.DatasetId);
Assert.Equal("c", query.DestinationTable.TableId);
Assert.Equal(false, query.FlattenResults);
Assert.Equal(10, query.MaximumBillingTier);
Assert.Equal("BATCH", query.Priority);
Assert.Equal(false, query.UseQueryCache);
Assert.Equal("WRITE_EMPTY", query.WriteDisposition);
}
internal void PopulateJobConfigurationQuery(JobConfigurationQuery query)
{
query.Query = Sql;
// Safe for now; we only have "named" or "positional". This is unlikely to change.
query.ParameterMode = ParameterMode.ToString().ToLowerInvariant();
query.QueryParameters = Parameters.Select(p => p.ToQueryParameter(ParameterMode)).ToList();
}
// Request creation
private JobsResource.InsertRequest CreateInsertQueryJobRequest(string sql, IEnumerable <BigQueryParameter> parameters, QueryOptions options)
{
GaxPreconditions.CheckNotNull(sql, nameof(sql));
if (parameters != null)
{
parameters = parameters.ToList();
GaxPreconditions.CheckArgument(!parameters.Contains(null), nameof(parameters), "Parameter list must not contain null elements");
}
var jobConfigurationQuery = new JobConfigurationQuery
{
Query = sql,
UseLegacySql = false,
ParameterMode = "named",
QueryParameters = parameters?.Select(p => p.ToQueryParameter()).ToList()
};
options?.ModifyRequest(jobConfigurationQuery);
// If there aren't any parameters, set ParameterMode to null - otherwise legacy SQL queries fail,
// even if haven't set any parameters.
if (parameters == null)
{
jobConfigurationQuery.ParameterMode = null;
}
// Now we've definitely set the parameter mode, validate that all parameters have names if appropriate.
if (jobConfigurationQuery.ParameterMode == "named")
{
GaxPreconditions.CheckArgument(parameters.All(p => !string.IsNullOrEmpty(p.Name)), nameof(parameters),
$"When using a parameter mode of '{nameof(BigQueryParameterMode.Named)}', all parameters must have names");
}
return(CreateInsertJobRequest(new JobConfiguration {
Query = jobConfigurationQuery, DryRun = options?.DryRun
}, options));
}
internal void ModifyRequest(JobConfigurationQuery query)
{
// Note: no validation of combinations (flatten results etc). Leave this to the server,
// to avoid restrictions getting out of date.
if (AllowLargeResults != null)
{
query.AllowLargeResults = AllowLargeResults;
}
if (CreateDisposition != null)
{
query.CreateDisposition = EnumMap.ToApiValue(CreateDisposition.Value);
}
if (DefaultDataset != null)
{
query.DefaultDataset = DefaultDataset;
}
if (DestinationTable != null)
{
query.DestinationTable = DestinationTable;
}
if (FlattenResults != null)
{
query.FlattenResults = FlattenResults;
}
if (MaximumBillingTier != null)
{
query.MaximumBillingTier = MaximumBillingTier;
}
if (MaximumBytesBilled != null)
{
query.MaximumBytesBilled = MaximumBytesBilled;
}
if (Priority != null)
{
query.Priority = EnumMap.ToApiValue(Priority.Value);
}
if (UseQueryCache != null)
{
query.UseQueryCache = UseQueryCache;
}
if (WriteDisposition != null)
{
query.WriteDisposition = EnumMap.ToApiValue(WriteDisposition.Value);
}
if (UseLegacySql != null)
{
query.UseLegacySql = UseLegacySql;
}
if (ParameterMode != null)
{
// Safe for now; we only have "named" or "positional". This is unlikely to change.
query.ParameterMode = ParameterMode.ToString().ToLowerInvariant();
}
if (DestinationEncryptionConfiguration != null)
{
query.DestinationEncryptionConfiguration = DestinationEncryptionConfiguration;
}
}
/// <inheritdoc />
public override BigQueryJob CreateQueryJob(string sql, CreateQueryJobOptions options = null)
{
GaxPreconditions.CheckNotNull(sql, nameof(sql));
var query = new JobConfigurationQuery {
Query = sql, UseLegacySql = false
};
var job = CreateInsertQueryJobRequest(query, options).Execute();
return(new BigQueryJob(this, job));
}
/// <inheritdoc />
public override async Task <BigQueryJob> CreateQueryJobAsync(string sql, CreateQueryJobOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
{
GaxPreconditions.CheckNotNull(sql, nameof(sql));
var query = new JobConfigurationQuery {
Query = sql, UseLegacySql = false
};
var job = await CreateInsertQueryJobRequest(query, options).ExecuteAsync(cancellationToken).ConfigureAwait(false);
return(new BigQueryJob(this, job));
}
// [END sync_query]
/// <summary>Inserts an asynchronous query Job for a particular query.</summary>
/// <param name="bigquery">an authorized BigQuery client</param>
/// <param name="projectId">a string containing the project ID</param>
/// <param name="querySql">the actual query string</param>
/// <param name="batch">true if you want to run the query as BATCH</param>
/// <returns>a reference to the inserted query job</returns>
// [START async_query]
public static Job AsyncQuery(BigqueryService bigquery, string projectId,
string querySql, bool batch)
{
JobConfigurationQuery queryConfig = new JobConfigurationQuery
{
Query = querySql,
Priority = batch ? "BATCH" : null
};
JobConfiguration config = new JobConfiguration { Query = queryConfig };
Job job = new Job { Configuration = config };
return bigquery.Jobs.Insert(job, projectId).Execute();
}
/// <inheritdoc />
public override BigQueryJob CreateQueryJob(BigQueryCommand command, CreateQueryJobOptions options = null)
{
GaxPreconditions.CheckNotNull(command, nameof(command));
var query = new JobConfigurationQuery {
UseLegacySql = false
};
command.PopulateJobConfigurationQuery(query);
var job = CreateInsertQueryJobRequest(query, options).Execute();
return(new BigQueryJob(this, job));
}
public override async Task <BigQueryJob> CreateQueryJobAsync(BigQueryCommand command, QueryOptions options = null, CancellationToken cancellationToken = default)
{
GaxPreconditions.CheckNotNull(command, nameof(command));
var query = new JobConfigurationQuery {
UseLegacySql = false
};
command.PopulateJobConfigurationQuery(query);
var job = await CreateInsertQueryJobRequest(query, options).ExecuteAsync(cancellationToken).ConfigureAwait(false);
return(new BigQueryJob(this, job));
}
// [END build_service]
// [START run_query]
/// <summary>
/// Executes the given query synchronously.
/// </summary>
/// <param name="querySql">the query to execute.</param>
/// <param name="bigquery">the BigquerService object.</param>
/// <param name="projectId">the id of the project under which to run the query.</param>
/// <returns>a list of the results of the query.</returns>
private static async Task<IList<TableRow>> ExecuteQueryAsync(
string querySql, BigqueryService bigquery, string projectId)
{
JobConfigurationQuery queryConfig = new JobConfigurationQuery { Query = querySql };
JobConfiguration config = new JobConfiguration { Query = queryConfig };
Job job = new Job { Configuration = config };
JobsResource.InsertRequest insert = bigquery.Jobs.Insert(job, projectId);
JobReference jobRef = (await insert.ExecuteAsync()).JobReference;
GetQueryResultsResponse queryResult = await
bigquery.Jobs.GetQueryResults(projectId, jobRef.JobId).ExecuteAsync();
return queryResult.Rows;
}
public void PropertiesSetOnRequest()
{
var options = new QueryOptions
{
AllowLargeResults = true,
CreateDisposition = CreateDisposition.CreateNever,
DefaultDataset = new DatasetReference {
ProjectId = "a", DatasetId = "b"
},
DestinationTable = new TableReference {
ProjectId = "a", DatasetId = "b", TableId = "c"
},
FlattenResults = false,
MaximumBillingTier = 10,
MaximumBytesBilled = 1000,
Priority = QueryPriority.Batch,
UseQueryCache = false,
WriteDisposition = WriteDisposition.WriteIfEmpty,
UseLegacySql = true,
ParameterMode = BigQueryParameterMode.Positional,
DestinationEncryptionConfiguration = new EncryptionConfiguration {
KmsKeyName = "projects/1/locations/us/keyRings/1/cryptoKeys/1"
},
DestinationSchemaUpdateOptions = SchemaUpdateOption.AllowFieldAddition | SchemaUpdateOption.AllowFieldRelaxation,
TimePartitioning = TimePartition.CreateDailyPartitioning(TimeSpan.FromHours(1), "field")
};
JobConfigurationQuery query = new JobConfigurationQuery();
options.ModifyRequest(query);
Assert.Equal(true, query.AllowLargeResults);
Assert.Equal("CREATE_NEVER", query.CreateDisposition);
Assert.Equal("b", query.DefaultDataset.DatasetId);
Assert.Equal("c", query.DestinationTable.TableId);
Assert.Equal(false, query.FlattenResults);
Assert.Equal(10, query.MaximumBillingTier);
Assert.Equal(1000, query.MaximumBytesBilled);
Assert.Equal("BATCH", query.Priority);
Assert.Equal(false, query.UseQueryCache);
Assert.Equal("WRITE_EMPTY", query.WriteDisposition);
Assert.Equal(true, query.UseLegacySql);
Assert.Equal("positional", query.ParameterMode);
Assert.Equal("projects/1/locations/us/keyRings/1/cryptoKeys/1", query.DestinationEncryptionConfiguration.KmsKeyName);
Assert.Equal(2, query.SchemaUpdateOptions.Count);
Assert.Contains("ALLOW_FIELD_ADDITION", query.SchemaUpdateOptions);
Assert.Contains("ALLOW_FIELD_RELAXATION", query.SchemaUpdateOptions);
Assert.Equal(60 * 60 * 1000, query.TimePartitioning.ExpirationMs);
Assert.Equal("DAY", query.TimePartitioning.Type);
Assert.Equal("field", query.TimePartitioning.Field);
}
internal void ModifyRequest(JobConfigurationQuery query)
{
// Note: no validation of combinations (flatten results etc). Leave this to the server,
// to avoid restrictions getting out of date.
if (AllowLargeResults != null)
{
query.AllowLargeResults = AllowLargeResults;
}
if (CreateDisposition != null)
{
query.CreateDisposition = EnumMap.ToApiValue(CreateDisposition.Value);
}
if (DefaultDataset != null)
{
query.DefaultDataset = DefaultDataset;
}
if (DestinationTable != null)
{
query.DestinationTable = DestinationTable;
}
if (FlattenResults != null)
{
query.FlattenResults = FlattenResults;
}
if (MaximumBillingTier != null)
{
query.MaximumBillingTier = MaximumBillingTier;
}
if (MaximumBytesBilled != null)
{
query.MaximumBytesBilled = MaximumBytesBilled;
}
if (Priority != null)
{
query.Priority = EnumMap.ToApiValue(Priority.Value);
}
if (UseQueryCache != null)
{
query.UseQueryCache = UseQueryCache;
}
if (WriteDisposition != null)
{
query.WriteDisposition = EnumMap.ToApiValue(WriteDisposition.Value);
}
if (UseLegacySql != null)
{
query.UseLegacySql = UseLegacySql;
}
}
public void PopulateJobConfigurationQuery()
{
var jobConfig = new JobConfigurationQuery();
var command = new BigqueryCommand("sql here")
{
Parameters = { { BigqueryParameterType.Int64, 10 } },
ParameterMode = BigqueryParameterMode.Positional
};
command.PopulateJobConfigurationQuery(jobConfig);
Assert.Equal("sql here", jobConfig.Query);
Assert.Equal("positional", jobConfig.ParameterMode);
Assert.Equal(1, jobConfig.QueryParameters.Count);
Assert.Equal("10", jobConfig.QueryParameters[0].ParameterValue.Value);
}
private JobsResource.InsertRequest CreateInsertQueryJobRequest(JobConfigurationQuery query, CreateQueryJobOptions options)
{
options?.ModifyRequest(query);
var request = Service.Jobs.Insert(new Job
{
Configuration = new JobConfiguration
{
Query = query,
// It's slightly annoying that this is set here rather than in ModifyRequest, but at least it's in a single place.
DryRun = options?.DryRun
},
}, ProjectId);
request.ModifyRequest += _versionHeaderAction;
return(request);
}
/// <inheritdoc />
public override BigQueryJob CreateQueryJob(string sql, CreateQueryJobOptions options = null)
{
GaxPreconditions.CheckNotNull(sql, nameof(sql));
var query = new JobConfigurationQuery {
Query = sql, UseLegacySql = false
};
options?.ModifyRequest(query);
var job = Service.Jobs.Insert(new Job
{
Configuration = new JobConfiguration
{
Query = query
},
}, ProjectId).Execute();
return(new BigQueryJob(this, job));
}
/// <inheritdoc />
public override async Task <BigQueryJob> CreateQueryJobAsync(string sql, CreateQueryJobOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
{
GaxPreconditions.CheckNotNull(sql, nameof(sql));
var query = new JobConfigurationQuery {
Query = sql, UseLegacySql = false
};
options?.ModifyRequest(query);
var job = await Service.Jobs.Insert(new Job
{
Configuration = new JobConfiguration
{
Query = query
},
}, ProjectId).ExecuteAsync(cancellationToken).ConfigureAwait(false);
return(new BigQueryJob(this, job));
}
// [END sync_query]
/// <summary>Inserts an asynchronous query Job for a particular query.
/// </summary>
/// </param>
/// <param name="projectId">a string containing the project ID</param>
/// <param name="querySql">the actual query string</param>
/// <param name="batch">true if you want to run the query as BATCH
/// </param>
/// <returns>a reference to the inserted query job</returns>
// [START async_query]
public Job AsyncQuery(string projectId, string querySql, bool batch)
{
BigqueryService bigquery = CreateAuthorizedClient();
JobConfigurationQuery queryConfig = new JobConfigurationQuery
{
Query = querySql,
Priority = batch ? "BATCH" : null
};
JobConfiguration config =
new JobConfiguration {
Query = queryConfig
};
Job job = new Job {
Configuration = config
};
return(bigquery.Jobs.Insert(job, projectId).Execute());
}
/// <inheritdoc />
public override BigQueryJob CreateQueryJob(BigQueryCommand command, CreateQueryJobOptions options = null)
{
GaxPreconditions.CheckNotNull(command, nameof(command));
var query = new JobConfigurationQuery {
UseLegacySql = false
};
command.PopulateJobConfigurationQuery(query);
options?.ModifyRequest(query);
var job = Service.Jobs.Insert(new Job
{
Configuration = new JobConfiguration
{
Query = query
},
}, ProjectId).Execute();
return(new BigQueryJob(this, job));
}
public void PropertiesSetOnRequest()
{
var options = new QueryOptions
{
AllowLargeResults = true,
CreateDisposition = CreateDisposition.CreateNever,
DefaultDataset = new DatasetReference {
ProjectId = "a", DatasetId = "b"
},
DestinationTable = new TableReference {
ProjectId = "a", DatasetId = "b", TableId = "c"
},
FlattenResults = false,
MaximumBillingTier = 10,
MaximumBytesBilled = 1000,
Priority = QueryPriority.Batch,
UseQueryCache = false,
WriteDisposition = WriteDisposition.WriteIfEmpty,
UseLegacySql = true,
ParameterMode = BigQueryParameterMode.Positional,
DestinationEncryptionConfiguration = new EncryptionConfiguration {
KmsKeyName = "projects/1/locations/us/keyRings/1/cryptoKeys/1"
}
};
JobConfigurationQuery query = new JobConfigurationQuery();
options.ModifyRequest(query);
Assert.Equal(true, query.AllowLargeResults);
Assert.Equal("CREATE_NEVER", query.CreateDisposition);
Assert.Equal("b", query.DefaultDataset.DatasetId);
Assert.Equal("c", query.DestinationTable.TableId);
Assert.Equal(false, query.FlattenResults);
Assert.Equal(10, query.MaximumBillingTier);
Assert.Equal(1000, query.MaximumBytesBilled);
Assert.Equal("BATCH", query.Priority);
Assert.Equal(false, query.UseQueryCache);
Assert.Equal("WRITE_EMPTY", query.WriteDisposition);
Assert.Equal(true, query.UseLegacySql);
Assert.Equal("positional", query.ParameterMode);
Assert.Equal("projects/1/locations/us/keyRings/1/cryptoKeys/1", query.DestinationEncryptionConfiguration.KmsKeyName);
}
