[CARBONDATA-4297] Create table(Carbon and Parquet) with combination of partitioned by, Clustered by, Sorted by and with options parameter and insert overwrite fails with parser errors in Carbon session in Spark 2.4.5

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 2.3.0
• Component/s: sql
• Environment: Spark 2.4.5
• Assignee: Unassigned
• Reporter: Chetan Bhat
• Created: 05/Oct/21 05:50
• Updated: 08/Oct/21 07:24

Description

Issue 1 : Create table (Carbon and Parquet) with combination of partitioned by, Clustered by, Sorted by fails -

Queries-

CREATE TABLE t (a STRING, b INT, c STRING, d STRING) stored as carbondata
OPTIONS (a '1', b '2')
PARTITIONED BY (c, d) CLUSTERED BY (a) SORTED BY (b ASC) INTO 2 BUCKETS
COMMENT 'table_comment'
TBLPROPERTIES (t 'test');
CREATE TABLE t (a STRING, b INT, c STRING, d STRING) stored as parquet
OPTIONS (a '1', b '2')
PARTITIONED BY (c, d) CLUSTERED BY (a) SORTED BY (b ASC) INTO 2 BUCKETS
COMMENT 'table_comment'
TBLPROPERTIES (t 'test');

0: jdbc:hive2://7.187.185.158:23040/default> CREATE TABLE t (a STRING, b INT, c STRING, d STRING) stored as carbondata
0: jdbc:hive2://7.187.185.158:23040/default> OPTIONS (a '1', b '2')
0: jdbc:hive2://7.187.185.158:23040/default> PARTITIONED BY (c, d) CLUSTERED BY (a) SORTED BY (b ASC) INTO 2 BUCKETS
0: jdbc:hive2://7.187.185.158:23040/default> COMMENT 'table_comment'
0: jdbc:hive2://7.187.185.158:23040/default> TBLPROPERTIES (t 'test');
Error: org.apache.spark.sql.AnalysisException: == Spark Parser: org.apache.spark.sql.execution.SparkSqlParser ==

mismatched input 'OPTIONS' expecting <EOF>(line 2, pos 0)

== SQL ==
CREATE TABLE t (a STRING, b INT, c STRING, d STRING) stored as carbondata
OPTIONS (a '1', b '2')
^^^
PARTITIONED BY (c, d) CLUSTERED BY (a) SORTED BY (b ASC) INTO 2 BUCKETS
COMMENT 'table_comment'
TBLPROPERTIES (t 'test')

== Carbon Parser: org.apache.spark.sql.parser.CarbonExtensionSpark2SqlParser ==
[1.8] failure: identifier matching regex (?i)MATERIALIZED expected

CREATE TABLE t (a STRING, b INT, c STRING, d STRING) stored as carbondata
^;
== Antlr Parser: org.apache.spark.sql.parser.CarbonAntlrParser ==
Antlr SQL Parser will only deal with Merge Into SQL Command; (state=,code=0)
0: jdbc:hive2://7.187.185.158:23040/default> CREATE TABLE t (a STRING, b INT, c STRING, d STRING) stored as parquet
0: jdbc:hive2://7.187.185.158:23040/default> OPTIONS (a '1', b '2')
0: jdbc:hive2://7.187.185.158:23040/default> PARTITIONED BY (c, d) CLUSTERED BY (a) SORTED BY (b ASC) INTO 2 BUCKETS
0: jdbc:hive2://7.187.185.158:23040/default> COMMENT 'table_comment'
0: jdbc:hive2://7.187.185.158:23040/default> TBLPROPERTIES (t 'test');
Error: org.apache.spark.sql.AnalysisException: == Spark Parser: org.apache.spark.sql.execution.SparkSqlParser ==

mismatched input 'OPTIONS' expecting <EOF>(line 2, pos 0)

== SQL ==
CREATE TABLE t (a STRING, b INT, c STRING, d STRING) stored as parquet
OPTIONS (a '1', b '2')
^^^
PARTITIONED BY (c, d) CLUSTERED BY (a) SORTED BY (b ASC) INTO 2 BUCKETS
COMMENT 'table_comment'
TBLPROPERTIES (t 'test')

== Carbon Parser: org.apache.spark.sql.parser.CarbonExtensionSpark2SqlParser ==
[1.8] failure: identifier matching regex (?i)MATERIALIZED expected

CREATE TABLE t (a STRING, b INT, c STRING, d STRING) stored as parquet
^;
== Antlr Parser: org.apache.spark.sql.parser.CarbonAntlrParser ==
Antlr SQL Parser will only deal with Merge Into SQL Command; (state=,code=0)
0: jdbc:hive2://7.187.185.158:23040/default>

 

Issue 2 : Create table with options parameter fails-

Queries-

