Creating Table for Dataset

Data Modeling
Teradata Employee

Creating Table for Dataset

Hi, I am newbie to data modeling, so please bear with me if i am wrong in using terminologies, What i am trying to do is to create the table for give Unidentified Flying Objects dataset. But i am having problem with TimeStamp. and STRING data forms.

 

here is the sample dataset:

datetimecitystatecountryshapeduration_secduration_hou_mincommentsdate postedlatitudelongitude
10/10/1949 20:30san marcostxuscylinder270045 minutesThis event took place in early fall around 1949-50. It occurred after a Boy Scout meeting in the Baptist Church. The Baptist Church sit4/27/200429.88306-97.9411
10/10/1949 21:00lackland afbtx light72001-2 hrs1949 Lackland AFB&#44 TX.  Lights racing across the sky & making 90 degree turns on a dime.12/16/200529.38421-98.5811

 

Here is the table i am trying t create: 

 

 SyntaxEditor Code Snippet

CREATE MULTISET TABLE AP.ufo_report ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      datetime TIMESTAMP(6) FORMAT 'MM/DD/YYYYBHH:MI',
      city VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      state CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      Country CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      shape VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      duration_sec INT,
      duration_ho_min VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      comments VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC,
      date_posted DATE FORMAT 'mm/dd/yyyy',
      latitude DECIMAL(9,6),
      longitude DECIMAL(12,9)
      );

 

4 REPLIES
Teradata Employee

Re: Creating Table for Dataset

It's difficult to be certain without more information as to the problem.

 

One thing I see in your sample is dates like 4/27/2004. If you are using "CAST" (explicitly or implicitly) to convert a character string to a date, Teradata always expects the month and day of month (and hour, in the case of a timestamp) to be two digits long - with leading zeros if needed. In other words, 04/27/2004 would match the format mm/dd/yyyy. There are a variety of ways to work around this issue, depending on the method you are using to load the data. You can search this site for some ideas.

Senior Apprentice

Re: Creating Table for Dataset

Hi,

It might also help if you said what problem you are having.

 

Is there an error code and/or message?

Is the problem with the create table itself (unlikely) or with inserting/loading data?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Creating Table for Dataset

Hi Dave,

 

Thanks for the reponse... Actually what i need is to import this dataset using BTEQ. I have creaetd the Table(DDL is given in the post).... but as you can see my Time formate is mm/dd/yyyyBmi but in Teradata we also have to have seconds in the time stamp like : mm/dd/yyyyBmi:ss.   So i am not sure how i can import it in the table 

 

I have been searching for the solution and i found that i need to do Casting or to use TO_CHAR function but not sure where i need to use this funtion...

 

currently this is the bteq script i have written:

.SET SESSIONS 8
.LOGON tdt5B/sysdba,learning1510;
.IMPORT VARTEXT ',' FILE = "/home/Assignment_work/complete.csv"
.QUIET ON
.REPEAT *
USING 
		datetime			TIMESTAMP FORMAT('mm/dd/yyyyBhh:mi'),
		city				(CHAR(25)),
		state				(CHAR(25)),
		Country				(CHAR(25)),
		shape				(CHAR(25)),
		duration_sec		(CHAR(25)),
		duration_ho_min		(CHAR(25)),
		comments			(VARCHAR(500)),
		date_posted			(DATE, FORMAT 'mm/dd/yyyy'),
		latitude			(DECIMAL(10, 8)),
		longitude			(DECIMAL(11, 8))

INSERT INTO AP.ufo_report

    VALUES 

     (:datetime,
	 
      :city,
	  
      :state,
	  
      :Country,
	  
	  :shape,
	  
	  :duration_sec,
	  
	  :duration_ho_min,
	  
      :comments,
	  
      :date_posted,
	  
	  :latitude,
	  
      :longitude
	  
     );
.LOGOFF;

Senior Apprentice

Re: Creating Table for Dataset

Hi,

 

One thing that you will have to change is your USING clause.

 

You have "import vartext" (which I think is sensible), but when using that all fields in the USING clause must be defined as VARCHAR(nn). Please refer to the BTEQ manual (https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/Query_Management_Tools/B035_2414_035...).

 

You can make some simple changes to the data on the INSERT command but you'll probably end up having to load into a temp table, then INSERT/SELECT from that into your final table.

 

In the INSERT that you've coded so far, you could concatenate a zero value for seconds to each timestamp. So something like:

:datetime||'.00'

That will give you the full timestamp that TD is expecting.

 

Try that and have a look at the manual and see how far you get.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com