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.

Highlighted
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