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
