public class ManageSqlInJson
extends Object
1. take a json file as input
2. read json file created in CountSql.java with this structure:
{"dbVendor":"dbvathena","statementType":"sstselect","sqlSize":156,"uniqueHashId":"0204e249ff1a6ae3f49db491e9709f13762314e95080e988d3ef99fe6b9c1773","sqlStatement":"WITH\r\ndataset AS (\r\n SELECT ARRAY [1,2,2,3,3,4,5] AS items\r\n)\r\nSELECT array_agg(distinct i) AS array_items\r\nFROM dataset\r\nCROSS JOIN UNNEST(items) AS t(i);"}
3. Found the sql statement in the json file with the same uniqueHashId which means the sql statement is duplicate
4. list the number of duplicate sql statements of under the same uniqueHashId, only list the number of duplicate sql statements that are more than 1
5. list the uniqueHashId,statementType, sqlStatement of the duplicate sql statements in the order of the number of duplicate sql statements from high to low
6. output the result to a csv file named as duplicate_sql_statements.csv
sample output:
Total JSON records processed: 54617
Analysis complete. Results written to:
- Summary CSV: c:\prg\tmp\o.csv
- Detailed CSV with SQL examples: c:\prg\tmp\o_with_sql.csv
Total SQL statements processed: 54617
Unique SQL statement types: 24669
Found 9257 duplicate statement types.
Total duplicate statements: 29948