I am trying to create a table with partition along with loading data from aster in the same query. Is there a way to do that. Here is what i am trying to do - I am getting a syntax error. Please help
Note - Individually, the partition query and the load query work. But together, they dont.
create table schema.tablename
DISTRIBUTE BY HASH(some_id)
PARTITION BY RANGE(date_field)
PARTITION sep_2016( END '2016-10-01' ),
PARTITION oct_2016( END '2016-11-01' ),
PARTITION nov_2016( END '2016-12-01' ),
PARTITION dec_2016( END '2017-01-01' ),
PARTITION jan_2017( END '2017-02-01' )
SELECT * FROM load_from_teradata(
QUERY('select * from schema.table'))
CREATE TABLE AS is not supported with logically partitioned tables. When creating a logically partitioned table using data from another table, you cannot create the table using CREATE TABLE...AS SELECT in one statement as you would with a flat table. You must first create the table with its partitions, and then INSERT INTO the partitioned table. Data will automatically be inserted into the correct partitions.
Please review the Aster Database User Guide for further details.