public void Insert_RepeatedRecordField()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.ComplexTypesTableId);
var guid = Guid.NewGuid().ToString();
var row = new InsertRow
{
["guid"] = guid,
["names"] = new[] {
new InsertRow {
["first"] = "a", ["last"] = "b"
},
new InsertRow {
["first"] = "x", ["last"] = "y"
}
}
};
table.Insert(row);
var command = new BigqueryCommand($"SELECT guid, name.first, name.last FROM {table}, UNNEST(names) AS name WHERE guid=@guid ORDER BY name.first")
{
Parameters = { { "guid", BigqueryParameterType.String, guid } }
};
var queryResults = WaitForRows(client, command)
.Select(r => new { Guid = (string)r["guid"], FirstName = (string)r["first"], LastName = (string)r["last"] })
.ToList();
var expectedResults = new[]
{
new { Guid = guid, FirstName = "a", LastName = "b" },
new { Guid = guid, FirstName = "x", LastName = "y" }
};
Assert.Equal(expectedResults, queryResults);
}
public void Insert_RecordField()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.ComplexTypesTableId);
var guid = Guid.NewGuid().ToString();
var row = new InsertRow
{
["guid"] = guid,
["position"] = new InsertRow {
["x"] = 10L, ["y"] = 20L
}
};
table.Insert(row);
var command = new BigqueryCommand($"SELECT guid, position.x, position.y FROM {table} WHERE guid=@guid")
{
Parameters = { { "guid", BigqueryParameterType.String, guid } }
};
var queryResults = WaitForRows(client, command)
.Select(r => new { Guid = (string)r["guid"], X = (long)r["x"], Y = (long)r["y"] })
.ToList();
var expectedResults = new[]
{
new { Guid = guid, X = 10L, Y = 20L }
};
Assert.Equal(expectedResults, queryResults);
}
public void Insert_RepeatedField()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
var table = dataset.GetTable(_fixture.ComplexTypesTableId);
var guid = Guid.NewGuid().ToString();
var row = new InsertRow
{
["guid"] = guid,
// The null element will be ignored here (at the server side)
["tags"] = new[] { "a", null, "b" }
};
table.Insert(row);
var command = new BigqueryCommand($"SELECT guid, tag FROM {table}, UNNEST(tags) AS tag WHERE guid=@guid ORDER BY tag")
{
Parameters = { { "guid", BigqueryParameterType.String, guid } }
};
var queryResults = WaitForRows(client, command)
.Select(r => new { Guid = (string)r["guid"], Tag = (string)r["tag"] })
.ToList();
var expectedResults = new[]
{
new { Guid = guid, Tag = "a" },
new { Guid = guid, Tag = "b" }
};
Assert.Equal(expectedResults, queryResults);
}
public void NullParameter()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
var parameter = new BigqueryParameter("player", BigqueryParameterType.String, "Angela");
var command = new BigqueryCommand($"SELECT score FROM {table} WHERE player=@player")
{
Parameters = { parameter }
};
var resultSet = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(5);
Assert.Equal(1, resultSet.Rows.Count);
Assert.Equal(95, (long)resultSet.Rows[0]["score"]);
// SQL rules: nothing equals null
parameter.Value = null;
resultSet = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(5);
Assert.Equal(0, resultSet.Rows.Count);
// But we should be able to find the null value this way.
command.Sql = $"SELECT score FROM {table} WHERE player=@player OR (player IS NULL AND @player IS NULL)";
resultSet = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(5);
Assert.Equal(1, resultSet.Rows.Count);
Assert.Equal(1, (long)resultSet.Rows[0]["score"]);
}
private BigqueryRow GetSingleRow(BigqueryCommand command)
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var results = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(10);
Assert.Equal(1, results.Rows.Count);
return(results.Rows[0]);
}
public void BytesParameter()
{
var command = new BigqueryCommand($"SELECT BYTE_LENGTH(@p) AS length")
{
Parameters = { { "p", BigqueryParameterType.Bytes, new byte[] { 1, 3 } } }
};
var row = GetSingleRow(command);
Assert.Equal(2, (long)row["length"]);
}
public void IntegerArrayParameter()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var command = new BigqueryCommand("SELECT value FROM UNNEST([0, 1, 2, 3, 4]) AS value WHERE value IN UNNEST(@p)")
{
Parameters = { { "p", BigqueryParameterType.Array, new[] { 1, 3, 5 } } }
};
var results = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(10);
Assert.Equal(new[] { 1L, 3L }, results.Rows.Select(r => (long)r["value"]));
}
public void ConstructionSample()
{
var command = new BigqueryCommand("sql here")
{
Parameters = { { BigqueryParameterType.Int64, 10 } }
};
Assert.Equal("sql here", command.Sql);
Assert.Equal(1, command.Parameters.Count);
Assert.Equal(10, command.Parameters[0].Value);
Assert.Equal(BigqueryParameterType.Int64, command.Parameters[0].Type);
}
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);
}
public void PopulateQueryRequest()
{
var request = new QueryRequest();
var command = new BigqueryCommand("sql here")
{
Parameters = { { BigqueryParameterType.Int64, 10 } },
ParameterMode = BigqueryParameterMode.Positional
};
command.PopulateQueryRequest(request);
Assert.Equal("sql here", request.Query);
Assert.Equal("positional", request.ParameterMode);
Assert.Equal(1, request.QueryParameters.Count);
Assert.Equal("10", request.QueryParameters[0].ParameterValue.Value);
}
/// <summary>
/// Waits for a query to return a non-empty result set. Some inserts may take a few seconds before the results are visible
/// via queries - and much longer to show up in ListRows. (It looks like these are inserts with repeated fields and/or record fields.)
/// </summary>
private IEnumerable <BigqueryRow> WaitForRows(BigqueryClient client, BigqueryCommand command)
{
for (int i = 0; i < 5; i++)
{
var rows = client.ExecuteQuery(command)
.PollUntilCompleted()
.GetRows()
.ToList();
if (rows.Count > 0)
{
return(rows);
}
Thread.Sleep(1000);
}
throw new TimeoutException("Expected rows were not available after 5 seconds");
}
public void TimestampParameter()
{
var client = BigqueryClient.Create(_fixture.ProjectId);
var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
var command = new BigqueryCommand($"SELECT score FROM {table} WHERE player=@player AND gameStarted > @start")
{
Parameters =
{
{ "player", BigqueryParameterType.String, "Angela" },
{ "start", BigqueryParameterType.Timestamp, new DateTime(2001, 12, 31, 23, 59, 59, DateTimeKind.Utc)},
}
};
// Find the value when we've provided a timestamp smaller than the actual value
var results = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(10);
Assert.Equal(1, results.Rows.Count);
// We shouldn't find it now. (Angela's game started at 2002-01-01T00:00:00Z)
command.Parameters[1].Value = new DateTime(2002, 1, 1, 0, 0, 1, DateTimeKind.Utc);
results = client.ExecuteQuery(command).PollUntilCompleted().GetResultSet(10);
Assert.Equal(0, results.Rows.Count);
}
public void ParameterModeIsVerified()
{
var command = new BigqueryCommand();
Assert.Throws <ArgumentException>(() => command.ParameterMode = (BigqueryParameterMode)3);
}
