Create external table with format serde and a extra column - HIVE

I'm trying import a txt file to external table whith a extra column in hive, like this:

 CREATE EXTERNAL TABLE IF NOT EXISTS bs.tbl_bt( tp_registro string, seq string, num_a string, dt_chamada string, hr_chamada string, num_b string, pt_interconect string, dur_rel_chamada string, dur_tar_chamada string, tp_servico string, vl_liq_chamada string, vl_brt_chamada string, reserva string, '${hiveconf:tez.task.operadora}' as operadora string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES (input.regex = (.{1})(.{10})(.{21})(.{8})(.{6})(.{20})(.{10})(.{7})(.{7})(.{2})(.{11})(.{11})(.{29}).*) STORED AS TEXTFILE LOCATION '/gr/Fi/B/${hiveconf:tez.task.operadora}'; 

What am i doing wrong?

1 Answer

The Hive DDL does not include AS when defining a column

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later) [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])] 

So, try just

'${hiveconf:tez.task.operadora}' STRING 

Or if you really need the column to always be operadora, then remove the hiveconf variable

Maybe you're mixing up a CTAS statement where you can select a column as another name when creating a table