SQL parser to alter table name

Question

I have a SQL query:

select 
    t1.name, t2.address 
from 
    Table1 t1 
inner join 
    Table2 t2 on t1.id = t2.id;

and a map:

Map<String,String> map = new HashMap<String,String>();
map.put("testTable", "hive.DB1");
map.put("testTable", "mongo.DB2");

I just want using this map the above mentioned query to be converted in :

select 
    t1.name, t2.address 
from 
    hive.DB1.`Table1` t1 
inner join 
    mongo.DB2.`Table2` t2 on t1.id = t2.id;

Which open source SQL parser is suitable for this purpose.

I guess (hope) you use an `on` instead of a `where` don't you ? — Raphaël Althaus, Aug 17 '15 at 09:36
By the way, where do you get your fields (in the select : `t1.name`, `t2.address`) from your map ? — Raphaël Althaus, Aug 17 '15 at 09:38
@RaphaëlAlthaus I updated my question. This is just an example. — Dev, Aug 17 '15 at 09:40
I don't need to get these fields from the map. These fields are as it is. I just want to change table names. — Dev, Aug 17 '15 at 09:42
You need to specify which dialect of SQL. There are many parsers out there, all for different vendor's dialects. Then you get to discover if the one you are using is actually amenable to this task. — Ira Baxter, Aug 27 '16 at 05:07

score 0 · Answer 1 · answered Aug 17 '15 at 10:06

0

Do you really need a SQL Parser ? Why not just using sed ?

Ex for one table :

sed -e "s/Table1/hive.DB1.TABLE1/g" C:\test.sql > C:\new.sql

answered Aug 17 '15 at 10:06

Jean-Christophe Blanchard

438
3
11

1

Some databases do not support a fully qualified name within expressions. Therefore: select table1.col1 from table1 would be select hive.db1.table1.col1 from hive.db1.table1 would be not correct. It should be select table1.col1 from hive.db1.table1. Thats why a parser is needed. – wumpz Jan 25 '16 at 11:05

wumpz · Answer 2 · 2016-01-25T13:22:08.867

One need a parser to only change table references from the "from" part. Some databases do not accept a fully qualified name within an expression.

This code uses JSqlParser 0.9.5-SNAPSHOT and prints out a modified SQL:

public class SimpleTableReplacer {
    public static void main(String args[]) throws JSQLParserException {
        String sql = "select t1.name, t2.address from Table1 t1 inner join Table2 t2 on t1.id = t2.id";
        Select select = (Select)CCJSqlParserUtil.parse(sql);

        //Replace Table1 with hive.DB1.Table1 and Table2 with mongo.DB2.Table2
        StringBuilder buffer = new StringBuilder();
        ExpressionDeParser expressionDeParser = new ExpressionDeParser();
        SelectDeParser deparser = new SelectDeParser(expressionDeParser,buffer ) {
            @Override
            public void visit(Table tableName) {
                switch (tableName.getName()) {
                    case "Table1": getBuffer().append("hive.DB1.Table1").append(' ').append(tableName.getAlias().getName());break;
                    case "Table2": getBuffer().append("mongo.DB2.Table2").append(' ').append(tableName.getAlias().getName());break;
                    default:
                        super.visit(tableName);
                }
            }
        };
        expressionDeParser.setSelectVisitor(deparser);
        expressionDeParser.setBuffer(buffer);
        select.getSelectBody().accept(deparser);
        //End of value modification

        System.out.println(buffer.toString());
    }
}

Results in: SELECT t1.name, t2.address FROM hive.DB1.Table1 t1 INNER JOIN mongo.DB2.Table2 t2 ON t1.id = t2.id.

For sure you could use this code to modify the class hierarchy itself, meaning changing the Table - objects name.

Additionally you could use a new feature of JSqlParser to deliver AST nodes for some parts of your SQL. You could extract the exact position of the table names within your SQL and make a text replace there. This could be coded this way:

SimpleNode node = (SimpleNode) CCJSqlParserUtil.parseAST(sql);
    node.childrenAccept(new CCJSqlParserDefaultVisitor() {
        @Override
        public Object visit(SimpleNode node, Object data) {
            if (node.getId() == CCJSqlParserTreeConstants.JJTTABLE) {
                System.out.println("table name '" + node.jjtGetValue() + "' found at " + node.jjtGetFirstToken().beginColumn + "-" + node.jjtGetLastToken().endColumn);
            }
            return super.visit(node, data); 
        }
    }, null);

score -1 · Accepted Answer · answered Jan 19 '16 at 08:30

I used JSqlParser.

I extracted table names from SQL query:

Statement statement = CCJSqlParserUtil.parse("select t1.name, t2.address from Table1 t1 inner join Table2 t2 on t1.id = t2.id;");
Select selectStatement = (Select) statement;
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
List<String> tableList = tablesNamesFinder.getTableList(selectStatement);

Then I modified Table Names according to the map.

score -1 · Answer 4 · answered Aug 27 '16 at 05:03

If you just need to extract table names from SQL and do some logic, you need the ultra light, ultra fast library to do so

Just add the following in your pom

<dependency>
    <groupId>com.github.mnadeem</groupId>
    <artifactId>sql-table-name-parser</artifactId>
    <version>0.0.1</version>
</dependency>

And use the following instruction

new TableNameParser(sql).tables()

For more details refer the project

Disclaimer : I am the owner

SQL parser to alter table name

4 Answers4