Create Table as Select Query Error

Working with Data Data Loading Tools & Integrations
1

I got an error while creating table using select query, step by this doc:

mysql jdbc 5.1
starrocks 2.5.10

my query

CREATE TABLE if not exists db.tmp_table
PRIMARY KEY(id)
COMMENT 'temp table'
PROPERTIES (
"replication_num" = "1"
)
AS SELECT
       *,
       '2024' cur_year
FROM db.source_table;

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<EOF>' at line 7

Is the grammar correct or what else caused the syntax error?

2

Try it in 3.2. I believe CTAS is only available in the newer versions.

3

id is not specified in the create table SQL, like this.

CREATE TABLE if not exists test.tmp_table(id, key1) PRIMARY KEY(id) COMMENT 'temp table' PROPERTIES ("replication_num" = "1") AS 
SELECT 
  1, 
  '2024' cur_year;
5

only PROPERTIES is not supported.

CREATE TABLE if not exists db.tmp_table
PRIMARY KEY(id)
COMMENT 'temp table'
AS SELECT
       *,
       '2024' cur_year
FROM db.source_table;

without properties, ctas works.

6

The problem is Datagrip split the sql into 2 pieces if “properties” parameter starts a new line. Jesus

1 Like