Hive Metastore hook for single insert into hive there is an two event occuring.

[Dayanand Lature]

Hi Team,

When i was tried to insert into hive table as a single record by using insert into statement that was made two entries in audit_log table.

And for  audit_objects table there is an four entries in mysql for single insert.

below are the  entries in mysql table. 

 select * from audit_log;

+----+---------------------+----------+--------------------+------------+-----------------------------+-----------------------------+----------+---------------+--------+

| id | create_time         | query_id | command_type       | command    | inputs                      | outputs                     | username | ip            | extras |

+----+---------------------+----------+--------------------+------------+-----------------------------+-----------------------------+----------+---------------+--------+

|  1 | 2019-04-30 09:40:56 | NULL     | THRIFT_ALTER_TABLE | THRIFT_API | {"tables":["db_stage.emp"]} | {"tables":["db_stage.emp"]} | NULL     | 170.0.0.0 | NULL   |

|  2 | 2019-04-30 09:40:57 | NULL     | THRIFT_ALTER_TABLE | THRIFT_API | {"tables":["db_stage.emp"]} | {"tables":["db_stage.emp"]} | NULL     | 170.0.0.0 | NULL   |

|  3 | 2019-04-30 11:36:08 | NULL     | THRIFT_ALTER_TABLE | THRIFT_API | {"tables":["db_stage.emp"]} | {"tables":["db_stage.emp"]} | NULL     | 170.0.0.0 | NULL   |

|  4 | 2019-04-30 11:36:09 | NULL     | THRIFT_ALTER_TABLE | THRIFT_API | {"tables":["db_stage.emp"]} | {"tables":["db_stage.emp"]} | NULL     | 170.0.0.0 | NULL   |

+----+---------------------+----------+--------------------+------------+-----------------------------+-----------------------------+----------+---------------+--------+

 select * from audit_objects;

|  1 | 2019-04-30 09:40:56 |            1 | INPUT    | TABLE | db_stage.emp | {"1":{"str":"emp"},"2":{"str":"db_stage"},"3":{"str":"abc"},"4":{"i32":1556608605},"5":{"i32":0},"6":{"i32":0},"7":{"rec":{"1":{"lst":["rec",7,{"1":{"str":"id"},"2":{"str":"int"}},{"1":{"str":"name"},"2":{"str":"string"}},{"1":{"str":"address"},"2":{"str":"string"}},{"1":{"str":"city"},"2":{"str":"string"}},{"1":{"str":"state"},"2":{"str":"string"}},{"1":{"str":"pincode"},"2":{"str":"string"}},{"1":{"str":"contact"},"2":{"str":"string"}}]},"2":{"str":"gs://abc/test/mani/db_stage/emp"},"3":{"str":"org.apache.hadoop.mapred.TextInputFormat"},"4":{"str":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"},"5":{"tf":0},"6":{"i32":-1},"7":{"rec":{"2":{"str":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"},"3":{"map":["str","str",3,{"serialization.format":",","line.delim":"\n","field.delim":","}]}}},"8":{"lst":["str",0]},"9":{"lst":["rec",0]},"10":{"map":["str","str",0,{}]},"11":{"rec":{"1":{"lst":["str",0]},"2":{"lst":["lst",0]},"3":{"map":["lst","str",0,{}]}}},"12":{"tf":0}}},"8":{"lst":["rec",0]},"9":{"map":["str","str",6,{"totalSize":"0","EXTERNAL":"TRUE","numRows":"7","rawDataSize":"287","numFiles":"0","transient_lastDdlTime":"1556608605"}]},"12":{"str":"EXTERNAL_TABLE"},"15":{"tf":0}} |

