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 test_local_file(self): x = lookml.File('lookml/tests/kitchenSink/kitchenSink.model.lkml') for v in x.views: for f in v.measures(): if f.type.value == 'sum' and not f.name.endswith('_total'): f.name = f.name + '_total' #Optionally Change the location x.setFolder('.tmp') #Write the file x.write()
def files(self,path=''): ''' Iteratively returns all the lkml files at a path in the project :param path: directory you would like to return the files from :type arg1: str :return: generator of LookML file objects :rtype: generator of lookml File objects ''' for f in self.repo.get_contents(path): yield lookml.File(f)
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_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 file(self,path): ''' returns a single LookML file at the specified path. examples: file('order_items.view.lkml') file('my_folder/users.view.lkml') :param path: path file location :type arg1: str :return: a single lookml File :rtype: File ''' return lookml.File(self.repo.get_contents(path))
def file(self,path): ''' returns a single LookML file at the specified path. examples: file('order_items.view.lkml') file('my_folder/users.view.lkml') :param path: path file location :type arg1: str :return: a single lookml File :rtype: File ''' return lookml.File(self.gitControllerSession.absoluteOutputPath + '/' + path)
def files(self,path=''): ''' Iteratively returns all the lkml files at a path in the project :param path: directory you would like to return the files from :type arg1: str :return: generator of LookML file objects :rtype: generator of lookml File objects ''' for root, dirs, files in os.walk(self.gitControllerSession.absoluteOutputPath + '/' + path, topdown=False): for name in files: if name.endswith('.lkml'): yield lookml.File(os.path.join(root, name))
def test_refinement(self): exp = lookml.Explore('exp') lookml.mkdir_force('.tmp/scratch') with open('.tmp/scratch/refinement_test.model.lkml', 'w') as f: f.write( '''explore: refine_ex {}''' ) myFile = lookml.File('.tmp/scratch/refinement_test.model.lkml') refine_ex = myFile.exps.refine_ex print(type(refine_ex)) # refine_ex.addProperty('aggregate_table',{'materialization':{'datagroup_trigger':'orders_datagroup'}}) print(refine_ex) # refine_ex.addProperty('aggregate_table','foo') print(myFile)
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_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_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_step6(self): ''' 6) Fetch and parse KitchenSink2 and test the successful addition of attributes in step 5 ''' self.f2 = lookml.File('lookml/tests/kitchenSink/kitchenSink2.model.lkml') order_items2 = self.f2.views.order_items #Check Sql Change self.assertEqual(order_items2.id.sql.value,"${TABLE}.test_sql_change") #Check dimension Addition: self.assertIsInstance(order_items2.test_add_dimension, lookml.Dimension) #Check view is still the same length + 1 (added one field in the prior test case) self.assertEqual(len(order_items2),48) #Check searching dimensions by sql: self.assertEqual(1,len(list(order_items2.search('sql','\$\{shipped_raw\}')))) #Check renamed field has the correct number of direct children self.assertEqual(1, len(list(order_items2.time_in_transit.children())))
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 setUp(self): self.f = lookml.File('lookml/tests/kitchenSink/kitchenSink.model.lkml') self.f2 = None self.order_items = self.f.views.order_items self.order_items_explore = self.f.explores.order_items
#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', [{ 'field': 'eventAction', 'value': eventAction }]) customEventsView + measure #Create/append to a new file newFile = lookml.File(customEventsView) proj.put(newFile) proj.deploy() fields_end = number_of_fields() print(str(fields_end - fields_start) + ' fields added to ' + newFile.path)