Support Describe Pinot table and schema

[xiangfu0]

Pinot table conf and schema access is from restful API only right now.

It would be good to support the built-in system tables, and we can query schema in SQL style.
This will be useful for users to have a similar onboarding experience that everything can come from the query console.
It's also good for Pinot integrations with other systems.

Query examples for references:

SHOW TABLES

DESCRIBE TABLE myTable

SHOW COLUMNS FROM myTable

SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA <> 'INFORMATION_SCHEMA'

[amarnathkarthik]

@fx19880617 I can give it a try, but since I'm fairly new to pinot, would you be able to point me to the entry point for query processing or any help to get started with this issue would be helpful.

[xiangfu0]

@fx19880617 I can give it a try, but since I'm fairly new to pinot, would you be able to point me to the entry point for query processing or any help to get started with this issue would be helpful.

Thanks!

The entrypoint of query processing starts from the parser: https://github.com/apache/incubator-pinot/blob/master/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java

You can parse the query and set corresponding objects to PinotQuery(https://github.com/apache/incubator-pinot/blob/master/pinot-common/src/main/java/org/apache/pinot/common/request/PinotQuery.java). You may need to add new fields if existing ones are not sufficient.

Then RequestHandler should be able to return the results directly based on the table information.

[amarnathkarthik]

Thanks! Please assign this issue to me, meanwhile, I'll start my analysis.

[amarnathkarthik]

@fx19880617 Here is my initial analysis, correct me if I'm wrong.
Calcite does not support SHOW (databases | tables) kind of SQL, below are the available implementations; therefore DESCRIBE TABLE <table_name> should work. To list all tables will have to go with SELECT table_name FROM information_schema.TABLES; and to list all schema then SELECT table_name FROM information_schema.SCHEMATA;

SqlAlter
SqlBasicCall
SqlCase
SqlCreate
SqlDdl
SqlDelete
SqlDescribeSchema
SqlDescribeTable
SqlDrop
SqlExplain
SqlInsert
SqlJoin
SqlMatchRecognize
SqlMerge
SqlOrderBy
SqlSelect
SqlSetOption
SqlSnapshot
SqlUpdate
SqlWindow
SqlWith
SqlWithItem

[xiangfu0]

@fx19880617 Here is my initial analysis, correct me if I'm wrong.
Calcite does not support SHOW (databases | tables) kind of SQL, below are the available implementations; therefore DESCRIBE TABLE <table_name> should work. To list all tables will have to go with SELECT table_name FROM information_schema.TABLES; and to list all schema then SELECT table_name FROM information_schema.SCHEMATA;

SqlAlter
SqlBasicCall
SqlCase
SqlCreate
SqlDdl
SqlDelete
SqlDescribeSchema
SqlDescribeTable
SqlDrop
SqlExplain
SqlInsert
SqlJoin
SqlMatchRecognize
SqlMerge
SqlOrderBy
SqlSelect
SqlSetOption
SqlSnapshot
SqlUpdate
SqlWindow
SqlWith
SqlWithItem

For queries like SHOW TABLES xxx, since it's fairly simple syntax, I feel we can just parse the query then show the results.

Since calcite supports SqlDescribeTable, then we can use it for table describe query.

Once we parse the information from the query, we can call controller APIs to fetch the info.

[amarnathkarthik]

Thanks for the clarification!

Yes, parsing SHOW <OBJECT> should be straight forward implementation.
Here is the list of SHOW SQL Statements in the scope (Please add if I have missed any):

1. SHOW TABLES
2. SHOW COLUMNS FROM <table_name>

Question:
Looking at PinotQuery.java and BrokerResponse.java it's implemented mainly for SELECT SQL statement adding DESCRIBE and SHOW would have to make quite a few changes to handle this. Instead, what do you think about the below implementation:

1. Entry point - PinotClientRequest.processSqlQueryPost - No change
2. In BaseBrokerRequestHandler.handleRequest, call is made to CalciteSqlComplier.compileToPinotQuery based on query format which in this case would be CALCITE_SQL_COMPILER for SQL. Instead enhance PinotQueryParserFactory in switch to parse the SQL type (a) SHOW SQL, (b) DESCRIBE SQL, and (c) all others. Will add subclass to implement interface AbstractCompiler.compileToBrokerRequest method for case (a) and (b) which can be enhanced later to handle other cases within the scope like LIKE | WHERE.
3. In the query executor - implement overloading loading method to handle (a) and (b) and add Switch based on PinotQueryParserFactory to all overloading methods.
4. In the handler, implement call to Controller REST API and return BrokerResponse.

Sorry for not having concise, but let me know your thoughts! Thanks!

[amarnathkarthik]

@fx19880617 Could you please review the above approach and let me know your thoughts. Thanks!

[xiangfu0]

@fx19880617 Could you please review the above approach and let me know your thoughts. Thanks!

Thanks! Will do!

[xiangfu0]

Thanks for the clarification!

Yes, parsing SHOW <OBJECT> should be straight forward implementation.
Here is the list of SHOW SQL Statements in the scope (Please add if I have missed any):

1. SHOW TABLES
2. SHOW COLUMNS FROM <table_name>

Question:
Looking at PinotQuery.java and BrokerResponse.java it's implemented mainly for SELECT SQL statement adding DESCRIBE and SHOW would have to make quite a few changes to handle this. Instead, what do you think about the below implementation:

1. Entry point - PinotClientRequest.processSqlQueryPost - No change
2. In BaseBrokerRequestHandler.handleRequest, call is made to CalciteSqlComplier.compileToPinotQuery based on query format which in this case would be CALCITE_SQL_COMPILER for SQL. Instead enhance PinotQueryParserFactory in switch to parse the SQL type (a) SHOW SQL, (b) DESCRIBE SQL, and (c) all others. Will add subclass to implement interface AbstractCompiler.compileToBrokerRequest method for case (a) and (b) which can be enhanced later to handle other cases within the scope like LIKE | WHERE.
3. In the query executor - implement overloading loading method to handle (a) and (b) and add Switch based on PinotQueryParserFactory to all overloading methods.
4. In the handler, implement call to Controller REST API and return BrokerResponse.

Sorry for not having concise, but let me know your thoughts! Thanks!

Hi @amarnathkarthik , thanks for sharing the thoughts! I think it's overall good! Since we are moving to SQL and plan to deprecate PQL, we can actually move this logic completely to CalciteParser.

I also looked at CalciteSqlParser, for SHOW TABLES, it throws org.apache.calcite.sql.parser.SqlParseException: Non-query expression encountered in illegal context. For query like DESCRIBE TABLE myTable, it generates a SqlDescribeTable node.

Then one way to solve it is to extend SqlCall to SqlShowTables class and update CalciteSqlParser parse the query to it.

Then we can check sqlNode instance type for it:
We have SqlOrderBy and SqlSelect for now, then we can add SqlShowTables/SqlDescribeTable nodes.

Example here in Flink:

https://github.com/apache/flink/tree/master/flink-table/flink-sql-parser/src/main/java/org/apache/flink/sql/parser/dql

https://github.com/apache/flink/blob/master/flink-table/flink-sql-parser/src/main/java/org/apache/flink/sql/parser/dql/SqlShowTables.java

https://github.com/apache/flink/blob/master/flink-table/flink-sql-parser/src/main/codegen/includes/parserImpls.ftl#L401