|  2 | 2019-04-30 09:40:56 |            1 | OUTPUT   | TABLE | db_stage.emp | {"1":{"str":"emp"},"2":{"str":"db_stage"},"3":{"str":"abc"},"4":{"i32":1556608605},"5":{"i32":0},"6":{"i32":0},"7":{"rec":{"1":{"lst":["rec",7,{"1":{"str":"id"},"2":{"str":"int"}},{"1":{"str":"name"},"2":{"str":"string"}},{"1":{"str":"address"},"2":{"str":"string"}},{"1":{"str":"city"},"2":{"str":"string"}},{"1":{"str":"state"},"2":{"str":"string"}},{"1":{"str":"pincode"},"2":{"str":"string"}},{"1":{"str":"contact"},"2":{"str":"string"}}]},"2":{"str":"gs://abc/test/mani/db_stage/emp"},"3":{"str":"org.apache.hadoop.mapred.TextInputFormat"},"4":{"str":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"},"5":{"tf":0},"6":{"i32":-1},"7":{"rec":{"2":{"str":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"},"3":{"map":["str","str",3,{"serialization.format":",","line.delim":"\n","field.delim":","}]}}},"8":{"lst":["str",0]},"9":{"lst":["rec",0]},"10":{"map":["str","str",0,{}]},"11":{"rec":{"1":{"lst":["str",0]},"2":{"lst":["lst",0]},"3":{"map":["lst","str",0,{}]}}},"12":{"tf":0}}},"8":{"lst":["rec",0]},"9":{"map":["str","str",7,{"totalSize":"0","EXTERNAL":"TRUE","numRows":"7","rawDataSize":"287","COLUMN_STATS_ACCURATE":"{}","numFiles":"0","transient_lastDdlTime":"1556617255"}]},"12":{"str":"EXTERNAL_TABLE"},"15":{"tf":0}} |

|  3 | 2019-04-30 09:40:57 |            2 | INPUT    | TABLE | db_stage.emp | {"1":{"str":"emp"},"2":{"str":"db_stage"},"3":{"str":"abc"},"4":{"i32":1556608605},"5":{"i32":0},"6":{"i32":0},"7":{"rec":{"1":{"lst":["rec",7,{"1":{"str":"id"},"2":{"str":"int"}},{"1":{"str":"name"},"2":{"str":"string"}},{"1":{"str":"address"},"2":{"str":"string"}},{"1":{"str":"city"},"2":{"str":"string"}},{"1":{"str":"state"},"2":{"str":"string"}},{"1":{"str":"pincode"},"2":{"str":"string"}},{"1":{"str":"contact"},"2":{"str":"string"}}]},"2":{"str":"gs://abc/test/mani/db_stage/emp"},"3":{"str":"org.apache.hadoop.mapred.TextInputFormat"},"4":{"str":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"},"5":{"tf":0},"6":{"i32":-1},"7":{"rec":{"2":{"str":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"},"3":{"map":["str","str",3,{"serialization.format":",","line.delim":"\n","field.delim":","}]}}},"8":{"lst":["str",0]},"9":{"lst":["rec",0]},"10":{"map":["str","str",0,{}]},"11":{"rec":{"1":{"lst":["str",0]},"2":{"lst":["lst",0]},"3":{"map":["lst","str",0,{}]}}},"12":{"tf":0}}},"8":{"lst":["rec",0]},"9":{"map":["str","str",7,{"totalSize":"0","EXTERNAL":"TRUE","numRows":"7","rawDataSize":"287","COLUMN_STATS_ACCURATE":"{}","numFiles":"0","transient_lastDdlTime":"1556617255"}]},"12":{"str":"EXTERNAL_TABLE"},"15":{"tf":0}} |

|  4 | 2019-04-30 09:40:57 |            2 | OUTPUT   | TABLE | db_stage.emp | {"1":{"str":"emp"},"2":{"str":"db_stage"},"3":{"str":"abc"},"4":{"i32":1556608605},"5":{"i32":0},"6":{"i32":0},"7":{"rec":{"1":{"lst":["rec",7,{"1":{"str":"id"},"2":{"str":"int"}},{"1":{"str":"name"},"2":{"str":"string"}},{"1":{"str":"address"},"2":{"str":"string"}},{"1":{"str":"city"},"2":{"str":"string"}},{"1":{"str":"state"},"2":{"str":"string"}},{"1":{"str":"pincode"},"2":{"str":"string"}},{"1":{"str":"contact"},"2":{"str":"string"}}]},"2":{"str":"gs://abc/test/mani/db_stage/emp"},"3":{"str":"org.apache.hadoop.mapred.TextInputFormat"},"4":{"str":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"},"5":{"tf":0},"6":{"i32":-1},"7":{"rec":{"2":{"str":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"},"3":{"map":["str","str",3,{"serialization.format":",","line.delim":"\n","field.delim":","}]}}},"8":{"lst":["str",0]},"9":{"lst":["rec",0]},"10":{"map":["str","str",0,{}]},"11":{"rec":{"1":{"lst":["str",0]},"2":{"lst":["lst",0]},"3":{"map":["lst","str",0,{}]}}},"12":{"tf":0}}},"8":{"lst":["rec",0]},"9":{"map":["str","str",6,{"totalSize":"16759807","EXTERNAL":"TRUE","numRows":"7","rawDataSize":"287","numFiles":"14","transient_lastDdlTime":"1556617256"}]},"12":{"str":"EXTERNAL_TABLE"},"15":{"tf":0}} |

