Creating and updating a temporary table in LookML

[JVFrancis]

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?

[marcwo]

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

[JVFrancis]

@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;

[marcwo · Accepted solution]

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?

[JVFrancis]

I am using the regular derived table syntax

[JVFrancis]

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.

[JVFrancis]

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!

[marcwo]

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

Best

[JVFrancis]

Thanks! Have a good day!