Automated testing and Looker?


(Ethan Herdrick) #1

We’ve got a lot of work invested into our Looker views and model, and in ETL code whose output is consumed by Looker. Does anyone have any experience in doing automated testing of Looker output?


(Ethan Herdrick) #2

Perhaps via scheduled Looks that send CSVs to some endpoint that checks the results?


(lloyd tabb) #3

We haven’t done this (except for the way we unit test Looker), with our coming release of the new API, writing something that pull looks then pulls data from a known timeframe from the API then comparing results would be a pretty good way of doing some automated testing.


(Ebjpdx) #4

We are starting to do automated testing using Rspec via the API. We have the framework setup, but not too many tests written yet.

Looking forward to the new API release!


(Rjnordeen) #5

Bump on this thread.

Does anyone have any new updates for processes they are using to test their looker models/dashboards/views?


(Ethan Herdrick) #6

Nothing by us, yet. :frowning:


(Lawrence) #7

Looks like this is something other organizations are struggling with.

I did want to hear how others are handling committing from multiple developers? Do you have Looker code reviews?


(John Norman) #8

I wrote a command line app to download a view (via a share URL). It returns an exit code of 1 on a SQL error, and then we send email if something is awry . . . that helps.


(Ben Owens) #9

Bumping this thread. Has anyone gotten anywhere using any kind of automated testing on Looker?


(romain.ducarrouge) #10

Hey @bowens at this time, the best way would still be to use the API to get your Looker data to compare its results.


(Arushabt) #11

Hi,

Could you please share sample framework ?

Thanks,
Arushabt


(Arushabt) #12

Hi,

Did you find any solution for automated testing of looker output ?

Thanks.


(romain.ducarrouge) #13

Hey @arushabt,
yeah we can use the API in order to test the output of Looker versus your database’s output as well as testing production versus dev mode.
Here are the details on a couple approaches you could take (with Python) to achieve different tests:

Testing value outputs between Looker and Database:

  1. Create a few Looks in Looker that we will use to compare values.
  2. Define your python script which will log you into your Looker instance.
  3. Use the run_look() function a first time, specifying the format to be sql, this will give you the sql used for that look and store that as a variable in your script.
  4. Use the run_look() function a second time, specifying the format to be json, this will give you the output values for that look and store that as a second variable in your script.
  5. Use the relevant packages for your script to access your database, e.g Psycopg2 for Postgres and establish a connection to it.
    eg: conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
  6. Now that you have the connection to the db established, you can run the SQL stored in the variable from step 3. for that Look directly in the DB and store the output in a third variable.
    eg: cur.execute("""SELECT datname from pg_database""")
  7. Finally you have two variables for the output out of Looker and the one out of the db, so you can compare these two to see if they match!

Testing value outputs between Production and Development:

  1. Create a few Looks in Looker that we will use to compare values.
  2. Define your python script which will log you into your Looker instance.
  3. Use the update_session() function to set your API session as dev mode.
  4. Use the run_look() function a first time, specifying the look_id for the look to test, this will give you the output for that look based on the code in your dev mode and you will store that as a variable in your script.
  5. Use the run_look() function a second time, specifying the following arguments; look_id, and also force_production:true, this will let you run the look the way is is currently defined in the production version of your code, and you will store that output as another variable in your script.
  6. Finally you have two variables for the output out of your Development mode and the Production mode, so you can compare these two to see if they match!

I hope this helps. Please feel free to let us know if you have questions or to add to this thread when you implement some other approaches to do this!!


(Arushabt) #14

Thank you @romain.ducarrouge. I’ll start building framework for this.


(Adam Lahley) #15

Is this still the only/best method for automated testing of Looker dashboards? I have tried Cypress which worked pretty well up until the last few Looker bumps and now it’s pretty much useless. Anyone else have ANY other options? I’m interesting in discussing if so. THANKS!


(romain.ducarrouge) #16

Here is a snippet on achieving the second example from above, Testing Values between Dev mode and Production for a user.

require 'looker-sdk'

# get API creds from environment variables
LOOKER_ID = ENV['LOOKER_ID']
LOOKER_SECRET = ENV['LOOKER_SECRET']
LOOKER_PATH = 'https://COMPANY.looker.com:19999/api/3.0'

looker = LookerSDK::Client.new(
  :client_id => LOOKER_ID,
  :client_secret => LOOKER_SECRET,
  :api_endpoint => LOOKER_PATH
)

# Get the user input to test the Look 
# Could be changed to read a file, or a list of looks to test
puts "Please enter the Look ID to use for the test?"
look_to_get = gets.chomp


# get look, and its attributes
my_look = looker.look(look_to_get)
look_title = my_look["title"].to_s
look_id = my_look["id"].to_s
current_session = looker.session["workspace_id"]

puts "Testing the Look '#{look_title}', with ID '#{look_id}' for #{current_session} branch."

# depending on the content used for testing, may need to use additional args for run_look():
# https://docs.looker.com/reference/api-and-integration/api-reference/look#run_look
# rebuild_pdts: true ?

prod_branch_results = looker.run_look(look_to_get, 'csv', force_production: true)
prod_branch_query = looker.run_look(look_to_get, 'sql', force_production: true)

# Changing to Dev branch to run the look there
puts "Changing to dev mode."
looker.update_session("workspace_id": "dev")
current_session =  looker.session["workspace_id"]

puts "Testing the Look '#{look_title}', with ID '#{look_id}' for #{current_session} branch."
dev_branch_results = looker.run_look(look_to_get, 'csv', force_production: false)
dev_branch_query = looker.run_look(look_to_get, 'sql', force_production: false)


if prod_branch_results == dev_branch_results
  puts "Success! Production data matches your dev mode data."
else
  # let's get some details on values and queries
  File.open("production_output.csv", 'w') { |file| file.write(prod_branch_results) }
  File.open("production_query.txt", 'w') { |file| file.write(prod_branch_query) }

  File.open("development_output.csv", 'w') { |file| file.write(dev_branch_results) }
  File.open("development_query.txt", 'w') { |file| file.write(dev_branch_query) }
  puts "The outputs are not identical, please check out the output files for details"
end

The code above assume you:

  1. have cloned our repo for the Looker Ruby SDK (require 'looker-sdk')
  2. have defined all your API credentials as local environment variables
    (LOOKER_ID = ENV['LOOKER_ID'] and LOOKER_SECRET = ENV['LOOKER_SECRET'])
  3. are testing Looks