search

apache / shardingsphere

Empowering Data Intelligence with Distributed SQL for Sharding, Scalability, and Security Across All Databases.
Apache License 2.0
19.99k stars 6.75k forks source link

Support parsing Doris sql #31493

Open iamhucong opened 5 months ago

iamhucong commented 5 months ago

Background

Hi community.\ The ShardingSphere SQL parser engine helps users to parse SQL to create the AST (Abstract Syntax Tree) and visit the AST to get SQLStatement (Java Object). Currently, we are planning to enhance the support for Doris SQL parsing in ShardingSphere.

More details: https://shardingsphere.apache.org/document/current/en/reference/sharding/parse/

Issue Background Explanation

The current issue involves using a custom web scraping script to fetch SQL cases from the official website of a corresponding database. These SQL cases are then passed to ShardingSphere's parsing engine for analysis. For SQL cases that fail to be parsed successfully, every 3 to 5 SQL cases are grouped together as an issue.

  1. Since SQL cases are obtained through web scraping, it cannot be guaranteed that all SQL cases are correct. Please follow the following process to handle this pull request (PR).
  2. Some SQL cases may have already been fixed in other PRs. For cases that can already be executed successfully, simply leave a comment to ignore them.
  3. If a SQL case can be executed successfully without any code changes, there is no need to add a corresponding test assert file.

Task

This issue is to support more Doris sql parsing, as follows:

CREATE TABLE example_db.table_hash (     k1 DATE,     k2 DECIMAL(10, 2) DEFAULT "10.5",     k3 CHAR(10) COMMENT "string column",     k4 INT NOT NULL DEFAULT "1" COMMENT "int column" ) DUPLICATE KEY(k1, k2) COMMENT "my first table" PARTITION BY RANGE(k1) (     PARTITION p1 VALUES LESS THAN ("2020-02-01"),     PARTITION p2 VALUES LESS THAN ("2020-03-01"),     PARTITION p3 VALUES LESS THAN ("2020-04-01") ) DISTRIBUTED BY HASH(k1) BUCKETS 32 PROPERTIES (     "replication_num" = "1" )
CREATE TABLE example_db.table_hash (     k1 BIGINT,     k2 LARGEINT,     v1 VARCHAR(2048),     v2 SMALLINT DEFAULT "10" ) UNIQUE KEY(k1, k2) DISTRIBUTED BY HASH (k1, k2) BUCKETS 32 PROPERTIES(     "storage_medium" = "SSD",     "storage_cooldown_time" = "2015-06-04 00:00:00" )
CREATE TABLE table_range (     k1 DATE,     k2 INT,     k3 SMALLINT,     v1 VARCHAR(2048) REPLACE,     v2 INT SUM DEFAULT "1" ) AGGREGATE KEY(k1, k2, k3) PARTITION BY RANGE (k1, k2, k3) (     PARTITION p1 VALUES [("2014-01-01", "10", "200"), ("2014-01-01", "20", "300")),     PARTITION p2 VALUES [("2014-06-01", "100", "200"), ("2014-07-01", "100", "300")) ) DISTRIBUTED BY HASH(k2) BUCKETS 32
CREATE TABLE example_db.example_table (     k1 TINYINT,     k2 DECIMAL(10, 2) DEFAULT "10.5",     v1 HLL HLL_UNION,     v2 BITMAP BITMAP_UNION ) ENGINE=olap AGGREGATE KEY(k1, k2) DISTRIBUTED BY HASH(k1) BUCKETS 32
CREATE TABLE t1 (     id int(11) COMMENT "",     value varchar(8) COMMENT "" ) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 10 PROPERTIES (     "colocate_with" = "group1" )
CREATE TABLE t2 (     id int(11) COMMENT "",     value1 varchar(8) COMMENT "",     value2 varchar(8) COMMENT "" ) DUPLICATE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES (     "colocate_with" = "group1" )
CREATE TABLE example_db.table_hash (     k1 TINYINT,     k2 DECIMAL(10, 2) DEFAULT "10.5",     v1 CHAR(10) REPLACE,     v2 INT SUM,     INDEX k1_idx (k1) USING INVERTED COMMENT 'my first index' ) AGGREGATE KEY(k1, k2) DISTRIBUTED BY HASH(k1) BUCKETS 32 PROPERTIES (     "bloom_filter_columns" = "k2" )
‥types: [DATE]
) [types: [DATE]
‥types: [DATE]

Overall Procedure

If you intend to participate in fixing this issue, please feel free to leave a comment below the issue. Community members will assign the issue accordingly.

For example, you can leave a comment like this: "Hi, please assign this issue to me. Thank you!"

Once you have claimed the issue, please review the syntax of the SQL on the official website of the corresponding database. Execute the SQL on the respective database to ensure the correctness of the SQL syntax.

You can check the corresponding source of each SQL case on the official database website by clicking on the link provided below each case.

Next, execute the problematic SQL cases mentioned above in the database (you can quickly start the corresponding database using the Docker image for that database, and then connect to it using a client you are familiar with), to ensure that the SQL syntax itself is correct.

Fixing ANTLR Grammar Parsing Issue

Once you have confirmed the correctness of the SQL syntax, you can validate and fix the grammar parsing issue in ShardingSphere.

If you are using IntelliJ IDEA, you will need to install the ANTLR plugin before proceeding.

If it is an ANTLR parsing error message, try to repair the .g4 file by comparing it with the official database syntax until the SQL can be correctly parsed by ANTLR.

When there is no error message in the ANTLR Preview window, it means that ANTLR can correctly parse the SQL.

Visitor problem fix

After ANTLR parses SQL into an abstract syntax tree, ShardingSphere will access the abstract syntax tree through Visitor and extract the required information. If you need to extract Segments, you need to first execute:

 mvn -T 2C clean install -DskipTests

Under the shardingsphere-parser module to compile the entire parser module.\ Then rewrite the corresponding visit method in SQLStatementVisitorr as needed to extract the corresponding Segment.

Add assertion test file

After the above SQL parsing problem is repaired, the corresponding Test needs to be added. The steps are as follows:

  1. Add the corresponding sql-case in the sql/supported directory.
  2. Add case assertions in the case directory of the shardingsphere-test-it-parser module.
  3. Run org.apache.shardingsphere.test.it.sql.parser.internal.InternalSQLParserIT\ After SQL Parser IT runs successfully, you can submit a PR.

Relevant Skills

  1. Master JAVA language
  2. Have a basic understanding of Antlr g4 file
  3. Be familiar with Doris SQLs
github-actions[bot] commented 4 months ago

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

vivekkoya commented 1 month ago

Hello, I can take this task. Can you please direct me to the relevant files and directories? Thanks for the help

iamhucong commented 1 month ago

@vivekkoya Sure! You can check out other similar closed issues for reference. https://github.com/search?q=repo%3Aapache%2Fshardingsphere+Support+parsing+Doris+sql++&type=issues&state=closed