All Forums Aster
Tomas.Li 1 post Joined 11/11
23 May 2012
"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.

 

Sudheer501 5 posts Joined 02/10
14 Feb 2013

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.
 

smaithil 1 post Joined 01/13
27 Feb 2013

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

eceyildirim 2 posts Joined 03/13
28 Mar 2013

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) 
 

eceyildirim 2 posts Joined 03/13
28 Mar 2013

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).

ahnajeeb 5 posts Joined 03/11
28 Mar 2013

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.

AN

ahnajeeb 5 posts Joined 03/11
28 Mar 2013

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)
                          );

AN

snoynaert 3 posts Joined 03/12
18 Apr 2013

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);
 
 

Qionglinnewbie 3 posts Joined 09/13
09 Jan 2014

+1. That works for me too.
Thanks !

You must sign in to leave a comment.