Thanks. Tried that, it inserts a new row with value as null rather than taking default value of default_dep_name as specified in avro schema.
Error is Avro could not validate record against schema. On Sep 24, 2017 2:09 AM, "Jörn Franke" <[email protected]> wrote: insert into dep_av values(8,null) should do what you intent. On 24. Sep 2017, at 03:03, BD <[email protected]> wrote: Hi , I have imported (using sqoop) departments table from retail_db in hdfs as avro file. Have created an external table stored as hive and used the avro schema generated by sqoop. I want to modify the avro schema so that a column is non nullable and if not specified in insert query then a default value is inserted into the table. Have tried modifying the avro schema as following, but it does not help. Avro Schema { "type" : "record", "name" : "departments", "doc" : "Sqoop import of departments", "fields" : [ { "name" : "department_id", "type" : "int" , "columnName" : "department_id", "sqlType" : "4" }, { "name" : "department_name", "type" : "string", "default" : "default_dep_name" , "columnName" : "department_name", "sqlType" : "12" } ], "tableName" : "departments" } If i do not provide the value for department name then hive gives error stating that two columns expected. Is this a valid use case? if so any suggestion? hive> insert into dep_av values(8); FAILED: SemanticException [Error 10044]: Line 1:12 Cannot insert into target table because column number/types are different 'dep_av': Table insclause-0 has 2 columns, but query has 1 columns. regards
