Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

SQL

For all of your SQL errors, troubleshooting questions, and cool queries

  • 347 Topics
  • 807 Replies

347 Topics

P
Paul_Price
asked in SQL

Filter out weekends in Redshift

I would expect the following two filters to return the exact same results. First filter Second filter However, the second filter returns no results. Here’s the SQL where clause generated by the first filter: WHERE (MOD(EXTRACT(DOW FROM paid_digital.date)::integer - 1 + 7, 7) IN (0,1,2,3,4)) and now the second: WHERE (MOD(EXTRACT(DOW FROM paid_digital.date)::integer - 1 + 7, 7) IN (-6,-5)) I’m pretty sure this is a bug in the Redshift dialect. In the meantime, if you run into this issue, just select MON-FRI as I did in the first example.

1
lloydtabb
Looker Staff
6 years ago
J
jakecoltmanMember
asked in SQL

Preventing pdt rebuilding

Hi, We have all of our pdts on a time schedule (either every hour or every day), but this causes problems in two cases: 1 - The underlying table is being rebuilt -> the pdt is empty or misses some data 2 - The database currently has a high load -> running expensive pdts slows everything else down In neither of these cases do we want the pdt to rebuild, what’s the best practice way to achieve this? My thoughts thus far have been to extract the sql triggers into a view in the database and have something like the below in every looker view. This allows us to force the value of the sql_trigger to not change in periods where cases (1) or (2) apply, but it feels like a really heavy solution to the problem. sql_trigger_value: SELECT * FROM schema.pdt_trigger_values where view_name = 'VIEW NAME' Any suggestions would be welcome Thanks Jake

1
J
6 years ago
B
barkha
asked in SQL

How to identify all dashboards and looks using a specific derived table?

Is there a way to identify the list of all dashboards and looks using a specific derived table? I am hoping that there is an automate way so that if we rename/drop a derived table, we don’t have to manually review all dashboards and looks.

1
mikhailxu
Looker Staff
6 years ago
B
barkha
asked in SQL

Best practices for managing derived table and dashboards

Are there any best practice recommendations for managing derived tables and dashboards? How to we ensure that we get best values out of this powerful feature while ensuring that we are managing them the same way we manage data warehouse tables etc. We are starting to work on some best practices but would love to hear any thoughts from the community here.

0
B
6 years ago
M
Matthew_Scott_AB
asked in SQL

Is there a way to use natural full joins?

I’m trying to perform a natural join between two views, “inventory_movements_ext” and “order_lines_ext”, each referencing a ‘product_id’, and each with an additional ‘location’ dimension that uses data in each original table to determine a common location label. The goal is, given a product_id, to pivot by location, and for each location to see the sums of certain quantities from each of the original views. My first hunch is to perform a natural join between the two views, and to coalesce the two location dimensions into one (since with a natural join between two tables, each row will only ever have one of the two tables’ values, and the other tables’ columns will be NULL). This would then let me pivot by the coalesced location, and then get the sums I’m looking for. My initial findings lead me to think that I’ll need to create a derived table to accomplish this, and then to create a view based on the derived table. I wanted to post here first though, to see if there were facilities in

0
M
6 years ago
A
apatersonMember
asked in SQL

Join table only if filtering

I have a derived table from a set of tables structured like the following: Event --> Detail --> Site, Section, … All of the details that I need are in the Event table and I have templated filters on some of those fields, but I also want to allow the users to filter on site, section, etc. The issue is that, if there is no filtering, the join to the Detail table (and possibly the Site, Section, etc. tables) is unnecessary. As there are (literally) millions of entries in the Event table, the superfluous joins are costly if there is no filtering, but I cannot see a way to do a join only if filtering on the relevant fields is requested. Is this possible in Looker? Thanks, Andrew

6
segahm
6 years ago
A
aposillico
asked in SQL

Parameter Value or Using a Filter as a Variable in a Function

