DEFINE SCHEMA target_schema FROM TABLE

Tools & Utilities
Enthusiast

Re: DEFINE SCHEMA target_schema FROM TABLE

Thank you feinholz.   So I don't confuse anyone I thought I had a single script  working to create a schema file and then use it via a include.   I thought this was working but in reality it was using the existing file (previous run) before I recreated it.

I wish there was a way to differ this include until after I create the schema.  For now I split this into two executions of tbuild.   

I also had some copy and paste issues above so here is the working schema generation script:

DEFINE JOB FILE_LOAD

DESCRIPTION 'Load a Teradata table from a file'

(

DEFINE SCHEMA SCHEMA_GEN

(

schema_out char(128)

);

/*****************************/

DEFINE OPERATOR FILE_WRITER()

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'

TYPE DATACONNECTOR CONSUMER

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName    = 'file_writer_privatelog',

VARCHAR FileName          = 'gen_schema_output.txt',

VARCHAR IndicatorMode     = 'N',

VARCHAR OpenMode          = 'Write',

VARCHAR Format            = 'TEXT',

VARCHAR TrimColumns = 'TRAILING'

);

/*****************************/

DEFINE OPERATOR EXPORT_OPERATOR()

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

TYPE EXPORT

SCHEMA SCHEMA_GEN

ATTRIBUTES

