CODEWITHSUNDEEP
hivehql
BlackMamba
BlackMamba

Reputation: 10254

In hive, how to generate dynamic table name in hql?

i want generate dynamic table name in hql which runing using beeline.

in db2, i can implement this requirement using ||. for example, using year to generate table name 'as400.trxfintrx_' || year(current date), but how can i implement this in hive'hql?

Upvotes: 1

Views: 2266

Answers (1)

Vijiy
Vijiy

Reputation: 1197

If i understand it correct, you want the table name to be paramterized,

For that you can use hive variables,

create table dbName.table1_${hivevar:yearMonthDate}
(
c1 int,
c2 int
)
stored as orc
tblproperties('ZLIB');

$ hive -f test_create_table.hql --hivevar yearMonthDate=20190215
OK
Time taken: 1.149 seconds
$ hive
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> use dbname;
OK
Time taken: 0.726 seconds
hive> desc table1_20190215;
OK
c1                      int
c2                      int
Time taken: 0.302 seconds, Fetched: 2 row(s)

you can refer https://cwiki.apache.org/confluence/display/Hive/LanguageManual+VariableSubstitution

From beeline terminal, you cannot define any function to set parameter value and then use them in you queries.

Hope this helps

Upvotes: 1

Related Questions