Creating and updating a temporary table in LookML

Looker Q&A Looker
1

I am trying to create/update table/view inside a SQL based derived_table in LookML. Is that possible? I keep getting an error which prevents the creation of the PDT in Looker but the query statements run fine in BigQuery and the table/view gets created in the temp database. Can someone please help?

2

Hello @JVFrancis

can you please elaborate what you are trying to achieve, whats the error you are receiving, and if possible, share some code here?

Thanks
Marc

3

@marcwo Sure! I am trying to run a sequence of create/update queries to be used as a LookML view. The temp table “Parent” gets created when I run these queries in BigQuery but I get the error “message”: “configuration.query.destinationTable cannot be set for scripts”, “status”: “INVALID_ARGUMENT” in the explore in Looker

Please see the code below,

declare counter int64;

set counter = 0;

create or replace temporary table Parent( OrgID int64 not null, OrgTypeID int64 not null, RootOrgID int64, CurrentOrgID int64 not null );

insert into Parent

select
OrgID,
OrgTypeID,
case when OrgTypeID in (0, 1, 3) then OrgID else null end,
ParentOrgID
from Organization where OrgID > 1;

repeat
set counter = counter + 1;

update Parent rpo
set
RootOrgID = case
when ans.OrgTypeID = 0 then ans.OrgID
when rpo.OrgTypeID = 2 and ans.OrgTypeID = 1 then ans.OrgID
when rpo.OrgTypeID = 4 and ans.OrgTypeID = 3 then ans.OrgID
else null end,
CurrentOrgID = ans.ParentOrgID
from Organization ans
where ans.OrgID = rpo.CurrentOrgID and rpo.RootOrgID is null;
until not exists(select 1 from Parent where RootOrgID is null) or counter > 15
end repeat;

select * from Parent
order by 1;

4

Are you trying to create this using the regular derived table syntax

view: customer_order_summary {
  derived_table: {
    sql:
      YOUR SQL CODE
  }
}

Or have you tried using the sql_create LookML syntax?

5

I am using the regular derived table syntax

6

I tried using sql_create instead of sql in the LookML but I got the "

Not found: Table us-ezl-dl-ingest-919726cd:looker_scratch.LR_8KE3O1724769551198_topparenttable was not found in location us-east4" error in the explore. topparenttable is the LookML view name.

7

I got it working by using the below code at the end of my derived_table LookML for creating the topparenttable view as given in the sql-create documentation
create or replace table ${SQL_TABLE_NAME} as (My final select query);
select * from ${SQL_TABLE_NAME} ;;
Thanks a lot @marcwo Much appreciated!

8

Perfect, glad we got it working- have a good day!

Best

9

Thanks! Have a good day!