In many SQL client GUIs you can create a query parameter, which can turn an element of a WHERE statement into a variable, and the user is prompted to enter a value so that the query can run based on that value. Here is my idea I am trying to solve for: I have a relatively complex formula that uses some product sales metrics and some user-defined values to come up with an expected cost. For simplicity sake, let’s say it just looks like this: Cost = metric1 x U, where U is some number Is there a way to turn a dimension within a derived table into a variable based on the value of a filter? I’m thinking I could create a derived table that generates a series, but I don’t have any ideas on how to join such a table to any other table without creating a cartesian product, which in this case could be too many rows. Any ideas? I’m an Accountant that knows enough SQL to be dangerous, so please forgive me if the answer is simple.

1
nbeyer
Looker Staff
7 years ago
segahm
segahmMember
posted in SQL

Optimizing Redshift Performance

Sort Keys Typically, the primary timestamp or date field of any given table will be the best candidate for your sortkey. This helps Redshift speed up queries that are sorted or limited by that column. Distribution Keys For tables that join in other tables with a great deal of frequency (such as fact tables in the star schema world), it’s best to set your distribution key to be the foreign key with the greatest cardinality. For tables that join into other tables (such as dimension tables in the star schema world), it is best to set the primary key to be the distribution key. More about Sort & Distribution Keys Redshift has a page on how to best choose sort and distribution setups depending on data configuration. Analyzing Table Design Redshift has a nice page with a script that you can run to analyze your table design. The script checks if you’ve got sort keys, distribution keys, and column compression dialed in. Vacuum As you update tables, it’s good practice to vacuum. This is be

3
M
7 years ago
J
johanntagle
asked in SQL

Using always_filter on a PDT column

I’m currently using a derived table with templated filter, which has a default setting of limit to past 3 months via always_filter. It works nicely, though quite slow due to all the grouping involved in the derived table. What I’m trying to do is to create a persistent derived table so that everything is pre-grouped. Then I will just apply the filter after the PDT has been built. Problem is, if I remove the following filter entry used by the templated filter (see 1st entry below) in the view definition I get a “always_filter: unknown filter field ‘view_name.filter_date’” message, even if I have a dimension field called filter_date (see 2nd entry). If I keep the filter entry, there’s no error but it ignores that filter (I guess the filter entry in the view is only used by templated_filters). Am I doing something wrong or am I hitting a bug? Below shows the relevant lookml. Thanks. On the view: - filter: filter_date label: 'Date or Date Range' type: date convert_tz:

3
J
7 years ago
drewgillson
drewgillsonMember
asked in SQL

Quarter and quarter_of_year timeframes are not compatible with MSSQL

Hi Looker team, I was looking forward to these new quarter timeframes, but they don’t work in MSSQL! The reason is that you guys are generating SQL that uses the CONCAT function, which doesn’t exist in MSSQL’s world - you just use +. This is the error our database returns when I try to create looks using the new timeframes: The Microsoft SQL Server database encountered an error while running this query. ‘CONCAT’ is not a recognized built-in function name. Hope you can get this fixed soon 😞

6
L
Looker Staff
7 years ago
H
herdrickMember
posted in SQL

'False' values for boolean field show up as NULL in SQL Runner and query results

I’m having an issue that I traced to this unexpected (by me) behavior: ‘False’ values for boolean fields show up as NULL in SQL Runner, and of course in query results as well. Here’s a simple example. I made a very simple table, seen here as queried from Postgres’s command line client: => select bar, count(*) from foo group by bar; bar | count -----+------- | 1 f | 1 t | 1 (3 rows) You can see there is one NULL, one False, and one True. Now run that same SQL in SQL Runner: Results Query returned 3 rows in 0.003s bar count ∅ 1 ∅ 1 true 1 So that’s weird. Why is Looker declaring False values to be NULL? And then, why isn’t it grouping those NULL values together?

12
H
7 years ago
M
msh210Member
asked in SQL

SQL comments and filter templates

-- {% condition foo %} bar {% endcondition %} will test the condition even though the SQL is commented out, and will generate an error if the filter foo doesn’t exist. Of course, you can instead put the obsolete condition in a LookML comment # formerly at line 42: {% condition foo %} bar {% endcondition %} after the SQL. But sometimes it’s nice to have them in a particular spot within the SQL. You can use -- {[]% condition foo %} bar {[]% endcondition %} or the like so Looker doesn’t test the condition.

