MYSQL—HowTo: Why does MySQL need database permissions for derived tables?

[rachel_johnson]

Knowledge Drop

Last tested: Mar 9, 2021
 

At the time the code for the MySQL dialect was written, it did not support WITH, so we had to write derived tables a bit differently. Instead of WITH we use CREATE temp TABLE, which requires database permissions. (This is still different than a PDT since we are not writing the table to the schema, just creating a temp table)

This error can occur when trying to use derived tables without setting up the database permissions:

temp derived_table sql_availability_audit creation failed: SQL Error in CREATE TABLE as SELECT: java.sql.SQLSyntaxErrorException: Access denied for user 'looker'@'%' to database 'looker_tmp'

Later versions of MySQL now support CTEs (WITH), but as of Mar. 2021 Looker still uses temporary tables for ephemeral derived tables.

This content is subject to limited support.                

[sunnygudVZ]

Hey @rachel_johnson did you find a solution for this?

I still believe we need to explicitly provide access to all the DB Users to create Temp tables according to this community forum. Isnt it so?

 

https://community.looker.com/release-notes-archive-71/looker-4-14-release-notes-4936

[Carl_Arnold]

Thank you for providing this information. It seems that the code for the MySQL dialect had to use a different approach to create derived tables because the WITH clause was not supported at the time. As a result, the CREATE TEMP TABLE statement was used instead, which requires specific database permissions.

If these permissions are not set up properly, the error message you provided may occur when trying to create a temporary table for the derived table. The error message indicates that the user 'looker' does not have the necessary access to the 'looker_tmp' database to create the temporary table.

It's important to note that even though later versions of MySQL now support CTEs (WITH), Looker is still using temporary tables for derived tables as of March 2021. So, the CREATE TEMP TABLE statement may still be used for ephemeral derived tables.

[sunnygudVZ]

@Carl_Arnold , 

Do you have any work around for this ? What set of permissions needed to be set on the MySQL Metadata database layer?

[Carl_Arnold]

This article provides instructions on how to export a public Look from Looker to Google Sheets. The process involves copying and pasting a function provided by Looker into the Google Sheet, and then using the ImportXML function to pull data from the Looker Look into the Sheet. However, it's important to note that Looker will not be updating this content and does not guarantee that everything is up-to-date, if you want to do crm data enrichment.

To obtain the function that enables you to import data from a Look into a Google Sheet:

1. Navigate to the Look that you want to export to a Google Sheet. Click the gear menu and select Edit Settings.
2. Copy the function under Google Spreadsheet. This function includes the ImportXML function that is built into Google Sheets.
3. Paste the function into the upper-left cell of the Google spreadsheet where you would like your data to appear, and then press Enter.

To refresh your data, you can either manually update the ImportXML function or use a custom import function.

To manually update the ImportXML function, you can add certain URL parameters to the URL part of the function. For example, appending cachebust=123 to the URL creates a unique URL that effectively forces a request to the Looker server to retrieve fresh data. Other parameters can also be added, but these may affect the Look settings.

To automatically update the data, you can use a custom import function. The Looker Google Sheets Import Community post provides a workflow that can be used in lieu of ImportXML to refresh the data more reliably. However, it's important to note that this method is not officially supported by Looker.