Re: Hive - Avro - Schema Manipulation

[BD]

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"  wrote:

insert into dep_av values(8,null) should do what you intent.

On 24. Sep 2017, at 03:03, BD  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

Re: Hive - Avro - Schema Manipulation

[Jörn Franke]

insert into dep_av values(8,null) should do what you intent.

> On 24. Sep 2017, at 03:03, BD  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

Hive - Avro - Schema Manipulation

[BD]

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