Share on facebook
Share on linkedin
Share on twitter
Share on reddit

Connecting RStudio to superQuery

These instructions explain how to connect your RStudio account to superQuery’s query optimization engine.
 
When connected, your queries will pass through superQuery — where it will be automatically optimized — before being executed in BigQuery. 
 

The result: a significant reduction in query costs and execution time.

General 

  1. Click on the Integrations link in the sidebar of superQuery:

  2. Enable your desired connection by clicking “Connect”
  3. After clicking “Connect” you’ll see the username and password screen, like so:

Rstudio

 

  1.  Whitelist your IP Address if necessary
  2.  Copy the hostname and port from this window into your service of choice.
    • server: bi.superquery.io
    • port: 3306

6.  Finalize the connection.

Prerequisites

  • Download RStudio
  • In RStudio run the following from the console:
    • install.packages(“RMySQL”)
    • install.packages(“jsonlite”)


Connect RStudio to superQuery

  1. Open RStudio and create a new R script:
    library(RMySQL)
    library(jsonlite)
    mydb = dbConnect(MySQL(), user='xxxx', password='xxxx', dbname='yourproject.yourdataset', host='bi.superquery.io')
    rs = dbSendQuery(mydb, 'SELECT COUNT(*) FROM `yourproject.yourdataset.yourtable`;')
    data = fetch(rs, n=-1)
    rs_explain = dbSendQuery(mydb, 'explain;')
    data_explain = fetch(rs_explain, n=-1)
    data_explain = fromJSON(data_explain$statistics)
  2. Enter the following details into the script:
    1. Your username and password into the ‘xxxx’ parts
    2. yourproject, yourdataset and yourtable in the relevants parts.
  3. Run the script and you will see two variables were created:
    1. data
    2. data_explain

      The variable called “data” will contain your query results and the variable “data_explain” will contain your query’s execution plan and statistics.  It should look like this:

Boom! You’re all set.

Now you can start building your own beautiful dashboards. 
Query on! 🙂

Here are some ways you can organize boards and queries (okay, maybe not the last one 😉)

User Acquisition Board

  • LTV:CAC
  • CAC
  • MRR Churn
  • AVG MRR
  • Gross Margin
  • LTV

Marketing Campaigns Board

  • Facebook
  • Twitter
  • LinkedIn
  • Quora
  • Pinterest
  • Instagram

Pokemon Pokedex

  • Easiest to Capture by Type
  • Strongest by Type
  • Strongest by Generation
  • # Pokemon by Type
…but really, the possibilities are endless! It depends on you, the data you’re examining, and how you mentally organize your data insights.

Ready to run perfect queries?

Free to get started. No credit card required.

  • © 2019 superQuery. All rights reserved.

By continuing to use the website, you consent to the use of cookies.