SQL Parser Visitor + Metabase + Presto

Question

我面临着一个似乎很容易的问题，但我无法思考这个问题以找到合适的解决方案。

问题 ：
我需要将模式附加到我的SQL语句中；怪异的"；(带有双引号的模式(方式。

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

上下文
基本上，我们托管并公开了一个元数据库工具，该工具将使用Presto SQL连接我们的配置单元数据库并执行查询。

元数据库允许客户编写SQL语句，而有些客户只是不在语句上键入模式。今天，我们正在为这些查询抛出错误，但我可以很容易地从Authorization头中检索模式值，因为在我们的多租户产品中，模式是记录该用户的租户id，有了这些信息，我可以将其附加到客户SQL语句中，从而避免错误。

假设客户键入以下语句：

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

我应该将其转换为(添加"SCHEMA"(：

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

仍在尝试找到一个仅使用presto-parser和Visitor+Instrumentation解决方案的解决方案。此外，我知道JSQLParser，我尝试过，但我总是回来尝试找到一个"；普通的"；解决方案担心JSQLParser将无法支持所有的Presto/Hive查询，这些查询与标准SQL有点不同；

我在GitHub上创建了一个小项目，其中包含要验证的测试用例。。

https://github.com/genyherrera/prestosqlerror

但对于那些不想克隆存储库的人来说，以下是类和依赖项：

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：我可以在没有双引号的情况下添加模式，但我遇到了identifiers must not start with a digit; surround the identifier with double quotes错误。这个错误基本上来自SqlParser$PostProcessor.exitDigitIdentifier(...)方法。。

感谢

Answer 1

我能够为我的案例找到解决方案，无论哪种方式，我都会在Presto Slack上分享我的发现，看看这是否是预期的行为。

因此，如果你想用双引号附加你的模式，你需要创建自己的Vistor类，你需要覆盖方法visitTable，当你用模式限定表的名称时(这里是勾号(，将模式作为大写传递，这样它就不会与formatName上SqlFormatter类的正则表达式模式匹配，它会添加双引号。。

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;
}
}