Community Articles
Find and share helpful community-sourced technical articles.
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.
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,076 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.

Don't have an account?
Version history
Last update:
‎08-17-2019 09:46 AM
Updated by:
Contributors
Top Kudoed Authors