Github user chenliang613 commented on a diff in the pull request:

    https://github.com/apache/carbondata/pull/1939#discussion_r168372650
  
    --- Diff: 
integration/spark-common-test/src/test/scala/org/apache/carbondata/spark/testsuite/createTable/TestCreateTableAsSelect.scala
 ---
    @@ -170,10 +175,277 @@ class TestCreateTableAsSelect extends QueryTest with 
BeforeAndAfterAll {
         }
       }
     
    +  test("test create table as select with where clause in select from 
parquet table that does not return data") {
    +    sql("DROP TABLE IF EXISTS ctas_select_where_parquet")
    +    sql(
    +      """
    +        | CREATE TABLE ctas_select_where_parquet
    +        | STORED BY 'carbondata'
    +        | as select * FROM parquet_ctas_test
    +        | where key=300""".stripMargin)
    +    checkAnswer(sql("SELECT * FROM ctas_select_where_parquet"),
    +      sql("SELECT * FROM parquet_ctas_test where key=300"))
    +  }
    +
    +  test("test create table as select with where clause in select from 
hive/orc table that does not return data") {
    +    sql("DROP TABLE IF EXISTS ctas_select_where_orc")
    +    sql(
    +      """
    +        | CREATE TABLE ctas_select_where_orc
    +        | STORED BY 'carbondata'
    +        | AS SELECT * FROM orc_ctas_test
    +        | where key=300""".stripMargin)
    +    checkAnswer(sql("SELECT * FROM ctas_select_where_orc"),
    +      sql("SELECT * FROM orc_ctas_test where key=300"))
    +  }
    +
    +  test("test create table as select with select from same carbon table 
name with if not exists clause") {
    +    sql("drop table if exists ctas_same_table_name")
    +    sql("CREATE TABLE ctas_same_table_name(key INT, value STRING) STORED 
BY 'carbondata'")
    +    checkExistence(sql("SHOW TABLES"), true, "ctas_same_table_name")
    +    sql(
    +      """
    +        | CREATE TABLE IF NOT EXISTS ctas_same_table_name
    +        | STORED BY 'carbondata'
    +        | AS SELECT * FROM ctas_same_table_name
    +      """.stripMargin)
    +    intercept[Exception] {
    +      sql(
    +        """
    +          | CREATE TABLE ctas_same_table_name
    +          | STORED BY 'carbondata'
    +          | AS SELECT * FROM ctas_same_table_name
    +        """.stripMargin)
    +    }
    +  }
    +
    +  test("test create table as select with select from same carbon table 
name with if not exists clause and source table not exists") {
    +    sql("DROP TABLE IF EXISTS ctas_same_table_name")
    +    checkExistence(sql("SHOW TABLES"), false, "ctas_same_table_name")
    +    intercept[Exception] {
    +      sql(
    +        """
    +          | CREATE TABLE IF NOT EXISTS ctas_same_table_name
    +          | STORED BY 'carbondata'
    +          | AS SELECT * FROM ctas_same_table_name
    +        """.stripMargin)
    +    }
    +  }
    +
    +  test("test create table as select with select from same carbon table 
name with if not exists clause and source table exists") {
    +    sql("DROP TABLE IF EXISTS ctas_same_table_name")
    +    sql("DROP TABLE IF EXISTS ctas_if_table_name")
    +    sql("CREATE TABLE ctas_same_table_name(key INT, value STRING) STORED 
BY 'carbondata'")
    +    sql(
    +      """
    +        | CREATE TABLE IF NOT EXISTS ctas_if_table_name
    +        | STORED BY 'carbondata'
    +        | AS SELECT * FROM ctas_same_table_name
    +      """.stripMargin)
    +    checkExistence(sql("show tables"), true, "ctas_if_table_name")
    +  }
    +
    +  test("add example for documentation") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED AS parquet
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,avg(age) FROM source_table group by city
    +      """.stripMargin)
    +    // results:
    +    //    sql("SELECT * FROM target_table").show
    +    //    +--------+--------+
    +    //    |    city|avg(age)|
    +    //    +--------+--------+
    +    //    |shenzhen|    29.0|
    +    //    +--------+--------+
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 
29)))
    +  }
    +
    +  test("test create table as select with sum,count,min,max") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        | STORED BY 'carbondata'
    +      """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,sum(age),count(age),min(age),max(age)
    +        |   FROM source_table group by city
    +      """.stripMargin)
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 58, 
2, 27, 31)))
    +  }
    +
    +  test("test create table as select with insert data into source_table 
after CTAS") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED BY 'carbondata'
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,sum(age),count(age),min(age),max(age)
    +        |   FROM source_table group by city
    +      """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 58, 
2, 27, 31)))
    +  }
    +
    +  test("test create table as select with auto merge") {
    +    CarbonProperties.getInstance().
    +      addProperty(CarbonCommonConstants.ENABLE_AUTO_LOAD_MERGE, "true")
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED BY 'carbondata'
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,avg(age)
    +        |   FROM source_table group by city
    +      """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +
    +    checkExistence(sql("SHOW SEGMENTS FOR TABLE source_table"), true, 
"Compacted")
    +    checkExistence(sql("SHOW SEGMENTS FOR TABLE target_table"), false, 
"Compacted")
    +
    +    sql("INSERT INTO target_table SELECT 'shenzhen',8")
    +    sql("INSERT INTO target_table SELECT 'shenzhen',9")
    +    sql("INSERT INTO target_table SELECT 'shenzhen',3")
    +    checkExistence(sql("SHOW SEGMENTS FOR TABLE target_table"), true, 
"Compacted")
    +    checkAnswer(sql("SELECT * FROM target_table"),
    +      Seq(Row("shenzhen", 29), Row("shenzhen", 8), Row("shenzhen", 9), 
Row("shenzhen", 3)))
    +    CarbonProperties.getInstance().
    +      addProperty(CarbonCommonConstants.ENABLE_AUTO_LOAD_MERGE,
    +        CarbonCommonConstants.DEFAULT_ENABLE_AUTO_LOAD_MERGE)
    +  }
    +
    +  test("test create table as select with filter, <, and, >=") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED BY 'carbondata'
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql("INSERT INTO source_table SELECT 3,'jack','shenzhen',5")
    +    sql("INSERT INTO source_table SELECT 4,'alice','shenzhen',35")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,avg(age)
    +        |   FROM source_table where age > 20 and age <= 31 GROUP BY city
    +      """.stripMargin)
    +
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 
29)))
    +  }
    +
    +  test("test create table as select with filter, >=, or, =") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED BY 'carbondata'
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql("INSERT INTO source_table SELECT 3,'jack','shenzhen',5")
    +    sql("INSERT INTO source_table SELECT 4,'alice','shenzhen',35")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,avg(age)
    +        |   FROM source_table where age >= 20 or age = 5 group by city
    +      """.stripMargin)
    +
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 
24.5)))
    +  }
    +
       override def afterAll {
         sql("DROP TABLE IF EXISTS carbon_ctas_test")
         sql("DROP TABLE IF EXISTS parquet_ctas_test")
         sql("DROP TABLE IF EXISTS orc_ctas_test")
    +    sql("DROP TABLE IF EXISTS ctas_same_table_name")
    +    sql("DROP TABLE IF EXISTS ctas_select_carbon")
    +    sql("DROP TABLE IF EXISTS ctas_select_direct_data")
    +    sql("DROP TABLE IF EXISTS ctas_select_parquet")
    +    sql("DROP TABLE IF EXISTS ctas_select_orc")
    +    sql("DROP TABLE IF EXISTS ctas_select_where_carbon")
    +    sql("DROP TABLE IF EXISTS ctas_select_where_parquet")
    +    sql("DROP TABLE IF EXISTS ctas_select_where_orc")
    +    sql("DROP TABLE IF EXISTS ctas_tblproperties_test")
    +    sql("DROP TABLE IF EXISTS ctas_if_table_name")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    sql("DROP TABLE IF EXISTS target_table")
    --- End diff --
    
    @manishgupta88  so your point is that : CTAS feature is reusing "create 
table and insert into" flows, so you proposed to add test cases from "create 
table and insert into" perspective , not from CTAS perspective ?  If my 
understanding is right,  i agree with manish's point.


---

Reply via email to