0
M
7 years ago
D
davekimMember
asked in SQL

Multiple CASE WHEN scenarios using one Templated Filter

I’m trying to use templated filters to allow the user to select the number weeks they want to see trend on. I came up with a way to do this but it only works if the user selects the filter. Since the templated filter creates 1=1 when no filter is selected, I’m unable to write multiple CASE WHEN scenarios. Are there any other ways to accomplish what I’m trying to do here? I’d like to give the users an option to select up to the last 13 weeks. Below is the LookML - filter: number_of_weeks_for_trend type: int default_value: 6 suggestions: [1,2,3,4,5,6,7,8,9,10,11,12,13] - dimension: number_of_weeks_to_trend_key hidden: true type: int sql: | (CASE WHEN {% condition number_of_weeks_for_trend %} 1 {% endcondition %} THEN 14 WHEN {% condition number_of_weeks_for_trend %} 2 {% endcondition %} THEN 21 WHEN {% condition number_of_weeks_for_trend %} 3 {% endcondition %} THEN 28 WHEN {% condition number_of_weeks_for_trend %} 4 {% endcondition %} THEN 35

1
lloydtabb
Looker Staff
7 years ago
J
jyauMember
asked in SQL

How to use join to PDTs in explore definition?

I’m trying to perform an INNER JOIN to a PDT as part of a join in an explore. I’ve previously used SQL_TABLE_NAME in defining PDTs that depend on other PDTs, but that syntax doesn’t appear to work when defining an explore. Perhaps I’m missing something obvious about how to do this correctly? Incidentally, in the very same explore, I also had to join to a regular (non persistent) derived table, which works just fine. - explore: repurchase_performance_by_customer joins: - join: user_purchase_facts # regular, non-persistent derived table - this works sql: INNER JOIN looker_scratch.user_purchase_facts as user_purchase_facts ON user_purchase_facts.user_id = repurchase_performance_by_customer.user_id - join: user_acquisition_product_facts # PDT - this one doesn't work sql: INNER JOIN ${user_acquisition_product_facts.SQL_TABLE_NAME} ON user_acquisition_product_facts.user_id = repurchase_performance_by_customer.user_id Error: Unknown or inaccessible field “user_acquisit

2
J
7 years ago
J
joshsiegelMember
asked in SQL

About the SQL & Derived Tables category

[Replace this first paragraph with a short description of your new category. This guidance will appear in the category selection area, so try to keep it below 200 characters. Until you edit this text or create topics, this category won’t appear on the categories page.] Use the following paragraphs for a longer description, as well as to establish any category guidelines or rules. Some things to consider in any discussion replies below: What is this category for? Why should people select this category for their topic? How is this different than the other categories we already have? Do we need this category? Should we merge this with another category, or split it into more categories?

1
colin
Looker Staff
7 years ago
H
herdrickMember
asked in SQL

Have filters on joined view not change measures on explore view?

I’m using symmetric aggregates. I want to put filters on a field in a joined-in view, “B”, such that it affects measures of that view, but doesn’t affect measures of the explore view, “A”. Without thinking about the SQL needed to generate this query, I initially intuited that because of the ‘left outer’ nature of joins in Looker, this would be the behavior of filters on joined-in views in this situation, but it is not, either in Looker or SQL. A row of the explore, “A”, shows up in the results if it does not match with any rows in “B”, or if it matches with rows in “B” which pass “B”'s filters. That’s a left outer join. But if a row in “A” matches only with rows in “B” which don’t pass “B”'s filters, that “A” row is not in the results. In fact I don’t think this is really feasible in Looker. In SQL you’d put the condition from the WHERE clause into ON clause of the join. Or you could replace the joined-in table with a query on that table with a WHERE that would do the filtering you wa

2
H
7 years ago
M
madsunisport
asked in SQL

Indexes for derived tables

Hi, I’m using a derived table for computing some details about a user. The Lookerml manual says that I can use indexes for derived tables, yet I get a warning about indexes being an unknown parameter for a derived table, http://www.looker.com/docs/reference/derived-tables We use Redshift which I believe should be a Postgres dialect version. Thank you. - view: user_avg_value derived_table: sql: | SELECT orders.user_id as user_id , orders.email as email , orders.website as website , AVG(orders.order_value) AS avg_value , SUM(orders.order_value) AS total_value , SUM(orders.order_refund) AS total_refund FROM orders WHERE NOT cancelled GROUP BY orders.user_id, orders.website, orders.email indexes: [email, website] sortkeys: [email] persist_for: 24 hours

1
anika
Looker Staff
7 years ago
segahm
segahmMember
asked in SQL

Recursive functions with Common Table Expressions

Say your data is stored in a relational store using child to parent relationship (e.g. one row per unique pair of child and its direct parent). Imagine now that this is a generic N-ary Tree and it has indefinite number of levels. An example of such a tree can be observed in the category hierarchy of Foursquare. Traversing this tree presents a problem. What if you need to match a child with its top-most parent? Or what if you need to match some category with all of its children (direct and non-direct)? Below is a solution that returns the top parent for every category. WITH RECURSIVE parent_category(parent_foursquare_id, parent_id, orig_foursquare_id ) AS ( SELECT parent_foursquare_id , foursquare_id AS parent_id , foursquare_id AS orig_foursquare_id FROM categories UNION ALL SELECT -- next iteration's id c.parent_foursquare_id -- this iteration's id , c.foursquare_id -- carry forward the original category id to connect ind

1
MikeD
Looker Staff
7 years ago
J
joshsiegelMember
posted in SQL

AWS release "Redshift Utilities"

Looks very useful: https://github.com/awslabs/amazon-redshift-utils Redshift Admin Scripts Scripts objective is to help on tuning and troubleshooting. If you are using psql, you can use \i <script.sql> to run. Script Purpose top_queries.sql Return the top 50 most time consuming statements in the last 7 days perf_alerts.sql Return top occurrences of alerts, join with table scans filter_used.sql Return filter applied to tables on scans. To aid on choosing sortkey commit_stats.sql Shows information on consumption of cluster resources through COMMIT statements

0
J
7 years ago
segahm
segahmMember
posted in SQL

Troubleshooting Redshift performance - extensive guide

This article covers general steps to troubleshooting performance issues arising from running Looker queries on Redshift database. It is not at all exhaustive since there can be many reasons for poor performance. Some of the commonly perceived reasons for poor database performance are: Poorly designed model Ex: using an unnecessary join, querying data without filters Less than optimal database set-up Ex: missing specification of sort keys, missing key procedures during the ETL process Poorly written queries by Looker Ex: Looker using a Redshift function that has been deprecated It is important to correctly diagnose the type of performance issue you are experiencing. For example, while Looker does handle a big part of logic for writing SQL queries, most of that logic is passed on to Looker in the form of LookML definitions. If definitions are correctly defined, on average Looker will generate more efficient queries than if all of the possible queries and their derivatives

0
segahm
8 years ago
J
joshsiegelMember
asked in SQL

SUBSTRING_INDEX in Redshift?

I am using a dimension like this in MySQL and need to convert it to Redshift: - dimension: major_version type: number sql: SUBSTRING_INDEX(${number}, '.', 1) Is there something like SUBSTRING_INDEX in Redshift?

2
anika
Looker Staff
8 years ago
izzy
izzyGood Looker
posted in SQL

About the SQL category

For all your SQL errors, troubleshooting questions, and cool queries If you have a question or post about a PDT, please make that in the appropriate subcategory.

0
izzy
8 years ago

Badges

  • Friendly Face
    sbrylowskihas earned the badge Friendly Face
  • Helpful
    carriehas earned the badge Helpful
  • Community Star
    carriehas earned the badge Community Star
  • Friendly Face
    carriehas earned the badge Friendly Face
  • Community Star
    Dawidhas earned the badge Community Star
Show all badges
Terms of Service