Connect to Turbot Pipes from R

Since your Turbot Pipes workspace is just a Postgres database, you can use the standard RPostgres adapter to query your workspace database from R.

You can get the information needed to connect to your Turbot Pipes database instance from the Query tab for your workspace. On the Query tab, click the info button at the top of the query window to show the connection information.

To get started, Install the RPostgres package, specify your connection string, create a connection, then run a query.

In this example we connect from the R console, load the query results, then use its summary function to summarize the data.

install.packages('RPostgres')
library(DBI)
db <- 'dea4px'
host_db <- 'rahulsrivastav14-rahulsworkspace.usea1.db.steampipe.io'
db_port <- '9193'
db_user <- 'rahulsrivastav14'
db_password <- 'f4**-****-**2s'
con <- dbConnect(RPostgres::Postgres(), dbname=db, host=host_db, port=db_port, user=db_user, password=db_password)
tbl <- dbGetQuery(con, 'select name, region, versioning_enabled from aws_s3_bucket')
summary(tbl)

Connect to Steampipe CLI from R

To connect R to Steampipe CLI, run steampipe service start --show-password and use the displayed connection details.

Steampipe service is running:

Database:

  Host(s):            localhost, 127.0.0.1, 172.28.158.171
  Port:               9193
  Database:           steampipe
  User:               steampipe
  Password:           9a**-****-**7e
  Connection string:  postgres://steampipe:9a49-42e2-a57e@localhost:9193/steampipe

Call the Turbot Pipes API from R

You can also use the Turbot Pipes query API. Grab your token, put it an environment variable like PIPES_TOKEN, and use this pattern.

install.packages("httr")
library(httr)
response <- POST( url="https://pipes.turbot.com/api/latest/user/rahulsrivastav14/workspace/rahulsworkspace/query",
add_headers(.headers = c(
'Authorization'='Bearer {PIPES_TOKEN}',
'Content-Type' = 'application/json',
'Encoding' = "UTF-8")),
body = '{"sql":"select name, region from aws_s3_bucket limit 2"}')
content(response, "text")

{
  "items": [
    {
      "name": "amplify-authcra-devc-deployment",
      "region": "us-east-2"
    },
    {
      "name": "appstream-app-settings-us-east-1-v01a5",
      "region": "us-east-1"
    }
  ]
}