Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Solved

How to create a query with custom dimensions via the API?

  • 27 October 2020
  • 4 replies
  • 754 views
Guy_Srinivasan

I’m trying to construct a query dynamically in Python and provide a link to it as part of some output. I’m posting to [url]/api/3.1/queries with body equivalent to

{'model': 'company_investigations', 'view': 'investigations', 'fields': ['a_bunch.of_fields', 'a_table.like_this.one'], 'filters': {'investigations.created_time': '2020-06-01 to 2020-07-01', 'investigations.team': '1234'}, 'query_timezone': 'UTC', 'limit': 500, 'dynamic_fields': [{'dimension': 'a_name_for_it', 'label': 'A Name For It', 'expression': '${investigations.a_number} <= 25', '_kind_hint': 'dimension', '_type_hint': 'yesno'}]}

Getting a 500 “An error has occurred.”

The same thing works if I remove the dynamic_fields kvp.

Does anyone have an example of a body that works while including a custom dimension?

icon

Best answer by izzy 27 October 2020, 23:49

View original

4 replies

Guy_Srinivasan

From the logs: Java::JavaSql::SQLSyntaxErrorException: (conn=112875697) Unknown column 'dimension' in 'field list': INSERT INTO `query` (`view`, `fields`, `filters`, `limit`, `dynamic_fields`, `query_timezone`, `model`, `hash`, `slug`, `created_at`) VALUES *REDACTED*, *REDACTED*, *REDACTED*, '5000000', (((`dimension` = 'a_name_for_it') AND (`label` = 'A Name For It') AND (`expression` = '1') AND (`_kind_hint` = 'dimension') AND (`_type_hint` = 'number'))), 'UTC', ...

izzy
Userlevel 7
Badge +1

Hmm that’s interesting. I’m not 100% sure what you’re doing there, but I just did some tinkering and here is a very barebones query that worked against create_query()

 

{
  "model": "thelook",
  "view": "order_items",
  "fields": [
    "order_items.id",
    "calculation_5"
  ],
  "pivots": [],
  "fill_fields": [],
  "filters": {},
  "filter_expression": "",
  "sorts": [],
  "limit": "500",
  "column_limit": "",
  "total": false,
  "row_total": "",
  "subtotals": [],
  "vis_config": {},
  "filter_config": {},
  "visible_ui_sections": "",
  "dynamic_fields": "[{\"dimension\":\"calculation_5\",\"label\":\"Calculation 1\",\"expression\":\"1 \",\"value_format\":null,\"value_format_name\":null,\"_kind_hint\":\"dimension\",\"_type_hint\":\"number\"}]",
  "query_timezone": "America/Los_Angeles",
  "runtime": 0
}

 

 

I’m sure the answer must lie in the dynamic_fields param:

Mine:

"dynamic_fields": "[{\"dimension\":\"calculation_5\",\"label\":\"Calculation 1\",\"expression\":\"1 \",\"value_format\":null,\"value_format_name\":null,\"_kind_hint\":\"dimension\",\"_type_hint\":\"number\"}]",

Yours:

'dynamic_fields': [{'dimension': 'a_name_for_it', 'label': 'A Name For It', 'expression': '${investigations.a_number} <= 25', '_kind_hint': 'dimension', '_type_hint': 'yesno'}]

Perhaps the URL encoding and escaping? I would give that a try.

Guy_Srinivasan

Answer: we want the dynamic_fields value to be a _string_ which can be parsed as json, rather than being a json object itself.

dynamic_fields = [
    {
    'dimension': 'cheap',
    'label': 'Cheap',
    'expression': '${a_table.dollar_value} <= 25',
    '_kind_hint': 'dimension',
    '_type_hint': 'yesno'
    }
]

query_request['dynamic_fields'] = json.dumps(dynamic_fields)

...

response = self.session.post(url, json=query_request)

This works fine.

izzy
Userlevel 7
Badge +1

Aaah of course, I hadn’t noticed the obvious quotation marks. Nice, glad you got it working.

Reply


Terms of Service