DEVOCEAN

String sql =
    "WITH recent_orders AS (
      SELECT
        o.id AS order_id,
        o.customer_id,
        o.order_date,
        ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn
      FROM
        orders o
      WHERE
        o.status = 'SHIPPED'
    ),
    high_value_customers AS (
      SELECT
        c.id,
        c.name,
        SUM(oi.quantity * oi.unit_price) AS total_spent
      FROM
        customers c
        JOIN orders o ON c.id = o.customer_id
        JOIN order_items oi ON o.id = oi.order_id
      GROUP BY
        c.id, c.name
      HAVING
        SUM(oi.quantity * oi.unit_price) > 10000
    )
    INSERT OVERWRITE TABLE SHOP.RESULT
    SELECT
      c.id,
      c.name,
      r.order_id,
      r.order_date,
      h.total_spent,
      CASE
        WHEN h.total_spent > 50000 THEN 'VIP'
        WHEN h.total_spent > 20000 THEN 'Premium'
        ELSE 'Regular'
      END AS customer_tier
    FROM
      high_value_customers h
      JOIN customers c ON c.id = h.id
      JOIN recent_orders r ON r.customer_id = c.id
    WHERE
      r.rn = 1
    ORDER BY
      h.total_spent DESC;"

public class ParseStatement {
    public void createStatement(String sql) throws JsonProcessingException {
        Statement statement = null;
        TableLineageDto tableLineageDto = new TableLineageDto(sql);
    
        try {
            statement = CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            throw new RuntimeException(e);
        }
    
        if (statement instanceof Insert) {
            parseInsertStatement(statement, tableLineageDto, sql);
        }
    }
    
    public void parseInsertStatement(Statement statement, TableLineageDto tableLineageDto, String sql) throws JsonProcessingException {
    Insert insertStatement = (Insert) statement;

    List<String> targetNameList = setTargetVariables(insertStatement);

    Select select = insertStatement.getSelect();
    List<WithItem<?>> withItemsList = insertStatement.getWithItemsList();

    InsertStatementParser insertStatementParser = null;

    if (targetNameList.size() == 1) {
        insertStatementParser = new InsertStatementParser(tableLineageDto, sql, null, targetNameList.get(0));
    } else {
        insertStatementParser = new InsertStatementParser(tableLineageDto, sql, targetNameList.get(0), targetNameList.get(1));
    }

    if (withItemsList != null) {
        for (int i = 0, limit = withItemsList.size() ; i < limit ; i++) {
            insertStatementParser.parseWithItem(withItemsList.get(i));
        }
    }

    if (select instanceof PlainSelect) {
        PlainSelect plainSelect = select.getPlainSelect();
        insertStatementParser.parsePlainSelect(plainSelect);
    } else if (select instanceof SetOperationList) {
        SetOperationList setOperationList = select.getSetOperationList();
        insertStatementParser.parseSetOperationList(setOperationList);
    }

    ObjectMapper objectMapper = new ObjectMapper();
    objectMapper.enable(SerializationFeature.INDENT_OUTPUT);

    String jsonString = objectMapper.writeValueAsString(tableLineageDto);
    System.out.println("---------------------RESULT---------------------\n" + jsonString);
}

public static List setTargetVariables(Insert insertStatement) {
    List<String> nameParts = insertStatement.getTable().getNameParts();
    int namePartsSize = nameParts.size();

    List<String> targetNameList = Lists.newArrayList();

    if (namePartsSize == 2) {
        String targetTableName = nameParts.get(0);
        String targetDatabaseName = nameParts.get(1);
        targetNameList.add(targetDatabaseName);
        targetNameList.add(targetTableName);
    } else if (namePartsSize == 1) {
        String targetTableName = nameParts.get(0);
        targetNameList.add(targetTableName);
    }

    return targetNameList;
}
}

public class InsertStatementParser {

    protected TableLineageDto tableLineageDto;
    protected String originalSql;
    protected String targetDatabaseName;
    protected String targetTableName;
    private Map<String, Integer> sourceTableNameSequenceMap;
    private String withName = null;

    public InsertStatementParser(TableLineageDto tableLineageDto, String originalSql, String targetDatabaseName, String targetTableName) {
        this.tableLineageDto = tableLineageDto;
        this.originalSql = originalSql;
        this.targetDatabaseName = targetDatabaseName;
        this.targetTableName = targetTableName;
        this.sourceTableNameSequenceMap = new HashMap<>();
    }

    public void parsePlainSelect(PlainSelect plainSelect) {
        FromItem fromItem = plainSelect.getFromItem();
        List<SelectItem<?>> selectItems = plainSelect.getSelectItems();
        List<Join> joins = plainSelect.getJoins();
        List<WithItem<?>> withItemsList = plainSelect.getWithItemsList();

        if (withItemsList != null) {
            for (int i = 0, limit = withItemsList.size() ; i < limit ; i++) {
                parseWithItem(withItemsList.get(i));
            }
        }

        if (fromItem instanceof FromItem) {
            if (fromItem instanceof ParenthesedSelect) {
                parseParenthesedSelect((ParenthesedSelect) fromItem);
            } else if (fromItem instanceof Table) {
                parseTable((Table) fromItem);
            }
        }

        if (joins != null) {
            for (Join join : joins) {
                parseJoin(join);
            }
        }
    }

    public void parseWithItem(WithItem withItem) {
        withName = withItem.getAlias().getName();
        parseParenthesedSelect(withItem.getSelect());
        withName = null;
    }

