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
