def _get_looker_client(self):
# The Looker SDK looks wants these as environment variables
os.environ["LOOKERSDK_CLIENT_ID"] = self.source_config.client_id
os.environ["LOOKERSDK_CLIENT_SECRET"] = self.source_config.client_secret
os.environ["LOOKERSDK_BASE_URL"] = self.source_config.base_url
return looker_sdk.init31()
def looker_api_call(request):
if request.method != 'POST':
return 'Unauthorized', 401
if not ('connection_name' in request.args
and len(request.args['connection_name']) > 0):
return 'Missing connection_name parameter', 404
if not ('table' in request.args and len(request.args['table']) > 0):
return 'Missing table parameter', 404
if not ('schema' in request.args and len(request.args['schema']) > 0):
return 'Missing schema parameter', 404
try:
sdk = looker_sdk.init31()
sql_query = "INSERT INTO %s.%s values ('%s')" % (
request.args['schema'], request.args['table'],
json.dumps(request.json))
model = looker_sdk.models31.SqlQueryCreate(
sql=sql_query, connection_name=request.args['connection_name'])
create_query = sdk.create_sql_query(model)
run_query = sdk.run_sql_query(slug=create_query.slug,
result_format="json")
return 'Success', 200
except:
return 'Uh Oh' + " Unexpected error:" + sys.exc_info()[0], 500
def action_execute(request):
payload = request.get_json()
print('action_execute payload:', payload)
keys = ['type', 'scheduled_plan', 'attachment', 'data', 'form_params']
for key in keys:
print(key, ':', payload[key])
original_query = payload['scheduled_plan']['query']
print('query details:', original_query)
sdk = looker_sdk.init31()
modified_query = {
"view": "order_items",
"fields": ["order_items.user_id"] + original_query['fields'],
"filters": {
"order_items.created_date": "2021/01/01 to 2021/03/30"
},
"sorts": ["order_items.created_date desc"],
"limit": "10",
"model": "hns_core",
}
resp = sdk.run_inline_query("json", modified_query)
resp = json.loads(resp)
print('response to modified query:', resp)
return {'statusCode': 200, 'body': json.dumps('action_execute received')}
def _sdk(api_version):
filename = os.getenv("LOOKERSDK_INI", "../looker.ini")
if api_version == 4.0:
sdk = looker_sdk.init40(filename)
elif api_version == 3.1:
sdk = looker_sdk.init31(filename)
return sdk
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 main():
kafka_producer = make_kafka_producer(EXTRA_KAFKA_CONF)
sdk = looker_sdk.init31()
dashboard_ids = [
dashboard_base.id for dashboard_base in sdk.all_dashboards(fields="id")
]
looker_dashboards = []
for dashboard_id in dashboard_ids:
try:
fields = ["id", "title", "dashboard_elements", "dashboard_filters"]
dashboard_object = sdk.dashboard(dashboard_id=dashboard_id,
fields=",".join(fields))
except SDKError as e:
# A looker dashboard could be deleted in between the list and the get
print(f"Skipping dashboard with dashboard_id: {dashboard_id}")
print(e)
continue
looker_dashboard = get_looker_dashboard(dashboard_object)
looker_dashboards.append(looker_dashboard)
pprint(looker_dashboard)
for looker_dashboard in looker_dashboards:
workaround_dashboard_kafka_events = WorkaroundDatahubEvents.make_dashboard_mce(
looker_dashboard)
# Hard to test these events since datahub does not have a UI, for now disable sending them
# proper_dashboard_kafka_events = ProperDatahubEvents.make_dashboard_mce(looker_dashboard)
for mce in workaround_dashboard_kafka_events.all_mces():
print(mce)
kafka_producer.produce(topic=KAFKA_TOPIC,
key=mce['proposedSnapshot'][1]['urn'],
value=mce)
kafka_producer.flush()
def get_sdk_for_schedule(
scheduled_plan_id: int) -> looker_sdk.sdk.api31.methods.Looker31SDK:
sdk = looker_sdk.init31()
plan = sdk.scheduled_plan(scheduled_plan_id)
sdk.login_user(plan.user_id)
return sdk
def create_db_connection(con_file):
# The saved connection must be a pre-pickled WriteDBConnection object
with open(con_file, "rb") as f:
db = pickle.load(f)
sdk = looker_sdk.init31()
sdk.create_connection(db)
def setup_sdk(client_id, client_secret, instance) -> methods.Looker31SDK:
os.environ["LOOKERSDK_BASE_URL"] = instance
os.environ["LOOKERSDK_API_VERSION"] = "3.1"
os.environ["LOOKERSDK_VERIFY_SSL"] = "true"
os.environ["LOOKERSDK_TIMEOUT"] = "120"
os.environ["LOOKERSDK_CLIENT_ID"] = client_id
os.environ["LOOKERSDK_CLIENT_SECRET"] = client_secret
return looker_sdk.init31()
def create_db_connection(con_file):
with open(con_file) as f:
db = json.load(f)
sdk = looker_sdk.init31(config_file=SCRIPT_PATH.joinpath("looker.ini"))
write_conn = models.WriteDBConnection()
write_conn.__dict__.update(db)
sdk.create_connection(write_conn)
def get_sql(model_name, explore_name, fields):
sdk = looker_sdk.init31()
body = models.WriteQuery(model=model_name,
view=explore_name,
fields=fields,
pivots=[],
filters={},
sorts=[],
limit=50)
sql = sdk.run_inline_query(result_format='sql', body=body)
return sql
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 create_project(project_name):
project = models.WriteProject(name=project_name)
sdk = looker_sdk.init31(config_file=SCRIPT_PATH.joinpath("looker.ini"))
# We need to enter dev mode to create a project
sdk.update_session(models.WriteApiSession(workspace_id="dev"))
resp = sdk.create_project(project)
# Now we update the project and set it to a bare repo
sdk.update_project(resp.id, models.WriteProject(git_service_name="bare"))
return resp.id
def create_model_entries(project_id):
sdk = looker_sdk.init31()
files = sdk.all_project_files(project_id)
lkml_models = [
file.title.split(".")[0] for file in files if file.type == "model"
]
for l in lkml_models:
new_model = models.WriteLookmlModel(name=l,
project_name=project_id,
unlimited_db_connections=True)
sdk.create_lookml_model(new_model)
def auth(log, ini_path='looker.ini', section=None):
"""Attempt to log in to Looker API with specified ini file and section"""
try:
client = looker_sdk.init31(ini_path, section)
user = client.me()
cprint(f"Authenticated as {user.display_name} (User ID {user.id})",
'HEADER', log)
except SDKError:
cprint(
f"Could not authenticate. Check `looker.ini` file at {ini_path} and section name {section}",
"FAIL", log)
raise SDKError
return client
def _get_looker_client(self):
# The Looker SDK looks wants these as environment variables
os.environ["LOOKERSDK_CLIENT_ID"] = self.source_config.client_id
os.environ[
"LOOKERSDK_CLIENT_SECRET"] = self.source_config.client_secret
os.environ["LOOKERSDK_BASE_URL"] = self.source_config.base_url
client = looker_sdk.init31()
# try authenticating current user to check connectivity
# (since it's possible to initialize an invalid client without any complaints)
client.me()
return client
def getConnectionFromAPI(self, name):
sdk = looker_sdk.init31('looker.ini')
connection = sdk.connection(name, 'name, database, schema')
if connection.name:
self.name = connection.name
if connection.database:
self.databaseName = connection.database
if connection.schema:
self.schemaName = connection.schema
self.connection = connection
def __init__(self, config: LookerAPIConfig) -> None:
# The Looker SDK looks wants these as environment variables
os.environ["LOOKERSDK_CLIENT_ID"] = config.client_id
os.environ["LOOKERSDK_CLIENT_SECRET"] = config.client_secret
os.environ["LOOKERSDK_BASE_URL"] = config.base_url
self.client = looker_sdk.init31()
# try authenticating current user to check connectivity
# (since it's possible to initialize an invalid client without any complaints)
try:
self.client.me()
except SDKError as e:
raise ConfigurationError(
"Failed to initialize Looker client. Please check your configuration."
) from e
def create_model_entries(project_id):
print("Creating model entry")
sdk = looker_sdk.init31(config_file=SCRIPT_PATH.joinpath("looker.ini"))
files = sdk.all_project_files(project_id)
lkml_models = [
file.title.split(".")[0] for file in files if file.type == "model"
]
for l in lkml_models:
new_model = models.WriteLookmlModel(name=l,
project_name=project_id,
unlimited_db_connections=True)
sdk.create_lookml_model(new_model)
print("Done creating model entries")
def register_user(*, hackathon: str, first_name: str, last_name: str,
email: str) -> str:
sdk = looker_sdk.init31()
user = find_or_create_user(sdk=sdk,
first_name=first_name,
last_name=last_name,
email=email)
assert user.id
if not user.credentials_email:
create_email_credentials(sdk=sdk, user_id=user.id, email=email)
if user.credentials_api3:
client_id = user.credentials_api3[0].client_id
else:
client_id = create_api3_credentials(sdk=sdk, user_id=user.id).client_id
set_user_group(sdk=sdk, user_id=user.id, hackathon=hackathon)
set_user_attributes(sdk=sdk, user_id=user.id, hackathon=hackathon)
disable_user(sdk=sdk, user_id=user.id)
assert client_id
return client_id
def generate_directories(
namespaces: Dict[str, NamespaceDict], spoke_dir: Path, sdk_setup=False
):
"""Generate directories and model for a namespace, if it doesn't exist."""
sdk = looker_sdk.init31()
logging.info("Looker SDK 3.1 initialized successfully.")
existing_dirs = {p.name for p in spoke_dir.iterdir()}
for namespace, defn in namespaces.items():
if namespace in existing_dirs:
continue
(spoke_dir / namespace).mkdir()
for dirname in ("views", "explores", "dashboards"):
(spoke_dir / namespace / dirname).mkdir()
(spoke_dir / namespace / dirname / ".gitkeep").touch()
generate_model(spoke_dir, namespace, defn)
if sdk_setup:
configure_model(sdk, namespace)
def enable_users_by_hackathons(hackathons: Sequence[str]) -> Dict[str, str]:
global LOOKER_GROUP_PREFIX
sdk = looker_sdk.init31()
groups = {g.name: g.id for g in sdk.all_groups(fields="id,name")}
ret = {}
for hackathon in hackathons:
try:
group_id = groups[f"{LOOKER_GROUP_PREFIX}{hackathon}"]
except KeyError:
raise RegisterError(f"No group found for hackathon: '{hackathon}'")
for user in sdk.search_users(group_id=group_id):
assert user.id
assert user.email
sdk.update_user(user_id=user.id,
body=models.WriteUser(is_disabled=False))
password_reset_url = sdk.create_user_credentials_email_password_reset(
user_id=user.id, expires=False).password_reset_url
assert password_reset_url
setup = re.sub("password/reset", "account/setup",
password_reset_url)
ret[user.email] = setup
return ret
def execute_nba(r):
if r.path == '/action_list':
return action_list(r)
headers = r.headers
current_time = datetime.datetime.now()
request_json = r.json
sdk = looker_sdk.init31()
query = sdk.query_for_slug(
request_json['scheduled_plan']['query']['client_id'])
sql = sdk.run_query(query.id, 'sql', 22).replace('LIMIT 22', '')
new_sql = "SELECT data.ts, users.*, data.id, uniform(1, 10, random()) as next_best_action FROM (%s) as users CROSS JOIN (SELECT '%s'::timestamp as ts, '%s' as id) as data" % (
sql, str(current_time), headers['x-looker-webhook-id'])
insert_sql = "INSERT INTO ${%s.SQL_TABLE_NAME} %s" % (
os.environ['LOOKML_VIEW_NAME'], new_sql)
sql_query = sdk.create_sql_query(body=looker_sdk.models.SqlQueryCreate(
model_name=os.environ['LOOKML_MODEL_NAME'], sql=insert_sql))
sdk.run_sql_query(sql_query.slug, 'json')
return rHandle(200, "ok")
def get_field_values(model_name, explore_name):
sdk = looker_sdk.init31()
# API Call to pull in metadata about fields in a particular explore
explore = sdk.lookml_model_explore(
lookml_model_name=model_name,
explore_name=explore_name,
fields="id, name, description, fields",
)
my_fields = []
# Iterate through the field definitions and pull in the description, sql,
# and other looker tags you might want to include in your data dictionary.
if explore.fields and explore.fields.dimensions:
for dimension in explore.fields.dimensions:
dim_def = {
"field_type": "Dimension",
"view_name": dimension.view_label,
"field_name": dimension.label_short,
"type": dimension.type,
"description": dimension.description,
"sql": dimension.sql,
}
my_fields.append(dim_def)
if explore.fields and explore.fields.measures:
for measure in explore.fields.measures:
mes_def = {
"field_type": "Measure",
"view_name": measure.view_label,
"field_name": measure.label_short,
"type": measure.type,
"description": measure.description,
"sql": measure.sql,
}
my_fields.append(mes_def)
return my_fields
from email.mime.text import MIMEText
from google_auth_oauthlib.flow import Flow, InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload, MediaIoBaseDownload
from google.auth.transport.requests import Request
from google.oauth2 import service_account
import requests
import smtplib
#######################GSHEETS AUTH#########################
SERVICE_ACCOUNT_FILE = 'gsheets-api.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
# SPREADSHEET_ID = '10rD-oV5FpojvPAqni0J1QHmA3ICnfxueG0DvOPyVRYw'
SPREADSHEET_ID = "17wh68_SaT5bkw3gRFp7PeRyWPzalyq9qaNNPp1_urXY"
###########Initilize Services#############
#looker sdk
sdk = looker_sdk.init31()
credentials, project = google.auth.default()
#gsheets
service = build('sheets', 'v4', credentials=credentials)
sheet = service.spreadsheets()
#smtp
server = smtplib.SMTP_SSL("smtp.gmail.com", 465)
server.ehlo()
server.login(os.environ.get('GMAIL_USER'), os.environ.get('GMAIL_PASS'))
def find_linked_dash(look_ids):
'''
Helper function to find dashboard(s) our linked look is on
'''
######query parameters#######
except TypeError:
pass
print(functionResponse)
if __name__ == "__main__":
"""
Command line parser arguments
"""
parser = argparse.ArgumentParser()
parser.add_argument('--ini', type=str)
parser.add_argument('--repo', type=str)
parser.add_argument('--lookml_param', type=str)
args = parser.parse_args()
ini_file = args.ini
config = ConfigParser.RawConfigParser(allow_no_value=True)
config.read(ini_file)
github_token = config.get('Github', 'github_token')
sdk = looker_sdk.init31(config_file=ini_file)
gitoutput = github_lkml(args.repo)
html_objects = find_param_lkml_objects(lookml_list=gitoutput,
param=args.lookml_param)
compare_html_objects(queryCompare=html_objects)
# print(compare_html_objects)
# s(queryCompare=find_html_lkml_objects(lookml_list=github_lkml('monkey100'))))
import requests
from github import Github
import looker_sdk
from looker_sdk import models
import lkml
#initialize SDK
sdk = looker_sdk.init31("YOUR INI FILE", section="SECTION OF YOUR INI FILE")
############SET THESE VARIABLES
user = "******" # your github user
token = "YOUR ACCESS TOKEN" # personal access token (https://docs.github.com/en/free-pro-team@latest/github/authenticating-to-github/creating-a-personal-access-token)
repo = "YOUR REPO" # name of your repo
owner = "REPO OWNER" # owner of the git repo
file_name = "LOOKML FILE" # ie. users.view.lkml
#authenticate via git and load lookML file
def git_auth(user, token, repo, file_name, owner):
query_url = f"https://api.github.com/repos/{owner}/{repo}"
params = {
"state": "open",
}
headers = {'Authorization': f'token {token}'}
r = requests.get(query_url, headers=headers, params=params)
#grab github repo content(s)
g = Github(token)
repo = g.get_repo(owner + "/" + repo)
#specify lookml file
content = repo.get_contents(path="/Views/" + file_name)
raw_data = content.decoded_content.decode("utf-8")
import looker_sdk # from typing import cast, MutableSequence, Sequence ####Initialize API/SDK for more info go here: https://pypi.org/project/looker-sdk/ from looker_sdk import methods31, models sdk = looker_sdk.init31() # or init40() for v4.0 API me = sdk.me() ### DEFINE VALUES HERE #### # DASHBOARDID = VALUE # USERID = VALUE # SCHEDULETITLE = VALUE # EMAIL = VALUE #Simple Create schedule plan example ## For more information on the Params accepted https://github.com/looker-open-source/sdk-codegen/blob/master/python/looker_sdk/sdk/api31/methods.py#L2144 ### And for schedule destination go: https://github.com/looker-open-source/sdk-codegen/blob/master/python/looker_sdk/sdk/api31/models.py#L4601 schedule = sdk.create_scheduled_plan(body=models.WriteScheduledPlan(name=SCHEDULETITLE, dashboard_id=DASHBOARDID, user_id=USERID, run_as_recipient= True, crontab="0 1 * * *", scheduled_plan_destination = [models.ScheduledPlanDestination(format="csv_zip", apply_formatting=True, apply_vis=True, address=EMAIL, type="email", message="Aloha!")]))
def get_sdk_all_access() -> looker_sdk.sdk.api31.methods.Looker31SDK:
sdk = looker_sdk.init31()
return sdk
import sys
import textwrap
import time
import looker_sdk
from looker_sdk import models
import sdk_exceptions
sdk = looker_sdk.init31("../../looker.ini")
def main():
"""Given a look title, find the corresponding look id and use
it to render its image.
$ python download_look.py "A good look" 1024 768 png
"""
look_title = sys.argv[1] if len(sys.argv) > 1 else ""
image_width = int(sys.argv[2]) if len(sys.argv) > 2 else 545
image_height = int(sys.argv[3]) if len(sys.argv) > 3 else 842
image_format = sys.argv[4] if len(sys.argv) > 4 else "png"
if not look_title:
raise sdk_exceptions.ArgumentError(
textwrap.dedent("""
Please provide: <lookTitle> [<img_width>] [<img_height>] [<img_format>]
img_width defaults to 545
img_height defaults to 842
img_format defaults to 'png'"""))
