All Forums Aster
QI.YOU 13 posts Joined 03/13
01 Aug 2013
Aster_function load_from_teradata

i happened to this error,"SELECT Failed. 34:  ERROR: The supplied statement could not be completed due to an internal error at a database worker node. Please contact support at Aster Data. ()" my code as follow
CREATE  TABLE ship(
comp_id  varchar(20)
)
DISTRIBUTE BY HASH(comp_id);
 
insert into ship
SELECT * FROM load_from_teradata
( ON ship
     tdpid('excop1')username('abc')password('abc')
     QUERY('select comp_id from PMART_KPI.MANAGER_QUANTITY')
)
 
Anyone one can help? Thanks.
 
 
 

 
 

Tags:
dnoeth 4628 posts Joined 11/04
02 Aug 2013

 
Don'T use your target table in load_from_teradata, use a dummy instead.
 
Teradata Aster Big Analytics Appliance Database User Guide
Teradata-Aster Database Connector
 
Copying Data from Teradata to Aster Database

 
The load_from_teradata SQL-MR function must be invoked on a partitioned fact table in
Aster Database. This is usually done by using a dummy table, which will be referred to as
“mr_driver”, created as follows:
CREATE TABLE mr_driver(
c1 INT)
DISTRIBUTE BY HASH (c1)
;

insert into ship
SELECT * FROM load_from_teradata
( ON mr_driver
     tdpid('excop1')username('abc')password('abc')
     QUERY('select comp_id from PMART_KPI.MANAGER_QUANTITY')
)

Dieter

Dieter

QI.YOU 13 posts Joined 03/13
14 Aug 2013

STILL GET THIS ERROR
 
SQLA Version: 14.0.0.1
Driver Version: ODBC 05.0.0000
System.Data.Odbc.OdbcException: ERROR [HY000] [AsterData][nCluster] ERROR: The supplied statement could not be completed due to an internal error at a database worker node.
Please contact support at Aster Data. ()
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcDataReader.Read()
   at System.Data.Odbc.OdbcDataReader.get_HasRows()
   at Teradata.SQLA.RunQry.DisplayResults(DbDataReader rdr, String stmt, Int32& rowCnt) in F:\ttu140_efix_snap\tdcli\qman\sqla\RunQry.vb:line 465

QI.YOU 13 posts Joined 03/13
14 Aug 2013

Aster runs on virtual machine with two workers, And the aster database table exactly match teradata table.

dnoeth 4628 posts Joined 11/04
15 Aug 2013

Did you use the mr_driver table? Could you post your full code?
Dieter

Dieter

QI.YOU 13 posts Joined 03/13
18 Aug 2013

mr_driver table :    
CREATE TABLE mr_driver(
comp_id  varchar(20))
DISTRIBUTE BY HASH (comp_id)
MR_SQL: 
SELECT
*
FROM load_from_teradata(
ON mr_driver
 tdpid('excop1')username('abc')password('abc')
     QUERY('select comp_id  from PMART_KPI.MANAGER_QUANTITY')
);
 
And the "comp_id" is varchar(20) in orginal database.

ulrich 816 posts Joined 09/09
19 Aug 2013

Hi,
the my_driver table is some kind of dummy and should be exactly defined as in the example.
The code below works for me
Ulrich

CREATE TABLE mr_driver( c1 INT)
DISTRIBUTE BY HASH (c1) ;

CREATE TABLE td_calendar DISTRIBUTE BY HASH(day_of_calendar) AS SELECT
* FROM load_from_teradata(
ON mr_driver -- empty Aster mr_driver table 
TDPID('myTD') -- Teradata hostname 
USERNAME('myUser') 
PASSWORD('myPassword')
NUM_INSTANCES('2')
QUERY('SELECT * FROM sys_calendar.calendar')
);

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

QI.YOU 13 posts Joined 03/13
20 Aug 2013

unfortunately,it not works for me ! because the aster runs on virtual machine??

ulrich 816 posts Joined 09/09
20 Aug 2013

Most likely to be a network issue where is can be very ugly to track this down as the issue can be on every layer (VM, host, firewall...). 
Are you sure that your Aster as access to your TD box? 
Can your virtual machine reach the TD via ping? SSH? 
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

QI.YOU 13 posts Joined 03/13
22 Aug 2013

it's fixed , its network issue, Thanks, and one more question, how to encode character to UTF-8 when loading from TD to Aster (load_from_teradata).

MrsCrystie 1 post Joined 12/13
06 Jan 2014

I am having an issue can you please help?  Can you tell me how to call the TDPID, username/password from logon file?  For instance I need to replace the hard code with a parameter file with the TDPID, username/password list in the file.
 

1

2

3

4

5

6

7

8

9

10

11

12

CREATE TABLE mr_driver( c1 INT)

DISTRIBUTE BY HASH (c1) ;

 

CREATE TABLE td_calendar DISTRIBUTE BY HASH(day_of_calendar) AS SELECT

* FROM load_from_teradata(

ON mr_driver -- empty Aster mr_driver table

TDPID('myTD') -- Teradata hostname

USERNAME('myUser')

PASSWORD('myPassword')

NUM_INSTANCES('2')

QUERY('SELECT * FROM sys_calendar.calendar')

);

 

Boulderman 20 posts Joined 10/09
06 Jan 2014

I have about 800gb data sitting on HDFS that I am trying to get to Aster. Till yet the only way I found is to use  Aster Loader tools to  directly suck it from HDFS or land those files at a mount point and load to Aster but the time taken is far too much  .  Is there any way to optimize the loading -  in terms of
-- table DDL - the hash distribution part . In TD we could create a No PI table that will be  like a huge storage bag and we can  process the next step from there - how about Aster - could the distibution step be taking time, how about if we say - replicated table - would that help change some of the timings
-- Load methodoloy : BOMK - I dont think we could have a SQL function or some ODBC / JDBC driven method thats comparable to what the loader gives. 
Dieter  - what would you say are some other avenues to explore .
TYSM
Sam

Alekh 1 post Joined 09/14
25 Sep 2014

HI QI.YOU,
I was going through your post and i am facing the same issue.My aster is also running on virtual machine,one Vm for Queen and one VM for Worker and one for TD.I am not able to reach Td from aster queen.It is giving me message "Network Unreachable".
Can you please help me how you solved your network issue problem and conncted aster database with Teradata.Thanks in advance for the help.!
 
Alekh

You must sign in to leave a comment.