|  5 | 2019-04-30 11:36:08 |            3 | INPUT    | TABLE | db_stage.emp | {"1":{"str":"emp"},"2":{"str":"db_stage"},"3":{"str":"abc"},"4":{"i32":1556608605},"5":{"i32":0},"6":{"i32":0},"7":{"rec":{"1":{"lst":["rec",7,{"1":{"str":"id"},"2":{"str":"int"}},{"1":{"str":"name"},"2":{"str":"string"}},{"1":{"str":"address"},"2":{"str":"string"}},{"1":{"str":"city"},"2":{"str":"string"}},{"1":{"str":"state"},"2":{"str":"string"}},{"1":{"str":"pincode"},"2":{"str":"string"}},{"1":{"str":"contact"},"2":{"str":"string"}}]},"2":{"str":"gs://abc/test/mani/db_stage/emp"},"3":{"str":"org.apache.hadoop.mapred.TextInputFormat"},"4":{"str":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"},"5":{"tf":0},"6":{"i32":-1},"7":{"rec":{"2":{"str":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"},"3":{"map":["str","str",3,{"serialization.format":",","line.delim":"\n","field.delim":","}]}}},"8":{"lst":["str",0]},"9":{"lst":["rec",0]},"10":{"map":["str","str",0,{}]},"11":{"rec":{"1":{"lst":["str",0]},"2":{"lst":["lst",0]},"3":{"map":["lst","str",0,{}]}}},"12":{"tf":0}}},"8":{"lst":["rec",0]},"9":{"map":["str","str",6,{"totalSize":"16759807","EXTERNAL":"TRUE","numRows":"7","rawDataSize":"287","numFiles":"14","transient_lastDdlTime":"1556617256"}]},"12":{"str":"EXTERNAL_TABLE"},"15":{"tf":0}} |

|  6 | 2019-04-30 11:36:08 |            3 | OUTPUT   | TABLE | db_stage.emp | {"1":{"str":"emp"},"2":{"str":"db_stage"},"3":{"str":"abc"},"4":{"i32":1556608605},"5":{"i32":0},"6":{"i32":0},"7":{"rec":{"1":{"lst":["rec",7,{"1":{"str":"id"},"2":{"str":"int"}},{"1":{"str":"name"},"2":{"str":"string"}},{"1":{"str":"address"},"2":{"str":"string"}},{"1":{"str":"city"},"2":{"str":"string"}},{"1":{"str":"state"},"2":{"str":"string"}},{"1":{"str":"pincode"},"2":{"str":"string"}},{"1":{"str":"contact"},"2":{"str":"string"}}]},"2":{"str":"gs://abc/test/mani/db_stage/emp"},"3":{"str":"org.apache.hadoop.mapred.TextInputFormat"},"4":{"str":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"},"5":{"tf":0},"6":{"i32":-1},"7":{"rec":{"2":{"str":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"},"3":{"map":["str","str",3,{"serialization.format":",","line.delim":"\n","field.delim":","}]}}},"8":{"lst":["str",0]},"9":{"lst":["rec",0]},"10":{"map":["str","str",0,{}]},"11":{"rec":{"1":{"lst":["str",0]},"2":{"lst":["lst",0]},"3":{"map":["lst","str",0,{}]}}},"12":{"tf":0}}},"8":{"lst":["rec",0]},"9":{"map":["str","str",7,{"totalSize":"16759807","EXTERNAL":"TRUE","numRows":"7","rawDataSize":"287","COLUMN_STATS_ACCURATE":"{}","numFiles":"14","transient_lastDdlTime":"1556624167"}]},"12":{"str":"EXTERNAL_TABLE"},"15":{"tf":0}} |