(

VARCHAR PrivateLogName = '_load_log_' || @LoadId ,

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

INTEGER MaxSessions       =  32,

INTEGER MinSessions       =  1,

VARCHAR AccountId,

VARCHAR SelectStmt        = 

'SELECT

CAST( 

CASE rn

when 1 then ''DEFINE SCHEMA source_schema (''

else '',''

end || s

||

case

when rn = cnt then  '');''

else ''''

end   

AS CHAR(128)) AS schema_out

from (

select  

ColumnName ||

CASE columnType

WHEN ''CV'' THEN ''VARCHAR('' || TRIM( TRAILING '')'' FROM SUBSTRING(columnFormat FROM 3)) || '')'' 

WHEN ''CF'' THEN ''VARCHAR('' || TRIM( TRAILING '')'' FROM SUBSTRING(columnFormat FROM 3)) || '')'' 

WHEN ''DA'' THEN ''ANSIDATE''

WHEN ''I'' THEN ''INTEGER''

WHEN ''I1'' THEN ''BYTEINT''

WHEN ''I2'' THEN ''SMALLINT''

WHEN ''I8'' THEN ''BIGINT''

WHEN ''TS'' THEN ''VARCHAR(27)''

ELSE  ''50''

END  as s,

row_number() over ( order by ColumnId) as rn,

count(*)  over ()  as cnt,

ColumnId

from  Dbc.COLUMNS c 

where TableName = ''' || @TargetTable || '''

AND DatabaseName = '''  || @jobvar_tgt_dbname || '''

) x

order by ColumnId

;'

);

/*****************************/

STEP export_to_file

(

APPLY TO OPERATOR (FILE_WRITER() )

SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );

);

);

Enthusiast

Re: DEFINE SCHEMA target_schema FROM TABLE

Looking back at my intial approach:

DEFINE SCHEMA EOD_USAGE FROM TABLE 'SONARTEST.EOD_USAGE'; 

i got it sort of working by suplying the source login information on the command line.

T:\td>tbuild   -u 'sourceTdpid='10.xx.xx.xx2' sourcerName='sonartest' sourceUserPassword='sonartest' "  -o

my current error is:

FILE_READER: TPT19003 NotifyMethod: 'None (default)'

FILE_READER: TPT19008 DataConnector Producer operator Instances: 2

FILE_READER: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR/VARDATE' schema.

FILE_READER: TPT19003 TPT Exit code set to 12.

FILE_READER: TPT19221 Total files processed: 0.

FILE_READER: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR/VARDATE' schema.

FILE_READER: TPT19003 TPT Exit code set to 12.

LOAD_OPERATOR: connecting sessions

The manual talkes about a special variable sourceFormat when set to 'Delimited' changes the generated schema output.

"SourceFormat

Before generating the DEFINE SCHEMA statement, Teradata PT queries the special job

variable SourceFormat. If has the value Delimited, then the generated schema will be in

delimited-file format; otherwise, it will be in the normal format in which the schema column

definitions closely match the Teradata Database table's column descriptions."

I tried supplting that on the command line as part of the -u" sourceFormat='Formated'.

IS there something wrong with my syntax?

-u" ...  sourceFormat='Delimited' "

I saw no change in the generated schema output.

Teradata Employee

Re: DEFINE SCHEMA target_schema FROM TABLE

I think you may be confusing a few things.

"SourceFormat" describes what the data will look like as it arrives to the Data Connector operator as a file reader. Not the file writer.

If you are trying to export from Teradata and write the data in delimited format to a flat file, then your schema must match what it looks like in the Teradata table (and you do that with your DEFINE SCHEMA . . . FROM TABLE command.

If you want your data in delimited format, then just define the format in the DC operator (as a file writer) definition. If you would rather specify that on the command line (not sure why you would want to do this), then you use TargetFormat.

-- SteveF
Enthusiast

Re: DEFINE SCHEMA target_schema FROM TABLE

Hi, No actually I reading from a file and writing to a teradata table.   It just happens that the file layout will match the Teradata table schema (althought all delimited character) since I am porting to Teradata from an identical table schema.  So I was hoping that I could just use the Teradata table layout to generate the schema for the file layout.   When I saw the description of sourceFormat I thought that might do the trick . 

Teradata Employee

Re: DEFINE SCHEMA target_schema FROM TABLE

Then use this:

DEFINE SCHEMA EOD_USAGE DELIMITED FROM TABLE 'SONARTEST.EOD_USAGE';

That will convert the data types of the columns in the table schema to their equivalent VARCHAR data types (with appropriate sizes).

No need to use SourceFormat.

-- SteveF
Enthusiast

Re: DEFINE SCHEMA target_schema FROM TABLE

Thank you so much that is what I needed!

The correct syntax is:

DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED 'SONARTEST.EOD_USAGE';  

Enthusiast

Re: DEFINE SCHEMA target_schema FROM TABLE

I'm sorry one more road block.  Can I have the table name as a variable?

I tried a few ways to no avail.

DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED @jobVar_TargetTable 

DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED ''' || @jobVar_TargetTable || '''

DEFINE SCHEMA EOD_USAGE  FROM TABLE DELIMITED '' || @jobVar_TargetTable || ''

Enthusiast

Re: DEFINE SCHEMA target_schema FROM TABLE

I see this exact example in the manual:

Example 1: Job Script with Simplified Syntax

DEFINE JOB PLOAD_JOB

DESCRIPTION 'PLOAD JOB'

(

/* Use the schema of the TargetTable for TPT_SCHEMA */

DEFINE SCHEMA TPT_SCHEMA DELIMITED @LoadTargetTable;

APPLY $INSERT @LoadTargetTable TO OPERATOR ($LOAD [@LoadInstances])

SELECT * FROM OPERATOR ($FILE_READER(TPT_SCHEMA) [@ReaderInstances]);

);

Enthusiast

Re: DEFINE SCHEMA target_schema FROM TABLE

I guess I should add it doesn't work.

Teradata Employee

Re: DEFINE SCHEMA target_schema FROM TABLE

You can put a job variable anywhere in the script you want.

We even have customers switching between the Update operator and the Stream operator and parameterizing the operator "type".

As in:

   DEFINE OPERATOR <some-name>

   TYPE @my_protocol

   . . . . .

So, yes you can use a job variable for the table name in the DEFINE SCHEMA.

In fact, we recommend it.

We recommend you use job variables for anything in the script that can change.

When you say "it does not work", please be more specific and include the job output, and version of TPT.

-- SteveF