4/21/2017

Compare Option in DBVisualizer

I've been working with a tool DBVisualizer.  This tool allows development against a variety of databases.  I happen to be working against AWS Data Lake Hive tables.  After you configure the Connection, you can go to the tables tree view, expand, highlight all objects, right click, script objects, to a file or window.  I selected the "Create" button and ran it.  544 objects to create, runs for a while.  So we have a file containing all table objects.


From there, we connect to another environment.  Assuming I had access to the Production Environment, I would perform same steps, generate a second file.


From there, you can click the Tools dropdown, Compare option, select your 2 new files, and see the differences. 




Now to view the differences, green indicates new:




This is a handy feature when developing, as sometimes the objects do differ between environments, and it's no fun to deploy a report that's been fully validated, only to have it fail in production, with different objects.  Missing Views or Tables, fields renamed or missing.


And there you have it~!

4/20/2017

Hive SQL Date Functions Cheat Sheet


Since I've been working with Hive SQL lately, against AWS Data Lake, assembled a quick list of key Date functions to speed up development:





SELECT
    from_unixtime(unix_timestamp(TO_DATE(CURRENT_DATE) , 'yyyy-MM-dd'), 'MM-dd-yyyy') AS TodaysDate
    ,
    from_unixtime(unix_timestamp(DATE_ADD(CURRENT_DATE,-(DAY(CURRENT_DATE)-1)), 'yyyy-MM-dd'),
    'MM-dd-yyyy') AS FirstDayThisMonth ,
    from_unixtime(unix_timestamp(LAST_DAY(DATE_ADD(CURRENT_DATE,-(DAY(CURRENT_DATE)-1))),
    'yyyy-MM-dd'), 'MM-dd-yyyy') AS LastDayThisMonth,
    from_unixtime(unix_timestamp((DATE_ADD(CURRENT_DATE, -1-DAY (CURRENT_DATE))) , 'yyyy-MM-dd'),
    'MM-01-yyyy') AS FirstDayPriorMonth,
    from_unixtime(unix_timestamp(DATE_ADD(CURRENT_DATE,-(DAY(CURRENT_DATE)+1)) , 'yyyy-MM-dd'),
    'MM-dd-yyyy')                                                              AS LastDayPriorMonth,
    from_unixtime(unix_timestamp(TO_DATE(CURRENT_DATE) , 'yyyy-MM-dd'), '01-01-yyyy') AS
    FirstDayThisYear,
    from_unixtime(unix_timestamp(TO_DATE(CURRENT_DATE) , 'yyyy-MM-dd'), '12-31-yyyy') AS
    LastDayThisYear,
    from_unixtime(unix_timestamp(date_sub(concat(from_unixtime(unix_timestamp(), YEAR(CURRENT_DATE)
    -1), '-01-01'), 0), 'yyyy-MM-dd'), 'MM-dd-yyyy') AS FirstDayPriorYear,
    from_unixtime(unix_timestamp(date_sub(concat(from_unixtime(unix_timestamp(), YEAR(CURRENT_DATE)
    -1), '-12-31'), 0), 'yyyy-MM-dd'), 'MM-dd-yyyy') AS LastDayPriorYear,
    DATE_ADD(CURRENT_DATE, -90)                         TodayMinus90Days,
    from_unixtime(unix_timestamp((DATE_ADD(CURRENT_DATE, -1-DAY(CURRENT_DATE))) , 'yyyy-MM-dd'),
    'MMM')                                                                      AS PriorMonth3Char,
    DATEDIFF(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())), TO_DATE(CSI.LOSSDT)) <90 as="" br="">    CheckForXDaysAgoTrueFalse
FROM
    eis_app.CLAIMSSUMMARYINFO CSI limit 1