public void InsertRow_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 BigQueryInsertRow
{
["guid"] = guid,
["position"] = new BigQueryInsertRow {
["x"] = 10L, ["y"] = 20L
}
};
table.InsertRow(row);
var command = new BigQueryCommand($"SELECT guid, position.x, position.y FROM {table} WHERE guid=@guid")
{
Parameters = { { "guid", BigQueryDbType.String, guid } }
};
var resultRows = 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, resultRows);
}
public void InsertRow_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 BigQueryInsertRow
{
["guid"] = guid,
["tags"] = new[] { "a", "b" }
};
table.InsertRow(row);
var command = new BigQueryCommand($"SELECT guid, tag FROM {table}, UNNEST(tags) AS tag WHERE guid=@guid ORDER BY tag")
{
Parameters = { { "guid", BigQueryDbType.String, guid } }
};
var resultRows = 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, resultRows);
}
public void NullParameter_Legacy()
{
// Assumption: no other test inserts a row with a null player name.
var client = BigQueryClient.Create(_fixture.ProjectId);
var table = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
var parameter = new BigQueryParameter("player", BigQueryDbType.String, "Angela");
var command = new BigQueryCommand($"SELECT score FROM {table} WHERE player=@player")
{
Parameters = { parameter }
};
var resultSet = client.ExecuteQuery(command).ReadPage(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).ReadPage(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).ReadPage(5);
Assert.Equal(1, resultSet.Rows.Count);
Assert.Equal(1, (long)resultSet.Rows[0]["score"]);
}
public void InsertRow_RecordRepeatedField()
{
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 BigQueryInsertRow
{
["guid"] = guid,
["job"] = new BigQueryInsertRow {
["company"] = "Pet Store", ["roles"] = new[] { "cashier", "manager" }
},
};
table.InsertRow(row);
var command = new BigQueryCommand($"SELECT job FROM {table} WHERE guid=@guid")
{
Parameters = { { "guid", BigQueryDbType.String, guid } }
};
var fetchedRow = WaitForRows(client, command).Single();
var job = (Dictionary <string, object>)fetchedRow["job"];
Assert.Equal("Pet Store", (string)job["company"]);
Assert.Equal(new[] { "cashier", "manager" }, (string[])job["roles"]);
}
public void InsertRow_BadData_IgnoreBadData()
{
var client = BigQueryClient.Create(_fixture.ProjectId);
var dataset = client.GetDataset(_fixture.DatasetId);
// Don't insert into a table used by other tests...
var table = dataset.CreateTable(
_fixture.CreateTableId(),
new TableSchemaBuilder {
{ "name", BigQueryDbType.String }
}.Build());
Assert.Equal(0, table.ListRows().Count());
var row = new BigQueryInsertRow {
{ "noSuchField", 10 }
};
table.InsertRow(row, new InsertOptions {
AllowUnknownFields = true
});
// Check that we get the row. Use WaitForRows as
// sometimes this seems to be not-completely-immediate.
var command = new BigQueryCommand($"SELECT * FROM {table}");
Assert.Equal(1, WaitForRows(client, command).Count());
}
private BigQueryRow GetSingleRow(BigQueryCommand command)
{
var client = BigQueryClient.Create(_fixture.ProjectId);
var results = client.ExecuteQuery(command).ReadPage(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", BigQueryDbType.Bytes, new byte[] { 1, 3 } } }
};
var row = GetSingleRow(command);
Assert.Equal(2, (long)row["length"]);
}
public void IntegerParameter()
{
var client = BigQueryClient.Create(_fixture.ProjectId);
var command = new BigQueryCommand("SELECT value FROM UNNEST([0, 1, 2, 3, 4]) AS value WHERE value > @value ORDER BY value")
{
Parameters = { { "value", BigQueryDbType.Int64, 2 } }
};
var results = client.ExecuteQuery(command).ReadPage(10);
Assert.Equal(new[] { 3L, 4L }, results.Rows.Select(r => (long)r["value"]));
}
public void IntegerArrayParameter_Legacy()
{
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) ORDER BY value")
{
Parameters = { { "p", BigQueryDbType.Array, new[] { 1, 3, 5 } } }
};
var results = client.ExecuteQuery(command).ReadPage(10);
Assert.Equal(new[] { 1L, 3L }, results.Rows.Select(r => (long)r["value"]));
}
public void ConstructionSample()
{
var command = new BigQueryCommand("sql here")
{
Parameters = { { BigQueryDbType.Int64, 10 } }
};
Assert.Equal("sql here", command.Sql);
Assert.Equal(1, command.Parameters.Count);
Assert.Equal(10, command.Parameters[0].Value);
Assert.Equal(BigQueryDbType.Int64, command.Parameters[0].Type);
}
public void InsertRow_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 BigQueryInsertRow
{
["guid"] = guid,
["names"] = new[] {
new BigQueryInsertRow {
["first"] = "a", ["last"] = "b"
},
new BigQueryInsertRow {
["first"] = "x", ["last"] = "y"
}
}
};
_fixture.InsertAndWait(table, () => table.InsertRow(row), 1);
// Fetch flattened fields
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", BigQueryDbType.String, guid } }
};
var resultRows = client.ExecuteQuery(command)
.GetRows()
.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, resultRows);
// Fetch unflattened
command = new BigQueryCommand($"SELECT names FROM {table} WHERE guid=@guid")
{
Parameters = { { "guid", BigQueryDbType.String, guid } }
};
var resultRow = client.ExecuteQuery(command)
.GetRows()
.Single();
var fetchedNames = (Dictionary <string, object>[])resultRow["names"];
Assert.Equal(2, fetchedNames.Length);
Assert.True(fetchedNames.Any(d => (string)d["first"] == "a" && (string)d["last"] == "b"));
Assert.True(fetchedNames.Any(d => (string)d["first"] == "x" && (string)d["last"] == "y"));
}
public void PopulateJobConfigurationQuery()
{
var jobConfig = new JobConfigurationQuery();
var command = new BigQueryCommand("sql here")
{
Parameters = { { BigQueryDbType.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 = { { BigQueryDbType.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");
}
private void AssertParameterRoundTrip(BigQueryClient client, BigQueryParameter parameter)
{
var command = new BigQueryCommand($"SELECT ? AS value")
{
Parameters = { parameter },
ParameterMode = BigQueryParameterMode.Positional
};
var results = client.ExecuteQuery(command).ToList();
Assert.Equal(1, results.Count);
Assert.Equal(parameter.Value, results[0]["value"]);
if (parameter.Value is DateTime)
{
AssertDateTimeEqual((DateTime)parameter.Value, (DateTime)results[0]["value"]);
}
}
public void TimestampParameter_Legacy()
{
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", BigQueryDbType.String, "Angela" },
{ "start", BigQueryDbType.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).ReadPage(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).ReadPage(10);
Assert.Equal(0, results.Rows.Count);
}
public void ParameterModeIsVerified()
{
var command = new BigQueryCommand();
Assert.Throws <ArgumentException>(() => command.ParameterMode = (BigQueryParameterMode)3);
}
