insert into dep_av values(8,null) should do what you intent.
> On 24. Sep 2017, at 03:03, BD <bigdat...@gmail.com> 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
