SQL解析器访问者+元数据库+Presto

Question

我面临的似乎是一个相当容易的问题，但我不能把我的头脑围绕这个问题找到一个合适的解决办法。

FROM "SCHEMA".tableB tableB
LEFT JOIN "SCHEMA".tableC tableC

SELECT tableA.*
     , (tableA.valorfaturado + tableA.valorcortado) valorpedido       
  FROM (SELECT from_unixtime(tableB.datacorte / 1000) datacorte
             , COALESCE((tableB.quantidadecortada * tableC.preco), 0) valorcortado
             , COALESCE((tableB.quantidade * tableC.preco), 0) valorfaturado
             , tableB.quantidadecortada
          FROM tableB tableB
          LEFT JOIN tableC tableC
            ON tableC.numeropedido = tableB.numeropedido
           AND tableC.codigoproduto = tableB.codigoproduto
           AND tableC.codigofilial = tableB.codigofilial
          LEFT JOIN tableD tableD
            ON tableD.numero = tableB.numeropedido
         WHERE (CASE
                  WHEN COALESCE(tableB.codigofilial, '') = '' THEN
                    tableD.codigofilial
                  ELSE
                    tableB.codigofilial
                END) = '10'
           AND from_unixtime(tableB.datacorte / 1000) BETWEEN from_iso8601_timestamp('2020-07-01T03:00:00.000Z') AND from_iso8601_timestamp('2020-08-01T02:59:59.999Z')) tableA
 ORDER BY datacorte

SELECT tableA.*
     , (tableA.valorfaturado + tableA.valorcortado) valorpedido       
  FROM (SELECT from_unixtime(tableB.datacorte / 1000) datacorte
             , COALESCE((tableB.quantidadecortada * tableC.preco), 0) valorcortado
             , COALESCE((tableB.quantidade * tableC.preco), 0) valorfaturado
             , tableB.quantidadecortada
          FROM "SCHEMA".tableB tableB
          LEFT JOIN "SCHEMA".tableC tableC
            ON tableC.numeropedido = tableB.numeropedido
           AND tableC.codigoproduto = tableB.codigoproduto
           AND tableC.codigofilial = tableB.codigofilial
          LEFT JOIN "SCHEMA".tableD tableD
            ON tableD.numero = tableB.numeropedido
         WHERE (CASE
                  WHEN COALESCE(tableB.codigofilial, '') = '' THEN
                    tableD.codigofilial
                  ELSE
                    tableB.codigofilial
                END) = '10'
           AND from_unixtime(tableB.datacorte / 1000) BETWEEN from_iso8601_timestamp('2020-07-01T03:00:00.000Z') AND from_iso8601_timestamp('2020-08-01T02:59:59.999Z')) tableA
 ORDER BY datacorte

import java.util.Optional;

import com.facebook.presto.sql.SqlFormatter;
import com.facebook.presto.sql.parser.ParsingOptions;
import com.facebook.presto.sql.parser.SqlParser;

public class SchemaAwareQueryAdapter {
    // Inspired from
    // https://github.com/prestodb/presto/tree/master/presto-parser/src/test/java/com/facebook/presto/sql/parser

    private static final SqlParser SQL_PARSER = new SqlParser();

    public String rewriteSql(String sqlStatement, String schemaId) {
        com.facebook.presto.sql.tree.Statement statement = SQL_PARSER.createStatement(sqlStatement, ParsingOptions.builder().build());
        SchemaAwareQueryVisitor visitor = new SchemaAwareQueryVisitor(schemaId);
        statement.accept(visitor, null);
        return SqlFormatter.formatSql(statement, Optional.empty());
    }
}

public class SchemaAwareQueryVisitor extends DefaultTraversalVisitor<Void, Void> {
    private String schemaId;

    public SchemaAwareQueryVisitor(String schemaId) {
        super();
        this.schemaId = schemaId;
    }

    /**
     * The customer can type:
     * [table name]
     * [schema].[table name]
     * [catalog].[schema].[table name]
     */
    @Override
    protected Void visitTable(Table node, Void context) {
        List<String> parts = node.getName().getParts();
        // [table name] -> is the only one we need to modify, so let's check by parts.size() ==1
        if (parts.size() == 1) {
            try {
                Field privateStringField = Table.class.getDeclaredField("name");
                privateStringField.setAccessible(true);
                QualifiedName qualifiedName = QualifiedName.of("\""+schemaId+"\"",node.getName().getParts().get(0));
                privateStringField.set(node, qualifiedName);
            } catch (NoSuchFieldException | SecurityException | IllegalArgumentException | IllegalAccessException e) {
                throw new SecurityException("Unable to execute query");
            }
        }
        return null;
        
    }
}

import static org.testng.Assert.assertEquals;

import org.gherrera.prestosqlparser.SchemaAwareQueryAdapter;
import org.testng.annotations.Test;

public class SchemaAwareTest {
        private static final String schemaId = "SCHEMA";
        private SchemaAwareQueryAdapter adapter = new SchemaAwareQueryAdapter();

        @Test
        public void testAppendSchemaA() {
            String sql = "select * from tableA";
            String bound = adapter.rewriteSql(sql, schemaId);
            assertEqualsFormattingStripped(bound,
                         "select * from \"SCHEMA\".tableA");
        }
        
        private void assertEqualsFormattingStripped(String sql1, String sql2) {
            
            assertEquals(sql1.replace("\n", " ").toLowerCase().replace("\r", " ").replaceAll(" +", " ").trim(),
                         sql2.replace("\n", " ").toLowerCase().replace("\r", " ").replaceAll(" +", " ").trim());
            
        }
}

<dependencies>
        <dependency>
            <groupId>com.facebook.presto</groupId>
            <artifactId>presto-parser</artifactId>
            <version>0.229</version>
        </dependency>
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>6.10</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

ps：我可以添加没有双引号的模式，但是我将它们添加到标识符中，标识符不能以数字开头；用双引号包围标识符错误。基本上，此错误来自SQLParser$PostProcessor.ExitDigitIdentifier(...)方法。

谢谢

Answer 1

我能够为我的案例找到一个解决方案，无论哪种方式都将分享我的发现，看看这是否是预期的行为。

因此，如果您想用双引号追加模式，您将需要创建自己的Vistor类，并且需要重写visittable方法，并且当您用schema限定表的名称时（下面是刻度），将模式作为大写传递，这样它将不匹配formatname方法上的类sqlformatter上的regex模式，并且它将添加双引号。

public class SchemaAwareQueryVisitor extends DefaultTraversalVisitor<Void, Void> {
  private String schemaId;

  public SchemaAwareQueryVisitor(String schemaId) {
    super();
    this.schemaId = schemaId;
  }

  @Override
  protected Void visitTable(Table node, Void context) {
      try {
        Field privateStringField = Table.class.getDeclaredField("name");
        privateStringField.setAccessible(true);
        QualifiedName qualifiedName = QualifiedName.of(schemaId, node.getName().getParts().get(0));
        privateStringField.set(node, qualifiedName);
      } catch (NoSuchFieldException
          | SecurityException
          | IllegalArgumentException
          | IllegalAccessException e) {
        throw new SecurityException("Unable to execute query");
      }
    return null;
  }
}