private string[] GetFieldsNamesThatExcludesCompareMembers(string[] compareMembers)
{
var validFields = typeof(T).GetProperties().Where(x => x.GetCustomAttribute <BigQueryIgnoreAttribute>() == null)
.Select(x => SnakeCaseConverter.ConvertToSnakeCase(x.Name));
var updateFields = validFields.Except(compareMembers).ToArray();
return(updateFields);
}
public static TableSchema BuildSchema <T>()
{
var publicProps = typeof(T).GetProperties();
var builder = new TableSchemaBuilder();
foreach (var property in publicProps)
{
if (property.GetCustomAttribute <BigQueryIgnoreAttribute>() != null)
{
continue;
}
builder.Add(SnakeCaseConverter.ConvertToSnakeCase(property.Name), new DataInfoFactory().Get(property).DbType);
}
return(builder.Build());
}
private async Task <BigQueryTable> GetTable()
{
var client = await bigQueryContextClientResolver.GetClient(projectId, credsPath);
var dataset = client.GetDataset(projectId, datasetId);
var schema = BigQueryContextTableSchemaBuilder.BuildSchema <T>();
BigQueryTable table = null;
await tableAccessLocker.WaitAsync();
try {
var partitionProperty = typeof(T).GetProperties()
.SingleOrDefault(x => x.GetCustomAttribute <BigQueryPartitionAttribute>() != null);
var tableDeclaration = new Table {
Schema = schema
};
if (partitionProperty != null)
{
tableDeclaration.TimePartitioning = new TimePartitioning {
Field = SnakeCaseConverter.ConvertToSnakeCase(typeof(T).GetProperties()
.Single(x => x.GetCustomAttribute <BigQueryPartitionAttribute>() != null).Name)
};
}
var tables = await dataset.ListTablesAsync().ReadPageAsync(10000);
if (tables.Any(x => x.Reference.TableId == tableName))
{
table = await dataset.GetOrCreateTableAsync(tableName, tableDeclaration);
}
else
{
table = await dataset.CreateTableAsync(tableName, tableDeclaration);
}
} finally {
tableAccessLocker.Release();
}
return(table);
}
private string[] GetFieldNames(Expression <Func <T, object> >[] mergeFields)
{
return(mergeFields.Select(x => {
string originalName = string.Empty;
if (x.Body is UnaryExpression unaryExpression)
{
originalName = ((MemberExpression)unaryExpression.Operand).Member.Name;
}
if (x.Body is MemberExpression memberExpression)
{
originalName = memberExpression.Member.Name;
}
var cased = SnakeCaseConverter.ConvertToSnakeCase(originalName);
return cased;
}).Where(x => x != null).ToArray());
}
public IEnumerable <BigQueryInsertRow> MapToInsertRows <T>(IReadOnlyCollection <T> dataModels)
{
var publicProps = typeof(T).GetProperties();
foreach (var dataModel in dataModels)
{
var row = new BigQueryInsertRow();
foreach (var property in publicProps)
{
if (property.GetCustomAttribute <BigQueryIgnoreAttribute>() != null)
{
continue;
}
string fieldName = SnakeCaseConverter.ConvertToSnakeCase(property.Name);
var fieldValue = dataInfoFactory.Get(property).MapToRowValue(property.GetValue(dataModel));
row.Add(fieldName, fieldValue);
}
yield return(row);
}
}
private async Task Merge(
string tempTableName,
Expression <Func <T, object> >[] compareFields,
Expression <Func <T, object> >[] updateFields = null)
{
var client = await bigQueryContextClientResolver.GetClient(projectId, credsPath);
await GetTable();
var sb = new StringBuilder();
sb.Append($"MERGE `{datasetId}.{tableName}` t {Environment.NewLine}");
sb.Append($"USING `{datasetId}.{tempTableName}` s {Environment.NewLine}");
sb.Append("ON ");
var compareMembers = GetFieldNames(compareFields);
var merges = compareMembers.Select(x => $"t.{x} = s.{x}");
var mergesStr = string.Join(" AND ", merges);
sb.Append(mergesStr);
sb.Append(Environment.NewLine);
var updateMembers = updateFields != null?GetFieldNames(updateFields) : GetFieldsNamesThatExcludesCompareMembers(compareMembers);
if (updateMembers.Any())
{
sb.Append($"WHEN MATCHED THEN {Environment.NewLine}");
sb.Append($"UPDATE SET {Environment.NewLine}");
var updates = updateMembers.Select(x => $"{x} = s.{x}").ToArray();
var updatesStr = string.Join(", ", updates);
sb.Append($"{updatesStr}{Environment.NewLine}");
}
sb.Append($"WHEN NOT MATCHED THEN {Environment.NewLine}");
var allMembers = typeof(T).GetProperties().Where(x => x.GetCustomAttribute <BigQueryIgnoreAttribute>() == null).Select(x => SnakeCaseConverter.ConvertToSnakeCase(x.Name)).ToArray();
var allMembersStr = $"({string.Join(", ", allMembers)})";
sb.Append($"INSERT {allMembersStr}");
sb.Append($"VALUES {allMembersStr}");
string sql = sb.ToString();
await client.ExecuteQueryAsync(sql, null);
}