Looker Community

Encapsulating Table Names in Quotes in SQL

Not sure whether to call this a bug or a Feature Request, but the SQL Runner (and Explores generated from them) don’t by default include quotes around a Table Name. This means if you happen to be stuck with Tables with spaces in the middle of them, the SQL Runner and Explores generated error out. I’ve attached a screen grab of an Explore query generated via the SQL Runner to give this example. Note how if the multi-word Table was wrapped with quotes in the “FROM” clause, all of this would be resolved.

I would call this a bug :bug:. In fact, our engineers would as well— We have some open issues for this on specific dialects. What dialect is this?

Note that this is not a super high priority issue (although please feel free to describe business impact if it is impacting you heavily), so while all updates will get posted in this thread, I can’t guarantee any kind of timeline on a fix. Thanks Ben!

Thanks Izzy! We’re on the MS SQL Server 2008+ connection (2012).

In terms of the business impact, it’s at this point just an inconvenience for generating an ad-hoc query from a Table. Moving forward, if Looker intends for clients to be able to let an Explore be a basis for importing fields into a new View (rather than using Describe to absorb all fields without a choice), this would make that change a bit less trivial, but nothing we couldn’t work around!

Hey Izzy! Just remembered to mention this still hasn’t been resolved. On an MS SQL instance, the SQL Runner doesn’t add quotes around the table names by default. As a result, anyone with a Table Name with a space, is left unable to use the Runner without manually modifying it. Even if you manually modify the code to add the quotation marks, the Explore functionality still doesn’t work. This is a real shame and I’d love if this could be made operational again!

Hey Ben,

Thanks for the reminder on this— It’s still a low priority bug and hasn’t been worked on, but I had a feeling there was a workaround… and there was!

So I realized we don’t actually add those quotes by default anywhere in our LookML SQL writer— Even in LookML, if you define the sql_table_name as

sql_table_name: dbo.Base Calendar ;;

You’ll get the same SQL resulting in a syntax error— You have to manually add the quotes,

sql_table_name: dbo."Base Calendar" ;;

So, I figured you’ve already done that in your view files, since you’re only seeing this in the SQL Runner.
If you use the “Model” Tab of the SQL runner to allow Looker to generate prepared SQL queries for you, you can do something like this:

By using the ${view._SQL_TABLE_NAME} syntax, you’re telling Looker to go find the view and then return the value in the sql_table_name parameter, in this case, the correctly quoted table name. And yes, when you “Explore” on that prepared query, it persists the quotations in the table name.

Does that tide you over on this? I can’t give a timeline estimate on when we might get to working on it, but this seems like a good option. I’d be curious to hear if your case isn’t addressed by it!

Hey Izzy, close, but not quite! While I can see this helping for when I have a view that already exists where I need to write a custom SQL query, it doesn’t assist with creating a view from a table (which to me is the primary reason I’d want this). In the absence of this, I use the Add View from Table and manually delete/hide any fields I don’t care to surface in a view, or if it’s faster, I just build the view from scratch.

I fully appreciate why this is low priority, by the way; hopefully the use case information I shared helps at least demonstrate the deficiency!

Ah, drat the rat!

That makes total sense. Yeah, given the specificity of the case and the inherently workaround-able nature of it, I think the priority is unlikely to change anytime soon— But like you said, the deficiency is clear. I’ll try to see if there’s a quick fix, appreciate you bringing it up and clarifying the exact use case you’re trying to solve!

Of course! We’ll get it good eventually. As an aside, it would be a great addition to SQL Runner functionality to have a way to ‘check for unsurfaced fields’. Something that would scrub through the SQL to find references to db fields, and put them on a list. It could serve as an Add Additional Fields to View approach. Please rein me in if I’m asking for the moon :slight_smile:

Correct me if I’m wrong @JeffH but this was part of the LoL project, right? Might merit it’s own thread explaining how if so :wink:

Any updates you can offer on this, Izzy? It restricts the power of a direct query tool substantially when the Explore functionality doesn’t work on standard tables, and it makes creating views a PITA compared to how easy it could be if this bug was resolved. Also feels like it shouldn’t need very ‘invasive surgery’ to get it right, but I know that what appears easy on the surface isn’t always under the hood :slight_smile:

Just so you’re aware, with this feature in place:

  1. Explore the Table from SQL Runner
  2. Select Fields
  3. Create View

Without it, there are two less-than-ideal options (unless I’m missing another):

  1. Add View from Table and manually remove all unneeded DB fields (only an option if the Table is within the first 500 Tables in the DB - otherwise the tables don’t preview on the Table List and can’t be selected to create a View from)
  2. Manually create view and each necessary DB fields