Looker is a powerful business intelligence and data analytics platform that enables organizations to access, analyze, and visualize their data in a more meaningful way. In this blog post, we’ll show you how to import data from Looker into R, a popular open-source programming language for statistical computing and graphics. By importing data from Looker into R, you can perform advanced analytics, modeling, and visualization techniques to gain insights into your data.
Prerequisites
Before we get started, you need the following:
- Access to a Looker instance with API access enabled
- A Looker username and password with API access permissions
- R installed on your computer
- The
httrlibrary installed in R
Step 1: Load the Required Libraries
To make API requests from R, we’ll use the httr library. To load the library, run the following code:
library(httr)
Step 2: Define the Looker API Endpoint and Credentials
The first step is to define the Looker API endpoint and your credentials in R. The API endpoint for your Looker instance will be in the following format:
endpoint <- "https://{host}.looker.com:19999/api/3.0/sql"
Replace {host} with the hostname of your Looker instance. For example, if your Looker instance is at https://myinstance.looker.com, the API endpoint would be:
endpoint <- "https://myinstance.looker.com:19999/api/3.0/sql"
Next, define your Looker username and password:
user <- "{your_username}"
password <- "{your_password}"
Replace {your_username} with your Looker username and {your_password} with your Looker password.
Step 3: Define the SQL Query
The next step is to define the SQL query that you want to run in Looker. The SQL query should be written as a string in R. For example, to select all the columns and rows from a table named orders, you can use the following code:
query <- "SELECT * FROM orders"
Step 4: Make the API Request
Once you have defined the API endpoint, credentials, and SQL query, it’s time to make the API request. We’ll use the GET function from the httr library to make the request. The GET function takes the following parameters:
endpoint: the API endpoint for your Looker instancequery: a list containing the SQL queryuser: your Looker usernamepassword: your Looker passwordadd_headers: a list of headers to include in the request, such asContent-Type
Here’s the code to make the API request:
response <- GET(endpoint, query = list(sql = query),
user(user),
password(password),
add_headers('Content-Type' = 'application/json'))
Step 5: Parse the Response
if (status_code(response) == 200) {
# If successful, parse the JSON response into a data frame
looker_data <- content(response, as = 'text') %>%
fromJSON(flatten = TRUE) %>%
as.data.frame()
} else {
# If unsuccessful, print the response content
print(content(response, as = 'text'))
}
- If the status code of the response is 200, the response content is a JSON string containing the result of the query. We parse the JSON string into a data frame using the
fromJSONfunction from thejsonlitelibrary. If the status code is not 200, it means there was an error, and we print the response content to see what the error message is.
After running these steps, the data from Looker will be stored in the looker_data data frame. You can use R’s standard data manipulation and visualization functions to work with this data.
For importing data from Looker into R, We do have an open source R library called LookR for API 3.0. However, it’s not supported and hasn’t been updated recently (in the past 2+ years, use with caution). It calls the Looker API to get data into R.
There are talks of updating LookR to fit the new API versions, but it is still in its early stages.
For importing data from Looker into R, This is another package that was put together by different people, not generated in the same way as our other currently supported SDK: https://github.com/looker-open-source/sdk-codegen and github.com/Rich1000/lookr
At this point in time, we’d recommend using Python if it’s an option.
How To Export Data From Looker To Google Sheets?
Looker and Google Cloud Platform (GCP): A Powerhouse Partnership for Data Analytics
Looker Multistage Deployment Framework (Dev->QA->Prod)
Improved Performance with New LookML Compiler
As we know saving the look in Looker can only 5000(max amount) can be saved.
In order to import all the data from a looker into R without rowlimits
As far as I know and based on my experience
You can definitely pull more than 5000 rows via the API. But Looks have row limits associated with them in the UI. Normally, when you run_look() via the API, you can specify a limit that is as high as you’d like.
————————————-
A quick way to get data out of Looker without row limits.
Returns a data frame (well actually it’s a tibble) with all data from specified Look.
set up
You will first need to create a file ~/.Renviron with the following variables:
LOOKER_API_PATH = 'https://???.looker.com:19999/api/3.0'
LOOKER_CLIENT_ID = '???'
LOOKER_CLIENT_SECRET = '???'
installation
install.packages('devtools')
library('devtools')
install_github('rich1000/lookr')
usage
library(lookr)
df = get_look(look_id = 123) # default row limit of 500
df = get_look(look_id = 123, limit = 10000) # custom row limit
df = get_look(look_id = 123, limit = -1) # without row limit
Hope you like my content, please leave feedback and also let me know if you have any other method or solution for this.
About Me:-
My name is Om Prakash Singh – welcome to my blog!
I am a data analytics consultant with a specialty in Looker. With years of experience in the field, I have developed a strong understanding of data analysis and visualization, and have a passion for helping organizations make informed decisions through data-driven insights. My expertise lies in utilizing Looker, a leading data platform, to help businesses turn their data into actionable insights, streamline their data processes, and drive growth. I am dedicated to sharing my knowledge and experience with others through this blog, and I hope you will find the content informative and valuable. Whether you’re a seasoned data professional or just starting out, I believe there is something here for everyone. Thank you for stopping by and I look forward to connecting with you!
Reach out to us here if you are interested to evaluate if Looker is right for you or any other BI solution.
