Schedule (Unlimited) Results to an SFTP Server

done
low_priority
reply

(Zach Taylor) #1

#The Basics

As of Looker 4.6, you can schedule reports directly to an SFTP server. Results can be unlimited, allowing users to schedule and send large result sets, provided they meet the streaming criteria (that is, the report can’t contain table calculations or totals or, in some dialects, pivots). The scheduler will let you know as you’re scheduling if the report can be unlimited or not. To see this option in the scheduler modal, the user needs to have the send_to_sftp permission.

When to Schedule to SFTP

Similar to sending results to S3 (discourse), sending results to an SFTP server works well when results are too large to send directly via email. Because Looker uses Sendgrid to process scheduled emails, and Sendgrid has a 19.5MB size limit (as of November 2016), the reports Looker sends via email have to be limited in size. Streaming to an SFTP server allows customers to bypass browser, memory and email limitations.

Additionally, scheduling results to an SFTP server allows customers to send large result sets from Looker to an SFTP server that can then securely pass those results to third parties. You could, for example, generate a report in Looker and schedule a CSV to be delivered daily to an SFTP server that could forward the CSV to vendors.

#How It Works

  1. Create a schedule for a saved Look or Dashboard.

  2. Select ‘SFTP’ from the ‘Destination’ dropdown (the user needs to have the send_to_sftp permission to see this)

  3. Enter the URL of the server that you want to send results to
    NOTE: the path should end with “/”
    sftp://sftp.server.com/home/user123/ NOT sftp://sftp.server.com/home/user123

  4. Enter SFTP credentials

  5. Select ‘Save All’ or send a test via ‘Send Test’ (‘Send Test’ also works for sending a one time email)

##Whitelisting
You can find a list of IP addresses to whitelist on your SFTP server here. They are the same ones used for secure database access.

Custom Applications

Have a great idea for ways that this feature can be used? Post details below to give ideas to others in the Looker community!


Setup SFTP Server for Unlimited Scheduling
Schedule & download Data from look - Limit
Looker 4.6 Release Notes
(Joe Schmid) #2

Can we use SSH private key credentials with this or is it strictly limited to username and password?


(sam) #3

Hi @Joe_Schmid - currently this is limited to username and password. I’ll let our product team know about your desire here!


(Rex Gibson) #4

I second private key request.


(Rex Gibson) #5

Can we get this into the main looker documentation?


(romain.ducarrouge) #6

Hey @Rex Thanks for letting us know, I have let our product guys know about your feedback and suggested a document update to reflect this in the Looker Documentation!


(Steve Calla) #7

Can you provide recommendations for sftp vendors, and detail on how to get the sftp address setup to input as noted in the instructions?


(Dave Hunsinger) #8

One thing to keep in mind is that an absolute path is required in the sftp url. If you use command like sftp to connect to the server (say, sftp.server.com) and issue a pwd command a path will be returned. Say it’s /home/user123. The sftp address for the schedule should be sftp://sftp.server.com/home/user123/.


(John Norman) #9

One of our partners is Box.com . . . we like them because they sign a HIPAA BAA.

This would be a nice feature for us, but . . . Box.com uses FTPS or FTPES (but not SFTP!). It is possible to upload to them with curl and/or lftp: We have the launch codes, so to speak, if you ever wanted to get a correct command that would actually work.


(Morgan Imel) #10

Hey John,

I’ll definitely let the team know about Box using FTPS!
For now, you might be able to utilize webhooks to make this work.

Morgan


(Mark Harnett) #11

Can we see any feedback if we typed something in erroneously?


(jeremy.eckman) #12

Hey @mharnett, it sounds like you are hoping to get more feedback from the scheduler/more ability to test beyond the regular error messages? I can pass this feedback along to our product team!


(Mark Harnett) #13

I discovered after posting that I was getting emailed with the error message, and it wasn’t super helpful, but yes instant on screen feedback would be better.


(jeremy.eckman) #14

Thank you for your feedback @mharnett - I’ve submitted this information to our product team.


(Tim O'Malley) #15

Is there any way to change what the SFTP’d filename is?


(Carl Saffron) #16

Hi @timomall – There is not currently a way to change the filename when shared via SFTP. I’ll let our product team know that this is something you’d like to see.

Could you explain more of your workflow and how this change will impact that workflow? I’d like to be able to relay as detailed information as possible. Thanks!


(Michael Jassowski) #17

I had the same question as Tim, and ran across this… Sorry to resurrect an old thread!

Our use case: we have a set of data defined in a Look that is used in our un*x automation scripts which needs to be updated once per day with the most recent results.

Since Looker appends the view name, timestamp, and random sequence to the file name, the automation script cannot know where the data will end up. We will also start filling up the disk with older out of date copies.

We could create a python API call and get the data directly from Looker, but there is a value in having all of our scheduled reports available in a single location (i.e. within the Looker UI).


(Izzy) #18

Definitely a valid request— I know in the past people have done things like webhook/otherwise schedule the results to an intermediary (like zapier, or a custom script) that then renames the files and puts them onto their final destination. That way you’d still, like you said, have everything in a single location.

But, it’s feedback we’ve heard before (and not just from @timomall) and it’s on our radar.


(Izzy) split this topic #19

2 posts were split to a new topic: Private Key authentication for SFTP