creating a date from an existing one in HiveQL

creating a date from an existing one in HiveQL

hive current date minus 1 month
hive current date minus 1 year
hive string to date
hive timestamp to date
hive date_format
hive sql current date - 1
hive string to timestamp
hive current date minus 1 day

I'm completely new to Hive and I would really appreciate some help.

I have a date column in my table and I would like to keep month and year of this date. What I would do in excel is the following:

datenew= date(year(old_date),month(old_date),1)

my old_date is in YYYY-MM-DD format.


You can use trunc with mm option (for Hive versions 1.2 and later), which will return the first of a month.


If trunc is not supported, use


Date Functions in Hive, Date data types do not exist in Hive. In fact the dates are treated as strings in Hive​. The date Example: DAY('2000-03-01 10:20:30') returns 1 Design/Implement​/Create SCD Type 2 Effective Date Mapping in Informatica. Load Data Statement. Generally, after creating a table in SQL, we can insert data using the Insert statement. But in Hive, we can insert data using the LOAD DATA statement. While inserting data into Hive, it is better to use LOAD DATA to store bulk records. There are two ways to load data: one is from local file system and second is from Hadoop file system.

You can use TRUNC (date,fmt) which is a function returns date with the time portion of the day truncated to the unit specified by the format model fmt. it always return a value of datatype DATE,:


hive> select trunc(current_date, 'MM');

hive> select trunc(current_date, 'YEAR');

How to perform a date range query in Hive on a tim, How to perform a date range query in Hive on a timestamp column I see one example: ts between '2017-03-01 00:00:03.00' and '2017-03-01 00:00:10.0' - is If you have define it as string/varchar then you can use any format you want and then All existing datetime UDFs (month, day, year, hour, etc.)  The Hive Query Language (HiveQL) is a query language for Hive to process and analyze structured data in a Metastore. This chapter explains how to use the SELECT statement with WHERE clause. SELECT statement is used to retrieve the data from a table. WHERE clause works similar to a condition.

Use date_format or trunc function, or substr()+concat().


hive> select current_date original_date,
    >               date_format(current_date,'yyyy-MM-01') `date_format`,
    >               trunc(current_date, 'MM')              `trunc`,
    >               concat(substr(current_date,1,7),'-01') `substr`
    >               ;
original_date   date_format     trunc           substr
2018-05-30      2018-05-01      2018-05-01      2018-05-01
Time taken: 0.093 seconds, Fetched: 1 row(s)

Hive Date Functions, Hive Date Functions. 05:43:37. Time taken: 0.153 seconds, Fetched: 1 row(s) create table B (dt string); Or link to existing content. Search. UK Data Service – HiveQL example queries 2.3. Creating frequency tables Despite the title, these tables don’t actually create “ tables” in Hive, they simply show the numbers in each category of a categorical variable in the results . If you want to store the results in a table for future use, see

4. HiveQL: Data Definition - Programming Hive [Book], This chapter and the ones that follow discuss the features of HiveQL using '​date' = '2012-01-02' ); hive > DESCRIBE DATABASE financials ; financials hdfs and the existing table has a different schema than the schema in the CREATE  Being able to select data from one table to another is one of the most powerful features of Hive. Hive handles the conversion of the data from the source format to the destination format as the query is being executed. Create Table Like. The LIKE form of CREATE TABLE allows you to copy an existing table definition exactly (without copying its

Insert date into hive tables shows null during select – BigData, total 584. -rw-r–r– 1 saurkuma saurkuma 591414 Mar 16 02:31 SalesData01.csv hive> create table salesdata01 (Row_ID INT, Order_ID INT,  CREATE TABLE table-name. Specifies the name for the Hive table that is to be created. The EXTERNAL parameter indicates that the table that is being created must point to a distributed file system that contains the data files. If the table name exists, this statement fails.

String to Date conversion in hive, When we create a hive table on top of these data, it becomes necessary to convert them into date format which is supported by hive. This chapter starts with the so-called data definition language parts of HiveQL, which are used for creating, altering, and dropping databases, tables, views, functions, and indexes. We’ll discuss databases and tables in this chapter, deferring the discussion of views until Chapter 7, indexes until Chapter 8, and functions until Chapter 13.