2

Suppose I am having a source Avro table having 10 columns and my target Avro table is having 12 columns, while inserting data into the target table I need to add null values to the extra 2 columns.

But when I execute the below query it has thrown the exception

AnalysisException: Target table 'target_table' has more columns (8) than the SELECT / VALUES clause returns (7)

insert overwrite table target_table select * from source_table;

How to make advantage of Avro table automatic schema change detection here?

Note: Suppose if I want to insert only 5 columns to the target and the rest should be default null. So how to achieve this?

Hedayatullah Sarwary
  • 161
  • 1
  • 1
  • 8
user109612
  • 21
  • 2

1 Answers1

1

You need to specify which columns you want to insert into in the insert statement. In the statement you wrote, the engine doesn't know which 7 columns out of the 8 to put the data in. http://www.cloudera.com/documentation/enterprise/5-5-x/topics/impala_insert.html

Insert overwrite table target_table (col1, col2, col3, ...) 
Select col1, col2, col3 ... From source_table;

NULL will be inserted into columns not mentioned in the column list.

mendosi
  • 2,097
  • 12
  • 22