CODEWITHSUNDEEP
oracle-databasedatehivesqoop
Brad Knoblauch
Brad Knoblauch

Reputation: 13

Handling dates in Hadoop

I'm new to the Big Data/Hadoop ecosystem and have noticed that dates are not always handled in standard way across technologies. I plan to be ingesting data from Oracle into Hive tables on an HDFS using Sqoop with Avro and Parquet file formats. Hive continues to import my dates into BIGINT values, I'd prefer TIMESTAMPS. I've tried using the "--map-column-hive" overrides... but it still does not work.

Looking for suggestions on the best way to handle dates for this use case.

Upvotes: 0

Views: 1524

Answers (1)

Sandeep Singh
Sandeep Singh

Reputation: 7990

Parquet File Format

If you use Sqoop to convert RDBMS data to Parquet, be careful with interpreting any resulting values from DATE, DATETIME, or TIMESTAMP columns. The underlying values are represented as the Parquet INT64 type, which is represented as BIGINT in the Impala table. The Parquet values represent the time in milliseconds, while Impala interprets BIGINT as the time in seconds. Therefore, if you have a BIGINT column in a Parquet table that was imported this way from Sqoop, divide the values by 1000 when interpreting as the TIMESTAMP type.

Avro File Format

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.

You can also use your Hive query like this to get the result in your desired TIMESTAMP format.

FROM_UNIXTIME(CAST(SUBSTR(timestamp_column, 1,10) AS INT)) AS timestamp_column;

Other workaround is to import data using --query in sqoop command, where you can cast your column into timestamp format.

Example

--query 'SELECT CAST (INSERTION_DATE AS TIMESTAMP) FROM tablename WHERE $CONDITIONS'

If your SELECT query gets a bit long, you can use configuration files to shorten the length of the command line call. Here is the reference

Upvotes: 1

Related Questions