PyLookML

https://pylookml.readthedocs.io/en/latest/
Admin
PyLookML is a Python library that enables scripting and automation of LookML files with an...
Tokens:6,626
Snippets:58
Trust Score:9.6
Update:2 months ago
Show doc for...
Tokens:
Raw
### Build from Developer Version

Source: https://pylookml.readthedocs.io/en/latest/index.html

Setup a virtual environment and install a specific commit of PyLookML from GitHub.

```bash
which python3 #(this is generally the best interpreter use as the startingpoint)
#Output: /Library/Frameworks/Python.framework/Versions/3.8/bin/python3
mkdir lookml_test
cd lookml_test
virtualenv -p /Library/Frameworks/Python.framework/Versions/3.8/bin/python3 lookml_test_env
source lookml_test_env/bin/activate
pip install pygithub
pip install lkml
```

```bash
pip install git+https://github.com/llooker/lookml.git@04dbd05dd3f37a7fa624501a370df52af26bb5fc
```

--------------------------------

### Install Dependencies

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Command to install the required Python packages for Looker SDK and pyLookML.

```bash
pip install lookml, looker_sdk
```

--------------------------------

### Install PyLookML

Source: https://pylookml.readthedocs.io/en/latest/autotune.html

Install the package via pip to enable the lookml command-line interface.

```bash
pip install lookml
```

--------------------------------

### Looping Over LookML Tags

Source: https://pylookml.readthedocs.io/en/latest/index.html

Example of iterating over tags defined in a dimension.

```lookml
dimension: id {
    type: string
    sql: ${TABLE}.id ;;
    tags: ["a","b","c"]
}
```

```python
for tag in order_items.id.tags:
    print(tag)
>>> 'a'
>>> 'b'
>>> 'c'
```

--------------------------------

### Get fields by tag

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Retrieves fields associated with a specific tag and performs operations on them.

```python
for field in orderItems.getFieldsByTag('x'):
   #do work
   field.removeTag('x')
```

--------------------------------

### Connect to a local filesystem project

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Initializes a project connection pointing to a local directory path.

```python
     proj = lookml.Project(
              path='path/to/myproject'
     )
```

--------------------------------

### Initialize Project and Access LookML Objects

Source: https://pylookml.readthedocs.io/en/latest/index.html

Initialize a project from a GitHub repository or a generic Git URL to access views and dimensions.

```python
import lookml
proj = lookml.Project(
      repo= "llooker/pyLookMLExample",
      access_token="your_github_access_token",
      #Optional args for the deploy URL (for deploying directly to prodcution mode)
     ,looker_host="https://mylooker.looker.com/"
     ,looker_project_name="my_project"
)
viewFile = proj.file('01_order_items.view.lkml')
orderItems = viewFile.views.order_items
print(orderItems.id)
```

```python
     self.proj = lookml.Project(
              git_url='git@bitbucket.org:myorg/russ_sandbox.git'
              #Optional args for the deploy URL (for deploying directly to prodcution mode)
             ,looker_host="https://mylooker.looker.com/"
             ,looker_project_name="my_project"
     )
```

--------------------------------

### Initialize pyLookML Project Connection

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Establishes a connection to a GitHub repository and a Looker project instance.

```python
proj = lookml.Project(
         #the github location of the repo
         repo= 'llooker/your_repo'
         #instructions on creating an access token: https://help.github.com/en/github/authenticating-to-github/creating-a-personal-access-token-for-the-command-line
         ,access_token='your_access_token'
         #your Looker host
         ,looker_host="https://example.looker.com/"
         #The name of the project on your looker host
         ,looker_project_name="pylookml_testing_2"
         #You can deploy to branches other than master, a shared or personal branch if you would like to hop into looker, pull
         #remote changes and review before the code is committed to production
         ,branch='master'
 )
```

--------------------------------

### Connect to a GitHub project

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Initializes a project connection using a GitHub repository and access token.

```python
import lookml
proj = lookml.Project(
      repo= "llooker/russ_sandbox",
      access_token="your_github_access_token",
)
```

--------------------------------

### Connect to a project via SSH

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Initializes a project connection using an SSH git URL, with optional parameters for Looker deployment.

