Create table with Partition and Load_From_Teradata

Aster
New Member

Create table with Partition and Load_From_Teradata

Hi,

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
(
col1 varchar(2),

col2 integer,

col3 date)
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' )
)as
SELECT * FROM load_from_teradata(
ON mr_driver
TDPID('hostname')
USERNAME('username')
PASSWORD('password')
QUERY('select * from schema.table'))

  • Load_From_Teradata
1 REPLY
Teradata Employee

Re: Create table with Partition and Load_From_Teradata

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.