Community Articles

Find and share helpful community-sourced technical articles.
Celebrating as our community reaches 100,000 members! Thank you!
Labels (1)
Master 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) {

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


   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.


Master 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.

New Contributor

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

Master Guru

Hmmm DDL to JSON is not a bad idea.