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_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)