CREATE TABLE tbl (a INT, b STRING, c INT) stored as carbondata OPTIONS ('a' 1);
CREATE TABLE tbl1 (a INT, b STRING, c INT) stored as parquet OPTIONS ('a' 1);

 

0: jdbc:hive2://7.187.185.158:23040/default> CREATE TABLE tbl (a INT, b STRING, c INT) stored as carbondata OPTIONS ('a' 1);
Error: org.apache.spark.sql.AnalysisException: == Spark Parser: org.apache.spark.sql.execution.SparkSqlParser ==

mismatched input 'OPTIONS' expecting <EOF>(line 1, pos 63)

== SQL ==
CREATE TABLE tbl (a INT, b STRING, c INT) stored as carbondata OPTIONS ('a' 1)
---------------------------------------------------------------^^^

== Carbon Parser: org.apache.spark.sql.parser.CarbonExtensionSpark2SqlParser ==
[1.8] failure: identifier matching regex (?i)MATERIALIZED expected

CREATE TABLE tbl (a INT, b STRING, c INT) stored as carbondata OPTIONS ('a' 1)
^;
== Antlr Parser: org.apache.spark.sql.parser.CarbonAntlrParser ==
Antlr SQL Parser will only deal with Merge Into SQL Command; (state=,code=0)
0: jdbc:hive2://7.187.185.158:23040/default> CREATE TABLE tbl1 (a INT, b STRING, c INT) stored as parquet OPTIONS ('a' 1);
Error: org.apache.spark.sql.AnalysisException: == Spark Parser: org.apache.spark.sql.execution.SparkSqlParser ==

mismatched input 'OPTIONS' expecting <EOF>(line 1, pos 61)

== SQL ==
CREATE TABLE tbl1 (a INT, b STRING, c INT) stored as parquet OPTIONS ('a' 1)
-------------------------------------------------------------^^^

== Carbon Parser: org.apache.spark.sql.parser.CarbonExtensionSpark2SqlParser ==
[1.8] failure: identifier matching regex (?i)MATERIALIZED expected

CREATE TABLE tbl1 (a INT, b STRING, c INT) stored as parquet OPTIONS ('a' 1)
^;
== Antlr Parser: org.apache.spark.sql.parser.CarbonAntlrParser ==
Antlr SQL Parser will only deal with Merge Into SQL Command; (state=,code=0)

 

Issue 3: insert overwrite fails with parser error

drop table if exists brinjal;
create table brinjal (imei string,AMSize string,channelsId string,ActiveCountry string, Activecity string,gamePointId double,deviceInformationId double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double) stored as carbondata ;
insert into brinjal select 'aaa','bbb','ccc','ddd','eee',98652,98652,'2018-01-19 13:02:07.724','2018-01-19 13:02:07.724',99875;
select count from brinjal;
--insert overwrite
insert overwrite brinjal select 'aaa','bbb','ccc','ddd','eee',98652,98652,'2018-01-19 13:02:07.724','2018-01-19 13:02:07.724',99875;

 

0: jdbc:hive2://7.187.185.158:23040/default> create table brinjal (imei string,AMSize string,channelsId string,ActiveCountry string, Activecity string,gamePointId double,deviceInformationId double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double) stored as carbondata ;
---------+

Result

---------+
---------+
No rows selected (0.084 seconds)
0: jdbc:hive2://7.187.185.158:23040/default> insert into brinjal select 'aaa','bbb','ccc','ddd','eee',98652,98652,'2018-01-19 13:02:07.724','2018-01-19 13:02:07.724',99875;
-------------+

Segment ID

-------------+

0

-------------+
1 row selected (0.468 seconds)
0: jdbc:hive2://7.187.185.158:23040/default> insert overwrite brinjal select 'aaa','bbb','ccc','ddd','eee',98652,98652,'2018-01-19 13:02:07.724','2018-01-19 13:02:07.724',99875;
Error: org.apache.spark.sql.AnalysisException: == Spark Parser: org.apache.spark.sql.execution.SparkSqlParser ==

no viable alternative at input 'insert overwrite brinjal'(line 1, pos 17)

== SQL ==
insert overwrite brinjal select 'aaa','bbb','ccc','ddd','eee',98652,98652,'2018-01-19 13:02:07.724','2018-01-19 13:02:07.724',99875
-----------------^^^

== Carbon Parser: org.apache.spark.sql.parser.CarbonExtensionSpark2SqlParser ==
[1.8] failure: identifier matching regex (?i)INTO expected

insert overwrite brinjal select 'aaa','bbb','ccc','ddd','eee',98652,98652,'2018-01-19 13:02:07.724','2018-01-19 13:02:07.724',99875
^;
== Antlr Parser: org.apache.spark.sql.parser.CarbonAntlrParser ==
Antlr SQL Parser will only deal with Merge Into SQL Command; (state=,code=0)
0: jdbc:hive2://7.187.185.158:23040/default>