TableSchema CreateTimesTableSchema()
{
var time = new TableFieldSchema {
Name = "time",
Type = "TIME",
Mode = "NULLABLE"
};
var date = new TableFieldSchema {
Name = "date",
Type = "DATE",
Mode = "NULLABLE"
};
var timestamp = new TableFieldSchema {
Name = "timestamp",
Type = "TIMESTAMP",
Mode = "NULLABLE"
};
var datetime = new TableFieldSchema {
Name = "datetime",
Type = "DATETIME",
Mode = "NULLABLE"
};
return(new TableSchema {
Fields = new List <TableFieldSchema> {
time, date, timestamp, datetime
}
});
}
internal static void WriteJsonSchema(JsonTextWriter jw, TableFieldSchema schema)
{
jw.WriteStartObject();
{
jw.WritePropertyName("name");
jw.WriteValue(schema.Name);
if (schema.Description != null)
{
jw.WritePropertyName("description");
jw.WriteValue(schema.Description);
}
if (schema.Mode != null)
{
jw.WritePropertyName("mode");
jw.WriteValue(schema.Mode);
}
jw.WritePropertyName("type");
jw.WriteValue(schema.Type);
if (schema.Fields != null)
{
jw.WritePropertyName("fields");
jw.WriteStartArray();
foreach (var item in schema.Fields)
{
WriteJsonSchema(jw, item);
}
jw.WriteEndArray();
}
}
jw.WriteEndObject();
}
protected override void EndProcessing()
{
switch (ParameterSetName)
{
case ParameterSetNames.ByValue:
TableFieldSchema tfs = new TableFieldSchema()
{
Name = Name,
Type = Type.ToString(),
Description = Description,
Mode = Mode.ToString(),
Fields = Fields
};
WriteObject(tfs);
break;
case ParameterSetNames.ByString:
WriteObject(JsonToColumns(JSON), true);
break;
case ParameterSetNames.ByFile:
WriteObject(JsonToColumns(File.ReadAllText(Filename)), true);
break;
default:
throw new Exception("Invalid parameter set in NewBqSchema");
}
}
public void GetFieldMode()
{
var field = new TableFieldSchema {
Mode = "REPEATED"
};
Assert.Equal(BigQueryFieldMode.Repeated, field.GetFieldMode());
}
public void GetFieldType()
{
var field = new TableFieldSchema {
Type = "INTEGER"
};
Assert.Equal(BigQueryDbType.Int64, field.GetFieldType());
}
public void GetFieldMode_Null()
{
var field = new TableFieldSchema {
Mode = null
};
Assert.Equal(BigQueryFieldMode.Nullable, field.GetFieldMode());
}
public static string ToJsonSchema(this TableFieldSchema schema, Formatting formatting = Formatting.Indented)
{
using (var sw = new StringWriter())
using (var jw = new JsonTextWriter(sw))
{
jw.Formatting = formatting;
WriteJsonSchema(jw, schema);
return(sw.ToString());
}
}
/// <param name="customFieldSchemaSelector">Use custom fallback. If return null, use default fieldschema.</param>
public static TableFieldSchema[] ToTableFieldSchema(Type type, Func <PropertyInfo, TableFieldSchema> customFieldSchemaSelector)
{
return(type.GetProperties()
.Select(x =>
{
if (x.GetCustomAttribute <JsonIgnoreAttribute>() != null)
{
return null;
}
var customSchema = customFieldSchemaSelector(x);
if (customSchema != null)
{
return customSchema;
}
var isNulable = x.PropertyType.IsNullable();
var isArray = x.PropertyType.IsArray;
var dataType = ToDataType(x.PropertyType);
var isRecord = dataType == DataType.Record;
var jsonName = x.GetCustomAttribute <JsonPropertyAttribute>();
var schema = new TableFieldSchema
{
Name = (jsonName != null) ? jsonName.PropertyName : x.Name,
Type = dataType.ToIdentifier(),
Mode = (isArray) ? "REPEATED"
: (isNulable || isRecord || dataType == DataType.String) ? "NULLABLE"
: "REQUIRED"
};
if (isRecord)
{
if (isArray)
{
schema.Fields = ToTableFieldSchema(x.PropertyType.GetElementType(), customFieldSchemaSelector);
}
else
{
schema.Fields = ToTableFieldSchema(x.PropertyType, customFieldSchemaSelector);
}
}
return schema;
})
.Where(x => x != null)
.ToArray());
}
private static Dictionary <string, object> ConvertRecord(JObject record, TableFieldSchema fieldSchema)
{
var fields = fieldSchema.Fields;
JArray values = (JArray)record["f"];
if (values.Count != fields.Count)
{
throw new InvalidOperationException($"Record had {values.Count} entries; expected {fields.Count}");
}
var ret = new Dictionary <string, object>(fields.Count);
for (int i = 0; i < fields.Count; i++)
{
var field = fields[i];
var token = values[i]["v"];
ret[field.Name] = ConvertSingleValue(token.Type == JTokenType.String ? (string)token : (object)token, field);
}
return(ret);
}
static TableSchema CreateNatalityTableSchema()
{
var weight_pounds = new TableFieldSchema {
Name = "weight_pounds",
Type = "FLOAT",
Mode = "NULLABLE"
};
var is_male = new TableFieldSchema {
Name = "is_male",
Type = "BOOLEAN",
Mode = "NULLABLE"
};
return(new TableSchema {
Fields = new List <TableFieldSchema> {
weight_pounds, is_male
}
});
}
static TableSchema CreateNatality2TableSchema()
{
var state = new TableFieldSchema {
Name = "state",
Type = "STRING",
Mode = "NULLABLE"
};
var source_year = new TableFieldSchema {
Name = "source_year",
Type = "INTEGER",
Mode = "NULLABLE"
};
var year = new TableFieldSchema {
Name = "year",
Type = "INTEGER",
Mode = "NULLABLE"
};
var weight_pounds = new TableFieldSchema {
Name = "weight_pounds",
Type = "FLOAT",
Mode = "NULLABLE"
};
var mother_married = new TableFieldSchema {
Name = "mother_married",
Type = "BOOLEAN",
Mode = "NULLABLE"
};
return(new TableSchema {
Fields = new List <TableFieldSchema> {
state, source_year, year, weight_pounds, mother_married
}
});
}
private static Dictionary <string, object>[] ConvertRecordArray(JArray array, TableFieldSchema fieldSchema)
{
var ret = new Dictionary <string, object> [array.Count];
for (int i = 0; i < ret.Length; i++)
{
JObject value = (JObject)array[i];
ret[i] = ConvertRecord((JObject)value["v"], fieldSchema);
}
return(ret);
}
static TableSchema CreateNatalityTableSchema()
{
var weight_pounds = new TableFieldSchema {
Name = "weight_pounds",
Type = "FLOAT",
Mode = "NULLABLE"
};
var is_male = new TableFieldSchema {
Name = "is_male",
Type = "BOOLEAN",
Mode = "NULLABLE"
};
return new TableSchema { Fields = new List<TableFieldSchema> { weight_pounds, is_male } };
}
static TableSchema CreateNatality2TableSchema()
{
var state = new TableFieldSchema {
Name = "state",
Type = "STRING",
Mode = "NULLABLE"
};
var source_year = new TableFieldSchema {
Name = "source_year",
Type = "INTEGER",
Mode = "NULLABLE"
};
var year = new TableFieldSchema {
Name = "year",
Type = "INTEGER",
Mode = "NULLABLE"
};
var weight_pounds = new TableFieldSchema {
Name = "weight_pounds",
Type = "FLOAT",
Mode = "NULLABLE"
};
var mother_married = new TableFieldSchema {
Name = "mother_married",
Type = "BOOLEAN",
Mode = "NULLABLE"
};
return new TableSchema {
Fields = new List<TableFieldSchema> { state, source_year, year, weight_pounds, mother_married }
};
}
private static object ConvertSingleValue(object rawValue, TableFieldSchema field)
{
if (rawValue == null || (rawValue as JToken)?.Type == JTokenType.Null)
{
return(null);
}
var type = field.GetFieldType();
if (field.GetFieldMode() == BigQueryFieldMode.Repeated)
{
JArray array = (JArray)rawValue;
switch (type)
{
case BigQueryDbType.String:
return(ConvertArray(array, StringConverter));
case BigQueryDbType.Int64:
return(ConvertArray(array, Int64Converter));
case BigQueryDbType.Float64:
return(ConvertArray(array, DoubleConverter));
case BigQueryDbType.Bytes:
return(ConvertArray(array, BytesConverter));
case BigQueryDbType.Bool:
return(ConvertArray(array, BooleanConverter));
case BigQueryDbType.Timestamp:
return(ConvertArray(array, TimestampConverter));
case BigQueryDbType.Date:
return(ConvertArray(array, DateConverter));
case BigQueryDbType.Time:
return(ConvertArray(array, TimeConverter));
case BigQueryDbType.DateTime:
return(ConvertArray(array, DateTimeConverter));
case BigQueryDbType.Struct:
return(ConvertRecordArray(array, field));
case BigQueryDbType.Numeric:
return(ConvertArray(array, NumericConverter));
case BigQueryDbType.Geography:
return(ConvertArray(array, GeographyConverter));
default:
throw new InvalidOperationException($"Unhandled field type {type} {rawValue.GetType()}");
}
}
switch (type)
{
case BigQueryDbType.String:
return(StringConverter((string)rawValue));
case BigQueryDbType.Int64:
return(Int64Converter((string)rawValue));
case BigQueryDbType.Float64:
return(DoubleConverter((string)rawValue));
case BigQueryDbType.Bytes:
return(BytesConverter((string)rawValue));
case BigQueryDbType.Bool:
return(BooleanConverter((string)rawValue));
case BigQueryDbType.Timestamp:
return(TimestampConverter((string)rawValue));
case BigQueryDbType.Date:
return(DateConverter((string)rawValue));
case BigQueryDbType.Time:
return(TimeConverter((string)rawValue));
case BigQueryDbType.DateTime:
return(DateTimeConverter((string)rawValue));
case BigQueryDbType.Numeric:
return(NumericConverter((string)rawValue));
case BigQueryDbType.Geography:
return(GeographyConverter((string)rawValue));
case BigQueryDbType.Struct:
return(ConvertRecord((JObject)rawValue, field));
default:
throw new InvalidOperationException($"Unhandled field type {type} (Underlying type: {rawValue.GetType()})");
}
}
/// <summary>
/// Adds the given field to the schema being built.
/// </summary>
/// <param name="field">The field to add. Must not be null.</param>
public void Add(TableFieldSchema field)
{
GaxRestPreconditions.CheckNotNull(field, nameof(field));
_fields.Add(field);
}
/// <summary> /// Returns the mode of a field as a <see cref="BigQueryFieldMode"/>. /// If the mode isn't specified, it defaults to Nullable. /// </summary> internal static BigQueryFieldMode GetFieldMode(this TableFieldSchema field) => field.Mode == null ? BigQueryFieldMode.Nullable : EnumMap <BigQueryFieldMode> .ToValue(field.Mode);
private static DataColumn ToParquetColumn(this TableFieldSchema field, List <FieldMapping> mappings, int index, IEnumerable <BigQueryRow> rows)
{
var foundMapping = mappings.FirstOrDefault(m => m.SourceFieldName.ToLower() == field.Name.ToLower());
var mappedName = field.Name;
if (foundMapping != null)
{
mappedName = foundMapping.MappedName;
}
switch (field.Type)
{
case "STRING":
{
return(new DataColumn(new DataField <string>(mappedName), rows.Select(r => r[index] as string).ToArray()));
}
case "TIMESTAMP":
{
return(new DataColumn(new DataField <string>(mappedName), rows.Select(r => r[index].ToString()).ToArray()));
}
case "BYTES":
{
return(new DataColumn(new DataField <string>(mappedName), rows.Select(r =>
{
var bytes = r[index] as byte[];
if (bytes == null)
{
return Convert.ToBase64String(new byte[] { });
}
else
{
return Convert.ToBase64String(bytes);
}
}).ToArray()));
}
case "INTEGER":
case "INT64":
{
return(new DataColumn(new DataField <long>(mappedName), rows.Select(r => (long)r[index]).ToArray()));
}
case "FLOAT":
case "FLOAT64":
{
return(new DataColumn(new DataField <double>(mappedName), rows.Select(r => (double)r[index]).ToArray()));
}
case "BOOL":
case "BOOLEAN":
{
return(new DataColumn(new DataField <bool>(mappedName), rows.Select(r => (bool)r[index]).ToArray()));
}
case "DATE":
{
return(new DataColumn(new DataField <string>(mappedName), rows.Select(r => ((DateTime)r[index]).ToString("o")).ToArray()));
}
case "TIME":
{
return(new DataColumn(new DataField <string>(mappedName), rows.Select(r => ((DateTime)r[index]).ToString("o")).ToArray()));
}
case "DATETIME":
{
return(new DataColumn(new DataField <string>(mappedName), rows.Select(r => ((DateTime)r[index]).ToString("o")).ToArray()));
}
case "RECORD":
{
throw new Exception($"Google Big Query 'RECORD' type is currently not supported in the ETL tool.");
}
default:
{
throw new Exception($"Google Big Query Unexpected type");
}
}
}
internal static IList<TableFieldSchema> ConvertBQColsToTFS(this IBQMetaObject metaObject)
{
IList<TableFieldSchema> lista = new List<TableFieldSchema>();
foreach (BQColumn col in metaObject.Columns)
{
TableFieldSchema tfsTemp = new TableFieldSchema();
JsonConvert.PopulateObject(JsonConvert.SerializeObject(col), tfsTemp);
lista.Add(tfsTemp);
}
return lista;
}
/// <summary> /// Returns the mode of a field as a <see cref="BigQueryFieldMode"/>. /// </summary> internal static BigQueryFieldMode GetFieldMode(this TableFieldSchema field) => EnumMap <BigQueryFieldMode> .ToValue(field.Mode);
private static object ConvertSingleValue(object rawValue, TableFieldSchema field)
{
if (rawValue == null)
{
return(null);
}
var type = field.GetFieldType();
if (field.GetFieldMode() == FieldMode.Repeated)
{
JArray array = (JArray)rawValue;
switch (type)
{
case BigqueryDbType.String:
return(ConvertArray(array, StringConverter));
case BigqueryDbType.Integer:
return(ConvertArray(array, Int64Converter));
case BigqueryDbType.Float:
return(ConvertArray(array, DoubleConverter));
case BigqueryDbType.Bytes:
return(ConvertArray(array, BytesConverter));
case BigqueryDbType.Boolean:
return(ConvertArray(array, BooleanConverter));
case BigqueryDbType.Timestamp:
return(ConvertArray(array, DateTimeConverter));
case BigqueryDbType.Record:
return(ConvertRecordArray(array, field));
default:
throw new InvalidOperationException($"Unhandled field type {type} {rawValue.GetType()}");
}
}
switch (type)
{
case BigqueryDbType.String:
return(StringConverter((string)rawValue));
case BigqueryDbType.Integer:
return(Int64Converter((string)rawValue));
case BigqueryDbType.Float:
return(DoubleConverter((string)rawValue));
case BigqueryDbType.Bytes:
return(BytesConverter((string)rawValue));
case BigqueryDbType.Boolean:
return(BooleanConverter((string)rawValue));
case BigqueryDbType.Timestamp:
return(DateTimeConverter((string)rawValue));
case BigqueryDbType.Record:
return(ConvertRecord((JObject)rawValue, field));
default:
throw new InvalidOperationException($"Unhandled field type {type} {rawValue.GetType()}");
}
}
/// <summary> /// Returns the type of a field as a <see cref="BigQueryDbType"/>. /// </summary> internal static BigQueryDbType GetFieldType(this TableFieldSchema field) => EnumMap <BigQueryDbType> .ToValue(field.Type);