Yes. It is possible to retrieve constraints of a Hive table. You need to query the database where Hive stores it's metastore...We are using MySQL database as Hive metastore and below is the query: SELECT d.NAME as SCHEMA_NAME,t.TBL_NAME as TABLE_NAME, kc.CONSTRAINT_NAME, kc.CONSTRAINT_TYPE, c.COLUMN_NAME, c.TYPE_NAME, '' as REFERENCE_SCHEMA_NAME, '' as REFERENCE_TABLE_NAME, '' as REFERENCE_COL_NAME FROM DBS d, TBLS t, SDS s, COLUMNS_V2 c, CDS cds, KEY_CONSTRAINTS kc WHERE d.DB_ID =t.DB_ID AND t.SD_ID = s.SD_ID AND s.cd_id = c.cd_id AND cds.CD_ID = c.CD_ID AND kc.PARENT_TBL_ID = t.TBL_ID AND kc.PARENT_CD_ID=c.CD_ID AND c.INTEGER_IDX=kc.PARENT_INTEGER_IDX AND CONSTRAINT_TYPE='0' AND t.tbl_name='pk' AND d.NAME='vsatyp_schema' UNION ALL SELECT d.NAME as SCHEMA_NAME,t.TBL_NAME as TABLE_NAME, kc.CONSTRAINT_NAME, kc.CONSTRAINT_TYPE, c.COLUMN_NAME, c.TYPE_NAME, p_d.NAME as REFERENCE_SCHEMA_NAME, p_t.TBL_NAME as REFERENCE_TABLE_NAME, p_c.COLUMN_NAME as REFERENCE_COL_NAME FROM hive_metastore.DBS d, hive_metastore.TBLS t, hive_metastore.SDS s, hive_metastore.COLUMNS_V2 c, hive_metastore.CDS cds, hive_metastore.KEY_CONSTRAINTS kc, hive_metastore.DBS p_d, hive_metastore.TBLS p_t, hive_metastore.SDS p_s, hive_metastore.COLUMNS_V2 p_c, hive_metastore.CDS p_cds WHERE d.DB_ID =t.DB_ID AND t.SD_ID = s.SD_ID AND s.cd_id = c.cd_id AND cds.CD_ID = c.CD_ID AND kc.CHILD_TBL_ID = t.TBL_ID AND kc.CHILD_CD_ID=c.CD_ID AND c.INTEGER_IDX=kc.CHILD_INTEGER_IDX AND p_d.DB_ID =p_t.DB_ID AND p_t.SD_ID = p_s.SD_ID AND p_s.cd_id = p_c.cd_id AND p_cds.CD_ID = p_c.CD_ID AND kc.PARENT_TBL_ID = p_t.TBL_ID AND kc.PARENT_CD_ID=p_c.CD_ID AND p_c.INTEGER_IDX=kc.PARENT_INTEGER_IDX AND CONSTRAINT_TYPE='1' AND d.NAME in ('vsatyp_schema') and t.tbl_name = 'fk' order by SCHEMA_NAME, TABLE_NAME, CONSTRAINT_TYPE Hope this helps... ... View more

Thanks a lot Matt. The last change binlog_format=row was required & missed in my case. Now it is working fine. I could able to see the evets in the flow file. I have one more query, in "column_type" : 4, is value 4 is data type? What are standard data types and are these specific to databases or universally common? Is it linked to java.sql.Types? Let me know. ... View more

Thanks Alim for reply... I could see the entries in the bin log file however no events got generated 😞 Request you to share the configuration for MySQL and NiFi CaptureChangeMySQL processor. Also share the output of events by viewing...I think it would be in Jason format. Thanks in advance. ... View more

Thanks Matt for quick reply. I have tried INSERT, UPDATE, DELETE events but it didn't worked. You are correct as we don't need auditing I will set it to FALSE but just for testing I enabled it so that at least I got some flow files as output 🙂 Also I tried resetting Retrieve All Records and clearing state but even that won't help. I even downloaded the source code of the CaptureChangeMySQL processor and tried to run the unit test cases but unit test class is in Groovy, I was not able to compile and test it. I am not sure why the unit test class in Groovy when everything else is written in Java language. ... View more