```python
     proj = lookml.ProjectSSH(
              git_url='git@bitbucket.org:myorg/russ_sandbox.git'
              #Optional args for the deploy URL (for deploying directly to prodcution mode)
             ,looker_host="https://mylooker.looker.com/"
             ,looker_project_name="my_project"
     )
```

--------------------------------

### Create a new file in a project

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Creates a new file and adds a view definition and a dimension to it.

```python
newFile = proj.new_file('views/my_new_file.view.lkml')
newFile + 'view: new_view {}'
newFile.views.new_view + lookml.Dimension('dimension: id {}')
```

--------------------------------

### Run autotune commands

Source: https://pylookml.readthedocs.io/en/latest/autotune.html

Execute the autotune process using the CLI with various configuration options.

```bash
lookml autotune
```

```bash
lookml autotune useconfig
```

```bash
lookml autotune guided
```

--------------------------------

### Create a new model file

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Creates a new model file and adds an explore definition to it.

```python
modelFile = proj.new_file('my_new_model.model.lkml')
modelFile + 'explore: foo {}'
```

--------------------------------

### Save and Deploy LookML Project

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Python commands to save a file to a GitHub-connected project and trigger a deployment to sync Looker production mode.

```python
# step 8 -- save the file back to the project in github
 proj.put(modelFile)
 #s step 9 -- hit the looker deploy URL to sync Looker production mode with the github master branch
 proj.deploy()
```

--------------------------------

### Accessing Field References

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Demonstrates how to access different reference formats for a field in a LookML view.

```python
>>> myView = View('order_items') + 'id'
>>> print(myView.id)
dimension: id {

   }
#__ref__ stands for reference
>>> print(myView.id.__ref__)
${order_items.id}
#__refs__ stands for reference short
>>> print(myView.id.__refs__)
${id}
#__refr__ stands for reference raw
>>> print(myView.id.__refr__)
order_items.id
#__refrs__ stands for reference raw short
>>> print(myView.id.__refrs__)
id
```

--------------------------------

### Connect to Looker API and Retrieve EAV Fields

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Initializes the Looker SDK and executes a SQL query to fetch EAV field definitions for further processing.

```python
 import lookml
 from looker_sdk import models, methods, init40
 import json

 # step 1 -- connect to the Looker API to pull a list of EAV fields
 sdk = init40("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
 """
 query_config = models.SqlQueryCreate(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"))
```

--------------------------------

### Assemble model file components

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Concatenate strings representing derived tables and access grants to the model file.

```text
flatteningNDT + drivedtableString
modelFile + accessGrants
```

--------------------------------

### Configure autotune.ini

Source: https://pylookml.readthedocs.io/en/latest/autotune.html

Define the required Looker API credentials and model settings in an autotune.ini file.

```ini
[autotune]
access_token = xxx
looker_host = https://mycompany.looker.com:19999
api_client = xxx
api_secret = yyy
model_name = bike_share
branch = dev-john-doe-yddt
```

--------------------------------

### Configure Looker API Credentials

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Configuration format for the api.ini file required to authenticate with the Looker API.

```ini
# Base URL for API. Do not include /api/* in the url
base_url = https://mylooker.looker.com:19999
# API 3 client id
client_id=put_your_client_id_here
# API 3 client secret
client_secret=put_your_sectret_here
# Set to false if testing locally against self-signed certs. Otherwise leave True
```

--------------------------------

### Access Model File and Views

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Retrieves the model file and specific views required for EAV manipulation.

```python
modelFile = proj['eav_model.model.lkml']

 # step 3 -- Set up the objects we'll be manipulating (some are just strings which will be added back to the LookML at the end)
 #the EAV source view points to our custom_profile_fields database table
 eavSource = modelFile['views']['eav_source']
 #the user profile we'll call the "flattening NDT" since that's where our flattening logic lives
 flatteningNDT = modelFile['views']['usr_profile']
```

--------------------------------

### Initialize Dimension with numeric LookML names

Source: https://pylookml.readthedocs.io/en/latest/changelog.html

Use this syntax to instantiate a Dimension object when the LookML name contains numeric characters.

```python
my_dim = lookml.Dimension('dimension: custom_5 {}')
```

--------------------------------