    public void parseParenthesedSelect(ParenthesedSelect parenthesedSelect) {
        Select select = parenthesedSelect.getSelect();
        if (select instanceof PlainSelect) {
            parsePlainSelect(select.getPlainSelect());
        } else if (select instanceof SetOperationList) {
            parseSetOperationList(select.getSetOperationList());
        }
    }

    public void parseSetOperationList(SetOperationList setOperationList) {
        List<Select> selects = setOperationList.getSelects();

        if (selects != null) {
            for (int i = 0, limit = selects.size() ; i < limit ; i++) {
                parsePlainSelect(selects.get(i).getPlainSelect());
            }
        }
    }

    public void parseTable(Table table) {
        String sourceTableName = null;
        String sourceDatabaseName = null;

        List<String> nameParts = table.getNameParts();
        int namePartsSize = nameParts.size();

        if (namePartsSize == 2) {
            sourceTableName = nameParts.get(0);
            sourceDatabaseName = nameParts.get(1);
        } else if (namePartsSize == 1) {
            sourceTableName = nameParts.get(0);
        }

        String loweredSourceTableName = StringUtils.lowerCase(sourceTableName);
        int sourceTableNameSequence = sourceTableNameSequenceMap.getOrDefault(loweredSourceTableName, 0);

        if (sourceTableNameSequence == 0) {
            sourceTableNameSequenceMap.put(loweredSourceTableName, 1);

            if (withName == null) {
                addTables(sourceDatabaseName, sourceTableName, 1, targetDatabaseName, targetTableName);
            } else {
                addTables(sourceDatabaseName, sourceTableName, 1, null, withName);
            }
        } else {
            sourceTableNameSequenceMap.put(loweredSourceTableName, sourceTableNameSequence + 1);
            if (withName == null) {
                addTables(sourceDatabaseName, sourceTableName, sourceTableNameSequence + 1, targetDatabaseName, targetTableName);
            } else {
                addTables(sourceDatabaseName, sourceTableName, sourceTableNameSequence + 1, targetDatabaseName, withName);
            }
        }
    }

    public void addTables(String sourceDatabaseName, String sourceTableName, int sourceTableNameSequence, String targetDatabaseName, String targetTableName) {
        tableLineageDto.addTableDto(sourceDatabaseName, sourceTableName, sourceTableNameSequence, targetDatabaseName, targetTableName);
    }

    public void parseJoin(Join join) {
        FromItem fromItem = join.getFromItem();

        if (fromItem instanceof FromItem) {
            if (fromItem instanceof ParenthesedSelect) {
                parseParenthesedSelect((ParenthesedSelect) fromItem);
            } else if (fromItem instanceof Table) {
                parseTable((Table) fromItem);
            }
        }
    }
}

{
  "sql" : "WITH recent_orders AS (\n  SELECT\n    o.id AS order_id,\n    o.customer_id,\n    o.order_date,\n    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn\n  FROM\n    orders o\n  WHERE\n    o.status = 'SHIPPED'\n),\nhigh_value_customers AS (\n  SELECT\n    c.id,\n    c.name,\n    SUM(oi.quantity * oi.unit_price) AS total_spent\n  FROM\n    dataset.customers c\n    JOIN orders o ON c.id = o.customer_id\n    JOIN order_items oi ON o.id = oi.order_id\n  GROUP BY\n    c.id, c.name\n  HAVING\n    SUM(oi.quantity * oi.unit_price) > 10000\n)\nINSERT INTO SHOP.RESULT\nSELECT\n  c.id,\n  c.name,\n  r.order_id,\n  r.order_date,\n  h.total_spent,\n  CASE\n    WHEN h.total_spent > 50000 THEN 'VIP'\n    WHEN h.total_spent > 20000 THEN 'Premium'\n    ELSE 'Regular'\n  END AS customer_tier\nFROM\n  high_value_customers h\n  JOIN dataset.customers c ON c.id = h.id\n  JOIN recent_orders r ON r.customer_id = c.id\nWHERE\n  r.rn = 1\nORDER BY\n  h.total_spent DESC;",
  "tableDtoSet" : [ {
    "sourceDatabaseName" : null,
    "sourceTableName" : "orders",
    "sourceTableNameSequence" : 1,
    "targetDatabaseName" : null,
    "targetTableName" : "recent_orders"
  }, {
    "sourceDatabaseName" : null,
    "sourceTableName" : "order_items",
    "sourceTableNameSequence" : 1,
    "targetDatabaseName" : null,
    "targetTableName" : "high_value_customers"
  }, {
    "sourceDatabaseName" : null,
    "sourceTableName" : "orders",
    "sourceTableNameSequence" : 2,
    "targetDatabaseName" : "SHOP",
    "targetTableName" : "high_value_customers"
  }, {
    "sourceDatabaseName" : "dataset",
    "sourceTableName" : "customers",
    "sourceTableNameSequence" : 2,
    "targetDatabaseName" : "SHOP",
    "targetTableName" : "RESULT"
  }, {
    "sourceDatabaseName" : null,
    "sourceTableName" : "high_value_customers",
    "sourceTableNameSequence" : 1,
    "targetDatabaseName" : "SHOP",
    "targetTableName" : "RESULT"
  }, {
    "sourceDatabaseName" : "dataset",
    "sourceTableName" : "customers",
    "sourceTableNameSequence" : 1,
    "targetDatabaseName" : null,
    "targetTableName" : "high_value_customers"
  }, {
    "sourceDatabaseName" : null,
    "sourceTableName" : "recent_orders",
    "sourceTableNameSequence" : 1,
    "targetDatabaseName" : "SHOP",
    "targetTableName" : "RESULT"
  } ]
}