get the current date and set it to variable in order to use it as table name in HIVE

user_6087989 0
Viewed 2433

I want to get the current date as YYMMDD and then set it to variable in order to use it as table name.

Here is my code:

set dates= date +%Y-%m-%d;
CREATE EXTERNAL TABLE IF NOT EXISTS dates(
    id STRING,
    region STRING,
    city STRING)

But this method doesn't work, because it seems the assignments are wrong. Any idea?

hiveql hive hadoop variables
1 Answers

Hive does not calculate variables, it substitutes them as is, in your case it will be exactly this string 'date +%Y-%m-%d'. Also it is not possible to use UDF like current_date() in place of table name in DDL.

The solution is to calculate variable in the shell and pass it to Hive:

In the shell

dates=$(date +%Y_%m_%d);

hive --hivevar  date="$dates" -f myscript.hql

In the script:

use mydb; create table if not exists tab_${hivevar:date} (id int);

Or you can execute hive script from command line using hive -e, in this case variable can be substituted using shell:

dates=$(date +%Y_%m_%d);

hive -e "use mydb; create table if not exists tab_${dates} (id int);"
user_27003440
Related