def test_step2(self): for f in self.proj.files('simple'): print(f.path) # print(self.proj.file('simple/tests.view.lkml')) tests = self.proj.file('simple/tests.view.lkml') tests + lookml.View('shell') tests.views.test1.bar.sql = 'WHOA' # tests.write() self.proj.update(tests) x = lookml.View('hello_world') x + 'dimension: id {}' xf = lookml.File(x) self.proj.put(xf) self.proj.delete(xf) self.proj.deploy()
def test_cool(self): v = lookml.View('cool') # v.properties.addProperty('derived_table',{'sql':'select 1'}) v.derived_table = {'sql':'select 1'} # v.derived_table = {} # v.derived_table.sql = "select 2" # v.derived_table.datagroup_trigger = 'etl_24_hour' print(v)
def test_create_view_from_info_schema(self): def column_to_dimension(col): if col['LOOKER_TYPE'] == 'time': tmpDim = lookml.DimensionGroup( lookml.lookCase(col['COLUMN_NAME']) ) else: tmpDim = lookml.Dimension(lookml.lookCase(col['COLUMN_NAME'])) tmpDim.setType(col['LOOKER_TYPE']) tmpDim.sql = "${TABLE}." + col['COLUMN_NAME'] return tmpDim sdk = init31("api.ini") sql = """ SELECT t.TABLE_NAME ,t.TABLE_SCHEMA ,t.COLUMN_NAME ,t.DATA_TYPE , CASE WHEN t.DATA_TYPE IN ('TIMESTAMP_LTZ') THEN 'time' WHEN t.DATA_TYPE IN ('FLOAT','NUMBER') THEN 'number' ELSE 'string' END as "LOOKER_TYPE" FROM information_schema.COLUMNS as t WHERE 1=1 AND t.table_name = 'ORDER_ITEMS' AND t.table_schema = 'PUBLIC' LIMIT 100 """ query_config = models.WriteSqlQueryCreate(sql=sql, connection_id="snowlooker") query = sdk.create_sql_query(query_config) response = sdk.run_sql_query(slug=query.slug, result_format="json") response_json = json.loads(response) order_items = lookml.View('order_items_3') order_items.sql_table_name = 'PUBLIC.ORDER_ITEMS' for column in response_json: order_items + column_to_dimension(column) order_items.sumAllNumDimensions() order_items.addCount() proj = lookml.Project( repo= config['github']['repo'] ,access_token=config['github']['access_token'] ,looker_host="https://profservices.dev.looker.com/" ,looker_project_name="russ_sanbox" ) myNewFile = lookml.File(order_items) proj.put(myNewFile) proj.deploy()
def test_adding_property(self): v = lookml.View('test') v + ''' derived_table: { explore_source: order_items { column: order_id {field: order_items.order_id_no_actions } column: items_in_order { field: order_items.count } column: order_amount { field: order_items.total_sale_price } column: order_cost { field: inventory_items.total_cost } column: user_id {field: order_items.user_id } column: created_at {field: order_items.created_raw} column: order_gross_margin {field: order_items.total_gross_margin} derived_column: order_sequence_number { sql: RANK() OVER (PARTITION BY user_id ORDER BY created_at) ;; } } datagroup_trigger: ecommerce_etl } ''' v + 'dimension: id {}' v.id + 'sql: ${TABLE}.id ;;' for item in ('a', 'b', 'c'): v + f''' dimension: {item}_id {{ sql: {v.id.__refs__} + {item} ;; }}''' v + f'''measure: sum_of_{item} {{ type: sum sql: ${{{item}_id}};; }} ''' for f in v.measures(): if f.type.value == 'sum': f.addTag('my function is to add') ex = lookml.Explore(v.name) ex + '''join: test_2 { from: test type: left_outer relationship: one_to_many sql_on: ${testid} = ${test_2.id};; } ''' ex.test_2 + 'sql_on: foo ;;' F = lookml.File(ex) F + v print(F)
def test_github_loop(self): proj = lookml.Project( repo= config['github']['repo'] ,access_token=config['github']['access_token'] ,looker_host="https://profservices.dev.looker.com/" ,looker_project_name="russ_sanbox" ) vf = proj.file('simple/tests.view.lkml') mf = proj.file('simple/test1.model.lkml') proj.update(vf) proj.update(mf) myNewView = lookml.View('great_test2').addDimension('id').addDimension('count_of_total') myNewView.id.sql = "${TABLE}.`id`" myNewView.id.setType('string') myNewFile = lookml.File(myNewView) proj.put(myNewFile) proj.deploy()
def test_step1(self): ## Do Work ### myNewView = lookml.View('great_test55').addDimension('id').addDimension('count_of_total') # myNewView = lookml.View('great_test55') + 'id' + 'count_of_total' myNewView.id.sql = "${TABLE}.`id`" myNewView.id.setType('string') myNewFile = lookml.File(myNewView) myNewFile.setFolder(self.proj.gitControllerSession.absoluteOutputPath) myNewFile.write() # myNewFile = lookml.File(order_items) # proj.put(myNewFile) # proj.deploy() myOldFile = lookml.File('.tmp/russ_sanbox/02_users.view.lkml') myOldFile.views.users.hello.setType("number") myOldFile.write() ## Deploy ### self.proj.gitControllerSession.add().commit().pushRemote()
def test_add_micro_units(self): testView = lookml.View('testView') testView + 'id' testView + 'dimension: success {}' testView + ''' derived_table: { explore_source: order_items { column: order_id {field: order_items.order_id_no_actions } column: items_in_order { field: order_items.count } column: order_amount { field: order_items.total_sale_price } column: order_cost { field: inventory_items.total_cost } column: user_id {field: order_items.user_id } column: created_at {field: order_items.created_raw} column: order_gross_margin {field: order_items.total_gross_margin} derived_column: order_sequence_number { sql: RANK() OVER (PARTITION BY user_id ORDER BY created_at) ;; } } datagroup_trigger: ecommerce_etl } ''' print(testView)
def create_view(config, file_name): # create a view and feed in the conifg file & file name vw = lookml.View(file_name) # create a variable called vw that creates a vew from each file name tmpDim = lookml.DimensionGroup('event') # tmpDim.sql = "${TABLE}." + 'event' vw + tmpDim vw.addCount() vw.setProperty("sql_table_name","LOOKER_SCRATCH.zz_" + file_name) vw.setProperty("view_label", config.get('label', file_name)) fields = config["fields"].split(",") for field in fields: # get a list of field objects, and split them apart by comma vw + field if (field[-7:] == '_number'): vw[field].type = "number" elif (field[-5:] == '_date'): vw[field].type = "date" if (field[-3:]) == '_id': vw.addCountDistinct(vw[field]) print(vw) # print just shows a string representing what's going on inside the computer return(vw) # return is how a function gives back a value
def test_extends_bug(self): cool = lookml.View('cool') cool + 'extends: [wut]' print(cool)
for row in column_names: tmp_field = f', MAX(CASE WHEN EAV_data.column_name = \'{row}\' THEN EAV_data.column_value ELSE null END) AS \"{row}\"' select_clause.append(tmp_field) select_clause = '\n'.join(select_clause) sql = f''' SELECT EAV_data.schema_id , EAV_data.entity_id {select_clause} FROM {pdthash} as EAV_data GROUP BY 1,2 ''' # pp.pprint(view_data) tmp_view = l.View('example_derived_volatile', derived_table={ 'sql': sql }).setFolder(OUTPUT_DIR) tmp_view + 'schema_id' + 'entity_id' tmp_view.setExtensionRequired() tmp_view.setMessage('This is an autogenerated view created by python at ' + str(dt.datetime.now().replace(microsecond=0).isoformat())) ### # Iterating over the api response and adding syntactically correct Dimensions with appropriate access grants to prevent multi tenant data views for data in view_data: entity_id = data['entity_id'] if data['data_type'] == 'numerical' or data['data_type'] == 'decimal': dim = l.Dimension(data['column_name']) dim.setType('number') # dim.hide() dim.set_Field_Level_Permission(f'eid_{entity_id}') tmp_view + dim
def add_column(column, column_fqn, view_stack, views): view_fqn = view_stack[-1] view_id = snake_case('__'.join(view_fqn)) depth = len(view_fqn) label = ' '.join(column_fqn[depth:]).replace('_', ' ') table_view = views[view_id].view tail_idx = -(len(column_fqn) - len(view_fqn)) tail_fqn = column_fqn[tail_idx:] name = snake_case('_'.join(tail_fqn)) dotted_key = '.'.join(tail_fqn) sql = f'${{TABLE}}.{dotted_key}' if len(column_fqn) - len(view_fqn) >= 2: group_label = ' '.join(column_fqn[depth:-1]).replace('_', ' ').title() group_item_label = column_fqn[-1].replace('_', ' ').title() else: group_label = '' group_item_label = '' if column['data_type'].startswith('ARRAY'): dim = lookml.Dimension() dim.setProperty('sql', sql) dim.setName(name) dim.hide() table_view + dim view_name = snake_case('__'.join(column_fqn)) join_column = '.'.join([view_id, name]) view_stack.append(column_fqn) view = lookml.View(view_name).setFolder(OUTPUT_DIR) view.setMessage( f'This is an autogenerated view created by python {column_fqn}') views[view_name] = NamedView(column_fqn, view, join_column) elif column['data_type'] in ['INT64', 'FLOAT64', 'NUMERIC']: dim = lookml.Dimension() dim.setProperty('sql', sql) dim.setName(name) dim.setType('number') if group_label: dim.setProperty('group_label', group_label) dim.setProperty('group_item_label', group_item_label) table_view + dim elif column['data_type'] == 'STRING': dim = lookml.Dimension() dim.setProperty('sql', sql) dim.setName(name) dim.setType('string') if group_label: dim.setProperty('group_label', group_label) dim.setProperty('group_item_label', group_item_label) table_view + dim elif column['data_type'] == 'DATE': dim = lookml.DimensionGroup() dim.setProperty('datatype', 'date') dim.setProperty('convert_tz', 'no') dim.setProperty('sql', sql) if name[-5:] == '_date': name = name[0:-5] dim.setName(name) dim.setType('time') table_view + dim elif column['data_type'] == 'TIME': dim = lookml.Dimension() dim.setProperty('sql', sql) dim.setName(name) dim.setType('string') table_view + dim elif column['data_type'] == 'BOOL': dim = lookml.Dimension() dim.setProperty('sql', sql) dim.setName(name) dim.setType('yesno') if group_label: dim.setProperty('group_label', group_label) dim.setProperty('group_item_label', group_item_label) table_view + dim
def generate_explore_from_data(table_name, sql_table_name, table_definition, partition=None, output_dir=OUTPUT_DIR): # Initialise base table view view_name = snake_case(table_name) table_view = lookml.View(view_name).setFolder(output_dir) table_view.setSqlTableName(sql_table_name=sql_table_name) table_view.setMessage( 'Base table view for nested BigQuery. Autogenerated using lookml (pip install lookml)' ) views = {view_name: NamedView([table_name], table_view, None)} # Iterate through columns, adding to each to correct view as we go view_stack = [[table_name]] for column in table_definition: fqn = [table_name] + column['field_path'].split('.') if view_stack[-1] != fqn[:len(view_stack[-1])]: view_stack.pop() add_column(column, fqn, view_stack, views) # Write the individual views to disk for identifier, named_view in views.items(): named_view.view.write() # Write the explore file: base view explore_name = view_name explore_string = f'explore: {explore_name} {{' if partition: explore_string += f''' always_filter: {{ filters: {{ field: {partition} value: "90 days" }} }} ''' # Write the explore file: other views for identifier, named_view in views.items(): if identifier != view_name: explore_string += f''' join: {named_view.view.identifier} {{ sql: LEFT JOIN UNNEST(${{{named_view.join_column}}}) as {named_view.view.identifier} ;; relationship: one_to_many }} ''' # Write the explore file: close explore object explore_string += ''' } ''' explore_file_name = f'{OUTPUT_DIR}/{views[view_name].view.identifier}.explore.lkml' with open(explore_file_name, 'w') as explore_file: explore_file.write(explore_string) # Concat the files output = open(OUTPUT_DIR + "/lkml/" + views[view_name].view.fileName, "wb") shutil.copyfileobj(open(explore_file_name, "rb"), output) for identifier, named_view in views.items(): shutil.copyfileobj( open(OUTPUT_DIR + "/" + named_view.view.fileName, "rb"), output) output.close()
import lookml as l ############################ # Example 1. Setting up the Looker View with 2 dimensions, id, value and dimnension group, created_at ############################ order_items = l.View('order_items') order_items + 'id' + 'value' + l.DimensionGroup('created_at') order_items.setSqlTableName('public.order_items') print(order_items)
def view(name, file): proj = lookml.Project(path='.') f = proj.file(file) f + lookml.View(f'view: {name} {{}}') f.write()
looker_project_name=args.project) def number_of_fields(): viewFile = proj.file('custom_events.view.lkml') customEvents = viewFile.views.custom_events return len(customEvents) try: fields_start = number_of_fields() except: fields_start = 0 #create a new LookML view customEventsView = lookml.View('custom_events') customEventsView.setExtensionRequired() customEventsView.setMessage( 'README: This view is auto-generated by pyLookML (see generate_lookml.py)') #For each result create a measure for row in resultsList: eventAction = row['hits_eventInfo.eventAction'] eventCategory = row['hits_eventInfo.eventCategory'] if eventAction: measure_name = lookml.snakeCase(eventAction.replace(' ', '_')) measure = lookml.Measure(measure_name) measure.setLabel(eventAction) measure.setProperty('group_label', eventCategory) measure.setType('count') measure.setProperty('filters', [{
def test_join_back_an_ndt(self): v = lookml.View('order_items') v + ''' sql_table_name: public.order_items ;; dimension: id { primary_key: yes } dimension: state {} dimension: sale_price {} parameter: {dynamic_dim_selector} { type: unquoted # suggestions: ["Brand","Category","Department"] allowed_value: { label: "Category" value: "Category" } allowed_value: { label: "Brand" value: "Brand" } allowed_value: { label: "Department" value: "Department" } allowed_value: { label: "State" value: "State" } } dimension: user_id {} dimension: inventory_item_id { sql: ${TABLE}.inventory_item_id ;; } dimension: new_dimension { type: string sql: {% if order_items.dynamic_dim_selector._parameter_value == 'Brand' %} ${products.brand} {% elsif order_items.dynamic_dim_selector._parameter_value == 'Category' %} ${products.category} {% elsif order_items.dynamic_dim_selector._parameter_value == 'Department' %} ${products.department} {% elsif order_items.dynamic_dim_selector._parameter_value == 'State' %} ${users.state} {% else %} 'N/A' {% endif %} ;; } measure: total_sale_price { type: sum sql: ${sale_price} ;; } ''' ex = lookml.Explore(v.name) agg = lookml.View('agg') agg + ''' derived_table: { explore_source: order_items { column: new_dimension {field: order_items.new_dimension} column: total_sale_price {field: order_items.total_sale_price} derived_column: rank { sql: ROW_NUMBER() OVER (ORDER BY total_sale_price DESC) ;; } # bind_all_filters: yes bind_filters: { from_field: order_items.{dynamic_dim_selector} to_field: order_items.{dynamic_dim_selector} } # bind_filters: { # from_field: order_items.created_date # to_field: order_items.created_date # } } } dimension: new_dimension { sql: ${TABLE}.new_dimension ;; } dimension: rank { type: number hidden: yes } filter: tail_threshold { type: number hidden: yes } dimension: stacked_rank { type: string sql: CASE WHEN ${rank} < 10 then '0' || ${rank} || ') '|| ${new_dimension} ELSE ${rank} || ') ' || ${new_dimension} END ;; } dimension: ranked_brand_with_tail { type: string sql: CASE WHEN {% condition tail_threshold %} ${rank} {% endcondition %} THEN ${stacked_rank} ELSE 'x) Other' END ;; } dimension: total_sale_price { value_format: "$#,##0.00" type: number } ''' ex + ''' join: inventory_items { type: left_outer relationship: one_to_many sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;; } join: products { type: left_outer sql_on: ${inventory_items.product_id} = ${products.id} ;; relationship: many_to_one } join: users { type: left_outer sql_on: ${order_items.user_id} = ${users.id} ;; relationship: many_to_one } join: agg { type: left_outer relationship: many_to_one sql_on: ${order_items.new_dimension} = ${agg.new_dimension};; } ''' myModel = lookml.File(ex) myModel + v myModel + agg myModel.properties.addProperty('connection', 'snowlooker') myModel.properties.addProperty('include', 'views/*.lkml') myModel.name = 'core2.model.lkml' proj = lookml.Project( repo= 'russlooker/oi' ,access_token=config['github']['access_token'] ,looker_host="https://profservices.dev.looker.com/" ,looker_project_name="test_pylookml" ) proj.put(myModel) proj.deploy()
def apply_top_n(project, view_file, view, rank_by_dims, rank_by_meas, model_file, explore, agg_view='rank_ndt', dynamic_dim_name='dynamic_dim', dynamic_dim_selector='dynamic_dim_selector'): #### SETUP #### p = project mf = p[model_file] vf = p[view_file] v = vf['views'][view] e = mf['explores'][explore] #### DO WORK #### #Add the parameter to the initial view file dynamic_dim_sql = '' i = 0 for key,val in rank_by_dims.items(): if i == 0: dynamic_dim_sql = f"{{% if {v.name}.{dynamic_dim_selector}._parameter_value == '{key}' %}} {val}" else: dynamic_dim_sql = dynamic_dim_sql + '\n' + f"{{% elsif {v.name}.{dynamic_dim_selector}._parameter_value == '{key}' %}} {val}" i = 1 + 1 dynamic_dim_sql = dynamic_dim_sql + f""" {{% else %}} 'N/A' {{% endif %}} """ allowed_values = '' for key in rank_by_dims.keys(): allowed_values = allowed_values + f'allowed_value: {{ label: "{key}" value: "{key}"}}' v + f''' parameter: {dynamic_dim_selector} {{ type: unquoted {allowed_values} }}''' v + f''' dimension: {dynamic_dim_name} {{ type: string hidden: yes sql: {dynamic_dim_sql};; }} ''' #create the aggregate ndt agg = lookml.View(agg_view) agg + f''' derived_table: {{ explore_source: {e.name} {{ column: {dynamic_dim_name} {{field: {v.name}.{dynamic_dim_name}}} column: {rank_by_meas} {{field: {v.name}.{rank_by_meas}}} derived_column: rank {{ sql: ROW_NUMBER() OVER (ORDER BY {rank_by_meas} DESC) ;; }} # bind_all_filters: yes bind_filters: {{ from_field: {v.name}.{dynamic_dim_selector} to_field: {v.name}.{dynamic_dim_selector} }} }} }} dimension: {dynamic_dim_name} {{ sql: ${{TABLE}}.{dynamic_dim_name} ;; hidden: yes }} dimension: rank {{ type: number hidden: yes }} filter: tail_threshold {{ type: number hidden: yes }} dimension: stacked_rank {{ type: string sql: CASE WHEN ${{rank}} < 10 then '0' || ${{rank}} || ') '|| ${{{dynamic_dim_name}}} ELSE ${{rank}} || ') ' || ${{{dynamic_dim_name}}} END ;; }} dimension: ranked_by_with_tail {{ type: string sql: CASE WHEN {{% condition tail_threshold %}} ${{rank}} {{% endcondition %}} THEN ${{stacked_rank}} ELSE 'x) Other' END ;; }} ''' #add our new aggregate view to the view file vf + agg #join in our aggregate table to the explore e + f''' join: {agg.name} {{ type: left_outer relationship: many_to_one sql_on: ${{{v.name}.{dynamic_dim_name}}} = ${{{agg.name}.{dynamic_dim_name}}};; }} ''' #### SAVE #### p.put(vf) p.put(mf) p.deploy()
''' ### # API method to run above sql aginst the inputted connection view_data = connection.run_sql(sql, 'thelook_events_redshift') table_name = list(set([d['table_name'] for d in view_data])) #### # to be removed, purely for Join Talk test = ['order_items'] ### # Create View files for each table name for table_name in test: tmp_view = l.View(table_name + '_base').setFolder( OUTPUT_DIR).setExtensionRequired().setMessage( 'This is an autogenerated view created by python at ' + str(dt.datetime.now().replace(microsecond=0).isoformat())) tmp_view.setSqlTableName(f'public.{table_name}') for table in view_data: if table['table_name'] == table_name: if table['data_type'] == 'integer' or table[ 'data_type'] == 'double precision': dim = l.Dimension(table['column_name']) dim.hide() dim.setType('number') if table['column_name'] == 'id': dim.setPrimaryKey() tmp_view + dim else: tmp_view + dim