Using Looker’s SQL Generator

[Dr__Strangelove]

With Looker, you use LookML to model your database both in a structured and reusable way, and in a way that governs the integrity of your data. When your users run a query in a Looker Explore, Looker generates the necessary SQL query to retrieve data from your database.

In addition to querying from Looker Explores, you can use the Looker API to leverage Looker's data governance. You can use the Run Inline Query Looker API endpoint to run a query and return the result in a format that you specify. By substituting different formats for {result_format} in your POST /queries/run/{result_format} request, you can do the following tasks:

• Access Looker's SQL generator to create a SQL query for you by specifying sql for {result_format} in the POST request.
• Run a specified query directly on your database by specifying json_bi for {result_format} in the POST request.

When you use the Looker API for these tasks, you don't have to worry if the business logic is correct, since that is managed in Looker. Similarly, you don't have to track changes in the underlying database or the format of the data. You can leverage Looker to manage all of that.

Example: Using POST /queries/run/sql to create a SQL query

You can use the Run Inline Query Looker API endpoint to have Looker generate a SQL query for you. Specify sql for the {result_format} in the POST request, then specify your query in JSON format in the body of the API call, like this:

{
  "model": "thelook",
  "view": "orders",
  "fields": ["order_items.total_sale_price", "users.email", "orders.id"],
  "filters": {
    "order_items.total_sale_price": ">=1000"
  },
  "sorts": ["order_items.total_sale_price desc 0"],
  "limit": "500"
}

NOTE: For the view value in the JSON body, enter the name of the Explore that you want to query.

This will return the SQL query that is equivalent to the JSON query in the body of your API call. For example, for the JSON in the previous example, the Looker API would provide this response:

SELECT
    `t1`.`users.email`,
    `t1`.`orders.id`,
    `t1`.`order_items.total_sale_price`
FROM
    (SELECT
            `orders`.`id` AS `orders.id`,
            `users`.`email` AS `users.email`,
            COALESCE(SUM(`order_items`.`sale_price`), 0) AS `order_items.total_sale_price`
        FROM
            `thelook`.`orders` AS `orders`
            INNER JOIN `thelook`.`users` AS `users` ON `orders`.`user_id` = `users`.`id`
            INNER JOIN `thelook`.`order_items` AS `order_items` ON `orders`.`id` = `order_items`.`order_id`
        GROUP BY
            1,
            2
        HAVING `order_items.total_sale_price` >= 1000) AS `t1`
ORDER BY
    `t1`.`order_items.total_sale_price` DESC
LIMIT 500
​

You can now use this SQL to query your database directly.

Example: Using POST /queries/run/json_bi to query your database

You can also use the Run Inline Query Looker API endpoint to get results directly from your database. Specify json_bi as the result format in the POST request, and then write the JSON document to describe your query in the body of the API call. For example, here is the same JSON as in the previous example, except with the limit set to 2:

{
  "model": "thelook",
  "view": "orders",
  "fields": ["order_items.total_sale_price", "users.first_name", "orders.id"],
  "filters": {
    "order_items.total_sale_price": ">=1000"
  },
  "sorts": ["order_items.total_sale_price desc 0"],
  "limit": "2"
}

When you run this API call, the API responds with row data plus metadata describing the fields, pivots, table calcs, and other aspects of the query, directly from your database, like this:

{
  "metadata": {
    # METADATA SNIPPED
  },
  "rows": [
    {
      "users.first_name": {
        "value": "patrick"
      },
      "orders.id": {
        "value": 4212
      },
      "order_items.total_sale_price": {
        "value": 1291.89
      }
    },
    {
      "users.first_name": {
        "value": "dennis"
      },
      "orders.id": {
        "value": 14742
      },
      "order_items.total_sale_price": {
        "value": 1237.99
      }
    }
  ]
}