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

[#1]

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

[#2]

Hi @Rak ,

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

Best,

Helmi KHALIFA

[#3]

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

[#4]

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

Accept as Solution

[#5]

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

[#6]

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

[#7]

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