Query Metabase data in Python
source link: https://towardsdatascience.com/query-metabase-data-in-python-ea7f866e6782
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Query Metabase data in Python
Metabase rest API and Python API
Metabase is an open-source BI tool, where you can store data, connect to outside data sources, query, and visualizing data. This article talks about when we have data stored in Metabase, how do we query the data and get results in Python. The first part of this article talks about how to use the Metabase rest API to query data. The second part tries out a Python API. Let’s get started.
Metabase rest API
Get session token
To use the Metabase rest API, we first need to get a session token. In the command line type the following:
get tokenThe output will be your token. Then save this token in your Python script or Jupyter Notebooks.
token = "YOUR_TOKEN"
Query card
There are two ways to query Metabase data. The first option is to query data from a card, or a question you asked on the UI. In the UI, we can ask a question and query the data directly through the UI:
Once we have created the question, we can check if this question shows up in the api/card:
The output shows a list of dictionaries, each of which corresponds to a question user asked through the UI. Then we can find the ID number of the card and get the result of the card with the following code. This basically queries the database with the query you defined in the card (question).
get card dataQuery database directly
Instead of defining the card first and querying the card, the second option is to query the database directly.
Let’s assume we want to query the table users from the database 1 and output all the rows that are created after 2020–10–15. Here we need to define this query in the SQL native query. And then added the query to the requests.post function. The resulting res.json() is a dictionary. To read in the data and the columns. We can get all the data from res.json()['data']['row'] , and then get the column names from res.json()['data']['results_metadata']['columns'] .
Now you should be able to see the results of your query in df.
Python API
I also tried out a Python API metabasepy. Similar to the Query card section we mentioned above, here we can query card 1 with the following code:
from metabasepy import Client, MetabaseTableParsercli = Client(username=username, password=password, base_url=endpoint)cli.authenticate()query_response = cli.cards.query(card_id="1")data_table = MetabaseTableParser.get_table(metabase_response=query_response)df = pd.DataFrame(data_table.__dict__['rows'])
df
I feel like the Python API is less flexible. But you are certainly welcome to try it out.
Conclusion
Here I showed you three ways to query data from Metabase: query from the card using Metabase rest API, query directly from the database with a user-defined query using Metabase rest API, and query from the card using a Python API metabasepy.
I prefer querying directly from the database with a user-defined query using Metabase rest API. With this method, we can have all our queries in the Python script and we know exactly what we are querying.
There are also many other API calls available in Metabase we did not cover. The code in this article should be a good starting point for you to learn and explore other API functions. Hope you enjoy it! Thanks!
References
Recommend
-
131
数据可视化的开源方案: Superset vs Redash vs Metabase (二)
-
24
Matrix 精选 Matrix 是少数派的写作社区，我们主张分享真实的产品体验，有实用价值的经验与思考。我们会不定期挑选 Matrix 最优质的文章，展示来自用户的最真实的体验和观点。 文章代表作者个人观点，少数派仅对标...
-
14
Metabase-BI系列12：0.35版本表达式是真的香 发表于 2020-04-12...
-
11
7 handy Metabase features you may not know about Apr 30, 2021 by The Metabase Team Metabase’s interface tries to stay out of your way to help bring your data to the forefront. This laid-back approach means that s...
-
7
Migrating to Metabase Cloud Jan 11, 2021 by The Metabase Team Ready to move your existing Metabase instance to Metabase Cloud? We’v...
-
6
How to run Metabase in production Dec 1, 2020 by The Metabase Team There are two editions of Metabase: Open Source and Enterprise. And there are two basic ways to run them in production: Meta...
-
6
Metabase 0.39 Apr 20, 2021 by The Metabase Team Metabase 0.39 brings better search, upgraded custom expressions, and ships with a boatload of other polish and improvements. Finding questions and dashboar...
-
4
Metabase 0.33 Preview - A query for your thoughts Jun 24, 2019 by The Metabase team One of our strengths at Metabase has been getting you to a pretty simple chart on your own without having to know (or use) SQL.
-
7
Unlocking Your Data With Metabase and AWS FargatePosted on Tuesday, Aug 9, 2022I love data. I mean, I really love data. Data gives you the ability to understand the world around you and, to a certain degree, project wh...
-
13
The 2023 Metabase Community Data Stack Report A look at the current state of data tooling and self-service analytics Earlier this year,...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK