"load_from_teradata" error

Aster
Teradata Employee

"load_from_teradata" error

Hi All,

I got the follow error in using "load_from_teradata" function:

My SQL statement:

CREATE FACT TABLE empty(

UserId int

)

DISTRIBUTE BY HASH(UserId);

SELECT *

FROM load_from_teradata(

ON empty

TDPID('dbccop1') -- Teradata hostname

USERNAME('dbc')

PASSWORD('dbc')

QUERY('SELECT csum(1,1) as UserId, AccountName, RowType from Accounts')

);

error output:

****************************************************** *

5/23/2012 16:27:35

SQLA Version: 13.10.0.3

System.Data.Odbc.OdbcException

ERROR [HY000] [AsterData][nCluster] ERROR: load_from_teradata must be invoked on an empty, partitioned table ()

? System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

? System.Data.Odbc.OdbcDataReader.Read()

? System.Data.Odbc.OdbcDataReader.get_HasRows()

? Teradata.SQLA.RunQry.DisplayResults(DbDataReader rdr, String stmt, Int32& rowCnt) ??

F:\ttu1310_efix_snap\tdcli\qman\sqla\RunQry.vb:?? 465

****************************************************** *

Anyone can help me? Thanks.

Tags (1)
8 REPLIES
Enthusiast

Re: "load_from_teradata" error

Hi,

 Were you able to resolve this issue ? I'm trying to load data from Teradata and keep hitting this error.

ERROR:  load_from_teradata must be invoked on an empty, partitioned table

Please refer to the Aster Database Administrator Guide for correct usage of the Teradata-Aster Data Connector

Thanks,

Sudheer.

Re: "load_from_teradata" error

Hi,

As per the user guide "The columns in the Aster Database table must exactly match the names and structure of the columns in the Teradata table(s) from which the data will be copied."

In your code the FACT Table "empty" has ONE column "UserId" however you are trying to load the data from THREE columns (UserId, AccountName, RowType) of Teradata database table "Accounts"

I will suggest you to add two coulmns AccountName and RowType in the definitions of Aster table "empty".

CREATE FACT TABLE empty(

UserId int
AccountName varchar --change the datatype if not compatible
RowType varchar --change the datatype if not compatible
)
DISTRIBUTE BY HASH(UserId);

SELECT *
FROM load_from_teradata(
ON empty
TDPID('dbccop1') -- Teradata hostname
USERNAME('dbc')
PASSWORD('dbc')
QUERY('SELECT csum(1,1) as UserId, AccountName, RowType from Accounts')
);

There is one more approach using CTAS, below is the code for the same

CREATE FACT TABLE empty 
DISTRIBUTE BY HASH(UserId)
AS
SELECT *
FROM load_from_teradata(
ON empty
TDPID('dbccop1') -- Teradata hostname
USERNAME('dbc')
PASSWORD('dbc')
QUERY('SELECT csum(1,1) as UserId, AccountName, RowType from Accounts')
);

Let me know if this was helpful and resolve the issue.

Thanks,

Saurabh

Infocepts

Teradata Employee

Re: "load_from_teradata" error

Hi, 

Were you able to solve this issue ? I have the same problem even the dummy table has the same structure with original table. (Though the document says it is not necessary) 

Teradata Employee

Re: "load_from_teradata" error

The solution is as follows. It worked for me. 

Disconnect all clients from Aster database. Login via putty to your queen node.

"su" to root.

#cd /home/beehive/bin/utils/support

Step 1: ./SetConcurrency.py --setConcurrency=0

Step 2: /home/beehive/bin/exec/changePartitionCountExec --desiredPartitionCount=2

Step 3: ./SetConcurrency.py --setConcurrency=100

Step 4: Perform balance data operation (if required).

Enthusiast

Re: "load_from_teradata" error

setConcurrency and Partitioncount are system level settings ( associated with Workload management) and you will get differnet error they are set in correctly.  setConcurrency can also be used as Disable Logons on Teradata.

Enthusiast

Re: "load_from_teradata" error

Did you added INSERT INTO ... in your SQL. (its missing in your post)

INSERT INTO your_target_table

 select  * 

   from load_from_teradata( on your_target_table

                            tdpid('your_source_System')

                            username('td_user')

                            PASSWORD('paswd')

                            query('select *   from your sourcetable)

                          );

Teradata Employee

Re: "load_from_teradata" error

The post from eceyildirim is the correct answer. Your Aster environment needs to have a minimum of two vworkers. This can be accomplished by adding another worker node or by using the changePartitionCountExec command and splitting the current single partitions into two partitions.

The "empty" table does not have to have the same columns as the Teradata source table. It is simply a driver table that has to be empty and physically partitioned, e.g.:

CREATE FACT TABLE empty(

A  int

)

DISTRIBUTE BY HASH(UserId);

Enthusiast

Re: "load_from_teradata" error

+1. That works for me too.

Thanks !