ILooker (application database) sync to BigQuery

Hi,

We run on-prem currently and we are doing a lot of analysis through iLooker and/or directly on the database (we have built further functionality than what iLooker offers, eg. extended historic data). The MySQL database is a bit slow and struggles with some of the things we throw at it.
Aside from bulking up the database instance (which I am also looking into) I was wanting to get the data into BigQuery. I really don’t want to script up syncs of individual tables as its going to be a huge overhead so I was wondering if anyone has any ideas or experience doing something like this (with as little effort as possible).
Thx!

2 Likes

Hey @IanT,

I have found a daily ingestion method to BigQuery using data accessible within the iLooker Explores which has worked for us over the last few months - with no scripting necessary.

I daily schedule to a GCS bucket csv data from the System Activity Explores, saved as looks and scheduled in Looker directly, i.e. History, Dashboard, Query, Look, Users.

When they arrive in the GCS bucket they overwrite the previous day’s bucket file with the same name. In BigQuery I set up a daily import job (BigQuery Transfers) to append the History file in GCS to a partitioned History table in BigQuery. For the other tables, I set them up as simple external tables as they are dimensional.

Any additional transformation or joining prior to loading into Looker is done via BigQuery and pipeline tools like DBT - some scripting there, naturally - but depending on the content of the scheduled Looker files, and if you are happen with them ‘out-of-the-box’, you could alternatively do the BigQuery table joins in a LookML model of course.

The ability to do this is already within the standard features of BQ and GCP. There may be other ways to do it (i.e. with App Script or Cloud Functions) but this method for me has been the most seamless. It has not failed even once and I have Looker usage data beyond 90 days.

I hope this helps - your comment “we have built further functionality than what iLooker offers” caught my attention - what have you been cooking up there?

2 Likes

Thanks for info, its certainly an option, I was initially thinking of a very crude process which didn’t even involve Looker.
We check permissions on some spaces to make sure they haven’t changed (because frustratingly edit and manage permissions are coupled so I cant stop editers of a space adding undesired people). We are half way through building a process which will email users or owners of content which we are about to delete (replicating in a way the query used in the unused content section in the looker UI…and we have spec’d out more “tidyup” things, my comments here - Henry - A Command Line Tool for Looker Instance Cleanup.

3 Likes

@IanT Since you are on-prem you could easily setup something like Stitch to start syncing the tables you want.