Community Articles

Find and share helpful community-sourced technical articles.
Labels (1)
Super Guru

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.

45413-jsontoddl.png

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:

7,636 Views
Comments
Super Guru

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.

Not applicable

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

Super Guru

Hmmm DDL to JSON is not a bad idea.

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.
Version history
Last update:
‎09-16-2022 01:41 AM
Updated by:
Contributors
Top Kudoed Authors