Member since
10-14-2019
17
Posts
0
Kudos Received
0
Solutions
01-20-2020
03:55 AM
Dear Sir, i want to enter python calculated values which are saved in df4 should get stored a column of ''per" per is name of column in hive table . df2 = pd.read_sql("SELECT * FROM ggg where clientcode='55'", conn) this is how i am reading the data how to write df4 python calculated values in "per" column of hive table.
... View more
11-11-2019
12:22 PM
@Rak You have a couple of errors in your sqoop syntax but you are almost there, please have a look at the below hints and retry after understanding and correcting them. 1. Sqoop import--connect This is wrong you need a space between the import and -- ie sqoop import --connect 2. 'jdbc:sqlserver'--username is also not correct you need a port number and databases name ie "jdbc:sqlserver://<Server_Host>:<Server_Port>;databaseName" 3. The quotes around the '2019-11-08'" is wrong too 3. All you -- should have a space before sqoop import--connect 'jdbc:sqlserver'--username 'sa' -P--query "select * from dlyprice where $CONDITIONS AND `date`= '2019-11-08'"--split-by `date--target-dir /home/hduser2 -m 2 Try something like this ! sqoop import --connect --connect "jdbc:sqlserver://<Server_Host>:<Server_Port>;DB_Name>" \ --driver com.microsoft.sqlserver.jdbc.SQLServerDriver \ --username XXXX -P --query "select * from dlyprice where $CONDITIONS AND `date`= '2019-11-08' \ --split-by `date` --target-dir /home/hduser2 -m 2 The above isn't tested by I wanted to highlight some of your mistakes by making it work makes you a better hadooper ! Here is a link to Sqoop User Guide Can you try out this syntax remember to replace the values with that of your environment sqoop import --connect "jdbc:sqlserver://hostname;username='sa';password='sa_password';database=yourDB" --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --query "select * from dlyprice where \$CONDITIONS" --split-by date -m 2 --target-dir /home/hduser2 Please revert
... View more
11-05-2019
01:21 AM
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
... View more
11-01-2019
08:35 AM
1 Kudo
@Rak ,
I'm suspecting you are using Impala, because of the statement of the values returning null, but please confirm so I can properly label this question so others can see it. Please see the following in the Impala documentation regarding the proper format for casting a string into a timestamp datatype:
https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_timestamp.html
Restrictions:
If you cast a STRING with an unrecognized format to a TIMESTAMP , the result is NULL rather than an error. Make sure to test your data pipeline to be sure any textual date and time values are in a format that Impala TIMESTAMP can recognize.
Currently, Avro tables cannot contain TIMESTAMP columns. If you need to store date and time values in Avro tables, as a workaround you can use a STRING representation of the values, convert the values to BIGINT with the UNIX_TIMESTAMP() function, or create separate numeric columns for individual date and time fields using the EXTRACT() function.
Examples:
The following examples demonstrate using TIMESTAMP values with built-in functions:
select cast('1966-07-30' as timestamp);
select cast('1985-09-25 17:45:30.005' as timestamp);
select cast('08:30:00' as timestamp);
select hour('1970-01-01 15:30:00'); -- Succeeds, returns 15.
select hour('1970-01-01 15:30'); -- Returns NULL because seconds field required.
select hour('1970-01-01 27:30:00'); -- Returns NULL because hour value out of range.
select dayofweek('2004-06-13'); -- Returns 1, representing Sunday.
select dayname('2004-06-13'); -- Returns 'Sunday'.
select date_add('2004-06-13', 365); -- Returns 2005-06-13 with zeros for hh:mm:ss fields.
select day('2004-06-13'); -- Returns 13.
select datediff('1989-12-31','1984-09-01'); -- How many days between these 2 dates?
select now(); -- Returns current date and time in local timezone.
... View more