Converting JSON to SQL DDL

[#1]

I needed to build a quick SQL table from a JSON. There's some online tools, but I'd rather Java this process.

It works okay enough, now I am wondering if this would make a good Apache NiFi processor

package com.dataflowdeveloper.processors.process;

import java.util.Iterator;
import java.util.Map;
import com.fasterxml.jackson.core.JsonFactory;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

public class JsonToDDL {
 /**
  * 
  * @param tableName
  * @param json
  * @return String DDL SQL
  */
 public String parse(String tableName, String json) {
  JsonFactory factory = new JsonFactory();


  StringBuilder sql = new StringBuilder(256);
  sql.append("CREATE TABLE ").append(tableName).append(" ( ");

  ObjectMapper mapper = new ObjectMapper(factory);
  JsonNode rootNode = null;
  try {
   rootNode = mapper.readTree(json);
  } catch (Exception e) {
   e.printStackTrace();
  }

  Iterator<Map.Entry<String, JsonNode>> fieldsIterator = rootNode.fields();
  while (fieldsIterator.hasNext()) {
   Map.Entry<String, JsonNode> field = fieldsIterator.next();
   System.out.println("Key: " + field.getKey() + "\tValue:" + field.getValue());

   sql.append(field.getKey());

   if (field.getValue().canConvertToInt()) {
    sql.append(" INT, ");
   } else if (field.getValue().canConvertToLong()) {
    sql.append(" LONG, ");
   } else if (field.getValue().asText().contains("/")) {
    sql.append(" DATE, ");
   } else if (field.getValue().asText().contains("-")) {
    sql.append(" DATE, ");
   } else if (field.getValue().asText().length() > 25) {
    sql.append(" VARCHAR( ").append( field.getValue().asText().length() + 25 ) .append("), ");   
   } else {
    sql.append(" VARCHAR(25), ");
   }
  }

  // end table
  sql.deleteCharAt(sql.length() - 2);
  sql.append(" ) ");
  return sql.toString();
 }

 public static void main(String[] args) {
  JsonToDDL ddl = new JsonToDDL();
  String json = "{\"EMP_ID\":3001,\"DURATION_SEC\":288000,\"LOG_DATE\":\"2017-11-07 10:00:00\"}";
  String ddlSQL = ddl.parse("TIME_LOG", json);
  System.out.println("DDL=" + ddlSQL);
  json = " {\"EMP_ID\":4001,\"GENDER\": \"M\",\"DEPT_ID\":4, \"FIRST_NAME\":\"Brett\",\"LAST_NAME\" :\"Lee\"}";
  ddlSQL = ddl.parse("EMPLOYEE", json);
  System.out.println("DDL=" + ddlSQL);
  json = "{\"DEPT_ID\":1,\"CODE\": \"FN\",\"NAME\":\"Finance\",\"DESCRIPTION\" :\"Finance Department\",\"ACTIVE\":1}";
  ddlSQL = ddl.parse("DEPARTMENT", json);
  System.out.println("DDL=" + ddlSQL);
 }
}

In tests it looks okay. I do some guessing of what type something should be from the JSON value.

I was also thinking I could hook this up to Avro Tools to do some other type investigation.

So should I make this a processor? Or just a little script.

Reference:

• https://github.com/kite-sdk/kite
• http://kitesdk.org/docs/1.1.0/Inferring-a-Schema-from-a-Java-Class.html
• https://avro.apache.org/docs/1.8.1/api/java/org/apache/avro/reflect/package-summary.html
• https://avro.apache.org/docs/1.8.2/gettingstartedjava.html#Serializing+and+deserializing+with+code+g...

[#2]

Perhaps we could add "CREATE TABLE" and other DDL commands to ConvertJsonToSQL? The hardest part of the exercise is to infer the types correctly, it's such a difficult problem that there's often a "why bother?" attitude, or the type inference is "good enough" for 80% of the use cases, etc.

[#3]

How to do it in reverse way, like ddl to json?

[#4]

Hmmm DDL to JSON is not a bad idea.