generator.views.lookml_utils
Utils for generating lookml.
1"""Utils for generating lookml.""" 2 3import re 4import tarfile 5import urllib.request 6from collections import defaultdict 7from io import BytesIO 8from pathlib import Path 9from typing import Any, Dict, Iterable, List, Optional, Tuple 10 11import click 12import yaml 13from jinja2 import Environment, FileSystemLoader 14 15GENERATOR_PATH = Path(__file__).parent.parent 16 17BIGQUERY_TYPE_TO_DIMENSION_TYPE = { 18 "BIGNUMERIC": "string", 19 "BOOLEAN": "yesno", 20 "BYTES": "string", 21 "DATE": "time", 22 "DATETIME": "time", 23 "FLOAT": "number", 24 "INTEGER": "number", 25 "NUMERIC": "number", 26 "STRING": "string", 27 "TIME": "time", 28 "TIMESTAMP": "time", 29} 30 31HIDDEN_DIMENSIONS = { 32 ("document_id",), 33 ("client_id",), 34 ("client_info", "client_id"), 35 ("context_id",), 36 ("additional_properties",), 37} 38 39MAP_LAYER_NAMES = { 40 ("country",): "countries", 41 ("metadata", "geo", "country"): "countries", 42} 43 44 45def _get_dimension( 46 path: Tuple[str, ...], field_type: str, mode: str, description: Optional[str] 47) -> Dict[str, Any]: 48 result: Dict[str, Any] = {} 49 result["sql"] = "${TABLE}." + ".".join(path) 50 name = path 51 if ( 52 mode == "REPEATED" 53 or path in HIDDEN_DIMENSIONS 54 or field_type not in BIGQUERY_TYPE_TO_DIMENSION_TYPE 55 ): 56 result["hidden"] = "yes" 57 else: 58 result["type"] = BIGQUERY_TYPE_TO_DIMENSION_TYPE[field_type] 59 60 group_label, group_item_label = None, None 61 if len(path) > 1: 62 group_label = slug_to_title(" ".join(path[:-1])) 63 group_item_label = slug_to_title(path[-1]) 64 if result["type"] == "time": 65 # Remove _{type} suffix from the last path element for dimension group 66 # names For example submission_date and submission_timestamp become 67 # submission, and metadata.header.parsed_date becomes 68 # metadata__header__parsed. This is because the timeframe will add a _{type} 69 # suffix to the individual dimension names. 70 name = *path[:-1], re.sub("_(date|time(stamp)?)$", "", path[-1]) 71 result["timeframes"] = [ 72 "raw", 73 "time", 74 "date", 75 "week", 76 "month", 77 "quarter", 78 "year", 79 ] 80 if field_type == "DATE": 81 result["timeframes"].remove("time") 82 result["convert_tz"] = "no" 83 result["datatype"] = "date" 84 if group_label and group_item_label: 85 # Dimension groups should not be nested, see issue #82 86 result["label"] = f"{group_label}: {group_item_label}" 87 elif len(path) > 1: 88 result["group_label"] = group_label 89 result["group_item_label"] = group_item_label 90 if path in MAP_LAYER_NAMES: 91 result["map_layer_name"] = MAP_LAYER_NAMES[path] 92 result["name"] = "__".join(name) 93 94 if description: 95 result["description"] = description 96 97 return result 98 99 100def _generate_dimensions_helper(schema: List[Any], *prefix: str) -> Iterable[dict]: 101 for field in sorted(schema, key=lambda f: f["name"]): 102 if field["type"] == "RECORD" and not field.get("mode", "") == "REPEATED": 103 yield from _generate_dimensions_helper( 104 field["fields"], *prefix, field["name"] 105 ) 106 else: 107 yield _get_dimension( 108 (*prefix, field["name"]), 109 field["type"], 110 field.get("mode", ""), 111 field.get("description", ""), 112 ) 113 114 115def _generate_dimensions(table: str, dryrun) -> List[Dict[str, Any]]: 116 """Generate dimensions and dimension groups from a bigquery table. 117 118 When schema contains both submission_timestamp and submission_date, only produce 119 a dimension group for submission_timestamp. 120 121 Raise ClickException if schema results in duplicate dimensions. 122 """ 123 dimensions = {} 124 [project, dataset, table] = table.split(".") 125 table_schema = dryrun.create( 126 project=project, 127 dataset=dataset, 128 table=table, 129 ).get_table_schema() 130 131 for dimension in _generate_dimensions_helper(table_schema): 132 name_key = dimension["name"] 133 134 # This prevents `time` dimension groups from overwriting other dimensions below 135 if dimension.get("type") == "time": 136 name_key += "_time" 137 # overwrite duplicate "submission", "end", "start" dimension group, thus picking the 138 # last value sorted by field name, which is submission_timestamp 139 # See also https://github.com/mozilla/lookml-generator/issues/471 140 if name_key in dimensions and not ( 141 dimension.get("type") == "time" 142 and ( 143 dimension["name"] == "submission" 144 or dimension["name"].endswith("end") 145 or dimension["name"].endswith("start") 146 ) 147 ): 148 raise click.ClickException( 149 f"duplicate dimension {name_key!r} for table {table!r}" 150 ) 151 dimensions[name_key] = dimension 152 return list(dimensions.values()) 153 154 155def _generate_dimensions_from_query(query: str, dryrun) -> List[Dict[str, Any]]: 156 """Generate dimensions and dimension groups from a SQL query.""" 157 schema = dryrun.create(sql=query).get_schema() 158 dimensions = {} 159 for dimension in _generate_dimensions_helper(schema or []): 160 name_key = dimension["name"] 161 162 # This prevents `time` dimension groups from overwriting other dimensions below 163 if dimension.get("type") == "time": 164 name_key += "_time" 165 166 # overwrite duplicate "submission", "end", "start" dimension group, thus picking the 167 # last value sorted by field name, which is submission_timestamp 168 # See also https://github.com/mozilla/lookml-generator/issues/471 169 if name_key in dimensions and not ( 170 dimension.get("type") == "time" 171 and ( 172 dimension["name"] == "submission" 173 or dimension["name"].endswith("end") 174 or dimension["name"].endswith("start") 175 ) 176 ): 177 raise click.ClickException(f"duplicate dimension {name_key!r} in query") 178 dimensions[name_key] = dimension 179 return list(dimensions.values()) 180 181 182def _generate_nested_dimension_views( 183 schema: List[dict], view_name: str 184) -> List[Dict[str, Any]]: 185 """ 186 Recursively generate views for nested fields. 187 188 Nested fields are created as views, with dimensions and optionally measures. 189 """ 190 views: List[Dict[str, Any]] = [] 191 for field in sorted(schema, key=lambda f: f["name"]): 192 if field["type"] == "RECORD" and field["name"] != "labeled_counter": 193 # labeled_counter is handled explicitly in glean ping views; hidden for other views 194 if field.get("mode") == "REPEATED": 195 nested_field_view: Dict[str, Any] = { 196 "name": f"{view_name}__{field['name']}" 197 } 198 dimensions = _generate_dimensions_helper(schema=field["fields"]) 199 nested_field_view["dimensions"] = [ 200 d for d in dimensions if not _is_dimension_group(d) 201 ] 202 nested_field_view["dimension_groups"] = [ 203 d for d in dimensions if _is_dimension_group(d) 204 ] 205 views = ( 206 views 207 + [nested_field_view] 208 + _generate_nested_dimension_views( 209 field["fields"], f"{view_name}__{field['name']}" 210 ) 211 ) 212 else: 213 views = views + _generate_nested_dimension_views( 214 field["fields"], f"{view_name}__{field['name']}" 215 ) 216 217 return views 218 219 220def _is_dimension_group(dimension: dict): 221 """Determine if a dimension is actually a dimension group.""" 222 return "timeframes" in dimension or "intervals" in dimension 223 224 225def escape_filter_expr(expr: str) -> str: 226 """Escape filter expression for special Looker chars.""" 227 return re.sub(r'((?:^-)|["_%,^])', r"^\1", expr, count=0) 228 229 230def _is_nested_dimension(dimension: dict): 231 return ( 232 "hidden" in dimension 233 and dimension["hidden"] 234 and "nested" in dimension 235 and dimension["nested"] 236 ) 237 238 239def render_template(filename, template_folder, **kwargs) -> str: 240 """Render a given template using Jinja.""" 241 env = Environment( 242 loader=FileSystemLoader(GENERATOR_PATH / f"{template_folder}/templates") 243 ) 244 template = env.get_template(filename) 245 rendered = template.render(**kwargs) 246 return rendered 247 248 249def slug_to_title(slug): 250 """Convert a slug to title case.""" 251 return slug.replace("_", " ").title() 252 253 254# Map from view to qualified references {dataset: {view: [[project, dataset, table],]}} 255BQViewReferenceMap = Dict[str, Dict[str, List[List[str]]]] 256 257 258def get_bigquery_view_reference_map( 259 generated_sql_uri: str, 260) -> BQViewReferenceMap: 261 """Get a mapping from BigQuery datasets to views with references.""" 262 with urllib.request.urlopen(generated_sql_uri) as f: 263 tarbytes = BytesIO(f.read()) 264 views: BQViewReferenceMap = defaultdict(dict) 265 with tarfile.open(fileobj=tarbytes, mode="r:gz") as tar: 266 for tarinfo in tar: 267 if tarinfo.name.endswith("/metadata.yaml"): 268 metadata = yaml.safe_load(tar.extractfile(tarinfo.name)) # type: ignore 269 references = metadata.get("references", {}) 270 if "view.sql" not in references: 271 continue 272 *_, project, dataset_id, view_id, _ = tarinfo.name.split("/") 273 if project == "moz-fx-data-shared-prod": 274 views[dataset_id][view_id] = [ 275 ref.split(".") for ref in references["view.sql"] 276 ] 277 return views
GENERATOR_PATH =
PosixPath('/root/project/generator')
BIGQUERY_TYPE_TO_DIMENSION_TYPE =
{'BIGNUMERIC': 'string', 'BOOLEAN': 'yesno', 'BYTES': 'string', 'DATE': 'time', 'DATETIME': 'time', 'FLOAT': 'number', 'INTEGER': 'number', 'NUMERIC': 'number', 'STRING': 'string', 'TIME': 'time', 'TIMESTAMP': 'time'}
HIDDEN_DIMENSIONS =
{('document_id',), ('additional_properties',), ('client_id',), ('context_id',), ('client_info', 'client_id')}
MAP_LAYER_NAMES =
{('country',): 'countries', ('metadata', 'geo', 'country'): 'countries'}
def
escape_filter_expr(expr: str) -> str:
226def escape_filter_expr(expr: str) -> str: 227 """Escape filter expression for special Looker chars.""" 228 return re.sub(r'((?:^-)|["_%,^])', r"^\1", expr, count=0)
Escape filter expression for special Looker chars.
def
render_template(filename, template_folder, **kwargs) -> str:
240def render_template(filename, template_folder, **kwargs) -> str: 241 """Render a given template using Jinja.""" 242 env = Environment( 243 loader=FileSystemLoader(GENERATOR_PATH / f"{template_folder}/templates") 244 ) 245 template = env.get_template(filename) 246 rendered = template.render(**kwargs) 247 return rendered
Render a given template using Jinja.
def
slug_to_title(slug):
250def slug_to_title(slug): 251 """Convert a slug to title case.""" 252 return slug.replace("_", " ").title()
Convert a slug to title case.
BQViewReferenceMap =
typing.Dict[str, typing.Dict[str, typing.List[typing.List[str]]]]
def
get_bigquery_view_reference_map(generated_sql_uri: str) -> Dict[str, Dict[str, List[List[str]]]]:
259def get_bigquery_view_reference_map( 260 generated_sql_uri: str, 261) -> BQViewReferenceMap: 262 """Get a mapping from BigQuery datasets to views with references.""" 263 with urllib.request.urlopen(generated_sql_uri) as f: 264 tarbytes = BytesIO(f.read()) 265 views: BQViewReferenceMap = defaultdict(dict) 266 with tarfile.open(fileobj=tarbytes, mode="r:gz") as tar: 267 for tarinfo in tar: 268 if tarinfo.name.endswith("/metadata.yaml"): 269 metadata = yaml.safe_load(tar.extractfile(tarinfo.name)) # type: ignore 270 references = metadata.get("references", {}) 271 if "view.sql" not in references: 272 continue 273 *_, project, dataset_id, view_id, _ = tarinfo.name.split("/") 274 if project == "moz-fx-data-shared-prod": 275 views[dataset_id][view_id] = [ 276 ref.split(".") for ref in references["view.sql"] 277 ] 278 return views
Get a mapping from BigQuery datasets to views with references.