Question

When creating a snapshot table, create_process reruns too frequently

  • 18 August 2019
  • 3 replies
  • 218 views

There’s a database table updated several times a day and I want a daily-snapshot table.


Let’s call the base table views.


So I created a table via


-- Snowflake
create table views_history(
id int identity not null primary key, -- that is, an autoincrement column
views_id int not null,
-- other columns from views,
as_of timestampltz not null default current_timestamp::timestampltz
)

and am populating it via


view: views_loader {
derived_table: {
sql_trigger_value: select current_date ;;
create_process: {
sql_step:
insert into views_history(views_id /* , other columns from views*/)
select id /* , other columns */ from views
;;
}
}
}

Then I have a Looker view into views_history.




Well, when Looker runs the views_loader PDT, it tries to first DROP TABLE LOOKER_SCRATCH.LR$[gibberish]_views_loader and then ALTER TABLE LOOKER_SCRATCH.LC$[other_gibberish]_views_loader RENAME TO LOOKER_SCRATCH.LR$[gibberish]_views_loader. Both of those commands, of course, fail, since there is no such table at all.


That wouldn’t be a problem except that Looker seems to interpret that as a reason to run the PDT again soon after. Thus, instead of getting a snapshot a day, I’m getting numerous snapshots a day. So… two things:



  • This is a bug report. There’s no reason a create_process PDT that doesn’t actually try to create a table should cause Looker to try to drop and rename a nonexistent table.

  • Is there a workaround? That is, until this bug is fixed, how do I populate a single table with a daily snapshot without the PDT’s running too often?


3 replies

A workaround @zckymc (many thanks!). Instead of


view: views_loader {
derived_table: {
sql_trigger_value: select current_date ;;
create_process: {
sql_step:
insert into views_history(views_id /* , other columns from views*/)
select id /* , other columns */ from views
;;
}
}
}

I use


view: views_loader {
derived_table: {
sql_trigger_value: select current_date ;;
create_process: {
sql_step:
create table ${SQL_TABLE_NAME}(views_id /* , ... */)as
select id /* , ... */from views
;;
sql_step:
insert into views_history(views_id /* , ... */)
select views_id /* , ... */ from ${SQL_TABLE_NAME}
;;
}
}
}

This seems to work fine — though it’s a workaround and doesn’t detract from the bug report above.

@menashe: I was interested in your post, and though I’m not a Looker expert, I also don’t understand why the simpler view LookerML def’n that you provided wouldn’t work. Have you received any responses, or come to an understanding of why the second option you laid out is necessary?

No, I haven’t, @Ethan_Miller.

Reply