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:
|10/10/1949 20:30||san marcos||tx||us||cylinder||2700||45 minutes||This event took place in early fall around 1949-50. It occurred after a Boy Scout meeting in the Baptist Church. The Baptist Church sit||4/27/2004||29.88306||-97.9411|
|10/10/1949 21:00||lackland afb||tx||light||7200||1-2 hrs||1949 Lackland AFB, TX. Lights racing across the sky & making 90 degree turns on a dime.||12/16/2005||29.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) );
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.
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?
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;
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:
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.