Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Knowledge base
Community ResourcesUsing the Community3 Account Info & Registration4 Community Feedback1
Community
WelcomeGetting started142 News & announcements125 Who's hiring494 Release notes (archive)63 Lesson Comments15 Challenge10
Looker developmentLookML1830 SQL319
Exploring & curating dataExplores558 Dashboards & looks1563
Application developmentEmbedding Looker / Powered by Looker155 Open source projects54 Looker API376
AdministrationUser access & management70 General Looker administration452
Data science & analyticsGot a question?83 Share your best practices16
The Kitchen TableTechnical Tips & Tricks1739 Common Account Questions14 Blog Archives347 DCL Mic Drops5 Video Tutorials 🎥4
コミュニティフォーラム (Japanese)ニュースと告知50 ヘルプとサポート14 コラム69

Ask question / Create topic
Login

Community
The Kitchen Table

The Kitchen Table

Explore the product knowledge we've built up together

2109 Topics

Newest first

Recently active Most replies Most views

Department of Customer LoveModerator

published in Technical Tips & Tricks

Can two connections on separate instances share a scratch schema/PDTs? aka Enemy ReaperKnowledge Drop

Last tested: May 11, 2020 TL/DR: Don't share the schema! Two instances should not share the same scratch schema because they can hit the "enemy reaper" scenario. PDTs CAN be shared using the publish_as_db_view feature (detailed below)The PDT Reaper cleans up (deletes) any tables that are expired. In the "enemy reaper" scenario, the reaper for one instance shares a connection registration key (the two letters after $ in the PDT name) and will delete all PDTs with the same reg key that are not listed in the "active_derived_table" internal db table. PDTs that were created by the other instance will not be here, so they will be deleted, and the other instance will do the same thing. Thus, all PDTs will constantly be being deleted. The most common way for instances to possibly have the same reg key would be sharing license keys.To share PDTs across instances, you should use publish_as_db_view. This way, one instance can create the PDT and be in charge of maintaining it, while the other one

1090

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

What are examples of how to configure the lookerstart.cfg startup file?Knowledge Drop

Last tested: Jul 3, 2018 Docs have a pretty simple exampleLOOKERARGS="--no-ssl --port 8080" Here's another example with more startup flags set:LOOKERARGS="--cipher-key-file key-20160226-220142.key -d looker-db --use-custom-jdbc-config --concurrent-render-jobs=4" As seen more clearly in the second example, all of the necessary startup flags go within the LOOKERARGS=" " variable.The startup flags seen are documented here. This content is subject to limited support.

1120

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentialsKnowledge Drop

Last tested: Jan 20, 2021If you're seeing this error, check to see if the table that is being queried is a google sheet that's stored in Drive.If that's the case, make sure that the Service Account for BQ that Looker is using has access to the Drive. This content is subject to limited support.

113037

1 month ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Custom Filters: 1 complete month agoKnowledge Drop

Last tested: Feb 2, 2021 ${events.created_date} >= add_months(-1,trunc_months(now()))AND ${events.created_date} < add_months(1,add_months(-1,trunc_months(now()))) This content is subject to limited support.

1190

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Custom Viz installation overviewKnowledge Drop