### Define LookML Explore and Views for EAV

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Initial LookML configuration defining the base user table, the EAV source table, and the profile view used for unpacking data.

```lookml
connection: "snowlooker"

explore: usr {
    access_filter: {
        field: usr_profile.org_id
        user_attribute: org_id
    }
    join: usr_profile {
        type: left_outer
        relationship: one_to_one
        sql_on: ${usr.id} =  ${usr_profile.user_id} ;;
    }
}

view: usr {
    sql_table_name: public.users ;;
    dimension: email {}
    dimension: id {}
    dimension_group: created { timeframes: [raw,date,month,year] }
}

view: usr_profile {
    dimension: org_id {}
    dimension: user_id {}
}

view: eav_source {
    sql_table_name: public.custom_profile_fields ;;
    dimension: datatype { type: string }
    dimension: field_name { type: string }
    dimension: org_id { type: number }
    dimension: user_id { type: number }
    dimension: value { type: string }
}
```

--------------------------------

### Loop over views in a file

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Accesses a specific file and iterates through all views contained within it.

```python
myFile = proj.file('01_order_items.view.lkml')
#Loops over 1:n views in the file
for view in myFile.views:
    print(view)
```

--------------------------------

### Add a new view to an existing file

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Creates a new view object and appends it to an existing file.

```python
myNewView = lookml.View('hello_world')
myFile = proj.file('01_order_items.view.lkml')
myFile + myNewView
for view in myFile.views:
   print(view.name)
>>> 'order_items'
>>> 'hello_world'
```

--------------------------------

### Loop over fields by type

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Iterates through dimensions, measures, and filters within a view to print their references.

```python
>>> for dim in myFile.views.order_items.dimensions():
...     print(dim.__ref__)
...
${order_items.new_dimension}
${order_items.id}
${order_items.cpt_code_value}
${order_items.inventory_item_id}
...
>>> for meas in myFile.views.order_items.measures():
...     print(meas.__ref__)
...
${order_items.count}
${order_items.min_sale_price}
${order_items.max_sale_price}
${order_items.order_count}
>>> for flt in myFile.views.order_items.filters():
...     print(flt.__ref__)
...
${order_items.cpt_code}
${order_items.cohort_by}
${order_items.metric}
```

--------------------------------

### Reference LookML Objects

Source: https://pylookml.readthedocs.io/en/latest/index.html

Access properties using dot notation or dictionary-style syntax.

```python
myProject = lookml.Project(
     repo= "llooker/russ_sandbox",
     access_token="your_github_access_token",
)
#Use a dot operator syntax:
myProject.file('order_items.view.lkml').views.order_items.id.primary_key.value

#Use a dictionary like syntax:
myProject['order_items.view.lkml']['views']['order_items']['id'].primary_key.value
```

```python
lookml.Project(**config['project1'])['order_items.view.lkml']['views']['order_items']['id'].primary_key.value
```

--------------------------------

### Search view fields by regex

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Searches for dimensions within a view that match a specific regex pattern in a given parameter.

```python
>>> for item in order_items.search('sql','\$\{shipped_raw\}'):
...     print(item.__ref__)
...     print(item.sql)
...
${order_items.shipping_time}
sql: datediff('day',${shipped_raw},${delivered_raw})*1.0 ;;
```

--------------------------------

### Complete LookML EAV Model

Source: https://pylookml.readthedocs.io/en/latest/eav.html

A comprehensive LookML file demonstrating access grants, explores, and derived tables for flattening EAV data structures.

