get the current date and set it to variable in ord

Question

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?

Answer 1

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);"