Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

csv imported from local its not taking date values as timestamp and it displays null

csv imported from local its not taking date values as timestamp and it displays null

Explorer

Hello Sir,

 

 

when i import Csv from local its not taking date values as timestamp and it displays null in hive

actually when i use `date` timestamp the values show null and

 when i use `date` string it shows but i need to merge with another table where `date` timestamp

so merge isn't happening

5 REPLIES 5

Re: csv imported from local its not taking date values as timestamp and it displays null

Contributor

Hi @Rak ,

 

Can you show us some sample the 5 first rows of your csv file, please ?

 

Best,

Helmi KHALIFA

Re: csv imported from local its not taking date values as timestamp and it displays null

Explorer

sc_code string
date string
co_code double
high double
low double
open double
close double
volume double
no_trades double
net_turnov double
dmcap double
return double
factor double
ttmpe double
yepe double
flag string

 

5004322 10/25/2019 3.0 1526.75 1501.05 1520.05 1517.95 51681.0 1368.0 7.8151398E7 28505.17 -0.052674897 1.0 23.76 18.93 A
5000022 10/25/2019 5.0 174.0 162.15 172.0 164.55 7661.0 1264.0 1275450.0 5589.22 -3.376394598 1.0 33.49 63.07 A
5008722 10/25/2019 73.0 184.2 175.2 182.3 181.2 114897.0 731.0 2.0801958E7 10365.55 -0.275178866 1.0 17.29 14.04 A
5004755 10/25/2019 3.0 76.9 73.15 75.9 74.65 2052480.0 3453.0 1.52315554E8 21913.71 -1.582069875 1.0 21.89 11.02 A

Re: csv imported from local its not taking date values as timestamp and it displays null

Explorer


SC_CODE DATE CO_CODE HIGH LOW OPEN CLOSE VOLUME NO_TRADES NET_TURNOV DMCAP Return Factor TTMPE YEPE FLAG

5004322 10/25/2019 3.0 1526.75 1501.05 1520.05 1517.95 51681.0 1368.0 7.8151398E7 28505.17 -0.052674897 1.0 23.76 18.93 A
5000022 10/25/2019 5.0 174.0 162.15 172.0 164.55 7661.0 1264.0 1275450.0 5589.22 -3.376394598 1.0 33.49 63.07 A
5008722 10/25/2019 73.0 184.2 175.2 182.3 181.2 114897.0 731.0 2.0801958E7 10365.55 -0.275178866 1.0 17.29 14.04 A
5004755 10/25/2019 3.0 76.9 73.15 75.9 74.65 2052480.0 3453.0 1.52315554E8 21913.71 -1.582069875 1.0 21.89 11.02 A

and thedatatypesare mentioned down

 

Re: csv imported from local its not taking date values as timestamp and it displays null

Contributor

Hi @Ra 

 

You have to change the name and column type as youscan see in red below :

sc_code string
ddate date
co_code double
high double
low double
open double
close double
volume double
no_trades double
net_turnov double
dmcap double
return double
factor double
ttmpe double
yepe double
flag string

 

I tried it and it works well for me.

 

Best,

Helmi KHALIFA

Re: csv imported from local its not taking date values as timestamp and it displays null

Contributor

Hi @Rak ;

 

here the script :

CREATE EXTERNAL TABLE IF NOT EXISTS sample_date (sc_code string, ddate timestamp, co_code DECIMAL, high DECIMAL, low DECIMAL, open DECIMAL, close DECIMAL, volume DECIMAL, no_trades DECIMAL, net_turnov DECIMAL, dmcap DECIMAL, return DECIMAL, factor DECIMAL, ttmpe DECIMAL, yepe DECIMAL, flag string)
ROW FORMAT
DELIMITED FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/lab/itim/ccbd/helmi/sampleDate'
tblproperties('skip.header.line.count'='1');


ALTER TABLE sample_date
SET SERDEPROPERTIES ("timestamp.formats"="MM/DD/YYYY");

 

Could you accept the answer please ?

 

Best,

Helmi KHALIFA

Don't have an account?
Coming from Hortonworks? Activate your account here