```lookml
connection: "snowlooker"

access_grant: org_8 {
    user_attribute: org_id
    allowed_values: [
        "8",
    ]
}
access_grant: org_101 {
    user_attribute: org_id
    allowed_values: [
        "101",
    ]
}
access_grant: org_102 {
    user_attribute: org_id
    allowed_values: [
        "102",
    ]
}

explore: usr {
    access_filter: {
        field: usr_profile.org_id
        user_attribute: org_id
    }
    join: usr_profile {
        type: left_outer
        relationship: one_to_one
        sql_on: ${usr.id} =  ${usr_profile.user_id} ;;
    }
}

explore: _eav_flattener {
    from: eav_source
    hidden: yes
}

view: usr {
    sql_table_name: public.users ;;
    dimension: email {}
    dimension: id {}
    dimension_group: created {
        timeframes: [
            raw, date, month, year,
        ]
        type: time
        }
}

view: usr_profile {

derived_table: {
    explore_source: _eav_flattener {
    column: user_id { field: _eav_flattener.user_id}
    column: org_id { field: _eav_flattener.org_id }
    column: c_donation_amount { field: _eav_flattener.c_donation_amount}
    column: c_monthly_contribution { field: _eav_flattener.c_monthly_contribution }
    column: c_highest_achievement { field: _eav_flattener.c_highest_achievement }
    column: age { field: _eav_flattener.age }
    }
}
dimension: age_org_101 {
    label: "age"
    type: number
    sql: ${TABLE}.age ;;
    required_access_grants: [org_101,]
    }
dimension: c_donation_amount_org_8 {
    label: "c_donation_amount"
    type: number
    sql: ${TABLE}.c_donation_amount ;;
    required_access_grants: [org_8,]
    }
dimension: c_highest_achievement_org_101 {
    label: "c_highest_achievement"
    type: string
    sql: ${TABLE}.c_highest_achievement ;;
    required_access_grants: [org_101,]
    }
dimension: c_highest_achievement_org_8 {
    label: "c_highest_achievement"
    type: string
    sql: ${TABLE}.c_highest_achievement ;;
    required_access_grants: [org_8,]
    }
dimension: c_monthly_contribution_org_101 {
    label: "c_monthly_contribution"
    type: number
    sql: ${TABLE}.c_monthly_contribution ;;
    required_access_grants: [org_101,]
    }
dimension: c_monthly_contribution_org_102 {
    label: "c_monthly_contribution"
    type: number
    sql: ${TABLE}.c_monthly_contribution ;;
    required_access_grants: [org_102,]
    }
dimension: org_id {}
dimension: user_id {}
measure: age_total_org_101 {
    label: "age_total"
    type: sum
    sql: ${age_org_101} ;;
    required_access_grants: [org_101,]
    }
measure: c_donation_amount_total_org_8 {
    label: "c_donation_amount_total"
    type: sum
    sql: ${c_donation_amount_org_8} ;;
    required_access_grants: [org_8,]
    }
measure: c_monthly_contribution_total_org_101 {
    label: "c_monthly_contribution_total"
    type: sum
    sql: ${c_monthly_contribution_org_101} ;;
    required_access_grants: [org_101,]
    }
measure: c_monthly_contribution_total_org_102 {
    label: "c_monthly_contribution_total"
    type: sum
    sql: ${c_monthly_contribution_org_102} ;;
    required_access_grants: [org_102,]
    }
}

view: eav_source {
sql_table_name: public.custom_profile_fields ;;
dimension: datatype { type: string }
dimension: field_name { type: string }
dimension: org_id { type: number }
dimension: user_id { type: number }
dimension: value { type: string }

measure: age {
    type: max
    sql: CASE WHEN ${field_name} = 'age' THEN ${value} ELSE NULL END ;;
    }
measure: c_donation_amount {
    type: max
    sql: CASE WHEN ${field_name} = 'c_donation_amount' THEN ${value} ELSE NULL END ;;
    }
measure: c_highest_achievement {
    type: max
    sql: CASE WHEN ${field_name} = 'c_highest_achievement' THEN ${value} ELSE NULL END ;;
    }
measure: c_monthly_contribution {
    type: max
    sql: CASE WHEN ${field_name} = 'c_monthly_contribution' THEN ${value} ELSE NULL END ;;
    }
}
```

--------------------------------

### Inject Hidden Explore and Initialize Derived Table

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Adds a hidden explore to the model and initializes the string for the derived table definition.

```python
#Ensure there is a hidden explore to expose the eav_souce transformations to our user_profile 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 pair of list to track the unique org ids and column names
 #since the api query will be at a org / column level this allows us to "de-dupe"
 orgIds, columns = [], []
```

--------------------------------

### Update and Deploy LookML Objects

Source: https://pylookml.readthedocs.io/en/latest/index.html

Modify LookML objects using the + operator and save changes back to the repository.

