2 Replies Latest reply on Nov 4, 2016 8:46 AM by Zach Leber

    BTUG at Broad Institute on November 3, 2016

    Zach Leber

      Looking forward to welcoming the BTUG community back to Broad in Cambridge!

       

      This event is now live on Eventbrite, please sign up here:

      Boston Tableau User Group Tickets, Thu, Nov 3, 2016 at 4:30 PM | Eventbrite

       

      We're still looking for a corporate snack sponsor and Tableau doctors. Please use the organizer link on the Eventbrite invite to contact me if you're interested or have any other questions. You can also reply to this thread so that others see the conversation.

        • 1. Re: BTUG at Broad Institute on November 3, 2016
          Zach Leber

          Here is some SQL code that will read web JSON data through a live Oracle 12 database connection. In this example the "readwebservice" function has been added to the "jiradwh" schema and the SELECT statement can be used as a view or as Custom SQL.

           

          CREATE OR REPLACE FUNCTION readwebservice(url VARCHAR2) RETURN CLOB

          IS

             pcs UTL_HTTP.html_pieces;

             retv CLOB;

          BEGIN

             pcs := UTL_HTTP.request_pieces(url, 5000);

             FOR i IN 1 .. pcs.COUNT

             LOOP

                retv := retv || pcs (i);

             END LOOP;

             RETURN retv;

          END;

           

           

          SELECT jt.*

           

           

          from (

           

              select

              jiradwh.readwebservice('http://www.aviationweather.gov//gis/scripts/MetarJSON.php?priority=5') as jstring

           

              from dual

           

          ) jdata,

          JSON_TABLE(jstring, '$.features[*]'

          COLUMNS (

                  id VARCHAR2(20) PATH '$.properties.id',

                  site VARCHAR2(20) PATH '$.properties.site',

                  temp NUMBER PATH '$.properties.temp',

                  dewp NUMBER PATH '$.properties.dewp',

                  wspd NUMBER PATH '$.properties.wspd',

                  wdir NUMBER PATH '$.properties.wdir',

                  cover VARCHAR2(20) PATH '$.properties.cover',

                  visib NUMBER PATH '$.properties.visib',

                  fltcat VARCHAR2(20) PATH '$.properties.fltcat',

                  altim NUMBER PATH '$.properties.altim',

                  slp NUMBER PATH '$.properties.slp',

                  longitude NUMBER PATH '$.geometry.coordinates[0]',

                  latitude NUMBER PATH '$.geometry.coordinates[1]'

          )) AS jt

          • 2. Re: BTUG at Broad Institute on November 3, 2016
            Zach Leber

            Attached please find some pictures from last night's event and a PDF copy of my slides. Thank you to our Tableau Doctors (Anthony, Misha, Kristen, and Yi) and to our snack sponsor Fresenius Kidney Care.

            20161103_164813.jpg20161103_183054.jpg

            1 of 1 people found this helpful