• Home
  • Privacy Policy
Breaking News, US News, World News and Bollywood News
  • Home
  • Technology
    IBM acquires data observability firm Databand.ai

    IBM acquires data observability firm Databand.ai

    Working with Microsoft’s .NET Rules Engine

    Working with Microsoft’s .NET Rules Engine

    How to use Rust with Python, and Python with Rust

    How to use Rust with Python, and Python with Rust

    India opens up its digital payments systems and other key tech

    India opens up its digital payments systems and other key tech

    How to nail your cloud computing job interview

    How to nail your cloud computing job interview

    Lego embraces modularity, metaverse with its software engineering culture

    Lego embraces modularity, metaverse with its software engineering culture

    Trending Tags

    • Nintendo Switch
    • CES 2017
    • Playstation 4 Pro
    • Mark Zuckerberg
  • Entertainment
    Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

    Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

    Next James Bond: Britain ditches Tom Hardy in pick for new 007 | Films | Entertainment

    Next James Bond: Britain ditches Tom Hardy in pick for new 007 | Films | Entertainment

    Steam sale FINAL warning: Last chance for BIG savings – End date and time for summer sale | Gaming | Entertainment

    Steam sale FINAL warning: Last chance for BIG savings – End date and time for summer sale | Gaming | Entertainment

    Queen and Adam Lambert live filmed concert Rhapsody Over London announced for streaming | Music | Entertainment

    Queen and Adam Lambert live filmed concert Rhapsody Over London announced for streaming | Music | Entertainment

    Sylvester Stallone’s ‘hatred’ of Arnold Schwarzenegger detailed | Films | Entertainment

    Sylvester Stallone’s ‘hatred’ of Arnold Schwarzenegger detailed | Films | Entertainment

    Star Wars ‘remake’: Original trilogy’s key scenes being ‘reconstructed’ for Disney Plus | Films | Entertainment

    Star Wars ‘remake’: Original trilogy’s key scenes being ‘reconstructed’ for Disney Plus | Films | Entertainment

    Trending Tags

      • Bollywood
      • Hollywood
      • Music
    • Lifestyle
      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      Monty Don shares the job to do ‘immediately’ if you spot blight – ‘it can be saved’

      Monty Don shares the job to do ‘immediately’ if you spot blight – ‘it can be saved’

      Lawn care tips July: Common mowing mistake turning your lawn yellow – how to fix it

      Lawn care tips July: Common mowing mistake turning your lawn yellow – how to fix it

      Monty Don: How to prune rambling roses now to encourage ‘more flowers’ next year

      Monty Don: How to prune rambling roses now to encourage ‘more flowers’ next year

      Gardening guru shares how to get an ‘unlimited’ supply of herbs with ‘super easy’ steps

      Gardening guru shares how to get an ‘unlimited’ supply of herbs with ‘super easy’ steps

      Gardening: Heatwave weather forecast warning – how to water your lawn and garden

      Gardening: Heatwave weather forecast warning – how to water your lawn and garden

      Trending Tags

      • Golden Globes
      • Game of Thrones
      • MotoGP 2017
      • eSports
      • Fashion Week
    • Nature
    • Business
    • Health
      • Food
    • Fashion
    • Science
    • Sports
    • Travel
    • World News
    No Result
    View All Result
    • Home
    • Technology
      IBM acquires data observability firm Databand.ai

      IBM acquires data observability firm Databand.ai

      Working with Microsoft’s .NET Rules Engine

      Working with Microsoft’s .NET Rules Engine

      How to use Rust with Python, and Python with Rust

      How to use Rust with Python, and Python with Rust

      India opens up its digital payments systems and other key tech

      India opens up its digital payments systems and other key tech

      How to nail your cloud computing job interview

      How to nail your cloud computing job interview

      Lego embraces modularity, metaverse with its software engineering culture

      Lego embraces modularity, metaverse with its software engineering culture

      Trending Tags

      • Nintendo Switch
      • CES 2017
      • Playstation 4 Pro
      • Mark Zuckerberg
    • Entertainment
      Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

      Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

      Next James Bond: Britain ditches Tom Hardy in pick for new 007 | Films | Entertainment

      Next James Bond: Britain ditches Tom Hardy in pick for new 007 | Films | Entertainment

      Steam sale FINAL warning: Last chance for BIG savings – End date and time for summer sale | Gaming | Entertainment

      Steam sale FINAL warning: Last chance for BIG savings – End date and time for summer sale | Gaming | Entertainment

      Queen and Adam Lambert live filmed concert Rhapsody Over London announced for streaming | Music | Entertainment

      Queen and Adam Lambert live filmed concert Rhapsody Over London announced for streaming | Music | Entertainment

      Sylvester Stallone’s ‘hatred’ of Arnold Schwarzenegger detailed | Films | Entertainment

      Sylvester Stallone’s ‘hatred’ of Arnold Schwarzenegger detailed | Films | Entertainment

      Star Wars ‘remake’: Original trilogy’s key scenes being ‘reconstructed’ for Disney Plus | Films | Entertainment

      Star Wars ‘remake’: Original trilogy’s key scenes being ‘reconstructed’ for Disney Plus | Films | Entertainment

      Trending Tags

        • Bollywood
        • Hollywood
        • Music
      • Lifestyle
        Mrs Hinch gardening fans share household item hack for removing weeds from paving

        Mrs Hinch gardening fans share household item hack for removing weeds from paving

        Monty Don shares the job to do ‘immediately’ if you spot blight – ‘it can be saved’

        Monty Don shares the job to do ‘immediately’ if you spot blight – ‘it can be saved’

        Lawn care tips July: Common mowing mistake turning your lawn yellow – how to fix it

        Lawn care tips July: Common mowing mistake turning your lawn yellow – how to fix it

        Monty Don: How to prune rambling roses now to encourage ‘more flowers’ next year

        Monty Don: How to prune rambling roses now to encourage ‘more flowers’ next year

        Gardening guru shares how to get an ‘unlimited’ supply of herbs with ‘super easy’ steps

        Gardening guru shares how to get an ‘unlimited’ supply of herbs with ‘super easy’ steps

        Gardening: Heatwave weather forecast warning – how to water your lawn and garden

        Gardening: Heatwave weather forecast warning – how to water your lawn and garden

        Trending Tags

        • Golden Globes
        • Game of Thrones
        • MotoGP 2017
        • eSports
        • Fashion Week
      • Nature
      • Business
      • Health
        • Food
      • Fashion
      • Science
      • Sports
      • Travel
      • World News
      No Result
      View All Result
      Updates News
      No Result
      View All Result
      Home Technology

      Tired of searching Slack, GitHub, and Google Drive separately? Do it all at once in SQL

      admin by admin
      June 7, 2022
      in Technology
      0
      Tired of searching Slack, GitHub, and Google Drive separately? Do it all at once in SQL
      0
      SHARES
      6
      VIEWS
      Share on FacebookShare on Twitter


      You know the drill: The words you are looking for might be in Slack, or GitHub, or Google Drive, or Google Sheets, or Zendesk, or … the list goes on. Searching across these silos is a common frustration. It should be frictionless, and this Steampipe dashboard makes it so.

      This wasn’t my first rodeo. I started this journey in 1996 and have revisited the idea periodically. In 2018 I wrote about a version that was the classic example of The Simplest Thing That Could Possibly Work: a web page that corrals the search URLs for various services and visits each in its own tab. As silly as that sounds it was helpful enough to get used a bit, and not just by me.

      Of course I wanted to use the underlying APIs, normalize the results, and merge them into a common view. But the effort required to wrangle all the APIs made that project more trouble than it was worth. If you’ve done this kind of thing before you know that most services provide search APIs along with adapters for your preferred programming language. But each service will have its own way of calling the API, paginating results, and formatting them. Those differences create friction you need to overcome in order to work with the results in a consistent way.

      When API wrangling becomes frictionless, though, many things become possible. Effective metasearch is one of them. Steampipe gets you out of the business of calling APIs, paginating results, and unpacking JSON objects. It calls the APIs for you and streams the results into database tables so you can focus entirely on working with the data. That solves the biggest problem you face when building a metasearch dashboard.

      Converging on a schema

      The next challenge is to bind search results to a common schema. SQL is a great environment in which to do that. The query that drives the dashboard shown in the screencast includes three stanzas that you don’t have to be a SQL wizard to write. They all follow the same pattern as this one for searching GitHub issues.

      select
         'github_issue' as type,
         repository_full_name || ' ' || title as source,
         to_char(created_at, 'YYYY-MM-DD') as date,
         html_url as link,
         substring(body from 1 for 200) || '...' as content
      from
         github_search_issue
      where
         $1 ~ 'github_issue'
         and query = 'in:body in:comments org:github ' || $2
         limit $3
      

      Items in blue are the names of columns in a database table—in this case github_search_issue, one of the tables made by Steampipe’s GitHub plugin. The Steampipe hub makes it easy to inspect the names and descriptions of the columns in the table, and shows you examples of how to use the information in the table.

      Because fetching the data doesn’t require calling APIs and unpacking JSON, you can focus on higher-order search syntax, which is plenty to think about, along with the interesting (and fun!) challenge of mapping source columns to a common schema.

      Items in red are the names of the columns that show up in the dashboard. For this dashboard we’ve decided each search result will map to these five columns: type, source, date, link, and content. SQL’s AS clause makes it easy for each stanza to rename its columns to match the schema.

      The full query

      Here’s the full query that drives the dashboard. There are three stanzas like the one above, each written as a CTE (common table expression) with parameters corresponding to input variables. And there’s almost nothing else! Each stanza queries an API-based table (slack_search, github_search_issue, googleworkspace_drive_my_file), selects (and maybe transforms) columns, then aliases the results to match the schema. All that’s left is to UNION the three CTEs, which act like temporary tables, and order the results.

      with slack as (
        select
          'slack' as type,
          user_name || ' in #' || (channel ->> 'name')::text as source,
          to_char(timestamp, 'YYYY-MM-DD') as date,
          permalink as link,
          substring(text from 1 for 200) as content
        from
          slack_search
        where
          $1 ~ 'slack'
          and query = 'in:#steampipe after:${local.config.slack_date} ' || $2
        limit $3
      ),
      github_issue as (
        select
          'github_issue' as type,
          repository_full_name || ' ' || title as source,
          to_char(created_at, 'YYYY-MM-DD') as date,
          html_url as link,
          substring(body from 1 for 200) || '...' as content
        from
          github_search_issue
        where
          $1 ~ 'github_issue'
          and query = ' in:body in:comments org:${local.config.github_org} ' || $2
        limit $3
      ),
      gdrive as (
        select
          'gdrive' as type,
          replace(mime_type,'application/vnd.google-apps.','') as source,
          to_char(created_time, 'YYYY-MM-DD') as date,
          'https://docs.google.com/document/d/' || id as link,
          name as content
        from
          googleworkspace_drive_my_file
        where
          $1 ~ 'gdrive'
          and query = 'fullText contains ' || '''' || $2 || ''''
        limit $3
      )
      
      select * from slack
      union 
      select * from github_issue
      union 
      select * from gdrive
      
      order by
        date desc
      

      Dashboards as code

      Many dashboard systems can work with this query. You can, for example, connect Metabase, or Tableau, or another Postgres client to Steampipe and build the same kind of interactive dashboard as shown here. You’d do that work in a low-code environment where widgets and settings are handled in a user interface. Steampipe’s dashboard subsystem takes a different approach informed by its infrastructure-as-code (IaC) roots. Queries against APIs should be expressed in SQL code that’s managed, like all other code, in version-controlled repositories. The dashboard widgets that display the results of those queries should likewise be expressed in code, and in this case the language is Terraform’s HCL.

      Here’s the HCL definition of the metasearch dashboard. It declares three kinds of input block: sources (multi-select), search_term (text), and max_per_source (single-select, which is the default). You can do much more with the input block—notably, you can fill it with results from a SQL query, as shown in the documentation. That’s not needed here, though.

      The table block uses the query defined above, and defines the parameters passed to it. The wrap argument ensures that columns with lots of text will be readable.

      dashboard "metasearch" {
      
        input "sources" {
          title = "sources"
          type = "multiselect"
          width = 2
          option "slack" {} 
          option "github_issue" {}
          option "gdrive" {}
        }  
      
        input "search_term" {
          type = "text"
          width = 2
          title = "search term"
        }
      
        input "max_per_source" {
          title = "max per source"
          width = 2
          option "2" {}
          option "5" {}
          option "10" {}   
          option "20" {}
        }  
      
        table {
          title = "search slack + github + gdrive"
          query = query.metasearch
          args = [
            self.input.sources,
            self.input.search_term,
            self.input.max_per_source
          ]
          column "source" {
            wrap = "all"
          }
          column "link" {
            wrap = "all"
          }
          column "content" {
            wrap = "all"
          }
        }
      
      }
      

      Again there’s not much else to see here, nor should there be. Building dashboards as code shouldn’t require lots of complex code, and it doesn’t.

      No wizardry required

      Just as you don’t need to be a SQL wizard to create new subqueries, you also don’t need to be an HCL wizard to add them to the dashboard. Would you like to add sources? There are dozens of other plugins to choose from, with more added each month. They don’t all offer search but many do, and it’s easy to find them with (of course!) a Steampipe query.

      select
        name
        html_url
      from
        github_search_code
      where
        query = 'search org:turbot org:francois2metz org:ellisvalentiner org:theapsgroup'
        and name ~ 'table'
        and name ~ 'search'
      order by
        name
      

      In the steampipe-samples repo we’ve included the code for the dashboard shown here, along with an extra search stanza for Zendesk that we removed when our trial account expired. Have fun extending this dashboard! If a search API you need isn’t already available, drop by our Slack community and let us know. Somebody might already be writing the plugin you need—or maybe you’d like to tackle that yourself. Every new plugin makes it possible for anyone who can work with basic HCL plus SQL to wield APIs like a pro and solve real problems.

      Copyright © 2022 IDG Communications, Inc.



      Source link

      admin

      admin

      • Trending
      • Comments
      • Latest
      UK’s most dangerous plant: Father’s warning — ‘Never seen a child so badly burned’ | Science | News

      UK’s most dangerous plant: Father’s warning — ‘Never seen a child so badly burned’ | Science | News

      June 6, 2022
      Plant warning as bamboo causes £100,000 of damage to Hampshire property

      Plant warning as bamboo causes £100,000 of damage to Hampshire property

      January 31, 2022
      Social workers warned weeks before baby Mitchell died in 2019 | TV & Radio | Showbiz & TV

      Social workers warned weeks before baby Mitchell died in 2019 | TV & Radio | Showbiz & TV

      January 2, 2022
      Yorkshire nan on how to make Yorkshire puddings with no ingredients weighed

      Yorkshire nan on how to make Yorkshire puddings with no ingredients weighed

      February 15, 2022
      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      0
      Boris Johnson slammed by Indie band for using their song ‘Blue Bunch Of Corrupt W****rs’ | Music | Entertainment

      Boris Johnson slammed by Indie band for using their song ‘Blue Bunch Of Corrupt W****rs’ | Music | Entertainment

      0
      Call Your Mom, Because Sue Grafton’s Alphabet Murder Mystery Books Are Becoming A Show

      Call Your Mom, Because Sue Grafton’s Alphabet Murder Mystery Books Are Becoming A Show

      0
      Release Date, Cast, And More

      Release Date, Cast, And More

      0
      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      July 7, 2022
      Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

      Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

      July 7, 2022
      Solar storm warning: UK ‘flying blind’ on space weather risks, but new monitor could help | Science | News

      Solar storm warning: UK ‘flying blind’ on space weather risks, but new monitor could help | Science | News

      July 7, 2022
      Who What Wear Podcast: Chris Horan

      Who What Wear Podcast: Chris Horan

      July 7, 2022

      Recent News

      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      July 7, 2022
      Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

      Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

      July 7, 2022
      Solar storm warning: UK ‘flying blind’ on space weather risks, but new monitor could help | Science | News

      Solar storm warning: UK ‘flying blind’ on space weather risks, but new monitor could help | Science | News

      July 7, 2022
      Who What Wear Podcast: Chris Horan

      Who What Wear Podcast: Chris Horan

      July 7, 2022
      Breaking News, US News, World News and Bollywood News

      Follow Us

      Browse by Category

      • Bollywood
      • Business
      • Entertainment
      • Fashion
      • Food
      • Health
      • Hollywood
      • Lifestyle
      • Music
      • Nature
      • Science
      • Sports
      • Technology
      • Travel
      • World News

      Recent News

      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      Mrs Hinch gardening fans share household item hack for removing weeds from paving

      July 7, 2022
      Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

      Wordle 383 July 7 daily HINTS: Can’t solve today’s Wordle? Three clues to help with answer | Gaming | Entertainment

      July 7, 2022
      • Home
      • Privacy Policy

      © 2021 Updates News

      No Result
      View All Result

      © 2021 Updates News