0

I have a hive table, where I want to replace the hyphen ('-') with underscore ('_'). The sample query is as: 

CREATE TABLE test_${yearAndMonth} ......
INSERT OVERWRITE TABLE test_${yearAndMonth} ......

The 'yearAndMonth' contains value like: 2017-05; So, I want to have the table value name as test_2017_05; however, the 'yearAndMonth' will must contain the hyphen value.

I have tried with: regex replace For example: 

CREATE TABLE test_${regexp_replace(yearAndMonth, '-', '_')} ......
INSERT OVERWRITE TABLE test_${regexp_replace(yearAndMonth, '-', '_')} ......

However, I am getting error as:
cannot recognize input near 'test_' '$' '{' in table name

Any suggestions please.

Update: Trying in this was way:

CREATE TABLE test_regexp_replace(${yearAndMonth}, "-", "_") ......
INSERT OVERWRITE TABLE test_regexp_replace(${yearAndMonth}, "-", "_") ......

I am getting this error: missing EOF at '(' near 'test_regexp_replace'

Tanvir
  • 174
  • 1
  • 2
  • 17
  • **(1)** You have a misconception of what Hive variables are. Check https://stackoverflow.com/questions/42887401/storing-result-of-query-in-hive-variable/42887453#42887453. **(2)** You have to pass the variables in the right format. – David דודו Markovitz May 26 '17 at 13:57

1 Answers1

0

Changing the variable format in hive is not a good idea, try to change the format before passing. Doing something similar to below will work (added id int as a sample column, you can add your own or pass them from another variable if required)

hive  --hiveconf table_name=table_$(date '+%Y')_$(date '+%m') -e "create table \${hiveconf:table_name}(id int); insert overwrite table \${hiveconf:table_name}"
Prabhat Ratnala
  • 650
  • 5
  • 17