You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by GitBox <gi...@apache.org> on 2020/11/27 21:20:05 UTC
[GitHub] [incubator-pinot] fx19880617 opened a new issue #6298: Make pinot schema queryable
fx19880617 opened a new issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298
Pinot table 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:
```
SHOW COLUMNS FROM table
```
or
```
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA <> 'INFORMATION_SCHEMA'
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] amarnathkarthik commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
amarnathkarthik commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-756919320
@fx19880617 Could you please review the above approach and let me know your thoughts. Thanks!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] amarnathkarthik commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
amarnathkarthik commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-752363299
@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. Would you want me to implement this first?
```
SqlAlter
SqlBasicCall
SqlCase
SqlCreate
SqlDdl
SqlDelete
SqlDescribeSchema
SqlDescribeTable
SqlDrop
SqlExplain
SqlInsert
SqlJoin
SqlMatchRecognize
SqlMerge
SqlOrderBy
SqlSelect
SqlSetOption
SqlSnapshot
SqlUpdate
SqlWindow
SqlWith
SqlWithItem
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] fx19880617 edited a comment on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
fx19880617 edited a comment on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-751450902
> @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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] fx19880617 commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
fx19880617 commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-752394089
> @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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] fx19880617 commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
fx19880617 commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-757056076
> @fx19880617 Could you please review the above approach and let me know your thoughts. Thanks!
Thanks! Will do!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] amarnathkarthik commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
amarnathkarthik commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-751339503
@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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] amarnathkarthik edited a comment on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
amarnathkarthik edited a comment on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-752363299
@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
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] fx19880617 commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
fx19880617 commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-759335011
> 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
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] fx19880617 commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
fx19880617 commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-757056076
> @fx19880617 Could you please review the above approach and let me know your thoughts. Thanks!
Thanks! Will do!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] fx19880617 commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
fx19880617 commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-751450902
> @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). Then RequestHandler should be able to return the results directly based on the table information.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] fx19880617 edited a comment on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
fx19880617 edited a comment on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-752394089
> @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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] amarnathkarthik commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
amarnathkarthik commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-751560867
Thanks! Please assign this issue to me, meanwhile, I'll start my analysis.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] amarnathkarthik commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
amarnathkarthik commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-756919320
@fx19880617 Could you please review the above approach and let me know your thoughts. Thanks!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] amarnathkarthik commented on issue #6298: Support Describe Pinot table and schema
Posted by GitBox <gi...@apache.org>.
amarnathkarthik commented on issue #6298:
URL: https://github.com/apache/incubator-pinot/issues/6298#issuecomment-752866538
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!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org