def run(r): git = Github(os.environ['GITHUB_PERSONAL_ACCESS_TOKEN']) # log into github using access token repo = git.get_repo(os.environ['GITHUB_REPOSITORY']) # access twitter repo def create_explore(file_name): ex = lookml.Explore(file_name) ex.setProperty('always_filter','{filters: {\n field: event_date \n value: "'+ config.get('start_date') + ' to ' + config.get('end_date')+'"}}') print(ex) return(ex) 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 proj = lookml.Project( repo="bryan-at-looker/twitter-tests-lookml" ,access_token=os.environ['GITHUB_PERSONAL_ACCESS_TOKEN'] ,looker_host="https://marketplace.dev.looker.com/" ,looker_project_name="twitter-experimentation" ) sdk = looker_sdk.init31() # instantiating the sdk contents = repo.get_contents("") # getting metadata for content in contents: # getting the actual file data if (content.path[0]!='.'): # if the first character of the file isn't a "." new_file_name = content.path.replace('.json', '') file_contents = repo.get_contents(content.path) config = json.loads(file_contents.decoded_content) view = create_view(config, new_file_name) # print (content.path) explore = create_explore(new_file_name) fl = lookml.File(view) fl + explore fl.path = 'autogenerated/' + fl.path proj.put(fl) proj.deploy() insert_sql = "INSERT INTO LOOKER_SCRATCH.z_all_tests SELECT '%s', '%s', '%s'::date, '%s'::date, '%s'" % (new_file_name, config['label'], config['start_date'], config['end_date'], config['fields']) sql_query = sdk.create_sql_query(body=looker_sdk.models.SqlQueryCreate(model_name='twitter-experimentation', sql=insert_sql)) sdk.run_sql_query(slug=sql_query.slug, result_format="json")
def setUp(self): self.proj = lookml.Project( # repo= config['github']['repo'] # ,access_token=config['github']['access_token'] git_url='[email protected]:llooker/russ_sandbox.git' ,looker_host="https://profservices.dev.looker.com/" ,looker_project_name="russ_sanbox" )
def test_project_level_functions(self): self.proj = lookml.Project( # repo= config['github']['repo'] # ,access_token=config['github']['access_token'] git_url='[email protected]:llooker/russ_sandbox.git' ,looker_host="https://dat.dev.looker.com/" ,looker_project_name="pylookml" ) self.proj.buildIndex()
def origin(ctx, use, github_repo_name): proj_local = lookml.Project(path='.') proj_remote = lookml.ProjectGithub( repo=github_repo_name, access_token=ctx.conf[use]['access_token']) print(proj_remote._git_connection) for f in proj_local.files(): proj_remote.new_file(f.path) f2 = proj_remote[f.path] for v in f['views']: f2 + str(v) f2.write()
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_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_eav_unnester(self): sdk = init31("api.ini") sql_for_fields = f""" SELECT cpf.org_id ,cpf.value ,cpf.datatype ,cpf.field_name as "FIELD_NAME" , CASE WHEN cpf.datatype IN ('TIMESTAMP_LTZ') THEN 'time' WHEN cpf.datatype IN ('FLOAT','NUMBER', 'int') THEN 'number' ELSE 'string' END as "LOOKER_TYPE" FROM -- public.custom_profile_fields as cpf ( SELECT 1 as user_id, 8 as org_id, 'c_donation_amount' as field_name, '40' as value, 'int' as datatype UNION ALL SELECT 1, 8, 'c_highest_achievement', 'gold badge', 'varchar' UNION ALL SELECT 2, 101, 'c_highest_achievement', 'silver badge', 'varchar' UNION ALL SELECT 2, 101, 'c_monthly_contribution', '300', 'int' UNION ALL SELECT 3, 101, 'c_highest_achievement', 'bronze badge', 'varchar' UNION ALL SELECT 3, 101, 'c_monthly_contribution', '350', 'int' UNION ALL SELECT 4, 101, 'c_monthly_contribution', '350', 'int' UNION ALL SELECT 4, 101, 'age', '32', 'int' UNION ALL SELECT 5, 102, 'c_monthly_contribution', '100', 'int' ) as cpf WHERE 1=1 GROUP BY 1,2,3,4,5 LIMIT 100 """ query_config = models.WriteSqlQueryCreate(sql=sql_for_fields, connection_id="snowlooker") query = sdk.create_sql_query(query_config) response = json.loads(sdk.run_sql_query(slug=query.slug, result_format="json")) 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" ,branch='dev-russell-garner-b7gj' ) modelFile = proj['eav_example/eav.model.lkml'] eavSource = modelFile['views']['eav_source'] flatteningNDT = modelFile['views']['usr_profile'] #Step 0) Ensure there is a hidden explore to expose the eav_souce for NDT modelFile + f''' explore: _eav_flattener {{ from: {eavSource.name} hidden: yes }} ''' #Begin the derived table, will be added to as we loop through the fields drivedtableString = f''' derived_table: {{ explore_source: _eav_flattener {{ column: user_id {{ field: _eav_flattener.user_id }} column: org_id {{ field: _eav_flattener.org_id }} ''' #Set up a list to track the unique org ids and column names orgIds, columns = [], [] for column in response: dimName = lookml.lookCase(column['FIELD_NAME']) orgIds.append(column['ORG_ID']) columns.append(dimName) #Step 1) Add flattening measure to the EAV source table eavSource + f''' measure: {dimName} {{ type: max sql: CASE WHEN ${{field_name}} = '{column['FIELD_NAME']}' THEN ${{value}} ELSE NULL END;; }} ''' #Step 2) Add to the NDT fields flatteningNDT + f''' dimension: {dimName}_org_{column['ORG_ID']} {{ label: "{dimName}" type: {column['LOOKER_TYPE']} sql: ${{TABLE}}.{dimName} ;; required_access_grants: [org_{column['ORG_ID']}] }} ''' if column['LOOKER_TYPE'] == "number": flatteningNDT + f''' measure: {dimName}_total_org_{column['ORG_ID']} {{ label: "{dimName}_total" type: sum sql: ${{{dimName}_org_{column['ORG_ID']}}} ;; required_access_grants: [org_{column['ORG_ID']}] }} ''' #Finalize / add the completed derived table string to the NDT: for col in set(columns): drivedtableString += f' column: {col} {{ field: _eav_flattener.{col} }}' drivedtableString += '}}' #Create the access grants for each org accessGrants = '' for org in set(orgIds): accessGrants += f''' access_grant: org_{org} {{ user_attribute: org_id allowed_values: [ "{org}" ] }} ''' #Finish by adding the derived table to the flatteningNDT + drivedtableString modelFile + accessGrants proj.put(modelFile)
def test_topN(self): 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() apply_top_n( lookml.Project(**config['project1']) ,'order_items.view.lkml' #ordinarily should be my view file ,'order_items' ,{'Brand':'${products.brand}','Category':'${products.category}','State':'${users.state}'} ,'total_sale_price' ,'order_items.model.lkml' ,'order_items' )
def test_one_line_access_github(self): print( lookml.Project(**config['project1'])['order_items.view.lkml']['views']['order_items']['id'].primary_key.value # lookml.Project(**config['project1']).file('order_items.view.lkml').views.order_items.id.primary_key.value )
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 routine(access_token, looker_host, api_client, api_secret, model_name, branch): looker_host = url(looker_host) sdk = init_looker_sdk(looker_host.with_port_if_exists(), api_client, api_secret) frequent_queries = sdk.run_inline_query( result_format='json', body={ "model": "system__activity", "view": "history", "fields": [ "query.model", "query.view", "query.formatted_fields", "query.filters", "query.slug", "history.query_run_count", "history.max_runtime", "history.average_runtime" ], "filters": { # "query.model": f"-NULL,-\"system__activity\",{model_name}" "query.model": f"{model_name}" }, "sorts": ["history.query_run_count desc"], "limit": "100" }) frequent_queries = json.loads(frequent_queries) click.echo("working on branch: " + branch) #look up the model attributes model = sdk.lookml_model(model_name) #look up the project attributes looker_project = sdk.project(model.project_name) if looker_project.git_service_name == 'github': git_search = re.search( r'[email protected]:([a-zA-Z0-9_]{1,100}\/[a-zA-Z_0-9]{1,100})\.git', looker_project.git_remote_url) if git_search: repo = git_search.group(1) pylookml_project = lookml.Project(repo=repo, access_token=access_token, looker_host=looker_host.with_no_port() + '/', looker_project_name=looker_project.name, branch=branch) if pylookml_project._exists(f'pylookml/{model_name}_aggs.view.lkml'): # click.echo('passed exists check') f = pylookml_project.file(f'pylookml/{model_name}_aggs.view.lkml') # click.echo(f.sha) else: f = pylookml_project.new_file(f'pylookml/{model_name}_aggs.view.lkml') field_type_index = dict() #P1: we can't assume the file path, what if in subfolder? pylookml_model = pylookml_project[model_name + '.model.lkml'] pylookml_model + f'includes: "pylookml/{model_name}_aggs.view"' #P1: we can't assume all explores are in the model file # lookml_model_explore has the filesystem location { # "source_file": "dbs.model.lkml" # } #P1: doesn't handle being run twice well with click.progressbar(frequent_queries) as fq: for query in fq: do = 0 if looker_project.name not in field_type_index.keys(): do = 1 field_type_index[looker_project.name] = {} if query["query.model"] not in field_type_index[ looker_project.name].keys(): do = 1 field_type_index[looker_project.name][ query["query.model"]] = {} if query["query.view"] not in field_type_index[ looker_project.name][query["query.model"]].keys(): do = 1 field_type_index[looker_project.name][query["query.model"]][ query["query.view"]] = { 'dimensions': [], 'measures': [] } if do: metadata = sdk.lookml_model_explore(query["query.model"], query["query.view"], fields='fields') for dimension in metadata.fields.dimensions: field_type_index[looker_project.name][ query["query.model"]][ query["query.view"]]['dimensions'].append( dimension.name) for measure in metadata.fields.measures: field_type_index[looker_project.name][ query["query.model"]][ query["query.view"]]['measures'].append( measure.name) try: #P1 we don't need the explore technically since we're writing elsewhere # pylookml_explore = pylookml_model.explores[query["query.view"]] # {query["query.slug"]} # pylookml_explore + f''' # click.echo(f'{query["query.view"]}') explore_source_file_path = sdk.lookml_model_explore( lookml_model_name=query["query.model"], explore_name=query["query.view"], fields='source_file') explore_source_file = pylookml_project.file( explore_source_file_path.source_file) pylookml_source_explore = explore_source_file.explores[ query["query.view"]] if 'persist_for' in pylookml_source_explore: materialization = pylookml_source_explore.persist_for elif 'persist_with' in pylookml_source_explore: materialization = 'datagroup_trigger: ' + pylookml_source_explore.persist_with.value elif 'persist_for' in explore_source_file: materialization = explore_source_file.persist_for elif 'persist_with' in explore_source_file: materialization = 'datagroup_trigger: ' + explore_source_file.persist_with.value else: materialization = 'persist_for: "24 hours"' f + 'include: "/**/*.model"' if f'+{query["query.view"]}' not in f.explores: f + f'''explore: +{query["query.view"]} {{ }}''' f.explores['+' + query["query.view"]] + f''' aggregate_table: auto_pylookml_{query["query.slug"]} {{ query: {{ dimensions: [] measures: [] description: "{looker_host.with_no_port()}/x/{query["query.slug"]}" filters: [] }} materialization: {{ {materialization} }} }} ''' fields = json.loads(query["query.formatted_fields"]) for field in fields: if field in field_type_index[looker_project.name][query[ "query.model"]][query["query.view"]]["dimensions"]: f.explores['+' + query["query.view"]].aggregate_table[ "auto_pylookml_" + query["query.slug"]].query.dimensions + field if field in field_type_index[looker_project.name][query[ "query.model"]][query["query.view"]]["measures"]: f.explores['+' + query["query.view"]].aggregate_table[ "auto_pylookml_" + query["query.slug"]].query.measures + field try: if query["query.filters"]: filters = json.loads(query["query.filters"]) click.echo(query["query.filters"] + ' | ' + filters) except: pass # click.echo(f'failure with {query["query.filters"]}') #P1: aggregate tables can't take paramter fields. Need to ensure each filter is not a param if filters: # f.explores['+'+query["query.view"]].aggregate_table["auto_pylookml_"+query["query.slug"]].query.filters + {k:v[1:][:-1] for k,v in filters.items()} f.explores['+' + query["query.view"]].aggregate_table[ "auto_pylookml_" + query["query.slug"]].query.filters + { k: v for k, v in filters.items() } # pylookml_model.write() # pylookml_project.put(pylookml_model) except: e = sys.exc_info()[0] click.echo(f'Unexpected error: {e}') f.write()
def view(name, file): proj = lookml.Project(path='.') f = proj.file(file) f + lookml.View(f'view: {name} {{}}') f.write()
def new(name): proj = lookml.Project(path='.') f = proj.new_file(name) f.write()
'hits_eventInfo.eventAction', 'hits_eventInfo.eventCategory', ] body = looker_sdk.models.WriteQuery( model=model, view=explore, #explore is what view means here... fields=fields, ) results = sdk.run_inline_query('json', body) resultsList = json.loads(results) #Setup pyLookML proj = lookml.Project(repo=args.repo, access_token=args.github_token, looker_host=args.base_url + '/', 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