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)