Last tested: Jan 26, 2021 There are two main ways you can install a custom visualization:Install an existing custom viz straight from marketplace Write your own custom viz from scratch and host the code either Outside of Looker, in a git repo or a CDN, or Within Looker, in the LookML Both of these approaches are overviewed here:INSTALLING A CUSTOM VIZ FROM MARKETPLACESteps to install a viz from marketplace:Search for the viz in marketplace and follow the installation instructions The viz will now be visible in Admin>Platform>Visualizations and will be available to use in exploresWhat happens when you install a viz from marketplace?In v. 6.24+ a project gets created on the instance (won't appear in the Dev drop down but you can find in “Manage LookML Projects”). The project will contain:-minified dist file (.js): allows for proper versioning and package management -configuration files (package.json, yarn.lock, .babelrc, webpack.config.js) -LICENSE: licensing -README: description

1750

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Maximum Images in Comp Three Image CarouselKnowledge Drop

Last tested: May 15, 2020 For the Marketplace Image Carousel Visualization (that is not made by Looker), we can add up to 5,000 images. This is in line with how many rows we can have in the UI of an Explore, as each row will have an image.However, the "breadcrumbs", or dots at the bottom of the visualization that we can select to navigate around, cap at 15. Any more than that, and we will use left/right. So it may not be as efficient to use more than 15, but it is possible. This content is subject to limited support.

1000

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Duplicate Views in Field Picker Using View_LabelKnowledge Drop

Last tested: Oct 21, 2020 Looker has an article about cool ways to use Liquid when labeling, but if you use Liquid in a view_label in one location, say for a field, but use a static string for view_label in another location, like the explore, you will see duplicate view labels in the field picker. ex: Liquid with view_label in view: Static view_label in explore:Result in Field Picker:WHAT IS HAPPENINGThe line breaks that help make the Liquid easier to read actually add spaces into the view_label, effectively making the labels different. ex: From explore: "My Awesome Name" From view: "My Awesome Name " To resolve this, remove any spaces/characters between each the liquid tags where you are defining the view name that you are trying to match. ex: THE RESULTYour field picker only has one view label for your custom name! Note: I left spaces between the {% else %} and Gobbleydegook because I wasn't trying to match another view_label defined else where. If you have other Liquid conditions

950

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Result Cache vs Rendered CacheKnowledge Drop

Last tested: Sep 6, 2019 1. Result cache is the results that came from the db with minimal post processing (this includes dimension fill but does not include formatting, drill fields, etc.) Result cache keys are created by hashing the SQL that generated them and a few other things including details about the connection.2. Rendered cache is the fully post processed and serialized (e.g. JSONified) result which is ready to return to the client. The rendered cache key is created by hashing info about the LookML project digest, the connection details in the Admin - Connections panel, general instance settings, query request params (field, filters etc), relevant user attribute values for the user (if the query uses access filters, access grants, liquid, etc.), other user settings (locale if localization is used, user timezone if User-Specific Timezones is enabled, etc.), result format (e.g. different file types for downloads), Looker version, and dev/prod. Unlike the result cache key, the re

1690

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Net::SFTP::StatusException (3, "permission denied") errorKnowledge Drop

Last tested: Dec 1, 2020 When you see the error Net::SFTP::StatusException (3, "permission denied") when setting up a sftp schedule it's usually caused by one of four things:A missing trailing slash at the end of the sftp connect url. This URL, for instance will not work:sftp://sftp.dev.looker.com/home/testaccount It will error with permission denied because sftp is trying to open a directory for writing. Adding a trailing slash opens a file for writing iside the target directory, which is what we want. So you should write:sftp://sftp.dev.looker.com/home/testaccount/The sftp user does not have write permissions to the destination directory. Test by creating a small text file in your local directory. Use command line sftp to connect to the destination server, e.g. sftp sftpuser@sftp.host.url. Once logged in try put filename. Type ls to verify that the file was transferred. If this errors or does not results in a file on the destination the sftp user doesn't have write permission. The de

6180

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

BIGQUERY (HowTo): Accessing all datasets in a BigQuery ConnectionKnowledge Drop

Last tested: Jan 21, 2019When setting up a BigQuery connection in Looker you have to choose a dataset in a project to create the connection, however, all datasets in a project should be available regardless of what dataset you put in your connection. This content is subject to limited support.

150

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

What does the Verify SSL Cert button do in the Connection panel?Knowledge Drop

Last tested: Jan 21, 2019 This checkbox allows you to specify if you want to require verification of the SSL certificate used by the connection. Unverified certificates will still ensure encrypted connections.Logic behind it:If the SSL certificate is not verified, a third party can’t eavesdrop on the communication, but we aren’t 100% certain that the server we are talking to is who they say they are. It is pretty common for companies to have servers with “self-signed” certificates inside their own infrastructure. These are usually safe to trust since their IT controls what servers are in their own infrastructure. If we have a ssh tunnel into their infrastructure it should be ok too. SSL is still important because otherwise someone who has access to the network might be able to read the packets as they go by....You should never trust a non-verified connection on the open internet, unless you are just using sample data or something else that is not important. This content is subject to l

930

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Why are my NDT values different when compared to an equivalent explore query?Knowledge Drop

Last tested: Sep 6, 2019 One reason why your NDT rows and your corresponding explore query rows may not be matching up is that your NDT is not doing the same timezone conversion. What this means is that aggregations like count distinct that get dimensionalized in your NDT may not be the same as your original explore query values, in particular when the NDT is persistent.In order to make sure that generated SQL of your persistent NDT includes the desired timezone conversion, you need to add in a timezone parameter under the explore_source parameter of your generated NDT LookML. Here's an example of the timezone parameter in action:view: example_ndt {derived_table: {explore_source: base_explore {timezone: "query_timezone"column: time_field {}column: count_distinct_field {}}}dimension: time_field {type: date_dateconvert_tz: no}measure: count_distinct_field {type: sum}} Note: if you DO NOT include convert_tz:no in your time_field dimension, querying the time_field will result in a double t

530

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

How are files sent to SFTP and S3 buckets named?Knowledge Drop

Last tested: May 31, 2019 All file names look like scheduleName_timestamp_hash, where hash is a random 6-character identifier and the timestamp follows the pattern YYYY-MM-DDTHHMM (e.g. 2019-05-31T0933).There is a feature request ticket to be able to customize the file name of files sent via schedules: Change file name for data sent in schedules This content is subject to limited support.

2173

3 months ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

persist_for hierarchyKnowledge Drop

Last tested: Jan 16, 2019 persist_for can be applied to PDTs, Models and Explores.There is a significant difference between PDT persist_for and the model/explore persist_for (caching). PDT persistence controls how long should the PDT live on the DB as a temporary table after it was first queried in the explore. Model and explore persist_for controls how long should the query previously run in Looker be kept in memory. It's simply results caching. The two are completely independent.When the persist_for parameter is set within both model and an explore, the explore setting will take precedence over the model setting. Check this Community article for more info This content is subject to limited support.

210

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Why do I have duplicate PDTs?Knowledge Drop

Last tested: Apr 4, 2020 PDTs can be shared by multiple models. When duplicates show up in the Admin PDT panel, the following could be the culprits:Datagroups used to persist the PDT are defined differently in different models. The models use a different connection. Defining some models to be case sensitive, while others are not. This might change the where clause in the PDT SQL.Duplicates also occur when we have include: *.view syntax across all of the models. To avoid this, one way is to create folders that hold the required views, and then including references to the folders, like like so: include: "/folder_name/*.view.lkml" This content is subject to limited support.

620

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Securing looker-db.ymlKnowledge Drop

Last tested: Oct 1, 2019 When moving a previously self-hosted instance to a MySQL backend, users are instructed to create a looker-db.yml file that contains the MySQL database credentials: https://docs.looker.com/setup-and-management/on-prem-mgmt/migrating-to-mysql#create_a_database_credentials_fileThose who do not want the clear text password for this configuration in a file on disk may optionally set environment variables for "LOOKER_DB" to accomplish the same thing.You can set the environment variable "LOOKER_DB" to contain a list of key/values for each line in the looker-db.yml file.export LOOKER_DB="dialect=mysql&host=localhost&username=root&password=&database=looker&port=3306"We'd also recommend that they limit use of the MySQL user account created to the IP address used by your Looker serverIt is important to also consider that, given Looker's encryption scheme, all sensitive data in the database is encrypted at rest. If someone were to steal the database cr

940

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Why aren't my new values for my embed parameters taking effect?Knowledge Drop

Last tested: May 28, 2020 If you’ve changed one of the embed parameters values in your script, in order for these new values to take effect, you would have to start a new embed session. If the user already has an active session it will just use that session instead of re-authenticating as the embed user with the new parameters. Creating a new session can be done by setting the "force_logout_login" parameter to be "true". This content is subject to limited support.

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Can you persist a derived table with a datagroup that's not included in the model the derived table is used in?Knowledge Drop

Last tested: Jan 21, 2019 No, you cannot. If a derived table is persisted by datagroup_trigger, that datagroup needs to be defined in every model that derived table is used in. If a datagroup isn't defined in a model that a derived table is used in, you'll get the following error:invalid datagroup_trigger {datagroup_name} declared for {derived_table_name} Since you cannot share identical datagroups across models, datagroups will be duplicated per explore/model if included in multiple models like this:NOTE: even though the derived table might be used in multiple models with the same datagroup defined, the derived table build should not be triggered multiple times. Instead, it should be built once and shared across the models. This content is subject to limited support.

1270

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Multiple PDTs and a single Datagroup ObjectKnowledge Drop

Last tested: Jan 16, 2019 If a user has multiple PDTs (say for example 3 PDTs) and they want to set a Datagroup trigger that only triggers when all 3 tables are built, then that is possible if the 3 PDTs are set to listen to the same Datagroup, only then will the cache invalidate when all 3 + PDTs are built.If each PDT is set to to the same Datagroup, the steps will be: Rebuild all PDTs, invalidate cache, send schedules This content is subject to limited support.

280

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

How can I have a PDT only trigger a rebuild when two (or more) tables in my database have been updated?Knowledge Drop

Last tested: Dec 27, 2018 Good question! There are at least two ways that we could get a PDT trigger that only triggers after two different tables in the DB have been updated.1. If last_load_time (or load times in general) are available, we should be able to use that query in your last comment or a query like below to trigger a rebuild only after both tables have been updated:-- Note: this min(timestamp) will only change when the most recent upload times for both Table A and Table B have changedselect min(timestamp) from(select max(timestamp) as timestamp from Table_A union select max(timestamp) from Table_B)Alternatively, we could a setup like below if Table load times are not available to us:# sql_trigger_value will trigger a rebuild every dayview: pdt_one {derived_table:sql: .... ;;sql_trigger_value: SELECT current_date;;}# sql_trigger_value will only force a rebuild when the master table updatesview: pdt_one_prime {derived_table:sql: SELECT count(*) as num FROM ${pdt_one.SQL_TABLE_

1010

1 year ago

samLooker Staff

published in Technical Tips & Tricks

Where can I use ${view_name.SQL_TABLE_NAME}?Knowledge Drop

Last tested: April 2021 Currently this syntax can be used anywhere that accepts valid SQL, INCLUDING the sql_trigger of a datagroup IF the datagroup is being used for a schedule trigger (not rebuilding PDTs). Docs: https://docs.looker.com/data-modeling/learning-lookml/derived-tables#referencing_derived_tables_in_other_derived_tables This content is subject to limited support.

2630

1 year ago

samLooker Staff

published in Technical Tips & Tricks

In what order does the PDT regenerator check trigger values?Knowledge Drop

Last tested: April 2021 The regenerator checks PDT triggers in a completely arbitrary order. The only rule is that if there are table dependencies, then the regenerator will check in order of those dependencies.What's a dependency?If Table A references Table B in its SQL, then we call that a dependency. For example:view: table_A { derived_table { sql: SELECT * FROM ${table_B.SQL_TABLE_NAME} ;; …This content is subject to limited support.

100

1 year ago

Page 67 / 85

Badges

sbrylowskihas earned the badge Friendly Face
carriehas earned the badge Helpful
carriehas earned the badge Community Star
carriehas earned the badge Friendly Face
Dawidhas earned the badge Community Star

Show all badges

Sign up

Already have an account? Login

Social Login

Username *

E-mail address *

Company

(Private)

Only you and moderators can see this information

Password *

I accept the Terms of Service.

If you’re an EEA-based consumer of digital content, services, or goods, and you’ve agreed to our Terms of Service, then EEA consumer laws provide you with a legal guarantee. Under this guarantee, we’re liable for any lack of conformity that you discover for the one-time supply of digital content or services at any time during the “continuous” supply of digital content or services.
Your national laws may provide an even longer guarantee. Your rights under these legal guarantees aren’t limited by any other commercial guarantees that we provide. If you want to make a guarantee claim or if you would like to delete your account data, please contact us lookerconnect-help@google.com.

loginBox.register.email_repeat

Login to the community

No account yet? Create an account

Social Login

Username or Email

Password

Remember me

Forgot password?

Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.

Username or e-mail

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.

The Kitchen Table

Can two connections on separate instances share a scratch schema/PDTs? aka Enemy ReaperKnowledge Drop

What are examples of how to configure the lookerstart.cfg startup file?Knowledge Drop

Update failed with `Timed out waiting for Looker to start`Knowledge Drop

Why would I get `504 gateway time-out nginx/1.12.21` errorKnowledge Drop

Custom Order for Field PickerKnowledge Drop

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentialsKnowledge Drop

Custom Filters: 1 complete month agoKnowledge Drop

Custom Viz installation overviewKnowledge Drop

Maximum Images in Comp Three Image CarouselKnowledge Drop

Duplicate Views in Field Picker Using View_LabelKnowledge Drop

Result Cache vs Rendered CacheKnowledge Drop

Net::SFTP::StatusException (3, "permission denied") errorKnowledge Drop

BIGQUERY (HowTo): Accessing all datasets in a BigQuery ConnectionKnowledge Drop

What does the Verify SSL Cert button do in the Connection panel?Knowledge Drop

Why are my NDT values different when compared to an equivalent explore query?Knowledge Drop

How are files sent to SFTP and S3 buckets named?Knowledge Drop

persist_for hierarchyKnowledge Drop

Why do I have duplicate PDTs?Knowledge Drop

Securing looker-db.ymlKnowledge Drop

Why aren't my new values for my embed parameters taking effect?Knowledge Drop

Can you persist a derived table with a datagroup that's *not* included in the model the derived table is used in?Knowledge Drop

Multiple PDTs and a single Datagroup ObjectKnowledge Drop

How can I have a PDT only trigger a rebuild when two (or more) tables in my database have been updated?Knowledge Drop

Where can I use ${view_name.SQL_TABLE_NAME}?Knowledge Drop

In what order does the PDT regenerator check trigger values?Knowledge Drop

Badges

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded

Can you persist a derived table with a datagroup that's not included in the model the derived table is used in?Knowledge Drop