|  7 | 2019-04-30 11:36:09 |            4 | INPUT    | TABLE | db_stage.emp | {"1":{"str":"emp"},"2":{"str":"db_stage"},"3":{"str":"abc"},"4":{"i32":1556608605},"5":{"i32":0},"6":{"i32":0},"7":{"rec":{"1":{"lst":["rec",7,{"1":{"str":"id"},"2":{"str":"int"}},{"1":{"str":"name"},"2":{"str":"string"}},{"1":{"str":"address"},"2":{"str":"string"}},{"1":{"str":"city"},"2":{"str":"string"}},{"1":{"str":"state"},"2":{"str":"string"}},{"1":{"str":"pincode"},"2":{"str":"string"}},{"1":{"str":"contact"},"2":{"str":"string"}}]},"2":{"str":"gs://abc/test/mani/db_stage/emp"},"3":{"str":"org.apache.hadoop.mapred.TextInputFormat"},"4":{"str":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"},"5":{"tf":0},"6":{"i32":-1},"7":{"rec":{"2":{"str":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"},"3":{"map":["str","str",3,{"serialization.format":",","line.delim":"\n","field.delim":","}]}}},"8":{"lst":["str",0]},"9":{"lst":["rec",0]},"10":{"map":["str","str",0,{}]},"11":{"rec":{"1":{"lst":["str",0]},"2":{"lst":["lst",0]},"3":{"map":["lst","str",0,{}]}}},"12":{"tf":0}}},"8":{"lst":["rec",0]},"9":{"map":["str","str",7,{"totalSize":"16759807","EXTERNAL":"TRUE","numRows":"7","rawDataSize":"287","COLUMN_STATS_ACCURATE":"{}","numFiles":"14","transient_lastDdlTime":"1556624167"}]},"12":{"str":"EXTERNAL_TABLE"},"15":{"tf":0}} |

|  8 | 2019-04-30 11:36:09 |            4 | OUTPUT   | TABLE | db_stage.emp | {"1":{"str":"emp"},"2":{"str":"db_stage"},"3":{"str":"abc"},"4":{"i32":1556608605},"5":{"i32":0},"6":{"i32":0},"7":{"rec":{"1":{"lst":["rec",7,{"1":{"str":"id"},"2":{"str":"int"}},{"1":{"str":"name"},"2":{"str":"string"}},{"1":{"str":"address"},"2":{"str":"string"}},{"1":{"str":"city"},"2":{"str":"string"}},{"1":{"str":"state"},"2":{"str":"string"}},{"1":{"str":"pincode"},"2":{"str":"string"}},{"1":{"str":"contact"},"2":{"str":"string"}}]},"2":{"str":"gs://abc/test/mani/db_stage/emp"},"3":{"str":"org.apache.hadoop.mapred.TextInputFormat"},"4":{"str":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"},"5":{"tf":0},"6":{"i32":-1},"7":{"rec":{"2":{"str":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"},"3":{"map":["str","str",3,{"serialization.format":",","line.delim":"\n","field.delim":","}]}}},"8":{"lst":["str",0]},"9":{"lst":["rec",0]},"10":{"map":["str","str",0,{}]},"11":{"rec":{"1":{"lst":["str",0]},"2":{"lst":["lst",0]},"3":{"map":["lst","str",0,{}]}}},"12":{"tf":0}}},"8":{"lst":["rec",0]},"9":{"map":["str","str",6,{"totalSize":"16759849","EXTERNAL":"TRUE","numRows":"7","rawDataSize":"287","numFiles":"15","transient_lastDdlTime":"1556624168"}]},"12":{"str":"EXTERNAL_TABLE"},"15":{"tf":0}} |

Could you please let me know where we need to make changes?

Thanks & Regards,

Dayanand Lature.