Hi,
I am attempting to generate an Avro schema from java to describe a table that I
can access via JDBC.
I use the JDBC getMetaData() method to retrieve the relevant column metadata
and store in an array list of "columnDetail" objects.
Column Detail defined as
private static class columnDetail {
public String tableName;
public String columnName;
public String dataTypeName;
public int dataTypeId;
public String size;
public String scale;
}
I then iterate through this array list and build up the Avro schema using the
org.apache.avro.SchemaBuilder class.
My issue is around decimal logical types.
I iterate throuth the array list twice. The first time to add all fields to the
FieldAssembler, the second to modify certain byte fields to add the decimal
logical datatype.
The issue I am experiencing is that I get an error if the Decimal scale value
changes between iterations.
As it iterates through the columnDetail array, it will work so long as the
value "scale" does not change. If it does change, the following occurs:
Exception in thread "main" org.apache.avro.AvroRuntimeException: Can't
overwrite property: scale
at org.apache.avro.JsonProperties.addProp(JsonProperties.java:187)
at org.apache.avro.Schema.addProp(Schema.java:134)
at org.apache.avro.JsonProperties.addProp(JsonProperties.java:191)
at org.apache.avro.Schema.addProp(Schema.java:139)
at org.apache.avro.LogicalTypes$Decimal.addToSchema(LogicalTypes.java:193)
at GenAvroSchema.main(GenAvroSchema.java:85)
I can prevent this by hardcoding the decimal size. i.e. I can replace
org.apache.avro.LogicalTypes.decimal(Integer.parseInt(cd.size),Integer.parseInt(cd.scale)).addToSchema(schema.getField(cd.columnName).schema());
with
org.apache.avro.LogicalTypes.decimal(18,2).addToSchema(schema.getField(cd.columnName).schema());
This however ends up with the same size datatype for all decimal fields which
is not desirable.
Can someone help with this ?
Java: 1.8.0_202 Avro: avro-1.8.2.jar
My java code:
public static void main(String[] args) throws Exception{
String jdbcURL = "jdbc:sforce://login.salesforce.com";
String jdbcUser = "userid";
String jdbcPassword = "password";
String avroDataType = "";
HashMap<String, String> dtmap = new HashMap<String, String>();
dtmap.put("VARCHAR", "string");
dtmap.put("BOOLEAN", "boolean");
dtmap.put("NUMERIC", "bytes");
dtmap.put("INTEGER", "int");
dtmap.put("TIMESTAMP", "string");
dtmap.put("DATE", "string");
ArrayList<columnDetail> columnDetails = new ArrayList<columnDetail>();
columnDetails = populateMetadata(jdbcURL, jdbcUser, jdbcPassword); // This
works so have not included code here
SchemaBuilder.FieldAssembler<Schema> fields =
SchemaBuilder.builder().record("account").doc("Account Detials").fields() ;
for(columnDetail cd:columnDetails) {
avroDataType = dtmap.get(JDBCType.valueOf(cd.dataTypeId).getName());
switch(avroDataType)
{
case "string":
fields.name(cd.columnName).type().unionOf().nullType().and().stringType().endUnion().nullDefault();
break;
case "int":
fields.name(cd.columnName).type().unionOf().nullType().and().intType().endUnion().nullDefault();
break;
case "boolean":
fields.name(cd.columnName).type().unionOf().booleanType().and().nullType().endUnion().booleanDefault(false);
break;
case "bytes":
if(Integer.parseInt(cd.scale) == 0) {
fields.name(cd.columnName).type().unionOf().nullType().and().longType().endUnion().nullDefault();
} else {
fields.name(cd.columnName).type().bytesType().noDefault();
}
break;
default:
fields.name(cd.columnName).type().unionOf().nullType().and().stringType().endUnion().nullDefault();
break;
}
}
Schema schema = fields.endRecord();
for(columnDetail cd:columnDetails) {
avroDataType = dtmap.get(JDBCType.valueOf(cd.dataTypeId).getName());
if(avroDataType == "bytes" && Integer.parseInt(cd.scale) != 0) {
//org.apache.avro.LogicalTypes.decimal(Integer.parseInt(cd.size),Integer.parseInt(cd.scale)).addToSchema(schema.getField(cd.columnName).schema());
org.apache.avro.LogicalTypes.decimal(18,2).addToSchema(schema.getField(cd.columnName).schema());
}
}
BufferedWriter writer = new BufferedWriter(new
FileWriter("./account.avsc"));
writer.write(schema.toString());
writer.close();
}
Thanks,
Eoin.
________________________________
Aon Centre for Innovation and Analytics Limited. Private company limited by
shares. Registered in Ireland. Reg. No. 150155. Reg. Office: The Metropolitan
Building, James Joyce Street, Dublin 1, Ireland.
Tel : +353 1 2666000 | Fax: +353 1 266 6625.
DISCLAIMER:
This message (and any associated files) is/are intended only for the use of the
individual(s) or entity(ies) to which it is addressed and may contain
information that is confidential, subject to copyright or constitutes a trade
secret. If you are not the intended recipient, any dissemination, copying or
distribution of this message or associated files is strictly prohibited. If you
have received this message in error, please notify us immediately by replying
to this message and deleting it. Internet communications cannot be guaranteed
to be secure or error-free as information can be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. Accordingly we cannot
accept responsibility for any error in or omission from this message or any
attachment that has been caused by email transmission. Any view or opinions
expressed in this message are those of the author and do not necessarily
represent those of the company.