```python
 order_items = lookml.View('order_items')
 order_items + '''
     dimension: id {
         type: string
         sql: ${TABLE}.id ;;
         }
 '''
 order_items.id.primary_key = 'yes'
 print(order_items)
```

```python
 newFile = lookml.File(order_items)
 #the put method, creates or overwrites
 myProject.put(newFile)
 #optionally hitting the Looker deploy URL (requires that you set your instance URL on project creation)
 myProject.deploy()
```

--------------------------------

### Write files back to GitHub

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Modifies a view field by adding a tag and commits the changes back to the project.

```python
viewFile = proj.file('01_order_items.view.lkml')
viewFile.views.order_items.id.addTag("hello, World!")
proj.put(viewFile)
```

--------------------------------

### Generate EAV Flattening Logic

Source: https://pylookml.readthedocs.io/en/latest/eav.html

Iterates through EAV key-value pairs to construct LookML measures and NDT dimensions dynamically.

```python
# step 4 -- loop over the list of EAV k,v pairs and do work
 for column in response:
     dimName = lookml.core.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;;
             }}
     '''

     # 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']}]
             }}
         '''
 # step 5 -- loop over the distinct raw columns (obtained in the full k,v loop) for adding columns to the NDT
 for col in set(columns):
     drivedtableString += f' column: {col} {{ field: _eav_flattener.{col} }}'
 drivedtableString += '}}'

 # step 6 -- loop over the distinct org ids to add the model's access grants
 accessGrants = ''
 for org in set(orgIds):
     accessGrants += f'''
         access_grant: org_{org} {{
         user_attribute: org_id
         allowed_values: [
             "{org}"
         ]
         }}
     '''
```

--------------------------------

### Check field children

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Retrieves and prints the references of all descendant fields for a specific field.

```python
>>> for child in order_items.sale_price.children():
...     print(child.__refs__)
...
${min_sale_price}
${max_sale_price}
${total_sale_price}
${average_sale_price}
${median_sale_price}
${returned_total_sale_price}
${gross_margin}
${item_gross_margin_percentage}
```

--------------------------------

### Modifying Local LookML Files

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Iterates through a file to rename measures and save changes to a new location.

```python
myFile = lookml.File('example.view.lkml')
   for v in myFile.views:
      for f in v.measures():
            if f.type.value == 'sum' and not f.name.endswith('_total'):
               f.name = f.setName_safe(f.name + '_total')
   #Optionally Change the location
   myFile.setFolder('pathto/other/folder')
   #Write the file
   x.write()
```

--------------------------------

### Window Function EAV Flattening

Source: https://pylookml.readthedocs.io/en/latest/eav.html

An alternative SQL approach using window functions to extract values from EAV tables.

```sql
FIRST_VALUE(
    CASE
        WHEN attributename = 'single_type' THEN attributevalue
        ELSE NULL
    END
IGNORE NULLS)
OVER (partition by sessionid order by sessionid)
```

--------------------------------

### Renaming Fields and Updating References

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Uses setName_safe to rename a field and automatically update all child references in the LookML.

```python
>>> print(order_items2.shipping_time)

dimension: shipping_time {
  type: number
  sql: datediff('day',${shipped_raw},${delivered_raw})*1.0 ;;
}

>>> for field in order_items2.shipping_time.children():
...    print(field)

measure: average_shipping_time {
  type: average
  value_format_name: decimal_2
  sql: ${shipping_time} ;;
}
#The setName_safe method previously change_name_and_child_references, use that if setName_safe not found
>>> order_items2.shipping_time.setName_safe('time_in_transit')
>>> print(time_in_transit)
dimension: time_in_transit {
  type: number
  sql: datediff('day',${shipped_raw},${delivered_raw})*1.0 ;;
}
>>> for field in order_items2.time_in_transit.children():
...    print(field)
measure: average_shipping_time {
  type: average
  value_format_name: decimal_2
  sql: ${time_in_transit} ;;
}
```

--------------------------------

### Adding Sum Measures

Source: https://pylookml.readthedocs.io/en/latest/examples.html

Adds a sum measure for every numeric dimension present in a view.

```python
orderItems.sumAllNumDimensions()
```
Tokens:
Raw