--Send SQL from your local environment using Looker API and receive the result
――It's a little tedious to download csv each time you analyze data, so I want to erase it before work is born. --If you have an SDK, you may be able to enjoy it even more.
Looker Api SDK Official description https://docs.looker.com/reference/api-and-integration/api-sdk
GitHub https://github.com/looker-open-source/sdk-codegen/tree/master/python
Install for the time being
pip install looker_sdk
--Click here to get API authentication key link --Click here for the types of environment variables link
###Environment variable settings
os.environ['LOOKERSDK_API_VERSION']='3.1'
os.environ['LOOKERSDK_BASE_URL']='https://xxxxxxxx.looker.com:19999'
os.environ['LOOKERSDK_CLIENT_ID']='xxxxxxxxxxxxxxxxxxxx'
os.environ['LOOKERSDK_CLIENT_SECRET']='xxxxxxxxxxxxxxxxxxxxxxxx'
###Authentication
sdk = looker_sdk.init31()
my_user = sdk.me()
print('####################')
print(my_user["first_name"])
print('####################')
There seems to be a way to read the ini file, but
did it! It seems that the SDK will read the authentication information from the environment variables.
Where can I find out? .. ..
Let's take a look at the reference for the time being. .. .. .. When
Oh oh oh! ?? There is something called SQL Runner in the API reference. It's like that!
Apparently it looks like the BETA version. (As of December 23, 2020) If it changes, it has changed, so for the time being GoGo !!
Probably a usable module looks like this Link
--There is no sample code, so it's a bit painful. --Looking at the reference, it seems that the operation of the corresponding part is as follows --Register query --Get query --Execute query --Probably it seems to be a mechanism to queue and execute. You can see the queue below - https://xxxx.looker.com/admin/queries
--As far as I can find it on the net, it seems that sdk will bring the method corresponding to the API URL. --This is in the reference. ――What should I do with the arguments? --Like creating with SqlQueryCreate - SqlQueryCreate - model_name ――I'm not sure. However, when I put the model string, it started to work. - sql --Explanation omitted
from looker_sdk import methods31, models31
#Create API arguments
SQL = "select * from xxxxxxx limit 10"
query = models31.SqlQueryCreate( model_name="xxxxxxxx",sql="select * from public.xxxxx limit 10")
#Execute query
result = sdk.create_sql_query(body=query).slug
Apparently, there is a module in looker_sdk that corresponds to the API version. This time it is 3.1, so select the one that corresponds to it
sdk.run_sql_query(result,"csv",True)
--Check here when you throw SQL and an empty one comes back.
It will come back in binary, so if you save it in binary, you will be able to use it.
fw = open('result.csv', 'wb')
fw.write(sdk.run_sql_query(result,"csv",True))
fw.close()
To be honest, I was wondering if it would be nice to have Looker execute SQL locally. Many people look at the dashboard, and if you want, you can connect BigQuery with pandas, so most of the merits of what I investigated this time do not feel like how to use the SDK. While using Looker's assets, for queries that are too complicated or change frequently to handle on Looker, get it with SQL via SDK like this → Build a mechanism to automatically rotate analysis I think it is meaningful.
Get authentication key
Official description
looker-open-source(github)
Recommended Posts