Using a Different Database Connection for PDT Jobs

connection
pdt

(Kevin Marr) #1

Note: This feature is covered in our documentation here.

In Looker version 5.12, persistent derived table (PDT) jobs can connect using separate JDBC parameters (host, port, user, database, etc). This is valuable for a number of reasons:

  1. PDT jobs can authenticate through a separate database user, with a higher query queue (a.k.a. workload management) priority. This way the database can prioritize the PDT jobs over less-critical user queries.
  2. Write access can be revoked for the standard Looker database connection, and only granted to a special user that PDT jobs will authenticate through. This is a better security posture for most organizations.
  3. For databases like Snowflake, PDT jobs can be routed altogether to more powerful hardware that is not shared with the rest of the Looker users. This way PDTs can build quickly without incurring the cost of running expensive hardware 24/7.

How It Works

On the edit connection screen, if you have PDTs enabled you may override any of the existing connection properties, like host, port, username, password, and whatever else is being used to specify the database connection.

In this example, I’ve overridden the “Additional Params” property for a Snowflake connection with a separate warehouse called “my_sweet_cluster”.

FAQ

Can I now use PDTs on my parameterized connection?

Yes! You can, but only if you’re parameterizing the connection with user attributes (as opposed to directly through LDAP).

Can the override be parameterized as well?

Not currently.

Can I write my PDTs to a separate database server software/dialect of SQL?

No. For example, you cannot write PDTs to a PostgreSQL database if your default connection goes to a MySQL database.

Can I write my PDTs to a separate database (superset of schemas) within the same dialect of SQL?

For systems that support “databases” as the superset namespace of schemas, no—we do not yet support scoping for the scratch schema (“Temp Database”). Note that wherever the PDTs are located, they must remain accessible and readable by the default (non-PDT) connection.


Looker 5.12 Release Notes
Looker 5.14 Extended Support Release Notes
(Seth Newman) #2

What do we need to do to successfully change our configuration from our normal looker DB user to another user made exclusively for PDT’s? On a Postgres DB, we encountered an issue where Looker tried to recreate PDT’s and got an error trying to rename the PDT to the name of a table that already existed -

Error in RENAME TABLE: org.postgresql.util.PSQLException: ERROR: relation "lr$lzh1vutdpb6b1qej835hd_some_table_name" already exists

Do we need to drop the original tables from the looker_scratch space? Or make sure that the new PDT user has the right ownership over the original scratch tables?

Hope this helps for anyone else who may have run into a similar issue.


(Ted Conbeer) #3

I had a bear of a time determining the correct privileges to use this feature with Redshift. For the benefit of others, here is a set of GRANTS that will generate the desired permissions:

grant usage on schema analytics to looker_pdt_builder;
grant select on all tables in schema analytics to looker_pdt_builder;

alter schema looker_scratch owner to looker_pdt_builder ;
grant all on schema looker_scratch to looker_pdt_builder WITH GRANT OPTION;
grant all on all tables in schema looker_scratch to looker_pdt_builder WITH GRANT OPTION;

grant usage on schema analytics to looker;
grant select on all tables in schema analytics to looker;

grant usage on schema looker_scratch to looker;
grant select on all tables in schema looker_scratch to looker;
alter default privileges for user looker_pdt_builder in schema looker_scratch
    grant select on tables to looker;

You’ll want to run this as a superuser.

Assumes you have your normal tables in analytics, build your pdts in looker_scratch and have Looker connecting with a user named looker with the pdt override user called looker_pdt_builder. If analytics contains views that select from other schemas, you’ll need to grant usage on those schemas to both users, as well.


(philip.martinelli) #4

Hey @Seth_Newman,

Provided the db user defined in the PDT Override section has the same grants as the original db user to the temp db scratch schema, Looker should always drop the existing pdt before renaming the new table. Whenever a rename fails, it’s possibly a consequence of a failed DROP. Please check whether your PDT Override user has the needed permissions to perform that function. If you’re still running into trouble, please email support@looker.com.

Thanks,
Philip